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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

不才黄某 2018-07-21 13:55:09 浏览682
展开阅读全文

前言

 

在之前的文章《聊聊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索引优化可以讲的内容还有不少,后面会再进行深入探讨。

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

 

网友评论

登录后评论
0/500
评论
不才黄某
+ 关注