高性能 MySQL 一书笔记。

参考：

• 高性能 MySQL 第四版

MySQL架构

本章概述了 MySQL 服务端的架构，各种存储引擎之间的主要区别。

MySQL的逻辑架构

最上层是客户端。

第二层是大多数 MySQL 的核心功能，包括查询解析、分析、优化、内置函数、存储过程、触发器、视图等。

第三层是存储引擎。存储引擎负责数据的存储和提取。服务器通过存储引擎 API 进行通信。存储引擎不会去解析 SQL，而是简单地响应服务器的请求。

连接管理与安全性

默认情况下，每个客户端连接都会在服务器进程中拥有一个线程，该连接的查询只会在这个单独的线程中执行，服务器维护了一个缓存区，用于存放已就绪的线程，因此不需要为每个新的连接创建或销毁线程。

当客户端连接到服务器时，会对其进行身份验证，加密认证，权限认证。

优化与执行

MySQL 解析查询以创建内部数据结构（解析树），然后对其进行各种优化（包括重写查询、决定表的读取顺序，以及选择合适的索引等）。用户可以通过特殊关键字向优化器传递提示，影响决策过程。

优化器不关心表的存储引擎，但存储引擎对查询优化有影响。优化器会向存引擎咨询一些信息。

并发控制

只要有多个查询需要同时修改数据，就会产生并发控制问题。

我们需要一种并发访问数据的方法。

读写锁

处理并发读写访问，通常由一个锁系统实现。

• 共享锁/读锁：互相不阻塞
• 排他锁/写锁：阻塞读锁和写锁

这样才能确保在特定的时间点只有一个客户端能执行写入，并防止其他客户端读取正在写入的资源。

在实际的数据库系统中，每时每刻都在发生锁定。当某个客户端在修改数据时，MySQL 会通过锁定防止其他客户端读取同一数据。如果速度足够快，则不会引起客户端的感知。

锁的粒度

一种提高共享资源并发性的方式是让锁定对象更有选择性。让锁定的数据量最小化。

加锁需要消耗资源。锁的各种开销，都会增加系统的开销，也会影响性能。

锁定策略是锁开销和数据性之间的平衡，这种平衡会影响性能。大多数商业数据库系统没有提供太多的选择，一般都是在表中施加行级锁（row level lock）。

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

表锁（table lock），是 MySQL 中最基本也是开销最小的锁策略。它会锁定整张表。

行级锁（row lock），可以最大程度地支持并发处理。它会锁定表中的某一行。代价是需要承担更多开销，以跟踪这些行级锁的相关信息。

行级锁是存储引擎而不是服务器中实现的。

事务

事务就是一组 SQL 语句，作为一个工作单元以原子方式进行处理。也就是说，作为事务的一组语句，要么全部执行成功，要么全部执行失败。

银行金融是解释事务必要性的经典例子。

1
2
3
4
5

START TRANSACTION;
sql1;
sql2;
...
COMMIT;

除非系统通过严格的 ACID 测试，否则空谈事务的概念是不够的。一个确保数据安全的事务处理系统，必须满足这些密切相关的标准。

ACID 代表：

• 原子性（atomicity）：一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
• 一致性（consistency）：数据库总是从一个一致性状态转换到下一个一致性状态。如果事务最终没有被提交，该事务所做的任何修改都不会被保存到数据库中。
• 隔离性（isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
• 持久性（durability）：一旦提交，事务所做的修改就会被永久保存到数据库中。

ACID 事务和 InnoDB 引擎提供的保证是 MySQL 中最强大成熟的特性之一。虽然它们在吞吐量方便做了一定的权衡，但如果应用得当，就可以避免在应用层实现大量复杂逻辑。

隔离级别

ANSI SQL 标准定义了 4 种隔离级别。较低的隔离级别通常允许更高的并发性，并且开销也更低。

• READ UNCOMMITTED（未提交读）：在事务中可以查看其他事务中还没有提交的修改。读取未提交的数据，称为脏读（dirty read）。
• READ COMMITTED（提交读）：一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。
• REPEATABLE READ（可重复读）：解决了提交读的不可重复读问题，保证了在同一个事务中多次读取相同行数据的结果是一样的。但理论上无法解决幻读问题。它是 MySQL 默认的事务隔离级别。
• SERIALIZABLE（可串行化）：它是最高隔离级别，通过强制事务按序执行，使不同事务之间不可能产生冲突，从而解决前面的幻读问题。

死锁

死锁指两个或多个事务相互持有和请求相同资源上的锁，产生了循环依赖。

为了解决这个问题，数据库系统实现了各种死锁检测和锁超时机制。InnoDB 处理死锁的方式是将持有最少行级排他锁的事务回滚。

锁的行为和顺序是和存储引擎相关的。

一旦发生死锁，如果不回滚其中一个事务，就无法打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。

事务日志

事务日志有助于提高事务的效率。存储引擎只需要更改内存中的数据副本，而不用每次修改磁盘中的表，这非常快。然后再把更改的记录写入事务日志中，事务日志会被持久化保存在硬盘上。因为事务日志采用追加写操作，所以相对比较快。最后会有一个后台进程去更新硬盘中的表。

如果修改操作已经写入事务日志，那么即使系统在数据本身写入硬盘之前发生崩溃，存储引擎仍可在重新启动时恢复更改。

MySQL中的事务

存储引擎是驱动如何从硬盘中存储和检索数据的软件。下面的事务原语基于 InnoDB 引擎中的事务。

默认情况下，单个 INSERT, UPDATE 或 DELETE 语句会被隐式包装在一个事务中并在执行成功后立即提交，这称为 自动提交（AUTOCOMMIT）模式。

可以禁用此模式。

在同一个事务中，混合使用多种存储引擎是不可靠的。

最好不要在应用程序中混合使用存储引擎。失败的事务可能导致不一致的结果，因为某些部分可以回滚，某些部分部分不能回滚。

隐式锁定和显式锁定。

建议不要显示执行 LOCK TABLES。

多版本并发控制

MySQL 的大多数事务型存储引擎会将行级锁和多版本并发控制（MVCC）结合使用。包括 Oracle、PGSQL等。

可以认为 MVCC 是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。根据其实现方式，不仅实现了非阻塞的读操作，写操作也只锁定了必要的行。

MVCC 的原理是使用数据在某个时间点的快照来实现的。

复制

MySQL 被设计用于在任何给定时间只在一个节点上接受写操作。这在管理一致性方面具有优势，但在需要将数据写入多台服务器或多个地区时，会导致需要做出取舍。

MySQL 提供了复制（replication）将一个节点执行的写操作分发到其他节点。源节点为每个副本节点提供了一个线程，该线程作为复制客户端登录，当写入发生时会被唤醒，发送新数据。

一主多副的拓扑结构：

1
2
3

     源节点
  | 二进制日志 |
 副本节点1   副本节点2

生产环境中，都应该使用复制并至少有三个以上的副本。理想情况下应该分布在不同的地区用于灾难恢复计划。

数据文件结构

在 8.0 版本中，MySQL 将表的元数据重新设计为一种数据字典，包含在 .idb 文件中。这使得表结构上的信息支持事务和原子级数据定义更改。

在操作期间，不再仅仅依赖 information_schema 来检索表定义和元数据，而是引入字典对象缓存。这是基于最近最少使用（LRU）的内存缓存，包括分区定义、表定义、存储程序定义、字符集和排序信息。

服务器访问表的元数据的方式的变化减少了 IO，非常高效。特别是当前访问最活跃的那些表，在缓存中最常出现。每个表的 .idb 和 .frm 文件被替换为已经序列化的字典信息（.sdi）。

InnoDB引擎

InnoDB 是 MySQL 默认事务型存储引擎。它是为处理大量短期事务而设计的，这些事务通常是正常提交的，也很少会被回滚。

默认情况下，InnoDB 将数据存储在一系列的数据文件中，这些文件被统称为表空间（tablespace）。表空间本质上是一个由 InnoDB 管理的黑盒。

InnoDB 使用 MVCC 来实现高并发性，并实现了所有 4个 SQL 标准隔离级别（默认是 REPEATABLE READ），并通过间隙锁（next-key locking）策略来防止在这个隔离级别上幻读：InnoDB 不只锁定在查询中涉及的行，还会对索引结构中的间隙进行锁定，以防止幻行被插入。

InnoDB 表是基于聚簇索引构建的，InnoDB 的索引结构与其他引擎有很大不同。聚簇索引提供了非常快速的主键查找，但是，因为二级索引需要包含主键列，如果主键较大，则其他索引也会很大。如果表中的索引较多，主键应当尽量小。

InnoDB 内部做了很多优化。包括从磁盘预取数据的可预测性读、能够自动在内存中构建哈希索引以进行快速查找的自适应哈希索引，以及用于加速插入操作的插入缓冲区。

json文档支持

JSON 类型在 5.7 版本被首次引入 InnoDB，它实现了 JSON 文档的自动验证，并优化了存储以允许快速读取。

InnoDB 还引入了 SQL 函数来支持在 JSON 文档上的丰富操作。

数据字典的变化

8.0 的一个变化是删除了基于文件的表元数据存储，并将其转移到使用 InnoDB 表存储的数据字典中。这给所有类似修改表结构的操作带来了 InnoDB 的崩溃恢复事务的好处。以前依赖于表元数据文件的备份程序，现在必须查询新的数据字典以提取表定义。

原子DDL

8.0 引入了源自数据定义更改。这意味着数据定义语句要么全部成功完成，要么全部失败回滚。这是通过创建 DDL 特定的 Undo 日志和 Redo 日志来实现，InnoDB 依赖这两种日志来跟踪变更。

可靠性工程世界中的监控

有效管理 MySQL 的一个关键点在于对数据库的健康状况进行良好的监控。

Performance Schema

在高负载下调优数据库性能是一个迭代循环的过程。每次进行更改以调优数据库的性能时，都需要了解更改是否有什么影响。查询速度比以前快吗？锁是否会减慢应用程序的速度，或者是否已经完全消失了？内存使用情况改变了吗？等待磁盘的时间改变了吗？

Performance Schema 是一个存储回答上述问题所需数据的数据库。

Performance Schema介绍

Performance Schema 提供了有关 MySQL 服务器内部运行的操作上的底层指标。

• 程序插桩（instrument）：在 MySQL 代码中插入探测代码，以获取想要的信息。
• 消费者表（consumer）：存储关于程序插桩代码信息的表。

Performance Schema 收集的数据保存在内存中。可设置消费者表的最大大小来限制其使用的内存量。

Performance Schema配置

• 启用或禁用 Performance Schema
• 启用或禁用插桩
• 启用或禁用消费者表
• 优化特定对象的监控
• 优化线程的监控
• 调整它的内存大小

操作系统和硬件优化

操作系统和硬件往往是 MySQL 服务器性能的限制因素。

平衡内存和磁盘资源

配置大内存的主要原因是为了避免磁盘 IO，访问磁盘比访问内存慢得多。重要的是平衡内存和磁盘大小、速度、成本和其他因素，以便让工作负载获得良好的性能。

缓存和读写

