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

mb6140060e201b0 2021-09-15 10:26:26
SQL 数据 字段 sql语句 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索引优化不得不提防的坑_字段

 

表的记录数为百万级别:

谈谈Mysql索引优化不得不提防的坑_sql语句_02

 

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

 

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

 

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

 

 SELECT * FROM `user` WHERE phone=12622717935

 

查询结果如下图:

谈谈Mysql索引优化不得不提防的坑_sql语句_03

 

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

 

EXPLAIN SELECT * FROM `user` WHERE phone=12622717935

 

结果输出如下:

谈谈Mysql索引优化不得不提防的坑_字段_04

 

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

 

其执行结果如下:

谈谈Mysql索引优化不得不提防的坑_字段_05

 

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

 

结果输出如下:

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

 

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

 

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

 

结果输出如下:

谈谈Mysql索引优化不得不提防的坑_字段_07

 

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

 

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

 

谈谈Mysql索引优化不得不提防的坑_sql语句_09

 

总结


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

 

版权声明
本文为[mb6140060e201b0]所创,转载请带上原文链接,感谢
https://blog.51cto.com/u_15367034/3890763

  1. 10天拿到字節跳動Java崗比特offer,Java知識點思維導圖
  2. 10 jours pour obtenir un Byte Jump Java post offer, Java Knowledge point Mind Map
  3. 10 jours pour obtenir l'octet Jump Java post offer, apprendre les étapes du développement Java
  4. Java version of gppc Reality Three: server side stream
  5. Linux Series: Dites - lui qu'il ne connaît pas kill du tout
  6. "Data structure and algorithm" of front end -- binary search
  7. 2020-2021京东Java面试真题解析,如何才能通过一线互联网公司面试
  8. 13 SpringBoot整合RocketMQ实现过滤消息-根据SQL表达式过滤消息
  9. 12 SpringBoot整合RocketMQ实现过滤消息-根据TAG方式过滤消息
  10. 11 SpringBoot整合RocketMQ实现事务消息
  11. 11 springboot Consolidated rocketmq Implementation transaction message
  12. 12 springboot Consolidated rocketmq Implements Filtering messages - Filtering messages according to tag method
  13. 13 springboot Consolidated rocketmq Implementation Filtering messages - Filtering messages according to SQL expressions
  14. linux系列之:告诉他,他根本不懂kill
  15. (1)java Spring Cloud+Spring boot企业快速开发架构之微服务是什么?它的优缺点有哪些?
  16. Oracle 检查 DATE 列 RANGE 分区表已有分区的最大日期时间
  17. ConcurrentHashMap--原理
  18. 2020 - 2021 JD Java interview Real question Analysis, How can interview through First - Line Internet Company
  19. Concurrenthashmap - - Principes
  20. Oracle vérifie l'heure de date maximale d'une partition existante dans la colonne date
  21. Docker Compose 实践及梳理
  22. Qu'est - ce qu'un microservice pour Java Spring Cloud + Spring Boot Enterprise Quick Development architecture?Quels sont ses avantages et ses inconvénients?
  23. Plus sign interview knowledge points in Java
  24. Pratique et organisation de la composition des dockers
  25. Linux Series: Dites - lui qu'il ne connaît pas kill du tout
  26. Convenient CSS and jQuery drop-down menu solution
  27. Linux analog packet loss rate
  28. Redis:我是如何与客户端进行通信的
  29. 15 useful cron work examples commonly used by Senior Linux system administrators
  30. 24个 JavaScript 循环遍历方法,你都知道吗?
  31. Reading notes of JavaScript advanced programming (3rd Edition) 4
  32. 30分钟学会Docker里面开启k8s(Kubernetes)登录仪表盘(图文讲解)
  33. 24 méthodes de traversée de boucle Javascript, vous savez?
  34. 30 minutes pour apprendre à ouvrir le tableau de bord k8s (kubernets) dans le docker (explication graphique)
  35. Redis: comment je communique avec les clients
  36. Wsl2: Windows native Linux subsystem
  37. 30分钟学会Docker里面开启k8s(Kubernetes)登录仪表盘(图文讲解)
  38. Docker Compose 实践及梳理
  39. Python高级用法总结(8)-函数式编程
  40. 261页前端面试题宝典,JavaScript变量声明提升
  41. The performance of JVM and Java applications of the same version differs by 30% on X86 and aarch64 platforms. Why?
  42. Page 261: dictionnaire des questions d'entrevue de première ligne, promotion de la Déclaration des variables JavaScript
  43. Python Advanced use Summary (8) - functional Programming
  44. Pratique et organisation de la composition des dockers
  45. 30 minutes pour apprendre à ouvrir le tableau de bord k8s (kubernets) dans le docker (explication graphique)
  46. [design pattern series] simple factory
  47. 2021Java最新大厂面试真题,阿里技术专家深入讲解
  48. Java equals and==
  49. Spring5(五)——AOP
  50. 海量列式非关系数据库HBase 原理深入
  51. java collections
  52. 海量列式非關系數據庫HBase 原理深入
  53. Base de données non relationnelle à grande échelle
  54. Printemps 5 (v) - AOP
  55. 2021 le dernier vrai problème d'entrevue d'usine de Java, expliqué en profondeur par les experts techniques d'Ali
  56. Java knowledge structure diagram and thinkinjava PDF file
  57. Do redis transactions satisfy atomicity?
  58. Java programmers must master 10 open source tools!
  59. [Cao Gong's essay] talk about the contract between Maven framework and plug-ins
  60. What books are better for learning Java