首先从索引上下功夫
在sql前面加上“explain” ，查看性能如何
查看表中是否使用了索引
show index from tableName
添加索引
ALTER TABLE tableName ADD INDEX (表字段);
删除索引
DROP INDEX 索引名（key_name） ON tableName
从sql语句中优化
- 最好用left join, inner join或者exists。用IN查询速度慢
- 一直以来认为 exists 比 in 效率高的说法是不准确的。
- 如果查询的两个表大小相当，那么用in和exists差别不大。
- 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
inner join（left / right）
SELECT t.t_advert_id, IFNULL( sum( t.success_target_account ), 0 ) AS account_num, IFNULL( sum( t.success_target_group ), 0 ) AS group_num FROM t_task_detail t INNER JOIN t_device d ON d.device_id = t.device_id INNER JOIN r_device_device_tag tag ON tag.t_device_id = d.t_device_id WHERE 1 = 1 AND tag.t_device_tag_id = 9 GROUP BY t.t_advert_id
exists
SELECT t.t_advert_id, IFNULL( sum( t.success_target_account ), 0 ) AS account_num, IFNULL( sum( t.success_target_group ), 0 ) AS group_num FROM t_task_detail t WHERE 1 = 1 AND EXISTS (SELECT d.device_id FROM t_device_tag dt INNER JOIN r_device_device_tag ddt ON ddt.t_device_tag_id = dt.t_device_tag_id INNER JOIN t_device d ON d.t_device_id = ddt.t_device_id WHERE dt.t_device_tag_id =9 AND t.device_id = d.device_id ) GROUP BY t.t_advert_id
in
SELECT t.t_advert_id, IFNULL( sum( t.success_target_account ), 0 ) AS account_num, IFNULL( sum( t.success_target_group ), 0 ) AS group_num FROM t_task_detail t WHERE 1 = 1 AND t.device_id IN (SELECT d.device_id FROM t_device_tag dt INNER JOIN r_device_device_tag ddt ON ddt.t_device_tag_id = dt.t_device_tag_id INNER JOIN t_device d ON d.t_device_id = ddt.t_device_id WHERE dt.t_device_tag_id = 9 ) GROUP BY t.t_advert_id