如果内存足够，可以完全避开磁盘读取操作。如果数据都能装入内存，那么一旦服务器的缓存预热完成，每次读取都将是一次缓存命中。此情况下，会从内存中读取数据。

写入则是另一回事。写入可以像读取一样在内存中执行，但迟早必须写入磁盘，才能永久保留数据。换句话说，缓存可以延迟写操作。

缓存允许方式组合：

• 多次写操作，一次刷新：一个数据片段可以在内存中被多次修改，而无需每一次都将新值写入磁盘。
• IO 合并：许多不同的数据片段可以在内存中被修改，这些修改可被收集在一起，因此物理写可以作为单个磁盘操作执行。

这也是许多事务系统使用提前写日志（write-ahead logging）策略的原因。提前写允许在内存中更改页面，而不用将更改刷新到磁盘。这通常涉及随机 IO，速度非常慢。相反，它们将更改的记录写入顺序日志文件，这样要快得多。后台线程可以稍后将修改过的页面刷新到磁盘，这样优化了写入操作的性能。

你的工作集是什么

每个应用程序都有一个数据工作集，即它真正需要的数据。许多数据库也有大量不在工作集中的数据。并不需要把整个数据库放入内存来获得最佳性能，仅仅将工作集放入内存即可。

固态存储

SSD 性能比 HDD 快很多。

RAID性能优化

选择文件系统

在 Linux 下，最好使用 ext4, xfs 和 zfs。通常建议使用 xfs。

选择磁盘队列调度器

在 Linux 上，队列调度器决定了对块设备的请求实际发送到底层设备的顺序。默认是完全公平排队（CFQ, Complete Fair Queuing）。在 MySQL 的工作负载下，CFQ 会导致非常糟糕的响应时间，因为会不必要地阻塞队列中的一些请求。

1

cat /sys/block/sda/queue/scheduler

内存和交换

外部内存分配器（如 tcmalloc 或 jemalloc）来替换内置的内存分配器（glibc），大量基准测试表明，与 glibc 相比，它们能提高性能并减少内存分片。

关闭交换。

操作系统状态

使用 vmstat 和 iostat 和 sar 命令查看系统状态。

优化服务器设置

MySQL 有许多可以更改但不应该更改的设置。通常更好的做法是正确地配置基本设置，并将更多的时间花在 schema 优化、索引和查询设计上。

MySQL 的默认设置是有充分理由的，在不了解其影响的情况下进行更改可能会导致崩溃、卡顿或性能下降。

首先应该确保 InnoDB 缓冲池和日志文件大小等基本设置是合适的。

如果不知道，使用默认配置是安全的。

MySQL的配置是如何工作的

MySQL 从命令行参数和配置文件（/etc/my.cnf 或 /etc/mysql/my.cnf）中获取配置信息。服务器实际上会读取配置文件中的内容，删除其中的注释行和换行符，然后将其与命令行选项一起处理。

需要永久使用的任何设置都以你改改写入全局配置文件，而不是命令行中指定。

1
2
3
4

# 查看会读取哪些文件
mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

配置文件采用标准的 INI 格式。

语法、作用域和动态性

配置设置全部用小写字母。

配置设置有多个作用域。有些设置是全局的，有些是基于每个对象的。

很多变量可以在服务器运行时进行更改，MySQL 将这些称为动态配置变量。动态设置的变量在 MySQL 重启后失效。

1
2
3

SET sort_buffer_size = <value>;
SET GLOBAL sort_buffer_size = <value>;
SET @@sort_buffer_size := <value>;

查看全局变量：

1

SHOW GLOBAL VARIABLES;

持久化系统变量

MySQL 8.0 引入了一个名为持久化系统变量的功能。新语法 SET PERSIST 允许在运行时设置一次值，MySQL 把这个设置写入磁盘。

设置变量的副作用

动态设置变量可能会产生意想不到的副作用（如引起缓冲区刷新脏块）。在线更改设置时要小心，可能会导致服务器执行大量工作。

规划变量的更改

设置变量时要小心，并不是越多越好，如果将值设置的太高，则很容易导致问题。

MySQL配置文件

最小化配置文件

一个 MySQL 8.0 示例的最小化配置文件：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid

user = mysql
port = 3306
# INNODB
innodb_buffer_pool_size = <value>
innodb_log_file_size = <value>
inondb_file_per_table = 1
innodb_flush_method = O_DIRECT
# LOGGING
log_error = /var/lib/mysql/mysql-error.log
log_slow_queries = /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = <value>
thread_cache_size = <value>
table_open_cache = <value>
open_files_limit = 65535

