面试题：MySQL中基于B+树索引的排序优化策略

覆盖索引：确保索引涵盖查询中SELECT、WHERE、ORDER BY子句涉及的列。例如，如果查询为SELECT column1, column2 FROM table1 JOIN table2 ON table1.id = table2.id WHERE table1.some_column = 'value' ORDER BY table2.sort_column;，应创建包含table1.some_column、table2.sort_column以及连接条件table1.id的复合索引，如CREATE INDEX idx_covering ON table1 (some_column, id); CREATE INDEX idx_covering_table2 ON table2 (id, sort_column);。这样查询过程中可直接从索引获取数据，减少回表操作。
前缀索引：对于长字符串列，使用前缀索引可减少索引大小，提高查询性能。但要注意选择合适的前缀长度，在保持索引选择性的同时降低索引存储开销。例如，CREATE INDEX idx_partial ON long_string_column (long_string_column(10));（假设选择10个字符作为前缀）。

避免使用SELECT *：只选择需要的列，减少数据传输和处理量。例如，SELECT specific_column1, specific_column2 FROM tables;
优化连接顺序：MySQL优化器通常会选择成本最低的连接顺序，但有时需要手动调整。一般原则是将小表放在JOIN左侧，因为MySQL会先扫描左侧表，然后为其每一行匹配右侧表数据。例如，SELECT * FROM small_table JOIN large_table ON small_table.id = large_table.id;
减少子查询：子查询可能导致性能问题，尽量使用JOIN替代。例如，原本子查询SELECT column FROM outer_table WHERE column IN (SELECT sub_column FROM sub_table);可改写为SELECT outer_table.column FROM outer_table JOIN sub_table ON outer_table.column = sub_table.sub_column;

索引碎片：随着数据的插入、删除和更新，索引可能产生碎片，导致查询性能下降。解决方案是定期重建或优化索引，在MySQL中可使用ALTER TABLE table_name ENGINE=InnoDB;（重建表及索引）或OPTIMIZE TABLE table_name;（优化表及索引）。
高并发下的锁争用：在高并发环境中，多个查询可能同时访问和修改索引数据，导致锁争用。可以通过适当调整事务隔离级别（如从默认的REPEATABLE READ调整为READ COMMITTED）、使用行级锁而非表级锁（InnoDB存储引擎默认行级锁，但某些操作可能升级为表级锁，需注意）以及优化业务逻辑，减少锁的持有时间来缓解。
索引膨胀：过多或不合理的索引会占用大量磁盘空间，并增加数据修改操作的开销。定期审查索引使用情况，删除不再使用的索引，可通过SHOW INDEX FROM table_name;查看索引信息，结合EXPLAIN分析索引在查询中的实际使用情况。

星途面试题库