总结一点sql优化

 

优化背景

sql优化是程序员必备的技能之一，无论是在面试还是实际工作中都是不可或缺的。sql优化同时又是需要持续学习的一件事情，在工作中必然会遇到性能的瓶颈，比如服务器cpu持续90%多，网页加载速度超慢等等这些性能问题，首先想到的是进行sql优化，因为相比于其他优化方式来说，sql优化更直接，成本更小。

 

1）在查询中避免用到select * from

反例：

 select * from user where id=100;

在实际的业务场景中，我们可能只需要1、2列就可以，*是查询出所有的列，这样白白浪费了数据库资源，增加了服务器内存或者cpu的使用，此外查询出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间，还有比较重要的一点，select *不会走覆盖索引，会出现大量的回表操作，从而导致查询效率低下。

正例：

 select name,age from user where id=100;

 

2）用union all 代替 union

union all和union的区别，union all获取的所有的数据，union 获取的是去重后的数据。

反例：

 (select * from user where id=100) union (select * from user where id=200);

排重的过程需要便利、排序和比较，会更耗时，更消耗cpu资源。所以能使用union all 尽量不用 union

正例：

 (select * from user where id=100) union all(select * from user where id=200);

 

3）小表驱动大表

这里以 in 关键字和 exists 来举例说明

假如有两张表 user用户表共有10条数据 order订单表共有10000条数据，先查询所有用户有多少订单。我下面使用*只是为了举例。

in sql如下：

 select * from order where user_id in (select id from user)

exists sql 如下：

 select * from order where exists (select 1 from user where order.user_id=user.id)

此时的业务场景更合适用 in 关键字，因为如果sql语句中包含in关键字，则会优先执行in里面的子查询然后在执行in外面的语句，此时in 子查询数据量很小，查询速度会很快。

exists 关键字会优先执行左边的语句，然后作为条件去和右边的数据过滤。此时如果order 表小 user表大，那么用exists更合适。

综上所属，in使用于左边大表右边小表，exists适用于左边打标右边小表。不过其核心思想都是用小表驱动大表。

 

4）sql语句批量处理

如果现在有业务需要插入10个用户，此时你想怎么操作，是一条一条插入还是10条一起插入。我们都知道一条一条插入需要频繁访问数据库，而且每一次访问数据库，都会消耗性能，势必会产生影响。而且在代码中循环调用数据库一般是不允许的。此时就需要用到批量插入如下：

 insert into user (id,name,age) values(100,'001',30),(101,'002',31),(102,'003',32);
 -- 每次数据尽量控制在500以内，如果多余500则分多批次处理。

 

5）善用limit

如查询用户最早一条订单

 select id, create_time from order where user_id=100 order by create_time asc limit 1;

而不是按时间正序查询出所有，然后再取第一条数据。

 

6）连接查询代替子查询

子查询可以用 in 关键字实现，在表不多的情况下没有问题。mysql执行子查询时，需要创建临时表，使用完毕后再进行删除，这样肯定会造成一些性能消耗。

子查询：

 select * from orderwhere user_id in (select id from user)

可改成连接查询：

 select a.* from order ainner join user b on a.user_id = b.id

 

7）表中索引数量不宜过多，下面说其中3项

（1）索引过多会增加写入操作的开销

当在表中插入、更新或删除数据时，数据库需要维护索引的更新，维护开销就会变得非常大，导致写入操作变慢。

（2）索引过多会占用大量磁盘空间

在创建索引时，数据库会为每个索引分配磁盘空间，就会占用大量的磁盘空间，导致磁盘空间不足，影响数据库的正常运行。

（3）索引过多会降低查询效率

虽然索引能够提高查询效率，但是索引过多也会导致查询效率下降。因为查询时需要扫描所有的索引，而索引过多会导致扫描的时间变长，从而降低查询效率。

 

8）索引失效的几种情况

（1）使用！=或者not查询的时候，由于索引数据的检索效率非常低，索引会失效

（2）使用like通配符放在匹配字段前面的时候，不符合索引的最左匹配规则，索引会失效。放在右边可以。（并不是所有like查询都会失效，只有在查询时字段最左侧加%和左右侧都加%才会导致索引失效）

（3）使用or连接查询的时候，如果or连接的是同一个字段（比如 name=“张三” or name=“李四”），那么索引不会失效。如果连接的不是同一个字段（比如name="张三"or age=“20”）,索引会失效。

（4）类型不一致，如果字段类型是字符串，where 时一定用引号括起来，否则会因为隐式类型转换，索引失效

（5）运算符号导致的失效，如果在sql语句中对列（字段）进行了（+、-、*、/、！）,那么都将不会走索引

（6）联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。（如果查询的字段在组合索引中不是最左侧的字段，那么该组合索引是不会生效的。即左前缀原则）

（7）在索引列上使用 mysql 的内置函数，索引失效。

（8）is not null 对于索引字段设计不管允许为空或不许为null 都不走索引，is null 对于索引字段设计为可许为null时 走索引 ；如果字段设计不可null 则不走索引

 

学习了大佬文章进行的总结。