[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

请注意 linxu 系统配置 open_files_limit 选项，应该尽可能大。否则可能会出现错误 too many open files。

检查MySQL服务器的状态变量

可以使用 SHOW GLOBAL STATUS 的的输出作为配置的输入，以更好地为工作负载定制设置。

配置内存使用

使用 innodb_dedicated_server 通常会占用 50%-75% 的内存，这样至少有 25% 的内存可用于每个连接的内存分配，操作系统开销和其他内存设置。

每个连接的内存需求

MySQL 只需要少量的内存就能保持一个连接（通常是一个相关的专用线程）打开。它还需要基本内存量来执行任何给定的查询。你需要为 MySQL 留出足够的内存，以便在负载高峰期执行查询。

为操作系统保留内存

需要为操作系统保留足够的内存已完成其工作。

InnoDB缓冲池

InnoDB 缓冲池需要的内存比其他任何组件都多。就性能而言，InnoDB 缓冲池大小通常是最重要的变量。它不仅缓存索引，还缓存行数据、自适应哈希索引、更改缓冲区、锁和其他内部架构等。它还是实现延迟写操作。简而言之，InnoDB 严重依赖缓冲池，应该确保为其分配足够的内存。可以使用 SHOW 或 innotop 等工具连监控缓冲池的内存使用。

线程缓存

线程缓存保存了与当前没有与连接关联但已准备好为新连接提供服务的线程。

配置MySQL的IO行为

一些配置项会影响 MySQL 将数据同步到磁盘和执行恢复的方式。这涉及 IO 操作，会极大地影响性能。

为了正常使用，需要更改的几个重要参数包括：InnoDB 日志文件大小、InnoDB 如何刷新其日志缓冲区，以及 InnoDB 如何执行 IO。

InnoDB事务日志

InnoDB 使用日志来降低提交事务的成本。它不会将每个事务提交时将缓冲刷新到磁盘，而是将事务记录到日志中。

使用日志，InnoDB 可以将随机磁盘 IO 转换为顺序 IO。一旦日志被安全地保存在磁盘中，即使更改的数据尚未写入数据文件，事务仍将是持久的。如果发生故障（如停电），InnoDB 可以重放日志并恢复已提交的事务。

当然，InnoDB 最终必须将更改的数据写入数据文件，因为日志的大小是固定的，采取的是循环写入的方式：当到达日志的末尾时，它会环绕到日志的开头。如果日志记录中包含的更改尚未应用于数据文件，则无法覆盖日志记录，因为这将删除已提交事务的唯一永久记录。

InnoDB 使用后台线程智能地刷新对数据文件的更改。该线程可以将写入分组，并使数据写入顺序话，以提高效率。

日志文件的大小由 innodb_log_file_size 和 innodb_log_files_in_group 控制，这对写入性能非常重要。使用 innodb_dedicated_server，日志文件的大小将根据系统内存量来自动管理。

日志缓冲区

InnoDB 修改数据时会将修改记录写入日志缓冲区，并将其保存在内存中。当缓冲区满了、事务提交时，或每秒1次（此三个条件以先满足为准），InnoDB 会将缓冲区刷新到磁盘上的日志文件中。

控制缓冲区大小的是 innodb_log_buffer_size。

InnoDB如何刷新日志缓冲区

当 InnoDB 将日志缓冲区刷新到磁盘的日志文件时，会使用互斥锁锁定缓冲区，将其刷新到所需的位置，然后将剩余的条目移动到缓冲区的前面。当释放互斥锁时，可能会有多个事务准备刷新其日志条目。InnoDB 使用了一个组提交特性，可以在单次 IO 操作中将一组日志全部提交。

InnoDB如何打开和刷新日志文件和数据文件

innodb_flush_method 选项会影响读取和写入数据的方式。

改变 InnoDB 执行 IO 操作的方式会极大地影响性能，所以在改变任何东西之前，一定要理解你在做什么！

InnoDB表空间

InnoDB 将数据保存在表空间中，表空间本质上是一个虚拟文件系统，有磁盘上的一个或多个文件组成。InnoDB 将表空间用于多种用途，而不仅仅是存储表和索引，还包括了 Undo 日志（重建旧行版本所需的信息）、修改缓冲区、双写缓冲区和其他内部结构。

可以使用 innodb_data_file_path 配置项指定表空间文件。这些文件都包含在 innodb_data_home_dir 目录下。

InnoDB 对表空间时完全不宽容的：不能简单地删除文件或改变其大小。如果损坏了表空间，InnoDB 将无法启动。同样，InnoDB 对日志文件也非常严格。

innodb_file_per_table 选项允许你将 InnoDB 配置为每个表使用单独的文件。它将数据存储在数据库目录下的 tablename.idb 文件中。这使得删除表时更容易回收空间。然而，将数据放在多个文件中实际上会导致更多的空间浪费。因为跟 InnoDB 单个表空间中的内部碎片相比，每个 .idb 文件中都会有一些浪费的空间。

per_table 提供了额外的可管理性和可视性。例如，通过检查单个文件来查找表的大小比使用 SHOW TABLES STATUS（必须执行更复杂的工作来确定为一个表分配了空间） 快很多。

per_table 也有不好的一面，会使 DROP TABLE 性能变差。严重时可能导致服务器范围内明显的停顿。

建议使用 per_table 并限制共享表空间的大小。

行的旧版本和表空间

InnoDB 的表空间在写操作频繁的环境中可能会变得非常大。如果事务长时间保持打开状态（即使没有做任何工作），并且使用默认的可重复读取事务隔离级别，InnoDB 将无法删除行的旧版本，因为未提交的事务仍需要能够看到它们。

InnoDB 将旧版本存储在表空间中，因此随着更多数据的更新，它将继续增长。

其它IO配置选项

sync_binlog 选项控制 MySQL 如何将二进制日志刷新到磁盘，默认值是 1，意味着 MySQL 将执行刷新并保存二进制日志的持久性和安全性。强烈建议将其设置为 1，不建议设置为其他值。

配置MySQL并发

当遇到 InnoDB 并发问题，并且版本低于 5.7，解决方法通常是升级服务器。如果升级到较新版本的 MySQL，在大多数情况下不需要限制并发性。

如果你发现自己遇到了并发性瓶颈，最好的选择是对数据进行分片。如果分片不可行，那么可能需要限制并发性。InnoDB 有自己的线程调度器，它控制线程如何进入内核访问数据，以及进入内核后可以做什么。限制并发性最基本的方法是使用 innodb_concurrency 变量，该变量限制了内核中同时可以有多少线程。

建议将并发设置为 CPU 核数，然后根据需要调整大小。

安全设置

一些服务器行为的有用选型：

• max_connect_errors
• max_connections
• skip_name_resolve
• sql_mode
• sysdate_is_now
• read_only 和 super_read_only：强烈建议将副本设置为只读模式。

高级InnoDB设置

一些 InnoDB 选项对服务器性能非常重要：

• innodb_autoinc_lock_mode：控制如何生成自动递增的主键值
• innodb_buffer_pool_instances：将缓冲池划分为多个段
• innodb_io_capacity
• innodb_read_io_threads 和 innodb_write_io_threads：控制有多少后台线程可用于 IO 操作
• innodb_strict_mode：设置咋某些情况下抛出错误而不是警告
• innodb_old_blocks_time

schema设计与管理

良好的逻辑设计和物理设计是高性能的基石，应该根据系统将要运行的特定查询设计 schema。

Schema（架构或模式），在数据库系统中是形式语言描述的一种结构，是对象的集合。可包含的对象如：表、字段、关系模型、试图、索引、报、存储过程、子程序、队列、触发器、数据类型、序列等。

MySQL 中的 Schema 等价于数据库，CREATE SCHEMA 等价于 CREATE DATABASE 。

选择优化的数据类型

选择正确的数据类型对于获得高性能至关重要。有几个原则：

• 更小的通常更好
• 简单为好
• 尽量避免存储 NULL（缺失值），也许可以用 0，特殊值或空字符串作为代替。

很多 MySQL 数据类型可以存储相同类型的数据，但在存储的值范围、表示的精度或需要的物理空间（内存和磁盘）上存在差异。

整数类型

有两种类型的数字：整数（whole number）和实数（real number）。

整数的值范围从 $-2^{n-1}$ 到 $2^{n-1}-1$，注意有符号和无符号（UNSIGNED）类型的范围。它们使用相同的存储空间，具有相同的性能。

• TINYINT：8 位存储空间
• SMALLINT：16 位
• MEDIAINT：24 位
• INT：32 位
• BIGINT： 64 位

你的选择决定了 MySQL 在内存和磁盘中保存数据的方式。然而，整数计算通常使用 BIGINT 整数。

MySQL 可以为整数类型指定宽度（如 INT(11)），但它不会限制值的合法范围，只是规定了一些交互工具用来显示字符的个数。对于存储和计算，INT(1) 和 INT(20) 是相同的。

实数类型

实数是带有小数部分的数字。它也可以使用 DECIMAL 存储比 BIGINT 还大的整数。MySQL 即支持精确类型，也支持不精确类型。

有两种方式可以指定浮点列所需的精度。这些精度说明符是非标准的，建议只指定数据类型，不指定精度。

MySQL 会使用 DOUBLE 进行浮点类型的内部计算。

• FLOAT：4 字节存储空间
• DOUBLE：8 字节。

由于额外的空间需求和计算成本，应该尽量只对小数进行精确计算时才使用 DECIMAL。在一些大容量的场景，可以考虑使用 BIGINT 代替 DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。

字符串类型

每个字符串列可以有自己的字符集和该字符集的排序规则集。

VARCHAR 用于存储可变长度的字符串。它比固定长度的类型更节省空间，因为它仅使用必要的空间。它需要额外使用 1 或 2 字节记录字符串的长度。

以下情况使用 VARCHAR 更合适：字符串列的最大长度远大于平均长度；列的更新很少，所以随便不是问题；使用了像 UTF-8 这样复杂的字符集，每个字符集都使用了不同的字节数进行存储。

InnoDB 可以将过长的 VARCHAR 只存储为 BLOB。

CHAR 是固定长度的。MySQL 总是为定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL 删除所有尾随空格。如果需要进行比较，值会用空格填充。

CHAR 适合存储非常短的字符串，或者所有值的长度都几乎相同的情况。例如，用户密码的 MD5 值。

对于经常修改的值，CHAR 也更好。因为固定长度的行不容易出现碎片。

BLOB 和 TEXT 都是为了存储很大的数据而设计， BLOB 类型存储二进制数据，没有排序规则或字符集，TEXT 类型存储字符数据，有字符集和排序规则。

BLOG：TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB。

TEST: TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT。

MySQL 不能将 BLOB 和 TEXT 数据类型的完整字符串放入索引，也不能使用索引进行排序。

在过去，某些应用程序接受上传的图片，并将其作为 BLOB 数据存储在 MySQL 中。如果可以避免的话，不要在数据库中存储图像这样的数据。相反，应该将它们写入单独的对象存储，并使用表来跟踪图像的位置和文件名。

有时可以使用 ENUM（枚举） 代替常规的字符串类型。它可以存储一组预定义的不同字符串的值。

MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到 1 或 2 字节中。在内部会将每个值在列表中的位置保存为整数。

尽量避免使用数字作为枚举常量，这种双重属性很容易导致混乱。

日期和时间类型

MySQL 可以存储的最小时间粒度是微秒。

DATETIME，可保存大范围的数值，从 1000 年到 9999 年，精度为 1 微秒。以 YYYYMMDDHHMMSS 格式存储压缩为整数的日期和时间，且与时区无关。需要 8 字节的存储空间。

TIMESTAMP，与 UNIX 时间戳相同。使用 4 字节的存储空间。时间戳依赖于时区。

MySQL 提供了 FROM_UNIXTIME() 函数来将 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数将日期转换为时间戳。

位压缩数据类型

MySQL 有几种使用值中的单个位来紧凑地存储数据的类型。所有这些位压缩类型，不管底层存储和处理方式如何，从技术上来说都是字符串类型。

BIT，可存储一个或多个 true/false 值。BIT 列的最大长度是 64 位。InnoDB 将其存储为足够容纳这些位的最小整数类型，所以它不会节省任何存储空间。建议最好避免使用这种类型。

SET，如果需要存储多个 true/false 值，可考虑使用它。它将多列组合成一列，这在 MySQL 内部是以一组打包的位的集合来表示的。这样可以更有效地利用存储空间。

JSON数据类型

JSON 数据类型将使用更多空间来存储预定义 JSON 的额外字符以及空格。

选择标识符

一般来说，标识符是引用行及通常使其唯一的方式。标识符可能与其他值进行比较，也可能作为在其他表中作为外键。因此标识符选择数据类型应该与联接表中的对应列保持一致。

标识符，不仅要考虑存储类型，也要考虑 MySQL 如何对该类型执行计算和比较。

特殊数据类型

某些类型的数据并不直接对应于可用的内置类型。如 IPv4，应该将其存储为 32 位无符号整数，而不是字符串。MySQL 提供了 INET_ATON() 和 INET_NTOA() 函数来转换。

使用的空间从 VARCHAR(15) 的 16 字节压缩到无符号 32 位整数的 4 字节。

如果你担心数据库的可读性，不想继续使用函数查看看行数据，请记住 MySQL 有视图，可用试图来简化数据查看的复杂性。

schema设计中的陷阱

一些 schema 设计中的常见问题：

• 太多的列
• 太多的联接
• 全能的枚举
• 变相的枚举
• NULL 不是虚拟值，但也不要害怕使用 NULL。

创建高性能的索引

索引，在 MySQL 中也叫做键（key），是存储引擎用于快速找到记录的一种数据结构。

当表的数据量越来越大时，索引对性能的至关重要。

索引优化应该是对查询性能优化最有效的手段了。

基础索引

要理解索引，最简单的方法就是查看一本书的索引，找到对应的页码。

1

SELECT first_name FROM db.table WHERE actor_id = 5;

如果在 actor_id 列上建了索引，则 MySQL 将使用该索引找到 actor_id 为 5 的记录。也就是说，MySQL 先在索引上按值进行查找，然后返回所有包含该值的记录。

索引可以包含一列或多列的值。如果索引包含多列，那么列的顺序也很重要，因为 MySQL 只能有效地使用索引最左前缀列。

索引的类型

索引有多种类型，可为不同的场景提供更好的性能。在 MySQL 中，索引是在存储引擎层而不是服务器层实现的，不同的存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

下面重点介绍 InnoDB 存储引擎的索引实现。

B-tree索引

B-tree 索引，它使用 B-tree 数据结构来存储数据。

B-tree 通常意味着所有的值都是按顺序存储的，并且每一个叶子页到跟的距离相同。

B-tree 索引能够加快数据访问的速度，有了索引，在查询某些有条件的数据时，存储引擎不再需要进行全表扫描。而是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，也么该记录不存在。

叶子节点比较特殊，它们的指针指向的是被索引的数据，而不是其他的节点页。

B-tree 是按照索引列中的数据大小顺序存储的，所以很适合按照范围来查询。

假设有如下数据表：

1
2
3
4
5
6
7

# 对于表中的每一行数据，索引中都包含对应的 last_name, first_name 和 bob 列的值。
CREATE TABLE People (
   last_name  varchar(50) not null,
   first_name varchar(50) not null,
   dob        date not null,
   key(last_name, first_name, dob)
);

下图显示了该索引是如何组织数据存储的：

请注意，索引对多个值进行排序的依据是中定义索引时列的顺序。看下最后两个条目，两人的姓名一样，则根据日期来排列。

InnoDB 引擎有一个被称为自适应哈希索引的特性。当 InnnoDB 发现某些索引值被非常频繁地被访问时，它会在原有的 B-tree 索引之上，在内存中再构建一个哈希索引。这就让 B-tree 索引也具备了一些哈希索引的优势（如可以实现非常快速的哈希查找）。这个过程是完全自动化的，不过可以通过参数关闭自适应哈希索引这个特性。

可以使用 B-tree 索引的查询类型。B-tree 索引适用于全键值、键值范围或键前缀查找（只适用于根据最左前缀的查找）。前面所述索引，对以下类型的查询有效：

• 全值匹配：指和索引中所有列匹配，如查找姓名为 Cuba Allen、日期为 1960-01-01 的人。
• 匹配最左前缀：如查找所有姓为 Allen 的人，即只用索引的第一列。
• 匹配列前缀：只匹配某一列的值的开头部分。如查找所有姓以 J 开头的人。
• 匹配范围值：如查找姓在 Allen 和 Barry 之间的人。
• 精确匹配某一列而范围匹配另外一列：如查找姓为 Allen，而名以 K 开头的人。
• 只访问索引的查询：即查询只需要访问索引，而无需访问数据行。

因为索引树中的节点是有序的，所以除了按值查找，索引还可以用于查询中的 ORDER BY 操作。如果排序满足前面列出的几种查询类型，则这个索引也可以用于这类排序场景。

下面是一些 B-tree 索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。（如，上面的索引无法查找名为 Bill 的人，也无法查找特定日期的人。）
• 不能跳过索引中的列。（如，前面的索引无法查找姓为 Smith 并且在某个日期的人，如果不指定名 first_name，则 MySQL 只能使用索引的第一列姓 last_name。）
• 如果查询中有某列的范围查询，则其右边所有列都无法使用索引优化查找。（如， WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob='1976-12-23'，这个查询只能使用索引的前两列。如果范围查询列值的数量有限，可以通过使用多个不等于条件来代替范围条件。）

现在可以看到 索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同的查询需求。

全文索引

FULLTEXT 是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。它更类似与搜索引擎做的事情，而不是简单的 WHERE 条件匹配。

在相同的列上同时创建全文索引和基于值的 B-tree 索引并不会有冲突，全文索引适用于 MATCH AGAINST 操作，而不是普通的 WHERE 条件操作。

使用索引的优点

索引可以让服务器快速定位到表的指定位置。索引也有一些其他的附加作用。

最常见的 B-tree 索引，按照顺序存储数据，所以 MySQL 可以用来做 ORDER BY 和 GROUP BY 操作。因为数据是有序的，所以它也就会将相关的列值都存储在一起。最后，索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

总结索引有以下三个优点：

• 索引大大减少了服务器需要扫描的数据量。
• 索引可以帮助服务器避免排序和临时表。
• 索引可以将随机 IO 变为顺序 IO。

高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。

前缀索引和索引的选择性

有时候为了提升索引的性能，同时也节省索引的空间，可以只对字段的前一部分字符进行索引。这样的缺点是，会降低索引的选择性。

索引的选择性是指，不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

一般情况下，列前缀的选择性也是足够高的，足以满足查询性能。对于 BLOB、TEXT 或很长的 VARCHAR 类型的列，必须使用前缀索引，因为 MySQL 并不支持对这些列的完整内容进行索引。

这里的关键点在于，既要选择足够长的前缀以保证比较高的选择性，同时也不能太长（以便节约空间）。

为了确定前缀的合适长度，需要找到最常见的值的列表。

书中示例中，找到了合适的前缀长度，创建前缀索引：

1

ALTER TABLE sakila.city_demo ADD KEY (city(7));

前缀索引能使索引更小、更快，但 MySQL 无法使用前缀索引做 OERGER BY 和 GROUP BY 操作，也无法使用前缀索引做覆盖扫描。

多列索引

很多人对多列索引的理解都不够。一个常见的错误是，为每列创建独立的索引，或者按照错误的顺序创建多列索引。

第一个问题：为每列创建独立的索引。

1
2
3
4
5
6
7
8

CREATE TABLE t (
   c1 INT,
   c2 INT,
   c3 INT,
   KEY(c1),
   KEY(c2),
   KEY(c3)
)

有时如果无法设计一个三星索引，那么不如忽略掉 WHERE 子句，集中精力优化索引列的顺序，或创建一个全覆盖的索引。

在多列上独立地创建多个单列索引，在大部分情况下，并不能提高 MySQL 的查询性能。MySQL 引入了一种叫 索引合并（index merge）的策略，它在一定程度上可以使用表中的多个单列索引来定位指定的行。在这种情况下，查询能够同时使用两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR 条件的联合（union），AND 条件的相交（intersection），组合前两种情况的联合即相交。

下面的查询就是用了两个索引扫描的联合，通过 EXPLAIN 可以看到：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor WHER actor_id = 1 OR film_id = 1

#### 解释 ####
id: 1
select type: SIMPLE
table: film actor
partitions: NULL
type: index_merge
possible_keys: PRIMARY,idx_fk film_id
key: PRIMARY,idx_fk film_id
key_len: 2,2
ref: NULL
rows: 29
filtered: 100.00
Extra: Using union(PRIMARY,idx_fk film_id); Using where

索引合并策略有时候效果非常不错，但更多时候，它说明了表中的索引建的很糟糕：

• 当优化器需要对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当优化器需要对多个索引做联合操作时，通常需要在算法的缓存、排序和合并操作上耗费大量 CPU 和 MEM 资源。尤其是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
• 优化器不会把这些操作计算到查询成本中，优化器只关心随机页面读取。这会使得查询的成本被低估，导致该执行计划还不如直接进行全表扫描。这样做不但会消耗更多的资源，还可能会影响并发的查询，但如果单独运行这样的查询则往往会忽略对并发性的影响。

如果在 EXPLAIN 中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结构，看是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭索引合并功能，还可以使用 IGNORE INDEX 语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划。

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询语句，同时，还需要考虑如何更好地满足排序和分组操作的需要。

在一个多列的 B-tree 索引中，索引列的顺序意味着索引首先按照最左列进行排序，依次往右。所以索引可以按照升序或降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY 和 DISTINCT 等子句的查询需求。

所以，多列索引的列顺序至关重要。

索引的顺序有一个重要的法则：将选择性最高的列放到最前列。在很多场景中可能有帮助，但要考虑如何避免大量随机 IO 和排序可能更重要。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这是索引的作用只是优化查询语句中的 WHERE 条件。然而，性能不只依赖于所有索引列的选择，也和查询的具体值相关，也就是和值的分布有关。

1
2
3
4
5
6
7
8
9

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;

# 应该以那个字段列在前面呢？可以通过查询来确定表中值的分布情况，并确定哪列的选择性更高。
SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment;
SUM(staff_id = 2): 7993
SUM(customer_id = 584): 30

# 应该将列 customer_id 放在前面，因为它对应条件的数量更小。
ALERT TABLE payment ADD KEY(customer_id, staff_id);

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-tree 索引页和数据行。

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页（leaf page）中。聚簇表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

聚集的数据有一些优点：

• 可以把相互关联的数据保存在一起。
• 数据访问更快。
• 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

• 聚簇数据最大限度地提高 IO 密集型应用的性能，但如果全部数据都放在内存中，则访问的顺序就没那么重要了。
• 插入速度严重依赖于插入顺序。
• 更新聚簇索引列的代价很高。
• 基于聚簇索引的表在插入新行，或主键被更新导致需要移动行的时候，可能面临页分裂的问题。
• 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏。
• 二级索引可能比想象中更大。
• 二级索引访问需要两次索引查找，而不是一次。

覆盖索引

设计优秀的索引应该考虑到整个查询，而不单是 WHERE 条件部分。如果一个索引包含（或覆盖）所有需要查询的字段的值，就称之为覆盖索引。只有 B-tree 索引可以用于覆盖索引。

覆盖索引能够极大地提高性能。试想一下，如果查询只需扫描索引而无须回表，会带来多少好处：

• 索引条目通常远小于数据行大小，所以只需读取索引，就会极大减少数据访问量。
• 因为索引是按照列值的顺序存储的，所以对于 IO 密集型的范围查询会比随机从磁盘读取每一行数据的 IO 要少得多。
• 由于 InnoDB 的聚簇索引的特点，覆盖索引对 InnoDB 特别有用。

在所有场景中，在索引中满足查询的成本一般比查询记录本身要小得多。

当执行一个被索引覆盖的查询时，在 EXPLAIN 的 Extra 列可看到 Using index 的信息。

使用索引扫描来做排序

MySQL 有两种方式生成有序的结果：排序操作，或按索引顺序扫描（在 EXPLAIN 的输出结果中，type 列的值为 index）。

扫描索引本身很快，只需从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录都回表查询一次对应的记录。这基本都是随机 IO，按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其在 IO 密集型的应用负载上。

MySQL 可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两项任务，这样是最好的。

只有当索引的顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能使用索引来对结果做排序。如果查询需要连接多张表，则只有当 ORDER BY 子句引用的字段全部在第一个表中时，才能使用索引做排序。

使用索引做排序的另一个重要的场景是，查询语句中同时有 ORDER BY 和 LIMIT 子句的情况。

冗余和重复索引

MySQL 允许在相同列上创建多个相同的索引，虽然会抛出一个警告，但并不会阻止你。MySQL 需要单独维护重复的索引，优化器在优化查询的时候需要逐个地进行评估，这会影响性能，同时也浪费磁盘空间。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免创建这样的重复索引，发现以后应该立即移除。

有时，还会在不经意间创建重复索引，如：

1
2
3
4
5
6
7

CREATE TABLE test (
   ID INT NOT NULL PRIMARY KEY,
   A INT NOT NULL,
   B INT NOT NULL,
   UNIQUE(ID),
   INDEX(ID)
) ENGINE=InnoDB;

MySQL 的限制和主键都是通过索引来实现的，上面的写法在相同的列上创建了三个重复的索引。

冗余索引和重复索引有一些不同。如果创建了索引 (A, B)，再创建索引 (A) 就是冗余索引，因为这只是前一个索引的前缀。因此，索引 (A, B) 也可以当作索引 (A) 来使用。如果再创建索引 (B, A)，则不是冗余索引，索引 (B) 也不是。因为 B 不是索引 (A, B) 的最左前缀列。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引。但有时出于性能方面的考虑也需要冗余索引，因为扩展已有索引会导致其变得太大，从而影响其他使用该索引的查询的性。

解决冗余索引索引和重复索引的方法很简单，删除这些索引就可以了，但首先要做的是找出这样的索引。可以使用 Percona 工具箱的 pt-duplicate-key-checker 来分析表结构来超出冗余和重复索引。

在删除和扩展索引的时候要非常小心。建议使用 percona 工具箱的 pt-upgrade 工具来仔细检查计划中的索引变更。

可以考虑使用 MySQL 8.0 的不可见索引特性，而不是直接删除索引。

未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远用不到的索引。这样的索引完全是累赘，建议删除。

使用系统数据库 performance_schema 和 sys 找到未使用的索引。

1
2
3
4

SELECT * FROM sys.schema_unused_indexes;

# 删除索引
ALTER TABLE 表名 DROP KEY 索引名;

维护索引和表

维护表有三个主要目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

找到并修复损坏的表

可尝试运行 CHECK TABLE 来检查是否发生了表损坏，它能找出大多数的表和索引的错误。

可使用 REPAIR TABLE 命令来修复损坏的表。如果存储引擎不支持，可以通过一个不做任何操作的 ALTER 操作来重建表。

1
2

# 修改表的存储引擎
ALTER TABLE <table> ENGINE=INNODB;

此外，还可以将数据导出再导入。

如果 InnoDB 存储引擎的表发生了损坏，那一定是发生了严重的错误，它一般不会出现损坏。

更新索引统计信息

可通过 ANALYZE TABLE 来生成统计信息。

可以使用 SHOW INDEX FROM 命令来查看索引的基数（Cardinality）。索引列的基数显示了存储引擎估算索引列有多少个不同的取值。

InnoDB 会在表首次打开，或执行 ANALYZE TABLE，或者表的大小发生非常大的变化时计算索引的统计信息。

InnoDB 在打开某些 INFOMATION_SCHEMA 表，或使用 SHOW TABLES STATUS 和 SHOW INDEX，或客户端开启自动补全功能时，会触发索引统计喜喜的更新。如果有大量的数据表，这可能会带来严重的性能问题。只要使用 SHOW INDEX 查看索引统计信息，就一定会出发统计信息的更新。

减少索引和数据的碎片

B-tree 索引可能会产生碎片化，这会降低查询的效率。碎片化的索引可能会以很差或无序的方式存储在磁盘上。

表的数据存储也可能发生碎片化，有三种类型的数据碎片：

• 行碎片
• 行间碎片
• 剩余空间碎片

可执行 OPTIMIZE TABLE 或导出再导入来重新整理数据。对于不支持的引擎，可修改表的存储引擎。

查询性能优化

设计最优的库表结构、建立最好的索引。但还不够——还需要合理地设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

查询优化、索引优化、库表结构优化需要齐头并进。

本章的目标是帮助大家更深刻地理解 MySQL 如何真正地执行查询，并明白高效和低效的原因何在，这样才能充分发挥 MySQL 的优势，并避开他的弱点。

优化通常需要三管齐下：不做、少做、快速地做。

为什么查询速度会慢

查询真正重要的是响应时间。如果把查询看作一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。

通常来说，查询的生命周期大致可按如下顺序来看：

• 从客户端到服务器
• 服务器上进行语法解析
• 生成执行计划
• 执行：包括检索数据和数据处理等。
• 给客户端返回结果

花费的时间：

• 网络
• CPU 计算
• 生成统计信息和执行计划
• 锁等待（互斥等待）等

尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作、CPU 操作和内存不足时导致的 IO 操作上消耗的时间。根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用。

在每一个消耗大量时间的查询中，都有一些不必要的操作、某些操作被额外地重复了很多次、某些操作执行得太慢等。优化查询就是减少和消除这些操作所花费的时间。

慢查询的基础：优化数据访问

一条查询，如果性能很差，最常见的原因是访问的数据太多。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。

对于低效的查询，下面两个步骤来分析总是很有效：

• 确认应用程序是否在检索大量且不必要的数据。
• 确认 MySQL 是否在分析大量不需要的数据行。

是否向数据库请求了不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃，这增加了服务器额外的负担，消耗额外的资源。

以下是一些典型案例：

• 查询了不需要的记录

一个常见的错误是，常常会误以为 MySQL 只会返回需要的数据，实际上 MySQL 却是先返回全部结果集再进行计算。某些开发者先使用 SELECT 语句查询大量的结果，然后获取前面的 N 行后关闭结果集（例如取 100 条记录，但只在页面上展示前 10 条）。他们认为 MySQL 会执行查询，并只返回他们需要的 10 条数据，然后停止查询。实际情况是，MySQL 会查询出全部的结果集，客户端程序会接受全部的结果集数据，然后抛弃其中大部分数据。最简单有效的解决方法是加上 LIMIT 子句。

• 多表联接时返回全部列

如果你想查询所有在电影 ABC 中出现的演员，千万不要按下面的写法编写查询：

1
2
3
4

SELECT * FROM sakila.actor
INNER JOIN sakila.film_actor USING(actor_id)
INNER JOIN sakila.film USING(fild_id)
WHERE sakila.film.title = 'ABC';

上面的查询将返回这三个表的全部数据列。正确的方式是只取需要的列：

1

SELECT sakila.actor.* FROM sakila.actor ...;

• 总是取出全部列

每次看到 SELECT * 的时候都需要用怀疑的目光审视，是不是真的需要返回全部的列，很可能不是必需的。去除全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的资源消耗。

获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有列的查询，相比多个独立的只获取部分列的查询可能更有好处。

• 重复查询相同的数据

很容易出现这样的错误——不断地重复执行相同的查询，然后每次都返回完全相同的数据。

是否在扫描额外的记录

在确定查询只返回需要的数据以后，接下来应该看看查询是否为了返回结果扫描了过多的数据。

衡量查询开销的 3 个指标：

• 响应时间
• 扫描的行数
• 返回的行数

这三个指标都会被记录到慢日志中，可以通过慢日志找出扫描行数过多的查询。

响应时间

响应时间是两部分之和：

• 服务时间：指数据库处理这个查询真正花了多长时间。
• 排队时间：指服务器因等待某些资源而没有真正执行查询的时间（如等待 IO 操作，等待行锁等）。最常见和重要的是 IO 等待和锁等待。

实际上可以使用 快速上限法 来估算查询的响应时间。概括地说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机 IO，再用其乘以再具体硬件条件下一次 IO 的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值的响应时间。

扫描的行数和返回的行数

分析查询时，查看该查询扫描的行数是非常有帮助的。这在一定程度上能够说明该查询的效率高不高。

并不是所有行的访问代价都是相同的。较短的行的访问速度更快，内存中的行比磁盘中的行访问速度要快得多。

理想情况下，扫描的行数应该接近于返回的行数，但实际并不多。

扫描的行数和访问类型

在评估查询开销的时候，需要考虑从表中找到某一行数据的成本。

EXPLAIN 语句中的 type 列反映了访问类型。访问类型有很多种：从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这些速度从慢到快，扫描的行数从多到少。你不需要记住这些访问类型，但需要明白扫描表、扫描索引、范围访问和单值访问的概念。

如果没有找到合适的访问类型，最好的解决办法就是增加一个合适的索引。索引让 MySQL 以最高效、扫描行数最少的方式找到需要的记录。

一个查询示例：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SELECT * FROM sakila.film_actor WHERE film_id = 1;

# 这个查询将返回 10 行数据，从 EXPLAIN 来看，MySQL 在索引 idx_kf_film_id 上使用 ref 访问类型来执行查询

EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
####
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_fiml_id
key_len: 2
ref: const
rows: 10
filterd: 100.00
Extra: NULL

结果显示 MySQL 预估需要访问 10 行数据。如果没有合适的索引，MySQL 就不得不使用一种糟糕的访问类型。下面来看看如果删除对应的索引再来运行这个查询会发生什么情况：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_file;
ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;

EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G
####
id: 1
select_type: SIMPLE
table: film_actor
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NUL
rows: 5462
filtered: 10.00
Extra: Using where

访问类型变成了一个全表扫描（ALL），现在 MySQL 预估需要扫描 5462 条记录来完成这个查询。这里的 “Using where” 表示 MySQL 将通过 WHERE 条件来筛选存储引擎返回的记录。

一般地，MySQL 能够使用如下三种方式应用 WHERE 条件，从好到坏依次为：

• 在索引中使用 WHERE 条件来过滤不匹配的记录。这是在存储引擎层完成的。
• 使用索引覆盖扫描（在 Extra 列中出现 Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在 MySQL 层完成的，但无须再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（在 Extra 列中出现 Using where）。这在 MySQL 层完成，MySQL 需要先从数据表中读取记录然后过滤。

如果发现查询需要扫描大量的数据，但只返回少数行，那么通常可以尝试下面的技巧去优化它：

• 所有索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果。
• 该表库表结构。如使用单独的汇总表。
• 重写这个复杂的查询，让 MySQL 优化器能够以更优化的方式执行这个查询。

重构查询的方式

获得更好的查询效率。

一个复杂查询还是多个简单查询

是否需要将一个复杂的查询分成多个简单的查询？

如果一个查询能够胜任时还将其写成多个独立的查询是不明智的。

切分查询

有时候对于一个大查询，需要将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

删除旧数据就是一个很好的例子。定期清除大量数据时，如果用一个大的语句一次完成的话，则可能需要一次锁住很多数据，占满整个事务日志、耗尽资源、阻塞很多查询。将一个大的 DELETE 语句切分成多个较小的语句以尽可能小地影响性能。

一次删除一万行数据一般来说是一个比较高效且对服务器影响最小的做法。同时，需要注意的是，如果每次删除数据后，都暂停一会再做下一次删除，也可以将服务器上原本一次性的压力分散到一个很长时间的时间段中，可大大降低对服务器的影响，还可大大减少删除时锁的持有时间。

分解联接查询

很多应用都会对联接查询进行分解。简单地说，可以对每一个表进行一次单表查询，然后将结果在应哟个程序中进行联接。

示例：

1
2
3
4

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag_id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';

可分解为下面这些查询来代替：

1
2
3

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123, 456, 567, 9098, 8904);

