MySQL09-索引

1. 索引概述

在关系型数据库中，索引是一种单独的、物理的对表中一列或多列值进行有序排序的存储结构，是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的逻辑指针清单。索引的作用相当于书籍的目录，可以根据目录中的页码快速找到所需的内容。

索引本质是一种数据结构，通过特定的数据结构和查找方法尽可能地避免大量的顺序 I/O，从而提高 MySQL 的检索速度。

1.1 索引的优点

1. 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引最主要的原因；
2. 通过索引列对数据进行排序，可以降低数据排序的成本，从而降低 CPU 的消耗；
3. 索引可以将随机 I/O 变成顺序 I/O，从而降低数据库的 I/O 成本；
4. 索引可以加速表和表之间的连接，在实现数据的参考完整性方面特别有意义；

1.2 索引的缺点

1. 创建和维护索引要耗费时间，这个时间随着数据量的增加而增加；
2. 对表中的数据进行增、删、改操作时，索引也要动态维护，降低了数据库整体的更新维护速度；
3. 除了数据表占用数据空间之外，每一个索引也要占用一定的物理空间；

1.3 索引的设计原则

1.3.1 适合建立索引的列

1. 数据量较大（表中的数据在百万级以上），且查询操作比较频繁的列；
2. 作为主键的列，规范该列的唯一性和组织表中数据的排列结构；
3. 经常使用连接（join）的列，主要是一些外键列，可以加快连接的速度；
4. 经常需要根据范围搜索（<、<=、=、>、>=、between、in）的列，因为索引已经排序完毕，其指定的范围是连续的；
5. 经常需要排序（order by）和分组（group by）的列，可以利用索引的排序，加快排序和分组的时间；
6. 经常使用条件子句（where）的列，可以加快条件的判断速度；
7. 区分度高的列，尽量建立唯一索引，区分度越高，使用索引的效率越高；

1.3.2 不适合建立索引的列

1. 在查询中很少使用或者参考的列；
2. 只有很少数据值或者重复值多的列；
3. 定义为 text、image 和 bit 数据类型的列；
4. 修改性能要求远远高于检索性能的列（修改性能和检索性能本来就是矛盾的）；

1.3.3 索引的建立原则

1. 字符串类型的字段，如果字段长度较长，可以针对于字段的特点，建立前缀索引；
2. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
3. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率；
4. 如果索引列不能存储NULL值，要在创建表时使用 NOT NULL 约束，当优化器知道每列是否包含NULL值时，可以更好地确定哪个索引最有效地用于查询；

2. 索引结构

2.1 索引结构概述

2.1.1 常见的索引结构

数据库索引，本质上是数据库管理系统中一个排序的数据结构，在 MySQL 中，有如下几种索引结构：

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

这些索引结构在常见的数据库存储结构中支持情况如下：

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree索引	不支持	支持	不支持
Full-text	5.6版本后支持	支持	不支持

2.1.2 索引结构的作用原理

数据库索引是采用树结构存储的，树结构的查询效率高，还能保持有序，但是索引本身很大，以索引表的形式存储在磁盘中，树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次 I/O 操作，索引的结构组织要尽量减少查找过程中磁盘 I/O 的存取次数，提高查询效率。

2.1.3 树结构相关概念

flowchart TB; A((A)) --> B((B)); A --> C((C)); A --> D((D)); B --> E((E)); B --> F((F)); B --> G((G)); C --> H((H)); D --> I((I)); D --> J((J)); E --> K((K)); E --> L((L)); I --> M((M))

1. 结点
   表示树中的元素，包括数据项及若干指向其子树的分支，上图每个圆都为一个结点。
2. 结点的度
   结点所拥有的子树的个数称为该结点的度，上图结点 B、C、D 的度分别为 3、1、2。
3. 叶子结点
   度为 0 的结点称为叶子结点，或者称为终端结点，上图结点 K、L、F、G、H、M、J 都为叶子结点。
4. 分支结点
   度不为 0 的结点称为分支结点，又叫非终端结点，一棵树除叶子结点外，其余都是分支结点，上图 B、C、D 都为分支结点。
5. 双亲、孩子、兄弟
   在一颗树中选定根结点后，相邻的两个结点，靠近根的是双亲，远一点的是孩子，具有同一个双亲的子结点互称为兄弟，根结点是树中唯一没有双亲的结点，上图结点 D 是结点 I、J 的双亲，结点 I、J 是结点 D 的孩子，结点 I 和结点 J 的互为兄弟。
