索引是怎么加速查询的

索引是怎么加速查询的

事实上，在你还没有执行 create index 语句的时候，MySQL 就已经创建索引了。

1、聚簇索引

mysql5.5之后的默认存储引擎是innoDB，mysql还支持另一个存储引擎–MylSAM

MySQL为保持版本兼容，依旧使用最多3字节的UTF8字符集，并在MySQL 5.5.3版本引入UTF8MB4字符集来支持4字节的Unicode字符。

执行建表语句：

CREATE TABLE `student` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT '主键id',
  `student_no` VARCHAR(64) COMMENT '学号',
  `name` VARCHAR(64) COMMENT '学生姓名',
  `age` INT COMMENT '学生年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='学生信息表';

插入 5 条数据：

insert into student(student_no,name,age) values(101,"Alice",18);
insert into student(student_no,name,age) values(102,"Bob",19);
insert into student(student_no,name,age) values(104,"Brandt",15);
insert into student(student_no,name,age) values(105,"David",19);
insert into student(student_no,name,age) values(109,"David",18);

在插入的过程中，MySQL 会用你指定的主键，在这里是递增主键，维护起一棵 B+树，我用了旧金山大学做的 BPlusTree Visualization 来模拟这棵树的样子，主键从 1 开始递增，插入五条，所以是 1 到 5

地址链接：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

其中有几个特性：

• 有序：左边节点比右边小
• 自平衡：左右两边数量区域相等
• 节点分裂：节点在遇到元素数量超过节点容量时，是如何分裂成两个的，这个也是 MySQL 页分裂的原理

MySQL 里绝大多数索引都是 B+树，另外有少数情况会使用 Hash索引、R-tree等等。

模拟工具只支持插入一个值，所以你看不到主键之外的其他数据，实际上，这棵 B+树的叶子节点是带有行的全部数据的。

如果没有这棵 B+树，你要根据主键查询，比如

select * from student where id = 5;

对不起，数据是无序的，你只能全表扫描，犹如大浪淘沙。

即使主键自增，也不能用二分法来进行查找。

主键虽然是递增的，但是如果写入磁盘时，没有去维护有序数组这样一个数据结构，因为B+树的这样的数据结构问题，比如：删除时，删掉了4,5的移动会产生无序化操作，而B+树无序后，只能进行随机查找。

但是如果不采用B+树这样的结构，采用有序数组的话，其实也是建立了索引，只是索引的数据结构不同罢了。

至于MySQL，为什么采用B+树而不是处采用其他的数据结构：

hash不支持范围查询，

二叉树树高很高

B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。

B+树进行范围查找的效率更高，因为B+树把所有的叶子节点数据构成一个有序链表。

建立了B+树索引，数据就有规律的存储起来，查找id = 5，就不再是全表扫描了：

• 从上到下，先找到3, 5比它大，找右节点
• 找到4，5和4比较，比4大，继续找右节点
• 这次到达叶子节点了，叶子节点是一个递增的数组，那就用二分法，找到id = 5 的数据

**要访问磁盘的次数，是由这棵树的层次决定的。**其速度完全接近于二分法查找。

当你没有主键时，唯一键也可以。

当唯一键也没有时，mysql会给你建一个rowid字段，用它来组织这课B+树

反正 MySQL 就一个目的，数据要有规律的存储起来，就像之前在 数据库是什么 里说的，数据是否被规律的管理起来，是数据库和文件系统区分开来的重要因素。

这个 MySQL 无论如何都会建起来，并且存储有完整行数据的索引，就叫聚簇索引（clustered index）。

2、二级索引

聚簇索引只能帮你加快主键查询，但是这时如果想要根据姓名查询呢？

因为没有给姓名创建索引，所以，只能进行全表扫描

当然也可以给name创建索引，然数据按照姓名有规律的进行组织：

create index idx_name on student(name);

这时候，MySQL又会建一颗新的B+树：

这时候会发现，这个数的叶子节点，只有姓名和ID两个字段，并没有行的完整性数据，这时候，执行：

select * from student where name = "David";

执行顺序：

• mysql先到name所在关键字的索引进行查询，查询David的 主键ID
• 查询到ID 为 4 和 5
• 然后拿着 主键 ID去主键所在的聚簇索引去查询数据
• 最终查询到数据

这个不带行数据完整信息的索引，就叫二级索引（secondary index），也叫辅助索引

3、复合索引

那如果有需求是，根据姓名和年龄进行查询呢？

select * from student where name = "David" and age = 18;

数据虽然按照 name 进行有规律的组织了，但是没有按照age有规律组织，所以我们要给name 和 age同时建索引：

create index idx_name_age on student(name,age);

这时候 MySQL 又会建一棵 B+树，这下 B+树 的节点里面，不只有 name，还有 age 了：

注意用红色虚线框出来的那两个节点，这是这棵树和上面那棵只给 name 建索引的树的唯一区别，两个元素换了个位，因为排序时，是先用 name 比较大小，如果 name 相同，则用 age 比较。

注意创建索引时，的最左前缀匹配原则！！！

**问：**只给 student 表建 idx_name_age 这个复合索引，这两个 sql 语句，会走索引吗？

select * from student where name = "David"; // 走
select * from student where age = 18;		// 不走

当name 相同的时候，B+树中存储的节点只会是name的，他的子节点才会包含age元素进行判断。

索引吗？

select * from student where name = "David"; // 走
select * from student where age = 18;		// 不走

当name 相同的时候，B+树中存储的节点只会是name的，他的子节点才会包含age元素进行判断。

参考：三太子敖丙https://mp.weixin.qq.com/s/7TPVOT7sloDUKmhldf9uvg