❝开头还是介绍一下群，如果感兴趣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 | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 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 ，在加完这些索引后，整体的查询变得有效且快速。
置顶
和架构师沟通那种“一坨”的系统，推荐只能是OceanBase，Why ?跟我学OceanBase4.0 --阅读白皮书 (OB分布式优化哪里了提高了速度)
跟我学OceanBase4.0 --阅读白皮书 （4.0优化的核心点是什么）
跟我学OceanBase4.0 --阅读白皮书 （0.5-4.0的架构与之前架构特点）
跟我学OceanBase4.0 --阅读白皮书 （旧的概念害死人呀，更新知识和理念）
MongoDB 相关文章
MongoDB “升级项目” 大型连续剧(4)-- 与开发和架构沟通与扫尾
MongoDB “升级项目” 大型连续剧(3)-- 自动校对代码与注意事项
MongoDB “升级项目” 大型连续剧(2)-- 到底谁是"der"
MongoDB “升级项目” 大型连续剧(1)-- 可“生”可不升
MongoDB 大俗大雅，上来问分片真三俗 -- 4 分什么分
MongoDB 大俗大雅，高端知识讲“庸俗” --3 奇葩数据更新方法
MongoDB 大俗大雅，高端的知识讲“通俗” -- 2 嵌套和引用
MongoDB 大俗大雅，高端的知识讲“低俗” -- 1 什么叫多模
MongoDB 合作考试报销活动 贴附属，MongoDB基础知识速通
MongoDB 使用网上妙招，直接DOWN机---清理表碎片导致的灾祸 (送书活动结束)
MongoDB 2023年度纽约 MongoDB 年度大会话题 -- MongoDB 数据模式与建模
“PostgreSQL” 高性能主从强一致读写分离，我行，你没戏！
POLARDB 添加字段 “卡” 住---这锅Polar不背
PolarDB 版本差异分析--外人不知道的秘密（谁是绵羊，谁是怪兽）
PolarDB 答题拿-- 飞刀总的书、同款卫衣、T恤，来自杭州的Package（活动结束了）
PolarDB for MySQL 三大核心之一POLARFS 今天扒开它--- 嘛是火
PostgreSQL 无服务 Neon and Aurora 新技术下的新经济模式 （翻译）
“PostgreSQL” 高性能主从强一致读写分离，我行，你没戏！
全世界都在“搞” PostgreSQL ，从Oracle 得到一个“馊主意”开始
PostgreSQL 加索引系统OOM 怨我了--- 不怨你怨谁
PostgreSQL “我怎么就连个数据库都不会建？” --- 你还真不会！
PostgreSQL 稳定性平台 PG中文社区大会--杭州来去匆匆
PostgreSQL 分组查询可以不进行全表扫描吗？速度提高上千倍？
POSTGRESQL --Austindatabaes 历年文章整理
PostgreSQL 查询语句开发写不好是必然，不是PG的锅
MySQL相关文章
文章转载自AustinDatabases，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
