程序员必知必会：MySQL上15个常见SQL优化技巧

无论你在面试，还是工作中，sql优化都是大家绕不过去的话题。为啥？随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，相对于改造代码，优化SQL语句是正本最低的，所以此时对SQL的优化更显得非常必要。 1.避免使用select * 实际业务场景中不需要所有的字段，只需要其中一两个，只查找用到 （1）浪费数据库资源，内存，cup （2）查出来的数据多，通过网络IO传输过程中也会增加传输时间 （3）select * 不会走覆盖索引，会出现回表 2.用union all 代替union （1）union会排重 （2）排重过程需要遍历，排序，比较，更消耗cpu资源 （3）确定唯一，没有重复数据能用union all尽量用 3.小表驱动大表 （1）in 的话里面驱动外面，in适合里子查询是小表 （2）exist 的话外面驱动里面，适合外面是小表 4.批量插入 （1）mybatis plus 的insertBatch （2）当然一次插入量也不能太大，可以分批插入。 5.多用limit 6.in中值太多 （1）查询出来数量太大，限制一次最大查询条数 （2）还可以，多线程查询，最后把查询出来的数据汇总。 7.增量查询 select name，age from user where id>#{lastId} limit 100; 查询比上次id 大的100条 8.高效的分页 select id,name,age from user limit 10000, 20; mysql会查询10020条，然后丢弃前面10000条，这个比较浪费资源 可以优化： select id,name,age from user id>10000 limit 20; 找到上次分页最大id 假如id是连续的，并且有序，可以用between 注意： between要在唯一索引上分页，不然会出现每页大小不一致问题。 9.用连接查询代替子查询 MySQL如果需要在两张以上表中查询数据的话，一般有两种实现方式 （1）子查询 （2）连接查询 select * from order where user_id in (select id from user where name='zhang');  子查询可以通过in实现，优点：这样简单， 但缺点是，MySQL执行子查询时，需要创建临时表，查询完成后再删除临时表，有一些额外开销。 可以改成连接查询: select o.* from order o inner join user u on o.user_id = u.id where u.name='zhang';  10.join表不宜过多 阿里巴巴开发者手册规定，join表不宜超过3个 如果join太多，MySQL在选择索引时会非常复杂，很容易选错索引。 并且没有命中，nested loop join 就会分别从两个表读一行数据进行对比，时间复杂度n^2。 11.join时需要注意 join用的最多的时left join 和 inner join （1）left join：两个表的交集和左表的剩余数据 （2）inner join：两个表的交集 inner join mysql会自动选择小表驱动， left join 左边的表驱动右边的表 12.控制索引数量 索引不是越多越好，索引需要额外的存储空间，B+树保存索引，额外的性能消耗。 阿里巴巴开发者手册中规定，单表索引数量尽量控制在5个以内，且单个索引字段数量控制在5个以内。 13.选择合理的字段类型 char：固定字符串类型，该类型在的字段在存储空间上是固定的，固定长度的可以用 varchar：变长字符串类型 （1）能用数字类型就不用字符串，字符串处理速度比数字类型慢 （2）尽量用小类型，比如：用bit存布尔值，用tinyint存枚举值等。 （3）长度固定字符串用char，不固定用varchar 14.提升group by效率 主要功能去重，分组 先过滤数据，减少数据，再分组 select id, name ,age from user group by id having id <50;  这种写法就不好， select id, name ,age from user where id <50 group by id;  15.索引优化 强制走哪个索引 force index select * from user  force index(索引)