为什么InnoDB索引有长度限制 MySQL之 (为什么inner join 后数据变多了)

本文目录导航：

• MySQL之：为什么InnoDB索引有长度限制？
• MYSQL中MyISAM 和InnoDB索引的区别
• mysql的innodb引擎中,主键索引和普通索引的工作原理是什么

MySQL之：为什么InnoDB索引有长度限制？

在MySQL的InnoDB存储引擎中，索引的长度限制是一个重要设计考虑因素，旨在优化查询性能和存储效率。

让我们深入探讨为什么索引会有长度限制。

B+树是InnoDB内部数据结构的基础，其特点是所有数据在叶子节点存储，且数据按键值顺序排列。

这确保了高效的搜索和插入操作。

InnoDB支持主键索引和非主键索引。

主键索引包含完整数据，而非主键索引只包含指针到主键数据。

系统参数`innodb_page_size`定义了页大小，影响数据库读写操作。

在当前版本（>=5.7.6）中，可以选择的值有4096、8192、、、，默认值为16KB。

这个值直接影响行数据和索引的存储。

考虑行数据中的隐藏字段，如`trx_id`、`roll_ptr`和`row_id`，它们合计使用6+7+6字节。

这意味着一张页中至少应包含2行数据以保持B+树的效率。

因此，每行的大小不能超过大约8000字节。

将此限制除以2，得到每行索引的大小上限为4000字节。

实际存储中还需预留空间，使得每行索引的最大实际大小约为3072字节。

对于聚簇索引，InnoDB将主键和数据存储在同一个页中，查找时直接访问数据。

这种设计提供了高效的数据访问，但数据的物理顺序与索引顺序一致。

如果主键不是自增ID，数据布局会更加复杂，频繁的调整可能导致性能下降。

相反，使用自增ID作为主键，数据布局更加紧凑，磁盘碎片少，提高了查询效率。

非聚簇索引将数据与索引分开存储，通过主键索引实现快速访问。

然而，这要求额外的查询步骤以获取完整数据。

这种设计在复杂查询场景中提供了灵活性，但可能不如聚簇索引高效。

总之，InnoDB索引长度限制是为了确保数据的高效访问、存储和优化查询性能。

通过合理选择索引结构和参数，可以最大限度地发挥InnoDB的性能优势。

MYSQL中MyISAM 和InnoDB索引的区别

MySQL中的索引是在存储引擎中实现的，MySQL拥有多种存储引擎，但大部分使用B+树作为索引结构，包括MyISAM和InnoDB。

MyISAM的索引文件与数据文件是分离的，索引存储在MYI文件中，是一种非聚合的存储方式。

InnoDB则将索引和数据文件共同保存，数据共享时存放在ibdata文件中，独享时存放在ibd文件中。

InnoDB的每个表都仅有一个聚集索引。

如果没有定义主键，会自动选择一个非空唯一索引作为替代主键；若仍不存在，则会定义一个隐藏的主键作为聚集索引。

聚集索引与非聚集索引有别：非聚集索引的叶子节点包含指向表中记录的指针，记录的物理顺序与索引顺序不一致；而聚集索引则将数据行与键值一同存储在叶子节点，记录的排列顺序与索引的排列顺序一致。

MyISAM和InnoDB在索引结构上的差异，导致了它们在性能、数据完整性和事务处理方面各有优劣。

选择哪种存储引擎应根据具体的应用需求来决定。

MyISAM适合用于读密集型应用，因它提供了更快的读取速度。

InnoDB则更适合写操作较多的场景，它支持事务处理和行级锁定，能更好地保证数据的一致性和完整性。

尽管InnoDB拥有更强大的事务支持，但它的读写速度可能不如MyISAM，尤其是在涉及大量并发操作时。

总之，MyISAM和InnoDB在索引存储方式上的不同，决定了它们在不同场景下的表现。

正确选择存储引擎对于优化数据库性能至关重要。

在实际应用中，开发者需要根据业务需求，权衡读写性能、数据完整性和事务处理能力，来决定使用MyISAM还是InnoDB。

值得注意的是，InnoDB支持外键约束，而MyISAM不支持。

因此，在需要维护复杂数据关系的应用中，InnoDB是更好的选择。

此外，InnoDB支持事务，可以实现数据的一致性。

对于需要频繁更新数据的应用，InnoDB能提供更好的支持。

然而，对于一些简单的查询应用，MyISAM因其更高的查询效率，可能更为合适。

总之，选择MyISAM还是InnoDB，需要根据具体的应用场景来决定。

mysql的innodb引擎中,主键索引和普通索引的工作原理是什么

在InnoDB引擎的存储结构中，主键索引和普通索引有显著的不同。

主键索引以B+树结构存储，其叶子节点直接存储整行数据，而普通索引则存储主键值，叶子节点中不包含实际数据行。

插入数据时，主键索引的维护相对简单，仅需在树结构中追加新节点即可。

而普通索引在插入时，如果主键值已存在，需在B+树中查找并移动已有记录，甚至可能触发页面分裂，影响性能。

页面分裂会导致数据存储效率降低，但通过合并操作，可以恢复页面利用率。

在设计表时，应考虑自增主键，因其插入数据时无需移动已有记录，性能更优。

而使用业务字段作为主键时，应确保其有序性，否则插入成本较高。

从存储空间角度考虑，使用更短的主键值可减少普通索引的叶子节点大小，从而节省存储空间。

对于KV场景，直接使用业务字段作为主键，可避免搜索多棵树，提高查询效率。

在设计数据库表结构时，应综合考虑性能、存储空间和数据插入的便利性，合理选择主键类型，以优化数据库的查询和存储效率。