「MySQL高级篇」MySQL索引进阶

派大金
1,473 阅读19分钟

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第三天，点击查看活动详情

大家好，我是Zhan，一名个人练习时长一年半的大二后台练习生，最近在学MySQL高级篇，欢迎各路大佬一起交流讨论

👉本篇速览

索引，好像从基础篇开始到后续项目中都会提到，留给我的印象就是，提高数据读取速度，但是索引底层具体怎么去实现的，又有哪些索引的种类，索引怎么去配置……而本篇将从以下七点，带你逐一攻破Ta：

  • 1️⃣首先第一个方面首先介绍一下：什么是索引，索引有什么作用以及索引的优缺点
  • 2️⃣接下来介绍一下索引的结构
  • 3️⃣然后就是索引的分类，索引有哪些种类
  • 4️⃣第四个方面就是索引的语法，如何创建、查询、删除索引
  • 5️⃣SQL性能分析的一些手段和工具
  • 6️⃣索引的使用原则
  • 7️⃣索引的设计原则 本篇为MySQL索引的下半篇，讲解索引的最后三点：SQL优化、索引的使用原则以及设计原则。如果有没有看上篇的可以先去看上篇，对索引有一个大概的了解： 「MySQL高级篇」MySQL索引入门

5️⃣ SQL性能分析工具

我们学习SQL性能分析的工具使用，目的是为了做SQL优化，做SQL优化之前我们需要定义出对于哪一类的SQL进行优化，同时也能看到优化的量变。

我们做SQL优化，优化的主要是DML，还是DDL语句呢？主要优化的是查询语句，在优化SQL查询语句这一块，索引占了主导地位，我们这里学习SQL性能分析的工具，就能为后续索引的使用和优化做准备工作。

🎁 SQL执行频率

如果存在一张数据表，数据量特别大，但是这张表只做插入，不做查询，那么其实我们对它做索引的优化没有必要，因此我们需要对SQL的执行频率判定：查询在SQL语句中执行的比重

那么想要知道SQL语句中执行的比重，我们可以借用MySQL提供给我们的命令：

-- 下面是七个下划线 --
SHOW global status like 'com_______';

得到四类SQL的执行频率: 有了这个，我们就能得到一个数据库的四种操作的频次，但是我存在的疑问就是：这是对于整个数据库的，而不是针对一个表的，但是对于SQL语句的优化是针对于单个表的，有大佬懂的可以评论区解答一下~

🎐 慢查询日志

如果在查询SQL执行频率的时候，我们发现SELECT语句的权重比较高，但是要针对数据库的哪些SELECT语句进行优化呢，此时，我们就需要借用MySQL的慢查询日志来定位执行效率比较低SQL语句，并对它进行优化。

慢查询日志记录了所有执行时间超过指定参数long_query_time（默认为10s） 的所有SQL语句的日志。默认情况下，MySQL的慢查询日志没有开启，需要在MySQL的配置文件中去打开：

-- 1.找到MySQL的配置文件 my.cnf --
-- 2.开启MySQL慢日志查询开关 --
show_query_log = 1
-- 3.设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视作为慢查询
long_query_time = 2

在配置好后，我们模拟一个超过2s的查询语句，去查询一个数据量为1000w的记录条数，实际操作的时间为13.350650s，而在慢查询日志中记录的数据为：

它记录了SQL语句的执行时间、执行耗时、SQL语句。有了这些信息，我们就能针对性的对SQL语句进行优化。

⛳ Profile详情

通过上述的慢查询日志，我们能够得到一个定量，也就是说所有超过所有2s的SQL语句，但是它缺少了一个“比值”，也就是说，对于一些很简单的业务，它的执行时长却达到了1.98s，却逃过了慢查询日志，那么这时就需要用到我们的Profile详情

在MySQL中，它提供给了我们一个指令：

	show profiles

它提供给我们：时间都去哪了。通过having_profiling参数，能够看到当前MySQL是否支持profile操作： 通过profiling参数，我们可以得到Profiles是否开启，上面是是否支持Profiles： 0表示没有打开Profile，1表示已经打开。设置的方式为：

set profiling = 1;

上面就是Profiles的准备工作，那我们如何去查看Profiles的详情呢，下面来介绍常用的三条命令：

  1. 首先是最简单的命令，查询所有的SQL语句以及它的查询时间：

  1. 在知道某一条SQL语句的耗时之后，我们如何知道它的耗时都耗费在哪些阶段了呢，此时就可以通过一条新的指令查看指定Query_ID的SQL语句在各个阶段的耗时情况：

  1. 补充一条查看CPU使用情况的命令，在profile后加上cpu：

以上就是对于Profiles的使用，实际上，Porfile就是记录了所有查询SQL语句的耗时，以及各个阶段耗时和CPU占用率的表，有了Profiles，我们能定位出哪些SQL的执行效率低，方便我们后续做优化。

