分享一些sql优化,希望对你有所帮助:
1避免使用select *:
在实际业务场景中,可能我们真正需要使用的只是一两列,查了很多数据,但是不用,白白浪费资源。且select*不会走覆盖索引,会出现大量的回表操作,从而导致sql性能降低。
2使用union all 替代union:
union是获取去重后结果,union all是获取所有数据,包含重复数据。但是如果特殊业务场景,不允许出现重复数据的时候,可以使用union。
3批量操作
避免循环请求数据库
反例:
for(Order order: list){
orderMapper.insert(order);
}
insert into order(id,code,user_id) values(123,'001',100);
正例:
orderMapper.insertBatch(list);
insert into order(id,code,user_id) values(123,'001',100),(124,'002',100),(125,'003',101);
这样只需要远程请求一次数据库,sql性能会得到提升,数据量越多,提升越大。但是特大批量的操作数据库,使用此方式会导致数据库响应慢,所以建议数量控制在200-500之内,如果超过500则分批处理
4多用limit
在mybatisplus的selectOne方法中,如果查出来的数据是多个会报错。如果我们只需要查一条记录的话,但是根据我们的查询条件可以查出来多个数据的话,我们可以加一个limit 1
5大数量级的时候高效分页
当我们在大数量级里面分页查询的时候可能会出现下面这种情况
select id,name,age from user limit 1000000,20;
mysql会查到1000020条数据,然后丢弃前面的1000000条,只查后面的20条数据,这个是非常浪费资源的。
方案一:select id,name,age from user where id > 1000000 limit 20;
先找到上次分页最大的id,然后利用id上的索引查询。不过该方案,要求id是连续的,并且有序的。
方案二:select id,name,age from user where id between 1000000 and 1000020;
需要注意的是between要在唯一索引上分页,不然会出现每页大小不一致的问题。
6join的表不宜过多
根据阿里巴巴开发者手册的规定,join表的数量不应该超过3个。
如果join太多,mysql在选择索引的时候会非常复杂,很容易选错索引。并且如果没有命中中,nested loop join 就是分别从两个表读一行数据进行两两对比,复杂度是 n^2。但业务比较复杂,需要join十几张表才能查询出数据,这种情况就要按需考虑了。所以join表的数量要根据系统的实际情况决定,不能一概而论,尽量越少越好。
7控制索引数量
因为表中新增数据时,需要同时为它创建索引,而索引是需要额外的存储空间的,而且还会有一定的性能消耗。
阿里巴巴的开发者手册中规定,单表的索引数量应该尽量控制在5个以内,并且单个索引中的字段数不超过5个。
8选择合理的字段类型
能用数字类型,就不用字符串,因为字符的处理往往比数字要慢。
尽可能使用小的类型,比如:用bit存布尔值,用tinyint存枚举值等。
长度固定的字符串字段,用char类型。
长度可变的字符串字段,用varchar类型。
9索引优化
可以使用explain命令,查看mysql的执行计划
10牢记会引起索引失效点,避免索引失效
联合索引,不满足最左前缀原则
使用了select *
索引列上有计算函数
字符串类型没有加引号
使用了 is null 或 is not null 没有注意到字段是否允许为空
like 使用左%
使用or 如果某个字段没有索引会导致索引失效
字段名 <> 2 使用反向操作该列索引会失效