用分解连接查询的方式重构查询有如下优势：

• 让缓存的效率更高。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做联接，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
• 查询本身的效率也可能会提升。
• 可以减少对冗余记录的访问。

查询执行的基础

弄清楚 MySQL 是如何优化和执行查询的。

MySQL 执行一个查询的过程：

• 客户端给服务器发送一条查询语句。
• 服务端进行 SQL 语句解析、预处理，再由优化器生成对应的执行计划。
• MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
• 将结果返回给客户端。

客户端服务器通信协议

MySQL 的客户端和服务器之间的通信协议是半双工，这意味着，在任何时候，要么是由服务器向客户端发送数据，要么是由客户端向服务器发送数据，这两个动作不能同时发生。所以，无法也无须将一个消息切成小块来独立发送。

这种协议让 MySQL 通信变得简单快速，但这意味着没法进行流量控制。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。

一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果。这也是必要时一定要在查询中加上 LIMIT 限制的原因。

查询状态

对于一个 MySQL 连接，或一个线程，任何时刻都有一个状态，该状态表示了 MySQL 当前正在做什么。使用 SHOW FULL PROCESSLIST 命令查看。

状态：

• Sleep：线程正在等待客户端发送新的请求
• Query：线程正在执行查询或正在将结果发送个客户端。
• Locked：在 MySQL 层，该线程正在等待锁。在存储引擎级别实现的锁，如 InnoDB 的行锁，并不会体现在线程状态中。
• Analyzing and statistics：线程正在检查存储引擎的统计信息，并优化查询。
• Copying to tmp table [on disk]：线程正在执行查询，并且将其结果集复制到一个临时表中。如果有 “on disk” 标记，表示 MySQL 正在将一个内存临时表放到磁盘上。
• Sorting result：线程正在对结果集进行排序。

