【MySQL进阶】索引

索引机制

索引介绍

MySQL 官方对索引的定义为：索引（index）是帮助 MySQL 高效获取数据的一种数据结构，**本质是排好序的快速查找数据结构。**在表数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样
索引使用：一张数据表，用于保存数据；一个索引配置文件，用于保存索引；每个索引都指向了某一个数据
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据的物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
索引的优点：

• 类似于书籍的目录索引，提高数据检索的效率，降低数据库的 IO 成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗

索引的缺点：

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
• 虽然索引大大提高了查询效率，同时却也降低更新表的速度。对表进行 INSERT、UPDATE、DELETE 操作，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，还会调整因为更新所带来的键值变化后的索引信息，但是更新数据也需要先从数据库中获取，索引加快了获取速度，所以可以相互抵消一下。
• 索引会影响到 WHERE 的查询条件和排序 ORDER BY 两大功能

---

索引操作

索引在创建表的时候可以同时创建， 也可以随时增加新的索引

• 创建索引：如果一个表中有一列是主键，那么会默认为其创建主键索引（主键列不需要单独创建索引）

CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] ON 表名(列名...);
-- 索引类型默认是 B+TREE

• 查看索引SHOW INDEX FROM 表名;
• 添加索引

-- 单列索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名);

-- 组合索引
ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,...);

-- 主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(主键列名); 

-- 外键索引(添加外键约束，就是外键索引)
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

-- 唯一索引
ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

-- 全文索引(mysql只支持文本类型)
ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

• 删除索引DROP INDEX 索引名称 ON 表名;
• 案例练习数据准备：student

id	NAME	 age	score
1	张三		23		99
2	李四		24		95
3	王五		25		98
4	赵六		26		97

索引操作：

-- 为student表中姓名列创建一个普通索引
CREATE INDEX idx_name ON student(NAME);

-- 为student表中年龄列创建一个唯一索引
CREATE UNIQUE INDEX idx_age ON student(age);

最左前缀法则

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

指定索引

我们可以在查询的时候自己指定索引

1. use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进

行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2. ignore index ： 忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3. force index ： 强制使用索引

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

索引失效情况

1. 在索引列上进行运算操作。
2. 当索引是字符串类型时，字段使用时，不加引号，索引将失效。

explain select * from tb_user where phone = ‘17799990015’;
explain select * from tb_user where phone = 17799990015;//失效

3. 如果是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
4. 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会

被用到，必须两个都有索引，才会生效

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

索引分类

在MySQL索引一般的分类如下：

• 按功能分类
  • 主键索引：一种特殊的唯一索引，不允许有空值，一般在建表时同时创建主键索引，只能有一个主键索引。
  • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引（普通索引）
  • 联合索引：顾名思义，就是将单列索引进行组合
  • 唯一索引：索引列的值必须唯一，允许有空值，如果是联合索引，则列值组合必须唯一
    • NULL 值可以出现多次，因为两个 NULL 比较的结果既不相等，也不不等，结果仍然是未知
    • 可以声明不允许存储 NULL 值的非空唯一索引
  • 外键索引：只有 InnoDB 引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作
• 按结构分类

索引	InnoDB	MyISAM	Memory
BTREE	支持	支持	支持
HASH	不支持	不支持	支持
R-tree	不支持	支持	不支持
Full-text	5.6 版本之后支持	支持	不支持

联合索引图示：根据身高年龄建立的组合索引（height、age）

---

索引结构

数据页

文件系统的最小单元是块（block），一个块的大小是 4K，系统从磁盘读取数据到内存时是以磁盘块为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么
InnoDB 存储引擎中有页（Page）的概念，页是 MySQL 磁盘管理的最小单位

• InnoDB 存储引擎中默认每个页的大小为 16KB，索引中一个节点就是一个数据页，所以会一次性读取 16KB 的数据到内存
• InnoDB 引擎将若干个地址连接磁盘块，以此来达到页的大小 16KB
• 在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘 I/O 次数，提高查询效率.