6. 祖先、子孙
   在一颗树中选定根结点后，根结点到该结点的唯一路径上的任意结点，都称为该结点的祖先结点，该结点则是这些结点的子孙结点，双亲结点也属于祖先结点，根结点是所有结点的祖先结点，上图 A、B、E 都是 K 的祖先结点， E、K 都是 B 的子孙结点。
7. 路径、路径长度
   树中两个结点之间所经过的结点序列构成这两个结点之间的路径，路径长度是路径上所经过的边的个数（层数-1）。
8. 结点的层数
   规定树的根结点的层树为 1，其余结点的层树等于它的双亲结点的层树加 1，上图中 E 结点的层数是 3，K 结点的层数是 4。
9. 树的深度
   树中所有结点的最大层树称为树的深度，上图中树的深度是 4。
10. 树的度
    树中所有结点的度的最大值作为该树的度，上图中 A、B 结点的度数为 3，是所有结点中度最大的，所以该树的度为 3。
11. 有序树和无序树
    树中结点的子树从左到右是有次序的，不能交换，这样的树称为有序树，反之则称为无序树，有序树中，一个结点的子结点按照从左到右的顺序出现是有关联的，上图所示的树是一棵有序树，若将子结点的位置互换，则变成一棵不同的树。
12. 森林
    零棵或有限棵不相交的树的集合称为森林，和自然界中树和森林是不同的概念，在数据结构中，树和森林只有很小的差别，任何一棵树，删去根结点就变成了森林。

2.1.4 二叉树

flowchart TB; A((10)) --> B((7)); A --> C((9)); A --> D((14)); B --> E((3)); B --> F((5)); B --> G((6)); C --> H((8)); D --> I((12)); D --> J((13)); E --> K((2)); E --> L((4)); I --> M((11))

二叉树（binary tree）是指树中结点的度不超过 2 的有序树。二叉树的递归定义可以为一棵空树，或者一个有根结点和两棵互不相交的，分别称为根的左子树和右子树组成的非空树，左子树右子树又同样是二叉树。B-Tree 是二叉搜索树的一般化。二叉树有两个特点：

1. 非叶子结点只能允许最多两个子结点存在；
2. 每一个非叶子结点左边子结点的值小于当前结点，右边的子结点的值大于当前结点，即任何结点的左子结点的键值都小于当前结点的键值，右子结点的键值都大于当前结点的键值；

二叉树查找结点值的方法为二分查找法，查找次数就是树的高度。

2.2 B-Tree

B-Tree 属于多叉树，又叫多路平衡查找树，是二叉搜索树的一般化，B-Tree 有如下特点：

1. 结点中的元素包含键值（key）和数据（data），结点中的键值左小右大按次序递增排列，也就是说，所有的结点都储存数据；
2. 一个 m 阶的 B-Tree，每个中间结点存储的键值个数范围是 \([ceil(\frac{m}{2})-1,m-1]\)，指针个数（也即子结点数）范围是 \([ceil(\frac{m}{2}),m]\)，m 作为阶数，表示一个结点的子结点个数的最大值，m 应大于等于 2，空树除外（ceil是一个向正无穷方向取整的函数）；
3. 一个 m 阶的 B-Tree，其根结点存储的键值个数范围是 \([1,m-1]\)，指针个数（也即子结点数）范围是 \([2,m]\)，
4. 所有的叶子结点都位于同一层，叶结点具有相同的深度，叶结点的键值之间没有指针，键值个数范围与非叶子结点相同；
5. 一个有 n 个子结点的 B-Tree 结点，有 n-1 个键值，n 个指针；
6. 父结点当中的元素不会出现在子结点中；

以下是一个 3 阶 B-Tree 的结构图：

B-Tree 查询实例：

-- 以上方B-Tree结构为例，执行以下语句，查询路径磁盘块1->磁盘块2->磁盘块6
select * from table where id = 10;

-- 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10 < 15，走左路，定位到磁盘块2；
-- 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7 < 10，定位到磁盘块6；
-- 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，找到10，取出其data，如果data存储的是行记录，则取出data即可，查询结束，如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据（在InnoDB中Data存储的是行数据，MyIsam中存储的是磁盘地址）；

-- 相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

可以看到，B-Tree 依然还有需要优化的地方，比如：

1. B树不支持范围查询的快速查找，如果想要查找10和35之间的数据，查找到15之后，需要回到根结点重新遍历查找，需要从根结点进行多次遍历，查询效率有待提高；
2. 如果 data 存储的是行记录，随着列数的增多，行的大小会增大，所占空间就会变大，一个页中可存储的数据量就会变少，树相应就会变高，磁盘 I/O 次数就会变大；

