敞开成长之旅！这是我参加「日新方案 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的履行频率:

「MySQL高级篇」MySQL索引进阶
有了这个，我们就能得到一个数据库的四种操作的频次，可是我存在的疑问便是：这是关于整个数据库的，而不是针对一个表的，可是关于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，而在慢查询日志中记载的数据为：

「MySQL高级篇」MySQL索引进阶

它记载了SQL句子的履行时刻、履行耗时、SQL句子。有了这些信息，我们就能针对性的对SQL句子进行优化。

⛳ Profile概况

经过上述的慢查询日志，我们能够得到一个定量，也便是说一切超越一切2s的SQL句子，可是它缺少了一个“比值”，也便是说，关于一些很简略的事务，它的履行时长却到达了1.98s，却逃过了慢查询日志，那么这时就需求用到我们的Profile概况

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

	show profiles

它提供给我们：时刻都去哪了。经过having_profiling参数，能够看到当前MySQL是否支撑profile操作：

「MySQL高级篇」MySQL索引进阶
经过profiling参数，我们能够得到Profiles是否敞开，上面是是否支撑Profiles：
「MySQL高级篇」MySQL索引进阶
0表明没有翻开Profile，1表明现已翻开。设置的办法为：

set profiling = 1;

上面便是Profiles的准备工作，那我们怎样去检查Profiles的概况呢，下面来介绍常用的三条指令：

  1. 首要是最简略的指令，查询一切的SQL句子以及它的查询时刻：

「MySQL高级篇」MySQL索引进阶

  1. 在知道某一条SQL句子的耗时之后，我们怎样知道它的耗时都消耗在哪些阶段了呢，此刻就能够经过一条新的指令检查指定Query_ID的SQL句子在各个阶段的耗时状况：

「MySQL高级篇」MySQL索引进阶

  1. 补充一条检查CPU运用状况的指令，在profile后加上cpu：

「MySQL高级篇」MySQL索引进阶

以上便是关于Profiles的运用，实际上，Porfile便是记载了一切查询SQL句子的耗时，以及各个阶段耗时和CPU占用率的表，有了Profiles，我们能定位出哪些SQL的履行功率低，便利我们后续做优化。

Explain履行方案

上面的判别都是依据时刻的层面去评判一条SQL句子的功能：履行时刻短就代表一条SQL句子的功能高，可实际上，这种断定办法很粗略，并不能真实去评判一条SQL句子的功能，要想真实评判一条SQL句子的功能，还需求第四种手法：Explain履行方案

经过Explain能够看到SQL句子的履行方案，履行进程傍边究竟是否用到了索引，表的衔接状况，表的衔接次序，都能够看到。那么我们怎样运用它呢？其实它的语法很简略，只需求在SELECT前加上它的关键字Explain / DESC即可：

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

「MySQL高级篇」MySQL索引进阶

经过测验，我们查询到了一些该SQL句子履行时的相关信息，下面我们就逐个来介绍一下这些字段的含义：

Id：查询序列号

Id：SELECT查询的序列号，一同它还表明查询中SELECT子句或许操作表的次序，也便是说：

关于不同巨细的Id，值越大，越早履行。SQL句子此处有三条履行记载，子查询的ID为2，它的ID值更大，更早履行，即履行的次序为：SUBQUERY ====> PRIMARY

「MySQL高级篇」MySQL索引进阶

关于相同巨细的Id，履行次序从上到下。SQL句子此处有三条记载，三条记载的ID都为1，他们履行的次序为从上到下，即履行次序为：vunue ===> venue_to_ordinary ===> ordinary_rule

「MySQL高级篇」MySQL索引进阶

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三个字段添加了联合索引：

「MySQL高级篇」MySQL索引进阶
我们分别去演示几种状况，去测验上述的联合索引，究竟在哪种状况下会索引失效，哪些状况下能正常运作：

  1. 以profession,age,status顺次作为判别的条件：

    「MySQL高级篇」MySQL索引进阶

    经过Explain能够看到它成功运用到了联合索引，索引长度为54

  2. 以profession,age顺次作为判别的条件：

    「MySQL高级篇」MySQL索引进阶

    我们发现，它也运用到了我们规划的联合索引，索引长度为49

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

    「MySQL高级篇」MySQL索引进阶

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

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

    「MySQL高级篇」MySQL索引进阶

    这儿我们能够发现：它并没有运用到索引，也便是说，当我们没有最左面的前缀，即profession的时分，一切的索引将会失效

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

    「MySQL高级篇」MySQL索引进阶

    这儿我们发现用到了索引，可是有一个小细节，索引的长度为47，与上面我们的成功的三种状况做比较，不难发现，此处虽然索引收效，可是只需profession这个索引收效，后续的索引失效，也便是上面提到的部分失效

