查看原文
娱乐生活

SQL 优化的 N 种方法

SpringForAll 2022-09-05
关注我,回复关键字“spring”
免费领取Spring学习资料

作者:随风_d6a2
链接:https://www.jianshu.com/p/4c697c0e0367

1.SQL语句中IN包含的值不应过多:
例如:select id from t where num in(1,2,3) 对于连续的数值,能用between就不要用in了;实测速度差距不是很大.

2.SELECT语句务必指明字段名称:
禁止用 * 来查询 ,禁止用 * 来查询 ,禁止用 * 来查询 , 查找哪个字段,就写具体的字段.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

3.只查询一条数据的时候,使用limit 1
【这个很有用】

4.避免在where子句中对字段进行null值判断:
【实测:null值的判断依然走了索引】

explain select uid from user_test WHERE phone is null;

5.避免在where子句中对字段进行表达式操作:

select  uid from user_test  WHERE uid*10=40;
上面的sql对字段就行了算术运算,这会造成引擎放弃使用索引,建议改成:
select  uid from user_test  WHERE uid=40/10;

6.对于联合索引来说,要遵守最左前缀法则:
例如组合索引(id,name,sex) 使用的时候,可以id 或者id,name . 禁止直接name,或者sex.会导致联合索引失败

注意: id, name,sex 这三个字段填写顺序不会有影响, mysql会自动优化成最左匹配的顺序.

前三条sql都能命中索引,中间两条由于不符合最左匹配原则,索引失效.

最后一条sql 由于有最左索引id 所以索引部分成功,部分失效. id字段索引使用成功.

explain select * from  `user_test` where uid=10  ; 
explain select * from  `user_test` where uid=10 and name='张三'
explain select * from  `user_test` where uid=10 and name='张三' and phone='13527748096'

explain select * from  `user_test` where name='张三' and phone='13527748096'
explain select * from  `user_test` where name='张三'

explain select * from  `user_test` where uid=10 and phone='13527748096'

7.尽量使用inner join,避免left join:
如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。
【实测:不是很准确,具体用explain测试】

8.注意范围查询语句:
对于联合索引来说,如果存在范围查询,比如between、>、<等条件时,会造成后面的索引字段失效。

解决办法: 业务允许的情况下,使用 >= 或者<= 这样不影响索引的使用.

explain select * from `user_test` where uid=10 and name='张三' and phone='13527748096';
explain select * from `user_test` where uid between1 and 10and name ='张三' and phone='13527748096';

9.不建议使用%前缀模糊查询:
例如 : LIKE“%name”或者LIKE“%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

explain select * from `user_test` where uid=10 and uid like "%1" ;
explain select * from `user_test` where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 来连接条件,如果or连接的条件有一方没有索引,将导致引擎放弃使用索引而进行全表扫描
解决办法: 将or连接的双方都建立索引,就可以使用.

explain select * from `user_test` where uid=10 or name='张三';

11.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。(此处存在疑点,我本人测试的时候,发现索引还是能使用到)

explain select uid from  `user_test` where uid=1 and  substring(phone,1,3)='135'

explain select uid from  `user_test` where uid=1 and   abs(uid)=1;

12.字符串类型的字段 查询的时候如果不加引号'' ,会导致自动进行隐式转换,然后索引失效


END



IntelliJ 项目迁移到 Java 17, 2022.3 版本都需要 Java 17 才能启动!
HikariCP 数据库连接池,太快了
SpringBoot 日志的各种使用姿势,你真的用对了吗?
Chrome将禁用HTTP/2服务器推送(Server Push)支持
PostgreSQL 越来越流行!

关注后端面试那些事,回复【2022面经】

获取最新大厂Java面经


最后重要提示:高质量的技术交流群,限时免费开放,今年抱团最重要。想进群的,关注SpringForAll社区,回复关键词:加群,拉你进群。

点击“阅读原文”领取2022大厂面经
↓↓↓ 

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存