面试题：MySQL中如何通过查询性能剖析优化复杂多表连接查询

SELECT 
    u.user_id, 
    u.name, 
    u.age, 
    o.order_id, 
    o.order_date, 
    od.detail_id, 
    od.product_name
FROM 
    users u
JOIN 
    orders o ON u.user_id = o.user_id
JOIN 
    order_details od ON o.order_id = od.order_id
ORDER BY 
    o.order_date DESC;

使用EXPLAIN关键字优化查询

执行EXPLAIN：在上述SQL语句前加上EXPLAIN关键字，即EXPLAIN SELECT...。执行后会得到查询计划，其中包含以下关键信息：
- id：表示查询中每个SELECT子句的标识符，相同id表示同一层次的查询。
- select_type：查询类型，常见的有SIMPLE（简单查询，不包含子查询或联合查询）。
- table：查询涉及的表名。
- partitions：分区信息（如果表是分区表）。
- type：连接类型，常见的有ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（非唯一索引扫描）等。ALL性能最差，尽量优化为其他类型。
- possible_keys：可能使用的索引。
- key：实际使用的索引。如果为NULL，则未使用索引。
- key_len：使用的索引长度。
- ref：显示哪些列或常量被用于查找索引列上的值。
- rows：估计需要扫描的行数。
- filtered：按表条件过滤的行百分比估计值。
优化措施
- 添加索引：
  - 在orders表的user_id列上添加索引，可提高users表与orders表连接的效率。CREATE INDEX idx_orders_user_id ON orders(user_id);
  - 在order_details表的order_id列上添加索引，可提高orders表与order_details表连接的效率。CREATE INDEX idx_order_details_order_id ON order_details(order_id);
  - 在orders表的order_date列上添加索引，可加速按order_date降序排序。CREATE INDEX idx_orders_order_date ON orders(order_date);
- 调整表连接顺序：如果EXPLAIN显示某些表连接顺序不佳，可尝试调整JOIN的顺序。通常小表驱动大表性能更好，即先连接数据量较小的表。
- 避免函数操作：查询条件中避免对列使用函数操作，如DATE_FORMAT(o.order_date, '%Y-%m')，因为这会导致索引失效，只能进行全表扫描。
- 使用覆盖索引：如果查询的列都包含在索引中，可使用覆盖索引，避免回表操作，提高查询性能。例如，如果经常查询user_id、order_date和product_name，可以创建一个复合索引CREATE INDEX idx_user_order_product ON orders(user_id, order_date), order_details(order_id, product_name);（索引列顺序要根据查询频率和选择性调整）。

星途面试题库

面试题：MySQL中如何通过查询性能剖析优化复杂多表连接查询

知识考点

面试题答案

使用EXPLAIN关键字优化查询