前言

 

在之前的文章《聊聊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，之所以这样设计纯粹是为了试验目的，切勿生搬硬套，接下来开始我们的实验。

 

实验一：查询手机号为12622717935的用户信息

 

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

 

 SELECT * FROM `user` WHERE phone=12622717935

 

查询结果如下图：

 

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

 

EXPLAIN SELECT * FROM `user` WHERE phone=12622717935

 

结果输出如下：

 

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

 

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

 

SELECT * FROM `user` WHERE phone='12622717935'

 

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

 

EXPLAIN SELECT * FROM `user` WHERE phone='12622717935'

 

其执行结果如下：

 

我们看下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索引优化可以讲的内容还有不少，后面会再进行深入探讨。

如果觉得这篇文章对你有帮助，可以扫描下方二维码，关注本人公众号，获得更多优质文章推送。