深入了解Mysql索引


欢迎关注我的微信公众号【Mflyyou】获取持续更新。

github.com/zhangpanqin/MFlyYou 收集技术文章及我的系列文章,欢迎 Star。

前言

索引是为了更快的找到某行数据的位置,然后从文件中将数据读取出来返回给客户端。

索引的添加也很讲究,每张表中的索引建议不超过 5 个,因为索引的维护(增删改)也是比较繁琐的,太多了比较消耗数据库性能。

本文内容

  • 索引的类型:UNIQUE,FULLTEXT,SPATIAL,NORMAL(普通索引)

  • 索引为什么会采用 B+ 树结构,为什么不是红黑树、B- 树

  • Mysql 中 B+ 树索引 和 Hash 索引应该选哪个

索引

Mysql 中常见的索引类型有:

  • 普通索引
  • 唯一索引
  • 全文索引
  • 空间索引

Mysql 中索引的数据结构有:

  • B+Tree ,存储引擎 InnoDBMyISAM 都支持。因为我们一般都是使用存储引擎 InnoDBMyISAM,我们都是使用 B+Tree 数据结构的索引。
  • HASH,存储引擎 MEMORY 支持,存储引擎 InnoDBMyISAM 不能手动定义 HASH 索引。

我们先来介绍一下两种索引的数据结构的区别,感受一些各自的使用场景。

Hash 数据结构的索引

Hash 数据结构,我们可以理解为 Java 中的 Map,存储的都是 key-value 键值对的数据,我们没有办法进行范围查找。但是它的等值查找比较快,时间复杂度 O(1)。

创建一张表,字段有 id 和 description,并且在 description 上添加 HASH 索引。

