面试题：MySQL关联子查询性能优化深入分析

Question

在复杂的业务场景下，有多个关联子查询嵌套且涉及多表联合查询的情况。例如，有`users`表（`user_id`, `user_name`）、`orders`表（`order_id`, `user_id`, `order_status`）、`order_details`表（`detail_id`, `order_id`, `product_id`, `price`）、`products`表（`product_id`, `product_name`）。现在需要查询出所有状态为已完成订单且购买了特定产品（假设产品名称为'ProductX'）的用户名称，并且要确保查询性能最优。请详细阐述优化思路，包括但不限于索引的创建、查询语句的改写、执行计划分析等方面。

Answer 1

优化思路

1. 索引创建
   • 在 orders 表的 order_status 列上创建索引，用于快速筛选出状态为已完成的订单。

   CREATE INDEX idx_order_status ON orders (order_status);
   

   • 在 order_details 表的 order_id 列上创建索引，方便关联 orders 表。

   CREATE INDEX idx_order_id ON order_details (order_id);
   

   • 在 order_details 表的 product_id 列上创建索引，便于筛选出购买特定产品的记录。

   CREATE INDEX idx_product_id ON order_details (product_id);
   

   • 在 products 表的 product_name 列上创建索引，用于快速定位特定产品。

   CREATE INDEX idx_product_name ON products (product_name);
   

2. 查询语句改写
   • 尽量避免子查询嵌套，可使用 JOIN 操作来替代。

   SELECT DISTINCT users.user_name
   FROM users
   JOIN orders ON users.user_id = orders.user_id
   JOIN order_details ON orders.order_id = order_details.order_id
   JOIN products ON order_details.product_id = products.product_id
   WHERE orders.order_status = '已完成'
     AND products.product_name = 'ProductX';
   

3. 执行计划分析
   • 使用 EXPLAIN 关键字分析查询语句的执行计划。

   EXPLAIN SELECT DISTINCT users.user_name
   FROM users
   JOIN orders ON users.user_id = orders.user_id
   JOIN order_details ON orders.order_id = order_details.order_id
   JOIN products ON order_details.product_id = products.product_id
   WHERE orders.order_status = '已完成'
     AND products.product_name = 'ProductX';
   

   • 查看执行计划中的 type 字段，确保尽可能多的表连接类型为 index 或 eq_ref，以提高查询性能。如果某些表连接类型不理想，进一步调整索引或查询语句。
   • 关注 Extra 字段，查看是否有 Using temporary 或 Using filesort 等信息。若出现 Using temporary，意味着查询过程中创建了临时表，可能会影响性能，需要优化查询语句避免这种情况；若出现 Using filesort，表示进行了文件排序，可通过调整索引来避免不必要的排序操作。