《高性能 MySQL》笔记——第1章 MySQL架构与历史（二）

声明：

本博客是本人在学习《高性能 MySQL》后整理的笔记，旨在方便复习和回顾，并非用作商业用途。

本博客已标明出处，如有侵权请告知，马上删除。

1.4 多版本并发控制

MySQL 的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。不仅是 MySQL，包括 Oracle、PostgreSQL 等其他数据库系统也都实现了 MVCC，但各自的实现机制不尽相同，因为 MVCC 没有一个统一的实现标准。

可以认为 MVCC 是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制不同，但大都实现了非阻塞的读操作，写操作也只锁定了必要的行。

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

前面说到不同存储引擎的 MVCC 实现是不同的，典型的有乐观（optimistic）并发控制和悲观（pessimistic）并发控制。下面我们通过 InnoDB 的简化版行为来说明 MVCC 是如何工作的。

InnoDB 的 MVVC，是通过在每行记录后面保存两个隐藏列来实现的。一个保存了行的创建时间，一个保存了行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number）。每开启一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为当前事务的版本号，用来和查询到的每行记录的版本号进行比较。

下面看一下在 REPEATABLE READ 隔离级别下，MVCC 具体是如何操作的。

SELECT

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

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

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

INSERT

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

DELETE

InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。只有 commit 的时候才会真正删除。

UPDATE

InnoDB 为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

MVCC 只在 REPEATABLE READ 和 READ COMMITTED 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容 ，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

1.5 MySQL 的存储引擎

在文件系统中，MySQL 将每个数据库（也称为 schema）保存为数据目录下的一个子目录。创建表时，MySQL 会在数据库子目录下创建一个和表同名的 .frm 文件保存表的定义。因为 MySQL 使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感和具体的平台密切相关。在 Windows 中，大小写是不敏感的；而在类 Unix 中则是敏感的。不同的存储引擎保存的数据和索引的方式是不同的，但表的定义则是在 MySQL 服务层统一处理的。

可以使用 SHOW TABLE STATUS 命令查看表的相关信息。例如，对于 mysql 数据库中的 user 表：

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> SHOW TABLE STATUS LIKE 'user'\G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2018-10-05 22:37:53
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_bin
       Checksum: NULL
 Create_options: stats_persistent=0
        Comment: Users and global privileges
1 row in set (0.04 sec)

1.5.1 InnoDB 存储引擎

InnoDB 是 MySQL 的默认事务型引擎，也是最重要、使用最广泛的存储引擎。他被设计出来处理大量的短期事务，短期事务大部分情况是正常提交的，很少会被回滚。InnoDB 的性能和自动崩溃恢复特性，使得他在非事务型存储需求中也很流行。除非要特别重要的原因需要使用其他存储引擎，否则应该优先考虑 InnoDB。

InnoDB 概览

InnoDB 的数据存储在表空间中，表空间是由 InnoDB 管理的一个黑盒子，由一系列的数据文件组成。在 MySQL4.1 以后的版本中，InnoDB 可以将每个表的数据和索引存放在单独的文件中。InnoDB 也可以使用裸设备作为表空间的存储介质，但现代的文件系统使得裸设备不再是必要选择。

InnoDB 采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别。其默认级别是 REPEATABLE READ（可重复读），并且通过间隙锁策略防止幻读的出现。间隙锁使得 InnoDB 不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，以防止幻影行的插入。

InnoDB 表是基于聚簇索引建立的，InnoDB 的索引结构和 MySQL 的其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能。不过它的二级索引（非主键索引）中必须包含主键列，所以如果主键列很大的话，其他的索引都会很大。因此，若表上的索引较多的话，主键应当尽可能的小。

InnoDB 内部做了很多优化，包括从磁盘读取数据时采用的可预测性预读，能够自动在内存中创建 hash 索引以加速读操作的自适应哈希索引(adaptive hash index)，以及能够加速插入操作的插入缓冲区(insert buffer)等。

1.5.2 MyISAM 存储引擎

