Mysql数据结构与EXPLAIN详解

田青钊

已于 2023-07-17 15:15:14 修改

阅读量114

点赞数

文章标签： mysql 数据结构数据库

于 2023-07-17 11:48:35 首次发布

本文链接：https://blog.csdn.net/weixin_44963129/article/details/130844495

版权

文章详细介绍了各种索引数据结构，包括二叉树、红黑树、Hash、B-树和B+树，并探讨了它们在数据库中的应用，特别是MySQL的InnoDB存储引擎如何利用这些数据结构实现高效查询。此外，还讨论了聚集索引与非聚集索引、主键选择以及EXPLAIN命令在查询优化中的作用。

摘要由CSDN通过智能技术生成

索引数据结构二叉树、红黑树、Hash、B-树、B+树详解

索引数据结构可视化网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

二叉树

每个节点是key-value结构，每个节点包含索引键值和一个指向对应数据记录物理地址的指针。二叉树的特性是左边元素小于它，右边元素大于它。
在这里插入图片描述
如果是主键顺序增长的，二叉树查找速度还是会和原来一样。

红黑树

类似于二叉树，只不过会自动平衡，但是数据量一旦过大，数的高度就会很大。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TBPLkCAb-1684902106888)(C:\Users\l\AppData\Roaming\Typora\typora-user-images\image-20211010135538034.png)]$

Hash索引

根据字段的值进行一次hash计算，根据计算出来的hash值放在所在的hash表里面，如果hash碰撞就继续往该hash值后面放。查找的时候也是根据hash值找到索引所在的磁盘文件地址。

hash类型索引对于等值查询要比B+Tree类型索引快，比如 select * from t_user where name = 'zhangsan'; ，因为它只需要经过一次hash运算即可，但是hash类型索引不支持范围查找，因为它要根据每列的hash值挨个进行比较。而B+Tree每个叶子节点通过指针相连，比如寻找某一列大于20，只需要该列所在的索引位置，根据该索引位置指针把后面所有节点的数据都拿出来即可。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0cSFhv2o-1684902106888)(E:\技术文章\mysql\hash索引.png)]$

B-Tree

在红黑树的基础上，每个节点可以存放多个数据。
在这里插入图片描述

B+Tree

非叶子节点不存储data，只存储索引；叶子节点包含所有索引字段和data；叶子节点用指针连接，提高区间访问的性能。mysql默认每页的存储大小为16kb，可以通过 show global status like 'innodb_page_size'; 来进行查看。，每次IO读取16K大小的数据，以索引列是bigInt类型为例，大小8字节，每一条数据还有一个指向下一层的指针6字节，16384/（8+6）=1170，一个节点就大约可以存1170条数据。以一个层高为3的树为例，叶子节点存放数据之后大小1KB，那么这个树可以存放 1170 *1170 *16 =21,902,400,大约2200万条数据。所以在这种千万级的表中通过主键索引查找一条数据，最多3次IO就可以找到一条数据。而很多时候树的根节点基本都是在内存中，所以多数时候只需要2次IO。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MHxkL0Sa-1684902106890)(E:\技术文章\mysql\B+树演示.png)]$

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p68Az9Sm-1684902106890)(E:\技术文章\mysql\B+树.png)]$

mysql为什么采用B+树没有采用B树：每个节点数据大小为1kb，每页为16kb，也就是每页最多存储16条数据，2200万数据大概要6次多。而B+树最多只需要3次。

聚集索引(聚簇索引)、稀疏索引到底是什么

innodb
主键的索引:密集索引,聚簇索引/聚集索引,
非主键索引:稀疏索引,非聚集索引/辅助索引,二级索引

主键索引(聚簇索引): 叶子节点存的是整行数据是密集索引。
聚集索引：叶节点包含了完整的数据记录。如果没有设置聚集索引，默认使用主键来作为聚集索引。

