面试题：MySQL InnoDB数据页记录存储对查询性能有哪些影响

Question

结合InnoDB数据页中记录的存储机制，分析不同的记录存储结构和布局会对各种查询（如范围查询、等值查询等）性能产生怎样的影响，并阐述原因。

Answer 1

不同记录存储结构和布局对查询性能的影响及原因

1. 范围查询

• 紧凑存储布局：在InnoDB中，如果记录存储紧凑，数据页内记录排列紧密，范围查询性能相对较好。例如，主键有序存储时，对于按主键范围查询，InnoDB可以利用页目录快速定位到包含起始记录的页，然后通过链表顺序扫描页内记录。因为记录紧密排列，减少了I/O操作，磁盘读取的数据量相对较少，所以性能提升。这是由于减少了不必要的页间跳转和数据冗余读取。
• 非紧凑存储布局：若记录存储不紧凑，存在大量碎片，范围查询时可能需要读取更多的数据页，因为记录分布较为分散，原本可以在一个页内完成的范围查找，可能因碎片问题需要跨多个页，增加了I/O开销，性能会显著下降。

2. 等值查询

• 聚簇索引存储结构：当基于聚簇索引进行等值查询时，由于聚簇索引将数据和索引存放在一起，且主键顺序决定了数据物理存储顺序。InnoDB可以通过B+树快速定位到具体的叶子节点，该叶子节点就包含了完整的记录数据，查询性能极高。这是因为B+树的结构特点，使得查找路径短，能快速定位到目标记录。
• 非聚簇索引存储结构：对于非聚簇索引，等值查询首先通过非聚簇索引B+树定位到索引记录，该记录包含指向聚簇索引的指针，然后需要再根据这个指针去聚簇索引中查找实际数据，这就是所谓的“回表”操作。相比聚簇索引直接获取数据，多了一次I/O操作，性能会有所降低。

3. 辅助索引与复合索引

• 辅助索引：辅助索引存储结构对于非主键列的查询很有帮助。它有自己独立的B+树结构，当查询条件基于辅助索引列时，可以快速定位到相关记录。但由于辅助索引不存储完整数据，查到记录位置后可能需回表操作，对性能有一定影响。影响程度取决于回表的次数和数据量。
• 复合索引：复合索引由多个列组成。如果查询条件能利用复合索引的最左前缀原则，性能会得到提升。例如，复合索引为 (col1, col2, col3)，查询条件为 WHERE col1 = 'value1' AND col2 = 'value2'，InnoDB可以利用B+树快速定位到满足条件的记录。但如果查询条件不满足最左前缀原则，如 WHERE col2 = 'value2'，则索引无法有效利用，性能可能较差。