多表关联时,在order by中使用了非驱动表字段进行排序,会出现Using temporary; Using filesort,此时需要优化。
mysql会自动将数据量较小的表设置为驱动表,但是我们排序的字段又不是该表的字段,使用left join,可以阻止小表被优化为驱动表,但是这种方式往往无法满足业务需求。
此时使用exists子查询刚好满足需求,可以使该表不再被动的置为驱动表,不再出现Using temporary; Using filesort
多表关联时,字段与字段之间的关联顺序也很有讲究,从执行计划中我们可以看到,mysql会首先确定驱动表,然后按照驱动表与其他表的字段关联顺序,依次join,从而决定了mysql会使用到哪些索引。这就是很影响性能的地方,因为此处可能会用到错误的索引,从ref字段中,我们可以看到下一个关联表使用了联合索引的哪些字段,也就是key_len为什么有的长有的短的意义。后关联的表会用到索引,ref到哪些字段。
所以如果字段关联的顺序发生变化,就会影响索引的选择。可能我们建立了联合索引,但是执行计划却用了单值索引,这是有驱动表和字段关联共同决定的。
SELECT xx,xx,xx
FROM
insu_xx_low t
LEFT JOIN ins_xx_ine td ON t.taskDefineId = td.taskId
LEFT JOIN insur_xx_stic s ON t.customerId = s.customerId
AND t.orgId = s.orgId
INNER JOIN ins_xx_tail cd ON cd.customerId = t.customerId
AND t.orgId = cd.orgId
LEFT JOIN sy_xx_ization o ON o.orgId = t.orgId
LEFT JOIN ins_xx_low isf ON isf.inShopId = t.inShopId
LEFT JOIN cc_xx_eport ctr ON ctr.licenseNo = t.licenseNo
JOIN sys_xx_org uo ON t.orgid = uo.orgid
WHERE
uo.userid = '227b700031db4d5caf0b80f6693d5eab'
AND t.txxime <= '2020-08-17 10:38:53' AND t.taxxxme >= '2020-07-01 00:00:00'
AND t.taxxtus = 0
AND td.txxxpe = 0
ORDER BY
isf.ixxxime DESC
LIMIT 20
JOIN sys_xx_org uo ON t.orgid = uo.orgid
改成JOIN sys_xx_org uo ON td.orgid = uo.orgid
效率得到大幅提升,因为关联的表发生变化,所以执行计划,表关联的顺序也发生了变化,从而选择了不同的索引