1、存储引擎

1.1 MySQL体系结构

1). 连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程 池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务 器也会为安全接入的每个客户端验证它所具有的操作权限。

2). 服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部 分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。在该层，服务器会解 析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等， 最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大， 这样在解决大量读操作的环境中能够很好的提升系统的性能。

3). 引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通 信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库 中的索引是在存储引擎层实现的。

4). 存储层

数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询 日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

1.2 存储引擎介绍

存储引擎是mysql数据库的核心，是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，理论来说，一个数据库的不同表可以使用不同的存储引擎，不过我们一般不会这么做。

目前MySQL支持多种存储引擎，可以使用如下命令查询MySQL支持的存储引擎

1
SHOW ENGINES;

image-20230905153648547

目前最主要存储引擎是InnoDB

1.3 InnoDB

1). 介绍

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的 MySQL 存储引擎。

2). 特点

  • 支持事务；
  • 行级锁，提高并发访问性能；
  • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

3). 文件

当一张表存储在一个文件中时，称为独立表空间

当多张表存储在一个文件中时，称为共享表空间

当使用独立表空间存储时，一张表对应一个xxx.ibd文件，xxx代表的是表名，这个.ibd文件保存了表的数据、结构和索引。文件存储在对应的数据库名称的文件下，如：

image-20230905165627691

这个ibd文件中不仅存放表结构、数据，还会存放该表对应的索引信息。 而该文件是基于二进制存储的，不能直接基于记事本打开，我们可以使用mysql提供的一 个指令 ibd2sdi ，通过该指令就可以从ibd文件中提取sdi信息，而sdi数据字典信息中就包含该表的表结构。

1
ibd2sdi user.ibd

image-20230905170601761

image-20230905170719613

当使用共享表空间存储时，表数据都存储在ibdata1文件中。

image-20230905165659863

可以通过以下sql查看MySQL是使用独立表空间还是共享表空间

1
show variables like 'innodb_file_per_table';

image-20230905155726397

当value是ON时，是独立表空间，这也是MySQL默认的值。当value是OFF时，则是共享表空间

也可以手动设置为共享表空间

1
set global innodb_file_per_table=0;

此时再进行查询，结构如下：

image-20230905160948810

扩展：

在MySQL8以前，使用.frm 文件存储表的结构和定义，包括表的列名、数据类型、索引等信息。在MySQL8后，使用sdi序列化的数据字典—-> 每个表的表空间自行管理json格式的私有数据字典信息，用来替换.frm。这个更改是为了提高数据字典的性能和可扩展性，并简化MySQL系统表的管理。

4). 逻辑存储结构

img

  • 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以 包含多个Segment段。
  • 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管 理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
  • 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为 16K， 即一个区中一共有64个连续的页。
  • 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
  • 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时 所指定的字段以外，还包含两个隐藏字段(后面会详细介绍)。

2、索引

2.1 索引概述

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构 上实现高级查找算法，这种数据结构就是索引。

image-20230906160138916

2.2 索引举例

这里有一张user表，表的结构和数据如下：

image-20230906163315958

假如我们要执行的SQL语句为 ： select * from user where age = 45;

1). 无索引情况

image-20230906165146617

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低。

2). 有索引情况

如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。

image-20230907090946429

此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

注：这里是假设索引结构是二叉树，仅仅是假设

2.3 索引结构

2.3.1 MySQL支持的索引结构

索引 InnoDB MyISAM Memory
B+tree索引 支持 支持 支持
Hash 索引 不支持 不支持 支持
R-tree 索引 不支持 支持 不支持
Full-text 5.6版本之后支持 支持 不支持

注： 我们平常所说的索引，如果没有特别指明，都是指B+树索引

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

2.3.2 二叉树

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

image-20230907105444917

但是，如果是按顺序插入的话，就会出现如下的情况：

image-20230907105107996

所以，如果选择二叉树作为索引结构，会存在以下缺点：

  • 顺序插入时，会形成一个链表，查询性能大大降低。
  • 大数据量情况下，层级较深，检索速度慢。

2.3.3 红黑树

红黑树是对二叉树的平衡改进，通过给每个节点增加一个颜色标识，每个节点颜色不是黑色就是红色，按一定的规则进行位置安排就能控制以每个节点为根的最长路径不大于最短路径的二倍，从而控制树的整体高度。

image-20230907110316681

但是，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

  • 大数据量情况下，层级较深，检索速度慢。

2.2.4 B-Tree

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。 以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：

image-20230907112905390

树的度数指的是一个节点的子节点个数。