在 MySQL 5.1 及之前的版本，MyISAM 是默认的存储引擎。MyISAM 提供了大量的特性，包括全文索引，压缩，空间函数（GIS）等，但 MyISAM 不支持事务和行级锁且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。正是由于 MyISAM 引擎的缘故，即使 MySQL 支持事务已经很长时间了，在很多人的概念中 MySQL 还是非事务性的数据库。尽管 MyISAM 引擎不支持事务，不支持崩溃后的安全回复，但它绝不是一无是处的。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以继续使用 MyISAM。

存储

MyISAM 会将表存储在两个文件中：数据文件和索引文件，分别以 .myd 和 .myi 为拓展名。MyISAM 表可以包含动态或者静态（长度固定）行。MySQL 会根据表的定义来决定采用何种行格式。MyISAM 表可以存储的行记录数，一般受限于可用磁盘控件，或者操作系统中单个文件的最大尺寸。

MyISAM 特性

加锁与并发：MyISAM 对整张表进行加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。但是在表有读取查询的时候，也可以往表中插入新的记录（这被称为并发插入）。

修复：对与 MyISAM 表，MySQL 可以手工或者自动执行检查和修复操作，但这里说的修复和事务回复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失，而且修复操作是非常慢的。可以通过 CHECK TABLE mytable 检查表的错误，如果有错误可以通过执行 REPAIR TABLE mytable 进行修复。另外，如果 MySQL 服务器已经关闭，可以通过 myisamchk 命令行工具进行检查和修复操作。

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

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

MyISAM 压缩表

如果表在创建并导入后，不会再进行修改操作，那么这样的表或许适合采用 MyISAM 压缩表。

可以使用 myisampack 对 MyISAM 表进行压缩（也叫打包 pack）。压缩表是不能进行修改的（除非先将表解除压缩，修改数据，然后再次压缩）。压缩表可以极大地减少磁盘空间占用，因此也可以减少磁盘 I/O，从而提升查询性能。压缩表也支持索引，但索引也是只读的。

以现在的硬件能力，对大多数应用场景，读取压缩表数据时的解压带来的开销影响并不大，而减少 I/O 带来的好处则要大得多。压缩时表中的记录是独立压缩的，所以读取单行的时候不需要去解压整个表（甚至也不解压行所在的整个页面）。

MyISAM 性能

MyISAM 引擎设计简单，数据以紧密格式存储，所以在某些场景下的性能很好。MyISAM 有一些服务器级别的性能扩展限制，比如对索引键缓冲区（key cache）的 Mutex 锁，MariaDB 基于段（segment）的索引缓冲区机制来避免该问题。但 MyISAM 最典型的性能问题还是表锁的问题，如果你发现所有的查询都长期处于 “Locked” 状态，那么毫无疑问表锁就是罪魁祸首。

1.5.3 MySQL 内建的其他存储引擎

Archive 引擎

Archive 引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前也不支持索引。Archive 引擎会缓存所有的写并利用 zlib 对插入的行进行压缩，所以比 MyISAM 表的磁盘 I/O 更少。但是每次 SELECT 查询都需要全表扫描。所以 Archive 表适合日志和数据采集类的应用，这类应用锁数据分析时往往需要全表扫描，或者在一些需要快速 INSERT 操作的场合下也可以使用。

Archive 引擎支持行级锁和专用的缓冲区，所以可以实现高并发的插入。在一个查询的开始直到返回表中存在所有行数之前，Archive 引擎会阻止其他 SELECT 的执行，以实现一致性读。另外，也实现了批量插入在完成之前对读操作是不可见的。这种机制模仿了 MVCC 的一些特性。但 Archive 不是一个事务型引擎，而是一个针对高速插入和压缩做了优化的简单引擎。

Blackhole 引擎

Blackhole 引擎没有实现任何的存储机制，他会舍弃所有插入的数据，不做任何保存。但是服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者只是简单的记录到日志。这种特殊的存储引擎可以在一些特殊的复制框架和日志审核时发挥作用，但是也会存在很多问题，所以不推荐使用。

CSV 引擎