DROP TABLE IF EXISTS `index_hash_test`;
CREATE TABLE `index_hash_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `inx_descrption` (`description`(100)) USING HASH
) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;
INSERT INTO `index_hash_test` VALUES (1, 'a');
INSERT INTO `index_hash_test` VALUES (3, 'b');
INSERT INTO `index_hash_test` VALUES (2, 'c');
INSERT INTO `index_hash_test` VALUES (4, 'd');
INSERT INTO `index_hash_test` VALUES (5, 'e');

HASH 索引范围查找不生效

-- 查看执行计划看到,全表扫描,没有走索引
EXPLAIN SELECT * FROM index_hash_test WHERE description >= 'b'

image-20201207222246741

HASH 等值查找生效

EXPLAIN SELECT * FROM index_hash_test WHERE description = 'b'

image-20201207222418954

B+Tree 数据结构的索引

B+Tree 数据结构的索引是可以进行范围查询的。

DROP TABLE IF EXISTS `index_test`;
CREATE TABLE `index_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_descrption` (`description`)USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `index_test` VALUES (1, 'a');
INSERT INTO `index_test` VALUES (3, 'b');
INSERT INTO `index_test` VALUES (2, 'c');
INSERT INTO `index_test` VALUES (4, 'd');
INSERT INTO `index_test` VALUES (5, 'e');

B+Tree 数据结构的索引表上执行查询计划,可以看到在查询的时候,索引可以使用。

EXPLAIN SELECT * FROM index_test WHERE description >= 'b';
EXPLAIN SELECT * FROM index_test WHERE description > 'b';
EXPLAIN SELECT * FROM index_test WHERE description > 'b';

image-20201209220413998

HASH 索引 和 B+Tree 索引总结

  • 实际开发中,我们大多数场景都是使用 存储引擎 InnoDBMyISAM
感兴趣的话可以在这个网站,看数据结构是怎样运行的。比如说 B+ 树插入、删除和查询
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

image-20200923203559362

相较于二叉树,B+ 树子节点会更多,树的高度会更低,在查找数据的时候,减少了遍历的次数以达到可以减少 Io 次数 (从磁盘加载数据到内存中)。

B+ 树相较于 B- 树,叶子节点是有序的,并且只有叶子节点会存数据。

比如查询大于 3 的数据的时候,找到了 3 直接遍历 3 上的链表就可以查询大于3 的数据。

当 B+ 树的每个子节点为 500 。500*500*500=125000000 树的高度为 4 时就能存下 1000 多万条数据。

但是一个表中的索引也不是越多越好,一般不超过 5 个索引。

索引相关名词

CREATE TABLE `my_test` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` varchar(200) DEFAULT NULL,
  `phone` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `index_a_b` (`name`(20),`age`(5)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `my_test` VALUES (1, 'a', '1');
INSERT INTO `my_test` VALUES (2, 'a', '2');
INSERT INTO `my_test` VALUES (3, 'a', '3');
INSERT INTO `my_test` VALUES (4, 'b', '1');
INSERT INTO `my_test` VALUES (5, 'b', '2');

关于索引的图只是示意性展示,重在理解。

聚簇索引

聚簇索引(也可以简单理解为主键)的叶子节点存的是整行数据,而非聚簇索引的叶子节点存的是索引数据和主键。

未命名文件

非叶子节点都是主键,叶子节点中既有主键对应的行数据。

当使用主键查询数据的时候,实际就是查询聚簇索引,然后从其中把数据读出来返回。

联合索引

联合索引:就是多个列组成一个索引。比如 name 和 age 组成一个索引

联合索引

我们看到覆盖索引是按照 name,age 进行排序的,当 name 一样的时候,按 age 排序,并且叶子节点会有 id 的数据。

通过查询这个索引就可以得到对应数据行的数据主键,然后根据主键 id 查询聚簇索引得到整行数据。

覆盖索引

覆盖索引:也是联合索引。只是我们查询的数据就在索引中,不用再去 回表 查找数据了。

SELECT `name`,age FROM my_test WHERE `name` = b;

上述 sql 执行就利用了覆盖索引,查询的结果就在索引中。

SELECT * FROM my_test WHERE `name` = b;

索引中的数据只有 id,name,age,差 phone。这个数据只能通过回表去查询数据。

先查询 (name,age) 这个索引拿到主键 id,在通过主键 id 去聚簇索引中查询数据。

也有人经常推荐说,查询的时候要查询需要的字段,不要使用 select * ,这样做的好处一是减少 io,另一个就是避免回表。

Mysql 与磁盘交互的最小单位是 Page, B+ 树 中存储的实际是一页一页的数据,下面这张图可以近似理解。

图来自《MySQL 是怎样运行的:从根儿上理解 MySQL

img

索引的使用

了解了 B+ 树索引数据结构,我们也就差不多知道怎样使用索引了,也可以理解使用索引的一些规则。

通常说的索引失效,一部分是可以从数据结构来推算出来的,一部分就是 mysql 查询统计的数据显示不走索引性能会更高。我们可以通过查看索引的执行计划来判断 sql 怎么优化。

最左匹配规则

我们在使用索引的时候,只需要包含索引的最左边就可以匹配索引(name,age)

SELECT * FROM my_test WHERE `name` = 'b';

SELECT * FROM my_test WHERE `name` = 'b' AND age = 1;

当我们执行下面的 sql 的时候,就用不到索引 (name,age)

-- 通过查看执行计划,看查询的性能:全表扫描
EXPLAIN SELECT * FROM my_test WHERE age=1;

从索引的数据结构可以知道,B+ 树是按照 name 排序再按照 age 排序。age 实际是乱序的,没有办法进行范围查找。如果你还想在 age 进行索引查找,就需要在 age 上建立一个新索引。

-- 全表扫描
EXPLAIN SELECT * FROM my_test WHERE NAME LIKE '%a';

-- 索引范围查找
EXPLAIN SELECT * FROM my_test WHERE NAME LIKE 'a%';

索引的建立,不会整个字段值都参与索引的建立,一般会指定多长的字段(从值开头部分的长度)参与索引的建立。当我们不适用前缀匹配可能会有的数据查不到,这就是大问题了,这就是最左匹配规则。

当你需要关键字查找的时候,可以使用全文索引,或者是增加一个 ES 用于检索。通常不推荐 ES 代替关系型数据库进行数据存储,一方面 ES 不支持事务保证不了你业务数据的可靠性,另一方面 ES 大量的索引在数据库中才会性能很高。增加了 ES 同时也增加了架构的复杂性,不要为了一个小功能就要加 ES ,根据业务需求来判断。

image-20200926231208148


欢迎关注我的微信公众号【Mflyyou】获取持续更新。

github.com/zhangpanqin/MFlyYou 收集技术文章及我的系列文章,欢迎 Star。

文章作者: 张攀钦
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 张攀钦 !
评论
  目录