SQL优化20技巧(建议收藏)

1、反例

先分组,再过滤

select job, avg(salary) from employee 
group by job
having job ='develop' or job = 'test';

2、正例

先过滤,后分组

select job,avg(salary) from employee 
where job ='develop' or job = 'test' 
group by job;

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;

这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。

十三、批量插入性能提升

1、多条提交

INSERT INTO user (id,username) VALUES(1,'软件测试部落');

INSERT INTO user (id,username) VALUES(2,'51ste');

2、批量提交

INSERT INTO user (id,username) VALUES(1,'软件测试部落'),(2,'51ste');

3、理由

默认新增SQL有事务控制,导致每条都需要事务开启和事务提交,而批量处理是一次事务开启和提交,效率提升明显,达到一定量级,效果显著,平时看不出来。

十四、表连接不宜太多,索引不宜太多,一般5个以内

1、表连接不宜太多,一般5个以内

  • 关联的表个数越多,编译的时间和开销也就越大;
  • 每次关联内存中都生成一个临时表;
  • 应该把连接表拆开成较小的几个执行,可读性更高;
  • 如果一定需要连接很多表才能得到数据,那么意味着这是个糟糕的设计了;
  • 阿里规范中,建议多表联查三张表以下。

2、索引不宜太多,一般5个以内

  • 索引并不是越多越好,虽其提高了查询的效率,但却会降低插入和更新的效率;
  • 索引可以理解为一个就是一张表,其可以存储数据,其数据就要占空间;
  • 索引表的数据是排序的,排序也是要花时间的;
  • insert或update时有可能会重建索引,如果数据量巨大,重建将进行记录的重新排序,所以建索引需要慎重考虑,视具体情况来定;
  • 一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否有存在的必要。

十五、避免在索引列上使用内置函数

1、反例

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

2、正例

SELECT * FROM user WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

3、理由

使用索引列上内置函数,索引失效。

十六、组合索引

排序时应按照组合索引中各列的顺序进行排序,即使索引中只有一个列是要排序的,否则排序性能会比较差。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java开发' order by deptid,position,createtime desc; 

实际上只是查询出符合 deptid= 1 and position = 'java开发'条件的记录并按createtime降序排序,但写成order by createtime desc性能较差。

十七、复合索引最左特性

1、创建复合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary);

2、满足复合索引的最左特性,哪怕只是部分,复合索引生效

SELECT * FROM employee WHERE NAME='软件测试部落';

3、没有出现左边的字段,则不满足最左特性,索引失效

SELECT * FROM employee WHERE salary=5000;

4、复合索引全使用,按左侧顺序出现 name,salary,索引生效

SELECT * FROM employee WHERE NAME='软件测试部落' AND salary=5000;

5、虽然违背了最左特性,但MySQL执行SQL时会进行优化,底层进行颠倒优化

SELECT * FROM employee WHERE salary=5000 AND NAME='软件测试部落';

6、理由

复合索引也称为联合索引,当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。



留言