MySQL 架构

这是《高性能 MySQL（第三版）》第一章的读书笔记。

1. MySQL 逻辑架构

在 MySQL 的架构中，查询处理及其他系统任务跟数据的存储提取相分离。这个特性使得 MySQL 的用户可以自由选择不同的存储引擎。常用的存储引擎有 MyISAM 和 InnoDB。

MySQL 的架构一般分为三层：

服务层：维护连接池，实现用户的认证授权，响应用户请求。
核心层：维护查询缓存，解析 SQL 语句，优化 SQL 语句。这一次实现了所有的内置函数（日期、时间、数学、加密函数等）和跨存储引擎的功能（存储过程、触发器、视图等）。
存储引擎层：存储和提取 MySQL 中的数据。MySQL 服务器通过 API 与存储引擎通信，存储引擎 API 包含几十个底层函数。存储引擎只是响应上层服务器的请求，不会解析 SQL（例外：InnoDB 会解析外键定义，因为 MySQL 服务器没有这个功能），不同存储引擎之间也不会通信。

也可以将上面的架构分为两层：

MySQL 服务器层：即上面的服务层和核心层。
存储引擎层：同上。

1.1 连接管理与安全性

每个客户端连接都会对应服务器进程中的一个线程，一旦连接建立，这个客户端的所有请求都会在这个线程中执行。MySQL 5.5 之后的版本提供了线程池，不需要为每个客户端创建及销毁线程。

MySQL 服务器有用户名密码和证书（需要开启安全套接字连接方式）两种认证方式。对于每个用户可以分配更加具体的权限（比如对指定库的指定表限制执行的 SQL 语句）。

1.2 优化与执行

MySQL 会解析查询并创建内部数据结构（解析树），然后对解析树进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。用户可以通过特殊的关键字 hint 提示优化器以影响优化器的决策过程。如果想知道服务器如何进行优化决策的，可以请求优化器解释 explain 优化过程的各个因素，并提供参考基准便于用户重构查询和 schema、修改相关配置。

优化器不关心表的存储引擎，但存储引擎影响优化查询。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

对于 SELECT 语句，服务器在解析查询之前会先检查查询缓存（Query Cache），如果缓存中有对应的查询，服务器会立刻返回查询缓存中的结果集，而不会再去执行查询解析、优化、执行。

2. 并发控制

多个查询在同一时刻修改数据会产生并发控制问题。MySQL 在两个层面支持并发控制：服务器层和存储引擎层。

2.1 读写锁

不管是读操作还是写操作都需要注意并发。

共享锁（share lock）：也叫读锁（read lock），读锁相互不会阻塞，多个用户可以同时读。
排它锁（exclusive lock）：也叫写锁（write lock），一个写锁会阻塞其他的写锁和读锁。写锁可以确保给定的时间里只有一个用户能够写入，同时防止其他用户读到正在写入的资源。

写锁比读锁优先级高，写锁请求可能会被插入到读锁队列的前面。

2.2 锁粒度

任何时候，在给定的资源上，锁定的数据量越少则系统的并发程度越高。

但是锁的各种操作也需要消耗资源，包括加锁、获得锁、检查锁、释放锁等。

锁策略是指在锁的开销和数据的安全性之间平衡。

MySQL 每种存储引擎都可以实现自己的锁策略和锁粒度。

表锁（table lock）

表锁会锁定整张表，锁管理的开销小。用户要对表进行写操作（增删改）时需要先获得写锁，阻塞其他所有用户对表的读写操作。

尽管存储引擎可以管理自己的锁，MySQL 本身还是会使用各种表锁，例如服务器会为 ALTER TABLE 语句使用表锁而忽略存储引擎的锁机制。

行级锁（row lock）

并发高，锁开销大。行级锁只在存储引擎层实现，而 MySQL 服务器层没有实现。

3. 事务

事务是一组原子性的 SQL 查询。事务内的语句，要么全部执行成功，要么全部执行失败。事务处理系统必须符合 ACID 特性：

