MySQL 索引知识教程详细总结-高老四博客

MySQL 在我们现在的互联网应用中基本算是占据了大半天，但是很多人其实总是停留在使用 SQL 的边缘，可能会一些基本的优化，不过搞 Java Web 开发的人可能估计 MySQL 都很少去研究，一切的业务都在 MyBatis 中完成了。其实 MySQL 的知识体系很庞大，有很多设计到你工作中常用的知识点或者笔面试中经常考的东西往往很容易被忽略。

就拿老四即将写的索引相关知识来说吧，很多人其实也就那么用了，估计很少去做总结，想想这一列加上索引就快了，很多人数据库一慢就要加个索引。然而呢，事情总没有我们想象的那么简单，在 MySQL 中，索引是比较常用的一块知识，面试中，面试官也会经常考你这方面的东西。之前在文章《阿里巴巴Java开发手册第五章MySQL数据库-索引规约篇》中答应你们会写一篇总结出来，好在，好饭不怕晚，本文老四竭尽所能将 MySQL 中索引的所有的知识点力求说的全面一些，偏向基础，没有太多的高级的东西，这些基础往往奠定了你的数据库技能。

本文相关 SQL 调试环境都是基于 MySQL 5.7.28 社区服务器版本，所以有的 SQL 在高版本或者较低版本可能会有所差异，这里不会做总结和提醒，另外，关于这篇文章中所设计到的知识点，老四都画了思维导图共享在了 GitHub 上，可以戳此直达参考查看。

引言

索引是 MySQL 存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。自古以来的示例就是将索引类比成一本书的目录，根据书的目录，我们能够快速定位相关内容所在的页数。同理，数据库索引也是这样，利用比如说「树」的数据结构等实现一张表的「目录」，当我们想要在很多数据中获取一些数据的时候，可以加速我们查找到想要的记录而不必要对一张表的数据进行全扫描，提高性能。当然，查询性能的优化不仅仅是「索引」一种手段，你至少还要对「分库分表」、分区等一些技术手段有所了解。

索引作为常用也是必须要使用的知识，无论你是程序员还是 DBA，对其有一个清晰的掌握脉络是必不可少的。在 MySQL 中，索引是根据存储引擎来制定的，所以在了解 MySQL 之前你应该知道 MySQL 一些比较常见的存储引擎：

InnoDB
BDB
MyISAM
MEMORY
MERGE
EXAMPLE
BDB Cluster
ARCHIVE

我只列举了 8 个，其实还有很多，除了 MySQL 官方的存储引擎，还有各种各样的大厂开发的开源的或者商业行的存储引擎。但对于我们，可能最常用的就是 InnoDB 和 MyISAM 了，以前笔面试的时候他几乎是必考的知识点。在这两个经常探讨的的存储引擎中，几乎涵盖了我们探讨的的几大索引类型。

其实按照数据结构和索引类型来讲，其实 MySQl 一共就四种索引类型：

B-Tree 索引
HASH 哈希索引
全文索引
R-Tree 索引

全文索引基本是 MyISAM 的专属，InnoDB 目前还不支持。而 R-Tree 索引用于店里空间数据类型，可能做 GIS 业务的专业人士使用的多一些。哈希索引，在 InnoDB 和 MyISAM 都不支持，它也不支持一些范围查询。所以四种索引类型中，最复杂、知识点最多、最常用的就是 B-Tree 索引了，所以下文开始，主要就以 B-Tree 索引为主线，描述一些它在 InnoDB 或者 MyISAM 中的一些特性和知识点。最后贴一下常用的存储引擎支持的索引类型列表：

索引/存储引擎	InnoDB	MyISAM	MEMORY
B-Tree	✅	✅	✅
HASH 索引	❌	❌	✅
R-Tree	❌	✅	❌
全文索引	❌	✅	❌

示例数据库及表创建

下文所有的演示示例均已老四创建的这张表为基准进行例子示范。建表语句如下：