🔔 Explain执行计划

上面的判断都是根据时间的层面去评判一条SQL语句的性能：执行时间短就代表一条SQL语句的性能高，可实际上，这种判定方法很粗略，并不能真正去评判一条SQL语句的性能，要想真正评判一条SQL语句的性能，还需要第四种手段：Explain执行计划

通过Explain可以看到SQL语句的执行计划，执行过程当中到底是否用到了索引，表的连接情况，表的连接顺序，都可以看到。那么我们怎么使用它呢？其实它的语法很简单，只需要在SELECT前加上它的关键字Explain / DESC即可：

	(EXPLAIN / DESC) SELECT 字段 FROM 表名 WHERE 条件；

经过尝试，我们查询到了一些该SQL语句执行时的相关信息，下面我们就逐一来介绍一下这些字段的含义：

🔴 Id：查询序列号

Id：SELECT查询的序列号，同时它还表示查询中SELECT子句或者操作表的顺序，也就是说：

对于不同大小的Id，值越大，越早执行。SQL语句此处有三条执行记录，子查询的ID为2，它的ID值更大，更早执行，即执行的顺序为：SUBQUERY ====> PRIMARY

对于相同大小的Id，执行顺序从上到下。SQL语句此处有三条记录，三条记录的ID都为1，他们执行的顺序为从上到下，即执行顺序为：vunue ===> venue_to_ordinary ===> ordinary_rule

🟠 select_type：查询类型

select_type：表示SELECT的类型，常见的取值有：

  • SIMPLE：简单表，即不使用表连接或者子查询
  • PRIMARY：主查询，即外层的查询，真正返回结果的那个SQL语句
  • UNION：UNION中的第二个或者后面的查询语句
  • SUBQUERY：子查询
  • …… 其实这个字段给我们的参考价值不大，因为只是给出了查询的类型，了解一下即可

🟡 type：连接类型

type：表示连接类型，它的性能由好到差的连接类型为：

NULL(最好) => system => const => eq_ref => ref => range => index => all(最差)

而我们在做优化的时候要做到何种程度呢，我们不妨来了解一下各个性能出现的情况：

  1. NULL：尽管NULL的性能在理论上讲是最好的，但是在实际的业务场景中，我们几乎不可能达到NULL，出现NULL的情况，就是不访问任何表。
  2. system：在访问系统表的时候才会出现system
  3. const：根据主键或者唯一索引进行访问一般会出现const
  4. ref：如果我们使用非唯一性的索引进行查询的时候就会出现ref
  5. index：尽管使用了索引，但是也会对索引进行全表扫描
  6. all：全表扫描的时候就会出现all

🟢 possible_key：可能用到的索引

type：表示可能应用在这张表上的索引，如果有多个，它也会展示多个

🔵 key：实际使用的索引

key：实际使用的索引，如果为NULL，表示没有使用索引

🟣 key_len：索引字段最大长度

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，而不是实际使用的长度，在不损失精确性的前提下，长度越短越好。

🟤 rows：执行查询的行数

rows：MySQL认为必须要执行查询的行数，在InnoDB引擎中，它只是一个预估值，因此并不准确。

⚫ filtered：返回结果行数栈读取行数的百分比

filtered：返回结果行数占读取行数的百分比，该值越大越好

  • 比如说我们根据主键去查，我们读取的行数就只读取了这一行数据，返回的结果刚好也是这一行数据，那么 filtered = 100%

💬 总结

在上面我们讲解了SQL语句性能分析的工具，主要介绍了四个工具：

  • 首先是SQL语句执行频次，但是呢由于它是对于整个表的，比较泛
  • 我们便引入了慢查询日志，有了慢查询日志我们便能找到所有超过我们设定的值的SQL语句，但是呢，对于那些简单但是效率低，也没有超过给定时间的SQL语句，我们不得而知
  • 因此我们又用到了Profiles，讲解了Profiles的开启、使用、分析，上面的两种方法都是根据时间定量的去判断效率，却没有一个标准
  • 这里我们就提到了MySQL提供给我们分析的工具Explain执行计划，有了它我们便能对SQL语句的执行计划、索引使用情况，表的连接情况 有了这四个手段，在我们分析SQL的性能时候就不至于晕头转向了，这样我们后面使用索引进行SQL优化的时候，就能快速的定位到那些效率比较低的SQL

6️⃣ 索引的使用原则

在前文中，我们已经了解到了索引的使用，以及索引对查询的优化，而索引在使用的时候要遵守那些原则呢？

🔅 最左前缀法则

最左前缀法则主要针对于联合索引，也就是索引了多个列。最左前缀法则是指，在查询的时候要从索引的最左边的列开始，并且不跳过索引中的列。

如果跳过了某一列，那么索引将部分失效，此处的部分是指后面的字段的索引。听起来可能不大能理解，我们来举几个例子：