A：atomicity，原子性，事务是不可分割的最小工作单元，只能全部成功或全部失败。
C：consistency，一致性，数据库总是从一个一致性状态转换到另一个一致性状态。因为事务只能全部成功或全部失败，所以数据库的一致性不应该变化。
I：isolation，隔离性，事务所做的修改在提交前通常来说对其他事务不可见。跟隔离级别（Isolation level）相关。
D：durability，持久性，一旦事务提交，所做的修改永久保存到数据库中。

3.1 隔离级别

SQL 标准中定义了四种隔离级别，每种级别都规定了一个事务中所做的修改，哪些在事务内和事务间是可见的，哪些是不可见的。低级别的隔离通常可以执行更高的并发，系统开销也低。

不同存储引擎，实现的隔离级别可能不同。

MySQL 中可以通过 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别，新隔离级别会在下个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别。

READ UNCOMMITTED 未提交读

这个隔离级别中，事务的修改即使没有提交，对其他事务也是可见的。读未提交的数据称为脏读（Dirty Read）。这个隔离级别的问题多，性能高，很少用。

READ COMMITTED 提交读

这是大多数数据库的默认隔离级别（MySQL 除外）。READ COMMITTED 满足隔离性：一个事务开始时，只能看见已经提交的事务所做的修改。事务提交之前的修改对其他事务不可见。提交读也叫不可重复读（nonrepeatable read），因为执行两次同样的查询可能会得到不一样的结果。

REPEATABLE READ 可重复读

可重复读解决了脏读问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是还有幻读（Phantom READ）的问题。幻读指的是，当某个事务在读某个范围内的记录时，另外一个事务又在该范围内插入新记录，当之前的事务再次读取该记录时会产生幻行（Phantom row）。InnoDB 通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读问题。

SERIALIZABLE 可串行化

最高的隔离级别，强制事务串行隔离，避免幻读问题。会在读取的每一行上加锁，会导致大量的锁争用和超时问题。很少用。

3.2 死锁

当多个事务试图以不同的顺序锁定资源时可能产生死锁。多个事务同时锁定同一个资源时也会产生死锁。

数据库系统需要死锁检测和死锁超时机制。可以在发现死锁时放弃锁请求（不好）或回滚持有最少行级排它锁的事务（InnoDB 采用的方法）。

3.3 事务日志

事务日志可以帮助提高事务效率。

使用事务日志，存储引擎在修改表的数据时，只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不是每次都将修改的数据持久到磁盘。事务日志采用追加方式，因此写日志操作是磁盘上一小块区域内的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头。事务日志持久以后，内存中被修改的数据可以在后台慢慢刷入磁盘。大多数存储引擎是这样实现的，称为预写式日志（Write-Ahead logging），修改数据需要写两次磁盘。

如果修改的数据已经记录到事务日志并持久化，但数据本身还没有写入磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

3.4 MySQL 中的事务

MySQL 提供两种事务型存储引擎：InnoDB 和 NDB Cluster，第三方的 XtraDB 存储引擎也支持事务。

自动提交 AUTOCOMMIT

MySQL 默认采用自动提交模式，如果不是显式开始一个事务，则每个查询都被当做一个事务执行提交操作。在当前连接中可以通过设置 AUTOCOMMIT 变量来启用或禁用自动提交模式：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.06 sec)

mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

关闭自动提交时，所有的查询都在一个事务中，直到显式执行 COMMIT 或回滚 ROLLBACK，结束当前事务并启动另一个事务。修改 AUTOCOMMIT 对于不支持事务（MyISAM）的表没有影响，这些表相当于一直开启 AUTOCOMMIT。

有些命令在执行前会强制执行 COMMIT 提交当前的活动事务。例如数据定义语言 DDL 中，导致大量数据改变的操作如 ALTER TABLE 会这样。

在事务中混合使用存储引擎

