MySQL 系列教程（笔记）

# MySQL 的存储引擎

本节只是概要的描述 MySQL 的存储引擎，不会涉及太多的细节，因为存储引擎的讨论及其相关特性将会贯穿全书。本节知识也不是完全指南，如果有需要，则有必要去阅读对应存储引擎的官方文档。

在文件系统中，MySQL 将每个数据库（也称为 schema）保存为数据目录下的一个子目录。创建表时，MySQL 会在目录下创建一个和表同名的 .frm 文件保存表的定义。表定义是在 MySQL 服务层统一处理的，大小写敏感问题和具体的系统平台有关系（windows 不敏感），保存数据和索引的方式则是各个存储引擎实现的。

可以使用以下语句显示表的相关信息，例如，查看 mysql 数据库中的 user 表：

mysql> use mysql
-- 在 5.0 以后的版本中，也可以查询 INFORMATION_SCHEMA 中对应的表
mysql> SHOW TABLE STATUS LIKE 'user' \G;
*************************** 1. row ***************************
           Name: user
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2020-04-15 11:26:47
    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.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

注意：笔者这里使用的是 MySQL8.0

上述是一个 InnoDB 的表，各项字段含义如下：

  • Name：表名

  • Engine：存储引擎类型，在旧版本中，该列为 Type

  • Row_format：行的格式

    对于 MyISAM 表，可选值为

    • Dynamic：动态，行长度是可变的，一般包含可变长度的字段，如 VARCHAR、BLOB
    • Fixed ：固定，行长度是固定的，只包含固定长度的列，如 CHAR、INTEGER
    • Compressed：行只在压缩表中存在，该部分可参考 MyISAM 压缩表一节

  • Rows：表中的行数，在 InnoDB 中，该值是一个估计值

  • Avg_row_length：平均每行包含的字节数

  • Data_length：表数据的大小（字节）

  • Max_data_length：表数据的最大容量，该值和存储引擎有关

  • Index_length：索引的大小（字节）

  • Data_free：

    对于 MyISAM 表，表示已分配但目前没有使用的空间。包含了之前删除的行，以及后续可以被 INSERT 利用到的空间

  • Auto_increment：下一个 AUTO_INCREMENT 的值

  • Create_time：表创建信息

  • Update_time：表数据的最后修改时间

  • Check_time：使用 CKECK TABLE 命令或则 myisamchk 工具最后一次检查表的时间

  • Collation：表示默认字符集和字符列排序规则

  • Checksum：如果启用，保存的是整个表的实时校验和

  • Create_options：创建表时指定的其他选项

  • Comment：该列包含了一些其他的额外信息。

    • 对于 MyISAM 表，保存的是表在创建时带的注释。
    • 对于 InnoDB 表，保存的是 InnoDB 表空间的剩余空间信息。

    如果是一个视图，则该列包含 “VIEW” 的文本字样

# InnoDB 存储引擎

InnoDB 是 MySQL 的默认事务型处理引擎，最重要，使用最广泛的存储引擎。被设计用来处理大量的 短期（short-lived）事务，短期事务大部分情况下是正常提交的，很少会被回滚。

InnoDB 的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中也很流行。

总之来说，对于使用和学习都应该优先选择 InnoDB，这是收益最大的。除非有特别的原因。

# InnoDB 的历史

他的历史很复杂，了解下这段历史对于理解 InnoDB很有帮助。

2008年，Oracle 发布了下一代 InnoDB 引擎， InnoDB plugin，适用于 MySQL 5.1 版本，拥有者是 InnoDB，而不是 MySQL。这基于很多原因，mysql 并没有默认集成该引擎，而是集成了旧版本的 InnoDB 引擎，而用户可以选择使用 InnoDB plugin 来覆盖掉旧的版本，直到 Oracle 收购了 Sun 公司后发布的 MySQL 5.5 中才彻底使用 InnoDB plugin 替代了旧版本的 InnoDB（意味 InnoDB plugin 已经是原生编译了，而不是一个插件，但名字已经约定成俗，很难修改了）

这个现代的 InnoDB 版本（MySQL 5.1）中的 InnoDB plugin，支持一些新特性，例如：

  • 利用排序创建索引（building index by sorting）
  • 删除或增加索引时不需要复制全表数据
  • 新的支持压缩的存储格式
  • 新的大型列值如 BLOB 的存储方式
  • 文件格式管理等

