面试题：MySQL查询优化之执行计划分析

Question

给出如下MySQL查询语句：SELECT t1.column1, t2.column2 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id WHERE t1.status = 'active' AND t2.value > 100; 请使用EXPLAIN关键字分析此查询的执行计划，解释各个字段的含义，并根据分析结果提出至少两种优化方案。

Answer 1

使用EXPLAIN分析查询执行计划

当在上述查询语句前加上EXPLAIN关键字，即EXPLAIN SELECT t1.column1, t2.column2 FROM table1 t1 JOIN table2 t2 ON t1.id = t2.table1_id WHERE t1.status = 'active' AND t2.value > 100;，会得到如下类似结果（不同版本MySQL显示字段可能略有差异）：

1. id：表示查询中执行SELECT子句或操作表的顺序。如果是单个SELECT语句，通常为1。如果有子查询或联合查询，会有多个不同的id值。
2. select_type：常见的值有SIMPLE（简单查询，不包含子查询或UNION）、PRIMARY（最外层的查询）、SUBQUERY（子查询中的第一个SELECT）等。在此查询中为SIMPLE。
3. table：显示这一行的数据是关于哪张表的，这里会显示t1和t2。
4. partitions：如果表是分区表，这里会显示查询访问的分区。如果不是分区表，显示NULL。
5. type：表示连接类型，常见的有ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（使用非唯一索引查找匹配行）、eq_ref（对于每个来自前面表的行组合，从该表中读取一行，常用于主键或唯一键的连接）等。如果这里是ALL，意味着性能较差，需要优化。
6. possible_keys：显示可能应用在这张表上的索引。如果为空，说明没有可用的索引。
7. key：实际使用的索引。如果为NULL，则没有使用索引。
8. key_len：表示索引中使用的字节数，可用来判断使用了索引中的哪些部分。
9. ref：显示使用哪个列或常数与索引进行比较。
10. rows：估计为了找到所需的行而要读取的行数。行数越多，性能越差。
11. filtered：表示存储引擎返回的数据在满足表条件的情况下，经过过滤后满足查询条件的记录数量的百分比。

优化方案

1. 添加索引：
   • 为table1表的status字段添加索引：CREATE INDEX idx_table1_status ON table1(status);，这样在WHERE t1.status = 'active'条件时可以使用索引，减少全表扫描。
   • 为table2表的value字段添加索引：CREATE INDEX idx_table2_value ON table2(value);，在WHERE t2.value > 100条件时可利用索引快速定位符合条件的数据。
   • 为table2表的table1_id字段添加索引：CREATE INDEX idx_table2_table1_id ON table2(table1_id);，使得JOIN操作时可以通过索引快速匹配关联数据。
2. 调整表结构：
   • 如果table1和table2数据量非常大，可以考虑对表进行分区，例如按时间或其他业务逻辑分区，这样查询时可以只扫描相关分区的数据，提高查询效率。
3. 查询重写：
   • 检查是否可以通过调整JOIN和WHERE子句的顺序来优化执行计划。例如，先在table2中过滤出value > 100的数据，再与table1进行JOIN，可能会减少中间数据量。可以尝试将查询改写为SELECT t1.column1, t2.column2 FROM (SELECT * FROM table2 WHERE value > 100) t2 JOIN table1 t1 ON t1.id = t2.table1_id WHERE t1.status = 'active';，但要注意子查询可能带来的其他性能影响，需要实际测试验证。