超过 16KB 的一条记录，主键索引页只会存储部分数据和指向溢出页的指针，剩余数据都会分散存储在溢出页中
数据页物理结构，从上到下：

• File Header：上一页和下一页的指针、该页的类型（索引页、数据页、日志页等）、校验和、LSN（最近一次修改当前页面时的系统 lsn 值，事务持久性部分详解）等信息
• Page Header：记录状态信息
• Infimum + Supremum：当前页的最小记录和最大记录（头尾指针），Infimum 所在分组只有一条记录，Supremum 所在分组可以有 1 ~ 8 条记录，剩余的分组可以有 4 ~ 8 条记录
• User Records：存储数据的记录
• Free Space：尚未使用的存储空间
• Page Directory：分组的目录，可以通过目录快速定位（二分法）数据的分组
• File Trailer：检验和字段，在刷脏过程中，页首和页尾的校验和一致才能说明页面刷新成功，二者不同说明刷新期间发生了错误；LSN 字段，也是用来校验页面的完整性

数据页中包含数据行，数据的存储是基于数据行的，数据行有 next_record 属性指向下一个行数据，所以是可以遍历的，但是一组数据至多 8 个行，通过 Page Directory 先定位到组，然后遍历获取所需的数据行即可
数据行中有三个隐藏字段：trx_id、roll_pointer、row_id（在事务章节会详细介绍它们的作用）

---

二叉树

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

但是如果逐渐是顺序插入的，那么就会形成一个单向链表，检索效率退化

所以，如果选择二叉树作为索引结构，会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低。
• 大数据量情况下，层级较深，检索速度慢。
  

红黑树

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数
据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

• 大数据量情况下，层级较深，检索速度慢。

所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是
B+Tree呢？在详解B+Tree之前，先来介绍一个BTree。

BTree

BTree 的索引类型是基于 B+Tree 树型数据结构的，B+Tree 又是 BTree 数据结构的变种，用在数据库和操作系统中的文件系统，特点是能够保持数据稳定有序
BTree 又叫多路平衡搜索树，一颗 m 叉的 BTree 特性如下：

• 树中每个节点最多包含 m 个孩子
• 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)] 个孩子
• 若根节点不是叶子节点，则至少有两个孩子
• 所有的叶子节点都在同一层
• 每个非叶子节点由 n 个 key 与 n+1 个指针组成，其中 [ceil(m/2)-1] <= n <= m-1

5 叉，key 的数量 [ceil(m/2)-1] <= n <= m-1 为 2 <= n <=4 ，当 n>4 时中间节点分裂到父节点，两边节点分裂
插入 C N G A H E K Q M F W L T Z D P R X Y S 数据的工作流程：

1. 插入前 4 个字母 C N G A

2. 插入 H，n>4，中间元素 G 字母向上分裂到新的节
3. 插入 E、K、Q 不需要分裂

4. 插入 M，中间元素 M 字母向上分裂到父节点 G
5. 插入 F，W，L，T 不需要分裂
6. 插入 Z，中间元素 T 向上分裂到父节点中
7. 插入 D，中间元素 D 向上分裂到父节点中，然后插入 P，R，X，Y 不需要分裂
8. 最后插入 S，NPQR 节点 n>5，中间节点 Q 向上分裂，但分裂后父节点 DGMT 的 n>5，中间节点 M 向上分裂

BTree 树就已经构建完成了，BTree 树和二叉树相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTree 的层级结构比二叉树少，所以搜索速度快。
BTree 结构的数据可以让系统高效的找到数据所在的磁盘块，定义一条记录为一个二元组 [key, data] ，key 为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key 值互不相同，BTree 中的每个节点根据实际情况可以包含大量的关键字信息和分支
缺点：当进行范围查找时会出现回旋查找
在 B 树中进行范围查找时，通常需要遍历节点来确定范围的起始位置和结束位置。如果范围的起始位置不在当前节点中，那么就需要回溯到父节点并向下搜索，直到找到起始位置所在的叶子节点。然后，从起始位置开始遍历叶子节点，直到找到范围的结束位置或到达范围的末尾。

---

B+Tree

数据结构

