本文共 9607 字，大约阅读时间需要 32 分钟。
工作上碰到了几个慢查询，之前在没有SSD的情况下要跑十几个小时，有了SSD以后依旧需要400秒以上，这两天仔细地研究了一下它的执行计划，对其进行了相应的调优。
原SQL特点：写得非常复杂，大量的join，order by，group by，并且join后的结果集非常大（大概500w以上），join的where条件选择性很低，排除了拆分为多步的方法。
原查询1
SELECT
order_only.driver_id,
SUBSTRING_INDEX(
GROUP_CONCAT(
t_status
ORDERBY
dispatch_detail_idDESC
),
',',
'100'
)AS accept_status
FROM
(
SELECT
newest.dispatch_detail_id,
newest.driver_id,
newest.service_order_id,
newest.t_status
FROM
(
SELECT
dispatch_detail.dispatch_detail_id,
dispatch_detail.driver_id,
dispatch_detail.service_order_id,
IF(
dispatch_detail.accept_status= '1',
1,
0
)AS t_status,
dispatch_detail.dispatch_time
FROM
dispatch_detail
INNERJOIN service_order ON (
dispatch_detail.service_order_id= service_order.service_order_id
)
WHERE
service_order.STATUS = 7
ANDdispatch_detail.dispatch_time <= '1411228799'
ORDERBY
dispatch_detail.service_order_idASC,
t_statusDESC
)AS newest
GROUPBY
driver_id,
service_order_id
)AS order_only
GROUP BY
driver_id
优化措施：强行修改service_order和dispatch_detail表的关联顺序，并且去掉原SQL中多余的order by操作，使得之前的全盘扫描改为ref查找，生产库库测试从437秒优化到269秒
修改后查询：
SELECT
order_only.driver_id,
SUBSTRING_INDEX(
GROUP_CONCAT(
t_status
ORDERBY
dispatch_detail_idDESC
),
',',
'100'
)AS accept_status
FROM
(
SELECT
newest.dispatch_detail_id,
newest.driver_id,
newest.service_order_id,
newest.t_status
FROM
(
SELECTSTRAIGHT_JOIN
dispatch_detail.dispatch_detail_id,
dispatch_detail.driver_id,
dispatch_detail.service_order_id,
IF(
dispatch_detail.accept_status= '1',
1,
0
)AS t_status,
dispatch_detail.dispatch_time
FROM
service_order
INNERJOIN dispatch_detail ON (
dispatch_detail.service_order_id= service_order.service_order_id
)
WHERE
service_order.STATUS = 7
ANDdispatch_detail.dispatch_time <= '1411228799'
ORDERBY
dispatch_detail.service_order_idASC,
t_statusDESC
)AS newest
GROUPBY
driver_id,
service_order_id
)AS order_only
GROUP BY
driver_id
原查询2
SELECT
order_only.driver_id,
order_only.city,
SUBSTRING_INDEX(
GROUP_CONCAT(
order_only.contribution
ORDERBY
end_timeDESC,
dispatch_detail_idDESC
),
',',
'100'
)AS contribution
FROM
(
SELECT
dispatch_detail_id,
driver_id,
service_order_id,
contribution,
city,
end_time
FROM
(
SELECT
a.dispatch_detail_idAS dispatch_detail_id,
a.driver_idAS driver_id,
a.service_order_idAS service_order_id,
a.scoreAS contribution,
b.cityAS city,
b.end_timeAS end_time
FROM
yc_core.dispatch_detailAS a
INNERJOIN yc_core.service_order AS b ON (
a.service_order_id= b.service_order_id
)
WHERE
b.`status`= 7
ANDa.score != 0
AND(
b.end_timeBETWEEN '1370534400'
AND'1411228799'
)
ORDER BY
end_timeDESC,
dispatch_detail_idDESC
)AS newest
GROUPBY
driver_id,
service_order_id
)AS order_only
GROUP BY
driver_id
优化措施：强行修改service_order和dispatch_detail表的关联顺序，去除多余的order by操作，使得之前的全盘扫描改为ref查找，生产库库测试从384秒优化到183秒
优化后：
SELECT
order_only.driver_id,
order_only.city,
SUBSTRING_INDEX(
GROUP_CONCAT(
order_only.contribution
ORDERBY
end_timeDESC,
dispatch_detail_idDESC
),
',',
'100'
)AS contribution
FROM
(
SELECT
dispatch_detail_id,
driver_id,
service_order_id,
contribution,
city,
end_time
FROM
(
SELECTSTRAIGHT_JOIN
a.dispatch_detail_idAS dispatch_detail_id,
a.driver_idAS driver_id,
a.service_order_idAS service_order_id,
a.scoreAS contribution,
b.cityAS city,
b.end_timeAS end_time
FROM
yc_core.service_orderAS b
INNERJOIN yc_core.dispatch_detail AS a ON(
a.service_order_id= b.service_order_id
)
WHERE
b.`status`= 7
ANDa.score != 0
AND(
b.end_timeBETWEEN '1370534400'
AND'1411228799'
)
)AS newest
GROUPBY
driver_id,
service_order_id
)AS order_only
GROUP BY
driver_id
原查询3
SELECT
a.driver_idAS driver_id,
a.cityAS city,
'tswk'AS `type`,
'1411243201'AS `stat_time`,
'tswk'AS time_flag,
service_kilometers,
service_times,
income,
complete_order,
accept_order,
refuse_order,
receive_order,
success_order
FROM
(
SELECT
d.driver_idAS driver_id,
d.cityAS city,
SUM(dependable_distance)AS service_kilometers,
SUM(
IF(
start_time> 0
ANDend_time > 0
ANDend_time - start_time > 0,
end_time- start_time,
0
)
)AS service_times,
SUM(sharing_amount)AS income,
COUNT(service_order_id)AS complete_order
FROM
yc_core.service_orderAS s
LEFTJOIN yc_crm_common.driver AS d ON s.driver_id = d.driver_id
WHERE
end_timeBETWEEN 1410710400
AND1411315199
AND`status` >= 7
ANDd.driver_id > 0
ANDd.city != ''
GROUPBY
driver_id
)AS a
LEFT JOIN (
SELECT
driver_id,
SUM(IF(accept_status= 1, 1, 0)) AS accept_order,
SUM(IF(accept_status= 2, 1, 0)) AS refuse_order,
COUNT(service_order_id)AS receive_order,
SUM(IF(decision_result= 2, 1, 0)) AS success_order
FROM
(
SELECT
driver_id,
service_order_id,
accept_status,
decision_result
FROM
(
SELECT
driver_id,
yc_core.dispatch_detail.service_order_id,
accept_status,
decision_result
FROM
yc_core.dispatch_detail,
yc_core.dispatch
WHERE
yc_core.dispatch_detail.service_order_id= yc_core.dispatch.service_order_id
ANDyc_core.dispatch. STATUS IN (50, 51)
ANDyc_core.dispatch_detail.dispatch_time BETWEEN 1410710400
AND1411315199
ORDERBY
dispatch_detail_idDESC
)AS tmp_dispatch_detail
GROUPBY
driver_id,
service_order_id
)AS tmp_select_car
GROUPBY
driver_id
) AS b ON a.driver_id = b.driver_id
UNION
SELECT
b.driver_idAS driver_id,
b.cityAS city,
'tswk'AS `type`,
'1411243201'AS `stat_time`,
'tswk'AS time_flag,
service_kilometers,
service_times,
income,
complete_order,
accept_order,
refuse_order,
receive_order,
success_order
FROM
(
SELECT
driver_id,
SUM(dependable_distance)AS service_kilometers,
SUM(
IF(
start_time> 0
ANDend_time > 0
ANDend_time - start_time > 0,
end_time- start_time,
0
)
)AS service_times,
SUM(sharing_amount)AS income,
COUNT(service_order_id)AS complete_order
FROM
yc_core.service_order
WHERE
end_timeBETWEEN 1410710400
AND1411315199
AND`status` >= 7
GROUPBY
driver_id
)AS a
RIGHTJOIN (
SELECT
d.driver_idAS driver_id,
d.cityAS city,
accept_order,
refuse_order,
receive_order,
success_order
FROM
(
SELECT
driver_id,
SUM(IF(accept_status= 1, 1, 0)) AS accept_order,
SUM(IF(accept_status= 2, 1, 0)) AS refuse_order,
COUNT(service_order_id)AS receive_order,
SUM(IF(decision_result= 2, 1, 0)) AS success_order
FROM
(
SELECT
driver_id,
service_order_id,
accept_status,
decision_result
FROM
(
SELECT
driver_id,
yc_core.dispatch_detail.service_order_id,
accept_status,
decision_result
FROM
yc_core.dispatch_detail,
yc_core.dispatch
WHERE
yc_core.dispatch_detail.service_order_id= yc_core.dispatch.service_order_id
ANDyc_core.dispatch. STATUS IN (50, 51)
ANDyc_core.dispatch_detail.dispatch_time BETWEEN 1410710400
AND1411315199
ORDERBY
dispatch_detail_idDESC
)AS tmp_dispatch_detail
GROUPBY
driver_id,
service_order_id
)AS tmp_select_car
GROUPBY
driver_id
)AS s
LEFTJOIN yc_crm_common.driver AS d ON s.driver_id = d.driver_id
WHERE
d.driver_id> 0
ANDd.city != ''
GROUPBY
driver_id
)AS b ON a.driver_id = b.driver_id
优化措施：
强制使用索引FORCE INDEX(dispatch_time)，简化大量的group by操作。
另外结果集和原先的查询结果在success_order，refuse_order两列上有细微差别，分析原因是因为原查询使用了GROUPBY driver_id,service_order_id而导致相关的数据变成了随机数据，不知道这是否是业务需求。
最后查询性能从1300秒变成110秒，提高了10倍以上。
优化后：
SELECT
a.driver_idAS driver_id,
a.cityAS city,
'tswk'AS `type`,
'1411243201'AS `stat_time`,
'tswk'AS time_flag,
service_kilometers,
service_times,
income,
complete_order,
accept_order,
refuse_order,
receive_order,
success_order
FROM
(
SELECT
d.driver_idAS driver_id,
d.cityAS city,
SUM(dependable_distance)AS service_kilometers,
SUM(
IF(
start_time> 0
ANDend_time > 0
ANDend_time - start_time > 0,
end_time- start_time,
0
)
)AS service_times,
SUM(sharing_amount)AS income,
COUNT(service_order_id)AS complete_order
FROM
yc_core.service_orderAS s
LEFTJOIN yc_crm_common.driver AS d ON s.driver_id = d.driver_id
WHERE
end_timeBETWEEN 1410710400
AND1411315199
AND`status` >= 7
ANDd.driver_id > 0
ANDd.city != ''
GROUPBY
driver_id
order by null
)AS a
LEFT JOIN (
SELECT
driver_id,
SUM(IF(accept_status= 1, 1, 0)) AS accept_order,
SUM(IF(accept_status= 2, 1, 0)) AS refuse_order,
COUNT(dispatch.service_order_id)AS receive_order,
SUM(IF(decision_result= 2, 1, 0)) AS success_order
FROM
yc_core.dispatch_detailFORCE INDEX (dispatch_time),
yc_core.dispatch
WHERE
yc_core.dispatch_detail.service_order_id= yc_core.dispatch.service_order_id
ANDyc_core.dispatch. STATUS IN (50, 51)
ANDyc_core.dispatch_detail.dispatch_time BETWEEN 1410710400
AND1411315199
GROUPBY
driver_id
order by null
) AS b ON a.driver_id = b.driver_id
UNION
SELECT
b.driver_idAS driver_id,
b.cityAS city,
'tswk'AS `type`,
'1411243201'AS `stat_time`,
'tswk'AS time_flag,
service_kilometers,
service_times,
income,
complete_order,
accept_order,
refuse_order,
receive_order,
success_order
FROM
(
SELECT
driver_id,
SUM(dependable_distance)AS service_kilometers,
SUM(
IF(
start_time> 0
ANDend_time > 0
ANDend_time - start_time > 0,
end_time- start_time,
0
)
)AS service_times,
SUM(sharing_amount)AS income,
COUNT(service_order_id)AS complete_order
FROM
yc_core.service_order
WHERE
end_timeBETWEEN 1410710400
AND1411315199
AND`status` >= 7
GROUPBY
driver_id
order by null
)AS a
RIGHTJOIN (
SELECT
d.driver_idAS driver_id,
d.cityAS city,
accept_order,
refuse_order,
receive_order,
success_order
FROM
(
SELECT
driver_id,
SUM(IF(accept_status= 1, 1, 0)) AS accept_order,
SUM(IF(accept_status= 2, 1, 0)) AS refuse_order,
COUNT(dispatch.service_order_id)AS receive_order,
SUM(IF(decision_result= 2, 1, 0)) AS success_order
FROM
yc_core.dispatch_detailFORCE INDEX (dispatch_time),
yc_core.dispatch
WHERE
yc_core.dispatch_detail.service_order_id= yc_core.dispatch.service_order_id
ANDyc_core.dispatch. STATUS IN (50, 51)
ANDyc_core.dispatch_detail.dispatch_time BETWEEN 1410710400
AND1411315199
GROUPBY
driver_id
order by null
)AS s
LEFTJOIN yc_crm_common.driver AS d ON s.driver_id = d.driver_id
WHERE
d.driver_id> 0
ANDd.city != ''
GROUPBY
driver_id
)AS b ON a.driver_id = b.driver_id
转载地址：http://gubgb.baihongyu.com/