SQL性能优化记录

笑醉踏歌行

已于 2024-02-03 10:56:50 修改

阅读量342

点赞数 11

分类专栏： MySQL 文章标签： sql 数据库前端

于 2024-02-03 10:55:29 首次发布

本文链接：https://blog.csdn.net/u011096468/article/details/136004981

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

原始SQL

SELECT a.member_id,  a.store_id,
        (SELECT temp.member_name FROM ex_member temp WHERE temp.member_id = a.parent_member_id) AS parent_member_name,
       (SELECT IFNULL(COUNT(o.order_id),0) FROM shop_order o WHERE a.`member_id` = o.`member_id` AND o.order_pay_status = 2
                                                               AND DATE_FORMAT(o.create_time,'%Y-%m-%d') >= DATE_FORMAT(NOW(),'%Y-%m-%d'))AS order_num,
       (SELECT IFNULL(COUNT(o.order_real_pay_amount),0) FROM shop_order o WHERE a.`member_id` = o.`member_id` AND o.order_pay_status = 2
                                                            AND DATE_FORMAT(o.create_time,'%Y-%m-%d') >= DATE_FORMAT(NOW(),'%Y-%m-%d')) AS order_amount
        FROM ex_member a
        WHERE a.parent_member_id = 305

修改前 EXPLAIN 结果

调整思路大体如下

*避免使用SELECT ：在查询中使用SELECT *会返回所有列，这可能会导致不必要的数据传输和处理。最好明确列出所需的列。
使用更具体的列名：在查询中，使用了b.member_id等列名前缀，这在可读性方面没有问题，但可能会导致列名过长。如果列名在上下文中是唯一的，可以省略前缀以简化代码。
避免不必要的子查询：子查询(SELECT IFNULL(COUNT(o.order_id),0) FROM shop_order o WHERE b.member_id= o.member_idAND o.order_pay_status = 2 AND DATE_FORMAT(o.create_time,'%Y-%m-%d') >= DATE_FORMAT(#{createTime},'%Y-%m-%d'))AS order_num可以替换为JOIN操作，这样可以提高性能。
对常用查询字段增增加索引,防止整表扫描

修改后SQL及查询结果如下

SELECT a.member_id,
       a.member_identification,

       a.remark,
       a.store_id,
       p.member_name AS parent_member_name,
       IFNULL(COUNT(o.order_id), 0) AS order_num,
       IFNULL(SUM(o.order_real_pay_amount), 0) AS order_amount
FROM ex_member a
LEFT JOIN shop_order o ON a.member_id = o.member_id AND o.order_pay_status = 2 
                     AND DATE_FORMAT(o.create_time, '%Y-%m-%d') >= DATE_FORMAT(NOW(), '%Y-%m-%d')
 LEFT JOIN ex_member AS p ON  a.member_id = p.member_id 
WHERE a.parent_member_id = 305
GROUP BY a.member_id;

修改后 EXPLAIN 结果