InnoDB 是一个很重要的存储引擎，很多个人和公司都对其贡献代码，而不仅仅是 Oracle 公司的开发团队。一些重要的贡献值包括 Google、Facebook 等，他们的一些改进被直接移植到官方版本，也有一些有 InnoDB 团队重新实现。在过去的几年间，InnoDB 的改进速度大大加快，主要的改进集中在可测量性、可扩展性、可配置化、性能、各种新特性和对 windows 的支持等方面。

为改善 InnoDB 的性能，Oracle 投入了大量的资源，并有很多卓有成效的工作，比如现在可以很好支持 24 核的系统，在某些场景下，32 核或更多核的系统中表现良好

# InnoDB 概览

InnoDB 的数据存储在 **表空间（tablespace）**中，表空间由一系列的数据文件组成（InnoDB 管理的一个黑盒子）。在 MySQL 4.1 以后，可以将 每个表的数据和索引放在单独的文件中 。

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

InnoDB 表是基于 聚簇索引 建立的（稍后章节讨论）。索引结构和 MySQL 的其他存储引擎有很的大不同，聚簇索引对主键查询有很高的性能。不过它的 **二级索引（secondary index，非主键索引）**中必须包含主键列，所以 如果主键列很大的话，其他的所有索引都会很大。InnoDB 的存储格式是平台独立的，也就是说数据和索引文件可以直接跨平台使用。

InnoDB 内部做了很多优化，包括

  • 从磁盘读取数据时采用的可预测性预读，
  • 能够自动在内存中创建 hash 索引以加速读取操作的自适应哈希索引（adaptive hash index），
  • 以及能够加速插入操作的插入缓冲区（insert buffer）等。

这部分知识后续详细讨论。

InnoDB 的行为是非常复杂的，强烈建议阅读官方手册中「InnoDB 事物模型和锁」一节。了解 MVCC 架构架构带来的一些微秒和细节之处是非常有必要的。存储引擎要为所有用户甚至包括修改数据的用户维持一致性的视图，是非常复杂的工作。

InnoDB 通过一些机制和工具支持真正的热备份，如：

  • MySQL Enterprise Backup：Oracle 提供
  • XtraBackup：percona 开源的

其他的存储引擎不支持热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合的场景中，停止写入可能也意味着停止读

# MyISAM 存储引擎

MySQL 5.1 之前是默认的存储引擎。提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等。但是它不支持事物和行级锁，而且还有一个缺陷是崩溃后无法安全恢复。但是它还是有价值的，在对于只读数据、或表较小、可以忍受修复（repair）操作，则可以使用它

# 存储

MyISAM 将表存储在两个文件中：

  • 数据文件：.MYD
  • 索引文件：.MYI

表可以包含动态或静态（长度固定）行，根据表的定义来决定采用哪种行格式，表可以存储的行记录数据，一般受限于可用的磁盘空间，或则操作系统中单个文件的最大尺寸

在 MySQL 5.0 中，MyISAM 表如果是变长行，则默认配置只能处理 256TB 的数据，因为指向数据记录的指针长度是 6 个字节。早期版本指针长度是 4 个字节，只能处理 4GB 的数据。而所有的 MySQL 版本都支持 8 字节的指针。可通过修改表的 MAX_ROWS 和 AVG_ROW_LENGTH 选项的值来修改 MyISAM 的表指针的长度，两者相乘就是表可能达到的最大大小。修改这两个参数会导致重建整个表和表的所有索引

# MyISAM 特性

作为最早的存储引擎之一，有一些已经开发出来很久的特性，可以满足用户的实际需求

  • 加锁与并发

    对整张表加锁，而不是针对行。读取时会对需要读到的所有表加共享锁，写入时则对表加排他锁。

    但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入，CONCURRENT INSERT）

  • 修复

    执行表的修复可能导致一些数据的丢失，而且修复操作非常慢。可以通过 CHECK TABLE mytable 检查表的错误，通过 PERAIR TABLE mytable 修复错误。如果 MySQL 服务器已经关闭，可以通过 myisamchk 命令行工具进行检查和修复操作

    注意：这里的修复和事物恢复以及崩溃恢复是不同的概念

  • 索引特性

    即使是 BLOB 和 TEXT 等长字段，也可以基于前 500 个字符创建索引。

    也支持全文索引，这是一种基于分词创建的索引，可以支持复杂的查询。第 5 章详细讲解索引

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

    创建 MyISAM 表时，如果指定了 DELAY_KEY_WRITE 选项，每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，先写入内存中的键缓冲区（in-memory key buffer），只有在清理键缓冲区或则关闭表的时候才会将对应的索引块写入到磁盘。

    这种特性极高的提升了写入性能，但是在数据库或主机崩溃时会造成索引损坏，需要执行修复操作。

    它可以全局设置，也可以为单个表设置。

