面试题：MySQL InnoDB行记录格式优化时索引结构如何配合

Question

在对InnoDB行记录格式进行优化时，索引结构起着至关重要的作用。请详细说明不同的行记录格式下，如何设计和优化索引结构以提高查询性能，例如在Compact格式下，针对不同类型的查询，应如何创建合适的索引。

Answer 1

Compact格式下索引结构设计与优化

1. 等值查询
   • 单列索引：若查询条件为对单个列进行等值判断，如SELECT * FROM table_name WHERE column_name = 'value';，在该列上创建单列索引能大幅提升查询效率。InnoDB会利用B - Tree索引快速定位到对应记录。
   • 复合索引：当等值查询涉及多个列，且这些列经常同时出现在查询条件中，如SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'value2';，创建复合索引(col1, col2)，索引顺序应与查询条件顺序尽量一致，InnoDB可通过索引快速定位满足所有条件的记录。
2. 范围查询
   • 单列索引：对于单列的范围查询，如SELECT * FROM table_name WHERE column_name > 'value';，在该列上创建单列索引即可。InnoDB通过B - Tree索引能够快速定位起始位置，并按顺序扫描符合范围条件的记录。
   • 复合索引：若范围查询与等值查询混合，例如SELECT * FROM table_name WHERE col1 = 'value1' AND col2 > 'value2';，创建复合索引(col1, col2)，先通过col1的等值条件快速定位到一个较小范围，再在该范围内通过col2的范围条件筛选记录。但要注意，复合索引中，只有最左前缀列能用于范围查询优化，如果查询是SELECT * FROM table_name WHERE col1 > 'value1' AND col2 = 'value2';，仅col1能利用索引的范围查找特性，col2的索引作用不大。
3. 前缀索引
   • 当列值较长，如长字符串列，为节省索引空间，可创建前缀索引。例如，对于一个很长的text类型列description，若经常使用该列前几个字符进行查询，如SELECT * FROM table_name WHERE description LIKE 'prefix%';，可创建前缀索引CREATE INDEX idx_description ON table_name(description(10));，这里10表示取前10个字符作为索引，但要注意选择合适的前缀长度，既要保证索引空间节省，又要确保足够的区分度，避免过多的索引冲突。
4. 覆盖索引
   • 若查询只需要索引列的值，而不需要回表操作获取整行记录，可创建覆盖索引。例如SELECT col1, col2 FROM table_name WHERE col1 = 'value';，创建复合索引(col1, col2)，这样InnoDB可直接从索引中获取所需数据，无需再根据索引定位到数据行，减少I/O操作，提升查询性能。

其他行记录格式下索引设计与优化思路类似

1. Redundant格式 虽然Redundant格式较旧，但索引设计原则与Compact类似。对于等值、范围等查询，同样要依据查询条件创建合适的单列或复合索引。不过由于Redundant格式在存储结构上的特点，可能索引占用空间相对较大，在创建索引时更要注意权衡空间与性能。
2. Dynamic和Compressed格式 这两种格式在存储上对大字段有优化。索引设计方面，除遵循上述基本的等值、范围查询索引创建原则外，对于大字段相关的查询，若经常对大字段的部分内容进行查询（如大文本字段的前缀查询），可创建合适的前缀索引。并且由于它们在存储上的优势，可适当创建更多覆盖索引以提升查询性能，同时要关注索引维护成本，避免过多索引导致写入性能大幅下降。