MyISAM与InnoDB的索引差异

MyISAM和InnoDB都使用B+树来实现索引： • MyISAM的索引与数据分开存储 • MyISAM的索引叶子存储指针，主键索引与普通索引无太大区别 • InnoDB的聚集索引和数据行统一存储 • InnoDB的聚集索引存储数据行本身，普通索引存储主键 • InnoDB一定有且只有一个聚集索引 • InnoDB建议使用趋势递增整数作为PK，而不宜使用较长的列作为PK MySQL中的存储引擎MyISAM和InnoDB在处理索引上有显著的差异，这些差异主要体现在索引的组织方式以及对数据存储的影响上。两者都基于B+树这种高效的索引结构，但具体实现有所不同。 MyISAM的索引采用非聚集索引（UnClustered Index）的方式。这意味着索引和数据行是分开存储的。不论是主键索引还是普通索引，它们的叶子节点都存储索引列和指向对应行记录的指针。在MyISAM中，主键索引和普通索引之间并没有本质的区别。如果表中没有定义主键，MyISAM会选择一个唯一且非空的列作为隐含主键。由于索引和数据分离，进行查询时，系统需要先通过索引找到指针，然后根据指针对数据行进行访问。 相比之下，InnoDB使用的是聚集索引（Clustered Index），数据行与索引是存储在一起的。在InnoDB中，主键索引的叶子节点直接包含数据行本身，而普通索引的叶子节点则存储主键值。正因为如此，InnoDB的主键查询速度通常较快。每个表在InnoDB中必须有一个聚集索引，如果没有显式定义主键，系统会选择第一个非空unique列，或者创建一个隐藏的row-id作为聚集索引。由于数据行在物理存储上与索引集成，InnoDB的表只能有一个聚集索引。这使得在插入新记录时，如果主键不是趋势递增的，可能导致索引页分裂和行记录的移动，影响性能。 对于InnoDB来说，使用较长的列作为主键可能导致普通索引变得庞大，因为每个普通索引的叶子节点都需要存储主键值。因此，建议选择较短且趋势递增的整数类型作为主键，以减少索引大小并优化插入性能。例如，如果查询条件是根据非主键列执行的，如`select * from t where name='lisi'`，InnoDB会首先通过name辅助索引找到主键值，然后通过聚集索引定位到数据行，这意味着这类查询可能需要扫描两次索引。 MyISAM的索引和数据分离，提供了更快的索引查找速度，但需要额外的指针来定位数据行。InnoDB的索引与数据行合一，提供了更快的主键查询，但对主键的选择和插入操作的顺序敏感。在实际应用中，选择哪种存储引擎和索引策略应根据业务需求和性能优化的目标来决定。