查询优化处理

查询的生命周期的下一步是将一个 SQL 查询转换成一个执行计划，MySQL 再按照这个执行计划和存储引擎进行交互。这包括多个子阶段：解析 SQL、预处理、优化 SQL 执行计划。这个过程中产生的任何错误都可能终止查询。

语法解析器和预处理

首先，MySQL 通过关键字将 SQL 语句进行解析，并生成一颗对应的解析树。解析器将使用 MySQL 语法规则验证和解析查询。

然后，预处理器检查生成的解析树，以查找解析器无法解析的其他语义。

下一步预处理器会验证权限。

查询优化器

现在解析树被认为是合法的了，并且由优化器将其转化成查询执行计划。一条查询可以有多种执行方式，最后返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL 使用基于成本的优化器，它将预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

表和索引的统计信息

因为服务器没有存储任何统计信息，所以 MySQL 查询优化器在生成查询的执行计划时，需要向存储引擎获取相应的统计信息（包括：每个表或索引有多少个页面、每个表的每个索引的基数是多少、数据行和索引的长度是多少、索引的分布信息等）。

如何执行联接查询

联接（JOIN）的范围非常广。MySQL 认为每一个查询都是联接——不仅是匹配两张表中对应行的查询，而是每一个查询、每一个片段都是联接。

UNION 查询，MySQL 先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表中的数据来完成查询。

当前 MySQL 的联接执行策略很简单：MySQL 对任何联接都执行嵌套循环联接操作，即 MySQL 现在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。最后根据各个表匹配的行，返回查询中需要的各列。MySQL 会尝试在最后一个联接表中找到所有匹配的行，如果最后一个联接表无法找到更多的行，MySQL 返回到上一层的联接表，看是否能够找到更多的匹配记录，依此类推，迭代执行。

MySQL 8.x 版本后，已经不再使用基于块的循环嵌套联接操作，而是哈希联接。这让联接操作性能变得更好，特别是当数据集可以全部存储在内存时。

执行计划

MySQL 生成查询的一颗指令树，然后通过查询执行引擎执行完成这颗指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果你对某个查询执行 EXPLAIN EXTENDED 后，再执行 SHOW WARNINGS，就可以看到重构出的查询。

任何多表查询都可以使用一个数来表示，一个四表的联接操作。