2.3 B+Tree

B+Tree 是应文件系统所需而产生的 B-Tree 的升级版，在 B-Tree 基础上，将数据全部存储到叶子结点上，叶子结点只存储键值用来索引，更充分地利用了结点的空间，让查询速度更加稳定，其速度也完全接近于二分法查找，是目前现代关系型数据库最广泛支持的索引结构，B+Tree 有如下特点：

1. 叶子结点存储了父结点的所有键值记录的指针和其数据，非叶子结点只存储键值，叶子结点本身依键值左小右大顺序排练，各结点之间使用双向指针连接，形成一个双向有序链表；
2. 所有的非叶子结点可以看成索引部分，其键值会出现在叶子结点中，且是叶子结点最大或最小的键值；
3. 一个有 n 个子结点的 B+Tree 结点，有 n 个键值，n 个指针；

以下是一个 3 阶 B+Tree 的结构图：

B+Tree 查询实例：

-- 以上方B+Tree结构为例，执行以下等值查询语句，查询路径磁盘块1->磁盘块2->磁盘块6
select * from table where id = 37;
-- 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，37<59，走左路，定位到磁盘块2；
-- 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，15<37<44，定位到磁盘块6；
-- 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第2个索引中找到37，取出data；

-- 以上方B+Tree结构为例，执行以下范围查询语句，查询路径磁盘块1->磁盘块2->磁盘块6
select * from table where id between 21 and 59;
-- 首先查找值等于21的数据，将值等于21的数据缓存到结果集，这一步和前面等值查询流程一样，发生了三次磁盘IO；
-- 查找到21之后，因为叶子结点是一个有序列表，从磁盘块6、键值21开始向后遍历筛选所有符合条件的数据，一直到磁盘块6全部遍历完成；
-- 第四次磁盘IO：磁盘6全部遍历完成后，根据磁盘块6后继指针定位到磁盘块7，将磁盘块7加载到内存中，在内存中从头遍历比较找到键值59，将data缓存到结果集；
-- 因为主键的唯一性和叶子结点的有序性，键值59后不会有小于等于59的值，也就不需再向后遍历，范围查询终止，返回结果集；

2.4 Hash

哈希索引（hash index）是一种基于哈希表的索引结构，需要精确匹配索引所有列的查询才生效。哈希索引会把键值通过哈希函数换算成一个哈希码（hash code），然后存储在哈希表的特定位置（哈希槽）上，同时在哈希表中保存指向每个数据行的指针。

如果两个或多个键值映射到一个相同的哈希槽上，就会产生哈希冲突（也称为哈希碰撞），可以通过链表的方式解决这种冲突。索引的结构是十分紧凑的，除非哈希冲突很高，否则哈希索引的查询效率很高，只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。

以下是一个 Hash 索引的结构图：

Hash 索引的限制：

1. 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行；
2. 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序；
3. 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的；
4. 哈希索引只支持等值比较查询，包括=、IN、<>（注意<>和<=>是不同的操作），不支持任何范围查询；
5. 访问哈希索引的数据非常快，除非有很多哈希冲突（不同的索引列值却有相同的哈希值），当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行；
6. 如果哈希冲突很多的话，一些索引维护操作的代价也会很高，例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大；

自适应哈希：

在 MySQL 中，InnoDB 引擎有自适应哈希功能，会在内存中根据 B+Tree 索引在指定条件下自动构建哈希索引。

2.5 索引结构思考

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

1. 相对于二叉树，层级更少，搜索效率高；
2. 相对于 B-Tree，将数据全部存储到叶子结点上，充分利用了结点的空间，让结点可以存储更多键值，降低了树的高度，提升了性能；
3. 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作；

3. 索引分类

MySQL的索引可以从数据结构、存储结构、功能等角度划分成不同的类型，比如：

• 按数据结构分：B+tree索引、Hash索引、R-Tree（空间索引）、FULLTEXT（全文索引）
• 按物理存储分：聚簇索引、二级索引（辅助索引、非聚簇索引）
• 按字段特性分：主键索引（PRIMARY）、唯一索引（UNIQUE）、普通索引、前缀索引
• 按字段个数分：单列索引、联合索引（多列索引、复合索引、组合索引）

3.1 常用索引

不同的索引分类并不意味着它们是完全不同的索引，这些索引分类通常都会有交叉，同一种索引可能属于多个分类，比如，大多数索引的数据结构都是 B+tree。从使用场景看，比较常用的几种索引有以下几种：