CREATE TABLE `tb_glorze` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '自增主键'，
	`username` VARCHAR ( 255 ) DEFAULT NULL COMMENT '用户名'，
	`password` VARCHAR ( 255 ) DEFAULT NULL COMMENT '密码'，
	`cust_no` DECIMAL ( 8， 0 ) DEFAULT NULL COMMENT '用户编号'，
	`create_date` datetime DEFAULT NULL COMMENT '创建日期'，
	PRIMARY KEY ( `id` )，
	KEY `idx_user_cust_date` ( `cust_no`， `username`， `create_date` ) USING BTREE，
	KEY `idx_username_cust` ( `username` ( 2 )， `cust_no` ) 
) ENGINE = INNODB AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8mb4 COMMENT = '索引示例表'

CREATE TABLE `tb_glorze` (

`id` INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '自增主键'，

`username` VARCHAR ( 255 ) DEFAULT NULL COMMENT '用户名'，

`password` VARCHAR ( 255 ) DEFAULT NULL COMMENT '密码'，

`cust_no` DECIMAL ( 8， 0 ) DEFAULT NULL COMMENT '用户编号'，

`create_date` datetime DEFAULT NULL COMMENT '创建日期'，

PRIMARY KEY ( `id` )，

KEY `idx_user_cust_date` ( `cust_no`， `username`， `create_date` ) USING BTREE，

KEY `idx_username_cust` ( `username` ( 2 )， `cust_no` )

) ENGINE = INNODB AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8mb4 COMMENT = '索引示例表'

B-Tree 数据结构

首先你必须明确的是 B树中的「B」代表的是「balance」而不是「binary」，即 B树不是二叉树而是平衡树。先来简单的复习一下树的基本概念、二叉树和平衡树。

什么是树

很多逻辑关系并不都线性的，往往存在着很多一对多或者多对多的关系，这样的逻辑关系就一棵树或长出各种各样的树枝，树枝在产生树叶。我们经常做笔记画的思维导图其实就跟树的概念很相似。

在数据结构中，树是 n 个节点的有限集合，n = 0 时叫做「空树」，想象一下思维导图的样子，我们可以总结处树有如下特点：

有且只有一个特定的开始节点，我们叫做「根节点」
当 n > 1 时，其余的节点可以分为 m 个互不产生交集的有限集合，每个集合又可以看作成一个树，他们统称为「根的子树」。

标准的树结构示例图

什么是二叉树

树的概念了解之后，二叉树顾名思义就是树的每个节点最多有两个子节点，这就是二叉树的概念了。作为二叉树，我们对于这种特殊的树结构也有一定的规定，也是它的特点：

两个子节点一个叫做「左孩子」，一个叫做「右孩子」，顺序是固定的。
最多两个子节点，告诉你二叉树不是要求必须要有两个子节点。

满二叉树&完全二叉树

数据结构中我们经常会将这两者记混，因为名字起的本身就容易令人混淆。利用你自己的记忆技巧，我们再来熟悉一下这两个树的概念区别。

满二叉树：一个二叉树的所有非叶子节点都有左右孩子，且所有叶子节点都在同一层级上面，那么这棵树就是一个「满二叉树」。

完全二叉树：其实可以理解为「不完全的满二叉树」，即他是满二叉树的一部分，但是这部分要求所有节点按照层级序号，要与原来的满二叉树深度、位置完全相同。

MySQL 索引知识详细总结的图片-高老四博客第2张

什么是平衡（二叉）树

平衡树是一棵空树或它的左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一棵平衡二叉树。平衡树有很多种类，用于各种各样的算法时间当中。像 AVL 树、树堆、伸展树、红黑树、加权平衡树、2-3 树、AA 树、替罪羊树都可以单拎出来仔细研究。B-Tree 就是从 2-3 树扩展而来的，大致的结构如下：

MySQL 索引知识详细总结的图片-高老四博客第3张