MySQL 服务器层不管理事务，事务由下层的存储引擎实现。所以在同一个事务中使用多种存储引擎是不可靠的。

事务中混合使用事务型和非事务型存储引擎时（InnoDB 和 MyISAM），正常提交时没有问题，但如果发生回滚，非事务型表上的更改无法撤销，导致数据库处在不一致的状态。

隐式和显式锁定

InnoDB 采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可以锁定。锁只有在 COMMIT 或 ROLLBACK 时才会释放，且同时释放。InnoDB 会根据隔离级别在需要时自动加锁，隐式锁定。

InnoDB 支持显式锁定，但不要用。

MySQL 服务器层也支持 LOCK TABLES 和 UNLOCK TABLES 语句，一般不要用。

4. 多版本并发控制 MVCC

MVCC 是行级锁的一个变种，在很多情况下避免了加锁操作，开销更低。MySQL、Oracle、PostgreSQL 都实现了 MVCC，但各自的实现机制不同，有乐观并发控制和悲观并发控制。

MVCC 的实现是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事物看到的数据都是一致的。根据事物开始的时间的不同，每个事物对同一张表，在同一个时刻看到的数据可能是不一样的。

4.1 MVCC 示例

InnoDB 的 MVCC，是通过在每一行后面的两个隐藏的列来实现的。这两个列，一个保存行的创建时间，一个保存行的过期时间（或删除时间）。这里存储的并不是实际的时间值，而是系统版本号。每开始一个新事务，系统版本号都会自动递增。事务开始时刻的版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。在 REPEATABLE READ 隔离级别下，MVCC 操作如下：

保存这两个额外的系统版本号，使大多数读操作可以不用加锁，使读操作简单，性能好，且只读到符合标准的行。

SELECT

InnoDB 会根据以下两个条件检查每行记录：

InnoDB 只查找版本早于当前事务版本的数据行（行的系统版本号小于等于事务的系统版本号），这样可以确保事务读取的行，要么在事务开始前已经存在，要么是事务自身插入或修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取的行，在事务开始之前未被删除。

只有符合上述两个条件的记录，才能返回作为查询结果。

INSERT

InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。

DELETE

InnoDB 为删除的每一行保存当前系统版本号作为行版本号。

UPDATE

执行 UPDATE 指令时，会插入新行，删除要更新的行。
InnoDB 为插入的新记录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行最为行删除标志。

5. MySQL 的存储引擎

在文件系统中，MySQL 会为每个数据库（也叫做 schema）保存为数据目录下的一个子目录。创建表时，MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。表的定义在 MySQL 服务层统一处理，但不同存储引擎保存数据和索引的方式不同。

可以通过 SHOW TABLE STATUS 命令查看表的相关信息：

mysql> show table status like 'users'G
*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 5242880
 Auto_increment: 8
    Create_time: 2017-05-22 11:20:02
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: users
1 row in set (0.00 sec)

字段解释：

Name：表名
Engine：存储引擎类型
Version：版本号
Row_format：行格式。MyISAM 表可选值为 Dynamic、Fixed 或 Compressed。Dynamic 行包含 VARCHAR 或 BLOB 等长度可变的字段。Fixed 行的长度固定，包含 CHAR、INTEGER 等固定长度的字段。Compressed 行只在压缩表中存在。
Rows：表中的行数。对于 InnoDB 是估计值，对于 MyISAM 是精确值。
Avg_row_length：平均每行字节数。
Data_length：表的总大小（以字节为单位）。
Max_data_length：表数据的最大容量，跟存储引擎有关。
Index_length：索引大小（以字节为单位）。
Data_free：对于 MyISAM 表，表示已经分配但是没有用到的空间。包括之前删除的行和后续可用于 INSERT 的空间。
Auto_increment：下一个 AUTO_INCREMENT 的值。
Create_tim：表创建时间
Update_time：表数据的最后修改时间
Check_time：使用 CHECK TABLE 命令或 myisamchk 工具最后检查表的时间
Collation：表的默认字符集和字符列排序规则
Checksum：如果启用，保存整个表的实时校验和
Create_options：创建表时指定的其他选项
Comment：额外信息。对于 MyISAM 表保存表注释，对于 InnoDB 表保存剩余空间信息。对于视图则该列还有“VIEW”文本。

