Mysql 结构、引擎、执行过程

2022-03-29 12:04:16

首先引入一个问题。
有个最简单的表，表里只有一个 ID 字段，在执行下面这个查询语句时：

select * from T where ID = 10；

这条语句在 MySQL 内部的执行过程是怎样的？

下面引用林晓斌大佬给出的 MySQL 的基本架构示意图，从中可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程。

Mysql 结构、引擎、执行过程

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括 连接器、查询缓存、分析器、优化器、执行器 等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持前面说过的 InnoDB 还有 MyISAM、Memory 等多个存储引擎。

连接器

连接器是msyql server层的第一个模块，它负责跟客户端 建立连接、获取权限、维持和管理连接。

连接命令一般是这么写的：

mysql -h$ip -P$port -u$user -p

连接命令中的 mysql 就是客户端工具，客户端和服务器端的连接使用的是TCP协议，在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

  • 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
  • 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

使用

show processlist

可以查看系统所有连接的信息。
Mysql 结构、引擎、执行过程

其中 Command 列表示连接的状态。连接完成后，如果你没有后续的动作，这个连接就处于空闲状态（Sleep）。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

解决方案有两种：

  • 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
  • . 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行
    mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立完成，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果对应地以 key-value 对的形式，直接缓存在内存中的引用表里，key是一个哈希值引用，这个哈希值包括了以下因素，即查询语句本身、当前要查询的数据库、客户端协议的版本等一些其他可能影响返回结果的信息。value是查询的结果。

如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。可以看到，如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

但是大多数情况下不建议使用查询缓存，因为查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你好不容易花费了些时间空间把结果存起来，还没使用，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。

除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

MySQL 考虑到这点，提供了这种“按需使用”的方式：将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

select SQL_CACHE * from T where ID = 10；

有关查询缓存的配置如下所示：

  • query_cache_type：是否打开查询缓存。可以设置为OFF、ON和DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才会放入查询缓存。
  • query_cache_size：查询缓存使用的总内存空间。
  • query_cache_min_res_unit：在查询缓存中分配内存块时的最小单元。较小的该值可以减少碎片导致的内存空间浪费，但是会导致更频繁的内存块操作。
  • query_cache_limit：MySQL能够查询的最大查询结果。如果查询结果大于这个值，则不会被缓存。因为查询缓存在数据生成的时候就开始尝试缓存数据，所以当结果全部返回后，MySQL才知道查询结果是否超出限制。超出之后，才会将结果从查询缓存中删除。

缓存命中率可以通过这个公式：Qcache_hits/(Qcache_hits + Com_select) 来计算。

需要注意的是以下几点：

  1. MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。
  2. 当判断缓存是否命中时，MySQL不会进行解析查询语句，而是直接使用SQL语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。
  3. 当查询语句中有一些不确定的数据时，则不会被缓存。如：任何用户自定义函数，存储函数，用户变量，临时表，mysql数据库中的系统表或者包含任何列级别权限的表，都不会被缓存。
  4. 返回结果前依旧会校验权限。

分析器

如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。它通过关键字将SQL语句进行解析，并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。

例子中，MySQL 从输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

做完了这些识别以后，就要做“语法分析”，也叫“预处理”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。例如检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义。如果你的语句不对，就会收到“++You have an error in your SQL syntax++”的错误提醒。

优化器

经过了分析器，MySQL 就知道你要做什么了。但是在开始执行之前，还要先经过优化器的处理。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是将解析树转化成这其中最好的执行计划。

例如在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

举个栗子：

select * from t1 join t2 using(ID) where t1.c = 10 and t2.d = 20;

执行逻辑可以是：

  • 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
  • 也可以先从表 t2 里面取出 c=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。它会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。由上可知，CBO中有两个依赖：统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。

有关优化器的原理十分复杂，有时间的话我再搬运上来。

执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示。

ERROR 1142 (42000): SELECT command denied to user ‘b‘@‘localhost‘ for table ‘T‘

如果有权限，就打开表继续执行。打开表的时候，优化器就会根据表的引擎定义，去使用这个引擎提供的接口，根据上面优化后的执行计划来完成整个查询。

比如开始那个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

  1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
  2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
  3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

你可以在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。但在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

Mysql 引擎

数据库底层软件组织，是用于存储、处理和保护数据的核心服务。

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。

在MySQL中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。Support列的值表示某种引擎是否能使用：YES表示可以使用、NO表示不能使用、DEFAULT表示该引擎为当前默认的存储引擎。

现在最常用的存储引擎是 InnoDB，它从 MySQL5.5版本开始成为了默认存储引擎。如果要想查看数据库默认使用哪个引擎，可以通过使用命令查看：

SHOW VARIABLES LIKE ‘storage_engine‘;

如果要使用别的存储引擎，可以在建表的时候指定，比如在 create table 语句中使用 engine = memory。如果要查看mysql支持的引擎，可以使用命令：

SHOW ENGINES;

以下是Mysql中几种常见的存储引擎。