B树的数据结构特性决定了它能够存储数据、并且对其进行排序、查找、顺序读取、插入和删除，所以经常用于数据库的涉及或者文件索引系统当中。根据上图示例并且按照数据结构的定义，总结一下一个 m 阶 B-Tree 的特性（这里面「阶」的概念代表一个节点的子节点数目的最大值）：

每个节点最多拥有 m 个子树
根节点至少有 2 个子树
分支节点至少拥有 m/2 颗子树（除根节点和叶子节点外都是分支节点）
所有叶子节点都在同一层、每个节点最多可以有 m-1 个 key，并且以升序排列。

什么意思，拿上图通俗的来讲，将 m 的值设置为 3，即 3 阶 B树，那么就要求跟节点 A 至少有两个子节点，他也可以有 3 个子节点，然后 A、B、C 三个节点每个节点最多只能拥有 3 个子树，跟阶数相同，同时这个条件就要求每个分支节点（B、C）中至少要有 1 颗子树。另外就是叶子节点要求最多只能放两个关键字，并且升序排列以满足数据的顺序性。

根据以上特点，我们会基本清晰 B-Tree 索引的的数据存储，参考下图：

MySQL 索引知识详细总结的图片-高老四博客第4张

B-Tree 索引详解

从上图我们可以看出，B-Tree 索引支持全关键字、关键字范围、关键字前缀（后缀）查询，接下来分析一下 B-Tree 索引的常用的使用场景，这些使用场景基本涵盖了我们涉及到的索引建立知识以及一些 SQL 用法。不过这里有个前提，就是「复合索引」或者叫「联合索引、多列索引」的概念，很多人都喜欢建立多个单列索引，这样的设计方式在某种程度上来讲是糟糕的，其实单列索引也是多列索引的一种形式不是吗？如果真的需要建立多个索引，进行业务场景的深入考虑之后，选择一个正当的索引列顺序然后建立复合索引才是恰当的方式，不过复合索引也有很多的限制和缺点，需要注意，老四后面也会详细说明一下。

全值匹配

对于单列索引，查询的时候直接指定索引的列的具体值即可。对于多列索引，也需要对所有列的指定等值匹配的条件。

SELECT * FROM tb_glorze WHERE id = 2;

SELECT * FROM tb_glorze WHERE cust_no = 2 AND username = '赵老大' AND create_date = '2019-10-30 22:39:39';

SELECT * FROM tb_glorze WHERE id = 2;

SELECT * FROM tb_glorze WHERE cust_no = 2 AND username = '赵老大' AND create_date = '2019-10-30 22:39:39';

对于上面的 SQL 语句可以使用 Explain 命令执行查看，你会看到他们会分别使用主键索引和联合索引进行全值匹配查询。关于 Explain 命令可以参考老四的这篇《MySQL中执行explain命令后显示的各个字段都是什么含义?》文章。

匹配值范围查询

顾名思义，我们知道平衡树是按照从左到右升序排列，所以我们能够很好的根据索引列进行范围查询。

SELECT * FROM tb_glorze WHERE cust_no > 1 AND cust_no < 4;

1	SELECT * FROM tb_glorze WHERE cust_no > 1 AND cust_no < 4;

执行 Explain 命令你会看到 MySQL 优化器使用的是「type=range」范围查询并且使用了联合索引加速查找。

最左前缀匹配

注意，这里的最左前缀就是针对联合索引来进行限制的，如老四的示例表所示，创建联合索引的顺序分别为 cust_no、username、create_date，那么在 B-Tree 索引中，我们可以选择单独的 cust_no 列、cust_no + username 列组合、cust_no、username、create_date 全部使用进行查询，这些组合都满足最左前缀匹配查询，即会使用联合索引帮助你提高查询速度。但是使用「username、create_date」的组合作为查询条件不会使用多列索引，会进行全表扫描。

匹配列前缀（后缀）

这里就是常说的前缀索引、后缀索引。有的时候我们会遇到某一列会存储比较长的数据段，比如说像富文本、一段备注或者 http 链接的那样的字段，但是由于业务场景我们还需要为其配置索引，这个时候为了减少索引由于数据量过大带来的开销，我们可以对其列前缀或者后缀进行抽象，拿出一部分作为索引，从而提高查询性能。不过这个过程可能涉及到回表操作。