CSV 引擎可以将普通的 CSV 文件（逗号分割值的文件）作为 MySQL 的表来处理，但是这种表不支持索引。CSV 引擎可以在数据库运行时拷入或者拷出文件。可以将 Excel 等电子表格软件中的数据存储为 CSV 文件，然后复制到 MySQL 数据目录下，就能在 MySQL 中打开使用。同样，如果将数据写入到一个 CSV 引擎表，其他外部程序也能立即从表的数据文件中读取 CSV 格式的数据。因此 CSV 引擎可以作为一个数据交换的机制非常有用。

Federated 引擎

Federated 引擎是访问其他 MySQL 服务器的一个代理，他会创建一个到远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。尽管该引擎看起来提供了一个很好的跨服务器灵活性，但是经常会带来问题，因此默认是禁用的，后续改良版本 FederatedX.

Memory 引擎

如果需要快速访问数据，并且这些数据不会被修改，重启后丢失也没关系，那么可以使用 Memory 表（以前也叫 HEAP 表）是非常有用的，Memory 比 MyISAM 至少要快一个数量级，因为所有数据都保存在内存中，不需要进行磁盘 I/O。Memory 表在重启后结构会保留，但是数据会丢失。

Memory 表可以在很多场景发挥好的作用：

• 用于查找（lookup）或者映射（mapping）表，如将邮编和州名映射的表。
• 用于缓存周期性聚合数据（periodically aggregated data）的数据。
• 用于保存数据分析中产生的中间数据。

Memory 表支持 Hash 索引，因此查找操作非常快。虽然 Memory 速度非常快，但是还是无法替代基于磁盘的表。Memory 是表级锁，因此并发写入的性能较低。他不支持 BLOB 以及 TEXT 类型的列，并且每行长度是固定的，所以即使指定了 VARCHAR 列，实际存储的时候也会转换成 CHAR，这可能导致部分内存的浪费（一些限制基本在 Percona 版本基本解决）。

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

人们经常混淆 Memory 表或者临时表，临时表是指 CREATE TEMPOARY TABLE 语句创建的表，他可以使用任何存储引擎，因此和 Memory 表不是一回事。临时表只在单个连接可见，当连接断开时，临时表也将不复存在。

Merge 引擎

Merge 引擎是 MyISAM 引擎的一个变种。Merge 表是由多个 MyISAM 表合成的虚拟表。如果将 MySQL 用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区后，该引擎被放弃。

NDB集群引擎

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

1.5.4 第三方存储引擎

略

1.5.5 选择合适的引擎

大部分情况下 InnoDB 都是正确的选择，所以 Oracle 在 MySQL5.5 版本时，将 InnoDB 作为默认的存储引擎。除非使用到了某些 InnoDB 不具备的特性，并且没有其他办法可以替代，否则都应该优先考虑 InnoDB 引擎。例如需要使用全文索引，建议优先考虑 InnoDB+Sphinx 组合，而不是使用支持全文索引的 MyISAM。当然如果不需要使用 InnoDB 的特性，同时其他特性能够更好地满足需求，可以考虑使用其他存储引擎。

除非万不得已，否则建议不要混合使用多种存储引擎，否则可能会带来一系列复杂的问题，以及一些潜在的 bug 和边界问题。存储引擎层和服务层的交互已经比较复杂，更不用说混合多个存储引擎了。至少，混合存储对一致性备份和服务器参数配置都带来了困难。

如果应用需要不同的存储引擎，请考虑以下几个因素：

• 事务：如果需要事务支持，那么 InnoDB（或者 XtraDB）是目前最稳定并且经过验证的选择。如果不需要使用事务，并且主要是使用 SELECT 和 INSERT 操作，那么 MyISAM 是一个不错的选择。一般日志型的应用比较符合这一特性。

• 备份：备份的需求也会影响存储引擎的选择。如果可以定期的关闭服务器进行备份，那么备份的因素可以忽略。反之，如果需要在线热备份，那么选择 InnoDB 就是基本要求。

• 崩溃恢复：数据量比较大的时候，系统崩溃后如何快速恢复是一个需要考虑的问题。相对而言，MyISAM 崩溃后发送损坏的概率比 InnoDB 要高很多，而且恢复速度也要慢。因此，即使不需要事务支持，很多人也选择 InnoDB 引擎，这是一个非常重要的因素。

