MST

星途 面试题库

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

在复杂的业务场景下,有多个关联子查询嵌套且涉及多表联合查询的情况。例如,有`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')的用户名称,并且要确保查询性能最优。请详细阐述优化思路,包括但不限于索引的创建、查询语句的改写、执行计划分析等方面。
50.1万 热度难度
数据库MySQL

知识考点

AI 面试

面试题答案

一键面试

优化思路

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