回表指的就是当使用索引查询到想要的数据时，但是索引中数据不完整，需要拿着索引指针找到完整的数据行，这个过程就是回表。

SELECT * FROM tb_glorze WHERE username LIKE '高%';

1	SELECT * FROM tb_glorze WHERE username LIKE '高%';

Explain 命令解释上面的 SQL，你会发现「extra=Using where」代表回表查询，但是使用了联合索引「idx_username_cust」。

覆盖索引

覆盖索引的意思其实你要查询的数据就是索引列，在这个时候你能够实现精准或者匹配部分精准索引且匹配值范围查询匹配另外一列。

SELECT username FROM tb_glorze WHERE cust_no > 1 AND cust_no < 4;

1	SELECT username FROM tb_glorze WHERE cust_no > 1 AND cust_no < 4;

如上 SQL，你会看到「extra 存在 Using index」代表使用了覆盖索引帮助我们加速查询。注意只有 B-Tree 支持聚簇索引。

仅对索引进行查询匹配

看上一条的范围查询，Explain 分析之后我们会看到其实 extra 还存在 Using where，代表了还有回表查询。如果相对比较严格的覆盖索引其实是查询索引字段，要求不要有范围查询，像下面的这样的 SQL，不需要回表，直接返回索引中的数据给你。

SELECT cust_no，username，create_date FROM tb_glorze WHERE cust_no =2 ;

1	SELECT cust_no，username，create_date FROM tb_glorze WHERE cust_no =2 ;

这里多提一下，就是匹配索引列为空一样会使用索引，类似于「索引列 is null」的形式，优化器依然会进行索引优化查询。

SELECT * FROM tb_glorze WHERE cust_no IS NULL;

1	SELECT * FROM tb_glorze WHERE cust_no IS NULL;

ICP 优化

ICP 全称「Index Condition Pushdown」，大概的表面意思是通过索引过滤操作进行下推，索引存在相关的列值，会直接在索引上进行条件过滤，然后在进行回表查询，直接返回数据行指针，减少 I/O 操作。是时候拿出这张 MySQL 基本结构图了：

MySQL 索引知识详细总结的图片-高老四博客第5张

从 MySQL 基本架构示意图我们应该知道的是，当我们执行一条查询语句，优化器进行索引选择，然后操作存储引擎返回数据给 Server 层，然后 Server 层会根据 where 关键字后面的查询条件在进行回表查询，数据过滤操作。这里面是涉及到大量的 I/O 操作的，ICP 的作用就是当发现索引中存在相关的列值，他会根据 where 条件直接在索引中完成条件、数据的过滤，然后返回给 Server 层数据指针，这样 Server 层回表的时候直接拿出的就是查询好的数据。

B-Tree 索引使用注意事项以及理想设计策略

总结一些 B-Tree 索引的一些缺点，了解这些缺点之后你就会知道以后遇到数据库的问题要尽量避免这样的问题发生。

联合索引中如果不是按照索引的最左列开始查询，无法使用索引（col1，col2，col3），如果从 col2 或 col3 查询不会使用索引。

这个问题在上面的示例中已经明确举例说明，在使用中切记「最左前缀原则」，也要记住不要遇到性能问题就「每个字段建立单列索引」。

联合索引中不能跳列查询，即必须满足最左原则。否则也不会使用索引。（col1，col2，col3），如果使用类似 col1=XX and col3=XX 查询不会使用索引。注意和上一条限制的区别。

这一条其实代表的不是不会使用索引查询，而是 MySQL 优化选择器只会选择 col1 索引，而对 col3 不会进行索引加速访问，从而联合索引失去其意义。

联合索引中如果某个列是范围查询，则后面的列索引失效。（col1，col2，col3）如果存在类似 col1=xx and col2 like ‘xx%’ and col3=xx，那么 col3 不会进行索引优化。

