ReturnAC

Mysql那些最需要掌握的原理

转载来源：字节跳动技术团队open in new window （部分截取）

如何写好SQL？如何发现日常开发中慢SQL的问题？

关于这些问题的解决，最好的方式当然是去了解MySQL的原理。本文选取MySQL体系中较为核心的部分内容，尽可能详细的介绍这些核心模块及其底层原理，尽可能多的加入实战案例来加深理解，以便于在以后的开发中更好的去避免问题，发现问题，解决问题。

MySQL版本时间线

2010年 MySQL 5.5

2012年 MySQL 5.6

2015年 MySQL 5.7

2016年 MySQL 8.0.1

2018年 MySQL 8.0.11(GA版本)

MySQL体系架构

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层，具体如下图所示：

网络连接层

客户端连接器（ Client Connectors） ：提供与MySQL服务器建立的支持。支持现在主流的编程技术Java、C、Python、PHP等，通过各自的API技术与MySQL建立连接。

服务层（MySQL Server）

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

  • 连接池（Connection Pool） ：负责用户的登录鉴权，执行操作鉴权，存储和管理客户端与数据库的连接
  • 系统管理和控制工具（Management Services & Utilities） ：包含例如备份恢复、安全管理、集群管理服务和工具
  • SQL 接口（SQL Interface） ：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等
  • 解析器（Parser） ：负责将请求的SQL解析生成一个"解析树"。然后根据一些MySQL规则进一步检查解析树是否合法
  • 查询优化器（Optimizer） ：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互
  • 缓存（Caches & buffers）： 包括全局和引擎特定的缓存，这个缓存机制是由一系列小缓存组成，如表缓存、记录缓存、key缓存、权限缓存等

存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异 。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB，以及支持归档的Archive和内存的Memory等。

系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid文件，socket文件等。

一条查询sql的执行过程

一条查询语句的过程大致如下：

第一步：建立连接

输入用户名、密码进行登录，权限验证后正式建立连接。连接器会到权限表里面查出你拥有的权限，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

长连接与短连接

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

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。在客户端体现就是使用线程池，springboot2及公司RDS SDK默认的数据库连接池为HikariCP。

Hikari 数据库连接池配置详解open in new window

* 另外，数据库连接池参数配置参考： * HikariCPopen in new window连接池参数配置多大合适open in new window * HikariCPopen in new window连接池参数配置多大合适(二)open in new window

第二步：查询缓存

建立连接后，就可以执行 select 语句了。执行逻辑就会来到查询缓存，MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

MySQL 8.0 版本后移除了该功能，因为查询缓存失效在实际业务场景中可能会非常频繁，假如对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。所以，一般在大多数情况下都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

第三步：分析器

MySQL 没有命中缓存，就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

第一步，词法分析，一条 SQL 语句由多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

第二步，语法分析，主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

第四步：优化器

优化器的作用就是以它认为的最优的执行方案去执行（有时候可能也不是最优），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

第五步：执行器

当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

更新语句

sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。先分析下查询语句，语句如下：

update tb_employee set level = '5' where name='张三'

其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志，这就会引入日志模块，MySQL 自带的日志模块是 binlog（归档日志） ，所有的存储引擎都可以使用，InnoDB 引擎还自带了一个日志模块 redo log（重做日志），就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

  1. 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
  2. 然后拿到记录，把 level 改为 5，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。
  3. 执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。
  4. 更新完成。

为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没有 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做：

先写 redo log 直接提交，然后写 binlog 假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。

先写 binlog，然后写 redo log 假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

判断 redo log 是否完整 (commit)，如果判断是完整的，就立即提交 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务

这样就解决了数据一致性的问题，这也就是Mysql事务的两阶段提交

总结

  • 查询语句执行流程如下： 权限校验（如果命中缓存）–> 查询缓存 —> 分析器 --> 优化器 —> 权限校验 —> 执行器 —> 引擎
  • 更新语句执行流程如下： 分析器 —> 优化器 —> 权限校验 —> 执行器 —> 引擎 —> redo log(prepare) —> binlog —> redo log(commit)

InnoDB VS MyISAM

功能对比 （InnoDB能取代MyISAM的原因）

InnoDB和MyISAM的功能对比如下：

  • InnoDB支持ACID的事务4个特性，而MyISAM不支持;
  • InnoDB支持行级别的锁粒度，MyISAM不支持，只支持表级别的锁粒度;
  • InnoDB支持4种事务隔离级别，默认是可重复读repeatable read，MyISAM不支持;
  • InnoDB支持crash安全恢复，MyISAM不支持;
  • InnoDB支持外键，MyISAM不支持;
  • InnoDB支持MVCC(多版本并发控制)，MyISAM不支持；
  • InnoDB特性上，InnoDB表最大可以64TB，支持聚簇索引、支持压缩数据存储，支持数据加密，支持查询/索引/数据高速缓存，支持自适应 hash索引、空间索引，支持热备份和恢复等。

应用场景

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。

InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

Order by 的工作原理

有如下表结构

CREATE TABLE `t` (
  `id` INT ( 11 ) NOT NULL,
  `city` VARCHAR ( 16 ) NOT NULL,
  `name` VARCHAR ( 16 ) NOT NULL,
  `age` INT ( 11 ) NOT NULL,
  `addr` VARCHAR ( 128 ) DEFAULT NULL,
  PRIMARY KEY ( `id` ),
  KEY `city` ( city ) 
) ENGINE = INNODB

查询语句

SELECT city,name,age from t where city='杭州' ORDER BY name LIMIT 1000

使用explain命令可以看到

