1.深入理解MySQL底层数据结构及Explain

Hz488
286 阅读16分钟

思维导图：点击查看思维导图
文章图片：点击查看图片

一.索引

什么是索引？索引的本质就是一种排好序数据结构。数据库中的列存在磁盘上，存储不是连续的，索引记录着这些列在磁盘上的位置（地址） 。同时索引也存储在磁盘上。

1.索引的数据结构

  • 二叉树
  • 红黑树
  • Hash
  • B-Tree
  • B+Tree

推荐一个数据结构在线演示网www.cs.usfca.edu/~galles/vis…. 下面我们来看一下这些数据结构

1.1.二叉树

当存储的数据为单边增长时，树太深，对于使用二叉树进行搜索时不太友好。

1.gif

1.2.红黑树

当数据量较大时，树的高度不可控 

2.gif

1.3.Hash

  • 很多时候使用 Hash 索引要比 B+ 树索引更高效
  • 不支持范围查询，仅支持 “=” 或 “in”
  • 对索引的 key 进行一次 Hash 计算就可以定位出数据存储的位置
  • Hash冲突问题

3.gif

1.4.B-Tree

  • 叶子节点具有相同的深度
  • 索引索引元素不重复
  • 节点索引树从左到右递增
  • 叶子节点没有指针 

4.gif

如果使用B-Tree作为索引 

image.png

1.5.B+Tree

6.gif B+Tree 作为索引

  • 非叶子节点不存储data，只存储索引(冗余索引)，每页可以放更多的索引
  • 叶子节点包含所有索引字段
  • 叶子节点用指针连接，提高区间访问的性能
  • 从左到右递增，使用 B+ 树优化后的结构，具有双向箭头指针，支持范围查找

image.png

为什么使用B+树作为索引数据结构而不使用B树？

  • 相比于B-Tree相同的高度下，B+Tree 能存储更多的数据：B-Tree 非叶子节点存储数据 ，而 B+ 树非叶子节点不存储数据（data），一颗高度为3的B+树大约能存储2千万+ 数据。
  • 变种 B+Tree 具有双向指针，支持范围查询，而 B-Tree 没有指针，范围查找速度慢（跨区间需重新检索）

MySQL 默认页文件大小16384（16K）假设一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为BigInt类型，即长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)。一颗高度为3的 B+ 树能存储的数据为：1170117016 = 21902400

低版本的MySQL根节点常驻内存，高版本的MySQL非叶子节点常驻内存，内存操作对比与磁盘IO时间几乎可以忽略不计，如果使用了索引先在内存中进行定位叶子节点位置，再将该区间的叶子节点进行磁盘 IO，Load 到内存进行查询。

3.常见的MySQL存储引擎

存储引擎一般都是表级生效，不同的表可以用不同的存储引擎进行存储

3.1.MyISAM

MyISAM索引文件和数据文件是分离的(非聚集/簇) 表存储在磁盘中，如果不改动，存储 MySQL 安装目录的该库的 data 目录下 对于 MyISAM 存储引擎用三个文件存储数据 xx.frm（表结构文件），xx.MYD （数据文件），xx.MYI（索引文件）  当我们使用一条 SQL 语句走 MyISAM 索引时：第一步先从 MYI 的 B+ 树结构找到 data （数据所在行磁盘地址），第二步有 MYD 文件找到具体的数据。 

image.png

3.2.InnoDB

InnoDB存储引擎索引与数据是不分离的（聚集/簇） 表存储在磁盘中，如果不改动，存储MySQL安装目录的该库的data目录下，对于 InnoDB 存储引擎用两个文件存储数据 xx.frm（表结构文件），xx.idb （数据文件+索引文件）  当我们使用一条SQL语句走 InnoDB 索引时：直接从 idb 的 B+ 树结构的叶子节点中找到具体数据

  • 表数据文件本身就是按 B+Tree 组织的一个索引结构文件
  • 聚集索引-叶节点包含了完整的数据记录
  • 一张表只有一个聚集索引（即主键索引） ，其他索引都是二级索引，叶子节点的 data 中存储叶子节点的主键值，由该值进行回表操作到主键索引中查找数据，这样既能满足快速找到数据也能保证数据的一致性和节约存储空间。如果没有唯一主键，则MySQL会选择唯一列，如果没有，会自己维护一个隐藏列（如rowid）作为聚簇索引。

主键索引

image.png 二级索引 

image.png

使用 InnoDB 作为存储引擎时，建议表必须建立一个主键(与业务无关的自增主键)，并且推荐使用整型的自增主键。

  • 如果不建立主键，MySQL 会找一列所有值都不相等的列来用于构建 B+ 树，如果没有这样的列，MySQL 会创建一个隐藏列来维护这个 B+ 树
  • 整型比长串的占用空间更少，比较大小时相对来说更容易，能更快的定义数据的位置
  • B+Tree 需要维护自身平衡，自增不会导致树分裂

单从索引角度来说，非聚集索引查找速度不如聚集索引，非聚集索引找到索引位置后还需要根据索引找到数据对应的位置

4.联合(复合)索引

联合索引的结构 

image.png

