MySQL体系架构与存储引擎详解：从连接层到存储引擎全面剖析

关键词MySQL架构, 存储引擎, InnoDB, MyISAM, 体系架构, 连接层, SQL处理层, 数据目录你是否在面试中被问过MySQL的体系架构是怎样的、“InnoDB和MyISAM有什么区别”、MySQL有哪些存储引擎如何选择这些问题看似简单但涉及MySQL的核心架构和存储机制。本文将从MySQL的分支变种出发深入剖析MySQL的三层架构连接层、SQL处理层、存储引擎层详解各种存储引擎的特点和适用场景帮你全面掌握MySQL体系架构。目录MySQL的分支与变种DrizzleMariaDBPercona ServerMySQL的替代方案PostgreSQLSQLiteMySQL体系架构连接层SQL处理层Server层存储引擎层MySQL存储引擎详解InnoDB存储引擎MyISAM存储引擎其他存储引擎存储引擎的选择与转换MySQL数据目录详解1. MySQL的分支与变种MySQL有几个久经考验的主流变种都有活跃的用户社区和商业支持。1.1 Drizzle特点真正的MySQL分支完全开源不与MySQL兼容不能简单替换进行了重大更改SQL语法变化大设计目标是解决高可用性问题清除了表现不佳和不必要的功能将语言从C换成了C适用场景多核服务器、64位Linux机器云计算服务器高并发网站每分钟数以万计点击率需要大幅度削减服务器成本的场景1.2 MariaDB背景MySQL创建者Monty Widenius创立不认同MySQL开发流程而离开Sun公司目标是社区开发Bug修复和提供新特性特点原版MySQL的超集不需要任何修改就可以运行已有系统提供MySQL的所有功能还提供其他功能由产品驱动非赢利目的用户Google、Facebook、维基百科等1.3 Percona Server背景由领先的MySQL咨询公司Percona发布口号是The Database Performance Experts创始人是《高性能MySQL》的作者特点与MySQL向后兼容的替代品不改变SQL语法、客户端/服务器协议和文件格式任何运行在MySQL上的都可以运行在Percona Server上切换时不需要导出和重新导入数据三个主要目标透明增加查看服务器内部信息和行为的方法性能包含性能和可扩展性改进操作灵活性提供可靠稳定运行的便利用户阿里内部运行上千个Percona Server实例2. MySQL的替代方案2.1 PostgreSQL特点世界上最先进的开源数据库全栈数据库一专多长1985年在加州大学伯克利分校开发优势稳定性极强崩溃、断电等灾难场景下依然保证数据正确高并发性能负载逼近极限时性能维持双曲线甚至对数曲线GIS领域优势丰富的几何类型大量字典、数组、bitmap等数据类型适用场景绝对需要可靠性和数据完整性的场景地理信息系统GIS劣势商业支持不如MySQL文档资料和易用性不如MySQL第三方支持不如MySQL2.2 SQLite特点世界上部署最广泛的数据库引擎**物联网IoT**首选数据库手机、PDA、MP3播放器的首选优势代码占用空间小不需要数据库管理员维护没有单独的服务器进程提供基本符合ACID的事务劣势简单意味着功能和性能受限不适合高并发、大数据量场景3. MySQL体系架构MySQL由以下部分组成连接池管理工具和服务SQL接口解析器优化器缓存存储引擎文件系统3.1 连接层职责处理客户端连接和权限验证工作流程MySQL启动后等待客户端连接每个客户端连接请求服务器创建一个线程处理交互客户端断开后线程缓存起来等待下一个客户端每个线程独立拥有各自的内存处理空间最大连接数SHOWVARIABLESLIKE%max_connections%;权限验证用户名、IP、密码验证验证是否具有执行特定查询的权限3.2 SQL处理层Server层核心功能SQL语句的解析、优化缓存的查询MySQL内置函数的实现跨存储引擎功能存储过程、触发器、视图等SQL执行流程查询缓存检查SELECT语句查询缓存是否有相应结果有则直接返回无则进行下一步解析查询创建内部数据结构解析树SQL语句的语义与语法解析优化重写查询决定表的读取顺序选择需要的索引询问存储引擎操作的开销信息查询缓存了解即可重要提示MySQL 8.0已移除查询缓存生产环境建议不开启严格要求两次SQL请求完全一样包括大小写移除原因命中率不可预测性能提升不稳定受单个互斥锁保护多核服务器上大量查询会导致互斥锁争用弊大于利-- 查看查询缓存配置5.7及之前版本SHOWVARIABLESLIKE%query_cache_type%;SHOWVARIABLESLIKE%query_cache_size%;3.3 存储引擎层核心特点插件式的表存储引擎架构存储引擎是基于表的而不是数据库优势每个存储引擎有各自的特点根据具体应用建立不同存储引擎表用户可以编写自己的存储引擎可以修改源码得到想要的特性注意存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。4. MySQL存储引擎详解4.1 InnoDB存储引擎地位MySQL的默认事务型引擎最重要、使用最广泛的存储引擎OLTP应用中使用最广泛的存储引擎特点事务支持支持ACID事务处理大量短期事务崩溃恢复自动崩溃恢复特性行级锁支持高并发外键支持支持外键约束适用场景需要事务支持的场景高并发读写需要崩溃恢复能力的场景建议除非有非常特别的原因否则应该优先考虑InnoDB引擎。4.2 MyISAM存储引擎历史MySQL 5.1及之前版本的默认存储引擎特点不支持事务不支持行级锁表级锁不支持崩溃恢复提供全文索引、压缩、空间函数GIS等特性锁机制对整张表加锁而不是针对行读取时加共享锁写入时加排他锁很容易因为表锁导致性能问题适用场景只读的数据表比较小、可以忍受修复操作需要全文索引且数据量不大警告请不要默认使用MyISAM应当默认使用InnoDB。4.3 其他存储引擎Archive引擎只支持INSERT和SELECT操作缓存所有写并利用zlib压缩插入的行每次SELECT都需要全表扫描适用场景日志和数据采集类应用需要高速INSERT的场合Blackhole引擎没有实现任何存储机制丢弃所有插入的数据不做任何保存服务器会记录日志适用场景特殊的复制架构和日志审核不推荐使用CSV引擎可以将CSV文件作为MySQL表处理不支持索引可以在数据库运行时拷入或拷出文件适用场景数据交换机制Federated引擎访问其他MySQL服务器的代理创建到远程MySQL服务器的客户端连接将查询传输到远程服务器执行默认禁用经常带来问题Memory引擎数据存储在内存中表结构在重启后保留数据丢失支持Hash索引查找操作非常快表级锁并发写入性能较低不支持BLOB或TEXT类型适用场景快速访问且不会被修改的数据重启后丢失也没关系NDB集群引擎MySQL集群MySQL Cluster分布式的、share-nothing的、容灾的、高可用的4.4 第三方存储引擎Percona的XtraDB存储引擎基于InnoDB的改进版本改进点性能、可测量性、操作灵活性可以兼容地读写InnoDB的数据文件支持InnoDB的所有查询TokuDB引擎使用**分形树Fractal Trees**索引数据结构缓存无关大小超过内存性能也不会下降高压缩比适合大数据量写性能优于InnoDB的B树读性能略低核心思想利用节点的MessageBuffer缓存更新操作将随机写转换为顺序写。Infobright面向列的存储引擎适合大数据量数十TB数据高度压缩按块排序不支持索引块结构是一种准索引适用场景数据分析和数据仓库应用5. 存储引擎的选择与转换5.1 如何选择存储引擎黄金法则除非需要用到某些InnoDB不具备的特性并且没有其他办法可以替代否则都应该优先选择InnoDB引擎。特殊情况MySQL中只有MyISAM支持地理空间搜索不在乎可扩展能力和并发能力不在乎崩溃后的数据丢失问题对InnoDB的空间占用过多比较敏感警告除非万不得已否则建议不要混合使用多种存储引擎混合存储对一致性备份和服务器参数配置带来困难5.2 存储引擎的转换方法1ALTER TABLEALTERTABLEmytableENGINEInnoDB;特点适用任何存储引擎执行时间长按行复制数据复制期间加读锁繁忙的表上执行要小心方法2导出与导入使用mysqldump工具导出数据到文件修改CREATE TABLE语句的存储引擎选项修改表名同数据库不能存在相同表名方法3CREATE和SELECT-- 创建新表CREATETABLEinnodb_tableLIKEmyisam_table;-- 修改存储引擎ALTERTABLEinnodb_tableENGINEInnoDB;-- 导入数据INSERTINTOinnodb_tableSELECT*FROMmyisam_table;数据量大时考虑分批处理针对每一段数据执行事务提交。5.3 查看存储引擎-- 查看MySQL提供的所有存储引擎SHOWENGINES;-- 查看当前默认的存储引擎SHOWVARIABLESLIKE%storage_engine%;5.4 InnoDB与MyISAM对比特性InnoDBMyISAM事务支持✅ 支持❌ 不支持行级锁✅ 支持❌ 表级锁崩溃恢复✅ 支持❌ 不支持外键✅ 支持❌ 不支持全文索引✅ 5.6支持✅ 支持空间函数✅ 支持✅ 支持压缩✅ 支持✅ 支持并发性能高低适用场景高并发读写、事务只读、小表6. MySQL数据目录详解6.1 确定数据目录-- 查看数据目录位置SHOWVARIABLESLIKEdatadir;可以通过配置文件修改数据目录位置。6.2 数据目录中的内容6.2.1 数据库在文件系统中的表示创建数据库时的操作在数据目录下创建和数据库名同名的子目录在该子目录下创建db.opt文件包含数据库属性字符集、比较规则注意information_schema数据库比较特殊在数据目录下没有对应的子目录。6.2.2 表在文件系统中的表示表信息分为两部分表结构的定义表名.frm二进制格式表中的数据不同存储引擎不同6.2.3 InnoDB如何存储表数据表空间Table Space抽象概念可以对应文件系统上一个或多个真实文件表空间被划分为很多页表数据存放在某些页里系统表空间System Tablespace默认对应数据目录下的ibdata1文件12M自扩展文件不够用时会自动增加大小MySQL 5.5.7到5.6.6之间数据默认存储到系统表空间独立表空间File-per-table TablespaceMySQL 5.6.6为每个表建立独立表空间文件名表名.ibd存放表的数据和索引控制参数innodb_file_per_table# 使用系统表空间 innodb_file_per_table0 # 使用独立表空间默认 innodb_file_per_table1其他表空间通用表空间general tablespaceundo表空间undo tablespace临时表空间temporary tablespace6.2.4 MyISAM如何存储表数据数据和索引分开存放文件说明表名.frm表结构定义表名.MYD数据文件MYData表名.MYI索引文件MYIndex6.3 日志文件MySQL在运行过程中会产生各种日志日志类型说明用途错误日志记录启动、运行、关闭过程定位问题慢查询日志记录执行时间超过阈值的SQLSQL优化查询日志记录所有对MySQL的请求审计二进制日志记录所有DDL和DML语句恢复、复制、审计慢查询日志-- 查看是否开启SHOWVARIABLESLIKEslow_query_log;-- 开启慢查询日志SETGLOBALslow_query_log1;-- 查看阈值默认10秒SHOWVARIABLESLIKE%long_query_time%;-- 设置阈值SETGLOBALlong_query_time2;-- 查看是否记录未使用索引的查询SHOWVARIABLESLIKE%log_queries_not_using_indexes%;二进制日志Binlog作用恢复recoverypoint-in-time恢复复制replication主从同步审计audit安全审计配置# my.cnf配置 log_bin mysql-bin binlog-format ROW server-id 1 expire_logs_days 30注意默认未启动需要手动开启开启后性能下降约1%总结本文全面介绍了MySQL的体系架构和存储引擎核心知识点MySQL分支Drizzle不兼容高性能、MariaDB超集、Percona Server向后兼容替代方案PostgreSQL稳定性强、SQLite嵌入式三层架构连接层处理连接和权限验证SQL处理层解析、优化、执行SQL存储引擎层数据的物理存储和读取存储引擎InnoDB默认引擎支持事务、行锁、崩溃恢复MyISAM不支持事务和行锁表级锁其他Archive日志、Memory内存、CSV数据交换数据目录数据库对应子目录InnoDB独立表空间.ibd或系统表空间ibdata1MyISAM.frm、.MYD、.MYI三个文件实际应用建议默认使用InnoDB除非有特殊需求不要混合使用多种存储引擎数据量大时独立表空间便于管理开启慢查询日志用于SQL优化开启二进制日志用于数据恢复和主从复制面试高频问题MySQL的体系架构是怎样的三层架构InnoDB和MyISAM的区别事务、锁、崩溃恢复如何选择存储引擎默认InnoDBMySQL有哪些日志错误、慢查询、查询、二进制InnoDB的数据文件有哪些.frm、.ibd、ibdata1、ib_logfile什么是独立表空间有什么好处每个表一个文件便于管理希望这篇文章能帮助你全面掌握MySQL体系架构如果觉得有帮助欢迎点赞、收藏、关注推荐标签MySQL体系架构存储引擎InnoDBMyISAM数据目录面试