MySQL查看索引信息

1 索引简介1.1 什么是 MySQL 的索引

官方定义：索引是帮助 MySQL 高效获取数据的数据结构

从上面定义中我们可以分析出索引本质是一个数据结构，他的作用是帮助我们高效获取数据，在正式介绍索引前，我们先来了解一下基本的数据结构

2 索引数据结构2.1 Hash 索引

Hash 索引是比较常见的一种索引，他是通过计算出记录对应的 hash 值，然后根据计算结果，存储在对应位置。查询的时候也是根据 hash 值快速找到位置。他的单条记录查询的效率很高，时间复杂度为1。但是，Hash索引并不是最常用的数据库索引类型，尤其是我们常用的Mysql Innodb引擎就是不支持hash索引的。

hash 索引在等值查询时速度很快，但是有以下两个问题

不支持范围查询hash 冲突，当两条记录的 hash 值相同时，就产生了 hash 冲突，需要在后面用链表存储起来

2.2 二叉树2.2.1 经典二叉树

1、一个节点只能有两个子节点

2、左子节点的值小于父亲节点值，右子节点的值大于父亲节点的值，采用二分查找，速度较快

经典二叉树会出现一个极端例子，就是链表，节点数据越来越大。这种情况下，二叉树搜索性能就会降低

2.2.2 平衡二叉树

平衡二叉树又称AVL树。它可以是一颗空树，或者具有以下性质的二叉排序树：

它的左子树和右子树的高度之差(平衡因子)的绝对值不超过1它的左子树和右子树都是一颗平衡二叉树。

数字 1-6 在平衡二叉树中图示如下：

2.3 B 树

B树属于多叉树又名平衡多路查找树，可以有多叉，有如下特点

（1）排序方式：所有节点关键字是按递增次序排列，并遵循左小右大原则；

（2）子节点数：非叶节点（根节点和枝节点）的子节点数 >1、且子节点数量=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M路,当M=2则是2叉树,M=3则是3叉）；

（3）关键字数：枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数 如ceil(1.1)结果为2);

（4）所有叶子节点均在同一层、叶子节点除了包含了关键字 和 关键字记录的指针外，也有指向其子节点的指针只不过其指针地址都为null对应下图最后一层节点的空格子;

MySQL 中 B 树存储结构如下：

2.4 B+ 树

B+树是在B树的基础上又一次的改进，其主要对两个方面进行了提升，一方面是查询的稳定性，另外一方面是在数据排序方面更友好。MySQL 索引的底层数据结构采用的就是 B+ 树

（1）B+树的非叶子节点不保存具体的数据，而只保存关键字的索引，而所有的数据最终都会保存到叶子节点。因为所有数据必须要到叶子节点才能获取到，所以每次数据查询的次数都一样，这样一来B+树的查询速度也就会比较稳定，而B树的查找过程中，不同的关键字查找的次数很有可能都是不同的（有的数据可能在根节点，有的数据可能在最下层的叶节点），所以在数据库的应用层面，B+树就显得更合适。

（2）B+树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。因为叶子节点都是有序排列的，所以B+树对于数据的排序有着更好的支持。

2.5 B* 树

B树是B+树一种变形，它是在B+树的基础上，将索引层以指针连接起来（B+ 树只是将数据层用指针连接起来），使搜索取值更加快捷

总结

分析了以上几种数据结构，MySQL 采用的是 B+ 树来存储索引，综合层面来说，这样查询效率最好。oracle 采用的是 B* 树

3 索引分类

MySQL 索引主要有以下几种

主键索引唯一索引普通索引组合索引全文索引3.1 主键索引

主键索引是比较特殊的索引，一般在建表时会给表设置一个主键，MySQL 会默认给这个主键加上索引。主键索引叶子节点存储的是数据表的某一行数据。当表没有创建主键索引是，InnDB 会自动创建一个 ROWID 字段用于构建聚簇索引。规则如下：

在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。如果表没有定义主键，InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引。如果以上两个都没有，InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引。该 ROWID 字段会在插入新行时自动递增。

创建方式：

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT ,`name` varchar(255) NOT NULL ,PRIMARY KEY (`id`));