• 特有的特性：最后有些应用可能依赖一些存储引擎所独有的特性或者优化，比如很多应用依赖聚簇索引的优化。另外，MySQL 中也只有 MyISAM 支持地理空间搜索。如果一个存储引擎拥有一些关键的特性，同时又缺乏一些必要的特性，那么有时候就不得不做折中的考虑，或者在架构设计上做一些取舍。某些存储引擎无法直接支持的特性，有时候可以通过变通也可以满足需要。

1.5.6 转换表的引擎

有很多种方法可以将表的存储引擎转换成另外一种引擎。每种方法都有其优点和缺点。下面介绍其中的三种方法：

ALTER TABLE

将表从一个引擎修改为另一个引擎最简单的版本是使用 ALTER TABLE 语句。下面的语句将 mytable 的引擎修改为 InnoDB：

ALTER TABLE mytable ENGINE=InnoDB;

上述语法可以适用于任何存储引擎。但是有一个问题：需要执行很长的时间。MySQL 会按行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的 I/O 能力，同时原表上会加上读锁。所以，在繁忙的表上执行此操作需要特别小心。一个替代方案是采用接下来将要讨论的导出导入的方法，手工进行表的复制。

如果转换表的存引擎，将会失去原引擎相关的所有特性。例如，将 InnoDB 表转为 MyISAM 表，再转换为 InnoDB，原 InnoDB 表上所有的外键将丢失。

导出与导入

为了更好控制转换的过程，可以使用 mysqldump 工具将数据导出到文件，然后修改文件中的 CREATE TABLE 语句的存储引擎选项，注意同时修改表名，因为同一个数据库中不能存在相同的表名，即使他们使用的是不同的存储引擎。同时还要注意 mysqldump 会默认在 CREATE TABLE 前加上 DROP TABLE 语句，不注意这一点可能会导致数据丢失。

创建与查询（CREATE 和 SELECT）

第三种转换的技术综合了第一种方法的高效和第二种方法的安全。不需要导出整个表的数据，而是先创建一个新的存储引擎表，然后利用 INSERT …SELECT 语法来导数据：

创建存储引擎为 MyISAM 的 myisam_table 表，并插入测试数据

mysql> create table myisam_table(name varchar(32)) ENGINE = MyISAM;
Query OK, 0 rows affected (0.12 sec)
 
mysql> insert into `myisam_table`(`name`) values('Amy');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into `myisam_table`(`name`) values('John');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into `myisam_table`(`name`) values('Rose');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from myisam_table;
+------+
| name |
+------+
| Amy  |
| John |
| Rose |
+------+
3 rows in set (0.00 sec)

创建和 myisam_table 存储引擎一样的 innodb_table，并修改其存储引擎

mysql> create table innodb_table like myisam_table;
Query OK, 0 rows affected (0.07 sec)
 
mysql> SHOW TABLE STATUS LIKE 'innodb_table'\G;
*************************** 1. row ***************************
           Name: innodb_table
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-10-07 04:09:37
    Update_time: 2018-10-07 04:09:37
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)
 
mysql>  select * from innodb_table;
Empty set (0.00 sec)
 
mysql> alter table innodb_table ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> SHOW TABLE STATUS LIKE 'innodb_table'\G;
*************************** 1. row ***************************
           Name: innodb_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: 1
    Create_time: 2018-10-07 04:10:03
    Update_time: 2018-10-07 04:09:37
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

使用 INSERT …SELECT 语句将 myisam_table 的数据插入到 innodb_table 中

mysql> insert into innodb_table select * from myisam_table;
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from innodb_table;
+------+
| name |
+------+
| Amy  |
| John |
| Rose |
+------+
3 rows in set (0.00 sec)

如果数据量不大的话，这样工作很好。如果数据量很大，则可以考虑分批处理针对每一段数据执行事务提交操作，以避免大事务产生过多的 undo。假设主键字段 id，重复执行以下语句（最小值 x 和最大值 y 的进行相应替换）将数据导入到新表：

mysql> START TRANSACTION;
 
mysql> INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
 
mysql> COMMIT;

这样操作完成后，新表是原表的一个全量复制，原表还在，如果需要可以删除原表。如果有必要可以在执行过程中对原表加锁，以确保新表和原表的一致性。