SQL优化,心中永远的痛,同事写的各种复杂SQL,各种坑,有时候想拿出来优化一下,但是总感觉力不从心,不知从哪里下手。下面就一起聊聊SQL优化有哪些技巧。
优化前准备
首先需要建立一张表,这个表里面有联合索引,有普通索引。也是平时最常用了,看SQL:
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uname` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`pos` varchar(64) DEFAULT NULL,
`null_col` varchar(32) DEFAULT NULL,
`notnull_col` varchar(32) NOT NULL,
`utime` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name_age_pos` (`uname`,`age`,`pos`) USING BTREE,
KEY `idx_null_col` (`null_col`) USING BTREE,
KEY `idx_notnull_col` (`notnull_col`) USING BTREE,
KEY `idx_time` (`utime`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
采用的存储引擎是InnoDB,工作中百分之九十都是用这个,其他的存储引擎不做特殊说明。
其中uname、age、pos三列用的是联合索引,其他都是普通索引。表创建好了,那就填几条数据,这个大家随意,不提供具体的执行SQL喽!进入正题。
SQL优化
这里总结的SQL优化有十条规则,看似很多,其实的确很多,但这是让自己吃上一碗热饭的必备技能,咬咬牙背下来,然后在工作中多用用,也许并没有那么的难。
尽量全值匹配
表示在对索引列查询的时候尽量使用完整的值去匹配。
最佳左前缀原则
这个原则只对联合索引来说,在建立联合索引的时候,多列会有一个依次的顺序,在使用的时候需要注意,第一列需要用上,如果不用直接用后面的字段查询,索引会失效。
如上面的两个SQL,uname是第一列,当uname和age同时存在于where条件中的时候,索引是生效的。当只使用age列查询的时候,索引就失效,type对应的值是ALL,表示全表扫描。
不在索引列上做任何操作
在我自己接手的一个项目中就有这种情况,以前的开发小朋友特别喜欢在SQL里面做各种操作,而且SQL比较复杂,可读性也不强,最重要的是会导致索引失效。
如上面的SQL,uname是建立索引的,就因为使用了left函数去截取字符串,导致了索引的失效。不过还有救,使用覆盖索引,但是这仅仅能挽回一点点,此时的type是index,在type类型的排行里面,index只比ALL高一级。
范围条件放最后
这个也是对联合索引来说的,如果联合索引有多个,其中一个索引使用了范围查询,会导致后续的索引失效。
如图,uname的长度是32对应的字节长度是99,age对应的字节长度是5,pos对应的字节长度是195,总共是299。从上面的SQL执行计划可以看出来key_len的长度只有104,也就是只有uname和age的索引生效,而pos的索引失效。原因就是age使用了范围查询。在建立索引的时候,age是放在了pos的前面。优化的方法就是将age在索引里面的位置调到pos的后面。也就是将原来的KEY idx_name_age_pos (uname,age,pos) USING BTREE
修改成KEY idx_name_age_pos (uname,pos,age) USING BTREE
。
查询SQL不变,看执行计划结果如下图:
两个图很像,但是注意key_len列的值变化。
覆盖索引尽量用
覆盖索引上面已经用到了,是在做列上操作的时候说到。覆盖索引可以在很多情况下优化SQL,下面也会经常用到,这里不做具体的SQL演示。主要是要覆盖索引是怎么用的。
在查询的时候,条件使用到索引列,同时在select中需要查询的内容也包括该索引列,此时就可以形成覆盖索引。
这里可以说了一下,为什么在很多其他博客中或者前辈们都说,不要用select *
,而是用具体的字段名,这个时候就是想尽量的使用覆盖索引,可以避免大部分的索引失效问题。
不等于要慎用
将不等于使用在索引上面,会导致索引失效。但是如果业务中必须使用到,可以使用覆盖索引,提高SQL的性能。如下图:
null/not null要注意
这个就要分不同的情况来说了。
- 当索引列是可以为null的时候,使用is not null会导致索引失效,而is null不会失效
- 当索引列不可为null的时候,使用is not null会导致索引失效,因为所有的列都是不为null的,这个条件没有意义,还是会全表扫描。而is null是不起作用的,因为这一列不可能出现这种情况,对应的extra的值也给出了Impossible WHERE提示,type也给出了null,是性能最高的,因为压根就没有去查表。
对于上面索引失效的情况,可以用覆盖索引来解决,具体写法不多做阐述,参考之前的使用。
like查询要注意通配符
like使用的时候可能会导致索引失效。以前都听说like肯定会导致索引失效,这个说法其实是不正确的,只是可能而已。下面说一下不同情况中的体现。(like查询的时候可能使用到通配符分别是"_“和”%",例子中以"%"为例)
- 当通配符放在like条件值的前面时会导致索引失效
- 上面情况的反之就不会导致索引失效
对于上面出现的失效问题,可以使用覆盖索引来解决,到这里可以看出来覆盖索引为什么要尽量用了吧。
字符串记得加引号
有时写SQL的时候偷懒或者没有注意,在写字符串条件的时候没有写引号,这样也是会导致索引失效的。如图:
这种情况怎么解决呢,使用覆盖索引,是的这是可以的,但是不是最好的,最好的方式是把引号加上。如图:
但是这种情况发生的几率不高,现在都是用mybatis或者第三方的ORM框架,在通过占位符引入变量的时候,ORM都会在生成SQL的时候帮忙加上。但是还是要注意,有时候太过于依赖第三方框架,更容易出错。
OR换成UNION效率会更高
直接看下面的语句,第一条使用的是select *
,这样在使用OR的时候,type类型值是ALL,就会导致全表扫描。如果使用覆盖索引,就会将原来的ALL变成range,有效的提高了性能,如第三条语句,但是这并不是最好的。最好的是第二条语句,将OR换成UNION方式查询,在第二条执行计划中可以看的出来,执行的type类型都是ref,性能是高于range的。
总结
上面的所有说完了是不是要回去改代码啦😏!