[MySQL] MySQL联表查询的执行顺序优化查询
SELECT t4.orgName, t3.projectName, t3.Partner, t1.type, COUNT(DISTINCT t1.imei) AS count FROM `t_teminal` AS t1 LEFT JOIN `t_orgcode_orgid_rela` AS t2 ON t1.orgid = t2.orgId LEFT JOIN `t_org_code` AS t3 ON t2.topOrganizationCode = t3.topOrganizationCode LEFT JOIN `t_organization` AS t4 ON t1.orgid = t4.orgId GROUP BY t1.orgid
联表查询会涉及到多个表的操作, 在索引的利用上会受限制. 上面的命令执行时间720s
以上面为例, 因为join查询的实现原理[MySQL 的Join及底层实现原理], 导致驱动表t1表的索引失效。而t1表的是最庞大的表, 约有50万行数据, 而其他表只是几百行的配置信息表.
所以利用t1表的索引是性能的关键.
由于统计中有groupby and count的需求,通过t1表即可实现, 顺势使用了子查询, 先处理完t1,再去匹配信息.
这种思路很简单,但应该对各种业务导向的报表查询都有帮助,.
下面的命令执行时间3.6s
SELECT t4.orgName, t3.projectName, t3.Partner, t1.type, t1.count FROM ( SELECT orgid, type, COUNT(DISTINCT imei) AS count FROM `t_teminal` GROUP BY orgid ) AS t1 LEFT JOIN `t_orgcode_orgid_rela` AS t2 ON t1.orgid = t2.orgId LEFT JOIN `t_org_code` AS t3 ON t2.topOrganizationCode = t3.topOrganizationCode LEFT JOIN `t_organization` AS t4 ON t1.orgid = t4.orgId