【MySQL-24】万字全面解析＜索引＞——【介绍＆语法＆性能分析＆使用规则】

前言

大家好吖，欢迎来到 YY 滴MySQL系列 ，热烈欢迎！ 本章主要内容面向接触过C++的老铁
主要内容含：

欢迎订阅 YY滴C++专栏！更多干货持续更新！以下是传送门！

• YY的《C++》专栏
• YY的《C++11》专栏
• YY的《Linux》专栏
• YY的《数据结构》专栏
• YY的《C语言基础》专栏
• YY的《单片机》专栏
• YY的《STM32》专栏
• YY的《数据库》专栏

一.索引概述

1.索引基本概念

• 索引(index)是帮助MvSOL 高效获取数据 的 数据结构(有序)

• 低效获取数据：例如全表扫描等
  

• 高效获取数据：二叉树等（示意图而已，不是真实索引结构）
  

2.索引的优缺点

优点：

1. 提高数据检索的效率 ，降低数据库的IO成本
2. 通过索引列对数据进行排序， 降低数据排序的成本 ，降低CPU的消耗

缺点：

1. 索引列也是要 占用空间的
2. 索引大大提高了查询效率，同时却也 降低更新表的速度 ，如对表进行INSERT、UPDATE、DELETE时，效率降低

二.索引结构＆分类

1.介绍＆不同索引在不同引擎中的适用情况

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:

1. B+Tree索引： 最常见 的索引类型，大部分引擎都支持 B+ 树索引
2. Hash索引：底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效, 不支持范围查询
3. R-tree(空间索引)：空间索引是MVISAM引擎的一个特殊索引类型，主要用于 地理空间数据类型 ，通常使用较少
4. Full-text(全文索引)：是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

不同索引在不同引擎中的适用情况：

2.Btree（多路平衡查找树）

【1】为什么不用二叉树的原因

• 特性：比节点小排左边，比节点大排右边
• 缺点： 顺序插入 时，会形成一个 链表，查询性能大大降低。大数据量情况下，层级较深
  检索速度慢
  

【2】Btree

3.B+tree

【1】B+tree

• 特点： 所有的元素都会出现在叶子节点 ，形成单向链表
• 机制： 非叶子节点充当 索引作用 ，即指针；叶子节点 存放数据

• MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了 带有顺序指针 的B+Tree，提高区间访问的性能。
• InnoDB引擎的逻辑存储结构，表空间->段->区->页->行； 页在InnoDB引擎中默认大小16k

【2】为什么InnoDB存储引擎选择使用B+tree索引结构?

• 相对于二叉树，层级更少 ，搜索效率高
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
• 相对Hash索引，B+tree支持 范围匹配 及 排序操作 ；

4.hash哈希

• 哈希索引就是采用 一定的hash算法 ，将键值换算成新的hash值， 映射到对应的槽位上，然后存储在hash表中 。
• 如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过 链表 来解决。

Hash索引特点：

1. Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，<，…)
2. 无法利用索引完成排序操作
3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+tree索引

存储引擎支持：

1. 在MySQL中，支持hash索引的是 Memory引擎
2. 而innoD8中具有 自适应hash功能 ，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

三.索引不同分类方式＆演示

索引常规分为4种，分别是：

1. 主键索引：针对于表中主键创建的索引，默认自动创建, 只能有一个 ， 关键字PRIMARY
2. 唯一索引：避免同一个表中某数据列中的值重复，可以有多个， 关键字UNIQUE
3. 常规索引：快速定位特定数据，可以有多个，无关键字
4. 全文索引：全文索引查找的是 文本中的关键词 ，而不是比较索引中的值，可以有多个， 关键字FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

1. 聚集索引(Clustered Index)：将 数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。 必须有，而且只有一个
2. 二级索引(SecondaryIndex)： 将数据与索引 分开 存储，索引结构的叶子节点关联的是对应的主键。 可以存在多个

聚集索引存储规则:

1. 如果存在主键，主键索引就是聚集索引 (聚集索引一定存在)
2. 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚集索引和二级索引演示：

四.索引的语法＆完成需求演示

1.索引的语法

1. 创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name ( index_col_name,...) ;

2. 查看索引

SHOW INDEX FROM table_name  （最后加上\G，可转换显示方式，由列展示变行展示）

3. 删除索引

DROP INDEX index_name ON table_name;

2.完成需求演示

按照下列的需求，完成索引的创建：

• 介绍表tb_user表：
  
• 需求
  
• 查看索引
  

-- 需求1：name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
create index idx_user_name on tb_user(name);

