源码仓库Gitee · GitHubMySQL的体系结构可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程Server层包括连接层、查询缓存、分析器、优化器、执行器等涵盖MySQL的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等实现所有跨存储引擎的功能比如存储过程、触发器、视图等存储引擎层负责数据的存储和提取MySQL5.5.5版本开始默认使用InnoDB即你执行create table建表时不指定引擎类型默认使用InnoDB连接器连接管理Connection Handling当一个客户端进行连接服务端时会得到一个server处理线程每个连接的请求处理都是单线程的同时服务器会缓存这些线程所以当一个新的连接来时就不需要创建和销毁线程【MySQL5.5及其以上版本提供了一个支持线程池插件的接口】权限认证Authentication当客户端连接服务端时服务端需要对连接进行认证即对username、host、password进行认证安全Securith当客户端成功连接上服务端时服务端会对每个连接查看它的系统权限清单【mysql show privileges \G】之后这个连接里面的权限判断逻辑都将依赖于此时读到的权限即一个用户成功建立连接后即使对这个用户的权限做了修改也不会影响到已经存在连接的权限修改完后只有再新建的连接才会使用新的权限设置备注连接完成后如果没有后续的操作这个连接就处于空闲状态可以使用show processlist进行查看当Command列中出现Sleep时表示该连接是空闲状态客户端如果太长时间没有动静连接器会自动断开连接这个时间由参数wait_timeout控制默认值是8小时如果在连接被断开后客户端再次发起请求会收到一个错误提醒Lost connection to MySQL server during query。这时候如果要继续就需要重连然后再执行请求数据库里面的长连接成功后如果客户端持续有请求则一直使用同一个连接短连接则是每次执行完很少的几次查询就断开连接下次查询再重新建立一个全部使用长连接后有时候MySQL占用内存涨的很快这是因为MySQL在执行过程中临时使用的内存是管理在连接对象中的这些资源在断开时才会被释放如果长连接累计下来可能导致内存占用太大被系统强行杀掉OOM)从现象看就是MySQL异常重启了解决方案1定期断开长连接。使用一段时间或程序里面判断执行过一个占用内存的大查询后断开连接之后要查询再重连解决方案2MySQL版本如果是5.7及其以上可以在每次执行一个大的操作后通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和做权限验证会将连接恢复到刚刚创建完时的状态。分析器ParserMySQL分析器会创建一个内部结构解析树MySQL的解析行为就像单趟编译器一样进行词法解析、语法解析、语义解析词法解析MySQL对语句进行识别里面的字符串分别是什么代表什么比如”select“是一个查询语句把字符串”tableName识别为“表名 tableName把字符串”id“识别成”列id语法解析根据语法规则判断输入的SQL语句是否满足MySQL语法比如“elect *from tableName这个会提示”You hava an error in your SQL syntax“的错误提醒一般语法错误会提示第一个出现错误的位置所以要关注的是紧接”use near的内容优化器Optimizer当解析树完成后MySQL会进行一系列的优化操作比如选择重写查询语句、读表顺序、索引选择等你可以使用explain进行查看细节【MySQL笔记3-- SQL分析】因此优化器主要做下面的事情选择最合适的索引选择表扫还是走索引选择表关联顺序优化 where 子句排除管理中无用表决定 order by 和 group by 是否走索引尝试使用 inner join 替换 outer join简化子查询决定结果缓存合并试图缓冲器Caches缓冲器会缓存查询的结果集当进行查询解析前服务端会去缓存器查找如果改查询命中缓存那就不需要进行解析、优化、执行等等操作直接返回缓存结果集【以key-value保存执行过的语句及查询结果】下面是查询缓存的问题如果对表进行了更新操作该表的所有查询缓存都会清空同时cache 的访问由一个单一的全局锁来控制这时候大量的查询将被阻塞直至锁释放。所以不要简单认为设置 cache 必定会带来性能提升。MySQL官方文档显示The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.表示在MySQL5.7.20版本就不推荐使用在8.0版本就移除了缓冲器执行器开始执行时需要先判断你有没有这个表的执行权限如果没有返回没有权限的错误【同时对于命中查询缓存会在查询缓存返回结果时做权限验证查询会在优化器之前调用precheck验证权限】如果有权限打开表继续执行执行器根据表的引擎定义去使用这个引擎提供的接口比如查询语句select *from table where id1;中的id字段没有索引那么执行器的执行流程是调用InnoDB引擎接口取这个表的第一行判断id是不是1如果不是则跳过如果是则将这行存在结果集中调用引擎接口取”下一行“重复相同的判断逻辑直到取到这个表的最后一行执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端完整体系结构图管理工具和服务Services and utilities提供备份backup、恢复recovery、安全security、复制replication、集群cluster、分区partitioning、工作台workbench功能SQL接口接受用户的SQL命令返回用户需要查询的结果比如select from调用了SQL Interface;是在MySQL客户端和服务端之间进行服务的工具SQL接口组件包括了数据处理语言【Data Manipulation Language (DML)】、数据定义语言【Data Definition Language(DDL)】存储过程、视图、触发器等等存储引擎MyISAMMySQL5.1及其之前版本默认的存储引擎该存储引擎支持全文索引基于分词创建的索引支持复杂的查询、压缩、空间函数等但不支持事务和行级锁而且崩溃后无法安全恢复对于只读的数据或表比较小、可以忍受修复操作则可以继续使用MyISAMMyISAM会将表存储在两个文件中数据文件.MYD为扩展名和索引文件.MYI为扩展名)MyISAM支持表锁即对整张表进行加锁读取时会对需要读到的所有表加共享锁写入时对表加排他锁。但是在表有读取查询的同时也可以往表插入新的记录并发插入CONCURRENT INSERT)使用了MyISAM存储引擎的表MySQL可以手工或自动执行检查check table mytable和修复repair table mytable)操作但执行表的修复可以导致一些数据丢失并且修复操作是非常慢的对于BLOB和TEXT等长字段可以基于其前500个字符创建索引创建MyISAM表的时候如果指定了DELAY_KEY_WRITE(延迟更新索引键选项在每次修改执行完时不会立刻将修改的索引数据写入磁盘而是会写到内存的键缓冲区in-memory key buffer中只有在清理键缓冲区或关闭表时才将对应的索引块写入到磁盘。这种方式大大的提升写入性能但是在数据库或主机崩溃时会造成索引损坏需要进行修复操作。如果表在创建并导入数据后不会再进行修改操作可以使用MyISAM压缩表使用myisampack对MyISAM表进行压缩也叫打包pack压缩表是不能进行修改的除非先将表解除压缩修改数据然后再次压缩压缩表可以极大的减少磁盘空间占用和磁盘I/O从而 提升了查询性能压缩表支持索引但索引是只读的InnoDBMySQL默认的事务型引擎用来处理大量的短期short-lived事务短期事务大部分情况是正常提交的很少会被回滚。InnoDB的性能和自动崩溃恢复特性使得它在非事务型存储的需求中也很流行。MySQL5.1新的InnoDB plugin支持一些新特性比如利用排序创建索引、删除或增加索引时不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式、以及文件格式管理等。InnoDB采用MVCC来支持高并发并且实现了四个标准的隔离级别。其默认级别是repeatable read可重复读并且通过间隙锁next-key-locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询设计的行还会对索引中的间隙进行锁定防止幻影行的插入InnoDB表是基于聚簇索引建立的聚簇索引对主键查询有很高的性能InnoDB的存储格式是平台独立的也就是说可以将数据和索引文件从Intel平台复制到PowerPc等其他平台InnoDB从磁盘读取数据时采用了可预测性预读能够自动在内存中创建hash索引以加速读操作的自适应哈希索引能够加速插入操作的插入缓冲区等InnoDB通过一些机制和工具支持真正的热备份MySQL的其他存储引擎不支持热备份要获取一致性视图需要停止对所有表的写入而在读写混合场景中停止写入可以也意味着停止读取Federated是访问其他MySQL服务器的一个代理它会创建一个到远程MySQL服务器的客户端连接并将查询传输到远程服务器执行然后提取或发送需要的数据。Archive只支持insert和select操作在MySQL5.1之前不支持索引Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩所以比MyISAM表的磁盘I/O更少但是每次select都需要进行全表扫描Archive表适合日志和数据采集类应用这类应用做数据分析时往往需要全表扫描Archive引擎支持行级锁和专用的缓冲区所以支持实现高并发的插入。在一个查询开始直到返回表中存在的所有行数之前Archive引擎会阻止其他的select执行以实现一致性读。另外也实现了批量插入在完成之前对读操作也是不可见的Merge是MyISAM引擎的一个变种Merge表是由多个MyISAM表合并而来的虚拟表但是将分区功能引入后该引擎已经被放弃Memory如果需要快速地访问数据并且这些数据不会被修改重启以后丢失也没关系那么使用Memory表是非常有用的。Memory表所有的数据都保存到内存中不需要进行磁盘I/O表的结构在重启以后会保留但数据会丢失使用场景比如用于查找或映射表将邮编和州名映射的表、缓存周期性聚合数据的结果、保存数据分析中产生的中间数据支持hash索引Memory表是表级锁并发写入的性能较低不支持BLOB或TEXT类型的列并且每行的长度是固定的所以即使指定了varchar列实际存储时也会转换成char这可以导致部分内存的浪费如果使用临时表来保存中间结果该临时表使用Memory引擎但中间结果太大超出了Memory表的限制或者含有BLOB或TEXT字段则临时表会转换成MyISAM表Cluster指的是MySQL服务器、NDB集群存储引擎以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合被称为MySQL集群Blackhole没有实现任何的存储机制所有插入的数据都会丢弃不做任何保存但服务器会记录Blackhole表的日志所以可以用于复制数据到备库或只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用CSV可以将普通的CSV文件逗号分割值的文件作为MySQL表来处理但不支持索引。CSV引擎可以在数据库运行时拷入或拷出文件可以将excel等电子表格软件中的数据存储为CSV文件然后复制到MySQL数据目录下就能在MySQL中打开使用。同样如果将数据写入到一个CSV引擎表其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制Example该引擎作为MySQL源代码中的示例说明了如何开始编写新的存储引擎它主要是开发人员感兴趣的。存储引擎是什么都不做的“存根”可以使用此引擎创建表但是不能将数据存储在表中或从表中检索数据。参考【网址需要翻墙】理解MySQL的体系结构https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html高性能MySQLhttps://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch01.html#ftn.CHP-1-FN-2MySQL 5.7官方文档《高性能MySQL》作者: 施瓦茨 (Baron Schwartz) / 扎伊采夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko) 译者: 宁海元 / 周振兴 / 彭立勋 / 翟卫祥,刘辉 
长河君 · 2026/10/22 17:04:16 · 阅读 1.2k MySQL笔记（5）-- SQL执行流程，MySQL体系结构
源码仓库Gitee · GitHubMySQL的体系结构可以清楚地看到 SQL 语句在 MySQL 的各个功能模块中的执行过程Server层包括连接层、查询缓存、分析器、优化器、执行器等涵盖MySQL的大多数核心服务功能以及所有的内置函数如日期、时间、数学和加密函数等实现所有跨存储引擎的功能比如存储过程、触发器、视图等存储引擎层负责数据的存储和提取MySQL5.5.5版本开始默认使用InnoDB即你执行create table建表时不指定引擎类型默认使用InnoDB连接器连接管理Connection Handling当一个客户端进行连接服务端时会得到一个server处理线程每个连接的请求处理都是单线程的同时服务器会缓存这些线程所以当一个新的连接来时就不需要创建和销毁线程【MySQL5.5及其以上版本提供了一个支持线程池插件的接口】权限认证Authentication当客户端连接服务端时服务端需要对连接进行认证即对username、host、password进行认证安全Securith当客户端成功连接上服务端时服务端会对每个连接查看它的系统权限清单【mysql show privileges \G】之后这个连接里面的权限判断逻辑都将依赖于此时读到的权限即一个用户成功建立连接后即使对这个用户的权限做了修改也不会影响到已经存在连接的权限修改完后只有再新建的连接才会使用新的权限设置备注连接完成后如果没有后续的操作这个连接就处于空闲状态可以使用show processlist进行查看当Command列中出现Sleep时表示该连接是空闲状态客户端如果太长时间没有动静连接器会自动断开连接这个时间由参数wait_timeout控制默认值是8小时如果在连接被断开后客户端再次发起请求会收到一个错误提醒Lost connection to MySQL server during query。这时候如果要继续就需要重连然后再执行请求数据库里面的长连接成功后如果客户端持续有请求则一直使用同一个连接短连接则是每次执行完很少的几次查询就断开连接下次查询再重新建立一个全部使用长连接后有时候MySQL占用内存涨的很快这是因为MySQL在执行过程中临时使用的内存是管理在连接对象中的这些资源在断开时才会被释放如果长连接累计下来可能导致内存占用太大被系统强行杀掉OOM)从现象看就是MySQL异常重启了解决方案1定期断开长连接。使用一段时间或程序里面判断执行过一个占用内存的大查询后断开连接之后要查询再重连解决方案2MySQL版本如果是5.7及其以上可以在每次执行一个大的操作后通过执行mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和做权限验证会将连接恢复到刚刚创建完时的状态。分析器ParserMySQL分析器会创建一个内部结构解析树MySQL的解析行为就像单趟编译器一样进行词法解析、语法解析、语义解析词法解析MySQL对语句进行识别里面的字符串分别是什么代表什么比如”select“是一个查询语句把字符串”tableName识别为“表名 tableName把字符串”id“识别成”列id语法解析根据语法规则判断输入的SQL语句是否满足MySQL语法比如“elect *from tableName这个会提示”You hava an error in your SQL syntax“的错误提醒一般语法错误会提示第一个出现错误的位置所以要关注的是紧接”use near的内容优化器Optimizer当解析树完成后MySQL会进行一系列的优化操作比如选择重写查询语句、读表顺序、索引选择等你可以使用explain进行查看细节【MySQL笔记3-- SQL分析】因此优化器主要做下面的事情选择最合适的索引选择表扫还是走索引选择表关联顺序优化 where 子句排除管理中无用表决定 order by 和 group by 是否走索引尝试使用 inner join 替换 outer join简化子查询决定结果缓存合并试图缓冲器Caches缓冲器会缓存查询的结果集当进行查询解析前服务端会去缓存器查找如果改查询命中缓存那就不需要进行解析、优化、执行等等操作直接返回缓存结果集【以key-value保存执行过的语句及查询结果】下面是查询缓存的问题如果对表进行了更新操作该表的所有查询缓存都会清空同时cache 的访问由一个单一的全局锁来控制这时候大量的查询将被阻塞直至锁释放。所以不要简单认为设置 cache 必定会带来性能提升。MySQL官方文档显示The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.表示在MySQL5.7.20版本就不推荐使用在8.0版本就移除了缓冲器执行器开始执行时需要先判断你有没有这个表的执行权限如果没有返回没有权限的错误【同时对于命中查询缓存会在查询缓存返回结果时做权限验证查询会在优化器之前调用precheck验证权限】如果有权限打开表继续执行执行器根据表的引擎定义去使用这个引擎提供的接口比如查询语句select *from table where id1;中的id字段没有索引那么执行器的执行流程是调用InnoDB引擎接口取这个表的第一行判断id是不是1如果不是则跳过如果是则将这行存在结果集中调用引擎接口取”下一行“重复相同的判断逻辑直到取到这个表的最后一行执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端完整体系结构图管理工具和服务Services and utilities提供备份backup、恢复recovery、安全security、复制replication、集群cluster、分区partitioning、工作台workbench功能SQL接口接受用户的SQL命令返回用户需要查询的结果比如select from调用了SQL Interface;是在MySQL客户端和服务端之间进行服务的工具SQL接口组件包括了数据处理语言【Data Manipulation Language (DML)】、数据定义语言【Data Definition Language(DDL)】存储过程、视图、触发器等等存储引擎MyISAMMySQL5.1及其之前版本默认的存储引擎该存储引擎支持全文索引基于分词创建的索引支持复杂的查询、压缩、空间函数等但不支持事务和行级锁而且崩溃后无法安全恢复对于只读的数据或表比较小、可以忍受修复操作则可以继续使用MyISAMMyISAM会将表存储在两个文件中数据文件.MYD为扩展名和索引文件.MYI为扩展名)MyISAM支持表锁即对整张表进行加锁读取时会对需要读到的所有表加共享锁写入时对表加排他锁。但是在表有读取查询的同时也可以往表插入新的记录并发插入CONCURRENT INSERT)使用了MyISAM存储引擎的表MySQL可以手工或自动执行检查check table mytable和修复repair table mytable)操作但执行表的修复可以导致一些数据丢失并且修复操作是非常慢的对于BLOB和TEXT等长字段可以基于其前500个字符创建索引创建MyISAM表的时候如果指定了DELAY_KEY_WRITE(延迟更新索引键选项在每次修改执行完时不会立刻将修改的索引数据写入磁盘而是会写到内存的键缓冲区in-memory key buffer中只有在清理键缓冲区或关闭表时才将对应的索引块写入到磁盘。这种方式大大的提升写入性能但是在数据库或主机崩溃时会造成索引损坏需要进行修复操作。如果表在创建并导入数据后不会再进行修改操作可以使用MyISAM压缩表使用myisampack对MyISAM表进行压缩也叫打包pack压缩表是不能进行修改的除非先将表解除压缩修改数据然后再次压缩压缩表可以极大的减少磁盘空间占用和磁盘I/O从而 提升了查询性能压缩表支持索引但索引是只读的InnoDBMySQL默认的事务型引擎用来处理大量的短期short-lived事务短期事务大部分情况是正常提交的很少会被回滚。InnoDB的性能和自动崩溃恢复特性使得它在非事务型存储的需求中也很流行。MySQL5.1新的InnoDB plugin支持一些新特性比如利用排序创建索引、删除或增加索引时不需要复制全表数据、新的支持压缩的存储格式、新的大型列值如BLOB的存储方式、以及文件格式管理等。InnoDB采用MVCC来支持高并发并且实现了四个标准的隔离级别。其默认级别是repeatable read可重复读并且通过间隙锁next-key-locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询设计的行还会对索引中的间隙进行锁定防止幻影行的插入InnoDB表是基于聚簇索引建立的聚簇索引对主键查询有很高的性能InnoDB的存储格式是平台独立的也就是说可以将数据和索引文件从Intel平台复制到PowerPc等其他平台InnoDB从磁盘读取数据时采用了可预测性预读能够自动在内存中创建hash索引以加速读操作的自适应哈希索引能够加速插入操作的插入缓冲区等InnoDB通过一些机制和工具支持真正的热备份MySQL的其他存储引擎不支持热备份要获取一致性视图需要停止对所有表的写入而在读写混合场景中停止写入可以也意味着停止读取Federated是访问其他MySQL服务器的一个代理它会创建一个到远程MySQL服务器的客户端连接并将查询传输到远程服务器执行然后提取或发送需要的数据。Archive只支持insert和select操作在MySQL5.1之前不支持索引Archive引擎会缓存所有的写并利用zlib对插入的行进行压缩所以比MyISAM表的磁盘I/O更少但是每次select都需要进行全表扫描Archive表适合日志和数据采集类应用这类应用做数据分析时往往需要全表扫描Archive引擎支持行级锁和专用的缓冲区所以支持实现高并发的插入。在一个查询开始直到返回表中存在的所有行数之前Archive引擎会阻止其他的select执行以实现一致性读。另外也实现了批量插入在完成之前对读操作也是不可见的Merge是MyISAM引擎的一个变种Merge表是由多个MyISAM表合并而来的虚拟表但是将分区功能引入后该引擎已经被放弃Memory如果需要快速地访问数据并且这些数据不会被修改重启以后丢失也没关系那么使用Memory表是非常有用的。Memory表所有的数据都保存到内存中不需要进行磁盘I/O表的结构在重启以后会保留但数据会丢失使用场景比如用于查找或映射表将邮编和州名映射的表、缓存周期性聚合数据的结果、保存数据分析中产生的中间数据支持hash索引Memory表是表级锁并发写入的性能较低不支持BLOB或TEXT类型的列并且每行的长度是固定的所以即使指定了varchar列实际存储时也会转换成char这可以导致部分内存的浪费如果使用临时表来保存中间结果该临时表使用Memory引擎但中间结果太大超出了Memory表的限制或者含有BLOB或TEXT字段则临时表会转换成MyISAM表Cluster指的是MySQL服务器、NDB集群存储引擎以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合被称为MySQL集群Blackhole没有实现任何的存储机制所有插入的数据都会丢弃不做任何保存但服务器会记录Blackhole表的日志所以可以用于复制数据到备库或只是简单地记录到日志。这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用CSV可以将普通的CSV文件逗号分割值的文件作为MySQL表来处理但不支持索引。CSV引擎可以在数据库运行时拷入或拷出文件可以将excel等电子表格软件中的数据存储为CSV文件然后复制到MySQL数据目录下就能在MySQL中打开使用。同样如果将数据写入到一个CSV引擎表其他的外部程序也能立即从表的数据文件中读取CSV格式的数据。因此CSV引擎可以作为一种数据交换的机制Example该引擎作为MySQL源代码中的示例说明了如何开始编写新的存储引擎它主要是开发人员感兴趣的。存储引擎是什么都不做的“存根”可以使用此引擎创建表但是不能将数据存储在表中或从表中检索数据。参考【网址需要翻墙】理解MySQL的体系结构https://www.rathishkumar.in/2016/04/understanding-mysql-architecture.html高性能MySQLhttps://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch01.html#ftn.CHP-1-FN-2MySQL 5.7官方文档《高性能MySQL》作者: 施瓦茨 (Baron Schwartz) / 扎伊采夫 (Peter Zaitsev) / 特卡琴科 (Vadim Tkachenko) 译者: 宁海元 / 周振兴 / 彭立勋 / 翟卫祥,刘辉