特点：

  • 5阶的B树，每一个节点最多存储4个key，对应5个指针。
  • 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
  • 在B树中，非叶子节点和叶子节点都会存放数据。

2.2.5 B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一 下其结构示意图：

image-20230907114926882

我们可以看到两部分：

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree相比，主要有以下三点区别：

  • 所有的数据都会出现在叶子节点。
  • 叶子节点形成一个单向链表。
  • 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

B+树的链表设计有几个重要的优点和用途：

  1. 范围查询效率高：由于叶子节点之间是按照顺序链接的，可以轻松地从一个叶子节点开始，按顺序遍历链表以获取一个范围内的所有数据。
  2. 节省磁盘I/O：当执行范围查询时，如果叶子节点之间没有链表，数据库系统可能需要进行更多的磁盘读取操作以获取范围内的所有数据。而有了链表，只需顺序遍历链表，减少了随机磁盘I/O，提高了查询性能。
  3. 便于维护：当插入或删除数据时，只需调整相邻叶子节点的链表指针，而不需要重新平衡整棵树。这降低了维护成本，使得B+树在高度动态的数据集中更具优势。
  4. 提供有序性：B+树的叶子节点是按顺序链接的，这使得B+树非常适合需要有序数据的应用场景，例如数据库索引。有序性对于某些查询操作和范围查询非常有用，可以减少排序操作的需求。
  5. 方便全盘扫描

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

image-20230907121039757

2.4 索引分类

2.4.1 索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引、空间索引。

分类 含义 特点 关键字
主键索引 针对于表中主键创建的索引 默认自动创建, 只能有一个 PRIMARY
唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE
常规索引 快速定位特定数据 可以有多个 NORMAL
全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT
空间索引 空间数据类型的索引，只能在MyISAM创建 可以有多个 SPATIAL

2.4.2 聚集索引&二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类 含义 特点
聚集索引(Clustered Index) 将数据存储与索引放到了一块，索引结构的叶子 节点保存了行数据 必须有,而且只 有一个
二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关 联的是对应的主键 可以存在多个

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

聚集索引和二级索引的具体结构如下：

image-20230907144624051

  • 聚集索引的叶子节点下挂的是这一行的数据 。
  • 二级索引的叶子节点下挂的是该字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

image-20230907145030584

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。

③. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

2.4.3 主键索引B+树推导

我们已经知道，InnoDB最小的存储单位页的大小是16K，假设主键是bigint类型，长度8字节，指针长度固定6字节，一行数据大小为1K，忽略innoDB页中其他占用空间的部分(很小)，推导一下不同的树高能保存多少数据。

①. 当树高=1时，此时使用一个叶子节点，因此可以存储的数据行数为：节点容量/每行数据大小=16K/1K=16（行）

②. 当树高=2时，这时只有根节点和叶子节点，根节点中存放主键和指针，叶子节点存放数据。

image-20230907155414175

每个叶子节点可以存放的数据行数按照上面的计算为16行。

假设根节点总的指针数为n，总的主键数为n-1，一个指针占6个直接，一个主键占8个字节，这有：n*8+(n-1)6=16K，计算得n约为1170。也就是说，根节点有1170个指针，那么就有1170个叶子节点，因此，可以存储的总数据行数=161170=18720（行）

③. 当树高=3时：

image-20230907173916061

按上述推算方式得出，树高=3时，可以存放的数量行数=单个叶子节点存放数据行数 X 非叶子节点指针数量 X 根节点指针数量 = 16 X 1170 X 1170 = 21902400（行）。也就是两千多万行。

2.5 SQL性能分析

2.5.1 SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信 息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

1
2
3
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE 'Com_______';

image-20230907175447115

2.5.2 慢查询日志

1). 开启sql的慢查询日志

MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

1
SHOW VARIABLES LIKE "%slow_query_log%"

查询结果如下：

变量名 值 说明
slow_query_log OFF ON开启，OFF关闭
slow_query_log_file D:\software\mysql\mysql-8.0.34-winx64\data\DESKTOP-S8QB488-slow.log 慢查询日志位置

手动设置开启慢查询日志

1
2
set global slow_query_log=ON;  # 设置开启sql慢查询日志
set global long_query_time=0.001;  # 设置0.001s就算是慢sql

然后运行一个sql，在对应的慢查询日志文件中查看到记录

image-20230908161442684

手动设置开启慢sql日志，会在MySQL服务重启时失效，要永久设置的话，还是要在配置文件设置

2). 使用druid的慢sql监控查看

image-20230908155414301

2.5.3 profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：

1
SELECT @@have_profiling;

image-20230908162057771

查看开关是否已经打开，如果开关未打开，需要使用set global profiling = 1;进行打开

1
SELECT @@profiling;

image-20230908163911411

打开开关后，可以使用以下sql查看sql的执行情况

1
2
3
4
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

2.5.4 explain查询执行计划

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

1
2
-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

image-20230911161815588

字段 含义
id select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)
select_type 表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等
type 表示连接类型，性能由好到差的连接类型为system、const、eq_ref、ref、range、 index、all等
possible_key 显示可能应用在这张表上的索引，一个或多个
key 实际使用的索引，如果为NULL，则没有使用索引
key_len 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好
ref 显示索引的哪一列被使用了，有时候会是一个常量，表示哪些列或常量被用于用于查找索引列上的值
rows MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered 表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好

type字段：

  • system: 查询只有少量数据的系统表，往往不需要进行磁盘IO

  • const: 查询条件是主键或者唯一索引等于常量。如explain select * from user where id=1

  • eq_ref: 查询条件使用了主键或者唯一索引的等值查询。如EXPLAIN SELECT u.*, ur.* FROM user_role ur LEFT JOIN user u ON u.id= ur.user_id。这条sql要注意，使用user_role 作为驱动表，user查询的type才会是eq_ref。

  • ref: 查询条件使用了普通索引的等值查询。

  • range: 在索引上的范围查询。

  • index: 在索引上的全表扫描，要扫描索引上的全部数据，它仅比全表扫描快一点。

  • all: 全表扫描

拓展1：使用EXPLAIN+SHOW WARNINGS查看执行计划的拓展信息

如下例子：

1
2
3
4
EXPLAIN
SELECT * FROM `user` WHERE id IN (SELECT user_id FROM user_role);

SHOW WARNINGS;

输出的拓展信息为：

1
2
3
4
5
6
7
8
9
10
SELECT
	`test`.`user`.`id` AS `id`,
	`test`.`user`.`name` AS `name`,
	`test`.`user`.`age` AS `age` 
FROM
	`test`.`user` semi
	JOIN ( `test`.`user_role` ) 
WHERE
	(
	`test`.`user`.`id` = `test`.`user_role`.`user_id`)

可以看到，MySQL将子查询优化为了表的连接查询

拓展2：MySQL8.0.16引入了EXPLAIN FORMAT=TREE语法，在MySQL8.0.18又引入了EXPLAIN ANALYZE语法。两者的输出差不多，只是EXPLAIN ANALYZE多了执行实际，返回行数、循环次数等。如：

1
2
3
4
5
EXPLAIN ANALYZE
SELECT u.*, r.* FROM `user` u
JOIN user_role ur ON ur.user_id=u.id
JOIN role r ON r.id=ur.role_id
WHERE u.name="张三";

结果为：

1
2
3
4
5
-> Nested loop inner join  (cost=0.7 rows=1) (actual time=0.0148..0.0171 rows=1 loops=1)
    -> Filter: ((ur.user_id = '1') and (ur.role_id is not null))  (cost=0.35 rows=1) (actual time=0.0079..0.01 rows=1 loops=1)
        -> Table scan on ur  (cost=0.35 rows=1) (actual time=0.0065..0.0084 rows=1 loops=1)
    -> Single-row index lookup on r using PRIMARY (id=ur.role_id)  (cost=0.35 rows=1) (actual time=0.0053..0.0053 rows=1 loops=1)

返回结果的阅读顺序为：

  1. 从右到左：没有遇到并列的迭代器之前，都是从右边开始执行；

  2. 从上到下：遇到并列的迭代器，都是上边的先开始执行。

    后面第3部分的多表连接有例子

2.6 索引使用

2.6.1 最左前缀法则

3、多表连接

我们已经知道，当我们执行select * from user where name="张三"时，执行方式是先到name索引去寻找“张三”的值，如果user表还有其他字段，就会回表查询聚集索引得到数据。那当进行多表查询时，执行方式又是如何的？

1
2
3
4
SELECT u.*, r.* FROM `user` u
JOIN user_role ur ON ur.user_id=u.id
JOIN role r ON r.id=ur.role_id
WHERE u.name="张三"
1
2
3
4
5
EXPLAIN FORMAT=TREE
SELECT u.*, r.* FROM `user` u
JOIN user_role ur ON ur.user_id=u.id
JOIN role r ON r.id=ur.role_id
WHERE u.name="张三"