面试题：MySQL回表代价分析与简单优化策略

Question

假设在一个包含百万条记录的MySQL表中，有联合索引(a, b)，现在执行查询语句`SELECT * FROM table_name WHERE a = 'value' AND b > 'another_value';`，请分析该查询可能产生的回表代价，并提出至少一种优化策略。

Answer 1

回表代价分析

1. 索引使用情况：由于存在联合索引(a, b)，条件 a = 'value' 能利用索引进行快速定位，确定满足 a = 'value' 的索引记录。但对于 b > 'another_value'，索引只能进一步筛选，但无法直接获取全部列的数据。
2. 回表操作：当通过索引找到满足条件的索引项后，因为查询是 SELECT *，需要获取表中的所有列数据，而索引中可能不包含所有列，所以需要根据索引中的主键值回表到聚簇索引中获取完整的行记录。在百万条记录的表中，若满足条件的记录较多，回表操作会带来较大的I/O开销，导致性能下降。

优化策略

1. 覆盖索引优化：
   • 方法：修改查询语句为只查询索引中包含的列，例如，如果表中有列 a、b、c，且联合索引(a, b)，可以修改为 SELECT a, b FROM table_name WHERE a = 'value' AND b > 'another_value';。这样查询可以直接从索引中获取数据，无需回表操作，大大提升查询性能。
   • 示例：假设表结构为 CREATE TABLE table_name (id INT PRIMARY KEY, a VARCHAR(50), b VARCHAR(50), c VARCHAR(50), INDEX idx_a_b (a, b));，将查询 SELECT * FROM table_name WHERE a = 'value' AND b > 'another_value'; 修改为 SELECT a, b FROM table_name WHERE a = 'value' AND b > 'another_value';。
2. 调整索引结构：
   • 方法：如果业务允许，可以考虑创建包含更多查询列的复合索引，例如 CREATE INDEX idx_a_b_c ON table_name (a, b, c);（假设查询还涉及列 c）。这样在查询 SELECT a, b, c FROM table_name WHERE a = 'value' AND b > 'another_value'; 时也无需回表。
   • 注意事项：增加索引会占用更多的存储空间，并且会影响插入、更新和删除操作的性能，需要综合评估业务读写场景来确定是否使用该策略。