-- 需求2：phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
create unique index idx_user_phone on tb_user(phone);

-- 需求3：为profession、age、status创建联合索引。
create index idx_user_pro_age_sta on th_user(profession,age,status);

-- 需求4：为email建立合适的索引来提升查询效率。
create index idx user_email on tb_user(email);

-- 删除索引
drop index idx_user_email on tb_user;

五.索引性能分析工具——>为sql优化(select)做准备

1.查看SQL执行频次

【1】为什么要关注执行频率

• 有张表数据量大，但是只插入不查询，所以没必要优化；
• 通过查看增删改/查占数据库操作的比例， 来判断是否需要优化

【2】查看SQL执行频次语法＆演示

MySQL客户端连接成功后，通过show [sessionlglobal] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:

SHOW GLOBAL STATUS LIKE 'Com_______‘; --模糊匹配，7个_代表7个字符

我们要关注增删查改操作

2.慢查询日志

【1】慢查询日志机理机理＆作用＆语法

• 慢查询日志记录了所有执行时间超过 指定参数 (longquerytime，单位:秒， 默认10秒 )的所有SOL语句的日志——————> 要自定义参数可看下文【3.profile详情】
• 慢查询日志的作用：一段操作后，我们可以在其中定位到效率比较低的sql，从而进行优化
• MySQL的 慢查询日志默认没有开启 需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
• 配置完毕之后，通过以下指令重新启动MSQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。
  

【2】演示：查看慢查询日志开关是否开启

show variables like 'slow query log';

【2】演示：Linux中配置慢查询日志

• 登录linux，编辑mysql下的配置文件：/etc/my.cnf
  
• 大G跳到最后
• 输入

slow_query_log=1
long_query_time=2

• 重新连接后；再次查询，发现接口已开启
  

【3】演示：实时监测慢查询日志

• 我们开启两个窗口，第一个窗口输入如下代码查看慢查询日志实时情况

tail -f localhost-slow.1og

• 第二个窗口执行一条select语句，由于用时没有超过10s，所以在第一个窗口中没有显示
  
• 第二个窗口执行一条典型性能低select语句，用时超过10s，所以在第一个窗口中显示

3.profiles详情——（解决慢查询日志–自定义问题）

【1】慢查询日志机理机理＆作用＆语法

• 慢查询日志只记录超过10s的sql记录，想要自定义要通过profiles
• show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
• 通过have profiling参数，能够看到当前MySQL 是否支持 profile操作：

SELECT @@have_profiling ;

• 默认profiling是关闭的 ，可以通过set语句在session/global级别开启profiling
• 通过以下语句，能够看到当前profile操作 是否开启

SET profiling=1;

• 查看profiling状态

select @profiling;

• 执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时:

【2】演示：打开profiles开关前后对比

【3】演示：执行一系列的业务SQL的操作，然后通过profile查看指令的执行耗时/各个阶段耗时/CPU使用情况:

• 执行一系列sql语句
  
• 查询profiles
  
• 我们想知道最后一条语句，为何花了9s多，耗费在哪些地方，我们可以接着操作
  
  
• 加上cpu，可以查看指定query id的SQL语句CPU的使用情况
  

4.explain执行计划（最常用）

【1】explain执行计划＆语法

EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT语句执行过程中表如何连接和连接的顺序。

【2】explain执行计划演示

• 在select语句前加入explain或desc即可

【3】explain执行计划各个字段的含义

• ID：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下； id不同，值越大，越先执行 )
• select_type：表示 SELECT的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
• type：表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
• possible_key：显示可能应用在这张表上的索引，一个或多个
• Key：实际使用的索引，如果为NULL，则没有使用索引。
• Key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好
• rows：MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
• filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
  

六.索引使用规则

※.验证索引效率提升

• 在未建立索引之前，执行如下SOL语句，查看SQL的耗时
• 耗时11s
  
• 利用主键索引（id）查，耗时0s
  
• 利用第二个字段sn查，耗时21s，性能极低
  
• 针对字段sn创建索引，然后再次执行相同的SQL语句，再次查看SQL的耗时
• 从21s变成0.01s，性能大大提升

1.覆盖索引——查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到

• 尽量使用覆盖索引 (查询使用了素引，并且需要返回的列，在该索引中已经全部能够找到) ，减少select *
  

演示：

• 我们查看索引
  
• 紫色部分：上面比下面的效率好
  
• 多出的蓝色部分(返回的列)，不能够在在该索引中找到， 上部分的效率比下部分高
• 上部分：usingindexcondition:查找使用了索引，但是需要回表查询数据
• 下部分：using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

2.单列索引＆联合索引

