SQL性能优化的小技巧二

亦凉 2024-04-03 10:43 41阅读 0赞

九、提高group by语句的效率  
1、反例

先分组，再过滤

select job, avg（salary） from employee  
group by job  
having job ='develop' or job = 'test';  
1  
2  
3  
2、正例

先过滤，后分组

select job，avg（salary） from employee  
where job ='develop' or job = 'test'  
group by job;  
1  
2  
3  
3、理由

可以在执行到该语句前，把不需要的记录过滤掉

十、清空表时优先使用truncate  
truncate table在功能上与不带 where子句的 delete语句相同：二者均删除表中的全部行。但 truncate table比 delete速度快，且使用的系统和事务日志资源少。

delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。 truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 drop table语句。

对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带 where子句的 DELETE 语句。由于 truncate table不记录在日志中，所以它不能激活触发器。

truncate table不能用于参与了索引视图的表。

十一、操作delete或者update语句，加个limit或者循环分批次删除  
1、降低写错SQL的代价

清空表数据可不是小事情，一个手抖全没了，删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

2、SQL效率很可能更高

SQL中加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。

3、避免长事务

delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

4、数据量大的话，容易把CPU打满

如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。

5、锁表

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

十二、UNION操作符  
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。  
实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select username,tel from user  
union  
select departmentname from department  
1  
2  
3  
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。  
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。  
[尚学堂给同学们带来全新的Java300集课程啦!java零基础小白自学Java必备优质教程\_手把手图解学习Java，让学习成为一种享受\_哔哩哔哩\_bilibili][Java300_java_Java_Java_bilibili]

[Java300_java_Java_Java_bilibili]: https://www.bilibili.com/video/BV1qL411u7eE/?spm_id_from=333.337.search-card.all.click