BTree 数据结构中每个节点中不仅包含数据的 key 值，还有 data 值。磁盘中每一页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小，当存储的数据量很大时同样会导致 B-Tree 的深度较大，增大查询时的磁盘 I/O 次数，进而影响查询效率，所以引入 B+Tree。
B+Tree 为 BTree 的变种，B+Tree 与 BTree 的区别为：

• n 叉 B+Tree 最多含有 n 个 key（哈希值），而 BTree 最多含有 n-1 个 key
• 所有非叶子节点只存储键值 key 信息，只进行数据索引，使每个非叶子节点所能保存的关键字大大增加
• 所有数据都存储在叶子节点，所以每次数据查询的次数都一样
• 叶子节点按照 key 大小顺序排列，左边结尾数据都会保存右边节点开始数据的指针，形成一个链表
• 所有节点中的 key 在叶子节点中也存在（比如 16)，key 允许重复，B 树不同节点不存在重复的 key

B* 树：是 B+ 树的变体，在 B+ 树的非根和非叶子结点再增加指向兄弟的指针

---

优化结构

MySQL 索引数据结构对经典的 B+Tree 进行了优化，在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能，防止回旋查找
区间访问的意思是比如访问索引为 6 - 18 的数据，可以直接根据相邻节点的指针遍历，不需要回溯到父节点进行遍历。
B+ 树的叶子节点是数据页（page），一个页里面可以存多个数据行
通常在 B+Tree 上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。可以对 B+Tree 进行两种查找运算：

• 有范围：对于主键的范围查找和分页查找
• 有顺序：从根节点开始，进行随机查找，顺序查找

InnoDB 中每个数据页的大小默认是 16KB，

• 索引行：一般表的主键类型为 INT（4 字节）或 BIGINT（8 字节），指针大小在 InnoDB 中设置为 6 字节节，也就是说一个页大概存储 16KB/(8B+6B)=1K 个键值（估值）。则一个深度为 3 的 B+Tree 索引可以维护 10^3 * 10^3 * 10^3 = 10亿 条记录
• 数据行：一行数据的大小可能是 1k，一个数据页可以存储 16 行

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2-4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作
B+Tree 优点：提高查询速度，减少磁盘的 IO 次数，树形结构较小。

---

索引维护

B+ 树为了保持索引的有序性，在插入新值的时候需要做相应的维护
每个索引中每个块存储在磁盘页中，可能会出现以下两种情况：

• 如果所在的数据页已经满了，这时候需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂，原本放在一个页的数据现在分到两个页中，降低了空间利用率
• 当相邻两个页由于删除了数据，利用率很低之后，会将数据页做页合并，合并的过程可以认为是分裂过程的逆过程
• 这两个情况都是由 B+ 树的结构决定的

一般选用数据小的字段做索引，字段长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小
自增主键的插入数据模式，可以让主键索引尽量地保持递增顺序插入，不涉及到挪动其他记录，避免了页分裂，页分裂的目的就是保证后一个数据页中的所有行主键值比前一个数据页中主键值大
参考文章：https://developer.aliyun.com/article/919861

Hash

MySQL中除了支持B+Tree索引，还支持一种索引类型—Hash索引。

• 哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在

hash表中

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可
以通过链表来解决。

特点

• Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…),hash值是无序的
• 无法利用索引完成排序操作。
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

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

为什么InnoDB存储引擎选择使用B+tree索引结构？

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

聚簇索引

索引对比

聚簇索引是一种数据存储方式，并不是一种单独的索引类型，与之对应的还有非聚簇索引

• 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引
• 非聚簇索引的叶子节点存放的是主键值或指向数据行的指针（由存储引擎决定）

在 Innodb 下主键索引是聚簇索引，在 MyISAM 下主键索引是非聚簇索引

---

Innodb

聚簇索引

在 Innodb 存储引擎，B+ 树索引可以分为聚簇索引（也称聚集索引、clustered index）和辅助索引（也称非聚簇索引或二级索引、secondary index、non-clustered index）
InnoDB 中，聚簇索引是按照每张表的主键构造一颗 B+ 树，叶子节点中存放的就是**整张表的数据，**将聚簇索引的叶子节点称为数据页