非主键索引(二级索引): 叶子节点内容是主键的值, 是稀疏索引。
非聚集索引：索引文件和数据文件分离的理论上聚集索引比非聚集索引查找速度要快。

为什么DBA总推荐建立索引并使用自增主键做索引

使用索引查找的时候，整形的是直接比较大小，而像uuid之类的，要根据ASIII码去进行转换再进行比较大小。

整形的存储空间也比uuid生成的字符串占用的空间小，节省服务器的内存。

在B+Tree结构中，自增的索引会一直往节点后面排，而非自增的主键索引可能新添加一个的时候要往一个放满了节点去做一个平衡，所以自增的效率要高。

联合索引底层数据结构又是怎样的

单列索引其实也可以看做联合索引，索引列为1的联合索引，从下图就可以看出联合索引的底层存储跟单列索引时类似的，区别在于联合索引是每个树节点中包含多个索引值，在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配，匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完；如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列，前往下一个节点。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ziai7TVq-1684902106892)(E:\技术文章\mysql\联合索引.png)]$

Mysql最左前缀优化原则是怎么回事

最左前缀索引：比如一张表建立了三个字段name、age、position作为索引，可以使用name、name和age、name和age和position作为索引，但是不能跳过name使用其他两列直接作为索引。因为不是所有列的name都相等，就不符合索引排好序的原理了。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OMxb0tg4-1684902106893)(E:\技术文章\mysql\最左前缀索引.png)]$

Mysql存储引擎

存储引擎是以表为单位，默认 Innodb。

Myisam存储引擎

分为三个文件结构，以 .frm 文件名结尾的表示框架，以 .MYD 文件名结尾表示存储的是数据，以 MYI 文件名结尾的表示为索引存储的。也就是Myisam存储引擎是非聚集索引。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yxy2pGgA-1684902106894)(E:\技术文章\mysql\t_myisam文件结构.png)]$

根据B+树自动平衡索引所在的节点，根据索引去 MYI 文件里面拿到节点的data(也就是数据所在的磁盘文件地址)，再根据磁盘文件地址去 MYD 文件里面定位具体的数据行。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1QqQG1R2-1684902106895)(E:\技术文章\mysql\Myisam存储引擎实现.png)]$

InnoDB存储引擎

分为两个文件结构，以 .frm 文件名结尾的表示框架，以 ibd 文件结构的表示为索引和数据所在的文件。InnoDB存储引擎是聚集索引的。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4j13gb0q-1684902106896)(E:\技术文章\mysql\innodb存储引擎结构.png)]$

叶子节点存储了索引和数据

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f11dVakZ-1684902106897)(E:\技术文章\mysql\innodb存储数据.png)]$

InnoDB存储引擎建议建一个主键，且整张表只有一个聚集索引，默认主键就是索引，如果没有主键的情况下会从每行数据的第一列对比，如果都不相同，就是用该列作为主键，如果选不到的情况下，mysql会自动建一个隐藏列，类似于row_id一样，这个隐藏列会帮助我们维护一个唯一的id。

非主键索引叶子节点存储的是主键索引值。也就是先去 ibd 文件里面找到二级索引所在节点的data，然后根据二级索引所在节点的data再去 ibd 文件里面根据主键索引找到数据。大概要经过一个回表的过程。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EU1Gw8jv-1684902106897)(E:\技术文章\mysql\非主键索引.png)]$

Explain详解

示例建表语句：

DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22
15:27:18'), (2,'b','2017‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

partitions

字段表示分区，一般都采用分库分表

filtered

字段表示返回结果的行占需要读到的行(rows列的值)的百分比。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-azO8zBK6-1684902106906)(E:\技术文章\mysql\explain1.png)]$

show warnings

一条explain的sql后面加上 SHOW WARNINGS 关键字之后，会出来一个结果2的，此处的message字段会展示mysql对这个sql的优化。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iEMs3pyA-1684902106907)(E:\技术文章\mysql\showwarings.png)]$

select_type