MyISAM

在 MySQL 5.5 之前的版本，MyISAM是默认的存储引擎。它是MySQL对 ISAM 的一种扩展格式。

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到++数据库被查询的次数要远大于更新的次数++ 。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。

MyISAM则提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等，但 MyISAM 不支持事务和行级锁，而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。正是由于 MyISAM 引擎的缘故，即使 MySQL 支持事务已经很长时间了，在很多人的概念中 MySQL 还是非事务型的数据库。

尽管 MyISAM 不支持事务、不支持崩溃后的安全恢复，但它绝不是一无是处的。对于只读的数据，或者表比较小，可以忍受 repair （修复）操作，则依然可以继续使用 MyISAM

存储

MyISAM 会将表存储在两个文件中：

  • 数据文件，扩展名：.MYD
  • 索引文件，扩展名：.MYI

MyISAM 表可以包含动态或者静态行。 MySQL 会根据表的定义来决定采用何种格式。 MyISAM 表可以存储的行记录数，一般受限于可用的磁盘空间，或者操作系统中单个文件的最大尺寸。

在 MySQL 5.0 中，MyISAM 表如果是变长行，则默认配置只能处理 256TB 的数据，因为指向数据记录的指证长度是 6 个字节。而在更早的 MySQL 版本中，指针的默认长度都支持 8 字节的指针。要改变 MyISAM 表指针的长度，可以通过修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项的值来实现，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引，这可能要很长的时间才能完成。

特性

作为 MySQL 最早的存储引擎之一，MyISAM 有一些已经开发出来很多年的特性，可以满足用户的实际需求。

  • 加锁与并发
    MyISAM 对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表添加排它锁。但是在表有读取查询时， 也可以往表中插入新的记录。

  • 修复
    对于 MyISAM 表，MySQL 可以手工或者自动执行检查和修复操作，但这里说的修复和事务修复以及崩溃修复是不同的概念。执行表的修复可能会丢失一部分数据，而且修复操作是非常慢的。

  • 索引特性
    对于 MyISAM 表，即使是 BLOB 和 TEXT 等长字段，也可以基于其前 500 个字符创建索引。MyISAM 也支持全文索引，这是一种基于分词创建的索引，可以支持复制的查询。

  • 延迟更新索引键
    创建 MyISAM 表的时候，如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区（in_memory key buffer）,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。延迟更新索引键的特性，可以在全局设置，也可以为单个表设置。

压缩表

如果表在创建并导入数据以后，不会在进行修改操作，那么这样的表或者适合采用 MyISAM 压缩表。

可以使用 myisampack 对 MyISAM 表进行压缩。压缩表是不能进行修改的。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘 I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。

性能

MyISAM 引擎设计十分简单，数据以紧密格式存储，所以在某些场景下的性能很好。MyISAM 有一些服务器级别的性能扩展限制。但是 MyISAM 最典型的的性能问题还是 表锁的问题。

InnoDB

是目前MySQL默认的事务型引擎，使用非常广泛，极擅长处理短期事务，具有自动崩溃恢复的特性，在日常开发中，一般都要求使用该引擎。

结构

Mysql 结构、引擎、执行过程

整体分为三层：

  1. 内存结构
    • Buffer Pool：缓冲池，是主内存中的一个区域，在InnoDB访问表和索引数据时会在其中进行高速缓存，大量减少磁盘IO操作，提升效率。
    • Change Buffer：写缓冲区，避免每次增删改都进行IO操作，提升性能。
    • Adaptive Hash Index：自适应哈希索引，使用索引关键字的前缀构建哈希索引，提升查询速度。
    • Log Buffer：日志缓冲区，保存要写入磁盘上的日志文件的数据，缓冲区的内容定期刷新到磁盘上。
  2. 缓存
  3. 磁盘结构
    • Tables：数据表的物理结构。
    • Indexes：索引的物理结构。
    • Tablespaces：表空间，数据存储区域。
    • Data Dictionary：数据字典，存储元数据信息的表，例如表的描述，结构，索引等。
    • Doublewrite Buffer：位于系统表空间的一个存储区域，InnoDB在BufferPool中刷新页面时，会将数据页写入该缓冲区后才会写入磁盘。
    • Redo log：记录DML操作的日志，用来崩溃后的数据恢复。
    • Undo logs：数据更改前的快照，可以用来回滚数据。

特性

  • 支持事务
    支持4个事务隔离级别，支持MVCC，上一篇博客中已经说过。除此之外也支持分布式事务。
  • 支持行级锁
    行级锁定（更新时一般是锁定当前行）。上一篇博客中也提到过，通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响。
  • 支持聚簇索引
    即按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，查询效率更快。详细 -> 聚簇索引和非聚簇索引
  • 插入缓冲
    插入缓冲的作用就是把普通索引上的DML操作从随机I/O，变为顺序I/O，从而提高I/O的效率。详细 -> Innodb关键特性之插入缓冲
  • 两次写
    两次写是保证写入的安全性，防止实例宕机时，innoDB发生数据页部分写的问题。对页面拷贝副本，提高数据页的可靠性，当写失效时，通过副本页还原原页，再进行redo。详细 -> InnoDB的两次写特性
  • 自适应哈希索引
    innoDB有一个机制可以监控索引的搜索，如果innodb注意到查询可以通过建立索引得到优化就会自动完成这件事。
    详细 -> MySQL中的哈希索引
  • 其他还有很多，如异步IO、刷新邻进页等等，不一一列举。

