sql性能优化经验

一，Sql

1，优化limit分⻚
反例：
1 select id，name，age from employee limit 10000，10
正例：
1 //⽅案⼀ ：返回上次查询的最⼤记录(偏移量)select id，name from employee where
id>10000 limit 10.
2 //⽅案⼆：order by + 索引select id，name from employee order by id limi
t 10000，10
3 //⽅案三：在业务允许的情况下限制⻚数：
理由：
当偏移量最⼤的时候，查询效率就会越低，因为Mysql并⾮是跳过偏移量直接去取后⾯的数据，⽽是先
把偏移量+要取的条数，然后再把前⾯偏移量这⼀段的数据抛弃掉再返回的。
如果使⽤优化⽅案⼀，返回上次最⼤查询记录（偏移量），这样可以跳过偏移量，效率提升不少。
⽅案⼆使⽤order by+索引，也是可以提⾼查询效率的。
⽅案三的话，建议跟业务讨论，有没有必要查这么后的分⻚啦。因为绝⼤多数⽤户都不会往后翻太多
⻚。

2，使⽤where条件限定要查询的数据，避免返回多余的⾏
需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销。

3，尽量避免在索引列上使⽤mysql的内置函数
业务需求：查询最近七天内登陆过的⽤户(假设loginTime加了索引)

反例：
select userId,loginTime from loginuser where Date_ADD(loginTime,Inter
val 7 DAY) >=now();
正例：
explain select userId,loginTime from loginuser where loginTime >= D
ate_ADD(NOW(),INTERVAL - 7 DAY);

理由：
索引列上使⽤mysql的内置函数，索引失效
如果索引列不加内置函数，索引还是会⾛的。

4， 应尽量避免在where⼦句中对字段进⾏表达式操作，这将导致系统放弃
使⽤索引⽽进⾏全表扫

5 ，Inner join 、left join、right join，优先使⽤Inner join，如果是left
join，左边表结果尽量⼩
Inner join 内连接，在两张表进⾏连接查询时，只保留两张表中完全匹配的结果集
left join 在两张表进⾏连接查询时，会返回左表所有的⾏，即使在右表中没有匹配的记录。
right join 在两张表进⾏连接查询时，会返回右表所有的⾏，即使在左表中没有匹配的记录。

6,应尽量避免在where⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索
引⽽进⾏全表扫描。

7,使⽤联合索引时，注意索引列的顺序，⼀般遵循最左匹配原则

8，对查询进⾏优化，应考虑在where及order by涉及的列上建⽴索引，尽
量避免全表扫描。

9，如果插⼊数据过多，考虑批量插⼊。

10，在适当的时候，使⽤覆盖索引。
覆盖索引能够使得你的SQL语句不需要回表，仅仅访问索引就能够得到所有需要的数据，⼤⼤提⾼了查询
效率。

11，慎⽤distinct关键字
distinct 关键字⼀般⽤来过滤重复记录，以返回不重复的记录。在查询⼀个字段或者很少字段的情况下使
⽤时，给查询带来优化效果。但是在字段很多的时候使⽤，却会⼤⼤降低查询效率。

反例：
1 SELECT DISTINCT * from user;
正例：
1 select DISTINCT name from user;
理由：
带distinct的语句cpu时间和占⽤时间都⾼于不带distinct的语句。因为当查询很多字段时，如果使⽤
distinct，数据库引擎就会对数据进⾏⽐较，过滤掉重复数据，然⽽这个⽐较、过滤的过程会占⽤系统
资源，cpu时间。

12 ，删除冗余和重复索引
重复的索引需要维护，并且优化器在优化查询的时候也需要逐个地进⾏考虑，这会影响性能的。

13,如果数据量较⼤，优化你的修改/删除语句。
避免同时修改或删除过多数据，因为会造成cpu利⽤率过⾼，从⽽影响别⼈对数据库的访问。
⼀次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

14,where⼦句中考虑使⽤默认值代替null。
理由：
并不是说使⽤了is null 或者 is not null 就会不⾛索引了，这个跟mysql版本以及查询成本都有关。
如果mysql优化器发现，⾛索引⽐不⾛索引成本还要⾼，肯定会放弃索引，这些条件 ！=，>isnull，
isnotnull 经常被认为让索引失效，其实是因为⼀般情况下，查询的成本⾼，优化器⾃动放弃索引
的。
如果把null值，换成默认值，很多时候让⾛索引成为可能，同时，表达意思会相对清晰⼀点。

15 ,exist&in的合理利⽤
exists查询的理解就是，先执⾏主查询，获得数据后，再放到⼦查询中做条件验证，根据验证结果
（true或者false），来决定主查询的数据结果是否得意保留。
1 select * from A where deptId in (select deptId from B);
我们要选择最外层循环⼩的，也就是，如果B的数据量⼩于A，适合使⽤in，如果B的数据量⼤于A，
即适合选择exist。

16, 尽量⽤union all替换union
如果检索结果中不会有重复的记录，推荐union all 替换 union。
如果使⽤union，不管检索结果有没有重复，都会尝试进⾏合并，然后在输出最终结果前进⾏排序。如
果已知检索结果没有重复记录，使⽤union all 代替union，这样会提⾼效率。

17,尽量使⽤数字型字段，若只含数值信息的字段尽量不要设计为字符型

相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销。

18,索引不适合建在有⼤量重复数据的字段上，如性别这类型数据库字段。

19,尽量避免向客户端返回过多数据量。

20,当在SQL语句中连接多个表时,请使⽤表的别名，并把别名前缀于每⼀
列上，这样语义更加清晰。

21,尽可能使⽤varchar/nvarchar代替char/nchar。
理由：
因为⾸先变⻓字段存储空间⼩，可以节省存储空间。
其次对于查询来说，在⼀个相对较⼩的字段内搜索，效率更⾼。

22,为了提⾼group by语句的效率，可以在执⾏到该语句前，把不需要的
记录过滤掉。

23,如果字段类型是字符串，where时⼀定⽤引号括起来，否则索引失效
这是因为不加单引号时，是字符串跟数字的⽐较，它们 类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做⽐较。