在计算机科学中，这被称为一颗平衡树。但这并不是 MySQL 执行查询的方式。MySQL 总是从一个表开始，一直嵌套循环、回溯完成所有表连接。所以，MySQL 的执行计划是一颗左侧深度优先的树，如下。

联接查询优化器

MySQL 查询优化器最重要的一部分就是联接查询优化器，它决定了多个表联接时的顺序。联接查询优化器通过评估不同顺序时的成本来选择一个成本最低的联接顺序。

排序优化

无论如何，排序都是一个成本很高的操作，所以从性能角度考虑，应该尽可能避免排序或者避免对大量数据进行排序。

当不能使用索引生成排序结果的时候，MySQL 需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘。MySQL 将这个过程统一称为文件排序（filesort），即使完全是在内存中排序不需要任何磁盘文件也是如此。

如果需要排序的数据量小于 ”排序缓冲区“，MySQL 使用内存进行快速排序操作。如果内存不够排序，那么 MySQL 会先将数据分块，对每个块使用 “快速排序” 进行排序，并将各个块的排序结果放在磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

MySQL 有两种排序算法：

• 两次传输排序（旧版本使用）
  读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需的数据行。这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机 IO，所以两次传输排序的成本非常高。

• 单次传输排序（新版本使用）
  先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接访问排序结果。效率要高很多。此方式可能占用更多空间，因为会保存查询中每一行所需要的列，而不仅仅是进行排序操作所需要的列。

查询执行引擎

MySQL 的执行计划是一个数据结构，而不是和很多其他关系数据库那样生成对应的可执行字节码。

MySQL 只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些接口也就是我们称为 “handler API” 的接口。

为了执行查询，MySQL 只需要重复执行计划中的各个操作，直到完成所有的数据查询。

将结果返回给客户端

即使查询不需要给客户端返回结果集，MySQL 仍然会返回这个查询的一些信息，如该查询影响到的行数。

MySQL 将结果集返回客户端是一个增量且逐步返回的过程。

查询优化器的局限性

查询优化器只对少部分查询不适用，而我们可以通过改写查询让 MySQL 高效地完成工作。

UNION的限制

有时，MySQL 无法将限制条件从 UNION 的外层下推的内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果需要 UNION 的各个子句能够根据 LIMIT 只取部分结果集，或希望能够排好序再合并结果集的话，就需要再 UNION 的各个子句中分别使用这些子句。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

( SELECT first_name, last_name
  FROM sakila.actor
  ORDER BY last_name
  LIMIT 20)
UNION ALL
( SELECT first_name, last_name
  FROM saklia.customer
  ORDER BY last_name
  LIMIT 20)
LIMIT 20;

现在这个临时表只包含 40 条记录了，除了考虑性能之外，还需注意：从临时表中取出数据的顺序并不是一定的，如果想获得正确的顺序，还需要在最后的 LIMIT 操作前加上一个全局的 ORDER BY 操作。

等值传递

某些时候，等值传递会带来一些意想不到的额外消耗。如，考虑一列上的巨大 IN() 列表，优化器知道它将等于其他表中的一些列，这是由 WHERE, ON 或 USING 子句使彼此相等。

优化器通过将列表复制到所有相关表的相应列来共享列表。通常，因为各个表新增了过滤条件，所以优化器可以更高效地从存储引擎过滤记录。但如果这个列表非常大，则会导致优化器和执行都会变慢。

并行执行

MySQL 无法利用多核特性来并行执行查询。所以不要花时间去尝试寻找并行执行查询的方法。

在同一个表中查询和更新

MySQL 不允许对一张表同时进行查询和更新。

可以使用生成表的形式来绕过上面的限制，因为 MySQL 只会把这个表当作一个临时表来处理。

优化特定类型的查询

介绍如何优化特定类型的查询。

优化COUNT查询

COUNT() 聚合函数，有两种非常不同的作用：

• 统计某列的值的数量：统计列值时要求值是非空的（不统计 NULL）。
• 统计行数

一个常见的问题，如何在一个查询中统计同一列的不同值的数量，以减少查询的语句量。

1
2
3
4
5
6

# 返回不同颜色的商品数量

# 使用 SUM()
SELECT SUM(IF(color = 'blue', 1, 0)) AS blue, SUM(IF(color = 'red', 1, 0)) AS red FROM items;
# 使用 COUNT()
SELECT COUNT(color = 'blue' OR NULL) AS blue, COUNT(color = 'red' OR null) AS red FROM items;

有时候，某些业务场景并不要求完全精确的统计值，此时可以使用 近似值 来代替。 EXPLAIN 出来的优化器估算的行数就是一个不错的近似值，执行 EXPLAIN 并不需要真正地去执行查询，所以成本很低。

很多时候，计算精确值非常复杂，而计算近似值则非常简单。

通常来说，COUNT() 查询需要扫描大量的行（意味着要访问大量的数据），才能获得精确的结果，因此很难优化。在 MySQL 层面能做的就只有索引覆盖扫描了。

优化联接查询

注意以下几点：

• 确保 ON 或 USING 子句的列上有索引。
• 确保任何 GROUP BY 和 ORDER BY 的表达式只涉及一个表中的列，这样 MySQL 才有可能使用索引来优化这个过程。

使用WITH ROLLUP优化GROUP BY

分组查询的一个变种就是要求 MySQL 对返回的分组结果再做一次超级聚合，可使用 WITH ROLLUP 子句来实现这种逻辑，但可能优化得不够。可通过 EXPLAIN 来观察其执行计划。特别要注意分组是否是通过文件排序或临时表实现的。然后再去掉 WITH ROLLUP 子句来看执行计划是否相同。

最好的办法是尽可能地将 WITH ROLLUP 功能转移到应用程序中处理。

优化LIMIT和OFFSET子句

在系统中需要进行分页操作的时候，通常会使用 LIMIT 加上偏移量的办法来实现，同时加上合适的 ORDER BY 子句。如果有对应的索引，通常效率会不错，否则，MySQL 需要做大量的文件排序操作。

一个常见的问题是，在偏移量非常大的时候（如 LIMIT 10000, 20），这时 MySQL 需要查询 10020 条记录然后只返回最后 20 条，前面 10000 条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，也么是在页面中限制分页的数量，要么是优化大偏移的性能。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列。在偏移量很大的时候，这样做的效率会有非常大的提升。

1
2
3
4
5
6

SELECT film.film_id, film_description
FROM sakila.film
INNER JOIN (
  SELECT film_id FROM sakila.film
  ORDER BY title LIMIT 50, 5
) AS lim USING(film_id);

这种延迟联接之所有有效，是因为它允许服务器在不访问行的情况下检查索引中尽可能少的数据，然后，一旦找到所需的行，就将它们与整个表联接，以从该行中检索其他列。

有时候也可以将 LIMIT 查询转换为已知位置的查询，让 MySQL 通过范围扫描获得对应的结果。

1
2

SELECT film_id, description FROM film
WHERE position BEWTEEN 50 AND 54 ORDER BY position;

LIMIT 和 OFFSET 的问题，其实是 OFFSET 的问题。它会导致 MySQL 扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用偏移。

优化SQL CALC FOUND ROWS

分页的时候，另一个常用的技巧是在 LIMIT 语句中加上 SQL_CALC_FOUND_ROWS 提示（hint），这样就可以获得去掉 LIMIT 以后满足条件的行数，因此可以作为分页的总数。但实际上，MySQL 只有在扫描了所有满足条件的行以后，才会知道行数，然后再抛弃不需要的行，而不是在满足 LIMIT 的行数之后就终止扫描。所以该提示的代价可能非常高。

一个更好的设计是将具体的页数换成 “下一页” 按钮，假设每页显示 20 条记录，那么每次查询时都是用 LIMIT 返回 21 条记录并只显示 20 条。如果 21 条存在，那么就显示 “下一页” 按钮，否则就说明没有更多的数据，也就无须返回下一页按钮了。

另一种做法是先缓存较多的数据（如，缓存 1000 条），然后每次分页都从这个缓存中获取。如果结果集小于 1000，则可以在页面上显示所有的分页链接，因为数据都在缓存中，不会对性能造成影响。如果结果集大于 1000，则可以在页面设计一个额外的 “找到的结果多余 1000 条” 之类的按钮。这两种策略都比每次生成全部结果集再抛弃不需要的数据的效率要高得多。

优化UNION查询

MySQL 总是通过创建并填充临时表的方式来执行 UNION 查询，因此很多优化策略在 UNION 查询中都没法很好地使用。经常需要手工地将 WHERE, LIMIT, ORDER BY 等子句下推到 UNION 的各个子查询中，以便优化器可以充分利用这些条件进行优化。

除非你确实需要给服务器消除重复的行，否则一定要使用 UNION ALL，这一点很重要。如果没有 ALL，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的的数据做唯一性检查。这样做的代价非常高。即使有 ALL，MySQL 仍会使用临时表存储结果。事实上，MySQL 总是将检查结果放入临时表，然后再读出，再返回客户端，虽然很多时候这样做是没有必要的。

复制

MySQL 内置的复制功能是构建基于 MySQL 的大规模、高性能应用的基础，这类应用使用所谓的 “水平扩展” 的架构。可通过为服务器配置一个或多个备库的方式来进行数据同步。

在复制方面，你的座右铭应该是 保持简单。除非确实需要，否则不要做任何花里胡哨的事情。在各个方面保持副本与源完全相同将有助于避免很多问题。

MySQL 用户对复制技术相关的术语 主库（master）和 从库（slave）很熟悉。这些术语已被 源（source）和 副本（replica）所代替。

复制概述

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步。它的实现机制：首先在源服务器上，任何数据修改和数据结果变更的事件都会被写入日志文件中，然后，副本服务器从源上的日志文件中读取这些事件并在本地重放执行。这是一个异步处理的过程，副本上的数据可能不是最新的。复制延迟也并没有上限。

MySQL 的复制基本上是向后兼容的，新版本的服务器可以作为老版本服务器的副本，但反过来不行。

通过复制可将读操作指向副本来获得更好的读扩展性，但除非设计得当，否则并不适合通过复制来扩展写操作。在一主多副本的架构中，写操作会被执行多次，这时候整个系统的性能取决于写入最慢的那部分。

复制的常见用途：

• 数据分发
• 读流量扩展
• 备份
• 分析与报告
• 高可用性和故障切换
• MySQL 升级测试

复制如何工作

复制有三个步骤：

• 源端把数据更改记录到二进制日志中（二进制日志时间 binary log events）。
• 副本将源上的日志复制到自己的中继日志中。
• 副本读取中继日志中的事件，将其重放到副本数据之上。

在复制架构中，读取和重放日志事件是解耦的，这就允许读取日志和重复日志异步进行。也就是说，这里面的 IO 线程和 SQL 线程都可以独立运行。

复制原理

选择复制格式

MySQL 提供了三种不同的二进制日志格式用于复制，可通过系统参数 binlog_format 控制。

• 基于语句的复制：通过记录所有在源端执行的数据变更语句来实现。
• 基于行的复制：将事件写入二进制日志，事件包含了该行记录发生了什么改变。
• 混合模式：试图结合以上两种格式的优点。事件的写入，默认使用基于语句的格式，仅在需要时才切换到基于行的格式。