这一条上面也示例过，对于像 like 这样的查询，回导致后面的列索引失效甚至导致整个索引失效。

以百分号「%」开头的 LIKE 查询不会使用 B-Tree 索引

其实如果 like 关键字用 % 开头的话，倒不如采用全文索引。我们看平衡二叉树的结构，他是做不到类似全文索引的。所以我们平时是禁止使用的，这一点在孤尽的《阿里巴巴 Java 开发手册》中也有提及到，详情参考《阿里巴巴Java开发手册第五章MySQL数据库-索引规约篇》文章。

条件查询出现类型对应错误，会进行全表扫描，不会使用索引。例如：name=glorze，但是 name 是字符串类型，glorze 必须加引号。

过滤性越高，MySQL 越容易使用索引。所以当 MySQL 认为全表扫描来得更快的时候不会使用索引。

如果条件选择使用关键字「or」，即使前面的列有索引但是后面的列没有索引，涉及到的索引不会被使用。

单列索引的限制

对于单列索引也不一定就是会被优化选择器选择使用的，当出现单列索引的索引列是「表达式的一部分」或者「MySQL 函数的参数」时，这个时候单列索引是无效的。

前缀（后缀）索引和索引选择性

这里说一下索引选择行的概念，我们都知道在索引中，各种类型的索引也有性能、好坏之分，那么评判标准就是这个「索引选择性」，他的定义是：不重复的索引值和表的总记录数的比例。所以比例越高，索引的性能的就越高。所以我们很清楚的知道，我们经常用的主键索引、唯一索引，他们的索引选择性比例值是1，所以性能最好。但是往往我们建立索引的过程中总会碰到某一列字段值超长的现象，如果对其建立完整索引，势必增大的索引的体积，有可能导致索引占用的存储空间甚至比表空间还要大，这样的话索引的意义就失效了。我们刚接触的 MySQL 的时候，你可能也被要求过主键索引不能使用 UUID，也有这方面的原因，无序的字符串导致乱序不讲，他也占用了极高的索引存储空间，所以是极其不建议使用 UUID 做主键的。

那么如果某一字段类似于 http 链接那样长且在生产情景下需要添加索引，那么就可以考虑前缀（后缀）索引。这样既能保证索引的高性能，又能避免性能开销过大。但是要注意前缀索引的合适选取长度。

但是前后缀索引也有其限制，就是前后缀索引无法进行排序 order by、无法进行分组 group by、无法进行覆盖扫描（where 条件的列和返回的数据在一个索引中，不需要回表查询）等。

尽量使用复合索引，多列索引

还是那句话，把 where 后面的常用列都建立单独索引其实不是一个优秀的解决方案。其实在 MySQL 中，还有个「索引合并」的概念，对于多个单列索引进行的复杂查询会进行优化，从而也说明单列索引的性能在大多数时候失效。但是建立联合索引的时候需要考虑联合索引的合理顺序以及要注意联合索引的经常失效的场景，举个例子：在不考虑排序和分组的情况下，如果用于优化 where 条件的查找，可以考虑将选择性最高的列放到索引最前列。

多利用聚簇索引

在前面「以百分号「%」开头的 LIKE 查询不会使用 B-Tree 索引」的问题，其实可以利用聚簇索引，使用嵌套查询来间接的使用索引实现 like 的全模糊查询。但是你要知道聚簇索引的概念不是一种索引形式，而是一种数据存储形式，在 InnoDB 中，聚簇索引代表的是主键索引存储完整数据行。这样的设计方式可以使相关数据保存在一起，数据访问更快。透过平衡树来看就是使用覆盖索引扫描的查询可以直接使用叶子节点的值。

不过我们可以看出来，主键索引存储完整的数据行也会给我们带来很多的问题和限制，数据在内存中会被飞速访问，快速查询，但是存储开销变大，当我们要进行插入数据的时候，插入的速度完全取决于插入的顺序，对于大量数据，查询的性能提高，但是牺牲了插入、更新（索引的更新）的性能，同时可能导致全表扫描变慢，尤其是行比较稀疏或者存储不连续。

