mysql通过减少联表行数来提升查询速度

今天发现一个视图查询有点慢，着手优化一下。

原始的SQL：

SELECT 
 `e`.`name` AS `姓名`,
 `r`.`name` AS `地区`,
 `dd1`.`name` AS `组别`,
 COUNT(DISTINCT IF( d.type = 1 AND d.`status` IN ( 1, 4 ), d.id, NULL )) AS `可使用有线设备`,
 COUNT(DISTINCT IF( d.type = 2 AND d.`status` IN ( 1, 4 ), d.id, NULL )) AS `可使用无线设备`,
 COUNT(DISTINCT IF( d.type = 1 AND d.`status` = 2 AND d.status_time >= (curdate() - INTERVAL 15 DAY), d.id, NULL )) AS `近15天使用有线设备`,
 COUNT(DISTINCT IF( d.type = 2 AND d.`status` = 2 AND d.status_time >= (curdate() - INTERVAL 15 DAY), d.id, NULL )) AS `近15天使用无线设备` 
FROM
 `engineer_region` AS `er`
 LEFT JOIN `engineer` AS `e` ON `e`.`id` = `er`.`engineer_id`
 LEFT JOIN `region` AS `r` ON  `r`.`zip_code` = substring_index(substring_index( `er`.`region_path`, '/', 3 ),'/',-1)
 LEFT JOIN `data_dict` AS `dd1` ON  `dd1`.`value` = `e`.`type` AND `dd1`.`group_name` = 'hw_engineer_type' 
 LEFT JOIN driver AS d ON d.engineer_id = er.engineer_id AND d.deleted = 0 
WHERE
 `e`.`id` IS NOT NULL 
 AND `er`.`deleted` = 0
GROUP BY
 `工程师姓名`,
 `地区`,
 `组别`

这段SQL主要用到了三个表，分别是：

engineer_region：AS er，存储工人负责的地市，通过engineer_id和工人信息表的ID关联

engineer：AS e，存储工人信息

driver：AS d，设备信息，通过 engineer_id和工人信息表的ID关联

现在查询需要1.5秒，先Explain一下：

主要是e表扫了全表(type = ALL)，并且没有用到索引(key=null)，并且用了临时表和文件排序(Using temporary; Using filesort) 。

一开始挺纳闷的，e表怎么就扫了全表而且还不用索引呢，尝试了force index来强制使用索引，没什么实际作用，然后发现where里有一个查询是e.id is not null，这个是为了防止er表有脏数据导致出现空行而存在的，将这个查询放到LEFT JOIN e表的地方可以解决不扫全表的问题：

LEFT JOIN `engineer` AS `e` ON `e`.`id` = `er`.`engineer_id` AND e.id IS NOT NULL

后来想想，如果怕是脏数据，应该提升程序的健全性以及清洗目前的脏数据才对，不要在SQL做这种事情了，于是就直接把原来W HERE里的 e.id IS NOT NULL給干掉了，现在EXPLAIN一下：

可以发现e表的 type=ALL变成了type=ref了并且也是用了索引，但是查询一下发现时间还是在1.5秒左右，没有什么明显变化。

再尝试分析一下，现在这三个表里，d表是最大的，这些联合查询最终会导致行数非常的多，在计数的时候又实用 DISTINCT 来去重，时间都消耗在去重上面了，但是不去重又不行，我们可以想办法减少联合d表查询出来的行数来提升查询速度，在select里用到了d表的状态是1,3,4还有状态时间是15天以内的，我们可以在LEFT JOIN的地方直接筛选掉，这样整个查询的行数就会变得非常的少，最终的SQL：

SELECT 
 `e`.`name` AS `工程师姓名`,
 `r`.`name` AS `地区`,
 `dd1`.`name` AS `组别`,
 COUNT(DISTINCT IF( d.type = 1 AND d.`status` IN ( 1, 4 ), d.id, NULL )) AS `可使用有线设备`,
 COUNT(DISTINCT IF( d.type = 2 AND d.`status` IN ( 1, 4 ), d.id, NULL )) AS `可使用无线设备`,
 COUNT(DISTINCT IF( d.type = 1 AND d.`status` = 2, d.id, null)) AS `近15天使用有线设备`,
 COUNT(DISTINCT IF( d.type = 2 AND d.`status` = 2, d.id, null)) AS `近15天使用无线设备` 
FROM
 `engineer_region` AS `er`
 LEFT JOIN `engineer` AS `e` ON `e`.`id` = `er`.`engineer_id` 
 LEFT JOIN `region` AS `r` ON  `r`.`zip_code` = substring_index(substring_index( `er`.`region_path`, '/', 3 ),'/',-1)
 LEFT JOIN `data_dict` AS `dd1` ON  `dd1`.`value` = `e`.`type` AND `dd1`.`group_name` = 'hw_engineer_type' 
 LEFT JOIN driver AS d ON d.engineer_id = e.id AND d.deleted = 0 and ( d.`status` in (1, 4) OR ( d.`status` = 2 and d.status_time >= (curdate() - INTERVAL 15 DAY)) )
WHERE
 `er`.`deleted` = 0
GROUP BY
 `工程师姓名`,
 `地区`,
 `组别`

现在查询的速度是0.3秒，有了相当大的提升了，EXPLAIN一下：

还是有临时表和文件排序的问题，临时表应该是聚组用到了，看了半天没看出文件排序是因为什么出来了，现在的查询速度可以接受了改天再优化这个问题吧，有大神看见了求指点。