谈谈Mysql索引优化不得不提防的坑-阿里云开发者社区

开发者社区> 不才黄某> 正文

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

简介: 前言   在之前的文章《聊聊Mysql优化之索引优化》中,笔者简单介绍了Mysql索引优化的原理和一些使用场景,然而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,之所以这样设计纯粹是为了试验目的,切勿生搬硬套,接下来开始我们的实验。

 

实验一:查询手机号为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索引优化可以讲的内容还有不少,后面会再进行深入探讨。

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

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL索引实战经验总结
MySQL索引对数据检索的性能至关重要,盲目的增加索引不仅不能带来性能的提升,反而会消耗更多的额外资源,本篇总结了一些MySQL索引实战经验。 索引是用于快速查找记录的一种数据结构。
1071 0
闲谈“如何优化SSH框架的项目”
使用struts框架的好处之一就是所有action类继承一个基类,将访问控制在基类中处理.2.所有的action类都继承自baseaction,一个资源对应一个action类.1.实现一个继承自struts的action的baseaction.
942 0
只需一步,DLA开启TableStore多元索引查询加速!
Data Lake Analytics(简称DLA)在构建第一天就是支持直接关联分析Table Store(简称OTS)里的数据,实现存储计算分离架构,满足用户基于SQL接口分析Table Store数据需求。
1348 0
聊聊Mysql索引和redis跳表
聊聊Mysql索引和redis跳表 摘要 面试时,交流有关mysql索引问题时,发现有些人能够涛涛不绝的说出B+树和B树,平衡二叉树的区别,却说不出B+树和hash索引的区别。这种一看就知道是死记硬背,没有理解索引的本质。
5185 0
MySQL索引性能测试
MySQL索引性能测试   blog文档结构图:   很长一段时间没学习MySQL了,一直致力于oracle的研究,最近得空了就再拾起MySQL看看吧,记得去年发布过的2篇MySQL文章:     MySQL 5.
816 0
+关注
10
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载