• 单列索引：即一个索引只包含单个列。
• 联合家引：即一个索引包含了多个列。
• 在业务场景中，如果存在多个查询负件，考虑针对于查询字段建立索引时， 建议优先建立联合索引，而非单列引。
• 多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。 要强制就用可视日志。

演示：

• name和phone字段，都是单列索引，但只用到一个字段索引
  
• 我们给name和phone字段创建联合索引，MySQL优化器会评估哪个字段的索引效率更高。如果我们要主动设置SQL语句用的索引，涉及到下文我们提到的SQL提示
  

3.前缀索引——解决冗长字符串与索引问题

【1】前缀索引＆索引选择性的介绍

• 当字段类型为字符串(varchar，text等)时， 有时候需要索引很长的字符串 ，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。
• 此时可以只将字符串的 一部分前缀，建立索引 ，这样可以大大节约索引空间，从而提高索引效率。
  
• 如下图，计算可得 字段选择性是1
  
• 不断调整substring截取部分，可得到不同选择性
  

【2】前缀索引创建演示：

• 针对email字段截取字符串，建立前缀索引，降低索引体积
  
• 建立前五个字符构成的前缀索引
  
• 查询发现用到了创建的前五个字符构成的前缀索引
  

5.SQL提示——指定某个索引/忽略索引/强制索引

• SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
• 例如下图SQL执行计划，可能索引有两个，但是最终应用的索引只有一个，某些情况下我们就是要 指定用某个索引
  

演示：

• 有这样一张表，我们看下这段SQL的执行计划，索引是 复合索引idx_user_pro_age_sta
  
• 我们针对profession创建一个单列索引
  
• 我们想要用这个单列索引，我们就建议索引use XX——MySQL不一定接受，要强制用force XX
  

6.数据分布影响——MySQL自我评估

• 如果MySQL评估使用索引比全表 更慢 ，则不使用索引

演示：

• 有一张表，我们关注其phone字段
  
• 当我们进行不同的范围查询时，MySQL会自己选择用不用索引
• 例如绿色部分用了联合索引，而红色部分要查找的数目已经大于总数一半了，此时MySQL自己选择全表扫描
  

7.查询失效的几种情况

【1】违背——最左前缀法则(联合索引)

• 如果索引了多列(联合索引)，要遵守最左前缀法则。
• 最左前缀法则指的是查询从索引的最左列开始 （最左列存在即可），并且不跳过索引中的列，索引才不会失效
• 如果跳跃某一列 ，索引将部分失效 (后面的字段索引失效)

演示：

• 有如下表
  
• 查看索引，有一个age字段和status字段的联合索引idx_user_pro_age_sta
  
• 联合索引生效，索引长度为54
• 去掉status条件后，索引长度为49，因此可以判断status部分对应的索引长度为5
  
• 去掉status和age条件后，索引长度为47，因此可以判断age部分对应索引长度为2
  

索引失效：

• 索引的最左列不存在，key为null，不走索引，走全表扫描
  

• 去除掉age，也会走联合索引，但是长度只有47， 只有profession部分走索引 ，部分失效
  

• 注： 索引的最左列只要存在即可，顺序无所谓
  

【2】范围查询右侧失效

• 联合索引中，出现范围查询(>，<)， 查询范围右侧的列索引失效
• 实际中应该规避(>，<)，在业务允许的范围下使用（>=，<=），（>=，<=）不会失效

演示：

• 如下图，联合索引正常长度应该是54，图中是49，说明没有走status索引——status索引失效了
  
• 如下图，采用>=,索引长度为54，说明 >=的范围查询不会让右侧失效
  

【3】用or分割开的条件，or后面没索引，所有索引失效

• 如果or前的条件中的列有索引，而后面的列中没有索引，那么 涉及的索引都不会被用到
• 例如：age无索引，但是主键索引仍然不会生效
  

【4】索引列上进行运算操作，索引失效

• 不要在索引列上进行运算操作， 索引将失效 、
  

演示：

• 有这样一张表，我们关注phone字段
  
• phone是单列索引，发现用上了
  
• 我们想要找手机号最后两位是15的，利用substring函数运算截取，第十位开始，截2位
• 索引失效
  

【5】字符串类型字段使用时，不加引号，索引失效

• 字符串类型字段使用时，不加引号， 索引失效

演示：

• phone是varchar类型，不加单引号，也能查
• 但是由于存在隐式类型转换，索引会失效
  

【6】头部模糊匹配，索引失效

• 如果仅仅是 尾部模糊匹配，索引不会失效
• 如果是 头部模糊匹配，索引失效