MySQL 8 SQL 优化两则 ---常见问题

❝

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2800人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 +9）(1 2 3 4 5 6 7群均已爆满，开8群约300 9群 100+)

最近上线一个新的系统，之前一直没管，等上线一看，好嘛。这语句写的。

SELECT DISTINCT o.id, 
                        o.title, 
                        o.pri, 
                        o.is_customer_server, 
                        o.is_closed, 
                        o.actor, 
                        DATE_FORMAT(o.createdDate, '%Y-%m-%d %H:%i:%s') AS createdDate, 
                        COALESCE(DATE_FORMAT(o.resolvedDate, '%Y-%m-%d %H:%i:%s'), '') AS resolvedDate,
                        o.reportTo, 
                        o.assignedTo, 
                        o.resolveTo,
                        o.resolveType, 
                        pt.name as product,
                        d.dept_name as dept,
                        COALESCE(DATE_FORMAT(o.closedDate, '%Y-%m-%d %H:%i:%s'), '') AS closedDate,
                        o.reportTo,
                        COALESCE(o.smallVersion, '') AS smallVersion,
                        CASE 
                        WHEN o.isMiniPrograms = 1 THEN '是'
                        WHEN o.isMiniPrograms = 2 THEN '否'
                        ELSE ''
                        END AS isMiniPrograms,
                        CASE 
                        WHEN o.severity = 1 THEN '致命'
                        WHEN o.severity = 2 THEN '严重'
                        WHEN o.severity = 3 THEN '一般'
                        WHEN o.severity = 4 THEN '轻'
                        ELSE ''
                        END AS severity,
                        bt.name as type,
                        CASE 
                        WHEN o.is_closed = 1 THEN '已关闭'
                        WHEN o.resolvedDate IS NOT NULL THEN '已解决'
                        ELSE '待解决'
                        END AS status,
                        CASE 
                        WHEN o.greenOrangeTag = 1 THEN '是' ELSE '否'
                        END AS greenOrangeTag,
                        -- 解析resolveType名称
                        COALESCE(
                            (SELECT st.name FROM solve_type st WHERE st.id = o.resolveType),
                            '无'
                        ) AS resolveType, 
                        CASE 
                        WHEN o.is_cloud = 1 THEN '是'
                        WHEN o.is_cloud = 2 THEN '否'



    
                        END AS is_cloud,
                        COALESCE(o.cloud_status, '') AS cloud_status, 
                        u1.nickname AS assignedToUsername,
                        COALESCE(u2.nickname, '') AS resolveToUsername,
                        m.name as module,
                        COALESCE(c.action_name, '') AS closedName,
                        COALESCE(g.storeVersion, '') AS storeVersion,
                        o.isreview,
                        COALESCE(p.name, '') AS productName,
                        COALESCE(TIMESTAMPDIFF(SECOND,  stc.endDate ,NOW()), 0) AS sla_time_seconds,
                        stc.endDate,
                        COALESCE(DATE_FORMAT(stc.endDate, '%Y-%m-%d %H:%i:%s'), '') AS endDateStr
                   FROM `order` o
                   JOIN users_user u1 ON o.assignedTo = u1.id
                   left JOIN users_user u2 ON o.resolveTo = u2.id
                   JOIN bug_type bt ON o.type = bt.id
                   left join (SELECT orderId, COUNT(*) as comment_count FROM order_life_action
                             WHERE action='激活' GROUP BY orderId) re on o.id = re.orderId
                   left join (SELECT * FROM order_life_action 
                        where action!='创建' ) b on o.id = b.orderId AND b.action != '创建'
                   left join (SELECT * FROM order_life_action
                        WHERE action='确认并关闭') c on o.id = c.orderId and u1.nickname = c.action_name
                   left join group_code g on o.id=g.orderId
                   left join project_group pg on g.slyGroupCode=pg.group_id
                   left join project p on p.id = pg.project_id
                   left join department d on d.id = o.dept
                   left join product pt on pt.id = o.product
                   left join module m on m.id = o.module
                   left join order_review_reviewmain rm on rm.order_id = o.id 
                   left join sla_time_cycle_recode stc on stc.orderId = o.id and stc.is_delete = 0
                   left join proposer_department pd on pd.orderId = o.id
                            WHERE o.resolvedstatus in ('unresolved') AND  o.is_deleted=0  and o.dept not in (9153);

