面试题：MySQL中优化器选择索引的基本策略有哪些？

Question

请简要阐述MySQL优化器在选择索引时所遵循的基本策略，例如在什么样的查询条件下，优化器倾向于选择某个特定的索引。

Answer 1

1. 全值匹配：
   • 当查询条件与索引列的顺序完全匹配时，优化器倾向于选择该索引。例如，假设有一个索引 (col1, col2, col3)，查询语句为 SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'value2' AND col3 = 'value3';，这种情况下优化器很可能使用该索引。
2. 最左前缀原则：
   • 索引可以用于查找匹配最左前缀的行。例如对于索引 (col1, col2, col3)，查询 SELECT * FROM table_name WHERE col1 = 'value1'; 或 SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'value2'; 都会利用该索引。但如果查询 SELECT * FROM table_name WHERE col2 = 'value2'; 则不会使用该索引，因为没有满足最左前缀 col1。
3. 范围查询：
   • 在范围查询时，如 BETWEEN、<、> 等操作符。假设索引为 (col1, col2)，查询 SELECT * FROM table_name WHERE col1 BETWEEN 'value1' AND 'value2'; 会使用该索引。不过在范围查询后，索引的后续列不能再用于过滤，例如 SELECT * FROM table_name WHERE col1 BETWEEN 'value1' AND 'value2' AND col2 = 'value3';，col2 不能再通过索引优化，因为 col1 已经是范围查询。
4. 排序优化：
   • 如果查询需要对某列进行排序，并且该列在索引中，优化器可能选择该索引。例如 SELECT * FROM table_name ORDER BY col1;，如果存在 col1 列的索引，优化器可能利用此索引来避免额外的排序操作。但如果是 SELECT * FROM table_name ORDER BY col1, col2;，则需要 (col1, col2) 这样的复合索引才能更好地优化排序。
5. 避免索引覆盖：
   • 当查询的列全部包含在索引中（索引覆盖），优化器可能优先选择该索引。例如 SELECT col1, col2 FROM table_name WHERE col1 = 'value1';，如果有索引 (col1, col2)，因为查询列都在索引中，直接从索引获取数据比回表查询效率高，优化器可能选择该索引。