也可以查询 INFORMATION_SCHEMA 数据库中的 TABLES 表，MySQL 中所有表的信息都保存在这里：

mysql> use INFORMATION_SCHEMA;
mysql> select * from TABLES where TABLE_NAME='users' G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: szhuizhong
     TABLE_NAME: users
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 3
 AVG_ROW_LENGTH: 5461
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 32768
      DATA_FREE: 5242880
 AUTO_INCREMENT: 8
    CREATE_TIME: 2017-05-22 11:20:02
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT: users
1 rows in set (0.00 sec)

5.1 InnoDB 存储引擎

InnoDB 是 MySQL 的默认事务型引擎，被设计用来处理大量短期事务（大部分情况下会正常提交）。InnoDB 的性能和自动崩溃恢复特性使得它同样适合非事务型存储需求。

InnoDB 的数据存储在表空间中，表空间由一系列数据文件组成。InnoDB 可以将表的数据和索引放在单独的文件中。
InnoDB 通过 MVCC 支持高并发，并实现了四个标志的隔离级别。其默认级别是 REPEATABLE READ 可重复读，并通过间隙锁（next-key locking）策略防止幻读。
InnoDB 的表基于聚簇索引建立。
通过一些机制和工具支持真正的热备份（可以在允许表写入的同时获得一致性视图），包括 MySQL Enterprise Backup、XtraBackup。

5.2 MyISAM 存储引擎

MySQL 5.1 之前默认的存储引擎是 MyISAM。MyISAM 不支持事务和行级锁，且有崩溃后无法安全恢复的致命缺陷。

存储

MyISAM 会将表存储在两个文件中：数据文件（后缀是 .MYD）和索引文件（后缀是 .MYI）。MyISAM 表可以包含动态行和静态行（长度固定）。MyISAM 表可以存储的行记录数，受限于可用磁盘空间或操作系统上单个文件的最大尺寸。

MyISAM 表如果是变长行，能处理的最大数据跟指向数据记录的指针长度有个。MySQL 支持 8 字节的指针，可以通过修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项值来实现，两者相乘就是表的最大数据量。修改这两个参数会导致重建整个表和表的所有索引，耗时。

MyISAM 特性

加锁与并发

MyISAM 只支持表级锁，读时对所有要读的表加共享锁，写时对表加排它锁。

修复

对于 MyISAM 表，MySQL 可以手工或自动执行检查和修复操作。这里的修复不是事务恢复或崩溃恢复。执行表的修复可能导致数据丢失且非常慢。可以通过 CHECK TABLE table 检查表的错误，如果有错误可以通过 REPAIR TABLE table 修复。如果 MySQL 服务器已经关闭，则可以通过 myisamchk 命令行工具进行检查和修复。

索引

对于 MyISAM 表，即使是 BLOB 和 TEXT 等很长的字段，也可以基于其前 500 个字符创建索引。MyISAM 支持全文索引这种基于分词创建的索引。

延迟更新索引键（Delayed Key Write）

创建 MyISAM 表时，如果指定了 DELAY_KEY_WRITE 选项，每次修改完成时不会立刻将修改的索引数据写入磁盘，而是写入内存中的键缓冲区，只有在清理缓冲区或关闭表的时候才会将对应的索引块写入磁盘。极大提升写入性能，但如果数据库或主机崩溃会导致索引损坏，需要执行修复操作。这个特性可以全局设置，也可以只用于一个表。

MyISAM 压缩表

表创建并导入数据后，不会再修改，则可以采用 MyISAM 压缩表。

