敞开成长之旅！这是我参加「日新方案 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履行频率的时分，我们发现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：
set profiling = 1;
上面便是Profiles的准备工作，那我们怎样去检查Profiles的概况呢，下面来介绍常用的三条指令：
- 首要是最简略的指令，查询一切的SQL句子以及它的查询时刻：
- 在知道某一条SQL句子的耗时之后，我们怎样知道它的耗时都消耗在哪些阶段了呢，此刻就能够经过一条新的指令检查指定Query_ID的SQL句子在各个阶段的耗时状况：
- 补充一条检查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(最差)
而我们在做优化的时分要做到何种程度呢，我们无妨来了解一下各个功能呈现的状况：
- NULL：虽然NULL的功能在理论上讲是最好的，可是在实际的事务场景中，我们几乎不或许到达NULL，呈现NULL的状况，便是不拜访任何表。
- system：在拜访系统表的时分才会呈现system
- const：依据主键或许唯一索引进行拜访一般会呈现const
- ref：假如我们运用非唯一性的索引进行查询的时分就会呈现ref
- index：虽然运用了索引，可是也会对索引进行全表扫描
- 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三个字段添加了联合索引：
-
以
profession,age,status顺次作为判别的条件：
经过Explain能够看到它成功运用到了联合索引，索引长度为54
-
以
profession,age顺次作为判别的条件：
我们发现，它也运用到了我们规划的联合索引，索引长度为49
-
以
profession作为判别的条件：
同样的，它也运用到了我们规划的联合索引，索引长度为47，我们发现，这三种状况都运用到了我们规划的索引，索引正常工作并没有失效，可假如我们不按照次序进行查询呢，我们下面一同来看看：
-
以
age,status作为判别条件
这儿我们能够发现：它并没有运用到索引，也便是说，当我们没有最左面的前缀，即
profession的时分，一切的索引将会失效 -
以
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'。
- 首要拿到email的前五个字符
17799然后遍历辅佐索引的B+Tree来到叶子结点的17799 - 3，也便是叶子结点的榜首个结点。 - 然后拿到id = 3后回表查询来到集合索引，得到真实的数据，然后比对方针的
17799990@sina.com以及查出来的17799990@139.com，发现不一致 - 然后沿着链表持续遍历，找到id = 4回表查询比对后发现正确，即找到了一个方针值
- 然后持续沿着链表遍历，可是发现为19980，因而就此打道回府
7️⃣ 索引的规划准则
假如说在详细的事务中要对某一张表的某一个字段树立索引，进步SQL履行的功率，我应该针关于哪些表的哪些字段树立哪些索引呢？这儿就会给出七条建议：
- 针关于数据量较大，而且查询比较频繁的表树立索引
- 针关于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段树立索引
- 尽量选择区分度高的列作为索引，也便是说唯一性高（例如身份证号、手机号），区分度越高，索引的功率也就越高
- 假如是字符串类型的字段，而且字段的长度比较长，能够树立前缀索引
- 尽量运用联合索引，削减单列索引，查询时，联合索引能够运用掩盖索引，节省存储空间，防止回表查询
- 操控索引的数量，索引的优缺点我们在开端的时分就讲过，维护索引也是需求空间的，因而索引并不是多多益善
- 假如索引列不能存储NULL值，在创立的时分就最好运用NOT NULL束缚它，这会便利优化器去判别是否运用索引
友链
- 「MySQL高档篇」MySQL存储引擎
- 「MySQL高档篇」MySQL索引入门（本文上篇）
- 本文的上篇的链接在这儿啦，我们能够去看看~
✒写在最后
都看到这儿啦~，给个点赞再走呗~，也欢迎各位大佬纠正，在谈论区一同沟通，共同进步！也欢迎加微信一同沟通：Goldfish7710。我们明天见~