建议使用基于行的复制，它提供了最安全的数据复制方法。

全局事务标识符

在 MySQL 5.6 之前，副本必须跟踪连接到源时读取的二进制日志文件和日志位置（如 binlog.0000002 的 2749 位置读取数据）。当副本从该二进制日志中读取事件时，它每次都会向后推进日志位点。如果此时发生故障，如数据库崩溃了，必须从备份中重建数据。那么问题来了：在源端，如果二进制日志位点重新开始，怎么能重新将副本连接到源库？确定从哪个位点开始是一个非常复杂的过程。

为了解决这个问题，MySQL 新增了另一种跟踪复制位点的方法：全局事务标识符（GTID）。使用它，源服务器提交的每个事务都被分配一个唯一标识符（由 server_uuid 和一个递增的事务编号组成）。当事务被写入二进制日志时，GTID 也随之被写入。

GTID 解决了运行 MySQL 复制的一个令人痛苦的问题：处理日志文件和位置。强烈建议始终按照文档的说明，在数据库中启用 GTID。

崩溃后的复制安全

为了降低复制中断的可能性，建议部分参数按如下配置：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 保障每个事务日志都被同步地写到磁盘。
innodb_flush_log_at_trx_commit=1

# 将二进制日志数据同步到磁盘的频率。
sync_binlog=1

relay_log_info_repository=TABLE

# 使副本服务器在检测到崩溃时会丢弃所有本地中继日志，并从源服务器中获取丢失的数据。
relay_log_recovery=ON

延迟复制

延迟复制的配置语句是 CHANGE REPLICATION SOURCE TO，配置项为 SOURCE_DELAY。

多线程复制

虽然在源上数据可以并行写入，但在副本上只能是单线程的。最新的 MySQL 则提供了多线程复制能力。

多线程复制有两种模式：

• DATABASE：可使用多线程更新不同的数据库，但不会有两个线程同时更新一个数据库。
• LOGICAL_CLOCK：允许对同一个数据库进行并行更新，只要它们都是同一个二进制日志组提交的一部分。

半同步复制

在启用半同步复制后，源在完成每个事务提交时，都需要确保事务至少被一个副本所接收。需要确认副本已收到并成功将其写入自己的中继日志。

由于每个事务都必须等待其他节点的响应，因此该功能会给服务器执行的每个事务都增加额外的延迟。需要根据实际情况考虑是否开启该选项。

如果在一定时间范围内没有副本确认事务，MySQL 将恢复到标准的异步复制模式。这时事务并不会失败。这也说明，半同步复制不是一种防止数据丢失的方法，而是可以让你拥有更具弹性的故障切换的更大工具集的一部分。

复制过滤器

复制过滤选项可以让副本仅复制一部分，不过这个功能并没有想象中那么实用。有两种复制过滤器：

• 从源上的二进制日志中过滤事件
• 从副本上的中继日志中过滤事件

复制切换

计划内切换

计划内切换：维护事件、安全补丁、内核更新和重启 MySQL 等。

要成功地执行此类切换，需要完成以下步骤：

• 确定将哪个副本切换为新的源。这通常是一个包含所有数据的副本。
• 检查演示，确保延时在秒级别。
• 通过设置 super_read_only 停止数据写入源服务器。
• 等待副本与目标完全同步。
• 在目标上取消 read_only 设置。
• 将应用流量切换到目标上。
• 将所有副本重新指向新的源。如果配置了 GTID 和 AUTO_POSTION=1 这很简单。

计划外切换

因此此时已不再存在一个实时运行的源服务器了，因此这将是一个很简短的计划外切换，需要根据副本上已有的数据进行选择：

• 确定需要切换的副本。
• 在目标上关闭 read_only 设置。
• 将应用流量切换到目标上。
• 将所有副本重新指向新源。
• 切换前的源服务器再次启动时，需要默认启用 super_read_only。这有助于防止任何意外的写入。

复制拓扑

强烈建议尽可能保持拓扑结构简单。推荐两种可能的策略，它们几乎涵盖了所有用例。

主动被动模式

在主动/被动拓扑中，应用将所有的读写都指向了单个源服务器。此外，还维护了少量不主动服务于任何应用程序流量的被动副本。

在这个拓扑结构下，应该尽量让源和副本在 CPU、内存等方面具有相同的配置。当需要切换时，能够保证支持之前的应用流量和吞吐量。

在物理环境中，推荐使用至少三台服务器的 n+2 冗余。如果一台发生故障，还有一台额外的副本用于故障切换。如果无法在源上进行备份，可使用其中一个副本作为备份服务器。

在此模式下，实际上是将读扩展绑定到单台服务器的容量上。如果达到读扩展上限，则必须演进到更复杂的拓扑，否则就不得不利用分片来减少源上的读取压力。

主动只读池模式

在主动/只读池模式中，你将所有写入指向源服务器。根据应用程序的需要，读取则可以被发送到源服务器或只读池。只读池可以实现读取密集型应用程序的都水平扩展。

理想情况下，至少要有一个副本（最好两个）与源具有相同配置。当需要故障切换时，该副本应该有足够的容量支持业务的流量。

如果随着时间的推移，只读池在持续增长，则可以让副本用不同的硬件配置来优化成本。在这种情况下，请尝试将流量进行加权，运行在更好的硬件配置的副本上可以承担更多的流量。

在只读池中的服务器数量应满足先前提出的要求，还需要至少一台服务器可充当故障切换的目标。此外，还需要有足够的节点来支撑读流量，以及用于节点故障的小缓冲区。对于读取，最有效的使用率参考指标是 CPU 使用率。

在使用读取池时，应用程序必须对延迟读取有一定的容忍度。可能还需要一种方法来识别那些复制延迟太大的节点，并根据需要将其踢出只读池。

不推荐的拓扑架构

双源主动-主动架构：双源复制设计两台服务器，每台服务器都配置为另一台的副本。不建议使用。

双源主动-被动模式：是双源服务器的一个变种。不建议使用。

带有副本的双源模式：这让架构变得更加复杂。不建议使用。

环形复制：具有三个或多个源，其中每台服务器都在环中。也称为循环复制。不建议使用。

如果此拓扑中的任何服务器损坏，则拓扑结构就会损坏，并且所有数据更新都不会在环中流动。

多源复制：这种拓扑架构非常适合特殊使用情况，不建议长期运行这种架构。

复制管理和维护

在数据量很小而且写入负载一直的时候，通常不太需要经常查看复制延迟或复制中断相关问题。但随着数据量的增加，相关的维护工作也会随之而来。

复制监控

在配置复制监控时，需要注意以下几点：

• 复制同时需要源和副本上的磁盘空间
• 监控复制的状态和错误
• 延迟复制的实际延迟

观察复制延迟

通常，最需要监控的就是副本与源之间的复制延迟是多少。最好的解决方案是心跳记录，它需要在源上每秒更新一次时间戳。需要计算延迟时，可以简单地在副本上用当前时间戳减去记录的心跳时间。

确定副本数据的一致性

建议遵顼如下规则或配置：

• 在副本上，始终启用 super_read_only，只允许复制写入，是运行副本的最安全方式。
• 使用基于行的复制或确定性的语句。
• 不要尝试同时写入复制拓扑中的多台服务器。

复制相关FAQ

• 源端二进制日志损坏：如果源端二进制日志已被损坏，那么别无选择，只能重建副本。
• 非唯一的服务器 ID：如果不小心配置了具有相同服务器 ID 的两个副本，不仔细观察的话，它们似乎可以正常工作，但会有些奇怪的情况。请配置不同的服务器 ID。
• 未配置服务器 ID：如果没有配置服务器 ID，MySQL 将显示为使用 CHANGE REPLICATION SOURCE TO 配置了复制，但不允许启动副本。必须设置该值。
• 临时表丢失：最好的方法是使用基于行的复制。其次是统一命令临时表（如 temp_ 为前缀），然后使用复制规则完全跳过复制临时表。
• 复制延迟过大：一些减少复制延迟的常用方法：多线程复制、使用分片技术、临时降低持久化要求（请非常小心）。
• 来自源服务器的超大数据包：当源的 max_allowd_packet 大小与副本不匹配时，可能会出现另一个难以跟踪的复制问题。
• 磁盘空间耗尽
• 复制的限制：有很多 SQL 函数和编程实践都无法可靠地被复制。

备份与恢复

有两种主要类型的备份：

• 裸文件（物理）备份：指文件系统中的文件副本。
• 逻辑备份：指重建数据所需的 SQL 语句。

为什么要备份

• 灾难恢复
• 审计
• 测试

设计备份方案

一些建议：

• 在生产环境中，对于大数据库来说，裸文件备份是必须的。逻辑备份太慢并受到资源限制，从逻辑备份中恢复需要很长时间。基于快照的备份，是很好的选择。
• 保留多个备份集。
• 定期从逻辑备份（或裸文件备份）中抽取数据进行恢复测试。
• 保存二进制日志用于基于故障时间点的恢复。
• 通过演练整个恢复过程来测试备份和恢复。
• 要仔细考虑安全性

在线备份还是离线备份

• 备份时间
• 备份负载
• 恢复时间

逻辑备份还是物理备份

逻辑备份要么是 SQL 语句，要么是以某个符号分隔的文本。

逻辑备份优点：

• 备份文件可用文本编辑器查看和操作
• 恢复非常简单。通过 mysql 命令导入即可。
• 可通过网络来备份和恢复。
• 可在类似云数据库这样不能访问底层文件系统的系统中使用。
• 备份很简单，因为 mysqldump 工具很灵活。
• 与存储引擎无关。
• 有助于避免数据损坏。

逻辑备份的缺点：

• 必须由数据库服务器完成备份工作，需要占用 CPU 资源。
• 备份在某些场景下比数据库文件更大。
• 无法保证导出后再还原出来一定是同样的数据，尽管非常少见。
• 从备份中恢复还需要 MySQL 加载和解释语句，将它们转化为存储格式，并重建索引。这一切会很慢。
• 最大缺点是从 MySQL 中导出数据和通过 SQL 语句将其加载回去的庞大开销。

物理（裸文件）备份优点：

• 备份基于文件的物理备份，只需将文件复制到其他地方即可完成备份，不需要额外的工作。
• 裸文件设备非常容易跨平台、操作系统和版本。
• 从备份恢复会很快。

物理备份的缺点：

• InnoDB 的原始文件通常比相应的逻辑备份要大得多。
• 裸文件不总是可以跨平台、操作系统和版本。

建议混合使用物理备份和逻辑备份这两种方式：

• 先使用物理备份，用得到的数据启动 MySQL 服务器实例并运行 mysqlcheck。
• 然后，周期性地使用 mysqldump 执行逻辑备份。

备份什么

多考虑以下几点：

• 数据和表定义
• 非显著数据：如二进制日志和 InnoDB 事务日志。
• 代码：如触发器和存储过程的代码。
• 服务器配置：配置文件。
• 选定的操作系统文件：一些定时任务脚本等。

增量备份和差异备份

当数据量很庞大时，一个常见的策略是做定期的增量备份和差异备份。它们都是部分备份。