索引分类	关键字	说明
主键索引	PRIMARY	默认自动创建，是特殊的唯一索引，用于唯一标识表中的每条记录，每张表只能有一个主键索引
唯一索引	UNIQUE	确保数据列中的每个值都不重复，同一表中可以有多个
常规索引	INDEX	用于加速查询操作的基本索引类型，同一表中可以有多个
全文索引	FULLTEXT	在文本较长的时候进行全文搜索，查找的是文本中的关键词，而不是索引中的值，同一表中可以有多个
单列索引	—	针对单个字段的索引，同一表中可以有多个
联合索引	—	针对多个字段的索引，只有在查询条件中使用了这些字段中的第一个字段，该索引才被使用

3.2 聚集索引与二级索引

在 InnoDB 存储引擎中，根据索引的存储形式，分为聚簇索引和二级索引，其性质如下：

索引分类	说明
聚集索引(Clustered Index)	将数据与索引一起存储，索引结构的叶子节点保存了行数据，每张表中必须有，且只能有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应行数据的主键，同一表中可以有多个

聚集索引选取规则：

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

以下是聚簇索引和二级索引的结构图：

3.3 前缀索引与索引排序

当表中某个字符串字段非常长，但是前面的几个字符就能够很好地区分记录时，可以通过设置索引长度，只取字符串的前几个字符设置索引，减少索引所占的空间，加快索引的构建和查询速度，提高索引效率，这种索引类型被称为前缀索引，只适用于字符串类型的数据列。

同时也可以使用 asc|desc 指定索引在物理存储时的顺序，但并非所有数据库系统都支持指定列的排序方向，或者即使支持，其实际效果也可能在具体实现时受到限制。如，MySQL 支持在语法中写明 ASC 或 DESC，但实际上并不影响索引的存储方向。

这两个选项在创建索引语句中是可选项，其语法如下：

create index 索引名 on 表名(索引字段名[(索引长度)] [asc|desc]);
alter table 表名 add index 索引名(索引字段名[(索引长度)] [asc|desc]);

-- 对text表中的email字段创建索引，索引名为index_email，设置索引长度为5，以升序排列
create index index_email on text(email(5) asc);
alter table text add index index_email(email(5) asc);

前缀索引中的前缀长度可以根据索引选择性来决定，选择性是指不重复的索引值和数据表的记录总数（T）的比值，范围从 \(\frac{1}{T}\) 到 1 之间，索引选择性越高则查询效率越高，唯一索引的选择性是1，是最好的索引选择性，性能也是最好的。可以用以下语法求出索引选择性：

# 求某字段（字符串字段）的选择性
select count(distinct 字段名) / count(*) from 表名;
# 求该字段（字符串字段）截取前五个字符后的选择性
select count(distinct substring(字段名, 1, 5)) / count(*) from 表名;

使用查看索引命令 show create table 表名;，从返回的数据中 sub_part 字段可以看到前缀索引所截取的字符长度。

3.4 索引分类思考

# 1. 下面两条查询SQL语句，哪条执行效率高？为什么？id为主键，针对name字段创建了常规索引。
select * from user where id = 10;
select * from user where name = 'Arm';

-- 第一条语句执行效率高，因为id字段为主键，意味着自动创建了主键索引，而主键索引通常是聚簇索引，只需要一次查询就可以得到数据，而name字段只有一个常规索引，且该字段不具有唯一性，而每张表中只能有一个聚簇索引，因此第二条查询语句并不能一次性查询到所需数据，而要在第一次查询后再次执行一次回表查询，才能得到数据，相当于进行了两次查询。

# 2. InnoDB存储引擎中采用B+Tree结构的主键索引能存储多少数据

-- 假设InnoDB中一页的大小是16k，指针占用6个字节，主键类型为bigint，占用8个字节，设n为当前节点存储的键值key的数量，则有：
-- n * 8 + (n + 1) * 6 = 16 * 1024
-- 其中n*8是键值所占空间，(n+1)*6是指针所占空间，解得键值数n为1170，指针数为1171，也即每个结点最多可对应1171个子节点
-- 假设每行数据大小为1k，一页可存储16条数据，由此可得
-- 如果树的高度为2，也即一个根结点对应1171个叶子节点，共有数据约 1171 * 16 = 18736 条
-- 如果树的高度为3，能存储的数据量约 1171 * 1171 * 16 = 21939856 条
-- 一般B+Tree的高度不会超过3，如果超过，就会考虑分表存储

3.5 索引的相关操作

3.5.1 创建索引