• 这个特性决定了数据也是索引的一部分，所以一张表只能有一个聚簇索引
• 辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引

聚簇索引的优点：

• 数据访问更快，聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

聚簇索引的缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序（递增）插入是最快的方式，否则将会出现页分裂，严重影响性能，所以对于 InnoDB 表，一般都会定义一个自增的 ID 列为主键
• 更新主键的代价很高，将会导致被更新的行移动，所以对于 InnoDB 表，一般定义主键为不可更新
• 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

---

非聚簇索引

在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引等
辅助索引叶子节点**存储的是主键值，**而不是数据的物理地址，所以访问数据需要二次查找，推荐使用覆盖索引，可以减少回表查询
回表查询: 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取
数据的方式，就称之为回表查询。

---

索引实现

InnoDB 使用 B+Tree 作为索引结构，并且 InnoDB 一定有索引

主键索引：

• 在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，这个索引的 key 是数据表的主键，叶子节点 data 域保存了完整的数据记录
• InnoDB 的表数据文件通过主键聚集数据，如果没有定义主键，会选择非空唯一索引代替，如果也没有这样的列，MySQL 会自动为 InnoDB 表生成一个隐含字段 row_id 作为主键，这个字段长度为 6 个字节，类型为长整形

辅助索引：

• InnoDB 的所有辅助索引（二级索引）都引用主键作为 data 域
• InnoDB 表是基于聚簇索引建立的，因此 InnoDB 的索引能提供一种非常快速的主键查找性能。不过辅助索引也会包含主键列，所以不建议使用过长的字段作为主键，过长的主索引会令辅助索引变得过大
  

查询流程

假设某张表的索引结构如下图所示

• 聚集索引的叶子节点下挂的是这一行的数据 。
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。
select *from user where name='Arm'

具体过程如下:
①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查
找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最
终找到10对应的行row。
③. 最终拿到这一行的数据，直接返回即可。

MyISAM

非聚簇

MyISAM 的主键索引使用的是非聚簇索引，索引文件和数据文件是分离的，**索引文件仅保存数据的地址**

• 主键索引 B+ 树的节点存储了主键，辅助键索引 B+ 树存储了辅助键，表数据存储在独立的地方，这两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别
• 由于索引树是独立的，通过辅助索引检索无需回表查询访问主键的索引树

---

索引实现

MyISAM 的索引方式也叫做非聚集的，之所以这么称呼是为了与 InnoDB 的聚集索引区分
主键索引：MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址
辅助索引：MyISAM 中主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 可以重复

---

设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率
创建索引时的原则：

• 对查询频次较高，且数据量比较大的表建立索引
• 使用唯一索引，区分度越高，使用索引的效率越高
• 索引字段的选择，最佳候选列应当从 where 子句的条件中提取，使用覆盖索引
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价越高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低 DML 操作的效率，增加相应操作的时间消耗；另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但提高了选择的代价
• MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配N 个列组合而成的组合索引，相当于创建了 N 个索引，如果查询时 where 句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率

-- 对name、address、phone列建一个联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
-- 查询语句执行时会依照最左前缀匹配原则，检索时分别会使用索引进行数据匹配。
(name,address,phone)
(name,address)
(name,phone)	-- 只有name字段走了索引
(name)

-- 索引的字段可以是任意顺序的，优化器会帮助我们调整顺序，下面的SQL语句可以命中索引
SELECT * FROM user WHERE address = '北京' AND phone = '12345' AND name = '张三';

-- 如果联合索引中最左边的列不包含在条件查询中，SQL语句就不会命中索引，比如：
SELECT * FROM user WHERE address = '北京' AND phone = '12345';

哪些情况不要建立索引：

• 记录太少的表
• 经常增删改的表
• 频繁更新的字段不适合创建索引
• where 条件里用不到的字段不创建索引

---

索引优化

SQL性能分析

索引优化主要是针对查询语句，要进行索引优化，就需要先知道某条Sql语句的性能。