同时，聚簇索引带来最严重的问题就是「页分裂」的问题。关于页分裂，我们要知道的数据是存储在磁盘当中的，磁盘的 I/O 相对于内存来讲是极其影响性能、浪费时间的，所以无论是什么样的系统，基本都会有一个「预读取」的概念来进行数据读取，比如你想读取「1」这个磁盘地址的数据，系统会将与「1」临近的数据都帮你取出来，因为你用「1」了，那么用到「2」、「3」的可能会很大，它为了避免多次的磁盘 I/O，所以会帮助我们进行预读取操作，所以我们将一次 I/O 读取的数据称之为「一页」。

我们继续回顾平衡术结构的特点，他要求所有叶子节点都在同一层、每个节点最多可以有 m-1 个 key，并且以升序排列。那么当我们插入不规则的数据时，可能就需要面临一个树结构重组的问题，这个问题也将会导致一个查询条件，可能一次 I/O 拿不到所有的相邻数据，这就是一次「页分裂」，平衡树的数据结构，如果插入不规则的数据，会导致当前节点所属的页满，然后进行页分裂操作，容纳新增的数据，这样的话会极大的影响性能。刚才说为什么不允许设置主键索引为 UUID，这是本质原因。

MySQL 索引知识详细总结的图片-高老四博客第6张

覆盖索引（只有 B-Tree 支持）

前面已经提到覆盖索引，不需要回表查询，减少数据访问量，简单的范围查询可以使用顺序的索引访问。

尽量使用索引扫描做排序

在索引设计的时候需要设置某一索引，让其尽量既能满足手动排序，又能做查找，设计就算是比较好的。要求索引的列顺序和 order by 顺序一致。同时要注意避免冗余和重复索引。

MySQL 命令大全（持续更新）

由于目前老四没有对 MySQL 的 SQL 语句进行系统化的梳理，所以借此文章先进行一些原始 SQL 的积累，到一定程度之后会单独整理常用的 MySQL 运维语句以及常用的 SQL 技巧、优化技巧等单独成文发布，所以这个模块以索引相关 SQl 语句开始，逐步积累更多相关实用 SQL。

1、查看当前的默认存储引擎

SHOW VARIABLES LIKE '%storage_engine%';

1	SHOW VARIABLES LIKE '%storage_engine%';

2、查询当前数据库支持的存储引擎

SHOW ENGINES;

1	SHOW ENGINES;

3、在 MySQL 中开启或者关闭 ICP

set @@optimizer_switch = "index_condition_pushdown=off"；
set @@optimizer_switch = "index_condition_pushdown=on"；

1 2	set @@optimizer_switch = "index_condition_pushdown=off"； set @@optimizer_switch = "index_condition_pushdown=on"；

4、前缀索引的创建

ALTER TABLE TB_GLORZE ADD KEY(url(8));

1	ALTER TABLE TB_GLORZE ADD KEY(url(8));

5.查看索引的使用情况

SHOW STATUS LIKE 'handler_read%'

1	SHOW STATUS LIKE 'handler_read%'

Handler_read_rnd_next 越大意味着查询低效。

6.SQL 语句的线程执行状态

SHOW PROCESSLIST

1	SHOW PROCESSLIST

执行结果中主要看「Command」这一列，他会有比较多的值，老四这里只列举常见的几个：

Sleep — 正在等待客户端向它发送执行语句
Query — 该线程正在执行一个语句
Daemon — 服务器内部线程，而不是来自客户端的链接
Execute: 正在执行一个 Prepared Statement

此外 Command 的值还有 Binlog Dump、Change user、Close stmt、Connect、Connect Out、Create DB、Debug、Delayed insert、Drop DB、Error、Field List、Fetch、Init DB、Kill、Long Data、Ping、Processlist、Quit、Refresh、Register Slave、Reset stmt、Set option、Shutdown、Statistics、Table Dump、Time，线程命令值可以点此参考 MySQL 5.7 官方文档。

