面试题：MySQL优化器选择索引时如何处理多列索引与单列索引的优先性？

Question

在复杂查询场景中，涉及到既有多列索引又有单列索引的情况，MySQL优化器基于哪些因素来判断使用多列索引还是单列索引更优？请详细说明判断的逻辑和考量的指标。

Answer 1

1. 查询条件覆盖度：
   • 考量逻辑：如果查询条件能覆盖多列索引中的大部分列，优化器倾向于使用多列索引。例如，对于多列索引(col1, col2, col3)，若查询为WHERE col1 = 'value1' AND col2 = 'value2'，大部分索引列被使用，多列索引更合适。
   • 指标：计算查询条件中涉及索引列的数量与总索引列数量的比例，比例越高越倾向使用多列索引。
2. 选择性：
   • 考量逻辑：索引的选择性是指索引列中不同值的数量与总行数的比例。选择性越高，索引过滤效果越好。对于单列索引，看该列的选择性；对于多列索引，综合看各列选择性以及组合后的选择性。如果单列索引的选择性非常高，且查询仅依赖该列过滤，优化器可能选择单列索引。例如，某单列索引列col有90%的不同值，而多列索引组合后的选择性不如该单列索引，可能选单列索引。
   • 指标：选择性计算公式为不同值数量 / 总行数，可通过SHOW INDEX FROM table_name查看索引信息，结合表行数计算。
3. 排序需求：
   • 考量逻辑：若查询结果需要按某索引列或多列排序，且多列索引顺序与排序顺序匹配，优化器会考虑多列索引。例如，查询SELECT * FROM table_name ORDER BY col1, col2，若存在多列索引(col1, col2)，则可利用该索引直接完成排序，无需额外排序操作，此时多列索引更优。
   • 指标：查看查询语句中的ORDER BY子句与索引列顺序的匹配程度。
4. 索引基数：
   • 考量逻辑：索引基数是指索引中不同值的数量。基数越大，索引区分度越好。优化器会比较单列索引和多列索引的基数。如果多列索引组合后的基数大于单列索引基数，且查询条件能利用多列索引，会倾向多列索引。
   • 指标：可通过SHOW INDEX FROM table_name查看索引的基数信息。
5. 回表成本：
   • 考量逻辑：当使用索引获取数据时，如果索引列不能满足查询所需的所有列，就需要回表操作，即根据索引查到的主键再到聚簇索引中获取其他列数据。多列索引若能覆盖更多查询列，减少回表次数，优化器会更倾向它。例如，查询SELECT col1, col2, col3 FROM table_name WHERE col1 = 'value1'，若单列索引只包含col1，则需回表获取col2和col3；而多列索引(col1, col2, col3)可直接获取所有列，减少回表，此时多列索引更优。
   • 指标：评估查询列在索引中的覆盖程度，以及预计回表的次数和数据量。