经过上面的比如，我相信我们对最左前缀规律的了解更深了，可是呢，现在又有一个打破我们刚刚树立起的思维的比如：

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

依据前面树立起的体系，或许我们的榜首反应是，没有运用到索引，也便是全表查询。可是！！ 事实是它用到了三个索引，也便是说索引的长度为54，为什么呢？由于最左前缀规律与条件的次序无关，至于条件是否存在有关，此处profession存在，那么就能满足做最前缀规律。

「MySQL高级篇」MySQL索引进阶

规模查询

在上面测验最左前缀规律的时分，我们都是等值查询，也便是运用=，可是假如我们运用<,>,between这种规模查询的时分，成果是否又会有不一样呢？

我们运用profession、status做等值查询，age做规模查询，检查成果：

「MySQL高级篇」MySQL索引进阶

发现索引的长度为49，也便是说规模查询右边的索引，即status会失效。

处理方案：把>,<修改为>=,<=，当然是在事务答应的状况下，就能规避上述这种状况

❄ 索引失效

虽然索引的运用会给我们的查询进步不少功率，可是呢，假如我们不当的操作导致了索引失效，那么SQL履行的功率仍是会比较低，因而我们也需求去了解索引失效的状况，进而在事务答应的状况下，尽或许去防止索引失效。

状况一：索引列运算

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

EXPLAIN SELECT * from tb_user where phone = '17799990015'

「MySQL高级篇」MySQL索引进阶
这儿运用到了我们规划的索引idx_user_phone

EXPLAIN SELECT * FROM tb_user where substring(phone, 10, 2) = '15'

「MySQL高级篇」MySQL索引进阶
而在此处，我们对索引做substring计算后，发现索引失效了，天然查找的功能也会随之下降

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

字符串类型字段运用时，不加引号，索引将会失效。我们仍是以这个为比如：

EXPLAIN SELECT * from tb_user where phone = 17799990015

「MySQL高级篇」MySQL索引进阶
我们能够发现，或许用到的索引possible_key的确有，可是实际用到key的却没有，由于字符串不加单引号，存在隐式类型转换，我觉得能够了解为对索引列进行了运算，因而索引失效

状况三：含糊查询

假如仅仅是尾部含糊查询，索引是不会失效的。而假如是头部含糊匹配，索引失效。同样的我们以比如来协助我们了解：

EXPLAIN SELECT * FROM tb_user where profession like '软件%';

「MySQL高级篇」MySQL索引进阶
关于尾部含糊查询，索引并没有失效，仍然运用了idx_user_pro_age_stu索引

EXPLAIN SELECT * FROM tb_user where profession like '%软件';

「MySQL高级篇」MySQL索引进阶
关于头部含糊查询，索引失效。关于头部和尾部含糊查询，天然也是失效的！

状况四：or的不恰当运用

用or分割开的条件，假如or前的条件中的字段有索引，后边的字段没有索引，那么索引会失效。同样的，我们以两个比如来阐明：

在给age树立起索引之前的查询： EXPLAIN SELECT * FROM tb_user where phone = '17799990015' or age = 23;

「MySQL高级篇」MySQL索引进阶
我们能够看到，虽然phone有索引，可是age没有索引，最后的查询中也没有索引

在给age树立起索引之后的查询： EXPLAIN SELECT * FROM tb_user where phone = '17799990015' or age = 23;

「MySQL高级篇」MySQL索引进阶
在给age树立起索引后，or的两侧的字段都树立了索引，索引天然就收效了

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

状况五：MySQL评价

假如MySQL评价运用索引比全表更慢，则不运用索引。这儿其实归于我们的不可控规模了，由于评价是取决于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

「MySQL高级篇」MySQL索引进阶

「MySQL高级篇」MySQL索引进阶

现在有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束缚它，这会便利优化器去判别是否运用索引

友链

  • 「MySQL高档篇」MySQL存储引擎
  • 「MySQL高档篇」MySQL索引入门（本文上篇）
  • 本文的上篇的链接在这儿啦，我们能够去看看~

✒写在最后

都看到这儿啦~，给个点赞再走呗~，也欢迎各位大佬纠正，在谈论区一同沟通，共同进步！也欢迎加微信一同沟通：Goldfish7710。我们明天见~

「MySQL高级篇」MySQL索引进阶