最左前缀匹配原则： 在 MySQL 建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

即：当 a,b,c 为联合索引时遵循最左匹配原则，即：a,ab,abc 索引都会生效,但 b,c,bc,ac 等不会生效（如果使用执行计划会可以看到，type 列为 index，扫描索引树，效率相对于最左匹配的索引效率极低），所以一定要注意索引顺序，最常用的最段要放在最前面。

  • 例如，创建一个idx_name_age_postion联合索引，它的索引树图如上图，由图可以看出 name 值是有序的，age 值是无序的，但是在name值相等的情况下age值又是有序的。由此可以看出MySQL创建联合索引时首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序,以此类推。所以age或postion单独作为条件时，索引是无效的。
  • 当a,b,c三个索引都用到时，只有全匹配，无论顺序如何，索引是有效的，MySQL执行计划会对其进行优化，自动使用最优方案执行。

二.Explain

1.Explain 使用详解

使用 Explain 关键字用于模拟优化器执行SQL语句，在select 语句之前增加 explain 关键字查看执行计划（不会执行这条 SQL）。 其后跟 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么，部分时候优化后的SQL是无法执行的。

注意： 如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 。 explain select * from student_inndb where serialNo=1; show warnning ;

image.png

1.1Explain 中的列

三个将要用来作为示例的表 sys_role 角色表 

image.png sys_user 用户表

image.png

sys_user_role 用户角色关联表 

image.png

1.1.1.id

select 的序列号，有几个 select 就有几个 id，id 的顺序是按 select 出现的顺序增长的。即：id 越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

1.1.2.select_type

对应行是简单还是复杂的查询。它有以下几种类型：

  1. simple 简单查询。查询不包含子查询和union。 explain select * from student_inndb where serialNo ='1'; image.png
  2. primary 复杂查询中最外层的 select。
  3. subquery 包含在 select 中的子查询（非 from 子句中的）
  4. derived 包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义） 版本较高的 MySQL 会对衍生表进行合并优化，展示前先关闭衍生表优化 set session optimizer_switch='derived_merge=off'; #on表示打开  再执行以下 SQL 进行演示 explain select (select 1 from sys_role where id = 1) from (select * from sys_role where id = 1) derive;image.png
  5. union 在 union 中的第二个和随后的 select explain select * from student_inndb where serialNo = '1'union all select * from student_inndb where serialNo='2'; image.png

1.1.3.table

这一行正在访问哪个表。当 from 子句中有子查询时，table列是<derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为 <union1,2>，1和2表示参与 union 的 select 行 id。 explain select * from student_inndb where serialNo = '1'union select * from student_inndb where serialNo='2';image.png

1.1.4.partitions

高版本 MYSQL 使用 explain 直接就可以展示的列，低版本MySQL使用explain partitions才能展示。若查询是基于分区表的，会显示查询将访问的分区。

1.1.5.type

这一列非常非常重要，表示关联类型或访问类型。即 MySQL 决定如何查找表中的行，查找数据行记录的大概范围。最优查询等级划分为：system > const > eq_ref > ref > range > index > ALL。一个好的 SQL语句至少要达到range级别，最好达到 ref。杜绝出现 ALL 级别。

NULL类型：一个特例，MySQL 执行时，部分查询没有必要访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。此类型的查询语句效率也是极高的。 explain select min(serialNo) from student_inndb; 

image.png 下面来描述具体类型：

  1. system 表只有一行记录，const 类型的特例，基本不会出现，可以忽略。 explain select * from (select * from sys_role where id = 1) tmp; image.png
  2. const 通过索引一次就查询出来了，const 用于比较主键索引（primary key）或唯一索引（unique key）。只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const。 explain select * from sys_user where id=1; image.png
  3. eq_ref primary key或unique key索引的所有部分被连接使用 ，唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。简单的 select 查询不会出现这种类型。 explain select * from sys_user_role left join sys_role on sys_user_role.role_id = sys_role.id; image.png
  4. ref 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。 a.简单查询，user_id 为联合索引前缀explain select * from sys_user_role where user_id = 1;image.png b.关联表查询，idx_role_user_id是role_id和user_id的联合索引explain select role_id from sys_role left join sys_user_role on sys_role.id = sys_user_role.role_id;image.png
  5. range 范围扫描通常出现在 in, between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引。 explain select * from sys_user where id > 0; image.png
  6. index 只遍历全索引树就能拿到结果，一般是扫描某个二级索引。这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用了覆盖索引 ，二级索引一般比较小，所以通常比 ALL 快。 explain select * from sys_role; image.png
  7. ALL 全表扫描，扫描你的聚簇索引的所有叶子节点。这种情况通常应该被杜绝，这是就要考虑添加索引来进行优化。 explain select * from sys_user; image.png

1.1.6.possible_keys

查询时可能使用哪些索引来查找。该列值为 NULL，则未使用到索引。 有时会出现 possible_keys 有值，而 key 值为 NULL 的情况，这是因为表中数据不多，MySQL 认为索引对此查询帮助不大，而选择全表查询。

1.1.7.key

