浅析mysql的架构和执行流程

mysql体系结构与模块划分是啥?

• 架构分层
  第一层是连接层, 用来管理连接和权限验证.
  第二层是服务层, 包括解析器, 预处理器, 优化器, 执行器和缓存. 解析器用来进行词法解析和语法解析. 预处理器进行语义分析和权限检查, 生成新的解析树. 优化器是基于成本/开销的, 用于执行计划的生成, 驱动表的选择, 索引选择. 执行器用于调用存储引擎的接口获取数据, 然后进行数据计算, 过滤和排序等操作, 最后返回结果.
  第三层是存储引擎层, 用于存储数据, 提供读写接口.
• 如何查询数据库版本?

SELECT VERSION();

• 接口之间如何交互?
  协议: TCP/webservice/http
  消息的格式: json/xml, 定长/变长, 请求参数, 字段的详细含义
  通信类型: 同步/异步
  连接方式: 长连接/短连接, 如连接池
  通信方式: 单工/半双工/全双工
• 如何设置连接超时时间? 单位是秒

// 通过jdbc连接数据库是非交互式连接。
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';
// 交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过mysql客户端连接数据库是交互式连接
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';

• 一个会话对应一个线程, 如何查询线程状态?

SHOW STATUS LIKE 'Thread%'

• 如何查询最大连接数?

// 默认151, 最大支持10万
SHOW GLOBAL VARIABLES LIKE 'max_connections';

• 如何查询系统变量和系统状态?

SHOW GLOBAL/SESSION VARIABLES/STATUS LIKE '%';

• 如何修改系统变量?

// 重启失效, 永久生效需修改配置文件my.cnf
SET GLOBAL max_connections=100;

• mysql的通信协议有哪些?
  tcp/ip, unix socket, 命名管道, 共享内存

// socket通信使用的文件位置
SELECT @@SOCKET

• mysql的通信方式?
  半双工
• mysql一次发送的最大字节数?

// 默认是4k大小, 单位是字节
SHOW VARIABLES LIKE 'max_allowed_packet'

• 查询mysql的缓存参数

// 8.0版本已经完全移除, 比较鸡肋
// 大小写, 空格敏感 记录修改或表结构修改都会导致缓存失效
SHOW VARIABLES LIKE 'query_cache%'

• 在MySQL5.5版本中，explain查看执行计划，只能支持select语句，但是在MySQL5.6/5.7版本中，可以支持DML语句，即UPDATE、DELETE、INSERT。
• 存储引擎(表类型)决定了数据的存储格式
• 查询表信息

SHOW TABLE STATUS FROM test

• 查询表的存储位置

SHOW VARIABLES LIKE 'datadir'

• mysql各存储引擎的区别
  innodb有两个文件: user_innodb.frm, user_innodb.ibd
  myisam有三个文件: user_myisam.frm, user_myisam.myd, user_myisam.myi
  memory有一个文件: 以frm结尾
  csv有三个文件: 以frm, csv和csm结尾
  archive有两个文件: 以frm和arz结尾
  frm后缀是表的元数据, myi后缀存放索引, myd后缀存储数据.
  各存储引擎的区别在于访问性能和操作方式, 以此来满足不同的业务. csv 用来做数据的导入导出.
• innodb引擎和myisam引擎的区别
  myisam的主键索引和辅助索引的叶子节点存储的是记录的地址, innodb的主键索引的叶子节点存储的是记录本身, 辅助索引的叶子节点存储的是主键索引的地址(回表).innodb索引就是数据, 数据就是索引.
• 查询buffer_pool相关的参数

// 磁盘的页大小是4k, 预读取, 局部性原理
// 磁盘 -> 内存, 单位是页, 大小是16k, 逻辑单位
SHOW STATUS LIKE 'innodb_buffer_pool%'

一条查询语句是怎么执行的?

第一步, 查询缓存, 看是否命中, 不推荐开启, 因为缓存经常因为写操作而失效, 命中率低等.
第二步, 解析器, 包括词法解析和语法解析.
第三步, 预处理器, 包含语义解析和检查权限.
第四步, 查询优化器, 选择使用哪个索引, 得到执行路径.
第五步, 生成执行计划.
第六步, 执行器和存储引擎, 执行器负责数据的过滤, 排序和计算, 存储引擎负责存取数据.
第七步, 执行器写入缓存, 返回数据给客户端.

一条更新语句是怎么执行的?

第一步, mysql客户端发送更新语句给server层.
第二步, server层调用存储引擎接口, 拿到这条数据, 执行器执行更新操作.
第三步, 存储引擎将修改结果更新到内存中.
第四步, 存储引擎记录redolog, 并将这行记录状态设置为prepare.
第五步, 存储引擎通知server层, 可以提交事务了.
第六步, server层写入binlog.
第七步, server层执行commit, 提交事务.
第八步, 将redolog里这个事务的相关记录状态设置为commit状态

为什么写入redolog和binlog要用二阶段提交呢?

为了保证两者的一致性, 出现异常, 需要故障恢复时, 若发现事务处于prepare阶段, 并且binlog存在则提交, 否则回滚.