引言

本文用于记录作者在一个后台管理系统项目中，遇到的页面查询join太多不必要的表导致慢SQL问题，以及优化过程。

问题现象

北美的某个客户公司的后台管理系统，设备列表查询较慢，影响用户体验

性能分析

通过arthas工具抓取SQL，发现第一页查询共2条SQL，1条count语句，1条select语句，将两条SQL分别执行一下

select

device.id as did,

device.mid as modelId,

device.uid,

device.dserial as imei,

device.area_id as areaId,

device.binding_time as bindTime,

device.pointname as deviceName,

device.test_flag testFlag,

device.serial_number serialNumber,

u.user_email userEmail,

dm.id modelId,

dm.name AS modelName,

dm.app_type appTypeId,

sc.iccid,

pi.num AS planName,

pi.id AS planId,

cc.combo_id AS simComboId,

cc.combo_name AS simComboName,

ci.update_time as latestPicTime,

u.id as uid,

u.uuid,

(

select count(*) as total

from m2m.`camera_image` cii

where cii.`status` = 1 and cii.did = device.id

) as photoNum

from m2m.device device

left join m2m.sim_device ds on ds.did = device.id

left join m2m.sim_card sc on ds.sim_id = sc.id and sc.is_del = 1

left join m2m.combo_card cc on sc.id = cc.sim_id and cc.status = 1

left join m2m.device_plan dp on dp.did = device.id and dp.del_status = 0

left join m2m.plan_item pi on pi.id = dp.plan_id and pi.del_status = 0

LEFT JOIN m2m.device_model dm on device.mid = dm.id

left join m2m_user.`user` u on device.uid = u.id

left join m2m.camera_info ci on ci.did = device.id

WHERE device.dstatus = 1

and dm.app_type = 1 LIMIT 10;

SELECT

COUNT(*) AS total

FROM

m2m.device device

LEFT JOIN m2m.sim_device ds ON

ds.did = device.id

LEFT JOIN m2m.sim_card sc ON

ds.sim_id = sc.id

AND sc.is_del = 1

LEFT JOIN m2m.combo_card cc ON

sc.id = cc.sim_id

AND cc.status = 1

LEFT JOIN m2m.device_plan dp ON

dp.did = device.id

AND dp.del_status = 0

LEFT JOIN m2m.plan_item pi ON

pi.id = dp.plan_id

AND pi.del_status = 0

LEFT JOIN m2m.device_model dm ON

device.mid = dm.id

LEFT JOIN m2m_user.`user` u ON

device.uid = u.id

LEFT JOIN m2m.camera_info ci ON

ci.did = device.id

WHERE

device.dstatus = 1

AND dm.app_type = 1;

如下图，明显是count语句慢

SQL调优

该SQL明显是开发人员比较懒，没有任何优化，直接把select列表查询的SQL加个count就上线了，

我们单从count语句的SQL本身分析，

where条件中，WHERE device.dstatus = 1 AND dm.app_type = 1;

很明显where条件只用到了device表和dm表，因此count的结果只与device表和dm表有关，查询时只需要关联查询这两张表即可，其余条件都可以去掉。优化后SQL如下：

select
COUNT(1)
from
m2m.device device
LEFT JOIN m2m.device_model dm on
device.mid = dm.id
WHERE
device.dstatus = 1
and dm.app_type = 1;

成果检验

如下图，查询耗时从4.5s降低至540ms

经验总结

1、后台管理的页面查询时，通常会有两条SQL，一条是查询列表数据，一条是统计列表结果的数量，方便分页。

2、针对count语句，尽量去掉不必要的join。