用哪个索引来优化。如果没有使用索引，则该列是 NULL。如果想强制 MySQL 使用或忽略possible_keys 列中的索引，在查询中使用 force index、ignore index。

1.1.8.key_len

MySQL在索引里使用的字节数。通过这个值可以算出具体使用了索引中的哪些列。explain select * from sys_user_role where role_id = 1;  image.png 通过该值，可以推算出该查询使用了 idx_role_user_id 的 role_id 来进行索引查找。 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个字节记录
  • 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引

1.1.8.ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：sys_role.id）

1.1.9.rows

扫描行数。该值是预估值。非结果集里面的行数。

1.1.10.filtered

高版本 MYSQL 使用 explain 直接就可以展示的列，低版本MySQL使用explain extended才能展示，一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）。

1.1.11.Extra

额外的详细说明信息。常见的不太友好的值有：Using filesort，Using temporary。

1）Using index：使用覆盖索引覆盖索引定义：查询的结果集在索引树里面全部包含，无需回表，这种情况一般可以说是用到了覆盖索引，extra里一般都有 using index；覆盖索引一般针对的是辅助（二级）索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值 explain select name from sys_role; name在索引树中可以直接找到

image.png 2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖explain select * from sys_user where name = 'a';

image.png 3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；explain select * from sys_user_role where role_id > 1; 

image.png

4）Using temporary：mysql需要创建一张临时表来处理查询。这种情况首先就要想到使用索引进行优化对比下面两条SQL： explain select distinct name from sys_user; explain select distinct name from sys_role; 第一条SQL的name无索引，需要临表来去重，第二条SQL的name有索引，无需临时表 

image.png

5）Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，数据量大时需要在磁盘进行排序。一般考虑使用索引优化。对比下面两条SQL： explain select * from sys_user order by name; explain select * from sys_role order by name; 第一条SQL的name无索引，会检索整个表，保存排序关键字name和对应的id，然后排序name并检索行记录，第二条SQL的name有索引，且表中只又id,name两个索引字段，使用覆盖索引，第三张图片，是添加了一个字段后生成的，由于查询所有，需要检索整个表。

image.png

6）Select tables optimized away：使用了聚合函数（比如 max、min）访问某个索引字段对比下面两条SQL： explain select max(name) from sys_user; explain select max(name) from sys_role; 

image.png

三.关于索引树的SQL优化相关建议

CREATE TABLE `student_inndb` (
  `serialNo` varchar(32) NOT NULL COMMENT '流水号',
  `name` varchar(40) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `idCard` int(11) DEFAULT NULL COMMENT '身份证号码',
  `sex` varchar(10) DEFAULT NULL COMMENT '性别',
  PRIMARY KEY (`serialNo`),
  KEY `name_age_idcard_idx` (`name`,`age`,`idCard`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1.全值匹配

#全值匹配
explain select serialNo,name,age,idCard,sex from student_inndb

2.字符串不加单引号索引失效

#有效使用索引
explain select * from student_inndb where idCard = '123456';
#索引失效
explain select * from student_inndb where idCard = 123456;

3.最左前缀法则

4.复合索引中范围条件右边的列的索引不能被使用

#使用索引name_age_idcard_idx
explain select * from student_inndb where name ='张三' and age = 20 and idCard = '123456';
#使用name_age_idcard_idx 的前缀name_age
explain select * from student_inndb where name ='张三' and age > 20 and idCard = '123456';

5.尽量使用覆盖索引，减少 select * 语句

6.MySQL 使用 !=， <>，not in ，not exists 会导致索引失效，从而全表扫描。使用< 、> 、<=，>=时 MySQL 内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

7.不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描

8.对于 is null，is not null 一般情况下也无法使用索引

9.对于 like 以通配符开头 '%xxx' 可能会导致索引失效而进行全表扫描（借助搜索引擎或覆盖索引优化）     

  • 对于 like 不以通配符开头 'xxx%' 会使用下面的索引 explain select * from student_inndb where name like '张%'; image.png

  • 对于 like '%xxx' 如果使用了覆盖索引 explain select name,age,idCard from student_inndb where name like '%张%'; image.png

  • like 'xx%'相当于=常量，'%xx' 和 '%xx%'相当于范围

10.范围查询优化：MYSQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。对于大范围的查询可以将其拆分为多个小范围查询

11.如果查询的结果集没多少，走索引还需要回表操作，MySQL 内部优化器可能会直接让该查询走全部扫描

总结

WHERE语句（a_b_c_idx 为索引）索引是否被使用
where a = 3Y，使用到 a
where a = 3 and b = 4Y，使用到a,b
where a = 3 and b = 4 and c = 5Y，使用到a,b,c
where b = 3 或 c = 3 或 b = 3 and c = 4N
where a = 3 and c = 4Y，使用到a
where a = 3 and b > 4 and c = 5Y，使用到a,b
where a = 3 and b like 'xx%' and c = 4Y，使用到a,b,c
where a = 3 and b like '%xx' and c = 4Y，使用到a,
where a = 3 and b like '%xx%' and c = 4Y，使用到a,
where a = 3 and b like 'x%xx%' and c = 4Y，使用到a,b,c
avatar
文章
阅读
粉丝