1. SQL语句执行频率

通过 show [session|global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。
但是我们如何定位哪些查询语句需要优化呢，需要用到慢查询日志。

2. 慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有
SQL语句的日志。
默认是关闭的，要开启慢查询日志，需要在MySQL的配置文件配置如下信息。

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。

3. profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

# MySQL是否支持profile操作
SELECT @@have_profiling ;
# 开启
SET profiling = 1;

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

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

4. explain

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

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

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 的值越大越好。
Extra	Using where; Using Index： 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需 要回表查询数据 Using index condition ：查找使用了索引，但是需要回表查询数据

覆盖索引

覆盖索引：包含所有满足查询需要的数据的索引（SELECT 后面的字段刚好是索引字段），可以利用该索引返回 SELECT 列表的字段，而不必根据索引去聚簇索引上读取数据文件（不需要回表查询）
回表查询：要查找的字段不在非主键索引树上时，需要通过叶子节点的主键值去主键索引上获取对应的行数据
使用覆盖索引，防止回表查询：
表 user 主键为 id，普通索引为 age，查询语句：SELECT * FROM user WHERE age = 30;

• 不使用覆盖索引：查询过程：先通过普通索引 age=30 定位到主键值 id=1，再通过聚集索引 id=1 定位到行记录数据，需要两次扫描 B+ 树
• 使用覆盖索引：只查询这两个字段，刚好这两个字段在辅助索引树上都有，就不需要回表查询了。

SELECT id,age FROM user WHERE age = 30;

使用覆盖索引，要注意 SELECT 列表中只取出需要的列，不可用 SELECT *，所有字段一起做索引会导致索引文件过大，查询性能下降

---

索引下推

索引条件下推优化（Index Condition Pushdown，ICP）是 MySQL5.6 添加，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
索引下推充分利用了索引中的数据，在查询出整行数据之前过滤掉无效的数据，再去主键索引树上查找

• 不使用索引下推优化时存储引擎通过索引检索到数据，然后回表查询记录返回给 Server 层，服务器判断数据是否符合条件
• 使用索引下推优化时，如果存在某些被索引的列的判断条件时，由存储引擎在索引遍历的过程中判断数据是否符合传递的条件，将符合条件的数据进行回表，检索出来返回给服务器，由此减少 IO 次数

适用条件：

• 需要存储引擎将索引中的数据与条件进行判断（所以条件列必须都在同一个索引中），所以优化是基于存储引擎的，只有特定引擎可以使用，适用于 InnoDB 和 MyISAM
• 存储引擎没有调用跨存储引擎的能力，跨存储引擎的功能有存储过程、触发器、视图，所以调用这些功能的不可以进行索引下推优化
• 对于 InnoDB 引擎只适用于二级索引，InnoDB 的聚簇索引会将整行数据读到缓冲区，不再需要去回表查询了

工作过程：用户表 user，(name, age) 是联合索引
SELECT * FROM user WHERE name LIKE ‘张%’ AND　age = 10; – 头部模糊匹配会造成索引失效

• 优化前：在非主键索引树上找到满足第一个条件的行，然后通过叶子节点记录的主键值再回到主键索引树上查找到对应的行数据，再对比 AND 后的条件是否符合，符合返回数据，需要 4 次回表
• 优化后：检查索引中存储的列信息是否符合索引条件，然后交由存储引擎用剩余的判断条件判断此行数据是否符合要求，不满足条件的不去读取表中的数据，满足下推条件的就根据主键值进行回表查询，2 次回表

当使用 EXPLAIN 进行分析时，如果使用了索引条件下推，Extra 会显示 Using index condition
参考文章：https://blog.csdn.net/sinat_29774479/article/details/103470244
参考文章：https://time.geekbang.org/column/article/69636

---

前缀索引

当要索引的列字符很多时，索引会变大变慢，可以只索引列开始的部分字符串，节约索引空间，提高索引效率
注意：使用前缀索引就系统就忽略覆盖索引对查询性能的优化了
优化原则：降低重复的索引值
比如地区表：

area		    	gdp		code
chinaShanghai	100		aaa
chinaDalian		200		bbb
usaNewYork		300		ccc
chinaFuxin		400		ddd
chinaBeijing	500		eee

发现 area 字段很多都是以 china 开头的，那么如果以前 1-5 位字符做前缀索引就会出现大量索引值重复的情况，索引值重复性越低，查询效率也就越高，所以需要建立前 6 位字符的索引：

CREATE INDEX idx_area ON table_name(area(7)

场景：存储身份证

• 直接创建完整索引，这样可能比较占用空间
• 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
• 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题（前 6 位相同的很多）
• 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

---

索引合并

使用多个索引来完成一次查询的执行方法叫做索引合并 index merge

• Intersection 索引合并：SELECT * FROM table_test WHERE key1 = ‘a’ AND key3 = ‘b’; # key1 和 key3 列都是单列索引、二级索引从不同索引中扫描到的记录的 id 值取交集（相同 id），然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序
• Union 索引合并：SELECT * FROM table_test WHERE key1 = ‘a’ OR key3 = ‘b’;从不同索引中扫描到的记录的 id 值取并集，然后执行回表操作，要求从每个二级索引获取到的记录都是按照主键值排序
• Sort-Union 索引合并SELECT * FROM table_test WHERE key1 < ‘a’ OR key3 > ‘b’;先将从不同索引中扫描到的记录的主键值进行排序，再按照 Union 索引合并的方式进行查询

索引合并算法的效率并不好，通过将其中的一个索引改成联合索引会优化效率

面试题

说一说你对MySQL索引的理解

索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。使用索引可 以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是 提高查询操作速度的最佳途径。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一 定支持所有索引类型。MySQL中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相 关。MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE 索引。
索引的优点主要有以下几条：

1. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的查询速度，这也是创建索引的主要原因。
3. 在实现数据的参考完整性方面，可以加速表和表之间的连接。
4. 在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

增加索引也有许多不利的方面，主要表现在如下几个方面：

1. 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
3. 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速 度。
   

索引有哪几种？

MySQL的索引可以分为以下几类：

1. 普通索引和唯一索引

普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 主键索引是一种特殊的唯一索引，不允许有空值。

2. 单列索引和组合索引

单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段 时，索引才会被使用。使用组合索引时遵循最左前缀集合。

3. 全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。

4. 空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

如何评估一个索引创建的是否合理？

建议按照如下的原则来设计索引：

1. 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建 索引，但要避免添加不必要的字段。
2. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短， 索引可能不会产生优化效果。
3. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如 在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引，如果建立索引不但不 会提高查询效率，反而会严重降低数据更新速度。
4. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整 性，以提高查询速度。
5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
   

数据库索引失效了怎么办？

可以采用以下几种方式，来避免索引失效：

1. 使用组合索引时，需要遵循“最左前缀”原则；
2. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
3. 尽量使用覆盖索引（之访问索引列的查询），减少 覆盖索引能减少回表次数；
4. MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
5. LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
6. 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
7. 少用or，用它来连接时会索引失效。
   

MySQL的索引为什么用B+树？

B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其他直接存取辅助设备设计的一种平衡查找
树，在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点，各叶子节点通过指针进 行链接。如下图：

B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在 数据库中，B+树的高度一般都在2～4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2～4次的IO操作意味着查询时间只需0.02～0.04秒。

聚簇索引和非聚簇索引有什么区别？

在InnoDB存储引擎中，可以将B+树索引分为聚簇索引和辅助索引（非聚簇索引）。无论是何种索引， 每个页的大小都为16KB，且不能更改。
聚簇索引是根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。辅助索引是根据索 引键创建的一棵B+树，与聚簇索引不同的是，其叶子节点仅存放索引键值，以及该索引键值指向的主 键。也就是说，如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，很有可能还需要根 据主键值查找聚簇索引来得到数据，这种查找方式又被称为回表查询。因为辅助索引不包含行记录的所 有数据，这就意味着每页可以存放更多的键值，因此其高度一般都要小于聚簇索引。