mysql|MySQL（基础架构与存储引擎） mysql|mysql

参考资料：
《mysql-基础架构分析》
《MySQL 工作流》
《mysql存储引擎》
写在开头：本文为学习后的总结，可能有不到位的地方，错误的地方，欢迎各位指正。
目录
一、基础架构
1、基本架构概览
2、Server 层基本组件介绍
（1）连接器
（2）查询缓存
（3）分析器
（4）优化器
（5）执行器
二、存储引擎
1、常见存储引擎
2、常用存储引擎的介绍
（1）MyISAM
（2）InnoDB
3、使用时的选择

一、基础架构 1、基本架构概览 MySQL 可以分为 Server 层和存储引擎层两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
存储引擎主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始被当做默认存储引擎。

文章图片

Server 层一般包括以下几个功能模块，提供了除数据查询外的绝大部分功能：

连接器：身份认证和权限相关(登录 MySQL 的时候)。
查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL 认为最优的方案去执行。
执行器：执行语句，然后从存储引擎返回数据。

2、Server 层基本组件介绍

文章图片

（1）连接器
连接是整个数据库操作的第一步，因而连接器主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作。
MySQL 客户端/服务端通信是半双工模式：即任一时刻，要么是服务端向客户端发送数据，要么是客户端向服务器发送数据。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。客户端如果太长时间没动静，连接器就会自动将它断开。客户端连接维持时间是由参数 wait_timeout 控制的，默认值是 8 小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。
建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。为了在程序中提高数据库连接的服用了，一般会使用数据库连接池来维护管理。
但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
为了解决这个问题，你可以考虑以下两种方案：
定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

（2）查询缓存
8.0版本已将该模块删除，老版本也不建议使用数据库缓存，因为往往弊大于利。
解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。
因此MySQL 提供了“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样：

select SQL_CACHE * from T where ID=10;

（3）分析器
如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。MySQL 通过关键字对 SQL 语句进行解析，并生成一颗对应的语法解析树。这个过程中，分析器主要通过语法规则来验证和解析。比如 SQL 中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据 MySQL 规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

分析器先会先做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。
接下来，要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”。

（4）优化器
经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。
经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成执行计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。
MySQL 使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。

mysql> select * from t_message limit 10; ...省略结果集mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name| Value| +-----------------+-------------+ | Last_query_cost | 6391.799000 | +-----------------+-------------+

示例中的结果表示优化器认为大概需要做 6391 个数据页的随机查找才能完成上面的查询。这个结果是根据一些列的统计信息计算得来的，这些统计信息包括：每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等。
有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL 认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短）等等。
MySQL 的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

重新定义表的关联顺序（多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序）
优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找 B+Tree 索引最左端，反之则可以找到最大值，具体原理见下文）
提前终止查询（比如：使用 Limit 时，查找到满足数量的结果集后会立即终止查询）
优化排序（在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于 I/O 密集型应用，效率会高很多）

（5）执行器
在完成解析和优化阶段以后，MySQL 会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL 在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

二、存储引擎在文件系统中，Mysql 将每个数据库（也可以成为 schema）保存为数据目录下的一个子目录。创建表时，Mysql 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。因为 Mysql 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体平台密切相关。Windows 中大小写不敏感；类 Unix 中大小写敏感。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在 Mysql 服务层统一处理的。
1、常见存储引擎可以使用SHOW ENGINES; 查询支持的存储引擎：

mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine| Support | Comment| Transactions | XA| Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED| NO| Federated MySQL storage engine| NULL| NULL | NULL| | MEMORY| YES| Hash based, stored in memory, useful for temporary tables| NO| NO| NO| | InnoDB| DEFAULT | Supports transactions, row-level locking, and foreign keys| YES| YES| YES| | PERFORMANCE_SCHEMA | YES| Performance Schema| NO| NO| NO| | MyISAM| YES| MyISAM storage engine| NO| NO| NO| | MRG_MYISAM| YES| Collection of identical MyISAM tables| NO| NO| NO| | BLACKHOLE| YES| /dev/null storage engine (anything you write to it disappears) | NO| NO| NO| | CSV| YES| CSV storage engine| NO| NO| NO| | ARCHIVE| YES| Archive storage engine| NO| NO| NO| +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)

InnoDB - Mysql 的默认事务型存储引擎，并且提供了行级锁和外键的约束。性能不错且支持自动崩溃恢复。
MyISAM - Mysql 5.1 版本前的默认存储引擎。特性丰富但不支持事务，也不支持行级锁和外键，也没有崩溃恢复功能。
CSV - 可以将 CSV 文件作为 Mysql 的表来处理，但这种表不支持索引。
Memory - 适合快速访问数据，且数据不会被修改，重启丢失也没有关系。
NDB - 用于 Mysql 集群场景。

其中最常见的存储引擎主要为MyISAM与InnoDB。
2、常用存储引擎的介绍 【mysql|MySQL（基础架构与存储引擎）】注意，引擎是以表为单位的而非以库为单位，也就是说我们可以在一个库中使用不同的引擎（虽然不太必要），引擎相关的执行语句如下：

# 建表是指定引擎 CREATE TABLE `test_atble` ( `BELONGXIAQUCODE` varchar(100) DEFAULT NULL, `OPERATEUSERNAME` varchar(100) DEFAULT NULL, `OPERATEDATE` datetime DEFAULT NULL, `ROW_ID` int(11) NOT NULL AUTO_INCREMENT, `YEARFLAG` varchar(8) DEFAULT NULL, `ROWGUID` varchar(100) NOT NULL, PRIMARY KEY (`ROWGUID`), KEY `ROW_ID` (`ROW_ID`) USING BTREE, ) ENGINE=InnoDB AUTO_INCREMENT=612142 DEFAULT CHARSET=utf8# 修改引擎为InnoDB ALTER TABLE mytable ENGINE = InnoDB

（1）MyISAM
由 .frm、MYD和MYI组成，其中MYD（MYData）用来存放数据文件,而MYI(MYIndex)则用来存放索引文件，.frm存储表定义。
不支持事务。不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。
默认BTREE索引，只有非聚簇索引。
支持压缩表和空间数据索引,但MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
（2）InnoDB
InnoDB 是 MySQL 默认的存储引擎，支持事务，支持行级锁，并发性能更高，也更消耗资源。
InnoDB 也使用 B+Tree 作为索引结构，但具体实现方式却与 MyISAM 截然不同。MyISAM 索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB 中，表数据文件（.ibd文件）本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此InnoDB 表数据文件本身就是主索引。
InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是可重复读（REPEATABLE READ），并且通过间隙锁（next-key locking）防止幻读。
InnoDB 是基于聚簇索引建立的，与其他存储引擎有很大不同。在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
3、使用时的选择大多数情况下，我们都会选择默认的InnoDB引擎，除非需要用到 InnoDB 不具备的特性。
如果应用需要选择 InnoDB 以外的存储引擎，可以考虑以下因素：

事务：如果需要支持事务，InnoDB 是首选。如果不需要支持事务，且主要是 SELECT 和 INSERT 操作，MyISAM 是不错的选择。所以，如果 Mysql 部署方式为主备模式，并进行读写分离。那么可以这么做：主节点只支持写操作，默认引擎为 InnoDB；备节点只支持读操作，默认引擎为 MyISAM。
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。所以，InnoDB 并发性能更高。
外键：InnoDB 支持外键。
备份：InnoDB 支持在线热备份。
崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
其它特性：MyISAM 支持压缩表和空间数据索引。