创建常规索引，只需用 index 关键字定义索引即可，如果创建的是特殊类型的索引，则需要用到这些索引的专属关键字，常见的索引的关键字有：主键索引 priamry、唯一索引 unique、常规索引 index、全文索引 fulltext、空间索引 spatial。

创建索引的一般语法：

create index 索引名 on 表名(索引字段名);

-- 要想创建特殊索引，只需在index关键字前加上特殊索引的关键字，如创建唯一索引unique
create unique index 索引名 on 表名(索引字段名);

通过修改表结构创建索引：

alter table 表名 add index 索引名(索引字段名);
-- alter table语法创建索引时不强制要求指定索引名称，如果不指定，MySQL会自动生成一个名称

-- 相应的，在index关键字前加上特殊索引的关键字即可创建特殊索引，如创建唯一索引unique
alter table 表名 add unique index 索引名(索引字段名);

创建表时直接创建索引：

alter table 表名 (
	字段名1 字段类型1,
	字段名2 字段类型2,
	...
	index 索引名(索引字段名)
);

-- 相应的，在index关键字前加上特殊索引的关键字即可创建特殊索引，如创建唯一索引unique
alter table 表名 (
	id int,
	name varchar(16),
	unique index unique_name(col_name);
);

以上方式创建的都是单列索引，如需创建联合索引（多列索引），只需将多个字段并列书写，中间用 , 连接即可，其余规则同上，如：

create index 索引名 on 表名(索引字段名1, 索引字段名2...);
alter table 表名 add index 索引名(索引字段名1, 索引字段名2...);

除此之外，还有一些特殊的索引，其创建方法与以上索引有所不同：

-- 1. 主键索引，主键索引是一种特殊的索引，在主键约束创建完成之后，系统会自动为主键列创建主键索引，因此主键索引一般不需主动创建，如需手动创建，可以使用以下语句对设置了主键约束的字段创建：
create index 主键索引名 on 表名(主键字段名);

-- 2. Hash索引，在大多数情况下，并不能显式地为InnoDB表创建HASH索引，如果需要，可以通过创建一个自定义的哈希函数来模拟，如果想要手动创建一个HASH索引，一般会选定一列或多列高唯一性的数据列作为哈希键，然后用以下命令对哈希键创建Hash索引：
create index 哈希索引名 on 表名 using hash (哈希键字段名);

3.5.2 删除索引

直接删除索引：

drop index 索引名 on 表名;

修改表结构删除索引：

alter table 表名 drop index 索引名;

3.5.3 查看索引

直接查看索引：

show index from 表名;

-- 该命令会将索引信息以表格形式展示，因为索引信息过多，表格会有变形，可以在其后加\G改变展示方式，使得以垂直列表的形式显示，如：
show index from 表名\G;

也可以通过查看生成表的SQL语句间接查看索引：

show create table 表名;

4. SQL性能分析

4.1 查看SQL执行频次

在数据库SQL优化时主要优化的是数据查询语句，而索引优化在提升数据查询效率中占主导地位，但并不是所有数据表都是需要查询的表，有的数据表执行更多的是插入、更新或删除操作，针对这些表做索引优化对整体的SQL优化并没有意义。可以通过以下命令查看当前数据库中 INSERT、UPDATE、DELETE 和 SELECT 语句的访问频次，为SQL优化提供依据：

show session|global status like 'Com_______';
-- session 表示查询当前会话，global 表示查询全局数据

-- 输出内容如下：
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| ...           | ...   |
| Com_delete    | 0     |
| Com_insert    | 1     |
| Com_select    | 2222  |
| Com_update    | 3     |
| ...           | ...   |
+---------------+-------+

4.2 慢查询日志

通过查看增删查改语句的执行频次，可以了解哪些数据库需要优化，借助慢查询日志，则可以发现执行效率低下的 SQL 查询，通过对这些查询的分析和优化，可以显著提高数据库的性能。

慢查询日志是记录了所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）的所有 SQL 语句的日志。可以通过以下语句查看慢查询日志的状态：

show variables like 'slow_query_log';

MySQL中慢查询日志默认是关闭的，需要在MySQL的配置文件中插入以下内容开启：

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

# 以下内容是可选的
# 指定慢查询日志文件的存储路径和文件名
slow_query_log_file = /var/lib/mysql/mysql-slow.log
# 是否记录未使用索引的查询，设置为1表示记录
log_queries_not_using_indexes = 1

MySQL的配置文件一般在如下位置：

# MySQL的配置文件
# Windows系统，其中X.X是MySQL的版本号
MySQL Server X.X/my.ini
# Linux系统
/etc/mysql/my.cnf 或 /etc/my.cnf