+----+--------------------+-------------------+------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------+--------+----------+-----------------------------------------------------------------+
| id | select_type        | table             | partitions | type   | possible_keys                         | key                                   | key_len | ref                   | rows   | filtered | Extra                                                           |
+----+--------------------+-------------------+------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------+--------+----------+-----------------------------------------------------------------+
|  1 | PRIMARY            | o                 | NULL       | ref    | idx_order_dept,idx_order_dept_pri     | idx_order_dept_pri                    | 131     | const                 |    534 |     8.32 | Using where; Using temporary                                    |
|  1 | PRIMARY            | bt                | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.type         |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | u1                | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.assignedTo   |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | u2                | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.resolveTo    |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            |         | NULL       | ref    |                            |                            | 4       | sunday.o.id           |     10 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | order_life_action | NULL       | ref    | idx_order_life_action_orderId_date    | idx_order_life_action_orderId_date    | 4       | sunday.o.id           |      5 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | order_life_action | NULL       | ref    | idx_order_life_action_orderId_date    | idx_order_life_action_orderId_date    | 4       | sunday.o.id           |      5 |   100.00 | Using where                                                     |



    
|  1 | PRIMARY            | g                 | NULL       | ref    | idx_group_code_orderId_slyGroupCode   | idx_group_code_orderId_slyGroupCode   | 4       | sunday.o.id           |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | pg                | NULL       | ref    | idx_project_group_group_id_project_id | idx_project_group_group_id_project_id | 131     | sunday.g.slyGroupCode |      1 |   100.00 | Using where; Using index                                        |
|  1 | PRIMARY            | p                 | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.pg.project_id  |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | d                 | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.dept         |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | pt                | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.product      |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | m                 | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.module       |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | rm                | NULL       | index  | idx_rm_order_id                       | idx_rm_order_id                       | 130     | NULL                  |    996 |   100.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
|  1 | PRIMARY            | stc               | NULL       | ref    | idx_stc_orderId_is_delete             | idx_stc_orderId_is_delete             | 4       | sunday.o.id           |      1 |   100.00 | Using where                                                     |
|  1 | PRIMARY            | pd                | NULL       | ref    | idx_pd_orderId                        | idx_pd_orderId                        | 4       | sunday.o.id           |      1 |   100.00 | Using where; Using index; Distinct                              |
|  3 | DERIVED            | order_life_action | NULL       | index  | idx_order_life_action_orderId_date    | idx_order_life_action_orderId_date    | 12      | NULL                  | 299833 |    10.00 | Using where                                                     |
|  2 | DEPENDENT SUBQUERY | st                | NULL       | eq_ref | PRIMARY                               | PRIMARY                               | 8       | sunday.o.resolveType  |      1 |   100.00 | Using where                                                     |
+----+--------------------+-------------------+------------+--------+---------------------------------------+---------------------------------------+---------+-----------------------+--------+----------+-----------------------------------------------------------------+
18 rows inset, 4 warnings (0.003 sec)

从语句优化的角度和方法，我这一眼就看到了一个关键的优化部分，合并同类项。在很多开发撰写语句的时候都是按照自己的思路去写SQL，所以导致很多情况下，有可以合并的同类项。

这个SQL就是典型的这类优化的方法，我们可以下图发现端倪。对于一个语句的数据过滤，写了两遍。同时我们把LEFT JOIN 中的表的查询条件融合在 JOIN语句本身，这样有助于类似MYSQL这样的数据库产品快速的编译SQL语句，避免改写后的错误。

SELECT DISTINCT 
    o.id,
    o.title, 
    o.pri, 
    o.is_customer_server, 
    o.is_closed, 
    o.actor, 
    DATE_FORMAT(o.createdDate, '%Y-%m-%d %H:%i:%s') AS createdDate, 
    COALESCE(DATE_FORMAT(o.resolvedDate, '%Y-%m-%d %H:%i:%s'), '') AS resolvedDate,
    o.reportTo, 
    o.assignedTo, 
    o.resolveTo,
    o.resolveType, 
    pt.name AS product,
    d.dept_name AS dept,
    COALESCE(DATE_FORMAT(o.closedDate, '%Y-%m-%d %H:%i:%s'), '') AS closedDate,
    o.reportTo,
    COALESCE(o.smallVersion, ''


    
) AS smallVersion,
    CASE 
        WHEN o.isMiniPrograms = 1 THEN '是'
        WHEN o.isMiniPrograms = 2 THEN '否'
        ELSE ''
    END AS isMiniPrograms,
    CASE 
        WHEN o.severity = 1 THEN '致命'
        WHEN o.severity = 2 THEN '严重'
        WHEN o.severity = 3 THEN '一般'
        WHEN o.severity = 4 THEN '轻'
        ELSE ''
    END AS severity,
    bt.name AS type,
    CASE 
        WHEN o.is_closed = 1 THEN '已关闭'
        WHEN o.resolvedDate IS NOT NULL THEN '已解决'
        ELSE '待解决'
    END AS status,
    CASE 
        WHEN o.greenOrangeTag = 1 THEN '是' ELSE '否'
    END AS greenOrangeTag,
    COALESCE((SELECT st.name FROM solve_type st WHERE st.id = o.resolveType), '无') AS resolveType, 
    CASE 
        WHEN o.is_cloud = 1 THEN '是'
        WHEN o.is_cloud = 2 THEN '否'
    END AS is_cloud,
    COALESCE(o.cloud_status, '') AS cloud_status, 
    u1.nickname AS assignedToUsername,
    COALESCE(u2.nickname, '') AS resolveToUsername,
    m.name AS module,
    COALESCE(c.action_name, '') AS closedName, -- 请确保 c 来源的 JOIN 已放在查询中
    COALESCE(g.storeVersion, '') AS storeVersion,
    o.isreview,
    COALESCE(p.name, '') AS productName,
    COALESCE(TIMESTAMPDIFF(SECOND, stc.endDate, NOW()), 0) AS sla_time_seconds,
    stc.endDate,
    COALESCE(DATE_FORMAT(stc.endDate, '%Y-%m-%d %H:%i:%s'), '') AS endDateStr
