MySQL数据库系列(五)：MySQL之SQL优化十大口诀了解一下

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的。

总结

上面的所有说完了是不是要回去改代码啦😏！下面编一个口诀大家可以参考一下。

全值匹配是最优
最左前缀原则要注意
乱在索引列上操作惹祸害
范围查询记得放后面
不等于使用要小心
空和非空要注意
模糊查询通配符勿乱放
字符串查询加引号
OR变UNION效率会更高
记得覆盖索引尽量用哦

完！！！