# MyISAM 压缩表

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

可以使用 myisampack 对 MyISAM 表进行压缩（也叫打包 pack）。压缩表是不能进行修改的（除非先将表接触压缩，修改数据，再次压缩）。压缩表极大的减少磁盘空间，因此减少磁盘 I/O，从而提升查询性能。

也支持索引，但索引时只读的。

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

# MyISAM 性能

MyISAM 引擎设计简单，数据以紧密格式存储，在某些场景下的性能很好。

有一些服务器级别的性能扩展限制，比如对索引键缓冲区（key cache）的 Mutex 锁、MariaDB 基于段（segment）的索引键缓冲区机制来避免该问题。但 MyISAM 最典型的性能问题还是表锁的问题，如果你发现所有的查询都长期处于 「Locked」 状态，那么毫无疑问表锁就是罪魁祸首

# MySQL 内建的其他存储引擎

MySQL 还有一些有特殊用途的存储引擎，需要明确启用后才能够使用。

# Archive 引擎

只支持 INSERT 和 SELECT 操作，在 MySQL 5.1 之前也不支持索引。

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

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

# Blackhole 引擎

它没有实现任何的存储机制，会丢弃所有插入的数据，不做任何保存。

但是服务器会记录 Blackhole 表的日志，所以可以用于复制数据到备库，或者只是简单的记录到日志。

这种特殊的存储引擎可以在一些特殊的复制架构和日志审核时发挥作用，单这种应用方式我们碰到过很多问题，因此不推荐。

# CSV 引擎

它 将普通的 CSV 文件（逗号分隔值的文件）作为 MySQL 的表来处理，但是不支持索引。

它可以在数据库运行时拷入或拷出文件。同样的，如果将数据写入到一个 CSV 引擎表，其他的外部程序也能立即从表的数据文件中读取到 CSV 格式的数据。

因此 CSV 引擎可以作为一 种数据交换的机制 ，非常有用

# Fedeerated 引擎

它是访问其他 MySQL 服务器的一个代理，它会创建一个远程 MySQL 服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或发送需要的数据。

该引擎看起来提供了一种很好的跨服务器的灵活性，但是问题多多，因此默认是禁用的。 MariaDB 使用了他的一个后续改进版本，叫做 FederatedX

# Memory 引擎

快速访问、数据不会修改、重启以后丢失也没有关系，那么使用 Memory 表（以前叫 HEAP 表）。它比 MyISAM 要快一个数量级，因为数据保存在内存中，不需要磁盘 I/O。Memory 表的结构在重启后还会保留，但数据会丢失。

它在很多场景下可以发挥好的作用：

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

Memory 表支持 Hash 索引，因此查找非常快，但是无法取代传统的基于磁盘的表。

Memory 表是表级锁，因此并发写入性能较低。它不支持 BLOB 或 TEXT 类型的列，并且每行的长度是固定的，所以即使制定了 VARCHAR 列，实际存储时也会转换长 CHAR，这可能导致部分内存的浪费（其中一些限制在 Percona 版本已经解决）

如果 MySQL 在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是 Memory 表。如果中间结果超出了 Memory 表的限制，或则含有 BLOB 或 TEXT 字段，则临时表会转换成 MyISAM 表。在后续章节中还会继续讨论该问题。

TIP

注意：Memory 表和临时表不是一个东西。

临时表是指使用 CREATE TEMPORARY TABLE 语句创建的表，它可以使用任何存储引擎，因此和 Memory 表不是一回事。临时表只在单个链接中可见，当链接断开时，临时表也会消失。

# Merge 引擎

它是 MyISAM 引擎的一个变种。Merge 表是由多个 MyISAM 表合并而来的虚拟表。如果将 MySQL 用于日志或则数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃（参考第 7 章）

# NDB 集群引擎