FROM `order` o
JOIN users_user u1 ON o.assignedTo = u1.id
LEFT JOIN users_user u2 ON o.resolveTo = u2.id
JOIN bug_type bt ON o.type = bt.id
LEFT JOIN order_life_action c ON o.id = c.orderId AND c.action = '确认并关闭' -- 确保 c 连接正确定义
LEFT JOIN group_code g ON o.id = g.orderId
LEFT JOIN project_group pg ON g.slyGroupCode = pg.group_id
LEFT JOIN project p ON p.id = pg.project_id
LEFT JOIN department d ON d.id = o.dept
LEFT JOIN product pt ON pt.id = o.product
LEFT JOIN module m ON m.id = o.module
LEFT JOIN sla_time_cycle_recode stc ON stc.orderId = o.id AND stc.is_delete = 0
WHERE o.resolvedstatus IN ('unresolved') 
  AND o.is_deleted = 0 
  AND o.dept NOT IN (9153);

整体在执行后，比原来的速度提高50%左右，执行计划看上去也比以前要好很多，只是把冗余的部分修改了一下而已。

第二个SQL是

SELECT
                do.id,
                do.title,
                do.status,



    
                u.nickname AS actorNickname,
                DATE_FORMAT(do.createdDate, '%Y-%m-%d %H:%i:%s') AS createdDate,
                COALESCE(do.phone, '') AS phone,
                do.product,
                do.module,
                do.product_manager,
                u2.nickname AS productManagerNickname,
                do.examiner,
                do.reviewer,
                do.orther_product_manager,
                do.business,
                COALESCE(do.demandtime, '') AS demandtime,
                COALESCE(do.needpurpose, '') AS needpurpose,
                COALESCE(do.questiontype, '') AS questiontype,
                COALESCE(do.questiongrade, '') AS questiongrade,
                do.grademsg,
                do.dept_name,
                do.dept,
                do.type,
                COALESCE(DATE_FORMAT(do.schedule_month, '%Y-%m-%d %H:%i:%s'), '') AS schedule_month,
                CASE 
                WHEN do.is_customized = 1 THEN '是'
                ELSE '否'
                END AS is_customized,
                COALESCE(do.cloud_examiner, '') AS cloudExaminer,
                COALESCE(DATE_FORMAT(do.cloud_gmt_examiner, '%Y-%m-%d %H:%i:%s'), '') AS cloud_gmt_examiner,
                COALESCE(do.cloud_reviewer, '') AS cloud_reviewer,
                COALESCE(DATE_FORMAT(do.cloud_gmt_reviewer, '%Y-%m-%d %H:%i:%s'), '') AS cloud_gmt_reviewer,
                COALESCE(do.cloud_pm, '') AS cloud_pm,
                COALESCE(DATE_FORMAT(do.cloud_gmt_pm, '%Y-%m-%d %H:%i:%s'), '') AS cloud_gmt_pm,
                d1.dept_name AS firstDeptName,
                d2.dept_name AS secondDeptName,
                p.name AS project,
                do.curator,
                u3.nickname AS curatorName,
                COALESCE(DATE_FORMAT(do.pm_start_time, '%Y-%m-%d %H:%i:%s'), '') AS pmStartTime,
                TIME_FORMAT(TIMEDIFF(do.pm_end_time, do.pm_start_time), '%H小时 %i分钟') AS communicateTime
            FROM
                demand_order do
            JOIN
                users_user u ON do.actor_id = u.id
            LEFT JOIN
                department d1 ON u.firstDept = d1.wx_code
            LEFT JOIN
                department d2 ON u.secondDept = d2.wx_code
            LEFT JOIN
                users_user u2 ON do.product_manager = u2.id
            LEFT JOIN
                users_user u3 ON do.curator = u3.username
            left join
                project p on p.id = do.project
            WHERE createdDate BETWEEN '2025-03-29 00:00:00' AND '2025-04-28 23:59:59' AND  do.is_deleted=0;

这个SQL一看就有两处的毛病

1  createDate 没有写前缀，这样运行没有问题但是会在前期执行会进行判断，一般我们都要标清到底是哪个表的createdate

2  is_deleted 应该 上移，上移到 demand_order的表有关的 left join 的条件上，这样更早的过滤数据，尽量不要等数据都查询完毕后，在进行过滤。当然在部分情况无法进行，主要还是业务逻辑的问题，这就牵扯到第二个问题，如果确定业务逻辑是inner join的，就尽量不要写成left join。也能提高SQLQ运行效率。

另外这个SQL缺少索引，如 department 的 wx_code, users_user的 username, 以及demand_order 的createdDate ，在加完这些索引后，整体的查询变得有效且快速。