SQL 性能优化总结

在这里插入图片描述

文章目录

一、性能优化策略
二、索引创建规则
三、查询优化总结

一、性能优化策略

1. SQL 语句中 IN 包含的值不应过多
MySQL 将 IN中的常量全部存储在一个排好序的数组里面，但是如果数值较多，产生的消耗也是比较大的。所以对于连续的数值，能用 between 就不要用 in。

2. SELECT 语句务必指明字段名称
SELECT * 增加很多不必要的消耗，所以要求直接在 select 后面接上字段名。

3. 当只需要一条数据的时候，使用 limit 1
这是为了使 EXPLAIN 中 type 列达到 const 类型。

4. 如果排序字段没有用到索引，就尽量少排序

5. 如果限制条件中其他字段没有索引，尽量少用 or
or 两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。

6. 尽量用 union all 代替 union
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算。当然，union all 使用的前提条件是两个结果集没有重复数据。

7. 根据不同情况使用 in 和 exists

如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况，EXISTS适合于外表小而内表大的情况。

select * from 表A where id in (select id from 表B)

//用exist改进
select * from 表A where exists(select * from 表B where 表B.id=表A.id)

8. 使用合理的分页方式以提高分页的效率

//随着表数据量的增加，直接使用limit分页查询会越来越慢
select id,name from table_name limit 866613, 20

//优化后的代码如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612
select id,name from table_name where id> 866612 limit 20

9. 分段查询
在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢，主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

10. 避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

11. 不建议使用%前缀模糊查询
例如 LIKE “%name” 或者 LIKE “%name%”，这种查询会导致索引失效而进行全表扫描，若要提高效率，可以考虑全文检索。

12. 避免在where子句中对字段进行表达式操作

//这会造成引擎放弃使用索引
select user_id,user_project from table_name where age*2=36;

//可以将上面查询语句改成这样，提高性能
select user_id,user_project from table_name where age=36/2;

13. 避免隐式类型转换

where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

14. 对于联合索引来说，要遵守最左前缀法则

在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面。

15. 对于联合查询要注意范围查询语句

如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。

16. 尽量使用 inner join，避免left join

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
在这里插入图片描述
17. 尽量避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

18. 尽量使用数字型字段
若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

19. 尽可能的使用 varchar 代替 char
首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

20. 尽量避免向客户端返回大数据量
若数据量过大，应该考虑相应需求是否合理。

21. 使用表的别名
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个 Colum上。这样一来，就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误，表名、列名在查询中以一个字母为别名，查询速
度要比建连接表快1.5倍。

22. 使用“临时表”暂存中间结果
将临时结果暂存在临时表，后面的查询就在 tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了阻塞，提高了并发性能。

23. 事先计算好结果
将需要查询的结果预先计算好放在表中，查询的时候再Select。

24. 不要有超过5个以上的表连接，少用子查询

25. IN 优化
在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。

26. 尽量将数据的处理工作放在服务器上，减少网络的开销

27. 合理调配线程数量
当服务器的内存够多时，配制线程数量 = 最大连接数 + 5，这样能发挥最大的效率；否则使用配制线程数量 < 最大连接数，并启用 SQL SERVER 的线程池来解决，如果还是数量 = 最大连接数 + 5，这会严重地损害服务器的性能。

28. 要注意索引的维护，周期性重建索引，重新编译存储过程

29. 批量插入或批量更新
当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新。

30. 尽量少使用循环
在所有的存储过程中，能够用SQL语句的，绝不会用循环去实现。

31. 选择最有效率的表名顺序
Oracle 的解析器按照从右到左的顺序处理 FROM 子句中的表名，FROM 子句中写在最后的表（基础表）将被最先处理，在 FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。

32. 提高GROUP BY语句的效率
可以通过将不需要的记录在GROUP BY之前过滤掉。

//低效
SELECT JOB , AVG(SAL) 
FROM EMP 
GROUP BY JOB 
HAVING JOB =’PRESIDENT’ 
OR JOB =’MANAGER’

//高效
SELECT JOB , AVG(SAL) 
FROM EMP 
WHERE JOB =’PRESIDENT’ 
OR JOB =’MANAGER’ 
GROUP BY JOB

33. SQL语句用大写
因为Oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。

34. 避免死锁
在一个事务中应尽可能减少涉及到的数据量，永远不要在事务中等待用户输入。

35. 最好不要使用触发器
触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程，如果能够使用约束实现的，就尽量不要使用触发器。

36. 应尽量减少空格的使用
在写 SQL 语句时，应尽量减少空格的使用，尤其是在 SQL 首和尾的空格，因为查询缓冲并不自动截取首尾空格。

37. 为数据库里的每张表都设置一个ID做为主键
而且最好是 INT类型的，并设置上自动增加的 AUTO_INCREMENT 标志。

38. MySQL 查询可以启用高速查询缓存
这是提高数据库性能的有效方法之一。当同一个查询被执行多次时，从缓存中提取数据比直接从数据库中返回数据要快的多。

39. EXPLAIN SELECT 查询用来跟踪查看效果
使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

40. 当只要一行数据时使用 LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，在这种情况下，加上 LIMIT 1 可以增加性能。这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

41. 优化表的数据类型
原则就是简单实用，因此在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。所有字段都得有默认值，尽量避免null。

42. 尽可能将操作移至等号右边
任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移
至等号右边。

二、索引创建规则

表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。
另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数
据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

（1）表的主键、外键必须有索引；
（2）数据量超过300的表应该有索引；
（3）经常与其他表进行连接的表，在连接字段上应该建立索引；
（4）经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
（5）索引应该建在选择性高的字段上；
（6）索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
（7）复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替；
（8）正确选择复合索引中的主列字段，一般是选择性较好的字段；
（9）如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
（10）如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
（11）如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
（12）频繁进行数据操作的表，不要建立太多的索引；
（13）删除无用的索引，避免对执行计划造成负面影响；
（14）尽量不要对数据库中某个含有大量重复的值的字段建立索引。

三、查询优化总结

（1）使用慢查询日志去发现慢查询，使用执行计划去判断查询是否正常运行，总是去测试你的查询看看是否他们运行在最佳状态下；
（2）避免在整个表上使用 count(*)，它可能锁住整张表；
（3）使查询保持一致，以便后续相似的查询可以使用查询缓存；
（4）在 WHERE、GROUP BY 和 ORDER BY 子句中使用有索引的列，保持索引简单，不在多个索引中包含同一个列；
（5）对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR；
（6）为了避免在更新前 SELECT，使用 INSERT ON DUPLICATE KEY 或者 INSERT IGNORE；
（7）在WHERE子句中使用 UNION 代替子查询，考虑持久连接，而不是多个连接，以减少开销；
（8）当负载增加在服务器上，使用 SHOW PROCESSLIST 查看慢查询和有问题的查询，在镜像数据中测试所有可疑的查询。