整理一点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 则不走索引

学习了大佬文章进行的总结。
————————————————
 版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。                        
原文链接：https://blog.csdn.net/m0_62986746/article/details/139271963