为什么建表时没有指定主键，MySQL 会默认使用一个隐式字段 ROWID 字段构建聚簇索引？这个在后面我们会提到

3.2 唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建方式

CREATE UNIQUE INDEX indexName ON user(column)或者ALTER TABLE table_name ADD UNIQUE indexName ON (column)3.3 普通索引

MySQL 基本的索引，没有什么限制

创建方式：

CREATE INDEX index_name ON user(column)或者ALTER TABLE user ADD INDEX index_name ON (column)3.4 组合索引

组合索引，顾名思义，给 MySQL 多个字段同时加上索引，在使用时要遵循最左匹配原则

创建方式：

CREATE INDEX index_name ON user(column1,column2) -- 给 column1 和 column2 加上索引3.5 全文索引

全文索引，主要用来查找文本中的关键字，不是直接与索引值相比较。与我们常见的搜索引擎（如elasticsearch、solr 等）功能相似。MySQL 全文索引性能一般，所以一般不用，作为了解即可

创建方式：

CREATE FULLTEXT INDEX index_column ON user(column)或者ALTER TABLE user ADD FULLTEXT index_column(column)4 索引设计4.1 三星索引

三星索引是我们设计 MySQL 索引时的一个规范，符合三星索引的索引设计通常是比较好的设计

一星：索引中查询相关的索引行是相邻的，或者至少相距足够靠近

二星：索引中数据列的顺序和查找中排序顺序相同

三星：索引中的列包含了查询中需要的全部列。索引包含查询所需要的数据列，不再进行全表查表，回表操作

下面举一个例子为大家介绍一下三星索引是什么样子的

现在有一张表，表结构如下

CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`age` int(10) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

一星

我们现在给 age 加上索引

create index idx_age on user (age);

查询

ｓｅｌｅｃｔ * from user where age in (10,20,35,43)

这条语句不一定符合一星，因为 age 是一个范围，数据可能比较分散

ｓｅｌｅｃｔ * from user where age = 20;

这条语句是符合一星的，因为索引是按照 age 从小到大排序的，所以 age = 20 的数据肯定是在一起的

二星

ｓｅｌｅｃｔ * from user where age = 20 order by name;

这条语句符合一星，但不符合二星，因为数据列的顺序是按照 age 排序的，如果现在改成 name 排序，可能导致索引顺序与 order by 排序结果不同，结果如下：

ｓｅｌｅｃｔ * from user where age = 20 order by age

这条查询语句则符合一星和二星

三星

ｓｅｌｅｃｔ * from user where age = 20

这条语句不符合三星，因为索引列中只有 id 和 age，没有 name

ｓｅｌｅｃｔ age from user where age = 20

这条语句则符合三星，因为只查询了 age，age 在索引中存在，不需要回表

4.2 回表

上面三星索引提到了一个次回表，那么回表是什么？

简单点说，就是查询语句中需要的列，在索引中不包含，需要根据主键 id 再查询一次才能获取到。回表相当于多查询一次，再查询时我们要尽量避免回表查询。

因为普通索引中只包含了对应列和主键的值，比如 age 索引，那么 age 索引中包含的数据有 age，id。此时如果需要 name 的话，需要先通过 age 索引找到对应的 id，然后再去主键索引上找到 name，主键索引包含了一行所有记录的值。这里回答了上面的问题，为什么 MySQL 一定要有主键索引，因为主键索引子节点中包含了全部数据