7.绕过查询缓存（MySQL 8.0 中已经干掉了查询缓存）

SELECT SQL_NO_CACHE * FROM tb_glorze;

1	SELECT SQL_NO_CACHE * FROM tb_glorze;

看一下上图的 MySQl 基本架构图，你要知道，一条 DQL 查询语句，从客户端进入到服务器之后，首先进行的是查询缓存的校验，这个查询缓存是你以前使用过的查询语句，MySQL 会根据你的 SQL、数据库等因素结合在一起生成的一个哈希库，当你再次使用这个 SQL 的时候，MySQL 会直接不经过分析优化而直接返回结果。至于为什么在 MySQL 会干掉查询缓存，大概是因为它的缓存机制太过于局限，只要表有更新，缓存就失效了，并且缓存的 SQL 必须要与再次输入的 SQl 语句完全一致才能命中缓存，属于完犊子的设计。

8.MySQl 索引成本统计

SHOW INDEX FROM tb_glorze;

1	SHOW INDEX FROM tb_glorze;

本条 SQL 跟下面面试题第一题有关，也跟上面的索引知识点中的「索引选择性」概念有关。我们知道 MySQL 中优化器负责挑选最合适的索引来对 SQL 进行优化处理，该命令结果中的 Cardinality 的字段值其实就可以理解为索引选择行的一个参考值，表示索引列中不重复值的个数。所以这个字段的值越大，代表索引选择行越高，性能越好，MySQL 优化器就会选择对应的索引加速 SQl 语句结果的返回。

9.MySQL 开启慢 SQL 日志

# 查看MySQL是否开启慢日志记录
SHOW VARIABLES LIKE 'slow_query_log';

# 开启慢SQL日志记录
SET GLOBAL slow_query_log = 'ON';

# 查看慢SQL日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

# 查看执行多久的SQL才算慢SQL
SHOW VARIABLES LIKE 'long_query_time';

# 设置慢SQL执行时间 只有新session才生效
SET GLOBAL long_query_time = 1;

# 查看MySQL是否开启慢日志记录

SHOW VARIABLES LIKE 'slow_query_log';

# 开启慢SQL日志记录

SET GLOBAL slow_query_log = 'ON';

# 查看慢SQL日志位置

SHOW VARIABLES LIKE 'slow_query_log_file';

# 查看执行多久的SQL才算慢SQL

SHOW VARIABLES LIKE 'long_query_time';

# 设置慢SQL执行时间只有新session才生效

SET GLOBAL long_query_time = 1;

10.MySQL 状态、事务、连接等常用 SQL

# 展示哪些线程正在运行
SHOW PROCESSLIST;

# 查看正在执行的事务
SELECT * FROM information_schema.INNODB_TRX;

# 查看正在锁的事务
SELECT * FROM information_schema.INNODB_LOCKS;

# 查看正在等待锁的事务
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

# 显示innodb存储引擎状态的大量信息，包含死锁日志
SHOW ENGINE INNODB STATUS ;

# 展示数据库最大连接数的配置
SHOW VARIABLES LIKE 'max_connections';

# 查看存在哪些触发器
SELECT * FROM information_schema.TRIGGERS;

# 查看MySQL版本
SELECT VERSION();

# 展示哪些线程正在运行

SHOW PROCESSLIST;

# 查看正在执行的事务

SELECT * FROM information_schema.INNODB_TRX;

# 查看正在锁的事务

SELECT * FROM information_schema.INNODB_LOCKS;

# 查看正在等待锁的事务

SELECT * FROM information_schema.INNODB_LOCK_WAITS;

# 显示innodb存储引擎状态的大量信息，包含死锁日志

SHOW ENGINE INNODB STATUS ;

# 展示数据库最大连接数的配置

SHOW VARIABLES LIKE 'max_connections';

# 查看存在哪些触发器

SELECT * FROM information_schema.TRIGGERS;

# 查看MySQL版本

SELECT VERSION();

11.待续。

