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

叁歲伎倆 2022-05-31 07:09 110阅读 0赞

在之前的文章《[聊聊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优化之索引优化][Mysql]》。

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

表的记录数为百万级别：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

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

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

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

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

查询结果如下图：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

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

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

结果输出如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

在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'

其执行结果如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

我们看下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';

结果输出如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

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

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

结果输出如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

首先看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没法使用到索引，而第一句则可以。不信的话大家可以看下执行计划，在此就不再做分析了：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

总结

本文通过2个小案例讲解了Mysql索引优化中经常碰到的坑，并简要介绍了如何通过EXPLAIN语句去分析sql语句的执行计划，从而对症下药，进行合适的索引优化。当然关于Mysql索引优化可以讲的内容还有不少，后面会再进行深入探讨。

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;

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

表的记录数为百万级别：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1][]

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

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

查询结果如下图：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2][]

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

结果输出如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3][]

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

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

其执行结果如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4][]

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

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

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

结果输出如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5][]

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

结果输出如下：

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6][]

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

select \* from user where age =70 limit 1;

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

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

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7][]

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8][]

总结

[Mysql]: http://mp.weixin.qq.com/s?__biz=MzAxNjM2MTk0Ng==&mid=2247483683&idx=1&sn=5396902294e4e8af804fa19fa5566bef&chksm=9bf4b196ac833880f1f4a8896214a88a8abaf5970aecaa7a6634549c16fde1688305a8673ee7&scene=21#wechat_redirect
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220531/95201f34be524028b4d0029a1dc2fb39.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 1]: /images/20220531/ccc3accf49834bb192c7aea08607f690.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 2]: /images/20220531/cb0f9fe4843d4ca4ab56dec92d47be75.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 3]: /images/20220531/2753aca51e584967b79334669d265ad1.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 4]: /images/20220531/c72dd362110945a5b2c105fdebe4956b.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 5]: /images/20220531/67acd8f390144136a215cbd8d78ea56e.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 6]: /images/20220531/0500d3a6888c4025bd5b29754960ce41.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 7]: /images/20220531/598ed41bfb2446cb926f39764153edd6.png
[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1 8]: /images/20220531/a503cf0aea184f45b623a29dce4188ad.png