2003 年 MySQL AB 公司收购了索尼爱立信公司的 NDB 数据库，开发了 NDB 集群存储引擎，作为 SQL 和 NDB 原生协议之间的接口。

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

本章后续会有章节来专门讨论 MySQL 集群

# 第三方存储引擎

MySQL 从 2007 年开始提供了插件式的存储引擎 API，从此出现了一系列为不同目的而设计的存储引擎。其中一些已经合并到 MySQL 服务器，但大多是第三方产品或是开源项目。

下面探讨一些我们认为在它设计的场景中确实很有用的第三方存储引擎。

# OLTP 类引擎

Percona 的 XtraDB 存储引擎是基于 InnoDB 引擎的一个改进版本，已经包含在 Percona Server 和 Maria DB 中，它的改进点主要集中在性能、可测量性和操作灵活性方面。XtraDB 可以作为InnoDB 的一个完全的带产品，甚至可以兼容地读写 InnoDB 的数据文件，并且支持 InnoDB 的所有查询

另外还有 一些和 InnoDB 非常类似的 OLTP 类存储引擎，比如都支持 ACID 事物和 MVCC。

比如 PBXT，由 Paul McCullagh 和 Primebase GMBH 开发。支持引擎级别的复制、外键约束，并且以一种比较复杂的架构对固态存储（SSD）提供了适当的支持，还对较大的值类型如 BLOB 也做了优化。PBXT 是一款社区支持的存储引擎，MariaDB 包含了该引擎

TokuDB 引擎使用了一种新的叫做 分形树（Fractal Trees） 的索引数据结构。该结构是缓存无关的，因此即使 大小超过内存，性能也不会下降，也就没有内存生命周期和碎片的问题。它是一种 大数据（Big Data）存储引擎。因为其拥有很高的 压缩比，可以 在很大的数据量上创建大量索引。在本书写作时，该引擎还处于早期生产版状态，在并发性方面还有很多的限制。目前最适合在需要大量插入数据的分析型数据集的场景中使用。

RethinkDB 最初是为固态存储（SSD）而设计的，目前看起来与最初的目标有一定的差距。该引擎比较特别的地方在于采用了一种 **只能能追加的写时复制 B 树（append-only copyon-write B-Tree）**作为索引的数据结构。目前也处于早期发展状态，没有听说过有实际的应用案例

在 Sun 收购 MySQL AB 以后，Falcon 存储引擎曾经被赋予厚望，现在该项目被取消很久了。Falcon 的主要设计者 Jim Starkey 创立了一家新公司，主要做开源支持云计算的 NewSQL 数据库产品，叫做 NuoDB（之前交 NimbusDB）。

# 面向列的存储引擎

MySQL 默认是面向行的，每一行的数据是一起存储的，服务器的查询也是以行为单位处理的。而在大数据量处理时，面向列的方式可能效率更高。如果不需要整行的数据，面向列的方式可以传输更少的数据，如果每一列都单独存储，那么压缩的效率也会更高。

Infobright 是最有名的面向列的存储引擎。在非常大的数据量（树十 TB）时，该引擎工作良好。它是为 数据分析 和 数据仓库 应用设计的。数据高度压缩，安装块进行排序，每个块都对应有一组元数据。在处理查询时，访问元数据可以决定跳过该块，甚至可能只需要元数据即可满足查询的需求。但该引擎不支持索引，不过在这么大的数据量级，即使有索引页很难发挥作用，而且 块结构也是一种准索引（quasi-index）。 Infobright 需要对 MySQL 服务器做定制，因为一些地方需要修改以适应面向列存储的需要。如果查询无法在存储层使用面向列的模式执行，则需要在服务器层转换成按行处理，这个过程会很慢。 Infobright 有社区版和商业两个版本。

InfiniDB 是 Calpont 公司的一个面向列的存储引擎。也有社区版本和商业版。InfiniDB 可以在一组机器集群间做分布式查询，单目前也还有生产环境的应用案例。

# 社区存储引擎