现在我们给profession,age,status三个字段添加了联合索引： 我们分别去演示几种情况，去测试上述的联合索引，究竟在哪种情况下会索引失效，哪些情况下能正常运作：

  1. 以profession,age,status依次作为判断的条件：

    通过Explain可以看到它成功使用到了联合索引，索引长度为54

  2. 以profession,age依次作为判断的条件：

    我们发现，它也使用到了我们设计的联合索引，索引长度为49

  3. 以profession作为判断的条件：

    同样的，它也使用到了我们设计的联合索引，索引长度为47，我们发现，这三种情况都使用到了我们设计的索引，索引正常工作并没有失效，可如果我们不按照顺序进行查询呢，我们下面一起来看看：

  4. 以age,status作为判断条件

    这里我们可以发现：它并没有使用到索引，也就是说，当我们没有最左边的前缀，即profession的时候，所有的索引将会失效

  5. 以profession,status作为判断条件：

    这里我们发现用到了索引，但是有一个小细节，索引的长度为47，与上面我们的成功的三种情况做比较，不难发现，此处尽管索引生效，但是只有profession这个索引生效，后续的索引失效，也就是上面提到的部分失效

通过上面的例子，我相信大家对最左前缀法则的理解更深了，但是呢，现在又有一个打破大家刚刚建立起的思维的例子：

EXPLAIN SELECT * FROM tb_user where age = 31 and status = '0' and profession = '软件工程';

根据前面建立起的体系，可能大家的第一反应是，没有使用到索引，也就是全表查询。但是！！ 事实是它用到了三个索引，也就是说索引的长度为54，为什么呢？因为最左前缀法则与条件的顺序无关，至于条件是否存在有关，此处profession存在，那么就能满足做最前缀法则。

🚀 范围查询

在上面测试最左前缀法则的时候，我们都是等值查询，也就是使用=，但是如果我们使用<,>,between这种范围查询的时候，结果是否又会有不一样呢？

我们使用profession、status做等值查询，age做范围查询，查看结果：

发现索引的长度为49，也就是说范围查询右边的索引，即status会失效。

解决方案：把>,<修改为>=,<=，当然是在业务允许的情况下，就能规避上述这种情况

❄ 索引失效

尽管索引的使用会给我们的查询提高不少效率，但是呢，如果我们不当的操作导致了索引失效，那么SQL执行的效率还是会比较低，因此我们也需要去了解索引失效的情况，进而在业务允许的情况下，尽可能去避免索引失效。

🛴 情况一：索引列运算

当我们拿建立好索引的字段做预算后去查询时，索引会失效，我们以下面这个查询为例子：

EXPLAIN SELECT * from tb_user where phone = '17799990015' 这里使用到了我们设计的索引idx_user_phone

EXPLAIN SELECT * FROM tb_user where substring(phone, 10, 2) = '15' 而在此处，我们对索引做substring计算后，发现索引失效了，自然查找的性能也会随之降低

✈ 情况二：字符串不加引号

字符串类型字段使用时，不加引号，索引将会失效。我们还是以这个为例子：

EXPLAIN SELECT * from tb_user where phone = 17799990015 我们可以发现，可能用到的索引possible_key确实有，但是实际用到key的却没有，因为字符串不加单引号，存在隐式类型转换，我觉得可以理解为对索引列进行了运算，因此索引失效

🚀 情况三：模糊查询

如果仅仅是尾部模糊查询，索引是不会失效的。而如果是头部模糊匹配，索引失效。同样的我们以例子来帮助我们理解：

EXPLAIN SELECT * FROM tb_user where profession like '软件%'; 对于尾部模糊查询，索引并没有失效，仍然使用了idx_user_pro_age_stu索引

EXPLAIN SELECT * FROM tb_user where profession like '%软件'; 对于头部模糊查询，索引失效。对于头部和尾部模糊查询，自然也是失效的！

🛰 情况四：or的不恰当使用

用or分割开的条件，如果or前的条件中的字段有索引，后面的字段没有索引，那么索引会失效。同样的，我们以两个例子来说明：

在给age建立起索引之前的查询： EXPLAIN SELECT * FROM tb_user where phone = '17799990015' or age = 23; 我们可以看到，尽管phone有索引，但是age没有索引，最后的查询中也没有索引

在给age建立起索引之后的查询： EXPLAIN SELECT * FROM tb_user where phone = '17799990015' or age = 23; 在给age建立起索引后，or的两侧的字段都建立了索引，索引自然就生效了

其实我感觉or类似于一个短路或，只要有一个不满足，就不生效，只有二者都满足了，就生效。

🛳 情况五：MySQL评估

