关于后端:一文读懂-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、且子节点数量 <=M、且 M >=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+ 树只是将数据层用指针连接起来），使搜寻取值更加快捷

<img src=”https://gitee.com/wugongzi-git/BlogFigurebed/raw/master/image/202209191323561.png” alt=”image-20220919132355520″ style=”zoom: 67%;” />

总结

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

3 索引分类

MySQL 索引次要有以下几种

• 主键索引
• 惟一索引
• 一般索引
• 组合索引
• 全文索引

3.1 主键索引

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

1. 在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
2. 如果表没有定义主键，InnoDB 会抉择第一个不为 NULL 的惟一索引列用作聚簇索引。
3. 如果以上两个都没有，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);

查问

select * from user where age in (10,20,35,43)

这条语句不肯定合乎一星，因为 age 是一个范畴，数据可能比拟扩散

select * from user where age = 20;

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

二星

select * from user where age = 20 order by name;

这条语句合乎一星，但不合乎二星，因为数据列的程序是依照 age 排序的，如果当初改成 name 排序，可能导致索引程序与 order by 排序后果不同，后果如下：

select * from user where age = 20 order by age

这条查问语句则合乎一星和二星

三星

select * from user where age = 20

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

select 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,
  `sex` varchar(2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age` (`name`,`age`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;


select name,age from user where name = "张三"
-- 这条语句就应用了索引笼罩，因为 name 和 age 再 idx_name_age 索引中都有，不须要回表查问
select name,age,sex from user where name = "张三"
-- 如果加上了 sex，那么就须要回表查问了，因为索引中不存在 sex 字段

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 语句

select sleep(6); 

这条语句执行工夫为 6s，咱们关上慢查问日志能够发现减少了一些数据

# Time: 2022-10-02T09:16:23.194396Z
# [email protected]: root[root] @ localhost [::1]  Id:     6
# Query_time: 6.011569  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1664675770;
select sleep(6);

咱们来一一剖析一下每行代表什么含意：

[email protected]：执行该 SQL 的用户和慢查问 IP 地址

Query_time：语句执行时长

Lock_time：获取锁的时长

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

Rows_examined：MySQL 扫描行数

timestamp：示意慢 SQL 记录时的工夫戳

select sleep(6)：则是慢查问 SQL

上面咱们来剖析一条实在的慢查问 SQL，之前测试时的一条 SQL 语句

# Time: 2022-07-27T09:26:44.440318Z
# [email protected]: root[root] @ localhost [127.0.0.1]  Id:   249
# Query_time: 68.461112  Lock_time: 0.000938 Rows_sent: 877281  Rows_examined: 877303
SET timestamp=1658914004;
SELECT  id,prd_line_id,shift_name,shift_id,app_id,weight,upload_time,operator,status,prd_line_name  FROM 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 注意事项

1. 在 MySQL 中，慢查问日志中默认不记录治理语句，如：
   alter table,，analyze table，check table 等。不过可通过以下属性进行设置：
   set global log_slow_admin_statements = “ON”
2. 在 MySQL 中，还能够设置将未走索引的 SQL 语句记录在慢日志查问文件中(默认为敞开状态)。通过下述属性即可进行设置：
   set global log_queries_not_using_indexes = “ON”
3. 在 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
-- ----------------------------
DROP 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,
  `sex` 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
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', '123', '男', '12323432', 1);
INSERT INTO `user` VALUES (2, '李四', '456', '男', '178873937', 1);
INSERT INTO `user` VALUES (3, '小花', '123', '女', '1988334554', 2);
INSERT INTO `user` VALUES (4, '小芳', '334', '女', '18765287937', 2);
INSERT INTO `user` VALUES (5, NULL, '122', NULL, NULL, NULL);


DROP 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
-- ----------------------------
INSERT INTO `dept` VALUES (1, '开发部');
INSERT INTO `dept` VALUES (2, '销售部');

explain 应用也很简略，间接在查问语句后面加上 explain 关键字即可：

EXPLAIN SELECT * FROM user where id = 1;

从图中咱们看到 MySQL 返回了一行记录，上面咱们一起来剖析每个字段代表什么含意

5.2.2 Explain 详解

1、id

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

explain select * from dept where id = (select dept_id from user where id = 1);

从执行打算来看，select dept_id from user where id = 1 这条语句先执行，因为外层查问须要借助这条查问语句的后果

2、select_type

查问类型，共蕴含四种

simple：简略查问。查问不蕴含子查问和 union

primary：简单查问中最外层的 select

subquery：蕴含在 select 中的子查问（不在 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 为 system

• const，常量查问，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，在索引字段上应用范畴查问，常见的有 >、<、in、like 等查问

• index，通过索引树进行全表扫描

• ALL，全表扫描，不通过索引树，因为这次是 select * 查问

6、possible_keys

MySQL 剖析此次查问可能会用到的索引，然而理论查问中不肯定会用到

剖析可能会用到 idx_name 这个索引，理论查问中没有用到索引，走的全表扫描

7、key

查问时真正用到的 key

查问中实际上用到了 idx_name 这个索引

8、ken_len

示意查问用到的索引列长度

咱们用这个索引来剖析，key_len 为 63 是怎么来的？

创立 user 表的时候，不晓得大家有没有留神到，name 的字符集为 utf8

MySQL 5.0 版本以上，utf8 字符集下每个字符占用 3 个字节，varchar(20) 则占用 60 个字节，同时因为 varchar 是变长字符串，须要额定地字节寄存字符长度，共两个字节，此外，name 字段能够为 null 值，null 值独自占用一个字节，加在一起一共 63 个字节

9、ref

当应用索引列等值匹配的条件去执行查问时，也就是在拜访办法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，ref列展现的就是与索引列作等值匹配的具体信息，比方只是一个常数或者是某个列。

10、rows

预计须要扫描的函数

11、filtered

filtered 这个是一个百分比的值，表里符合条件的记录数的百分比。简略点说，这个字段示意存储引擎返回的数据在通过过滤后，剩下满足条件的记录数量的比例。

12、Extra

Extra是用来阐明一些额信息的，从而帮忙咱们更加精确的了解查问

5.3 高性能 的索引应用策略

5.3.1 不要在索引列上做任何操作

explain select * from user where left(name,2) = '小芳';

这段 sql 对 name 字段做了函数操作，导致索引生效

5.3.2 最左前缀法令

在应用联结索引查问时，应该遵循最左前缀准则，指的是查问从索引的最左前列开始并且不跳过索引中的列。

创立一张 goods 表，有一个联结索引蕴含了 name，price、mark 三个字段

DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (`id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` int(10) NULL DEFAULT NULL,
  `mark` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_all`(`name`, `price`, `mark`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, '手机', 5678, '华为手机');
INSERT INTO `goods` VALUES (2, '电脑', 9888, '苹果电脑');
INSERT INTO `goods` VALUES (3, '衣服', 199, '难看的衣服');

执行上面的查问语句：

explain select * from goods where name = '手机' and price = 5678 and mark = '华为手机';

从上图能够看到 type 为 ref。

当初咱们不从最左侧开始查问，间接跳过 name 字段

explain select * from goods where price = 5678 and mark = '华为手机';

type 从 ref 变成了 index，这是因为 MySQL 建设索引时是依照组合索引中的字段程序来排序的，如果跳过两头某个字段，则不肯定是有序的了。

5.3.3 尽量应用笼罩索引

笼罩索引，须要查问的字段全副蕴含在索引列中，不须要回表查问