更改MySQL配置文件后需要重启MySQL服务才生效，重启的语句如下，注意以下语句不是SQL语句，要在系统级命令行工具执行：

# Windows系统
# 停止MySQL服务
net stop mysql80
# 启动MySQL服务
net start mysql80

# Linux系统
systemctl restart mysql

在MySQL客户端内部无法直接查看慢查询日志的内容，Windows系统中可以直接找到慢查询日志所在位置查看，Linux中则可以使用 cat 命令查看慢查询日志，可以使用以下SQL语句查询慢查询日志的位置：

show variables like 'slow_query_log_file';

4.3 profiling

慢查询日子可以记录超过指定时间的 SQL 语句，profiling 语句则可以查询所有 SQL 语句的执行耗时，通过以下语句可以查看当前 MySQL 是否支持 profile 操作：

select @@have_profiling;
-- 返回 YES 表示支持，NO 表示不支持

MySQL中，profiling 默认关闭，可以通过以下语句查询其状态并开启：

# 查询 profiling 状态，ON 或 1 表示开启，OFF 或 0 表示关闭
show variables like 'profiling';
# 开启 profiling
set profiling = 1;

开启 profiling 后，可以使用以下语句查看所有SQL语句的耗时（本质上是查询其性能数据）：

show profiles;

-- 返回以下内容，分别为自动赋予该语句的Query_ID、查询耗时、SQL语句内容
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00019275 | select * from student |
+----------+------------+-----------------------+

还可以通过以下语句查看指定 query_id 的SQL语句在各个阶段中的耗时：

show profile for query query_id;

还可以在以上语句的基础上接受其他参数来获取不同类型的性能数据，例如CPU使用情况、块IO次数等，其语法如下：

show profile [type1, type2, ...] for query query_id;
-- 其中 type 可以是如下值：
-- ALL 显示所有类型的性能数据
-- BLOCK IO 显示块IO操作的次数和耗时
-- CONTEXT SWITCHES 显示上下文切换的次数
-- CPU 显示CPU的使用情况
-- IPC 显示每次指令的执行周期数（Instructions Per Cycle）
-- MEMORY 显示内存的使用情况
-- PAGE FAULTS 显示页面错误（Page Faults）的次数
-- SOURCE 显示查询的源代码
-- SWAPS 显示发生的交换次数

-- 例如，查看查询语句的CPU使用情况和上下文切换次数，语法为：
show profile CPU, CONTEXT SWITCHES for query query_id;

4.4 explain

MySQL 的 explain 工具用于分析查询的执行计划，使用 explain 可以揭示 MySQL 如何使用索引、在查询过程中如何处理各个表间的连接（JOINs），对于调整和改善数据库的性能至关重要。其语法如下：

-- 直接在 select 语句之前加上关键字 explain / describe / desc
[explain|describe|desc] select查询语句;

-- 如：
explain select * from student;
-- 返回内容如下：
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+-----+
|id|select_type|table  |partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+-----+
| 1|SIMPLE     |student|NULL      |ALL |NULL         |NULL|NULL   |NULL|  24|  100.00|NULL |
+--+-----------+-------+----------+----+-------------+----+-------+----+----+--------+-----+

在实际开发中，需要重点关注的字段有：type、possible_keys、key、key_len、rows、Extra，各字段的含义如下：

属性	含义
id	select 查询的序列号，表示查询中 select 子句或者操作表的执行顺序，id 值相同时，被视为同一组，从上到下依次执行，id 值不同（比如子查询，id 值会递增）时，id 值越大越先执行，id为 NULL 则最后执行
select_type	表示查询语句的类型，如：simple（简单查询）、uncacheable subquery（子查询）
table	表示当前这一行正在访问哪张表，如果SQL定义了别名，则展示表的别名
partitions	表示当前查询匹配记录的分区。对于未分区的表，返回null
type	表示连接类型，性能由好到差分别为：NULL、system、const、eq_ref、ref、range、index、all
possible_keys	可能应用在这张表上的一个或多个索引，索引不一定被实际使用
key	实际使用的索引，如果为 NULL，则表示没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好，key_len 是根据表定义计算而得的，不是通过表内检索出的
ref	显示索引的哪一列被使用了，如果可能的话，是一个常量 const
rows	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，是一个估计值，不是结果集里的真实行数
filtered	表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好
Extra	展示有关本次查询的附加信息，如：Using index（索引覆盖）、Using filesort（文件排序）