• 差异备份：是对上次全量备份后所有改变的部分而做的备份。
• 增量部分：是对自任意类型的上次备份后的所有修改做的备份。

一些建议：

• 使用 Percona XtraBackup 和 MySQL Enterprise Backup 中的增量备份特性。
• 备份二进制文件。可在每次备份后使用 FLUSH LOGS 来记录一个新的二进制日志，这样就只需要备份新的二进制日志。
• 如果有一些引用表，可考虑将它们单独放在一个数据库中，这样就不需要每次都备份这些表。

增量备份的缺点包括会增加恢复的复杂性、额外的风险，更长的恢复时间。如果可以做全备份，考虑到简单性，建议尽量做全备份。

在副本中备份

从副本中备份最大好处是可以不干扰源库，避免在源库上增加额外的负载。

副本可能与源库中的数据不完全一样。

管理和备份二进制日志

二进制日志对于基于时间点的恢复是必需的，通常比数据要小，所以更容易进行频繁的备份。

如果有某个时间点的数据备份，和所有从那以后的二进制日志，就可以重放从上次全备份以来的二进制日志并向前回滚所有的变更。

需要制定日志的过期策略以防止磁盘被二进制日志写满。建议尽可能保留日志以备所需。

1
2
3

# 通知 MySQL 定期清理日志
binlog_expire_logs_seconds

备份和恢复工具

• MySQL Enterprise Backup
• Percona XtraBackup
• mydumper
• mysqldump

备份数据

应该最大化地利用网络、磁盘和 CPU 的能力以尽可能快地完成备份。

逻辑SQL备份

逻辑 SQL 导出是 mysqldump 默认的方式。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

mysqldump test t1

/*140101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
.. [More version-specific comments to save options for restore]
-- Table structure for table `t1`
DROP TABLE IF EXISTS `t1`;
/*140101 SET @saved_cs_client = @@character_set_client */;
/*150503 SET character_set_client = utf8mb4 */;
CREATE TABLE `t1` (
`a` int NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*140101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*140000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1);
/*140000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*140103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*140101 SET SQL_MODE=@OLD_SQL_MODE */;

导出文件包含表结构和数据，均以有效的 SQL 命令形式写出。文件以设置 MySQL 各种选项的注释开始。这些选项要么是为了使恢复工作更高效，要么是为了保证兼容性和正确性。

单独的 SQL 逻辑备份有几个问题：

• 巨大的 SQL 语句
• 单个巨大的文件
• 逻辑备份的成本很高
• 建议使用 mydumpe，以避免单线程备份的一些问题。

文件系统快照

文件系统/磁盘快照能够瞬间创建用来备份的一致的镜像。

XtraBackup

Percona XtraBackup 是备份 MySQL 最流行的解决方案之一，它的配置非常灵活，包括备份压缩、文件加密等。

从备份中恢复数据

如何恢复数据取决于数据是怎么备份的。

• 停止 MySQL 服务器
• 记录服务器的配置和权限
• 将数据从备份中移动到 MySQL 数据目录
• 改变配置
• 改变文件权限
• 以限制访问模式重启服务器，等待其完成启动
• 载入逻辑备份文件
• 检查和重放二进制日志
• 检查已经还原的数据
• 以完全权限重启服务器

恢复逻辑备份

在加载导出文件之前，应该花时间考虑文件有多大，需要多久加载完，以及在启动之前还需要做什么事情。

加载巨大的文件对于一些存储引擎有影响。如，在单个事务中将 100GB 数据加载到 InnoDB 就不是一个好想法，因为巨大的回滚段将会导致问题。应该以可控大小的块来加载，并且逐个提交事务。

1
2
3
4
5
6
7
8

# 恢复命令
mysql < test-backup.sql

# 单个表
grep 'INSERT INTO `test`' test-bakcup.sql | mysql

# 压缩文件
gunzip -c test-backup.sql.gz | mysql

1
2
3

SET SQL_LOG_BIN = 0;
SOURCE /tmp/backup/test-bakckup.sql;
SET SQL_LOG_BIN = 1;

从快照中恢复

使用XtraBackup进行恢复

原始文件恢复后启动MySQL

检查配置和权限，观察错误日志。对每个数据库运行 SHOW TABLE STATUS 来再次检测错误日志。

扩展MySQL

在高速的业务环境中，流量可能逐年增长几个数量级，环境会变得更加复杂，随之而来的数据需求也会快速增加。扩展 MySQL 与其他类型的服务非常不同，因为数据是有状态的。

什么是可扩展性

可扩展性是系统支撑不断增长的流量的能力。一个系统的可扩展性可以用成本和简单性来衡量。

容量是一个和可扩展性相关的概念。

从更高的视角来看，可扩展性就是能够通过增加资源来提升容量的能力。

从几个角度考虑负载：

• 数据量
• 用户数
• 用户活跃度
• 相关数据集的大小

读限制和写限制的工作负载

首先要审视的问题是，需要扩展读限制还是写限制？

• 读限制工作负载是指读取（SELECT）总流量超过服务器容量的工作限制。
• 写限制工作负载则超过了服务器提供 DML（INSERT, UPDATE, DELETE）操作的容量。

功能拆分

如何恰当地将大型的整体/混合数据库拆分为一组合理的较小集群，以帮助业务扩展。下面是一些原则：

• 不要根据工程团队的组织架构拆分，它会经常变动。
• 根据业务功能来拆分表。支撑账户注册的表可以与负责现有客户设置的表分开，而支持新功能的表应该在单独的数据库中开发。
• 不要回避处理数据中混杂了不同业务关系的问题，你不仅需要倡导数据分离，还需要倡导应用程序重构，并需要引入 API 来实现相互跨界的访问。

使用读池扩展读

集群中的副本可用于多个目的：故障切换和处理读请求。

使用读池会有不止一台服务读请求的数据库主机，有几点需要考虑：

• 如何将流量路由到读副本？
• 复合均匀地分配负载？
• 如何进行健康检查并移除不健康或延迟的副本，以避免提供陈旧的数据？
• 如何避免因意外地删除所有节点而对应用程序流量造成更多损害？
• 如何手动移除服务器以进行维护？
• 如何将新配置的服务器添加到负载均衡器中？
• 有哪些自动检查可避免在负载均衡器准备就绪之前添加新配置的节点？
• 对“准备好迎接新节点”的定义是否足够明确？

管理读池的一种常见方法是使用负载均衡器来提供 VIP。实现技术有：HAProxy、硬件负载均衡器，或公云环境的负载均衡器。

在 MySQL 中，建议使用最少连接数（leastconn）实现池节点之间的平衡。在负载升高时，像轮询这样的随机平衡策略将无助于使用未过载的主机。

排队机制

当设计上倾向于一致性而不是可用性的数据存储来扩展事务时，扩展应用程序会变得很复杂。写入一个源数据节点的多个应用程序节点将导致数据库系统更容易出现锁定超时、死锁和必须重试的写失败，所有这些最终将导致影响客户的错误或不可接受的延迟。

在讨论数据分片之前，应该先检查数据中的写入热点，并考虑是否所有的写入都真的需要主动持久化到数据库中。其中的一些是否可以被放入到队列中，并在一个可接受的时间范围内写入数据库？

如果将队列应用于写负载，那么一个重要的设计选择是，预先确定这些调用在被放入队列后所期望完成的时间范围。监控请求在队列中花费时间的增长，对于此策略何时运行，以及你何时确实需要开始分割此数据集以支持更多的并行写负载，能提供重要的衡量指标。

使用分片扩展写

如果不能通过最佳的查询和排队写入来管理写入流量的增长，那么分片将是剩下的选项。

分片意味着将数据切分成不同的、更小的数据库集群，这样就可以同时在更多的源主机上执行更多的写入操作。可进行两种不同类型的分片：

• 功能分割（Functional partitioning）：将不同的节点用于不同的任务。
• 数据分片（Data sharding）：是当今扩展超大型 MySQL 应用程序最常见和最成功的方法。通过将数据切分成更小的部分或片，并将它们存储在不同的节点上，可以达到拆分数据的目的。

选择切分方案

分片技术最重要的挑战是查找和检索数据。如果查找和检索数据取决于如何切分数据。

目标是使最重要和最频繁的查询接触到尽可能少的分片（请记住，可扩展性的原则之一是避免节点之间的交叉访问）。该过程最关键的部分是为数据选择一个或多个分片键。分片键决定了每个分片上应该分布哪些行。

如果知道了对象的分片键，就可以回答以下两个问题：

• 我们应该把数据存储到哪里？
• 在哪里可以找到我需要获取的数据？

我们总是希望将查询本地化到一个分片。当水平切分数据时，总是希望避免通过跨分片查询来完成任务。跨分片关联数据将增加应用层的复杂性，并从一开始就削弱了数据分片的好处。使用数据分片的最坏的情况是，当不知道所需的数据存储在哪里时，需要扫描每个分片来找到它。

一个良好的分片键是数据库中一个非常重要的实体的主键。这些键决定了分片的单元。（例如，如果按照用户 ID 切分数据，则分片的单元是用户。）

选择片键时，尝试挑选某些尽可能避免跨分片查询的键，但也要使分片足够小，这样将不会出现不成比例的大数据块带来的问题。如果有可能，让分片最终均匀地小，或至少要小到很容易通过将不同数量的分片组合在一起来实现平衡。

一个良好的开展这项工作的方法是，用一张实体-关系（E-R）图来绘制模型。

左边的数据模型很容易被切分，因为它有许多连接的子图。

多个分片键

仅仅是因为需要多个分片键，并不意味着需要设计两份完全冗余的数据存储。

跨分片查询

大多数分片应用程序都会有一些查询需要聚合或联接多个分片的数据。实现数据切分最困难的部分是使这类查询正常工作，因为应用程序所认为的单个查询需要被分裂为多个查询并且并行执行，每个分片一个。一个好的数据库抽象层可以帮你减轻查询的负担，但即便如此，这样的查询也比分片内查询慢得多，且代价更高，因此主动缓存通常也是有必要的。

你应该尽力使查询尽可能简单，并包含在一个分片内。对于需要跨分片聚合的情况，建议从应用程序逻辑的整体加以考虑。

跨分片查询也可以从汇总表中获益。可通过遍历所有分片并在完成后将结果冗余地存储在每个分片上来构建汇总表。如果觉得在每个分片上复制数据太浪费，可将汇总表合并到另一个数据存储中，这样其就只被存储一次。

非分片的数据通常存在于全局节点中，并使用大量的缓存来使其免受负载的影响。

跨分片查询并不是使用分片技术后唯一的难题，维护数据的一致性也很困难。外键不能跨分片工作，所以通常的解决方案使根据应用程序的需要检查引用完整性，或当分片的内部一致性很重要的时候可以在分片中使用外键。

接下来介绍两个最流行的开源工具，可以帮助数据分片与功能分割顺利进行。

Vitess

Vitess 是面向 MySQL 的一个数据库集群系统，它支持许多特性：

• 支持水平分片，包括数据分片
• 拓扑结构管理
• 源节点故障切换管理
• schema 变更管理
• 连接池
• 查询重写

ProxySQL

ProxySQL 是专门为 MySQL 协议编写的。

合规性