SQL性能优化

一、查询SQL尽量不要使用select *，而是具体字段

理由

1. 节省资源、减少网络开销。
2. 可能用到覆盖索引，减少回表，提高查询效率。

二、避免在where子句中使用 or 来连接条件

正例

（1）使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000

（2）分开两条sql写

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000

理由

1. 使用or可能会使索引失效，从而全表扫描；
2. 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；
3. 也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；
4. 虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的；
    

三、尽量使用数值替代字符串类型

理由

1. 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
2. 而对于数字型而言只需要比较一次就够了；
3. 字符会降低查询和连接的性能，并会增加存储开销；

四、使用varchar代替char

理由

1. varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；
2. char按声明大小存储，不足补空格；
3. 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

五、where中使用默认值代替null

正例

SELECT * FROM user WHERE age>0

理由

1. 并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；
2. 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；
3. 其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；
4. 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点

六、避免在where子句中使用!=或<>操作符

理由

1. 使用!=和<>很可能会让索引失效
2. 应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
3. 实现业务优先，实在没办法，就只能使用，并不是不能使用

七、inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

• inner join 内连接，只保留两张表中完全匹配的结果集；
• left join会返回左表所有的行，即使在右表中没有匹配的记录；
• right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；
• 使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
• 这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

八、提高group by语句的效率

先过滤，后分组

理由

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

九、清空表时优先使用truncate

十、操作delete或者update语句，加个limit或者循环分批次删除

1、降低写错SQL的代价

2、SQL效率很可能更高

3、避免长事务

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

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

5、锁表

十一、批量插入性能提升

理由

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

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

表连接不宜太多

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

索引不宜太多

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

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

理由

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

十四、优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。

理由

• 首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；
• 左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；
• 全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。