MySQL 索引常见笔面试题（会持续更新）

一、MySQL 某表建了多个单索引，查询多个条件时如何走索引的？

继续参考上面的 MySQl 基本架构示意图，本题考察的是优化器的相关底层知识，我个人认为对于 Java 工程师考察这样的知识点的确优点超出范围了，不过在表面上我们还是要知道一些优化器的一些运行原理，也就是优化器是如何选择索引的。我们知道 Explain 命令中有一列「type」字段，其中「type=range」代表了索引范围扫描，常见于＜、≤、＞、≥、between 等操作符。其实在优化器中存在着一个 Range 优化器专门负责范围查询优化，这个优化器就是通过索引成本统计来进行优化的，具体参考上面 MySQl 常用命令大全第八条的说明。除此之外，MySQl 还有一个优化方式就是计算每个索引的 CPU 和 I/O 开销，选择一个开销最小的索引作为选择，但是这种计算开销的方式本身就带来了很大的开销，所以，优化器一般都是以第一种方式来进行优化的。

二、MySQL 行锁是如何实现的（InnoDB）？

本题主要考察的是 InnoDB 的行锁的实现方式，你要问我别的我可能也说不明白。

其实 InnoDB 行锁是通过给索引加锁实现的，这一点一定要记住。所以当我们只有通过索引条件来查询数据的时候，才会使用行级锁，要不然的话采用的都是表级锁。

三、你是怎么建立索引的？

其实这种题目的的问法包含隐藏式的提问，不仅需要你回答出你都建立了什么索引，怎么建的。其实更多的是要考察你是如何发现 SQl 慢的，然后利用什么索引解决这个慢 SQL 的，所以在笔面试的过程中切记答死题，尽量将自己的考虑放宽，回答出更多的扩展性方面，会加分不少。比如说线上生产环境，你通过连接池监控或者设置 my.cnf 监控慢查询日志监控慢 SQl，然后针对 SQL 进行 Explain 或者 Trace 分析，然后再根据自己的索引建立规范标准进行优化，实现慢查询的优化，一套下来，问题回答的完美。

一般的索引建立规则（基本就是上文的索引知识点的总结）：

在频繁排序、分组的字段上面建立联合索引
一张表的数据量很少不要建立索引，至于什么是少自己心里有点数吧？
索引不是越多越好，索引的增多往往带来索引存储性能的开销，甚至会比表存储空间都要大了，而且他会影响插入更新的性能。
除了分组排序的字段建立索引之外，剩下就是对经常用于查询的字段建立索引
挑选索引选择性高的字段建立索引

四、索引的分类？

这个问题老四上文其实只说了索引的类型，如果你想回答比较全面一些，可以将 MySQl 中的索引分为数据结构角度、物理存储角度、逻辑角度三方面去说明。其中数据结构角度就是 B树索引、HASH 哈希索引、FULLTEXT 全文索引、和 R-Tree 空间索引；物理存储角度就是聚簇索引和非聚簇索引；逻辑角度就是主键索引、普通索引、单列索引、联合索引、唯一索引、前缀索引等等这些索引综合在一起说。

五、如何避免回表查询？什么是索引覆盖？

问题已经告诉你如何避免回表查询了。详细去了解一下覆盖索引的概念和设计方式。

六、MySQL 索引结构使用红黑树可以么？

先来基本的了解一下什么红黑树。红黑树也是平衡树的一种，它的特点如下：

每个结点要么是红的要么是黑的。
根结点是黑的。
叶子节点都是黑的。
如果一个结点是红的，那么它的两个儿子都是黑的。
对于任意节点而言，其到叶子节点的每条路径都包含相同数目的黑结点。

基本的红黑树结构如下：

MySQL 索引知识详细总结的图片-高老四博客第7张

但是红黑树不像 B树，红黑树基本都要要求在内存中使用，所以对于像数据库这样的要求大规模的数据存储交换及查询等，显然我们不可能将所有的数据和索引等都放在内存中进行操作，红黑树不适合。

引言