一文了解MySQL二级索引的查询过程

让我来详细讲解一下“一文了解MySQL二级索引的查询过程”的完整攻略。

什么是MySQL二级索引

MySQL的二级索引也称为非聚集索引。通俗易懂的解释，就是在主索引外的索引，其存储方式为“单独索引”，不绑定表中的具体行数据。

MySQL二级索引查找流程概述

MySQL的二级索引在查找数据时，是通过B+树来实现的。以下是MySQL二级索引查找流程的概述：

1. 从二级索引B+树的根节点开始，向下定位到相应的叶子节点；
2. 找到叶子节点后，在其中搜索符合条件的记录；
3. 根据叶子节点中记录的索引值定位到对应表中的数据行，获取需要的数据；

MySQL二级索引查找流程详解

数据库表的创建和数据插入

代码示例1：创建测试表，并插入5条数据

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(50) DEFAULT NULL,
  `goods_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order_goods_id` (`goods_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

INSERT INTO `order` (`id`, `order_no`, `goods_id`, `amount`, `create_time`)
VALUES
    (1,'order001',101,55.00,'2022-01-01 00:00:00'),
    (2,'order002',102,66.60,'2022-01-02 10:00:00'),
    (3,'order003',103,77.77,'2022-01-03 10:00:00'),
    (4,'order004',104,88.88,'2022-01-04 10:00:00'),
    (5,'order005',105,99.99,'2022-01-05 10:00:00');

以上是创建了名为order的测试表，并在其中插入了五条数据。这个测试表有主键id、一般索引index_order_goods_id以及其他几个字段。

测试1：查询单个字段的数据

代码示例2：查询goods_id为101的数据

SELECT * FROM `order` WHERE `goods_id`=101;

以上是查询goods_id为101的数据。

MySQL使用二级索引查找的流程如下：

1. 使用index_order_goods_id二级索引得到符合条件的记录，例如：指向goods_id=101的B+树节点；
2. 从goods_id=101这个节点下，找到该节点下所有的叶子节点；
3. 遍历所有goods_id=101的叶子节点，并获取主键id的值；
4. 根据获取的主键id值，再次到主键索引树上查找对应的数据，并返回这些数据；

通过以上流程，MySQL进行了一次二级索引查找。 实际上，MySQL使用的是一个排序较快的查找过程，不需要完全遍历所有索引节点，并且B+树叶子节点指向的是数据行地址，特别适合查找件数很多的数据。

测试2：测试不包含二级索引的查询

代码示例3：查询order_no为order002的数据

SELECT * FROM `order` WHERE `order_no`='order002';

以上查询虽然没有使用二级索引，但 MySQL 同样也会先查找有没有二级索引可以使用。然后若二级索引无法使用，就会直接使用主键索引。

• MySQL首先会检查二级索引，发现没有与order_no这个列相关联的二级索引。
• MySQL随后将去访问包含该数据的主键索引树，找到该数据，并返回对应的结果。

总结

以上就是“一文了解MySQL二级索引的查询过程”的完整攻略。通过以上的示例和讲解，我们已经大致掌握了 MySQL 的二级索引的查询过程。