如果MySQL评估使用索引比全表更慢，则不使用索引。这里其实属于我们的不可控范围了，因为评估是取决于MySQL的，索引比全表更慢的情况就是可能，需要查询的数据占了一大半，原本可以全表解决的，但是要多走索引这张表反而效率降低。

例如这种情况，数据库中的profession全是空，因此在查询的时候，如果查询profession is null的，它就不会使用索引。而查询profession is not null的，就会使用索引。

🏝 SQL提示

如果对于一个字段，它既有一个聚集索引，就像那个profession一样，还有一个单独的索引，那么在使用的时候，它会使用哪种索引呢？这种不可控的因素又怎么解决呢？这里就用到我们的SQL提示：

SQL提示，是优化数据库的一个重要手段，它可以在SQL语句中加入一些人为的提示来达到优化的目的，比如说使用哪个索引，不使用哪个索引，以及强制使用哪个索引，命令如下：

-- 使用某个特定的索引 --
EXPLAIN SELECT * from tb_user use index (indexName) where profession = '软件工程';
-- 忽略某个特定的索引 --
EXPLAIN SELECT * from tb_user ignore index (indexName) where profession = '软件工程';
-- 强制使用某个索引 --
EXPLAIN SELECT * from tb_user force index (indexName) where profession = '软件工程';

🥂 覆盖索引

在查询中尽量使用覆盖索引，这里首先就得知道什么是覆盖索引：查询使用了索引，并且需要返回的列在索引中能全部找到，也就是说减少使用SELECT *

覆盖索引为什么效率更高呢？

如果我们需要查询的字段全部都是索引中的列，那么我们就可以直接拿索引中的数据返回，而不需要回表查询，这个在Explain中也有体现，出现在Extra这个字段中：

  • using index condtion：查找使用了索引，但是需要回表查询数据
  • using where;using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

🌼 前缀索引

当字段为字符串时，有时候需要索引很长的字符串，那么此时索引的长度就很大，导致查询时浪费大量的磁盘IO，降低效率。而前缀索引就能解决这个问题：

只把字符串的一部分前缀建立索引，这样就可以大大节约索引空间，从而提高索引效率。

前缀索引的创建语法：

CREATE index idx_xxx on table_name(colnum(n))

其实我们不难发现，前缀索引的创建方法和普通索引的创建方法的不同之处就在于colnum(n)的n，n表示的就是前缀长度，也就是说我们截取多少个字符作为索引

似乎，好像并不是很好判断究竟选取多少作为前缀的长度，我们不妨先给出一个指标：不重复的索引值和数据表的记录总数的比值，也就是说截取后的字符与总记录数的比值，这样就能体现出它索引的选择性，最好的情况是1，性能自然也是最好的。我们可以借助SQL语句来进行计算：

SELECT count(distinct email) / count(*) from tb_user;

SELECT count(distinct substring(email, 1, 5)) / count(*) from tb_user;

如果两列的数据的前缀一样，那是否会查询失败呢？

是否会查询失败，我们就需要了解前缀索引的查询流程：

首先是根据前缀索引构建出来的B+Tree

现在有SQL语句SELECT * from tb_user where email = '17799990@sina.com'。

  1. 首先拿到email的前五个字符17799然后遍历辅助索引的B+Tree来到叶子结点的17799 - 3，也就是叶子结点的第一个结点。
  2. 然后拿到id = 3后回表查询来到聚集索引，得到真正的数据，然后比对目标的17799990@sina.com以及查出来的17799990@139.com，发现不一致
  3. 然后沿着链表继续遍历，找到id = 4回表查询比对后发现正确，即找到了一个目标值
  4. 然后继续沿着链表遍历，但是发现为19980，因此就此打道回府

7️⃣ 索引的设计原则

如果说在具体的业务中要对某一张表的某一个字段建立索引，提高SQL执行的效率，我应该针对于哪些表的哪些字段建立哪些索引呢？这里就会给出七条建议：

  1. 针对于数据量较大，并且查询比较频繁的表建立索引
  2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
  3. 尽量选择区分度高的列作为索引，也就是说唯一性高（例如身份证号、手机号），区分度越高，索引的效率也就越高
  4. 如果是字符串类型的字段，并且字段的长度比较长，可以建立前缀索引
  5. 尽量使用联合索引，减少单列索引，查询时，联合索引可以使用覆盖索引，节省存储空间，避免回表查询
  6. 控制索引的数量，索引的优缺点我们在开始的时候就讲过，维护索引也是需要空间的，因此索引并不是多多益善
  7. 如果索引列不能存储NULL值，在创建的时候就最好使用NOT NULL约束它，这会方便优化器去判断是否使用索引

🍁 友链

✒写在最后

都看到这里啦~，给个点赞再走呗~，也欢迎各位大佬指正，在评论区一起交流，共同进步！也欢迎加微信一起交流：Goldfish7710。咱们明天见~

求赞.jpeg

avatar
文章
阅读
粉丝