• 关于各字段的详细解释，可参考MySQL官方文档：EXPLAIN Output Format。

5. 索引的使用规则

5.1 最左前缀法则

最左前缀法则指的是，在执行查询时，如果利用联合索引加速查询，则查询条件必须从联合索引中的最左边的字段开始匹配，并且不可跳过索引中的字段。如果查询条件缺少索引最左端的字段，则该联合索引不会被使用，如果跳过了索引中的字段，则这一字段右边的字段将不会使用联合索引（部分失效）。

需要注意的是，这里描述的左右顺序是创建联合索引时字段的顺序，而不是查询语句的查询条件中字段的顺序。因此，在创建联合索引时要有意识地将常搜索的数据列放在联合索引的前面创建，以最大长度使使用该联合索引的查询符合最左前缀法则：

# 创建联合索引时，字段排列顺序一般如下，使用频率从左至右依次降低
create index 索引名 on 表名(使用频率高的字段, 使用频率一般的字段, 使用频率低的字段...);

下面将使用一个案例说明，假设存在一个 student 表，采用表内的 id、name、age 三个字段构建一个联合索引，联合索引名为 index_id_name_age，可以使用 explain 工具查看查询使用的索引，并通过工具中的 Key_len 字段的值，判断哪些字段使用了联合索引，有如下几种查询情况：

# 创建索引语句
create index index_id_name_age on student(id, age, name);

# 查询示例 1，符合最左前缀法则，使用了联合索引
select * from student where id = '003' and age = 20 and name = '张三';
# 查询示例 2，符合最左前缀法则（最左端字段未缺少，未跳过字段），使用了联合索引
select * from student where id = '003' and age = 20;
# 查询示例 3，符合最左前缀法则，使用了联合索引
select * from student where id = '003';

# 查询示例 4，不符合最左前缀法则，没有使用联合索引
select * from student where age = 20 and name = '张三';
# 查询示例 5，不符合最左前缀法则，没有使用联合索引
select * from student where age = 20;
# 查询示例 6，不符合最左前缀法则，没有使用联合索引
select * from student where name = '张三';

# 查询示例 7，部分符合最左前缀法则，id 字段使用了联合索引，但 age 字段没有使用联合索引，联合索引部分失效
select * from student where id = '003' and name = '张三';

# 查询示例 8，符合最左前缀法则，使用了联合索引，前面说到，这里的左右顺序是联合索引中字段的顺序，和查询条件中字段顺序无关
select * from student where name = '张三' and id = '003' and age = 20;

此外，在联合索引中，如果查询语句中出现范围查询（<、>），查询范围右侧的字段将不会使用联合索引，联合索引也会部分失效。可以尽量使用 >= 或者 <= 在不改变查询条件的前提下替代 >、>，规避索引失效问题，如：

# 查询示例 1，出现了范围查询（age > 20），id、age 使用了联合索引，name 没有使用联合索引
select * from student where id = '003' and age > 20 and name = '张三';
# 查询示例 2，使用 >= 替代 >，规避了范围查询，三个字段都使用了联合索引
select * from student where id = '003' and age >= 21 and name = '张三';

5.2 索引失效情况汇总

1. 在带有索引的列上进行运算操作，索引将失效，如：

# 查询 student 表中手机号码倒数两位是 15 的同学，phone 列带有索引
select * from student where substring(phone, 10, 2) = '15';
# 因为使用 substring() 函数计算了 phone 列，即使 phone 列带有索引，该查询也不会使用该索引

2. 查询语句中使用字符串类型字段时，如果不对该字段内容添加引号，索引将失效，如

# 查询 student 表中手机号码是 12345678910 的同学，phone 列带有索引
select * from student where phone = 12345678910;
# 因为 phone 列的数据是字符串类型，在使用时未对该列数据添加引号，即使 phone 列带有索引，该查询也不会使用该索引

3. 模糊查询中，尾部模糊匹配，索引不会失效，头部模糊匹配，索引将失效，如

# 查询 student 表中姓李的同学，name 列带有索引，仅是尾部模糊匹配，索引不会失效
select * from student where name like '李%';
# 查询 student 表中最后一个字是“勋”的同学，name 列带有索引，是头部模糊匹配，该查询不会使用该索引
select * from student where name like '%勋';
# 查询 student 表中中间一个字是“勋”的同学，name 列带有索引，头尾都有模糊匹配，该查询也不会使用该索引
select * from student where name like '%建%';

4. 查询语句中用 or 连接的两个条件，其中一个条件的列没有设置索引，即使另一条件的列有索引，如：

