SQL优化一

---

首先从索引上下功夫

在 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