SQL优化(一)

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
posted @ 2019-08-19 17:11  玄机  阅读(198)  评论(0编辑  收藏  举报