SIMPLE：简单查询，比包含子查询和union，连接查询也是SIMPLE

explain select * from film where id = 2;

PRIMARY：复杂查询中最外层的select
SUBQUERY：包含在select中的子查询，不在from子句中
DERIVED：在from语句中的子查询，MYSQL会存储在一个临时表，也叫派生表，derived的英文意思

set session optimizer_switch='derived_merge=off'; -- 关闭mysql5.7新特性对衍生表的合并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
set session optimizer_switch='derived_merge=on'; #还原默认配置

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wqgSLsbu-1684902106908)(E:\技术文章\mysql\select_type.png)]$

union：在 union 中的第二个和随后的 select

explain select 1 union all select 1;

id

id值越大，执行的优先级越高，有几个select就有几个id，id相同则从上往下执行，id为空最后执行

table

表示访问的表名

type

Null：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可
以单独查找索引来完成，不需要在执行时访问表
```
 explain select min(id) from film;
```
system：const的一种特例，查询的表里面只有一条数据时候就是system
const：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于
primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是
const的特例，表里只有一条元组匹配时为system
```
explain extended select * from (select * from film where id = 1) tmp;
```
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。这可能是在
const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
```
explain select * from film_actor left join film on film_actor.film_id = film.id;
```
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会
找到多个符合条件的行。
1. 简单 select 查询，name是普通索引（非唯一索引）
```
 explain select * from film where name = 'film1';
```
2. 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
```
explain select film_id from film left join film_actor on film.id = film_actor.film_id;
```
range：范围查询，> < >= <= between and in()，但是sql是使用了索引查询的
```
explain select * from actor where id > 1;
```
index：扫描全表，也就是叶子节点从左到右依次扫描的，key那一列一般是我们给某一个二级索引起的名字，为什么不是主键索引或者唯一索引？因为主键索引下面的data是整行记录的数据，数据量比较大，而二级索引的叶子节点的data只是主键索引的数据，所以显示二级索引的。
```
explain select * from film;
```
ALL：全表扫描，扫描聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
```
 explain select * from actor;
```

possible_keys

这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引
对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提
高查询性能，然后用 explain 查看效果。

key

使用到了哪个索引名称。如果没有使用索引，则该列为null。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force
index、ignore index。

key_len

用到的索引类型占多个长度，比如int类型的占用4个字节，用到一个int类型的索引key_len就是4，用到两个int类型的索引key_len就是8。每种类型占用多少个字节与编码格式也有关系。对于允许NULL值的索引列，也会占用一个字节，key_len也就是1。

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字
或字母占1个字节，一个汉字占3个字节
- char(n)：如果存汉字长度就是 3n 字节
- varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型
- tinyint：1字节
- smallint：2字节
- int：4字节
- bigint：8字节
时间类型
- date：3字节timestamp：4字节
- datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL。索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

索引关联的字段，也有可能是一个常量值

const：常量值
字段名

rows

要读取并检测的行数，该行数并不是结果集里面的行数。

Extra

Using index：覆盖索引，它并不是一种索引方式，而是要查询的列是否只查询了索引，也就是是否需要根据查询的索引列去主键索引里面回表查询其他列，如果不需要回表就是Using index。覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。
```
 explain select film_id from film_actor where film_id = 1;
```
Using where：使用了where关键字，并且查询的列没有被索引覆盖。
```
 explain select * from actor where name = 'a';
```
Using index condition：查询的列没有使用索引，并且where条件是一个前导列范围。
```
explain select * from film_actor where film_id > 1;
```
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索
引来优化。
- 1. actor.name没有索引，此时创建了张临时表来distinct
```
explain select distinct name from actor;
```
- 1. film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
```
explain select distinct name from film;
```
Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一
般也是要考虑使用索引来优化的。
- 1. actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
```
explain select * from actor order by name;
```
- 1. film.name建立了idx_name索引,此时查询时extra是using index
```
explain select * from film order by name;
```
Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是
```
explain select min(id) from film;
```