4.3 索引覆盖CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) NOT NULL,`age` int(1) DEFAULT NULL,`***` varchar(2) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_name_age` (`name`,`age`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;ｓｅｌｅｃｔ name,age from user where name = "张三"-- 这条语句就使用了索引覆盖，因为 name 和 age 再 idx_name_age 索引中都有，不需要回表查询ｓｅｌｅｃｔ name,age,*** from user where name = "张三"-- 如果加上了 ***，那么就需要回表查询了，因为索引中不存在 *** 字段5 索引优化5.1 慢查询5.1.1 简介

慢查询日志是 MySQL 提供的日志记录，用来记录所有的慢 SQL 语句，我们可以通过设置慢查询的时间阈值 long_query_time，来定义什么样的 SQL 是慢 SQL。通过慢查询日志我们可以找出需要优化的 SQL，下一步就是进行 SQL 优化

5.1.2 慢查询配置

第一步：我们可以通过 show variables like ‘slow_query_log‘ 语句查询慢查询是否开启，默认是关闭（OFF）

slow_query_log_file 是慢查询日志存放的位置，如果是 window 的话，通常在你的安装文件夹 Data 目录下

第二步：打开慢查询

set global slow_query_log= 1;

第三步：设置慢查询阈值

什么样的查询叫做慢查询呢？1s，5s 还是 10s，这点 MySQL 不知道，所以需要我们通过配置去设置 long_query_time 参数

通过命令 show variables like ‘%long_query_time%‘ 查看慢查询时间，默认是 10 s

如果需要修改，可以通过命令 set global long_query_time = 5 来设置

注意：这里通过 set global long_query_time = 5 设置完慢查询时间后，再次查询发现慢查询时间依然是 10s，难道是设置没生效？

使用此命令修改后，需要重新连接或者新开启一个会话就可以看到修改后的配置

或者通过 show global variables like ‘%long_query_time%‘ 命令查看

5.1.3 慢查询日志分析

我们刚才已经将慢查询阈值设置为 5s，现在我们执行一条这样的 sql 语句

ｓｅｌｅｃｔ sleep(6);

这条语句执行时间为 6s，我们打开慢查询日志可以发现增加了一些数据

# Time: 2022-10-02T09:16:23.194396Z# User@Host: root[root] @ localhost [::1]Id: 6# Query_time: 6.011569Lock_time: 0.000000 Rows_sent: 1Rows_examined: 0SET timestamp=1664675770;ｓｅｌｅｃｔ sleep(6);

我们来逐个分析一下每行代表什么含义：

User@Host：执行该 SQL 的用户和慢查询 IP 地址

Query_time：语句执行时长

Lock_time：获取锁的时长

Rows_sent：MySQL 返回给客户端的行数

Rows_examined：MySQL 扫描行数

timestamp：表示慢 SQL 记录时的时间戳

ｓｅｌｅｃｔ sleep(6)：则是慢查询 SQL

下面我们来分析一条真实的慢查询 SQL，之前测试时的一条 SQL 语句

# Time: 2022-07-27T09:26:44.440318Z# User@Host: root[root] @ localhost [127.0.0.1]Id:249# Query_time: 68.461112Lock_time: 0.000938 Rows_sent: 877281Rows_examined: 877303SET timestamp=1658914004;SELECTid,prd_line_id,shift_name,shift_id,app_id,weight,upload_time,operator,status,prd_line_nameFROM prd_weight WHERE (upload_time > ‘2022-07-27 00:00‘ AND upload_time < ‘2022-07-27 17:24‘);

Query_time：总查询时长 68.461112s

Lock_time：0.000938s

Rows_examined：扫描行 877281

Rows_sent：返回了 877303

当然了，这是测试用的，生产上一般不会出现这么离谱的 SQL 语句

5.1.4 注意事项在 MySQL 中，慢查询日志中默认不记录管理语句，如：alter table,，****yze table，check table 等。不过可通过以下属性进行设置：set global log_slow_admin_statements = "ON"在 MySQL 中，还可以设置将未走索引的 SQL 语句记录在慢日志查询文件中(默认为关闭状态)。通过下述属性即可进行设置：set global log_queries_not_using_indexes = "ON"在 MySQL 中，日志输出格式有支持：FILE(默认)，TABLE 两种，可进行组合使用。如下所示:set global log_output = "FILE,TABLE"这样设置会同时在 FILE， MySQL 库中的 slow_log 表中同时写入。但是日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。5.2 Explain 执行计划

通过上面的慢查询日志分析，我们可以知道有哪些慢 SQL 语句。但是这些 SQL 具体慢在哪里，需要如何优化，我们还需要更详细的分析计划，这里 MySQL 给我们提供了 Explain 关键字，通过该关键字我们可以分析出 SQL 语句的详细执行信息。

5.2.1 Explain 使用

我们在数据库中创建一张 user 表用于测试

SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for user-- ----------------------------ｄｒｏｐ TABLE IF EXISTS `user`;CREATE TABLE `user`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`***` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`dept_id` int(10) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,INDEX `idx_name`(`name`) USING BTREE,INDEX `idx_dept_id`(`dept_id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of user-- ----------------------------ｉｎｓｅｒｔ INTO `user` VALUES (1, ‘张三‘, ‘123‘, ‘男‘, ‘12323432‘, 1);ｉｎｓｅｒｔ INTO `user` VALUES (2, ‘李四‘, ‘456‘, ‘男‘, ‘178873937‘, 1);ｉｎｓｅｒｔ INTO `user` VALUES (3, ‘小花‘, ‘123‘, ‘女‘, ‘1988334554‘, 2);ｉｎｓｅｒｔ INTO `user` VALUES (4, ‘小芳‘, ‘334‘, ‘女‘, ‘18765287937‘, 2);ｉｎｓｅｒｔ INTO `user` VALUES (5, NULL, ‘122‘, NULL, NULL, NULL);ｄｒｏｐ TABLE IF EXISTS `dept`;CREATE TABLE `dept`(`id` int(11) NOT NULL AUTO_INCREMENT,`dept_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of dept-- ----------------------------ｉｎｓｅｒｔ INTO `dept` VALUES (1, ‘开发部‘);ｉｎｓｅｒｔ INTO `dept` VALUES (2, ‘销售部‘);

explain 使用也很简单，直接在查询语句前面加上 explain 关键字即可：

EXPLAIN ｓｅｌｅｃｔ * FROM user where id = 1;

从图中我们看到 MySQL 返回了一行记录，下面我们一起来分析每个字段代表什么含义

字段

含义

id

一次查询过程中该条 ｓｅｌｅｃｔ 语句的唯一标识

select_type

查询类型，共包含四种 simple、primary、subquery、derived

table

查询的是哪张表

partitions

表的分区信息

type

访问类型，分析性能主要通过该字段

possible_keys

可能会用到的索引

key

实际用到的索引

key_len

索引里使用的字节数

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量

rows

MySQL 预估的扫描行

filtered

MySQL 过滤后，满足条件记录数的比例

Extra

展示了一些额外信息

5.2.2 Explain 详解

1、id

id 是查询语句中的唯一标识，id 的值越大，该 id 对应的 sql 语句越先执行

explain ｓｅｌｅｃｔ * from dept where id = (ｓｅｌｅｃｔ dept_id from user where id = 1);

从执行计划来看，ｓｅｌｅｃｔ dept_id from user where id = 1 这条语句先执行，因为外层查询需要借助这条查询语句的结果

2、select_type

查询类型，共包含四种

simple：简单查询。查询不包含子查询和union

primary：复杂查询中最外层的 ｓｅｌｅｃｔ

subquery：包含在 ｓｅｌｅｃｔ 中的子查询（不在 from 子句中）

derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

3、table

查询的是哪张表，比较好理解

4、partitions

查询时匹配到的分区信息，对于非分区表值为 NULL，当查询的是分区表时，partitions 显示分区表命中的分区情况。

5、type

type：查询使用了何种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > range > index > ALL

system 是 const 的特例，也就是当表中只存在一条记录时，type 为 systemconst，常量查询，id 是主键，通过 id 可以查询到所有信息eq_ref，连接查询中，primary key 或 unique key 索引的所有部分被连接使用

注意：这里 dept 的 id 和 user 的 id 并无关联关系，只是为了演示该查询类型

user 的 id 和 dept 的 id 都是主键，在连接查询中，两个主键都被使用到

ref，不使用唯一索引，使用普通索引或者唯一索引，可能会找到多个条件的值，idx_name 是普通索引

ref_of_null，和 ref 功能类似，区别在于会额外搜索索引包含 NULL 的值，name 字段是普通索引，且数据库中存在 name 为 null 的数据

range，在索引字段上使用范围查询，常见的有 >、