可以用 myisampack 对 MyISAM 表进行压缩。压缩表支持索引，但是索引只读。

MyISAM 性能

最大的性能问题是不支持表锁。

5.3 MySQL 内置的其他存储引擎

CSV 引擎

CSV 引擎可以将普通的 CSV 文件（逗号分隔值的文件）作为 MySQL 的表来处理，但是不支持索引。可以将 Excel 数据另存为 CSV 文件，复制到 MySQL 的数据目录下，就能在 MySQL 中打开使用。同样，数据写入 CSV 引擎表，产生的 CSV 格式的文件也是通用的。

Memory 引擎

需要快速访问数据，且数据不会修改，重启后数据丢失也没用关系，则可以使用 Memory 表，所有数据保存在内存中，比 MyISAM 表快一个数量级以上。Memory 表的表结构在重启后保留，但是数据会丢失。

Memory 表的应用场景：

用于查找表（lookup）或映射表（mapping），例如邮编对应城市的表。
用于缓存周期性聚合数据的结果
用于保存数据分析中产生的中间数据

Memory 表支持 Hash 索引，查找操作非常快，但是是表级锁，并发写入的性能很低。它不支持 BLOB 或 TEXT 类型的列，且每行的长度是固定的，即使指定了 VARCHAR 列，存储时也会转为 CHAR。

如果 MySQL 在执行查询过程中需要临时表来保存中间结果，内部使用的临时表就是 Memory 表。如果中间结果太大超出了 Memory 表的限制，或 BLOB 或 TEXT 字段，则临时表会转成 MyISAM 表。

临时表指的是用 CREATE TEMPORARY TABLE 语句创建的表，可以使用任何存储引擎。临时表只在单个连接中可见。

NDB 集群引擎

MySQL 服务器、NDB 集群存储引擎，以及分布式的、share-nothing、容灾的、高可用的 NDB 数据库组合称为 MySQL 集群（MySQL Cluster）。

5.4 第三方存储引擎

OTLP 类引擎

XtraDB 存储引擎是基于 InnoDB 的改进版本，包含在了 MariaDB 中。XtraDB 完全兼容 InnoDB。

面向列的存储引擎

MySQL 是面向行的，每一行数据一起存储，服务器的查询也是以行为单位。大数据量处理时，面向列的方式效率更高。

Infobright 是面向列的存储引擎，在大数据量（数十 TB 时），工作良好。使用 Infobright 时需要对 MySQL 进行定制。

5.5 选择合适的存储引擎

一般用 InnoDB 就可以了。

需要综合考虑的因素：

事务：需要事务支持，则选用 InnoDB 或 XtraDB。不用事务且主要是 SELECT 和 INSERT 操作（一般的日志应用），则可以用 MyISAM。
备份：需要热备份，则用 InnoDB。
崩溃恢复：需要在数据量大时，快速安全的崩溃恢复，则用 InnoDB。

5.6 转换表的引擎

转换表的存储引擎，常用的有三种：

ALTER TABLE

修改时间跟数据量成正比。修改时，MySQL 会按行将数据从从原表复制到新表中，复制期间原表加读锁，可能消耗系统所有的 I/O 能力。

mysql> ALTER TABLE report_day ENGINE=InnoDB;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

导出与导入

用 mysqldump 工具将数据导出到文件，然后修改这个文件中的 CREATE TABLE 语句的存储引擎选项，注意同时修改表名。

创建与查询

先创建新的存储引擎的表，然后用 INSERT ··· SELECT 语法导入数据：

CREATE TABLE new LIKE old;
ALTER TABLE new ENGINE=InnoDB;
INSERT INTO new SELECT * FROM old;

如果数据量大，可以分批处理，针对每一段数据执行事务提交操作，避免大事务产生过多的 undo。假设主键字段是 id，重复运行下面的语句：

START TRANSACTION;
INSERT INTO new SELECT * FROM old WHERE id BETWEEN x AND y;
COMMIT;