MySQL系列（一）——基本架构

《高性能 MySQL》第一次读的马马虎虎，听了丁奇大佬的《MySQL实战45 讲》之后，再读感觉理解起来清晰多了，所以参照听的内容做一个系列笔记。

我的新书《LangChain编程从入门到实践》 已经开售！推荐正在学习AI应用开发的朋友购买阅读！

MySQL的基本架构

1. 大体上，MySQL可以分为Server层和存储引擎层
2. Server层包括连接器、查询缓存、分析器、优化器和执行器等
   • 涵盖MySQL的大多数核心服务功能，以及所有的内置函数
   • 所有的跨存储引擎的功能都在这一层实现，例如存储过程、触发器和视图等
3. 存储引擎层负责数据的存储和提取，架构模式为插件式
   • 支持InnoDB、MyISAM和Memory等存储引擎
   • 最常用为InnoDB（Since 5.5.5，默认）

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接，命令如下

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

权限

• mysql是客户端工具，用来与服务端建立连接
• 在完成TCP三次握手之后，连接器就要开始认证身份，即-u和-p
  • 如果账号密码不对，客户端会收到Access denied for user的错误
  • 如果账号密码认证通过，连接器会到权限表里查询拥有的权限
    • 之后在这个连接里的权限判断，都依赖于此时读取的权限
    • 因此即使用管理员账号对权限进行修改，也不会影响到已经存在连接的权限

连接

• 连接完成后，如果没有后续的动作，这个连接就会处于空闲状态
  • 可以通过show processlist命令查看，Command=Sleep表示是一个空闲连接
• 如果客户端太长时间没有动静，连接器会自动将其断开，有参数wait_timeout控制，默认为8小时
• 如果在连接被断开之后，客户端再次发送请求后，会收到错误
  • Lost connection to MySQL server during query
  • 如果需要继续，就需要重新连接，然后再执行请求
• 数据库层面的长连接和短连接
  • 长连接：在连接成功后，如果客户端持续有请求，则一直使用同一连接
    • 建立连接的过程比较复杂，推荐使用
  • 短连接：每次执行完很少的几次查询后就会断开连接，下次查询再重新建立连接
• 全部使用长连接后，MySQL的占用内存可能会涨的很快
  • 原因：MySQL在执行过程中临时使用的内存是在连接对象里面管理的，而这些资源在连接断开时才会释放
  • 如果长连接累计下来，可能会导致占用内存太大，因为OOM被系统强行Kill掉，表现为MySQL异常重启
  • 解决方案
    • 定期断开长连接。可以减少wait_timeout，或者在程序中判断是否执行过占用大内存的查询，然后断开连接
    • 从MySQL 5.7开始，可以在每次执行较大的操作后，执行mysql_reset_connection来初始化连接资源
      • 该过程不需要重新建立连接和重新做权限校验
      • 只是会将连接恢复到刚刚创建完时的状态

mysql> show processlist;
+---------+-------------+----------------------+-----------+---------+------+-------+------------------+
| Id      | User        | Host                 | db        | Command | Time | State | Info             |
+---------+-------------+----------------------+-----------+---------+------+-------+------------------+
| 5649500 | live_prop_r | 192.168.30.55:38764  | live_prop | Sleep   |  327 |       | NULL             |
| 5790469 | live_prop_r | 192.168.85.200:56204 | live_prop | Query   |    0 | init  | show processlist |
+---------+-------------+----------------------+-----------+---------+------+-------+------------------+

# 连接过程中的等待时间
mysql> SHOW VARIABLES LIKE '%connect_timeout%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 10    |
+-----------------+-------+

# 非交互式，连接完成后，使用过程中的等待时间
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 3600  |
+---------------+-------+

# 交互式，连接完成后，使用过程中的等待时间
mysql> SHOW VARIABLES LIKE '%interactive_timeout%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 3600  |
+---------------------+-------+

查询缓存

• MySQL收到一个查询语句后，首先会查询缓存
• 之前执行过的语句及其结果可能会以Key-Value的形式，被直接缓存在内存中
  • Key：查询语句
  • Value：查询结果
• 如果能在缓存中找到这个Key，直接返回对应的Value
• 否则，就会继续执行后续的执行阶段，执行完成后，执行结果会被存入查询缓存中
• 查询缓存往往弊大于利
  • 查询缓存的失效非常频繁，只要有一个对表的更新操作，这个表上的所有查询缓存都会被清空
  • 因此对更新频繁的场景来说，查询缓存的命中率很低
  • 查询缓存比较适合读多写极少的场景，例如系统配置表
• MySQL 8.0删除掉了查询缓存这个功能

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

分析器

• 如果没有命中查询缓存，就要开始真正地执行语句
• 分析器首先会做词法分析
  • MySQL需要识别SQL语句里面的字符串是什么
  • 通过select关键字识别出这是一个查询语句
  • 将T识别成表名，将字符串ID识别成列名
• 分析器会接着做语法分析
  • 根据词法分析的结果，语法分析会根据语法规则，判断SQL是否满足MySQL语法
  • 如果不满足，将会收到错误You have an error in your SQL syntax错误

优化器

• 经过分析器，MySQL已经能理解要做什么，在开始执行之前，需要经过优化器的处理，达到怎么做
• 优化器会在表里存在多个索引的时候，选择使用哪个索引
• 优化器也会在多表关联的时候，决定各个表的连接顺序
• 优化器阶段完成后，语句的执行方案就已经能确定下来了，然后进入执行器阶段

执行器

• MySQL通过分析器能明白做什么，再通过优化器能明白怎么做，而执行器是负责语句的具体执行
• 首先会判断是否有执行权限，如果没有就会返回权限错误，SELECT command denied to user
  • 如果命中查询缓存，也会在查询缓存返回结果的时候，做权限验证
  • 优化器之前也会调用precheck做验证权限
• 如果有权限，那么将打开表继续执行
  • 打开表的时候，执行器会根据表的引擎定义，去调用引擎提供的接口
• 假设表T中，ID字段没有索引，执行器的执行流程如下
  • 调用InnoDB的引擎接口，获取表的第一行，判断ID是否为10
    • 如果不是则跳过，如果是则将这行存放在结果集中
  • 调用引擎接口获取下一行，重复上面的判断逻辑，直到获取到表的最后一行
  • 执行器将上面遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端
• rows_examined
  • 数据库的慢查询日志会有rows_examined字段
  • 在执行器每次调用引擎获取数据行的时候累加的（+1）
  • 有些场景，执行器调用一次，但在引擎内部则是扫描了很多行
    • 例如在InnoDB中，一页有很多行数据
  • 因此，引擎扫描行数（引擎内部真正扫描的次数）跟rows_examined并不完全相同

The Slow Query Log

mysql> SHOW VARIABLES LIKE '%slow_query%';
+---------------------+----------------------------------------------+
| Variable_name       | Value                                        |
+---------------------+----------------------------------------------+
| slow_query_log      | OFF                                          |
| slow_query_log_file | D:\mysql-8.0.16-winx64\data\MorsoLi-slow.log |
+---------------------+----------------------------------------------+
2 rows in set, 1 warning (0.53 sec)