Memory（HEAP）

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于memory存储引擎的表实际对应一个磁盘文件，该文件的文件名与表名相同，类型为frm类型。该文件只存储表的结构，而其数据文件，都是存储在内存中的，这样有利于对数据的快速的处理，提高整个表的处理效率。

值得注意的是：服务器需要有足够的内存来维持memory存储引擎的表的使用。如果不需要了，可以释放这些内存，甚至可以删除不需要的表。

特性

  • 索引特点
    支持HASH索引（等值查询）和BTree索引（范围查找）（默认HASH）
  • 字段特点
    所有字段都为固定长度varchar(10) = char(10)，也不支持BLOG和TEXT等大字段
  • 表级锁
  • max_heap_table_size
    表的最大大小由max_heap_table_size参数决定（默认16M，对存在的表修改是无效的）

使用场景：

  • 用于查找或者是映射表，例如邮编和地区的对应表
  • 用于保存数据分析中产生的中间表
  • 用于缓存周期性聚合数据的结果表

注意，MEMORY用到的很少，因为Memory表的所有数据都是存储在内存上的，如果内存出现异常会影响到数据的完整性。如果重启或者关机，所有数据都会消失，因此，基于MEMORY的表的生命周期很短，一般是一次性的。

其他存储引擎

MySQL还有一些其他特殊用途的引擎，有些可能不再支持，具体支持情况参考数据库各个版本支持的引擎。

Archive

Archive引擎支持是Insert，Select操作，现在支持索引，Archive引擎会缓存所有的写，并利用zlib对写入行进行压缩，所以比MyISAM表的磁盘IO更少。但是在每次Select查询都需要执行全表扫描。所以在Archive适合日志和数据采集应用。这类应用在分析时往往需要全表扫描忙活着更快的Insert操作场景中也可以使用。

Archive引擎支持行级锁和专用的缓存区，所以可以实现高并发写入，在查询开始到返回表存在的所有行数之前，Archive会阻止其他Select执行，用来实现一致性读。另外也实现了批量写入结束前批量写入数据对读操作不可见，这种机制模仿了事务和MVCC的特性，但是Archive不是一个事务型引擎，而是针对高写入压缩做了优化的简单引擎。

Blackhole

Blackhole没有实现任何存储机制，它会舍弃所有写入数据，不做任何保存，但是服务器会记录Blackhole表的日志，用于复制数据到备库，或者只是简单的记录到日志，这种特殊的存储引擎可以在一些特俗的复制架构和日志审核时发挥作用。但是不推荐。

CSV

CSV引擎可以将普通的CSV文件作为MySQL表来处理，但是这种表不支持索引，CSV可以在数据库运行时拷贝或者拷出文件，可以将Excel等电子表格中的数据存储未CSV文件，然后复制到MySQL中，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表，其他外部程序也可以从表的数据文件中读取CSV的数据。因此CSV可以作为数据交换机制。非常好用。

Federated

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如MicrosoftSQLServer和Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Merge

Merge引擎是MyISAM引擎的一个变种。Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃

NDB 集群 引擎

NDB集群存储引擎，作为SQL和NDB原生协议之间的接口。MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（MySQLCluster）。

PERFORMANCE_SCHEMA

MySQL 5.5新增一个存储引擎：命名PERFORMANCE_SCHEMA ，主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

performance_schema提供以下功能：

  1. 提供进程等待的详细信息，包括锁、互斥变量、文件信息；
  2. 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
  3. 对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

MyISAM 与 InnoDB 的区别

作为一个常考的面试题，有必要拿过来记一下。

区别：

  1. InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
  2. InnoDB支持表、行(默认)级锁，而MyISAM只支持表级锁。不过行锁是实现在索引上的，索引失效的话还是会锁表；
  3. InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
  4. InnoDB是聚集索引，主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；MyISAM是非聚集索引，主键索引和辅助索引的叶子节点都是数据文件的地址指针。具体说明在上面有。
  5. InnoDB不保存表的具体行数，执行count(*)时，InnoDB会尝试遍历一个尽可能小的索引（除非优化器提示使用别的索引）去全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）；归根到底，还是因为InnoDB有事务机制。
  6. Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
  7. MyISAM表格可以被压缩后进行查询操作；
  8. InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有；
  9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
    • Innodb：frm是表定义文件，ibd是数据文件
    • Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

参考资料

Mysql 结构、引擎、执行过程

上一篇：SQL高级语法操作记录


下一篇：《Python金融大数据分析》一2.2　结语