社区存储引擎就很多了，可能有超过 3 位的数量，但是其中大部分影响力有限，从未听说或只有极少数人在使用。这里列举一些，也大都没有在生产环境应用过，慎用！

  • Aria（原名 Maria）

    MySQL 创建者计划用来代替 MyISAM 的一款引擎。MariaDb 在服务层，实现了该引擎的大部分特性，所以后来取消了。在本书写作之际，Aria 解决了崩溃安全恢复问题的 MyISAM，还有新特性：数据的缓存（MyISAM 只能缓存索引）

  • Groonga

    一款全文索引引擎，号称可以提供准确而高效的全文索引

  • OQGraph

    Open Query 研发，支持图操作（比如查找两点之间的最短路径），用 SQL 很难实现该类操作

  • Q4M

    该引擎在 MySQL 内部实现了队列操作，而用 SQL 很难再一个语句实现这类队列操作

  • SphinxSE

    为 Sphinx 全文索引搜索服务器提供了 SQL 接口

  • Spider

    该引擎可以将数据分成不同的分区，比较搞笑透明的实现了分片（shard），并且可以针对分片执行并行查询（分片可以分布在不同的服务器上）

  • VPForMySQL

    支持垂直分区，通过一系列的代理存储引擎实现。

    垂直分区：指可以将表分成不同列的组合，并且单独存储。但对查询来说，看到的还是一张表。该引擎和 Spider 的作者是同一人

# 选择合适的引擎

在大部分情况下，InnoDB 都是正确的选择，所以在 MySQL 5.5 时 InnoDB 作为默认的存储引擎了。

总结为一句话：除非需要用到某些 InnoDB 不具备的特性，并且没有其他办法可以替代，否则都应该优先选择 InnoDB 引擎。

例如，要用到全文索引，建议优先考虑 InnoDB + Sphinx 的组合，而不是使用支持全文索引的 MyISAM。

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

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

  • 事物

    需要事物，选择 InnoDB（或 XtraDB）比较稳当，且经过验证的选择。

    不需要事物，并且主要是 SELECT 和 INSERT 操作，那么可以选择 MyISAM，一般日志型的应用比较符合这一特性

  • 备份

    如果可以定期的关闭服务器来执行备份，那么备份的因素可以忽略。反之，需要在线热备份，选择 InnoDB 就是基本的要求

  • 崩溃恢复

    数据量比较大的时候，系统崩溃后如何快速地恢复，是一个需要考虑的问题。

    相对而言，MyISAM 崩溃后发生损坏的概率比 InnoDB 要高很多，而且恢复速度也要慢。因此即使不需要特性很多人也会选择 InnoDB。

  • 特有的特性

    有些应用可能依赖一些特定或独有的特性或者优化。比如很多应用依赖 聚簇索引的优化。

    另外，MySQL 中也只有 MyISAM 支持地理空间搜索。所以当一个存储引擎不存在你完全想要的特性的话，要么折中，要么修改架构。某些存储引擎无法支持的特性，有时候通过变通也可以满足要求。

本书在后续还会继续提供很多关于各种存储引擎的优缺点详细描述，也会讨论一些架构设计的技巧。当然选择是依赖应用场景的，且上面提到的概念也都是比较抽象的。所以接下来会讨论一些场景的应用场景，在这些场景中会涉及很多的表，以及这些表如何选用合适的存储引擎。

等到阅读完这些之后，你再针对自己的业务场景进行选择，如果还是无法选择，那么就使用 InnoDB，比较安全。

# 日志型应用

比如你需要实时记录一台中心电话交换机的每一通电话的日志到 MySQL 中，或则通过 Apache 的 mod_log_sql 模块将网站的所有访问信息直接记录在表中。

这一类应用的插入速度有很高的的要求，MyISAM 或则 Archive 存储引擎对这类应用比较适合，因为他们 开销低，而且 插入速度非常快

如果需要对记录的日志做分析报表，生成报表的SQL 很有可能会导致插入效率明显降低，这时候该怎么办？

一种是：利用 MySQL 内置的复制方案将数据复制一份到备库，在备库上执行比较消耗时间和 CPU 的查询。当然也可以在系统负载较低的时候执行统计操作，只是应用在不断变化，后续有可能会出现问题

另一种：在日志记录表的名字中包含年和月的信息，比如 web_logs_2012-01 ，这样可以在已经没有插入操作的历史表上做频繁的查询操作，而不会干扰到最新的当前表上的查询操作

# 只读或则大部分情况下只读的表

有些表的数据用于编制类目或则分列清单（如工作岗位、竞拍、不动产等），这种应用场景是典型的 读多写少 的业务。如果不介意 MyISAM 的崩溃恢复问题，选择它是合适的。MyISAM 只将数据写到内存中，然后等待操作系统定义将数据刷出到磁盘上（如果没能刷出到磁盘，数据就丢了）