# 查询 student 表中 id 为 003 或 age = 20 的同学，id 列设有索引，age 列没有索引
select * from student where id = '003' or age = 20;
# 即使 id 列带有索引，但和其连接的 age 列没有索引，所以该查询不会使用该索引

5. 由数据表中的数据结构决定是否使用索引，如果 MySQL 评估在该次查询中，使用索引比全表扫描效率更低（比如查询返回的数据将占整张数据表的大多数），则该次查询将不会使用索引，如：

# 查询示例 1，查询 student 表中 18 岁以上的同学，age 列最小值为 18，且带有索引
select * from student where age >= 18;
# 因为age列最小值为18，本次查询返回的数据将是整张表格的内容，全表扫描比使用索引更快，即使age列带有索引，该索引也不会被使用

-- 相应的，即使返回的不是整张表格，只要是整张数据表的大多数数据，MySQL都会使用全表扫描而忽略索引

# 查询示例 2，进行两次查询，分别查询 student 表中 addr 为 null 和不为 null 的同学，addr 列带有索引
select * from student where addr is not null;
select * from student where addr not null;
# 和例 1 相似，以上两个查询是否使用索引取决于返回的结果集占整个数据表的比例，哪个比例大，哪个就不用索引
# 也就是说，这种情况下使不使用索引由表中的数据结构决定，和查询条件无关

5.3 索引提示

索引提示（INDEX HINT） 是指在查询语句中加入提示语句，显式地告诉优化器使用哪个索引，达到优化操作的目的，是优化数据库的一个重要手段。通常以下两种情况会使用索引提示：

1. MySQL 的优化器错误地选择了某个索引，导致SQL运行很慢，这个情况比较少见。优化器在绝大部分情况下都是正确有效的。
2. 某些SQL语句可以选择的索引非常多，这时优化器选择执行计划所耗费的时间可能会大于执行SQL语句本身。

在 MySQL 中，有以下三个索引提示的语法：

1. 建议数据库使用某个索引，但数据库不一定会采纳建议，而会权衡运行速度选择合适的索引

select 字段名 from 表名 use index(索引名) where 条件表达式;

2. 不使用某个索引，告诉数据库不使用某个索引进行本次查询

select 字段名 from 表名 ignore index(索引名) where 条件表达式;

3. 必须使用某个索引，强制数据库使用某个索引进行本次查询

select 字段名 from 表名 force index(索引名) where 条件表达式;

5.4 回表查询与索引覆盖

由于二级索引的叶子节点不存储完整的表数据，索引当通过二级索引查询到行数据的主键（一般就是聚簇索引的列值）后，还需要回到聚簇索引也就是表数据本身进一步获取数据，这种情况被叫做回表查询。

如下图，执行查询语句 select * from table where name = 'Qian';，先从二级索引找到 Qian 所在键值，取出其叶子结点存储的主键 3，再返回聚簇索引，查到键值 3 所在叶子结点存储的行数据。回表查询需要额外的检索过程，必然增大查询耗时。

需要注意的是，通过二级索引查询时，回表不是必须的过程，当 SELECT 查询的所有字段在单个二级索引中都能够找到时，就不需要回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖。 可以用 explain 命令查看SQL语句的执行计划，执行计划的 Extra 字段中若出现 Using index，即表示查询触发了索引覆盖。结合以下案例理解索引覆盖：

# 以上图数据表为例，在 id 列建立了一个聚簇索引，在 name 列建立了一个单列二级索引，现执行以下语句：

-- 查询示例 1，以下查询使用了聚簇索引，不触发回表查询，属于索引覆盖
select id, name from table where name = 'qian';

-- 查询示例 2，以下查询使用了二级索引，不触发回表查询，属于索引覆盖
-- 尽管 id 和 name 两个字段没有建立联合索引，但 name 字段在索引中最终指向的是 id 值，因此也属于索引覆盖
select id, name from table where name = 'qian';

-- 查询示例 2，以下查询使用了二级索引，但 password 列数据无法在二级索引中查到，因此触发了一次回表查询
select id, name, password from table where name = 'qian';

索引覆盖思考：

# 还是以上图数据表为例，id 列有一个聚簇索引，name 列有一个单列二级索引，如何优化下列查询语句，避免回表查询
select id, name, password from table where name='qian';

-- 要想避免回表查询，只需把不在二级索引中的 password 字段包括进一个二级索引即可，因此，可以给 name 和 password 字段建立一个联合索引，这样在查询时会使用这个联合索引，从而触发覆盖索引，也就不需要回表查询。