Extra 字段中的Using filesort表示需要排序， MySQL会给每个线程分配一块内存用于排序，称为 sort_buffer，sort_buffer的大小由由参数 sort_buffer_size 控制

全字段排序

city 索引的示意图如下

上面查询语句的执行流程如下

  1. 初始化 sort_buffer，确定放入 name、city、age 这 3 个字段
  2. 从普通索引 city 中找到第一个满足 city = ’杭州’ 的主键 ID（ID_x）
  3. 到主键索引树中找到 ID_x，取出该整行数据，取 name、city、age 这 3 个字段的值，存入 sort_buffer
  4. 从普通索引 city 取下一个满足 city = ’杭州’ 的主键 ID
  5. 重复 3、4 步，直到 city 值不满足条件
  6. 对 sort_buffer 中的数据按照 name 做快速排序

把排序结果中的前 1000 行返回给客户端

这个排序过程叫做全字段排序，因为需要返回的字段都放入了 sort_buffer 参与排序过程

Rowid排序

排序可能是在 内存 中完成，也可能需要 外部 排序，这取决于排序所需要的内存和 sort_buffer_size 参数值。如果排序的数据量小于sort_buffer_size，排序就在内存中完成，否则会利用磁盘临时文件来辅助排序。

上面的查询中只返回 3 个字段，不会太长，可以一起都放在 sort_buffer 中，但如果排序的单行长度太大，MySQL会怎么做？

假设 name、city、age 这 3 个字段定义的总长度为 36，而 max_length_for_sort_data = 16，就是单行的长度超了，MySQL 认为单行太大，需要换一个算法。此时，放入 sort_buffer 的字段就会只有要排序的字段 name 和主键 id，那么排序的结果中就少了 city 和 age，需要回表了。

排序流程变为：

  1. 初始化 sort_buffer，确定放入 2 个字段，name 和 id
  2. 从普通索引 city 中找到第一个满足 city = ’杭州’ 的主键 ID（ID_x）
  3. 到主键索引树中取出整行，把 name、id 这 2 个字段放入 sort_buffer
  4. 从普通索引 city 取下一个满足 city = ’杭州’ 的的主键 ID
  5. 重复 3、4 步，直到 city 值不满足条件
  6. 对 sort_buffer 中的数据按照 name 做快速排序
  7. 取排序结果中的前 1000 行，并按照 id 的值到原表中取出 name、city、age 这 3 个字段的值返回给客户端

这种排序过程称为 rowid 排序

全字段排序 vs Rowid排序

分析两个执行流程，可以得出：

  • 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
  • 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

优化

由上面分析可以看出 MySQL 做排序是一个成本比较高的操作。那么，是不是所有的 order by 都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

从上面分析的执行过程，可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，

原因是原来的数据都是无序的。

所以，我们可以在这个表上创建一个 city 和 name 的联合索引，alter table t add index city_user(city, name);

在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。

这样整个查询过程的流程就变成了：

  1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
  2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
  3. 从索引 (city,name) 取下一个记录主键 id
  4. 重复步骤 2、3
  5. 直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束

查看执行计划，没有Using filesort

进一步优化

走覆盖索引，不再回表

count( *)的工作原理

COUNT() 函数，可以用来统计某个列值的数量，也可以用来统计行数。

拿 count(*) 为例，它在 MySQL 中不容的引擎有着不同的的实现方式，例如语句 select count(*) from t ，（注意这里不带任何的 where 条件）。

  • 在 MyISAM 引擎 中，每个表的总行数都会在内存和磁盘文件中进行保存，当执行 count( *) 语句的时候，会直接将内存中保存的数值返回，所以执行非常快。
  • 而在InnoDB 引擎中，当执行 count( *) 的时候，它需要一行一行的进行统计和计数，并将最终的统计结果返回。

也就是说，MyISAM 引擎中 count() 的时间复杂度是 O(1)，InnoDB 引擎中 count() 的时间复杂度是O(N)。

所以随着表中数据越来越多，使用InnoDB 引擎的表，这条语句执行得也会越来越慢。

那为什么 InnoDB 引擎就不能像 MyISAM 引擎一样，也把总行数保存到内存和磁盘文件中呢？

这是因为 InnoDB 引擎实现了多版本并发控制（MVCC）的原因：对同一个表，不同事物在同一时刻，看到的数据可能是不一样的。

InnoDB做的优化

InnoDB是索引组织表，主键索引的叶子节点是数据，而普通索引的叶子节点是主键值，所以普通索引树比主键索引树小很多。

对于COUNT( *)来说，遍历哪颗树都一样，所以mysql优化器会选择最小的树进行遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

其他方式

SHOW TABLE STATUS命令中有个字段TABLE_ROWS，表示行数

实际上，TABLE_ROWS 就是从这个采样估算得来的(与索引统计的值类似)，因此它也很不准。有多不准呢，官方文档说误差可能达到 40% 到 50%。所以，show tablestatus 命令显示的行数也不能直接使用。

count( *)、count(1)、count(字段)之间的区别

  • count(1)： innodb引擎会扫描整个表，但不取数据。server层对于每一行放个1进去，判断不可能为NULL，逐行累计。
  • count(字段)： 计算所有column字段为"非null"值的总数 。 若该字段声明为NOT NULL ，server直接累计得出数量。若可以为NULL，则server层还要判断每一行的值，不为NULL的进行累计。
  • count( *)： 专门做了优化，不取值，判断不可能为NULL，逐行累计。

性能：count(字段)<count(1)≈count( *)

备案号：鄂ICP备2023006484号-1浏览量: 0
Last Updated: