文章目录

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

一、性能优化策略

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

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

3.当只需要一条数据的时候，使用限制1
这是为了使解释中类型列达到常量类型。

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

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

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

7.根据不同情况使用在和中存在

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

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子句中对字段进行为空值判断

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

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子句中出现列字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定WHERE中的参数类型。

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

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

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

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

16.尽量使用内联接、避免左联接

参与联合查询的表至少为2张表，一般都存在大小之分.如果连接方式是INNER JOIN，在没有其他过滤条件的情况下MYSQL会自动选择小表作为驱动表，但是LEFT JOIN在驱动表的选择上遵循的是左边驱动右边的原则，即LEFT JOIN左边的表名为驱动表。

17.尽量避免全表扫描
对查询进行优化，应尽量避免全表扫描，首先应考虑在Where及Order by涉及的列上建立索引。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

//低效
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.解释SELECT查询用来跟踪查看效果
使用解释关键字可以让你知道MySql是如何处理你的语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈.

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

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

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 DIPLICATE KEY或者INSERT IGNORE；
(7)在Where子句中使用Union代替子查询，考虑持久连接，而不是多个连接，以减少开销；
(8)当负载增加在服务器上，使用show PROCESSLIST查看慢查询和有问题的查询，在镜像数据中测试所有可疑的查询。