面试题：MySQL中InnoDB B+树索引有哪些常见的注意事项

Question

请简要阐述在MySQL的InnoDB存储引擎下，B+树索引在日常使用过程中，有哪些需要特别关注的注意事项，例如对查询性能的影响方面等。

Answer 1

• 索引选择：
  • 覆盖索引：尽量使用覆盖索引，即查询列包含在索引中，避免回表操作。例如SELECT col1, col2 FROM table WHERE col1 = 'value';，若(col1, col2)建立联合索引，查询效率更高，因为无需再回表获取数据。
  • 前缀索引：对于长字符串列，可使用前缀索引以减少索引空间。如对长文本字段text_column，CREATE INDEX idx_text ON table(text_column(10));使用前10个字符建立索引，但要注意前缀长度选择，太短可能导致索引选择性差。
• 索引维护：
  • 定期重建：随着数据的增删改，B+树索引会产生碎片，定期重建索引（如ALTER TABLE table_name ENGINE = InnoDB;）可优化性能，提高空间利用率。
  • 删除无用索引：无用索引不仅占用空间，还会影响写入性能。应定期检查并删除不再使用的索引，可通过SHOW INDEX FROM table_name;查看索引使用情况。
• 查询优化：
  • 最左匹配原则：联合索引遵循最左匹配原则。例如有(col1, col2, col3)联合索引，WHERE col1 = 'value' AND col2 = 'value'能利用索引，而WHERE col2 = 'value'则不能充分利用。
  • 避免函数操作：避免在索引列上使用函数。如WHERE UPPER(col1) = 'VALUE';会导致索引失效，应改为WHERE col1 = 'value';并确保数据一致性。
  • 范围查询：范围查询（如WHERE col1 > 'value'）后，索引后续列无法使用。例如(col1, col2)联合索引，WHERE col1 > 'value' AND col2 = 'value'，col2无法使用索引。