不要轻易相信 「MyISAM 比 InnoDB 快」之类的经验之谈，这个结论往往是不准确的。在很多我们已知的场景中，InnoDB 的速度都可以让 MyISAM 望尘莫及，尤其是使用到聚簇索引，或则需要访问的数据库都可以放入内存的应用。在本书后续章节，可以了解更多影响存储引擎性能的因素（如数据大小、I/O 请求量、主键还是二级索引等）以及这些因素对应用的影响

# 订单处理

支持事物是必要选项。另一个重点考虑是存储引擎对外键的支持情况。InnoDB 是订单处理类应用的最佳选择。

# 电子公告牌和主题讨论论坛

主题讨论区一般都有更新计数器，并且会为各个主题计算访问统计信息。多数应用只涉及了几张表来保存所有的数据，所以核心表的读写压力可能非常大。为保证这些核心表的数据一致性，锁成为资源争用的主要原因素。

尽管有这些涉及缺陷，但大多数应用在中低负载时可以工作得很好。如果站点规模扩张，浏览猛增，则数据库访问则可能变得非常慢。此时一个典型的解决方案是更改为支持更高读写的存储引擎，但是有时候会发现这样做了之后系统变得更慢了。

有可能没有意识到这是由于某些特殊查询的缘故，典型的如：

select count(*) from table;
1

问题就在于，不是所有存储引擎运行该语句都非常快。对于 MyISAM 确实会很快，其他的则不行。每种存储引擎都能找出类似对自己有利的列子。下一章将帮助用户分析这些状况，演示符合发现和解决存在的这类问题。

# CD-ROM 应用

如果要发布一个基于 CD-ROM 或 DVD-ROM 并且使用 MySQL 数据文件的应用，可以考虑使用 MyISAM 表或 MyISAM 压缩表，这样表之间可以隔离并且可以在不同介质上相互拷贝。压缩表节约空间，但是是只读的，在非只读的场景下就无法采用压缩表了

# 大数据量

什么样的数据量算大？我们管理过很多 InnoDB 数据库的数据量在 3~5TB 之间，或则更大，这是单台机器的量，不是一个分配（shard）的量。这些系统运行得还不错，要做到这一点需要合理的选择硬件、做好物理设计、并未服务器的 I/O 瓶颈做好规划。在这样的数据量上，如果采用 MyISAM，崩溃后的恢复就是一个噩梦

如果数据量持续增长到 10TB 以上的级别，可能就需要建立数据仓库。Infobright 是 MySQL 数据仓库最成功的解决方案。 也有一些大数据库不适合 Infobright ，却可能适合 TokuDB。

# 转换表的引擎

有多种方法，每种方法都有其有点和缺点，这里介绍三种：

# ALTER TABLE

下面将 mytable 的引擎为 InnoDB 的：

ALTER TABLE mytable ENGINE = InnoDB;
1

上述语法适用于任何存储引擎。问题就是：需要执行很长时间。

MySQL 会先将数据从原表复制到一张新表中，在复制期间可能会消耗系统所有的 I/O 能力，同时 原表上会加上读锁。一个替代方案是采用导出与导入的方法，手工进行表的复制

转换表的存储引擎将会失去和原引擎相关的所有特性。 例如，将 InnoDB 表转为 MyISAM，再将它转为 InnoDB，那么原来 InnoDB 表上所有的外键都没有了。

# 导出与导入

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

# 创建与查询（CREATE 和 SELECT）

这种方法同时实现了第一种方法的 高效 和第二种方法的 安全。先创建一个新的存储引擎表，然后利用 INSERT...SELECT 语法来导出数据：

CREATE TABLE innodb_table LIKE myisam_table;
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
1
2
3

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

START TRANSACTION;
INSERT INTO innodb_table SELECT * FROM myisam_table WHERE id BETWEEN x AND y;
COMMIT;
1
2
3

上述就是分批处理数据，如果有需要可以在执行过程中对原表加锁，确保新表和原表的数据一致。

Percona Toolkit 提供了一个 pt-online-schema-change 的工具（基于 Facebook 的在线 schema 变更技术），可以比较简单、方便的执行上述过程，避免手工操作可能导致的失误和烦琐。

← MySQL 时间线（Timeline） →