谈谈Mysql索引优化不得不提防的坑

前言

在之前的文章《聊聊Mysql优化之索引优化》中，笔者简单介绍了Mysql索引优化的原理和一些使用场景，然而Mysql索引优化的内容还远远不止这些。在实际工作中，我们有时候会碰到明明已经建了索引，但是查询速度还是上不去的问题，这时候就要当心了，有可能你的查询语句根本就没使用到索引，因为Mysql索引在某些情况下会失效，今天我将为大家介绍下Mysql索引优化中不得不提防的坑。

为了方便下文讲解，我们先建2张表：user表和address表（由于不同MySQL版本与执行引擎的优化方法不一样，所以本文所举的例子都是针对MySQL 5.7.18版本，InnoDB存储引擎而言的），建表语句如下：

CREATE TABLE `user` (

`id` varchar(255) NOT NULL ,

`phone` varchar(255) NULL ,

`age` int NULL ,

`name` varchar(255) NULL ,

PRIMARY KEY (`id`) ,

INDEX `normal_index_phone` (`phone`) USING BTREE ,

INDEX `normal_index_age` (`age`) USING BTREE

)

ENGINE=InnoDB

DEFAULT CHARACTER SET=utf8;

从上述SQL语句可知，user表一共有4个字段，id为varchar类型，最大长度为255，且为主键；phone为varchar类型，最大长度为255；name为varchar类型，最大长度为255；age为int类型。此外，我们在phone字段上建了一个普通的B-tree索引normal_index_phone；在age字段上建了一个普通的B-tree索引normal_index_age。关于B-tree索引的介绍，可以阅读文章《聊聊Mysql优化之索引优化》。

接下来我们往这张表插入一批数据，数据概要如下：

表的记录数为百万级别：

由于是实验目的，所以手机号跟姓名是随意填写的，此外id的特点是前缀“id”+序号，当然在实际工作中我们很少会这样设计id，之所以这样设计纯粹是为了试验目的，切勿生搬硬套，接下来开始我们的实验。

大家可能心里会想，这也太简单了吧，一个where语句就搞定了。没错，只要是有一点sql基础的人都能很容易写出这句sql。于是，有些同学很快就写出了以下sql：

查询结果如下图：

数据顺利查出来了，但是这样就完事了吗？我可以告诉你这样做虽然可以查询出数据，但是却不是最优的写法。大家可以回过头看看上文的建表语句，phone字段是varchar类型的，而上述我们写的sql语句的where条件是一个数字，并不是字符串，因为没有带上单引号。在这种情况下MySQL是不会使用索引去查询数据的。不信的话我们用EXPLAIN语句查询下该语句的执行计划。关于EXPLAIN语句的用法在下文会进行简要介绍，有兴趣的同学可以自己去深入了解下。接下来我们执行以下EXPLAIN语句：

结果输出如下：

在EXPLAIN输出中我们重点关注的是key字段、rows字段和type字段。key字段表示执行引擎最终选择使用的索引，该字段为空，说明该查询没有选择索引查询。再看rows字段，rows字段表示执行引擎预估要扫描的记录数，注意是预估的，并非绝对精确，这里可以看到扫描的行数非常多，接近全表行数了。再看type字段，其值为ALL，表示MySQL将进行全表扫描。

以上种种表明该查询并没有使用我们在phone字段上建的B-tree索引，那有没有办法既能查询出数据又能使用到索引呢？当然有，而且很简单，只要把上述查询语句稍微修改下就可以了：

修改后的sql语句与之前的sql语句相比，仅仅是在手机号前后多了个单引号而已。有同学可能会有疑问，这样就能使用到索引了吗？我们再EXPLAIN下就知道了：

其执行结果如下：

我们看下key字段，这个时候值为normal_index_phone，也就是phone字段上的B-tree索引，说明MySQL选中了normal_index_phone索引进行查询。再看rows字段，这个时候预估的扫描记录数变为1了，不再是之前的全表扫描了。

因此，对于字符串字段的查询，在查询条件中一定要使用单引号括起来，这是一个好习惯。

实验二：查询id序号为1000的用户信息

由于id字段具有一定的规则：前缀“id”+序号。因此，这里至少有2个方法可以查询出数据，一个方法是查询id为“id1000”的用户，另一个方法是利用字符串截取函数SUBSTR截取“id”字符串后面的整数，再查询该整数等于1000的用户。在实际工作中，相信绝大多数人都会使用第一种方式，这里因为实验需要才引入第二种查询方式，实际上很少人会用第二种方式去实现的。那么这两种实现方式有什么不同呢？我们EXPLAIN一下就知晓了。首先看下第一种方式，我们执行下列EXPLAIN语句：

EXPLAIN SELECT * FROM `user` WHERE id ='id1000';

结果输出如下：

首先看key字段，为PRIMARY，说明使用到了主键索引。再看rows字段，值为1，说明预估的扫描行数为1。执行计划看起来非常理想。接下来我们看下第二种实现方式，我们执行下列EXPLAIN语句：

EXPLAIN SELECT * FROM `user` WHERE SUBSTR(id,3) =1000;

结果输出如下：

首先看key字段为空，说明查询引擎没有使用到索引。再看rows字段，值非常大，已经接近总行数了。接着看type字段，值为ALL，说明使用了全表扫描。

观察两句sql的区别，无非就是第二句sql在索引列上使用了函数，导致查询引擎无法使用索引查询。因此，在索引列上进行条件查询时，一定要保证索引列是独立的，独立的意思是索引列不能使用函数，也不能是表达式的一部分。在索引列上使用函数就是上述第二种查询方式犯的错。至于说索引列不能是表达式的一部分，简单理解就是表达式不能参与列加减乘除等运算。比如查询年龄等于70的用户，有下列2种sql写法（第二种写法基本不会有人这样写，同样也是出于实验目的才列出来的）：

select * from user where age =70 limit 1;

select * from user where age+1 =71 limit 1;

第二句sql的索引列是表达式的一部分，因此第二句sql没法使用到索引，而第一句则可以。不信的话大家可以看下执行计划，在此就不再做分析了：

总结

本文通过2个小案例讲解了Mysql索引优化中经常碰到的坑，并简要介绍了如何通过EXPLAIN语句去分析sql语句的执行计划，从而对症下药，进行合适的索引优化。当然关于Mysql索引优化可以讲的内容还有不少，后面会再进行深入探讨。如果觉得这篇文章对你有帮助，可以点击左上角关注本公众号。顺便也预祝大家新年快乐！