mysql表连接执行顺序查看_[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

以上面为例, 因为联表查询全部并行运行, 导致t1表的索引失效. 而t1表的是最庞大的表, 约有50万行数据, 而其他表只是几百行的配置信息表.

t1表的索引是性能的关键.

为了能利用上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

http://www.cnblogs.com/oDoraemon/p/9001326.html