MySQL 实战总结
因为最近快入职了，正好在公众号看到推广，就在拉勾教育上购买了一套 MySQL 实战教程：《姜承尧的MySQL实战宝典》，重新温习一下 MySQL，这篇文章主要记录通过这个课程学习到的一些 MySQL 使用技巧。
这里附上课程链接：拉勾教育——MySQL实战宝典
表结构设计
数字类型
对于表的主键，采用 BIGINT 类型。在 MySQL 中，当自增达到 INT/BIGINT 上限后，再次进行自增插入时，会报重复错误，MySQL 数据库并不会自动将其重置为 1。
另外，MySQL 8.0 版本前，自增不持久化，自增值可能会存在回溯问题。例如，在正常情况下，在删除自增值为 3 的记录后，下一个自增值依然为 4（AUTO_INCREMENT=4），这并没有错误，自增并不会进行回溯。但若这时数据库发生重启，那数据库启动后，表的自增起始值将再次变为 3，即自增值发生回溯。MySQL 版本到 8.0 后，每张表的自增值会持久化，此时就不存在回溯问题。
另外，在海量互联网架构设计过程中，为了之后更好的分布式架构扩展性，不建议使用整型类型做主键，更为推荐的是字符串类型
而对于资金字段的设计，不推荐使用 DECIMAL 类型，因为类型 DECIMAL 是个变长字段，若要定义金额字段，则定义诸如 DECIMAL(8,2) 是远远不够的。这样只能表示存储最大值为 999999.99，百万级的资金存储。用户的金额至少要存储百亿的字段，而统计局的 GDP 金额字段则可能达到数十万亿级别。用类型 DECIMAL 定义，不好统一。另外重要的是，类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。
DOUBLE,FLOAT 后续版本不再支持
一般使用整数类型来作为转换，也就是说，资金类型更推荐使用用分单位存储，而不是用元单位存储。如 1 元在数据库中用整型类型 100 存储。这样的好处是，所有金额相关字段都是定长字段，占用 8 个字节，存储高效。另一点，直接通过整型计算，效率更高。推荐使用 BIG INT 来存储金额相关的字段
这一点当时在实习的时候，因为部门跟京东，拼多多等有业务合作，发现不管是部门内部的字段，还是京东，拼多多的金额字段，都是使用分为单位，字段类型都是 BIG INT。
在数据库设计中，非常强调定长存储，因为定长存储的性能更好。
数据库中记录的存储方式，大致如下：
若发生更新，记录 1 原先的空间无法容纳更新后记录 1 的存储空间，因此，这时数据库会将记录 1 标记为删除，寻找新的空间给记录1使用，如：
上图中 *记录 1 表示的就是原先记录 1 占用的空间，而这个空间后续将变成碎片空间，无法继续使用，除非人为地进行表空间的碎片整理。
课后题：账户余额字段可以用整型替代高精度型，还有哪些字段设计时可以考虑使用整型，从而有更好的性能？
IP可以用整形存储，IP本身是个变长字段，但是通过 INT 存储，固定占用4个字节，提升了存储性能和效率。但是在使用 INT 存储 IP 字段的时候，要使用 INT UNSIGNED ，官方文档有提示：
To store values ganerated by INET_ATON(), use an INT UNSIGNED column rather than INT, which is signed.
日期类型不建议使用整型，因为效率不高，性能也不好，还不支持存储毫秒值。
字符串类型
在 MySQL 数据库下，绝大部分字符串场景使用类型 VARCHAR 就足够了，推荐把 MySQL 的默认字符集设置为 UTF8MB4 以支持 emoji 表情包的存储。在 MySQL8.0 之后，默认的字符集已经设置为 UTF8MB4，对于 8.0 之前的版本，需要手动进行更改：
[mysqld] character-set-server = utf8mb4
...
不同的字符集，CHAR(N)、VARCHAR(N) 对应最长的字节也不相同（这里 N 指定的是字符）。比如 GBK 字符集，1 个字符最大存储 2 个字节，UTF8MB4 字符集 1 个字符最大存储 4 个字节。所以从底层存储内核看，在多字节字符集下，CHAR 和 VARCHAR 底层的实现完全相同，都是变长存储。所以针对多字符集，在表结构设计时，可以把 CHAR 全部用 VARCHAR 替换，底层存储的本质实现一模一样。
另外，每一种字符集都有默认的排序规则，可以用命令 SHOW CHARSET 来查看：
mysql> SHOW COLLATION LIKE 'utf8mb4%';
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
......
排序规则以 _ci 结尾，表示不区分大小写（Case Insentive），_cs 表示大小写敏感，_bin 表示通过存储字符的二进制进行比较。需要注意的是，比较 MySQL 字符串，默认采用不区分大小的排序规则。
对于后期需要修改字段字符集的场景，如果只是执行例如下面这种语句，实际上插入新的数据列仍然使用的是原来的字符集。
ALTER TABLE emoji_test CHARSET utf8mb4;
其实，上述修改只是将表的字符集修改为 UTF8MB4，下次新增列时，若不显式地指定字符集，新列的字符集会变更为 UTF8MB4，但对于已经存在的列，其默认字符集并不做修改。
因此，正确修改列字符集的命令应该使用 ALTER TABLE ... CONVERT TO... 这样才能将之前的列 a 字符集从 UTF8 修改为 UTF8MB4：
mysql> ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4;
Query OK, 0 rows affected (0.94 sec)
Records: 0 Duplicates: 0 Warnings: 0
性别，状态字段
对于数据库中的性别，状态等字段，不建议使用 TINYINT,INT 这种类型进行存储，原因如下：
- 表达不清：在具体存储时，0 表示女，还是 1 表示女呢？每个业务可能有不同的潜规则；
- 脏数据：因为是 tinyint，因此除了 0 和 1，用户完全可以插入 2、3、4 这样的数值，最终表中存在无效数据的可能，后期再进行清理，代价就非常大了。
对于这种场景，在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚举类型，只允许有限的定义值插入。如果将参数 SQL_MODE 设置为严格模式，插入非定义数据就会报错：
mysql> SHOW CREATE TABLE User\G
*************************** 1. row ***************************
Table: User
Create Table: CREATE TABLE `User` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sex` enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
mysql> SET sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)
由于类型 ENUM 并非 SQL 标准的数据类型，而是 MySQL 所独有的一种字符串类型。抛出的错误提示也并不直观，主要是因为 MySQL 8.0 之前的版本并没有提供约束功能。自 MySQL 8.0.16 版本开始，数据库原生提供 CHECK 约束功能，可以方便地进行有限状态列类型的设计：
mysql> SHOW CREATE TABLE User\G
*************************** 1. row ***************************
Table: User
Create Table: CREATE TABLE `User` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sex` char(1) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `user_chk_1` CHECK (((`sex` = _utf8mb4'M') or (`sex` = _utf8mb4'F')))
) ENGINE=InnoDB
1 row in set (0.00 sec)
账户密码存储设计
数据库密码不能明码存储，另外，通过函数 MD5 加密存储隐私数据也不是很推荐。因为 MD5 算法虽然不可逆，但是 MD5 加密后的值是固定的，如密码 12345678，它对应的 MD5 固定值即为 25d55ad283aa400af464c76d713c07ad。
因此，可以对 MD5 进行暴力破解，计算出所有可能的字符串对应的 MD5 值。若无法枚举所有的字符串组合，那可以计算一些常见的密码，如111111、12345678 等。
所以，在设计密码存储使用，还需要加盐（salt），每个公司的盐值都是不同的，因此计算出的值也是不同的。若盐值为 psalt，则密码 12345678 在数据库中的值为：
password = MD5（‘psalt12345678’）
这样的密码存储设计是一种固定盐值的加密算法，其中存在三个主要问题：
- 若 salt 值被（离职）员工泄漏，则外部黑客依然存在暴利破解的可能性；
- 对于相同密码，其密码存储值相同，一旦一个用户密码泄漏，其他相同密码的用户的密码也将被泄漏；
- 固定使用 MD5 加密算法，一旦 MD5 算法被破解，则影响很大。
所以一个真正好的密码存储设计，应该是：动态盐 + 非固定加密算法。
在这里，作者比较推荐这么设计密码，列 password 存储的格式如下：
$salt$cryption_algorithm$value
其中：
$salt：表示动态盐，每次用户注册时业务产生不同的盐值，并存储在数据库中。若做得再精细一点，可以动态盐值 + 用户注册日期合并为一个更为动态的盐值。$cryption_algorithm：表示加密的算法，如 v1 表示 MD5 加密算法，v2 表示 AES256 加密算法，v3 表示 AES512 加密算法等。$value：表示加密后的字符串。
假设表 User 的设计如下：
CREATE TABLE User (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex CHAR(1) NOT NULL,
password VARCHAR(1024) NOT NULL,
regDate DATETIME NOT NULL,
CHECK (sex = 'M' OR sex = 'F'),
PRIMARY KEY(id)
);
其存储的数据如下：
SELECT * FROM User\G
*************************** 1. row ***************************
id: 1
name: David
sex: M
password: $fgfaef$v1$2198687f6db06c9d1b31a030ba1ef074
regDate: 2020-09-07 15:30:00
*************************** 2. row ***************************
id: 2
name: Amy
sex: F
password: $zpelf$v2$0x860E4E3B2AA4005D8EE9B7653409C4B133AF77AEF53B815D31426EC6EF78D882
regDate: 2020-09-07 17:28:00
在上面的例子中，用户 David 和 Amy 密码都是 12345678，然而由于使用了动态盐和动态加密算法，两者存储的内容完全不同。
即便别有用心的用户拿到当前密码加密算法，则通过加密算法 $cryption_algorithm 版本，可以对用户存储的密码进行升级，进一步做好对于恶意数据攻击的防范。
课后题：
假设表中有个字段 status，表示订单的状态，可能值为：start，done，timeout，unknown，请问你会如何设计？请给出你的SQL。
状态列的最优设计是用字符串+CHECK 约束，这样可以避免脏数据的插入：
CREATE TABLE ...( ... status CHAR(1) ... CONSTRAINT `check_status` CHECK (status in ('S','D','T','U')) ... )若现在要求字段 img 存储用户的头像，你会如何设计这个列？请写入你的SQL
对于图片的存储，一般将其存储在对象存储上，数据库只存储图片对应的链接地址。
日期类型
DATETIME
类型 DATETIME 最终展现的形式为：YYYY-MM-DD HH：MM：SS，固定占用 8 个字节。
从 MySQL 5.6 版本开始，DATETIME 类型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存储 6 位的毫秒值。同时，一些日期函数也支持精确到毫秒，例如常见的函数 NOW、SYSDATE：
mysql> SELECT NOW(6);
+----------------------------+
| NOW(6) |
+----------------------------+
| 2020-09-14 17:50:28.707971 |
+----------------------------+
1 row in set (0.00 sec)
在数据库设计中一般会有一个创建时间和一个更新时间字段，一般默认值都是当前时间，对于更新时间还会设置当更新时自动更新：
CREATE TABLE User (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex CHAR(1) NOT NULL,
password VARCHAR(1024) NOT NULL,
money INT NOT NULL DEFAULT 0,
register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
CHECK (sex = 'M' OR sex = 'F'),
PRIMARY KEY(id)
);
在上面的表 User 中，列 register_date 表示注册时间，DEFAULT CURRENT_TIMESTAMP 表示记录插入时，若没有指定时间，默认就是当前时间。
列 last_modify_date 表示当前记录最后的修改时间，DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) 表示每次修改都会修改为当前时间。
这样的设计保证当用户的金钱（money 字段）发生了变更，则 last_modify_date 能记录最后一次用户金钱发生变更时的时间。
TIMESTAMP
TIMESTAMP 实际存储的内容为 ‘1970-01-01 00:00:00’ 到现在的毫秒数。在 MySQL 中，由于类型 TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到 ‘2038-01-19 03:14:07’。
从 MySQL 5.6 版本开始，类型 TIMESTAMP 也能支持毫秒。与 DATETIME 不同的是，若带有毫秒时，类型 TIMESTAMP 占用 7 个字节，而 DATETIME 无论是否存储毫秒信息，都占用 8 个字节。
类型 TIMESTAMP 最大的优点是可以带有时区属性，因为它本质上是从毫秒转化而来。如果业务需要对应不同的国家时区，那么类型 TIMESTAMP 是一种不错的选择。比如新闻类的业务，通常用户想知道这篇新闻发布时对应的自己国家时间，那么 TIMESTAMP 是一种选择。
TIMESTAMP 可以通过参数 time_zone 指定了当前使用的时区，默认为 SYSTEM 使用操作系统时区，用户可以通过该参数指定所需要的时区。指定时区有两种方式：
SET time_zone = '-08:00';
SET time_zone = 'Asia/Shanghai';
DATETIME or TIMESTAMP ?
在做表结构设计时，对日期字段的存储，开发人员通常会有 3 种选择：DATETIME、TIMESTAMP、INT。
INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。
当然，有些同学会认为 INT 比 TIMESTAMP 性能更好。但是，由于当前每个 CPU 每秒可执行上亿次的计算，所以无须为这种转换的性能担心。更重要的是，在后期运维和数据分析时，使用 INT 存储日期，是会让 DBA 和数据分析人员发疯的，INT的可运维性太差。
也有的同学会热衷用类型 TIMESTEMP 存储日期，因为类型 TIMESTAMP 占用 4 个字节，比 DATETIME 小一半的存储空间。
但若要将时间精确到毫秒，TIMESTAMP 要 7 个字节，和 DATETIME 8 字节差不太多。另一方面，现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近，这是需要开发同学好好思考的问题。
总的来说，作者建议使用类型 DATETIME。 对于时区问题，可以由前端或者服务这里做一次转化，不一定非要在数据库中解决。
另外，TIMESTAMP 还存在潜在的性能问题：虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU 指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数 __tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。
- 性能不如 DATETIME： DATETIME 不存在时区转化问题。
- 性能抖动： 海量并发时，存在性能抖动问题。
为了优化 TIMESTAMP 的使用，强烈建议使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区：
[mysqld]
time_zone = "+08:00"
最后，通过命令 mysqlslap 来测试 TIMESTAMP、DATETIME 的性能，命令如下：
复制代码
# 比较time_zone为System和Asia/Shanghai的性能对比
mysqlslap -uroot --number-of-queries=1000000 --concurrency=100 --query='SELECT NOW()'
最后的性能对比如下：
从表中可以发现，显式指定时区的性能要远远好于直接使用操作系统时区。所以，日期字段推荐使用 DATETIME，没有时区转化。即便使用 TIMESTAMP，也需要在数据库中显式地配置时区，而不是用系统时区。
课后题：若设置字段为 DATETIME(6)，时间精确到小数点后 6 位，在这么高精度的存储下，请问存储的值是否是唯一的？我们又如何验证呢？
多线程并发执行的情况有可能会重复，可以通过 MySQL 自带的测试工具 muysqlslap 进行多线程测试。
JSON 类型
JSON 类型是从 MySQL 5.7 版本开始支持的功能，8.0 版本解决了更新 JSON 的日志性能瓶颈。所以建议在 MySQL8.0 当中使用 JSON 数据类型。
MySQL 中 JSON 类型有自己的存储格式，还能在每个对应的字段上创建索引，做特定的优化。同时，其无须预定义字段，字段可以无限拓展，避免关系型数据库中拓展列而使用 ALTER TABLE ... ADD COLUM ... 这样比较重的操作。
在数据库中，JSON 类型比较适合存储一些修改较少、相对静态的数据，比如用户登录信息，用户画像的存储。
用户登录信息
CREATE TABLE UserLogin (
userId BIGINT NOT NULL,
loginInfo JSON,
PRIMARY KEY(userId)
);
由于现如今登录方式越来越多样化，如同一账户支持手机、微信、QQ 账号登录，所以这里可以用 JSON 类型存储登录的信息。
插入数据如下：
SET @a = '
{
"cellphone" : "13918888888",
"wxchat" : "破产码农",
"QQ" : "82946772"
}
';
INSERT INTO UserLogin VALUES (1,@a);
SET @b = '
{
"cellphone" : "15026888888"
}
';
INSERT INTO UserLogin VALUES (2,@b);
而如果不采用 JSON 数据类型，就需要针对每一种方式都建立一个列，这种方式存在两个问题：
- 有些列可能比较稀疏，一些列可能大部分都是空值。
- 如果出现一种新的登录方式，需要创建新的列，而 JSON 可以避免这么重的操作。
MySQL 中提供了丰富的 JSON 字段处理函数，其中最常见的就是函数 JSON_EXTRACT，它用来从 JSON 数据中提取所需要的字段内容，如下面的这条 SQL 语句就查询用户的手机和微信信息：
SELECT
userId,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.cellphone")) cellphone,
JSON_UNQUOTE(JSON_EXTRACT(loginInfo,"$.wxchat")) wxchat
FROM UserLogin;
+--------+-------------+--------------+
| userId | cellphone | wxchat |
+--------+-------------+--------------+
| 1 | 13918888888 | 破产码农 |
| 2 | 15026888888 | NULL |
+--------+-------------+--------------+
2 rows in set (0.01 sec)
如果觉得每次写 JSON_EXTRACT、JSON_UNQUOTE 非常麻烦，MySQL 还提供了 ->> 表达式，和上述 SQL 效果完全一样：
SELECT
userId,
loginInfo->>"$.cellphone" cellphone,
loginInfo->>"$.wxchat" wxchat
FROM UserLogin;
当 JSON 数据量非常大，如果希望对 JSON 数据进行有效检索时，可以利用 MySQL 的函数索引功能对 JSON 中的某个字段进行索引。
比如在上面的用户登录示例中，假设用户必须绑定唯一手机号，且希望未来能用手机号码进行用户检索时，可以创建下面的索引：
ALTER TABLE UserLogin ADD COLUMN cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone");
ALTER TABLE UserLogin ADD UNIQUE INDEX idx_cellphone(cellphone);
上述 SQL 首先创建了一个虚拟列 cellphone，这个列是由函数 loginInfo->>"$.cellphone" 计算得到的。然后在这个虚拟列上创建一个唯一索引 idx_cellphone。这时再通过虚拟列 cellphone 进行查询，就可以看到优化器会使用到新创建的 idx_cellphone 索引：
EXPLAIN SELECT * FROM UserLogin
WHERE cellphone = '13918888888'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: UserLogin
partitions: NULL
type: const
possible_keys: idx_cellphone
key: idx_cellphone
key_len: 1023
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
可以在一开始创建表的时候，就完成虚拟列及函数索引的创建。如下表创建的列 cellphone 对应的就是 JSON 中的内容，是个虚拟列；uk_idx_cellphone 就是在虚拟列 cellphone 上所创建的索引。
CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JSON,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY(userId),
UNIQUE KEY uk_idx_cellphone(cellphone)
);
用户画像设计
某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。比如：
- 在电商行业中，根据用户的穿搭喜好，推荐相应的商品；
- 在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲；
- 在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。
在这种场景下，作者强烈推荐使用 JSON 类型在数据库中存储用户画像信息，并结合 JSON 数组类型和多值索引的特点进行高效查询。
假设有张画像定义表：
CREATE TABLE Tags (
tagId bigint auto_increment,
tagName varchar(255) NOT NULL,
primary key(tagId)
);
SELECT * FROM Tags;
+-------+--------------+
| tagId | tagName |
+-------+--------------+
| 1 | 70后 |
| 2 | 80后 |
| 3 | 90后 |
| 4 | 00后 |
| 5 | 爱运动 |
| 6 | 高学历 |
| 7 | 小资 |
| 8 | 有房 |
| 9 | 有车 |
| 10 | 常看电影 |
| 11 | 爱网购 |
| 12 | 爱外卖 |
+-------+--------------+
可以看到，表 Tags 是一张画像定义表，用于描述当前定义有多少个标签，接着给每个用户打标签，比如用户 David，他的标签是 80 后、高学历、小资、有房、常看电影；用户 Tom，90 后、常看电影、爱外卖。
若不用 JSON 数据类型进行标签存储，通常会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签：
+-------+---------------------------------------+
|用户 |标签 |
+-------+---------------------------------------+
|David |80后 ； 高学历 ； 小资 ； 有房 ；常看电影 |
|Tom |90后 ；常看电影 ； 爱外卖 |
+-------+---------------------------------------+
这样做的缺点是： 不好搜索特定画像的用户，另外分隔符也是一种自我约定，在数据库中其实可以任意存储其他数据，最终产生脏数据。
而采用 JSON 数据类型就可以很好的解决这个问题：
CREATE TABLE UserTag (
userId bigint NOT NULL,
userTags JSON,
PRIMARY KEY (userId)
);
INSERT INTO UserTag VALUES (1,'[2,6,8,10]');
INSERT INTO UserTag VALUES (2,'[3,10,12]');
userTags 存储的标签就是表 Tags 已定义的那些标签值，只是使用 JSON 数组类型进行存储。
MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以可以在表 UserTag 上创建 Multi-Valued Indexes：
ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
如果想要查询用户画像为常看电影的用户，可以使用函数 MEMBER OF：
EXPLAIN SELECT * FROM UserTag
WHERE 10 MEMBER OF(userTags->"$")\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: UserTag
partitions: NULL
type: ref
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
SELECT * FROM UserTag
WHERE 10 MEMBER OF(userTags->"$");
+--------+---------------+
| userId | userTags |
+--------+---------------+
| 1 | [2, 6, 8, 10] |
| 2 | [3, 10, 12] |
+--------+---------------+
2 rows in set (0.00 sec)
如果想要查询画像为 80 后，且常看电影的用户，可以使用函数 JSON_CONTAINS：
EXPLAIN SELECT * FROM UserTag
WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: UserTag
partitions: NULL
type: range
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: NULL
rows: 3
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
SELECT * FROM UserTag
WHERE JSON_CONTAINS(userTags->"$", '[2,10]');
+--------+---------------+
| userId | userTags |
+--------+---------------+
| 1 | [2, 6, 8, 10] |
+--------+---------------+
1 row in set (0.00 sec)
如果想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP：
EXPLAIN SELECT * FROM UserTag
WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: UserTag
partitions: NULL
type: range
possible_keys: idx_user_tags
key: idx_user_tags
key_len: 9
ref: NULL
rows: 4
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
SELECT * FROM UserTag
WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');
+--------+---------------+
| userId | userTags |
+--------+---------------+
| 1 | [2, 6, 8, 10] |
| 2 | [3, 10, 12] |
+--------+---------------+
2 rows in set (0.01 sec)
思考题：我们说了很多 JSON 类型的好处，但思考一下，JSON 类型的缺点是什么？
JSON 的优点是灵活，列无须定义，但是缺点是过于灵活，对业务没有任何约束，可能导致最终管理非常麻烦。所以，JSON 是张鬼牌，在一定特点场景下用好，则威力巨大，否则会遭到反噬。
表结构设计
自增主键设计
使用 BIGINT 的自增类型作为主键的设计仅仅适合非核心业务表，比如告警表、日志表等。真正的核心业务表，不要用自增键做主键，主要有 6 个原因：
自增存在回溯问题（8.0之后会进行持久化解决该问题）；
自增值在服务器端产生，存在并发性能问题；
自增值做主键，只能在当前实例中保证唯一，不能保证全局唯一；
公开数据值，容易引发安全问题，例如知道地址http://www.example.com/User/10/，很容猜出 User 有 11、12 依次类推的值，容易引发数据泄露；
MGR（MySQL Group Replication） 可能引起的性能问题；
分布式架构设计问题。
另外，自增值是在 MySQL 服务端产生的值，需要有一把自增的 AI 锁保护，若这时有大量的插入请求，就可能存在自增引起的性能瓶颈。比如在 MySQL 数据库中，参数 innodb_autoinc_lock_mode 用于控制自增锁持有的时间。假设有一 SQL 语句，同时插入 3 条带有自增值的记录：
INSERT INTO ... VALUES (NULL,...),(NULL,...),(NULL,...);
参数 innodb_autoinc_lock_mode 对性能的影响如下：
从表格中可以看到，一条 SQL 语句插入 3 条记录，参数 innodb_autoinc_lock_mode 设置为 1，自增锁在这一条 SQL 执行完成后才释放。
如果参数 innodb_autoinc_lock_mode 设置为2，自增锁需要持有 3 次，每插入一条记录获取一次自增锁。
这样设计好处是： 当前插入不影响其他自增主键的插入，可以获得最大的自增并发插入性能。
缺点是： 一条 SQL 插入的多条记录并不是连续的，如结果可能是 1、3、5 这样单调递增但非连续的情况。
所以，如果想获得自增值的最大并发性能，把参数 innodb_autoinc_lock_mode 设置为2。
虽然可以通过调整参数 innodb_autoinc_lock_mode获得自增的最大性能，但是由于其还存在上述 5 个问题。因此，在互联网海量并发架构实战中，更推荐 UUID 做主键或业务自定义生成主键。
UUID 主键设计
UUID（Universally Unique Identifier）代表全局唯一标识 ID。显然，由于全局唯一性，可以把它用来作为数据库的主键。
MySQL 数据库遵循 DRFC 4122 命名空间版本定义的 Version 1规范，可以通过函数 UUID自动生成36字节字符：
mysql> SELECT UUID();
+--------------------------------------+
| UUID() |
+--------------------------------------+
| e0ea12d4-6473-11eb-943c-00155dbaa39d |
+--------------------------------------+
根据 Version 规范，MySQL 中的 UUID 由以下几个部分组成：
UUID = 时间低（4字节）- 时间中高+版本（4字节）- 时钟序列 - MAC地址
前 8 个字节中，60 位用于存储时间，4 位用于 UUID 的版本号，其中时间是从 1582-10-15 00：00：00.00 到现在的100ns 的计数。
60 位的时间存储中，其存储分为：
时间低位（time-low），占用 12 位；
时间中位（time-mid），占用 2 字节，16 位；
时间高位（time-high），占用 4 字节，32 位；
需要特别注意的是，在存储时间时，UUID 是根据时间位逆序存储， 也就是低时间低位存放在最前面，高时间位在最后，即 UUID 的前 4 个字节会随着时间的变化而不断“随机”变化，并非单调递增。而非随机值在插入时会产生离散 IO，从而产生性能瓶颈。这也是 UUID 对比自增值最大的弊端。
为了解决这个问题，MySQL 8.0 推出了函数 UUID_TO_BIN，它可以把 UUID 字符串进行如下转换：
通过参数将时间高位放在最前，解决了 UUID 插入时乱序问题；
去掉了无用的字符串”-“，精简存储空间；
将字符串其转换为二进制值存储，空间最终从之前的 36 个字节缩短为了 16 字节。
将之前的 UUID 字符串 e0ea12d4-6473-11eb-943c-00155dbaa39d 通过函数 UUID_TO_BIN 进行转换，得到二进制值如下所示：
SELECT UUID_TO_BIN('e0ea12d4-6473-11eb-943c-00155dbaa39d',TRUE) as UUID_BIN;
+------------------------------------+
| UUID_BIN |
+------------------------------------+
| 0x11EB6473E0EA12D4943C00155DBAA39D |
+------------------------------------+
1 row in set (0.00 sec)
除此之外，MySQL 8.0 也提供了函数 BIN_TO_UUID，支持将二进制值反转为 UUID 字符串。在下表中，将主键设置为 BINARY(16)，用于存储排序后的 16 字节的 UUID 值，表的结构如下：
CREATE TABLE User (
id BINARY(16) NOT NULL,
uuid CHAR(36) AS (BIN_TO_UUID(id)),
PRIMARY KEY(id)
);
因此，对于表的主键列，可以设计为 BINARY(16) ，用于存储排序后的 16 字节的 UUID 值，在插入时执行如下操作：
INSERT INTO User VALUES (UUID_TO_BIN(UUID(),TRUE),......);
关于 UUID 的性能和存储占用的空间问题，作者也做了相关的插入性能测试，结果如下表所示：
可以看到，MySQL 8.0 提供的排序 UUID 性能最好，甚至比自增ID还要好。此外，由于 UUID_TO_BIN 转换为的结果是16 字节，仅比自增 ID 增加 8 个字节，最后存储占用的空间也仅比自增大了 3G。
而且由于 UUID 能保证全局唯一，因此使用 UUID 的收益远远大于自增ID。可能已经习惯了用自增做主键，但在海量并发的互联网业务场景下，更推荐 UUID 这样的全局唯一值做主键。
比如，特别推荐游戏行业的用户表结构设计，使用 UUID 作为主键，而不是用自增 ID。因为当发生合服操作时，由于 UUID 全局唯一，用户相关数据可直接进行数据的合并，而自增 ID 却需要额外程序整合两个服务器 ID 相同的数据，这个工作是相当巨大且容易出错的。
自定义业务生成主键
当然了，UUID 虽好，但是在分布式数据库场景下，主键还需要加入一些额外的信息，这样才能保证后续二级索引的查询效率。分布式数据库架构，仅用 UUID 做主键依然是不够的。 所以，对于分布式架构的核心业务表，推荐类似如下的设计，比如：
PK = 时间字段 + 随机码（可选） + 业务信息1 + 业务信息2 ......
反范式设计
通常会在 OLAP 数据分析场景中使用反范式设计，但随着 JSON 数据类型的普及，MySQL 在线业务也可以进行反范式的设计。
对于上面的表 UserTag，就是通过 JSON 数据类型进行了反范式的设计，如果通过范式设计，则表 UserTag 应该设计为：
CREATE TABLE UserTag (
userId BIGINT NOT NULL,
userTag INT NOT NULL,
PRIMARY KEY(userId,userTag)
);
SELECT * FROM UserTag;
+--------+---------+
| userId | userTag |
+--------+---------+
| 1 | 2 |
| 1 | 6 |
| 1 | 8 |
| 1 | 10 |
| 2 | 3 |
| 2 | 10 |
| 2 | 12 |
+--------+---------+
对比后可以发现，范式设计并没有使用 JSON 数据类型来得更为有效，使用 JSON 数据类型，userID 只需保存一次，从一定程度上减少了数据的冗余：
+--------+---------------+
| userId | userTags |
+--------+---------------+
| 1 | [2, 6, 8, 10] |
| 2 | [3, 10, 12] |
+--------+---------------
思考题：
除了上面所讲的 UUID 全局唯一实现，业界还有哪些全局唯一的设计实现？
雪花算法比较常用，但是存在时间回溯问题，雪花算法后来也有很多的实现。另外，还可以设计全局 ID 生成器服务，每次要主键时通过服务获取，但是这种方式完全依赖于全局 ID 生成服务，依赖性大，服务一旦宕机，会影响所有相关依赖服务。
UUID 能够实现全局唯一，但它有缺点吗？你认为的缺点是什么？
比如在后期的运维中，看到的是一长串的字符串，而不是一个直接的数字，刚开始使用可能会不适应。
表压缩
数据库中的表是由一行行记录（rows）所组成，每行记录被存储在一个页中，在 MySQL 中，一个页的大小默认为 16K，一个个页又组成了每张表的表空间。
通常认为，如果一个页中存放的记录数越多，数据库的性能越高。这是因为数据库表空间中的页是存放在磁盘上，MySQL 数据库先要将磁盘中的页读取到内存缓冲池，然后以页为单位来读取和管理记录。
一个页中存放的记录越多，内存中能存放的记录数也就越多，那么存取效率也就越高。若想将一个页中存放的记录数变多，可以启用压缩功能。此外，启用压缩后，存储空间占用也变小了，同样单位的存储能存放的数据也变多了。
若要启用压缩技术，数据库可以根据记录、页、表空间进行压缩，不过在实际工程中，普遍使用页压缩技术，这是为什么呢？
压缩每条记录： 因为每次读写都要压缩和解压，过于依赖 CPU 的计算能力，性能会明显下降；另外，因为单条记录大小不会特别大，一般小于 1K，压缩效率也并不会特别好。
压缩表空间： 压缩效率非常不错，但要求表空间文件静态不增长，这对基于磁盘的关系型数据库来说，很难实现。
而基于页的压缩，既能提升压缩效率，又能在性能之间取得一种平衡。
可能很多人认为，启用表的页压缩功能后，性能有明显损失，因为压缩需要有额外的开销。的确，压缩需要消耗额外的 CPU 指令，但是压缩并不意味着性能下降，或许能额外提升性能，因为大部分的数据库业务系统，CPU 的处理能力是剩余的，而 I/O 负载才是数据库主要瓶颈。
借助页压缩技术，MySQL 可以把一个 16K 的页压缩为 8K，甚至 4K，这样在从磁盘写入或读取时，就能将 I/O 请求大小减半，甚至更小，从而提升数据库的整体性能。
当然，压缩是一种平衡，并非一定能提升数据库的性能。这种性能“平衡”取决于解压缩开销带来的收益和解压缩带来的开销之间的一种权衡。但无论如何，压缩都可以有效整理数据原本的容量，对存储空间来说，压缩的收益是巨大的。
MySQL 的表压缩设计
COMPRESS 页压缩
COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。只要在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例。
需要牢记的是， 虽然是通过选项 ROW_FORMAT 启用压缩功能，但这并不是记录级压缩，依然是根据页的维度进行压缩。
下面这是一张日志表，ROW_FROMAT 设置为 COMPRESS，表示启用 COMPRESS 页压缩功能，KEY_BLOCK_SIZE 设置为 8，表示将一个 16K 的页压缩为 8K。
CREATE TABLE Log (
logId BINARY(16) PRIMARY KEY,
......
)
ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8
COMPRESS 页压缩就是将一个页压缩到指定大小。如 16K 的页压缩到 8K，若一个 16K 的页无法压缩到 8K，则会产生 2 个压缩后的 8K 页，具体如下图所示：
总的来说，COMPRESS 页压缩，适合用于一些对性能不敏感的业务表，例如日志表、监控表、告警表等，压缩比例通常能达到 50% 左右。
虽然 COMPRESS 压缩可以有效减小存储空间，但 COMPRESS 页压缩的实现对性能的开销是巨大的，性能会有明显退化。主要原因是一个页的数据在内存缓冲池中，会同时存在压缩和解压两个页。
如图所示，Page1 和 Page2 都是压缩页 8K，但是在内存中还有其解压后的 16K 页。这样设计的原因是 8K 的页用于后续页的更新，16K 的页用于读取，这样读取就不用每次做解压操作了。
很明显，这样的实现会增加对内存的开销，会导致缓存池能存放的有效数据变少，MySQL 数据库的性能自然出现明显退化。
为了 解决压缩性能下降的问题，从MySQL 5.7 版本开始推出了 TPC 压缩功能。
TPC 压缩
TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。可以使用下面的命令创建 TPC 压缩表：
CREATE TABLE Transaction （
transactionId BINARY(16) PRIMARY KEY,
.....
)
COMPRESSION=ZLIB | LZ4 | NONE; -- 有三种不同的算法
要使用 TPC 压缩，首先要确认当前的操作系统是否支持空洞特性。通常来说，当前常见的 Linux 操作系统都已支持空洞特性。
由于空洞是文件系统的一个特性，利用空洞压缩只能压缩到文件系统的最小单位 4K，且其页压缩是 4K 对齐的。比如一个 16K 的页，压缩后为 7K，则实际占用空间 8K；压缩后为 3K，则实际占用空间是 4K；若压缩后是 13K，则占用空间依然为 16K。
TPC 压缩的具体实现如下所示：
上图可以看到，一个 16K 的页压缩后是 8K，接着数据库会对这 16K 的页剩余的 8K 填充 0x00，这样当这个 16K 的页写入到磁盘时，利用文件系统空洞特性，则实际将仅占用 8K 的物理存储空间。
空洞压缩的另一个好处是，它对数据库性能的侵入几乎是无影响的（小于 20%），甚至可能还能有性能的提升。
这是因为不同于 COMPRESS 页压缩，TPC 压缩在内存中只有一个 16K 的解压缩后的页，对于缓冲池没有额外的存储开销。
另一方面，所有页的读写操作都和非压缩页一样，没有开销，只有当这个页需要刷新到磁盘时，才会触发页压缩功能一次。但由于一个 16K 的页被压缩为了 8K 或 4K，其实写入性能会得到一定的提升。
在 MySQL 官方的 LinkBench 测试结果中，无压缩的测试结果为 13,432 QPS，传统的 COMPRESS 页压缩性能下降为 10,480 QPS，差不多 30% 的性能下降。基于TPC压缩的测试结果为 18,882，在未压缩的基础上还能有额外 40% 的性能提升。
表压缩在业务上的使用
总的来说，对一些对性能不敏感的业务表，例如日志表、监控表、告警表等，它们只对存储空间有要求，因此可以使用 COMPRESS 页压缩功能。
在一些较为核心的流水业务表上，更推荐使用 TPC压缩。因为流水信息是一种非常核心的数据存储业务，通常伴随核心业务。如一笔电商交易，用户扣钱、下单、记流水，这就是一个核心业务的微模型。
所以，用户对流水表有性能需求。此外，流水又非常大，启用压缩功能可更为有效地存储数据。
若对压缩产生的性能抖动有所担心，建议是由于流水表通常是按月或天进行存储，对当前正在使用的流水表不要启用 TPC 功能，对已经成为历史的流水表启用 TPC 压缩功能，如下所示：
需要特别注意的是： 通过命令 ALTER TABLE xxx COMPRESSION = ZLIB 可以启用 TPC 页压缩功能，但是这只对后续新增的数据会进行压缩，对于原有的数据则不进行压缩。所以上述 ALTER TABLE 操作只是修改元数据，瞬间就能完成。
若想要对整个表进行压缩，需要执行 OPTIMIZE TABLE 命令：
ALTER TABLE Transaction202102 COMPRESSION=ZLIB；
OPTIMIZE TALBE Transaction202102;
索引调优
B+ 树索引
B+ 树索引的特点是： 基于磁盘的平衡二叉树，但树非常矮，通常为 3~4 层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次 I/O。
又因为现在的固态硬盘每秒能执行至少 10000 次 I/O ，所以查询一条数据，哪怕全部在磁盘上，也只需要 0.003 ~ 0.004 秒。另外，因为 B+ 树矮，在做排序时，也只需要比较 3~4 次就能定位数据需要插入的位置，排序效率非常不错。
所有 B+ 树都是从高度为 1 的树开始，然后根据数据的插入，慢慢增加树的高度。索引是对记录进行排序， 高度为 1 的 B+ 树索引中，存放的记录都已经排序好了，若要在一个叶子节点内再进行查询，只进行二叉查找，就能快速定位数据。
随着插入 B+ 树索引的记录变多，1个页（16K）无法存放这么多数据，所以会发生 B+ 树的分裂，B+ 树的高度变为 2，当 B+ 树的高度大于等于 2 时，根节点和中间节点存放的是索引键对，由（索引键、指针）组成。
索引键就是排序的列，而指针是指向下一层的地址，在 MySQL 的 InnoDB 存储引擎中占用 6 个字节。
那一个高度为 2 的 B+ 树索引，理论上最多能存放多少行记录呢?
在 MySQL InnoDB 存储引擎中，一个页的大小为 16K，假设主键是 BIGINT 类型，则：
根节点能最多存放以下多个键值对 = 16K / 键值对大小(8+6) ≈ 1100
再假设表中每条记录的大小为 500 字节，则：
叶子节点能存放的最多记录为 = 16K / 每条记录大小 ≈ 32
综上所述，树高度为 2 的 B+ 树索引，最多能存放的记录数为：
总记录数 = 1100 * 32 = 35,200
也就是说，35200 条记录排序后，生成的 B+ 树索引高度为 2。在 35200 条记录中根据索引键查询一条记录只需要查询 2 个页，一个根叶，一个叶子节点，就能定位到记录所在的页。
高度为 3 的 B+ 树索引本质上与高度 2 的索引一致，如下图所示：
同理，树高度为 3 的 B+ 树索引，最多能存放的记录数为：
总记录数 = 1100（根节点） * 1100（中间节点） * 32 = 38,720,000
高度为 3 的 B+ 树索引竟然能存放 3800W 条记录。在 3800W 条记录中定位一条记录，只需要查询 3 个页。
不过，在真实环境中，每个页其实利用率并没有这么高，还会存在一些碎片的情况，我们假设每个页的使用率为60%，则：
表格显示了 B+ 树的威力，即在 50 多亿的数据中，根据索引键值查询记录，只需要 4 次 I/O，大概仅需 0.004 秒。如果这些查询的页已经被缓存在内存缓冲池中，查询性能会更快。
另外，对于 B+ 树索引的插入性能，在数据是顺序插入（自增ID，时间列）的情况下，B+ 树的插入通常是比较快的。但是在无序插入（例如用户昵称列创建索引）时，B+ 树为了维护排序，需要对页进行分裂、旋转等开销较大的操作，另外，即便对于固态硬盘，随机写的性能也不如顺序写，所以磁盘性能也会收到较大影响。
在表结构设计时，主键的设计一定要尽可能地使用顺序值，这样才能保证在海量并发业务场景下的性能。
另外，对于网上所说的：“一般一张表的索引不超过 5 个”的说法纯属无稽之谈，如果业务确实需要，就要根据业务进行对应索引的创建，使用时个人觉得指定索引就好了。
思考题
MySQL数据库中，如何在线上业务中尽快发现对应的 SQL 没有合理创建索引呢？
查看 MySQL 的慢查询日志，如设置执行超过 1s 的是慢 SQL，然后优化慢日志中的 SQL。
全文索引、R 树索引在哪些业务中可以使用？
全文索引用于多个维度的查询，类似我们搜索引擎的查询，输入多个条件，然后输出结果。
R 数索引用于地理空间查询。
索引组织表
数据存储有堆表和索引组织表两种方式。堆表中的数据无序存放，数据的排序完全依赖于索引：
从表中可以看出，堆表的组织结构中，数据和索引分开存储。索引是排序后的数据，而堆表中的数据是无序的，索引的叶子节点存放了数据在堆表中的地址，当堆表的数据发生改变，且位置发生了变更，所有索引中的地址都要更新，这非常影响性能，特别是对于 OLTP 业务。
而索引组织表，数据根据主键排序存放在索引中，主键索引也叫聚集索引（Clustered Index）。在索引组织表中，数据即索引，索引即数据。
关于MySQL索引数据结构这里不再赘述。
函数索引
从 MySQL5.7 开始就支持创建函数索引，也就是索引键是一个函数表达式，函数索引有两大用处：
- 优化业务SQL性能；
- 配合虚拟列（Generated Column）。
一般来说，在条件查询的等式左边进行函数运算会导致索引失效。要解决这个问题，就可以使用函数索引，例如假设需要对 register_date 进行函数 DATE_FORMAT 运算，可以创建一个 DATE_FORMAT(register_date) 的索引，这样就能利用排序数据快速定位了：
ALTER TABLE User
ADD INDEX
idx_func_register_date((DATE_FORMAT(register_date,'%Y-%m')));
对于配合虚拟列的使用，例如类型为 JSON 的列通过函数表达式生成一个虚拟列，就可以为其创建一个函数索引，从而对该虚拟列建立索引：
CREATE TABLE UserLogin (
userId BIGINT,
loginInfo JSON,
cellphone VARCHAR(255) AS (loginInfo->>"$.cellphone"),
PRIMARY KEY(userId),
UNIQUE KEY idx_cellphone(cellphone)
);
其中的列 cellphone 就是一个虚拟列，它是由后面的函数表达式计算而成，本身这个列不占用任何的存储空间，而索引 idx_cellphone 实质是一个函数索引。
适用场景：对于爬虫类的业务，会从网上先爬取很多数据，其中有些是我们关心的数据，有些是不关心的数据。通过虚拟列技术，可以展示我们想要的那部分数据，再通过虚拟列上创建索引，就是对爬取的数据进行快速的访问和搜索。
思考题
因为二级索引无需维护，索引组织表在更新的场景下性能明显由于堆表。请问堆表在设计上如何优化，能降低更新操作导致性能的大幅下降？
堆表在设计时，可以让每个页都预留一定的空间，比如 8k 页，预留 4k 空间，当发生更新时，记录可以在这个页中，保持不变，这样就不会每个索引都更新地址了。但是当预留的空间都满了，还是会发生迁移的问题，所以只是缓解问题，而不是像索引组织表那样，完全没有任何问题。
组合索引
利用组合索引包含多个列的特性，可以实现索引覆盖技术避免回表查询，提升 SQL 的查询性能。用好索引覆盖技术，性能提升 10 倍不是难事。
思考题
组合索引可以避免
WHERE a = ? ORDER BY b这条的额外排序问题，在现有的业务中，如何发现可以的 SQL，通过组合索引进行优化呢？这种情况下使用慢查询是比较难发现的，因为有 a 索引的情况下查询并不会特别慢，不一定能够进入慢查询日志。可以通过下面的 sql 语句进行查看：
SELECT query,rows_sorted FROM x$statement_analysis WHERE db NOT IN ('sys','mysql') AND rows_sorted != 0下面这条 SQL 该如何创建组合索引？同时对比 MySQL 5.7 和 MySQL 8.0 版本，查看优化器的执行计划，看看 8.0 在 5.7 版本基础上，还有哪些进一步的优化：
SELECT * FROM orders WHERE o_custkey = 1 ORDER BY o_orderDate DESC,o_orderStatusMySQL 8.0 支持不同方向的索引排序，MySQL 5.7 仅支持一个方向。因此 8.0 可以创建
(o_orderDate DESC,o_orderStatus asc)的索引。而 5.7 即使创建不同排序方向的索引，也需要额外的排序。
索引选择
在关于 MySQL 的面试问题中，看过一个问题是一条 SQL 为什么执行的很慢，其中有一个原因说的就是 SQL 没有走索引查询，原因在于 SQL优化器会有一个判断，当时并没有深究其判断原理。很意外在这个课程里面了解到了其具体的原理。
在关系型数据库中，B+树索引只是存储的一种数据结构，具体怎么使用，还要依赖数据库的优化器，优化器决定了具体某一索引的选择，也就是常说的执行计划。而优化器的选择是基于成本（cost），哪个优化器的成本越低，优先使用哪个索引。SQL 优化器会分析所有可能的执行计划，选择成本最低的执行，这种优化器被称之为：CBO（Cost-based Optimizer，基于成本的优化器）。
而在 MySQL中，一条 SQL 的计算成本计算如下所示：
Cost = Server Cost + Engine Cost
= CPU Cost + IO Cost
其中，CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序……这些操作都在 Server 层完成；
IO Cost 表示引擎层 IO 的开销，MySQL 8.0 可以通过区分一张表的数据是否在内存中，分别计算读取内存 IO 开销以及读取磁盘 IO 的开销。
数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种成本的计算，如：
表 server_cost 记录了 Server 层优化器各种操作的成本，这里面包括了所有 CPU Cost，其具体含义如下。
disk_temptable_create_cost：创建磁盘临时表的成本，默认为20。disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为0.5。key_compare_cost：索引键值比较的成本，默认为0.05，成本最小。memory_temptable_create_cost：创建内存临时表的成本：默认为1。memory_temptable_row_cost：内存临时表中每条记录的成本，默认为0.1。row_evaluate_cost：记录间的比较成本，默认为0.1。
可以看到， MySQL 优化器认为如果一条 SQL 需要创建基于磁盘的临时表，则这时的成本是最大的，其成本是基于内存临时表的 20 倍。而索引键值的比较、记录之间的比较，其实开销是非常低的，但如果要比较的记录数非常多，则从成本会变得非常大。
而表 engine_cost 记录了存储引擎层各种操作的成本，这里包含了所有的 IO Cost，具体含义如下。
io_block_read_cost：从磁盘读取一个页的成本，默认值为 1。memory_block_read_cost：从内存读取一个页的成本，默认值为 0.25。
也就是说， MySQL 优化器认为从磁盘读取的开销是内存开销的 4 倍。
不过，上述所有的成本都是可以修改的，比如如果数据库使用是传统的 HDD 盘，性能较差，其随机读取性能要比内存读取慢 50 倍，那可以通过下面的 SQL 修改成本：
INSERT INTO
engine_cost(engine_name,device_type,cost_name,cost_value,last_update,comment)
VALUES ('InnoDB',0,'io_block_read_cost',12.5,CURRENT_TIMESTAMP,'Using HDD for InnoDB');
FLUSH OPTIMIZER_COSTS;
可以通过命令 EXPLAIN 的 FORMAT=json 来查看各成本的值：
EXPLAIN FORMAT=json
SELECT o_custkey,SUM(o_totalprice)
FROM orders GROUP BY o_custkey
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "626899.50" # 总成本
},
"grouping_operation": {
"using_filesort": false,
"table": {
"table_name": "orders",
"access_type": "index",
"possible_keys": [
"idx_custkey_orderdate",
"ORDERS_FK1",
"idx_custkey_orderdate_totalprice"
],
"key": "idx_custkey_orderdate_totalprice",
"used_key_parts": [
"O_CUSTKEY",
"O_ORDERDATE",
"O_TOTALPRICE"
],
"key_length": "14",
"rows_examined_per_scan": 5778755,
"rows_produced_per_join": 5778755,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "49024.00", # IO Cost(Engine Cost)
"eval_cost": "577875.50", # CPU Cost(Server Cost)
"prefix_cost": "626899.50", # 总成本
"data_read_per_join": "2G" # 总的读取记录字节数
},
"used_columns": [
"O_ORDERKEY",
"O_CUSTKEY",
"O_TOTALPRICE"
]
}
}
}
从第 32 行开始记录了成本的相关信息：
read_cost：表示就是从 InnoDB 存储引擎读取的开销；eval_cost：表示 Server 层的 CPU 成本；prefix_cost：表示这条 SQL 的总成本；data_read_per_join：表示总的读取记录的字节数。
索引选择出错案例分析
案例1:未能使用创建的索引
MySQL 优化器永远是根据成本，选择出最优的执行计划。哪怕是同一条 SQL 语句，只要范围不同，优化器的选择也可能不同。
如下面这两条 SQL：
SELECT * FROM orders
WHERE o_orderdate > '1994-01-01' and o_orderdate < '1994-12-31';
SELECT * FROM orders
WHERE o_orderdate > '1994-02-01' and o_orderdate < '1994-12-31';
上面这两条 SQL 都是通过索引字段 o_orderdate 进行查询，然而第一条 SQL 语句的执行计划并未使用索引 idx_orderdate，而是使用了如下的执行计划：
EXPLAIN SELECT * FROM orders
WHERE o_orderdate > '1994-01-01'
AND o_orderdate < '1994-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: idx_orderdate
key: NULL
key_len: NULL
ref: NULL
rows: 5799601
filtered: 32.35
Extra: Using where
从上述执行计划中可以发现，优化器已经通过 possible_keys 识别出可以使用索引 idx_orderdate，但最终却使用全表扫描的方式取出结果。 最为根本的原因在于：优化器认为使用通过主键进行全表扫描的成本比通过二级索引 idx_orderdate 的成本要低，可以通过 FORMAT=tree 观察得到：
EXPLAIN FORMAT=tree
SELECT * FROM orders
WHERE o_orderdate > '1994-01-01'
AND o_orderdate < '1994-12-31'\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: ((orders.O_ORDERDATE > DATE'1994-01-01') and (orders.O_ORDERDATE < DATE'1994-12-31')) (cost=592267.11 rows=1876082)
-> Table scan on orders (cost=592267.11 rows=5799601)
EXPLAIN FORMAT=tree
SELECT * FROM orders FORCE INDEX(idx_orderdate)
WHERE o_orderdate > '1994-01-01'
AND o_orderdate < '1994-12-31'\G
*************************** 1. row ***************************
EXPLAIN: -> Index range scan on orders using idx_orderdate, with index condition: ((orders.O_ORDERDATE > DATE'1994-01-01') and (orders.O_ORDERDATE < DATE'1994-12-31')) (cost=844351.87 rows=1876082)
可以看到，MySQL 认为全表扫描，然后再通过 WHERE 条件过滤的成本为 592267.11，对比强制使用二级索引 idx_orderdate 的成本为 844351.87。
成本上看，全表扫描低于使用二级索引。故，MySQL 优化器没有使用二级索引 idx_orderdate。
为什么全表扫描比二级索引查询快呢？ 因为二级索引需要回表，当回表的记录数非常大时，成本就会比直接扫描要快，因此这取决于回表的记录数。
所以，第二条 SQL 语句，只是时间范围发生了变化，但是 MySQL 优化器就会自动使用二级索引 idx_orderdate了，这时再观察执行计划：
EXPLAIN SELECT * FROM orders
WHERE o_orderdate > '1994-02-01'
AND o_orderdate < '1994-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: idx_orderdate
key: idx_orderdate
key_len: 3
ref: NULL
rows: 1633884
filtered: 100.00
Extra: Using index condition
再次强调，并不是 MySQL 选择索引出错，而是 MySQL 会根据成本计算得到最优的执行计划， 根据不同条件选择最优执行计划，而不是同一类型一成不变的执行过程，这才是优秀的优化器该有的样子。
案例2：索引创建在有限状态上
B+ 树索引通常要建立在高选择性的字段或字段组合上，如性别、订单 ID、日期等，因为这样每个字段值大多并不相同。
但是对于性别这样的字段，其值只有男和女两种，哪怕记录数再多，也只有两种值，这是低选择性的字段，因此无须在性别字段上创建索引。
但在有些低选择性的列上，是有必要创建索引的。比如电商的核心业务表 orders，其有字段 o_orderstatus，表示当前的状态。
在电商业务中会有一个这样的逻辑：即会定期扫描字段 o_orderstatus 为支付中的订单，然后强制让其关闭，从而释放库存，给其他有需求的买家进行购买。
但字段 o_orderstatus 的状态是有限的，一般仅为已完成、支付中、超时已关闭这几种。
通常订单状态绝大部分都是已完成，只有绝少部分因为系统故障原因，会在 15 分钟后还没有完成订单，因此订单状态是存在数据倾斜的。
这时，虽然订单状态是低选择性的，但是由于其有数据倾斜，且我们只是从索引查询少量数据，因此可以对订单状态创建索引：
ALTER TABLE orders
ADD INDEX idx_orderstatus(o_orderstatus)
但这时根据下面的这条 SQL，优化器的选择可能如下：
EXPLAIN SELECT * FROM orders
WHERE o_orderstatus = 'P'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5799601
filtered: 50.00
Extra: Using where
由于字段 o_orderstatus 仅有三个值，分别为 ‘O’、’P’、’F’。但 MySQL 并不知道这三个列的分布情况，认为这三个值是平均分布的，但其实是这三个值存在严重倾斜：
SELECT o_orderstatus,count(1)
FROM orders GROUP BY o_orderstatus;
+---------------+----------+
| o_orderstatus | count(1) |
+---------------+----------+
| F | 2923619 |
| O | 2923597 |
| P | 152784 |
+---------------+----------+
因此，优化器会认为订单状态为 P 的订单占用 1/3 的数据，使用全表扫描，避免二级索引回表的效率会更高。
然而，由于数据倾斜，订单状态为 P 的数据非常少，根据索引 idx_orderstatus 查询的效率会更高。这种情况下，可以利用 MySQL 8.0 的直方图功能，创建一个直方图，让优化器知道数据的分布，从而更好地选择执行计划。直方图的创建命令如下所示：
ANALYZE TABLE orders
UPDATE HISTOGRAM ON o_orderstatus;
在创建完直方图后，MySQL会收集到字段 o_orderstatus 的数值分布，可以通过下面的命令查询得到：
SELECT
v value,
CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') ratio
FROM information_schema.column_statistics,
JSON_TABLE(histogram->'$.buckets','$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist
WHERE column_name = 'o_orderstatus';
+-------+-------+
| value | ratio |
+-------+-------+
| F | 49% |
| O | 48.5% |
| P | 2.5% |
+-------+-------+
可以看到，现在 MySQL 知道状态为 P 的订单只占 2.5%，因此再去查询状态为 P 的订单时，就会使用到索引 idx_orderstatus了，如：
EXPLAIN SELECT * FROM orders
WHERE o_orderstatus = 'P'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_orderstatus
key: idx_orderstatus
key_len: 4
ref: const
rows: 306212
filtered: 100.00
Extra: Using index condition
此外，对于数据的区分度，MySQL 会进行计算，一个索引的区分度越大，走索引就越有优势，但是对于区分度的计算，MySQL 采用的是获取部分数据进行判断，因此有可能存在误差，统计出来的区分度与实际不符，导致对于 sql 的执行直接走全表扫描。
思考题
字段 o_orderstatus 上创建索引虽然能解决问题，但是成本非常大的，需要维护索引 idx_orderstatus 的各种变更，性能会受到一定影响。有什么其他更好的方法来判断还处在支付过程中的订单吗？
可以通过订单的时间状态来查询订单状态，比如，查询 5 分钟的订单状态，超过 15 分钟的订单就将状态设置为关闭。
JOIN 连接
MySQL 8.0 版本支持两种 JOIN 算法用于表之间的关联：
Nested Loop Join；
Hash Join。
通常认为，在 OLTP 业务中，因为查询数据量较小、语句相对简单，大多使用索引连接表之间的数据。这种情况下，优化器大多会用 Nested Loop Join 算法；而 OLAP 业务中的查询数据量较大，关联表的数量非常多，所以用 Hash Join 算法，直接扫描全表效率会更高。
联机事务处理 OLTP（on-line transaction processing） 主要是执行基本日常的事务处理，通常是数据库记录的增删查改。OLTP的特点一般有：
- 实时性要求高。以前银行异地汇款，要隔天才能到账，而现在是分分钟到账的节奏，说明现在银行的实时处理能力大大增强。
- 数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。
- 交易一般是确定的，比如银行存取款的金额肯定是确定的，所以OLTP是对确定性的数据进行存取
- 高并发，并且要求满足ACID原则。比如两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。
联机分析处理 OLAP（On-Line Analytical Processing） 是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。
OLAP的特点一般有：
- 实时性要求不是很高，比如最常见的应用就是天级更新数据，然后出对应的数据报表。
- 数据量大，因为 OLAP 支持的是动态查询，所以用户也许要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大;
- OLAP 系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在 OLAP 中，维度的概念特别重要。一般会将用户所有关心的维度数据，存入对应数据平台。
Nested Loop Join
Nested Loop Join 之间的表关联是使用索引进行匹配的，假设表 R 和 S 进行连接，其算法伪代码大致如下：
for each row r in R with matching condition:
lookup index idx_s on S where index_key = r
if (found)
send to client
在上述算法中，表 R 被称为驱动表，表 R 中通过 WHERE 条件过滤出的数据会在表 S 对应的索引上进行一一查询。如果驱动表 R 的数据量不大，上述算法非常高效。
对以下三种 JOIN 类型的驱动表进行分析：
SELECT ... FROM R LEFT JOIN S ON R.x = S.x WEHRE ...
SELECT ... FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...
SELECT ... FROM R INNER JOIN S ON R.x = S.x WEHRE ...
对于上述 Left Join 来说，驱动表就是 S；Right Join中，驱动表就是 R。这是 JOIN 类型决定左表或右表的数据一定要进行查询。但对于 INNER JOIN，驱动表可能是表 R，也可能是表 S。
在这种场景下，谁需要查询的数据量越少，谁就是驱动表。 看下面的例子：
SELECT ... FROM R INNER JOIN S
ON R.x = S.x
WHERE R.y = ? AND S.z = ?
上面这条 SQL 语句是对表 R 和表 S 进行 INNER JOIN，其中关联的列是 x，WHERE 过滤条件分别过滤表 R 中的列 y 和表 S 中的列 z。那么这种情况下可以有以下两种选择：
优化器一般认为，通过索引进行查询的效率都一样，所以 Nested Loop Join 算法主要要求驱动表的数量要尽可能少。
所以，如果 WHERE R.y = ? 过滤出的数据少，那么这条 SQL 语句会先使用表 R 上列 y 上的索引，筛选出数据，然后再使用表 S 上列 x 的索引进行关联，最后再通过 WHERE S.z = ? 过滤出最后数据。
为了深入理解优化器驱动表的选择，看下面这条 SQL：
SELECT COUNT(1)
FROM orders
INNER JOIN lineitem
ON orders.o_orderkey = lineitem.l_orderkey
WHERE orders.o_orderdate >= '1994-02-01'
AND orders.o_orderdate < '1994-03-01'
上面的表 orders 类似于电商中的订单表，在示例数据库中记录总量有 600万条记录。
表 lineitem 是订单明细表，比如一个订单可以包含三件商品，这三件商品的具体价格、数量、商品供应商等详细信息，记录数约 2400 万。
上述 SQL 语句表示查询日期为 1994 年 2 月购买的商品数量总和，通过命令 EXPLAIN 查看得到执行计划如下所示：
EXPLAIN: -> Aggregate: count(1)
-> Nested loop inner join (cost=115366.81 rows=549152)
-> Filter: ((orders.O_ORDERDATE >= DATE'1994-02-01') and (orders.O_ORDERDATE < DATE'1994-03-01')) (cost=26837.49 rows=133612)
-> Index range scan on orders using idx_orderdate (cost=26837.49 rows=133612)
-> Index lookup on lineitem using PRIMARY (l_orderkey=orders.o_orderkey) (cost=0.25 rows=4)
上面的执行计划步骤如下，表 orders 是驱动表，它的选择过程如下所示：
Index range scan on orders using idx_orderdate：使用索引 idx_orderdata 过滤出1994 年 2 月的订单数据，预估记录数超过 13 万。
Index lookup on lineitem using PRIMARY：将第一步扫描的结果作为驱动表，然后将驱动表中的每行数据的 o_orderkey 值，在 lineitem 的主键索引中进行查找。
Nested loop inner join：进行 JOIN 连接，匹配得到的输出结果。
Aggregate: count(1)：统计得到最终的商品数量。
但若执行的是下面这条 SQL，则执行计划就有了改变：
EXPLAIN FORMAT=tree
SELECT COUNT(1)
FROM orders
INNER JOIN lineitem
ON orders.o_orderkey = lineitem.l_orderkey
WHERE orders.o_orderdate >= '1994-02-01'
AND orders.o_orderdate < '1994-03-01'
AND lineitem.l_partkey = 620758
EXPLAIN: -> Aggregate: count(1)
-> Nested loop inner join (cost=17.37 rows=2)
-> Index lookup on lineitem using lineitem_fk2 (L_PARTKEY=620758) (cost=4.07 rows=38)
-> Filter: ((orders.O_ORDERDATE >= DATE'1994-02-01') and (orders.O_ORDERDATE < DATE'1994-03-01')) (cost=0.25 rows=0)
-> Single-row index lookup on orders using PRIMARY (o_orderkey=lineitem.l_orderkey) (cost=0.25 rows=1)
上述 SQL 只是新增了一个条件 lineitem.l_partkey =620758，即查询 1994 年 2 月，商品编号为 620758 的商品购买量。
这时若仔细查看执行计划，会发现通过过滤条件 l_partkey = 620758 找到的记录大约只有 38 条，因此这时优化器选择表 lineitem 为驱动表。
Hash Join
MySQL 中的第二种 JOIN 算法是 Hash Join，用于两张表之间连接条件没有索引的情况。
这里可能有人会有疑问，没有连接，那创建索引不就可以了吗？或许可以，但：如果有些列是低选择度的索引，那么创建索引在导入数据时要对数据排序，影响导入性能；另外，二级索引会有回表问题，若筛选的数据量比较大，则直接全表扫描会更快。
对于 OLAP 业务查询来说，Hash Join 是必不可少的功能，MySQL 8.0 版本开始支持 Hash Join 算法，加强了对于 OLAP 业务的支持。
所以，如果查询数据量不是特别大，对于查询的响应时间要求为分钟级别，完全可以使用单个实例 MySQL 8.0 来完成大数据的查询工作。
Hash Join 算法的伪代码如下：
foreach row r in R with matching condition:
create hash table ht on r
foreach row s in S with matching condition:
search s in hash table ht:
if (found)
send to client
Hash Join会扫描关联的两张表：
首先会在扫描驱动表的过程中创建一张哈希表；
接着扫描第二张表时，会在哈希表中搜索每条关联的记录，如果找到就返回记录。
Hash Join 选择驱动表和 Nested Loop Join 算法大致一样，都是较小的表作为驱动表。如果驱动表比较大，创建的哈希表超过了内存的大小，MySQL 会自动把结果转储到磁盘。
为了演示 Hash Join，接下来，再来看一个 SQL：
SELECT
s_acctbal,
s_name,
n_name,
p_partkey,
p_mfgr,
s_address,
s_phone,
s_comment
FROM
part,
supplier,
partsupp,
nation,
region
WHERE
p_partkey = ps_partkey
AND s_suppkey = ps_suppkey
AND p_size = 15
AND p_type LIKE '%BRASS'
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'EUROPE';
上面这条 SQL 语句是要找出商品类型为 %BRASS，尺寸为 15 的欧洲供应商信息。
因为商品表 part 不包含地区信息，所以要从关联表 partsupp 中得到商品供应商信息，然后再从供应商元数据表中得到供应商所在地区信息，最后在外表 region 连接，才能得到最终的结果。
最后的执行计划如下图所示：
从上图可以发现，其实最早进行连接的是表 supplier 和 nation，接着再和表 partsupp 连接，然后和 part 表连接，再和表 part 连接。上述左右连接算法都是 Nested Loop Join。这时的结果集记录大概有 79,330 条记录。
最后和表 region 进行关联，表 region 过滤得到结果5条，这时可以有 2 种选择：
在 73390 条记录上创建基于 region 的索引，然后在内表中通过索引进行查询；
对表 region 创建哈希表，73390 条记录在哈希表中进行探测；
选择 1 就是 MySQL 8.0 不支持 Hash Join 时优化器的处理方式，缺点是：如关联的数据量非常大，创建索引需要时间；其次可能需要回表，优化器大概率会选择直接扫描内表。
选择 2 只对大约 5 条记录的表 region 创建哈希索引，时间几乎可以忽略不计，其次直接选择对内表扫描，没有回表的问题。很明显，MySQL 8.0 会选择 Hash Join。
了解完优化器的选择后，最后看一下命令 EXPLAIN FORMAT=tree 执行计划的最终结果：
-> Nested loop inner join (cost=101423.45 rows=79)
-> Nested loop inner join (cost=92510.52 rows=394)
-> Nested loop inner join (cost=83597.60 rows=394)
-> Inner hash join (no condition) (cost=81341.56 rows=98)
-> Filter: ((part.P_SIZE = 15) and (part.P_TYPE like '%BRASS')) (cost=81340.81 rows=8814)
-> Table scan on part (cost=81340.81 rows=793305)
-> Hash
-> Filter: (region.R_NAME = 'EUROPE') (cost=0.75 rows=1)
-> Table scan on region (cost=0.75 rows=5)
-> Index lookup on partsupp using PRIMARY (ps_partkey=part.p_partkey) (cost=0.25 rows=4)
-> Single-row index lookup on supplier using PRIMARY (s_suppkey=partsupp.PS_SUPPKEY) (cost=0.25 rows=1)
-> Filter: (nation.N_REGIONKEY = region.r_regionkey) (cost=0.25 rows=0)
-> Single-row index lookup on nation using PRIMARY (n_nationkey=supplier.S_NATIONKEY) (cost=0.25 rows=1)
以上就是 MySQL 数据库中 JOIN 的实现原理和应用了。
对于 OLTP 业务能不能写 join 的疑问
OLTP 业务是海量并发，要求响应非常及时，在毫秒级别返回结果，如淘宝的电商业务、支付宝的支付业务、美团的外卖业务等。
如果 OLTP 业务的 JOIN 带有 WHERE 过滤条件，并且是根据主键、索引进行过滤，那么驱动表只有一条或少量记录，这时进行 JOIN 的开销是非常小的。
比如在淘宝的电商业务中，用户要查看自己的订单情况，其本质是在数据库中执行类似如下的 SQL 语句：
SELECT o_custkey, o_orderdate, o_totalprice, p_name FROM orders,lineitem, part
WHERE o_orderkey = l_orderkey
AND l_partkey = p_partkey
AND o_custkey = ?
ORDER BY o_orderdate DESC
LIMIT 30;
以往看到的很多面经讲解都会建议拆分成简单的 sql 来写：
SELECT * FROM orders
WHERE o_custkey = ?
ORDER BY o_orderdate DESC;
SELECT * FROM lineitem
WHERE l_orderkey = ?;
SELECT * FROM part
WHERE p_part = ?
其实完全不用人工拆分语句，因为拆分的过程就是优化器的执行结果，而且优化器更可靠，速度更快，而拆成三条 SQL 的方式，本身网络交互的时间开销就大了 3 倍。
所以，放心写 JOIN，要相信数据库的优化器更为专业。上述 SQL 的执行计划如下：
EXPLAIN: -> Limit: 30 row(s) (cost=27.76 rows=30)
-> Nested loop inner join (cost=27.76 rows=44)
-> Nested loop inner join (cost=12.45 rows=44)
-> Index lookup on orders using idx_custkey_orderdate (O_CUSTKEY=1; iterate backwards) (cost=3.85 rows=11)
-> Index lookup on lineitem using PRIMARY (l_orderkey=orders.o_orderkey) (cost=0.42 rows=4)
-> Single-row index lookup on part using PRIMARY (p_partkey=lineitem.L_PARTKEY) (cost=0.25 rows=1)
由于驱动表的数据是固定 30 条，因此不论表 orders、lineitem、part 的数据量有多大，哪怕是百亿条记录，由于都是通过主键进行关联，上述 SQL 的执行速度几乎不变。
所以，OLTP 业务完全可以大胆放心地写 JOIN，但是要确保 JOIN 的索引都已添加， DBA 们在业务上线之前一定要做 SQL Review，确保预期内的索引都已创建。
思考题：
- 下面这条较复杂的 SQL 查询的内容是什么？
SELECT
supp_nation,
cust_nation,
l_year,
SUM(volume) AS revenue
FROM
(
SELECT
n1.n_name AS supp_nation,
n2.n_name AS cust_nation,
EXTRACT(YEAR FROM l_shipdate) AS l_year,
l_extendedprice * (1 - l_discount) AS volume
FROM
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
WHERE
s_suppkey = l_suppkey
AND o_orderkey = l_orderkey
AND c_custkey = o_custkey
AND s_nationkey = n1.n_nationkey
AND c_nationkey = n2.n_nationkey
AND (
(n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')
OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE')
)
AND l_shipdate BETWEEN DATE '1995-01-01' AND DATE '1996-12-31'
) AS shipping
GROUP BY
supp_nation,
cust_nation,
l_year
ORDER BY
supp_nation,
cust_nation,
l_year;
这条 sql 表示查询 1995年到 1996 年期间供应商是德国或法国，客户是法国或德国，按年供应商、客户、年分组统计销售额，大致结果如下：
查看它的执行计划，比较 MySQL 5.7 与 8.0 版本的不同，并说明它的执行过程。
5.7 由于不支持 Hash Join，因此执行计划使用 Nested Loop Join 算法，MySQL8.0 的执行计划如下：
如何在原有索引的基础上，添加额外的索引，提升查询效率？
使用索引覆盖技术，避免回表带来的性能影响。观察发现表 lineitem 需要大量回表，在表上添加索引，观察 explain 结果，是否出现 using index 提示。sql 大致如下：
alter trable lineitem and index idx_opt(L_SUPPKEY,l_orderkey,l_shipdate,l_extendeprice,l_discount);
子查询
先下结论：MySQL 8.0 版本中，子查询的优化得到大幅提升。所以从现在开始，可以放心大胆地在MySQL 中使用子查询！
通常来讲，开发人员在写 sql 的时候，会更偏向于使用子查询，而不是 Join 语句，因为 JOIN 是一个代数关系，而子查询更偏向于人类的思维角度进行理解。
例如以“找出1993年，没有下过订单的客户数量”为例子，开发人员可能这样写 sql：
SELECT
COUNT(c_custkey) cnt
FROM
customer
WHERE
c_custkey NOT IN (
SELECT
o_custkey
FROM
orders
WHERE
o_orderdate >= '1993-01-01'
AND o_orderdate < '1994-01-01'
);
从中可以看到，子查询的逻辑非常清晰：通过 NOT IN 查询不在订单表的用户有哪些。
不过上述查询是一个典型的 LEFT JOIN 问题（即在表 customer 存在，在表 orders 不存在的问题）。所以，这个问题如果用 LEFT JOIN 写，那么 SQL 如下所示：
SELECT
COUNT(c_custkey) cnt
FROM
customer
LEFT JOIN
orders ON
customer.c_custkey = orders.o_custkey
AND o_orderdate >= '1993-01-01'
AND o_orderdate < '1994-01-01'
WHERE
o_custkey IS NULL;
可以发现，虽然 LEFT JOIN 也能完成上述需求，但不容易理解。所以，大部分人都更倾向写子查询。
不过从优化器的角度看，LEFT JOIN 更易于理解，能进行传统 JOIN 的两表连接，而子查询则要求优化器聪明地将其转换为最优的 JOIN 连接。
在 MySQL 8.0 版本中，针对子查询做了优化，对于上述两条 SQL，不论是子查询还是 LEFT JOIN，最终都被转换成了 Nested Loop Join，所以上述两条 SQL 的执行时间是一样的。也就是说，在 MySQL 8.0 中，优化器会自动地将 IN 子查询优化，优化为最佳的 JOIN 执行计划，这样一来，会显著的提升性能。
子查询 IN 和 EXISTS 的性能对比
对于上面讲的 NOT IN 子查询，可以改写为 NOT EXISTS 子查询，重写后的 SQL 如下所示：
SELECT
COUNT(c_custkey) cnt
FROM
customer
WHERE
NOT EXISTS (
SELECT
1
FROM
orders
WHERE
o_orderdate >= '1993-01-01'
AND o_orderdate < '1994-01-01'
AND c_custkey = o_custkey
);
之前在网上看过，查询用 not in 那么内外表都进行全表扫描，没有用到索引；而 not extsts 的子查询依然能用到表上的索引。not exists 都比 not in 要快。然而实际上，在 MySQL 8.0 版本中，上述 sql 的执行计划是一样的，所以效率其实是一样的。
依赖子查询的优化
在 MySQL 8.0 版本之前，MySQL 对于子查询的优化并不充分。所以在子查询的执行计划中会看到 DEPENDENT SUBQUERY 的提示，这表示是一个依赖子查询，子查询需要依赖外部表的关联。
如果看到这样的提示，就要警惕， 因为 DEPENDENT SUBQUERY 执行速度可能非常慢，大部分时候需要你手动把它转化成两张表之间的连接。
以下面这条 SQL 为例：
SELECT
*
FROM
orders
WHERE
(o_clerk , o_orderdate) IN (
SELECT
o_clerk, MAX(o_orderdate)
FROM
orders
GROUP BY o_clerk);
上述 SQL 语句的子查询部分表示“计算出每个员工最后成交的订单时间”，然后最外层的 SQL表示返回订单的相关信息。
这条 SQL 在最新的 MySQL 8.0 中，通过命令 EXPLAIN FORMAT=tree 输出执行计划，可以看到，在第 3 行会有这样的提示：Select #2 (subquery in condition; run only once)。这表示子查询只执行了一次，然后把最终的结果保存起来了。
执行计划的第 6 行 Index lookup on <materialized_subquery>，表示对表 orders 和子查询结果所得到的表进行 JOIN 连接，最后返回结果。
所以，当前这个执行计划是对表 orders 做 2 次扫描，每次扫描约 5587618 条记录：
第 1 次扫描，用于内部的子查询操作，计算出每个员工最后一次成交的时间；
第 2 次表 oders 扫描，查询并返回每个员工的订单信息，即返回每个员工最后一笔成交的订单信息。
最后，直接用命令 EXPLAIN 查看执行计划，如下图所示：
如果是老版本的 MySQL 数据库，它的执行计划将会是依赖子查询，执行计划如下所示：
对比 MySQL 8.0，只是在第二行的 select_type 这里有所不同，一个是 SUBQUERY，一个是DEPENDENT SUBQUERY。
接着通过命令 EXPLAIN FORMAT=tree 查看更详细的执行计划过程：
可以发现，第 3 行的执行技术输出是：Select #2 (subquery in condition; dependent)，并不像先前的执行计划，提示只执行一次。另外，通过第 1 行也可以发现，这条 SQL 变成了 exists 子查询，每次和子查询进行关联。
所以，上述执行计划其实表示：先查询每个员工的订单信息，接着对每条记录进行内部的子查询进行依赖判断。也就是说，先进行外表扫描，接着做依赖子查询的判断。所以，子查询执行了5587618，而不是1次！！！
所以，两者的执行计划，扫描次数的对比如下所示：
| 表 orders 的扫描次数 | 扫描记录数 | |
|---|---|---|
| 独立子查询 | 1+1 | 5587618+5587618 |
| 依赖子查询 | 1+1*5587618 | 5587618+5587618*5587618 |
对于依赖子查询的优化，就是要避免子查询由于需要对外部的依赖，而需要对子查询扫描多次的情况。所以可以通过派生表的方式，将外表和子查询的派生表进行连接，从而降低对于子查询表的扫描，从而提升 SQL 查询的性能。
那么对于上面的这条 SQL ，可将其重写为：
SELECT * FROM orders o1,
(
SELECT
o_clerk, MAX(o_orderdate)
FROM
orders
GROUP BY o_clerk
) o2
WHERE
o1.o_clerk = o2.o_clerk
AND o1.o_orderdate = o2.orderdate;
可以看到，我们将子查询改写为了派生表 o2，然后将表 o2 与外部表 orders 进行关联。关联的条件是：o1.o_clerk = o2.o_clerk AND o1.o_orderdate = o2.orderdate。
通过上面的重写后，派生表 o2 对表 orders 进行了1次扫描，返回约 5587618 条记录。派生表o1 对表 orders 扫描 1 次，返回约 1792612 条记录。这与 8.0 的执行计划就非常相似了，其执行计划如下所示：
可以看到，经过 SQL 重写后，派生表的执行速度几乎与独立子查询一样。所以，若看到依赖子查询的执行计划，记得先进行 SQL 重写优化。
总结
子查询相比 JOIN 更易于人类理解，所以受众更广，使用更多；
当前 MySQL 8.0 版本可以“毫无顾忌”地写子查询，对于子查询的优化已经相当完备；
对于老版本的 MySQL，请 Review 所有子查询的SQL执行计划， 对于出现 DEPENDENT SUBQUERY 的提示，请务必即使进行优化，否则对业务将造成重大的性能影响；
DEPENDENT SUBQUERY 的优化，一般是重写为派生表进行表连接。
思考题
下面的 SQL 大概表示什么样的业务逻辑？
SELECT o_orderpriority, COUNT(*) AS order_count FROM orders WHERE o_orderdate >= DATE '1993-07-01' AND o_orderdate < DATE '1993-07-01' + INTERVAL '3' MONTH AND EXISTS ( SELECT * FROM lineitem WHERE l_orderkey = o_orderkey AND l_commitdate < l_receiptdate ) GROUP BY o_orderpriority ORDER BY o_orderpriority;查询 1993 年第三季度中，根据发货优先级分组，未达承诺交付时间的订单数量，结果大致如下：
查看通过命令 EXPLAIN 查看执行计划，说出 MySQL 8.0 的执行过程。
执行
SET @@optimizer_switch='semijoin=off'; 接着再通过命令 EXPLAIN 查看执行计划，说出执行的过程。
分区表
分区表就是把物理表结构相同的几张表，通过一定算法，组成一张逻辑大表。这种算法叫“分区函数”，当前 MySQL 数据库支持的分区函数类型有 RANGE、LIST、HASH、KEY、COLUMNS。
分区表的使用
无论选择哪种分区函数，都要指定相关列成为分区算法的输入条件，这些列就叫“分区列”。另外，在 MySQL 分区表中，主键也必须是分区列的一部分，不然创建分区表时会失败，比如：
CREATE TABLE t (
a INT,
b INT,
c DATETIME(6),
d VARCHAR(32),
e INT,
PRIMARY KEY (a,b)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
上面创建了表 t，主键是复合索引，由列 a、b 组成。表 t 创建分区表的意图是根据列 c（时间列）拆分数据，把不同时间数据存放到不同分区中。
而可以从错误的提示中看到：分区表的主键一定要包含分区函数的列。所以，要创建基于列 c 的数据分片的分区表，主键必须包含列 c，比如下面的建表语句：
CREATE TABLE t (
a INT,
b INT,
c DATETIME,
d VARCHAR(32),
e INT,
PRIMARY KEY (a,b,c),
KEY idx_e (e)
)
partition by range columns(c) (
PARTITION p0000 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p2020 VALUES LESS THAN ('2021-01-01'),
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
);
创建完表后，在物理存储上会看到四个分区所对应 ibd 文件，也就是把数据根据时间列 c 存储到对应的 4 个文件中：
t#p#p0000.ibd t#p#p2019.ibd t#p#p2020.ibd t#p#p9999.ibd
要理解的是：MySQL 中的分区表是把一张大表拆成了多张表，每张表有自己的索引，从逻辑上看是一张表，但物理上存储在不同文件中。
分区表的唯一索引
在 MySQL 数据库中，分区表的索引都是局部，而非全局。也就是说，索引在每个分区文件中都是独立的，所以分区表上的唯一索引必须包含分区列信息，否则创建会报错，比如：
ALTER TABLE t ADD UNIQUE KEY idx_d(d);
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function (prefixed columns are not considered).
可以看到错误提示： 唯一索引必须包含分区函数中所有列。而下面的创建才能成功：
ALTER TABLE t ADD UNIQUE KEY idx_d(d,c);
但是，正因为唯一索引包含了分区列，唯一索引也就变成仅在当前分区唯一，而不是全局唯一了。那么对于上面的表 t，插入下面这两条记录都是可以的：
INSERT INTO t VALUES
(1,1,'2021-01-01','aaa',1),
(1,1,'2020-01-01','aaa',1);
SELECT * FROM t;
+---+---+---------------------+------+------+
| a | b | c | d | e |
+---+---+---------------------+------+------+
| 1 | 1 | 2020-01-01 00:00:00 |aaa | 1 |
| 1 | 1 | 2021-01-01 00:00:00 |aaa | 1 |
+---+---+---------------------+------+------+
可以看到，列 d 都是字符串 ‘aaa’，但依然可以插入。这样带来的影响是列 d 并不是唯一的，所以要由当前分区唯一实现全局唯一。
那如何实现全局唯一索引呢？ 和之前表结构设计时一样，唯一索引使用全局唯一的字符串（如类似 UUID 的实现），这样就能避免局部唯一的问题。
表分区与性能
表分区并不能提升性能，分区表技术不是用于提升 MySQL 数据库的性能，而是方便数据的管理。
B+ 树的高度与数据存储量的关系如下：
从表格中可以看到，B+ 树的高度为 4 能存放数十亿的数据，一次查询只需要占用 4 次 I/O，速度非常快。
但是当使用分区之后，效果就不一样了，比如上面的表 t，我们根据时间拆成每年一张表，这时，虽然 B+ 树的高度从 4 降为了 3，但是这个提升微乎其微。
除此之外，分区表还会引入新的性能问题，比如非分区列的查询。非分区列的查询，即使分区列上已经创建了索引，但因为索引是每个分区文件对应的本地索引，所以要查询每个分区。
观察这条 SQL 以及它的执行计划：
SELECT * FROM t WHERE d = 'aaa'
******** 1. row ********
id: 1
select_type: SIMPLE
table: t
partitions: p0000,p2019,p2020,p9999
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where
通过执行计划可以看到：上述 SQL 需要访问 4 个分区，假设每个分区需要 3 次 I/O，则这条 SQL 总共要 12 次 I/O。但是，如果使用普通表，记录数再多，也就 4 次的 I/O 的时间。
所以，分区表设计时，务必明白你的查询条件都带有分区字段，否则会扫描所有分区的数据或索引。所以，分区表设计不解决性能问题，更多的是解决数据迁移和备份的问题。
分区表在业务上的设计
以电商中的订单表 Orders 为例，如果在类似淘宝的海量互联网业务中，Orders 表的数据量会非常巨大，假设一天产生 5000 万的订单，那么一年表 Orders 就有近 18 亿的记录。
所以对于订单表，在数据库中通常只保存最近一年甚至更短时间的数据，而历史订单数据会入历史库。除非存在 1 年以上退款的订单，大部分订单一旦完成，这些数据从业务角度就没用了。
那么如果想方便管理订单表中的数据，可以对表 Orders 按年创建分区表，如：
CREATE TABLE `orders` (
`o_orderkey` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_CLERK` char(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`o_orderkey`,`O_ORDERDATE`),
KEY `orders_fk1` (`O_CUSTKEY`),
KEY `idx_orderdate` (`O_ORDERDATE`)
)
PARTITION BY RANGE COLUMNS(o_orderdate)
(
PARTITION p0000 VALUES LESS THAN ('1992-01-01') ENGINE = InnoDB,
PARTITION p1992 VALUES LESS THAN ('1993-01-01') ENGINE = InnoDB,
PARTITION p1993 VALUES LESS THAN ('1994-01-01') ENGINE = InnoDB,
PARTITION p1994 VALUES LESS THAN ('1995-01-01') ENGINE = InnoDB,
PARTITION p1995 VALUES LESS THAN ('1996-01-01') ENGINE = InnoDB,
PARTITION p1996 VALUES LESS THAN ('1997-01-01') ENGINE = InnoDB,
PARTITION p1997 VALUES LESS THAN ('1998-01-01') ENGINE = InnoDB,
PARTITION p1998 VALUES LESS THAN ('1999-01-01') ENGINE = InnoDB,
PARTITION p9999 VALUES LESS THAN (MAXVALUE)
）
可以看到，这时 Orders 表的主键修改为了 (o_orderkey,O_ORDERDATE)，数据按照年进行分区存储。那么如果要删除 1 年前的数据，比如删除 1998 年的数据，之前需要使用下面的 SQL，比如:
DELETE FROM Orders
WHERE o_orderdate >= '1998-01-01'
AND o_orderdate < '1999-01-01'
可这条 SQL 的执行相当慢，产生大量二进制日志，在生产系统上，也会导致数据库主从延迟的问题。而使用分区表的话，对于数据的管理就容易多了，直接使用清空分区的命令就行：
ALTER TABLE orders_par
TRUNCATE PARTITION p1998
上述 SQL 执行速度非常快，因为实际执行过程是把分区文件删除和重建。另外产生的日志也只有一条 DDL 日志，也不会导致主从复制延迟问题。
思考题
除了订单表，还有那些业务适合使用分区表技术呢？
- 日志、监控等非核心数据
对于电商中的订单表，经常需要根据用户查询购买记录，这时会使用如下 SQL。如果把表 Orders 设计成按年分区，这时的执行计划如何？存在什么样的问题？如何优化呢？
SELECT * FROM orders_par WHERE o_custkey = 1 ORDER BY o_orderdate DESC limit 30o_custkey不是分区键，会扫描所有分区，可以自己建一张表，用于索引表功能，如idx_orders_par_custkey(o_custkey,o_orderkey,o_orderdate)，查询时先查询索引表，再通过分区表进行查询，由于有o_oerder_date分区键，反查时只需要查询指定分区。这里其实优化的思路就是人为的实现二级索引的回表。
高可用结构篇
MySQL 复制
复制架构
数据库复制本质上就是数据同步。MySQL 数据库是基于二进制日志（binary log）进行数据增量同步，而二进制日志记录了所有对于 MySQL 数据库的修改操作。
在默认 ROW 格式二进制日志中，一条 SQL 操作影响的记录会被全部记录下来，比如一条 SQL语句更新了三行记录，在二进制日志中会记录被修改的这三条记录的前项（before image）和后项（after image）。
对于 INSERT 或 DELETE 操作，则会记录这条被插入或删除记录所有列的信息，看一个例子：
DELETE FROM orders_test
WHERE o_orderdate = '1997-12-31';
Query OK, 2482 rows affected (0.07 sec)
可以看到，上面这条 SQL 执行的是删除操作，一共删除了有 2482 行记录。可以在 mysql 命令行下使用命令 SHOW BINLOG EVENTS 查看某个二进制日志文件的内容，比如上述删除操作发生在二进制日志文件 binlog.000004 中，可以看到：
通过 MySQL 数据库自带的命令 mysqlbinlog，可以解析二进制日志，观察到更为详细的每条记录的信息，比如：
可以看到，通过二进制日志记录看到被删除的每条记录的完整信息，还有每个列的属性，比如列的类型，是否允许为 NULL 值等。
如果是 UPDATE 操作，二进制日志中还记录了被修改记录完整的前项和后项，比如：
在有二进制日志的基础上，MySQL 数据库就可以通过数据复制技术实现数据同步了。而数据复制的本质就是把一台 MySQL 数据库上的变更同步到另一台 MySQL 数据库上。
另外，MySQL 可以根据需要构建级联的拓扑结构：
在上图中，Slave1、Slave2、Slave3 都是 Master 的从服务器，而 Slave11 是 Slave1 的从服务器，Slave1 服务器既是 Master 的从机，又是 Slave11 的主机，所以 Slave1 是个级联的从机。同理，Slave3 也是台级联的从机。
MySQL 复制配置
搭建 MySQL 复制实现非常简单，基本步骤如下：
创建复制所需的账号和权限；
从 Master 服务器拷贝一份数据，可以使用逻辑备份工具 mysqldump、mysqlpump，或物理备份工具 Clone Plugin；
通过命令 CHANGE MASTER TO 搭建复制关系；
通过命令 SHOW SLAVE STATUS 观察复制状态。
虽然 MySQL 复制原理和实施非常简单，但在配置时却容易出错，请务必在配置文件中设置如下配置：
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
relay_log_recovery = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
上述设置都是用于保证 crash safe，即无论 Master 还是 Slave 宕机，当它们恢复后，连上主机后，主从数据依然一致，不会产生任何不一致的问题。
MySQL 复制类型
MySQL 复制可以分为以下几种类型：
默认的复制是异步复制。
异步复制
在异步复制（async replication）中，Master 不用关心 Slave 是否接收到二进制日志，所以 Master 与 Slave 没有任何的依赖关系。你可以认为 Master 和 Slave 是分别独自工作的两台服务器，数据最终会通过二进制日志达到一致。
异步复制的性能最好，因为它对数据库本身几乎没有任何开销，除非主从延迟非常大，Dump Thread 需要读取大量二进制日志文件。
如果业务对于数据一致性要求不高，当发生故障时，能容忍数据的丢失，甚至大量的丢失，推荐用异步复制，这样性能最好（比如像微博这样的业务，虽然它对性能的要求极高，但对于数据丢失，通常可以容忍）。但往往核心业务系统最关心的就是数据安全，比如监控业务、告警系统。
半同步复制
半同步复制要求 Master 事务提交过程中，至少有 N 个 Slave 接收到二进制日志，这样就能保证当 Master 发生宕机，至少有 N 台 Slave 服务器中的数据是完整的。
半同步复制并不是 MySQL 内置的功能，而是要安装半同步插件，并启用半同步复制功能，设置 N 个 Slave 接受二进制日志成功，比如：
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl-semi-sync-master-enabled = 1
rpl-semi-sync-slave-enabled = 1
rpl_semi_sync_master_wait_no_slave = 1
上面的配置中：
第 1 行要求数据库启动时安装半同步插件；
第 2、3 行表示分别启用半同步 Master 和半同步 Slave 插件；
第 4 行表示半同步复制过程中，提交的事务必须至少有一个 Slave 接收到二进制日志。
在半同步复制中，有损半同步复制是 MySQL 5.7 版本前的半同步复制机制，这种半同步复制在Master 发生宕机时，Slave 会丢失最后一批提交的数据，若这时 Slave 提升（Failover）为Master，可能会发生已经提交的事情不见了，发生了回滚的情况。
有损半同步复制原理如下图所示：
可以看到，有损半同步是在 Master 事务提交后，即步骤 4 后，等待 Slave 返回 ACK，表示至少有 Slave 接收到了二进制日志，如果这时二进制日志还未发送到 Slave，Master 就发生宕机，则此时 Slave 就会丢失 Master 已经提交的数据。
而 MySQL 5.7 的无损半同步复制解决了这个问题，其原理如下图所示：
从上图可以看到，无损半同步复制 WAIT ACK 发生在事务提交之前，这样即便 Slave 没有收到二进制日志，但是 Master 宕机了，由于最后一个事务还没有提交，所以本身这个数据对外也不可见，不存在丢失的问题。
所以，对于任何有数据一致性要求的业务，如电商的核心订单业务、银行、保险、证券等与资金密切相关的业务，务必使用无损半同步复制。这样数据才是安全的、有保障的、即使发生宕机，从机也有一份完整的数据。
多源复制
无论是异步复制还是半同步复制，都是 1 个 Master 对应 N 个 Slave。其实 MySQL 也支持 N 个 Master 对应 1 个 Slave，这种架构就称之为多源复制。
多源复制允许在不同 MySQL 实例上的数据同步到 1 台 MySQL 实例上，方便在 1 台 Slave 服务器上进行一些统计查询，如常见的 OLAP 业务查询。
多源复制的架构如下所示：
上图显示了订单库、库存库、供应商库，通过多源复制同步到了一台 MySQL 实例上，接着就可以通过 MySQL 8.0 提供的复杂 SQL 能力，对业务进行深度的数据分析和挖掘。
延迟复制
前面介绍的复制架构，Slave 在接收二进制日志后会尽可能快地回放日志，这样是为了避免主从之间出现延迟。而延迟复制却允许Slave 延迟回放接收到的二进制日志，为了避免主服务器上的误操作，马上又同步到了从服务器，导致数据完全丢失。
可以通过以下命令设置延迟复制：
CHANGE MASTER TO master_delay = 3600
这样就人为设置了 Slave 落后 Master 服务器1个小时。
延迟复制在数据库的备份架构设计中非常常见，比如可以设置一个延迟一天的延迟备机，这样本质上说，用户可以有 1 份 24 小时前的快照。
那么当线上发生误操作，如 DROP TABLE、DROP DATABASE 这样灾难性的命令时，用户有一个 24 小时前的快照，数据可以快速恢复。
对金融行业来说，延迟复制是备份设计中，必须考虑的一个架构部分。
思考题
若有 N 个 Slave，Master 上是有 1 个 Dump 线程还是 N 个 Dump 线程呢？
N 个 dump 线程
二进制日志能完整记录一条记录，但它的缺点是什么呢？
由于二进制日志记录一整行记录的信息， UPDATE 时还会记录前项和后项，以及保存会话变量信息，因此占用存储空间较大。
MySQL 读写分离设计
逻辑日志的优缺点
MySQL 复制基于的二进制日志是一种逻辑日志，其写入的是每个事务中已变更的每条记录的前项、后项。
有了每条记录的变化内容，用户可以方便地通过分析 MySQL 的二进制日志内容，准时地将 MySQL 中的数据同步到异构的数据平台，如 HBase、ES、Hive 等大数据平台。
可以发现，逻辑日志简单易懂，方便数据之间的同步，但它的缺点是：事务不能太大，否则会导致二进制日志非常大，一个大事务的提交会非常慢。
假设有个 DELETE 删除操作，删除当月数据，由于数据量可能有 1 亿条记录，可能会产生 100G 的二进制日志，则这条 SQL 在提交时需要等待 100G 的二进制日志写入磁盘，如果二进制日志磁盘每秒写入速度为 100M/秒，至少要等待 1000 秒才能完成这个事务的提交。
所以在 MySQL 中，一定要对大事务特别对待， 总结起来就是：
设计时，把 DELETE 删除操作转化为 DROP TABLE/PARTITION 操作；
业务设计时，把大事务拆成小事务。
对于第一点（把 DELETE 删除操作转化为 DROP TABLE/PARTITION 操作），主要是在设计时把流水或日志类的表按时间分表或者分区，这样在删除时，二进制日志内容就是一条 DROP TABLE/PARITION 的 SQL，写入速度就非常快了。
而第二点（把大事务拆分成小事务）也能控制二进制日志的大小。比如对于前面的 DELETE 操作，如果设计时没有分表或分区，那么可以进行如下面的小事务拆分：
DELETE FROM ...
WHEREE time between ... and ...
LIMIT 1000;
上面的 SQL 就是把一个大的 DELETE 操作拆分成了每次删除 1000 条记录的小操作。而小事务的另一个优势是：可以进行多线程的并发操作，进一步提升删除效率。
MySQL 数据库中，大事务除了会导致提交速度变慢，还会导致主从复制延迟。
试想一下，一个大事务在主服务器上运行了 30 分钟，那么在从服务器上也需要运行 30 分钟。在从机回放这个大事务的过程中，主从服务器之间的数据就产生了延迟；产生大事务的另一种可能性是主服务上没有创建索引，导致一个简单的操作时间变得非常长。这样在从机回放时，也会需要很长的时间从而导致主从的复制延迟。
除了把大事务拆分成小事务，可以避免主从复制延迟，还可以设置复制回放相关的配置参数。
主从复制延迟优化
要牢记：要彻底避免 MySQL 主从复制延迟，数据库版本至少要升级到 5.7，因为之前的 MySQL 版本从机回放二进制都是单线程的（5.6 是基于库级别的单线程）。
从 MySQL 5.7 版本开始，MySQL 支持了从机多线程回放二进制日志的方式，通常把它叫作“并行复制”，官方文档中称为“Multi-Threaded Slave（MTS）”。
MySQL 的从机并行复制有两种模式。
COMMIT ORDER： 主机怎么并行，从机就怎么并行。
WRITESET： 基于每个事务，只要事务更新的记录不冲突，就可以并行。
COMMIT ORDER 模式的从机并行复制，从机完全根据主服务的并行度进行回放。理论上来说，主从延迟极小。但如果主服务器上并行度非常小，事务并不小，比如单线程每次插入 1000 条记录，则从机单线程回放，也会存在一些复制延迟的情况。
而 WRITESET 模式是基于每个事务并行，如果事务间更新的记录不冲突，就可以并行。还是以“单线程每次插入 1000 条记录”为例，如果插入的记录没有冲突，比如唯一索引冲突，那么虽然主机是单线程，但从机可以是多线程并行回放！！！
所以在 WRITESET 模式下，主从复制几乎没有延迟。要启用 WRITESET 复制模式，需要做这样的配置：
binlog_transaction_dependency_tracking = WRITESET
transaction_write_set_extraction = XXHASH64
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 16
因为主从复制延迟会影响到后续高可用的切换，以及读写分离的架构设计，所以在真实的业务中，要对主从复制延迟进行监控。
主从延迟监控
Seconds_Behind_Master
通过命令 SHOW SLAVE STATUS，其中的 Seconds_Behind_Master 可以查看复制延迟，如：
但是，Seconds_Behind_Master 不准确！用于严格判断主从延迟的问题并不合适， 有这样三个原因。
它计算规则是（当前回放二进制时间 - 二进制日志中的时间），如果 I/O 线程有延迟，那么
Second_Behind_Master为 0，这时可能已经落后非常多了，例如存在有大事务的情况下；对于级联复制，最下游的从服务器延迟是不准确的，因为它只表示和上一级主服务器之间的延迟；
若主从时区不一样，那么
second_behind_master也不准确；
总的来说，线上业务通过 Seconds_Begind_Master 值观察主从复制延迟并不准确，需要额外引入一张表，才能真正监控主从的复制延迟情况。
心跳表
想要实时准确地监控主从复制延迟，可以在主服务器上引入一张心跳表 heartbeat，用于定期更新时间（比如每 3 秒一次）。于主从复制机制，主机上写入的时间会被复制到从机，这时对于主从复制延迟的判断可以根据如下规则：
主从延迟 = 从机当前时间 - 表 heartbeat 中的时间
这可以很好解决上述 Seconds_Behind_Master 值存在的问题。表 heartbeat 和定期更新时间可以根据类似的设计：
USE DBA;
CREATE TABLE heartbeat (
server-uuid VARCHAR(36) PRIMARY KEY,
ts TIMESTAMP(6) NOT NULL
);
REPLACE INTO heartbeat(@@server_uuid, NOW())
上面的设计中，创建了DBA库，以及库下的一张表 heartbeat，用于记录当前时间。
REPLACE 语句用于定期更新当前时间，并存入到表 heartbeat，表 heartbeat 在正常运行情况下只有一条记录。定期执行 REPLACE 语句可以使用定期的脚本调度程序，也可以使用 MySQL自带的事件调度器（event scheduler），如：
CREATE EVENT e_heartbeat
ON SCHEDULE
EVERY 3 SECOND
DO
BEGIN
REPLACE INTO DBA.heartbeat VALUES (@@server_uuid,NOW())
END
读写分离设计
读写分离设计的前提是从机不能落后主机很多，最好是能准实时数据同步，务必一定要开启并行复制，并确保线上已经将大事务拆成小事务。
当然，若是一些报表类的查询，只要不影响最终结果，业务是能够容忍一些延迟的。但无论如何，请一定要在线上数据库环境中做好主从复制延迟的监控。
如果真的由于一些不可预知的情况发生，比如一个初级 DBA 在主机上做了一个大事务操作，导致主从延迟发生，那么怎么做好读写分离设计的兜底呢？
在 Load Balance 服务器，可以配置较小比例的读取请求访问主机，如上图所示的 1%，其余三台从服务器各自承担 33% 的读取请求。
如果发生严重的主从复制情况，可以设置下面从机权重为 0，将主机权重设置为 100%，这样就不会因为数据延迟，导致对于业务的影响了。
思考题
读写分离设计后，主服务器就完全没有读取请求了吗？
UPDATE,DELETE 操作还是需要读取的，先读取再修改。另外，对于一些实时性要求非常高的数据，业务还是需要读取主服务器。
还有一种主从延迟问题的处理，调用函数
WAIT_UNTIL_SQL_THREAD_AFTER_GITDS，请问这个设计的原理如何？是一种好的设计吗？要求从机已经回放到指定的 GTID，这样表示数据已经同步，没有主从延迟。这种设计对于业务侵入性较大，不是一种通用的设计。如果担心主从延迟，要么就彻底解决主从复制，但如果 DBA 把控能力不够，又或者改造需要时间，这不失为一种读写分离避免延迟的一种方法。
MySQL 高可用设计
业界度量高可用能力有一个统一的标准：判断宕机时间，并以此计算出每年系统可用时间达到几个 9，来判断高可用架构是否健壮。具体如下表所示：
通常来说，系统至少要达到 4 个 9（99.99%），也就是每年宕机时间不超过 52.56 分钟，否则用户体验会非常差，感觉系统不稳定。
不过 4 个 9 宕机 52 分钟对于生产环境的影响还是比较大，但是 5 个 9 对大部分系统来说要求又太高。所以一些云服务商会提出一个 99.995% 的可用性概念，那么系统一年的不可用时长为：
不可用时长 = (1 - 99.995%)*365*24*60 = 26.28 (分钟)
即一年最多的影响服务的时间为 26.28 分钟。
高可用架构设计
系统要达到高可用，一定要做好软硬件的冗余，消除单点故障（SPOF single point of failure）。
冗余是高可用的基础，通常认为，系统投入硬件资源越多，冗余也就越多，系统可用性也就越高。
除了做好冗余，系统还要做好故障转移（Failover）的处理。也就是在最短的时间内发现故障，然后把业务切换到冗余的资源上。
在明确上述高可用设计的基本概念后之后，看一下高可用架构设计的类型，主要分为两种：
- 无状态服务高可用设计
- 数据库高可用架构设计
无状态服务高可用设计
无状态的服务（如 Nginx ）高可用设计非常简单，发现问题直接转移就行，甚至可以通过负载均衡服务，当发现有问题，直接剔除，对于上层用户来说，他只会在几秒内的访问出现问题，之后服务就立刻恢复了。对于无状态的服务来说，高可用设计相对简单。
数据库高可用架构设计
所以，系统高可用设计，真正的难点、痛点不在于无状态服务的设计，而在于数据库的高可用设计，这是因为：
数据持久化在数据库中，是有状态的服务；
数据库的容量比较大，Failover 的时间相对无状态服务会更多；
一些系统，如金融场景的数据库，会要求数据完全不能丢失，这又增加了高可用实现的难度。
其实从架构角度看，数据库高可用本身也是业务高可用，所以要从业务全流程的角度出发，思考数据库的高可用设计。
作者在这里提供了三种数据库的高可用架构设计方法，它们不但适用于 MySQL 数据库，也适用于其他数据库。
基于数据层的数据库高可用架构
基于数据层的数据库高可用架构，就是基于数据同步技术。当主服务器 Master 发生宕机，则故障转移到从服务器 Slave。
对于 MySQL 数据库来说，就是基于前面介绍的复制技术。对于上面的读写分离架构，如果主服务器发生宕机，做如下操作就行了：
可以发现，原先的 Slave3 从服务器提升为了新主机，然后建立了新的复制拓扑架构，Slave2、Slave3 都连到新 Master 进行数据同步。
为了在故障转移后对 Service 服务无感知，所以需要引入 VIP（Virtual IP）虚拟 IP 技术，当发生宕机时，VIP 也需要漂移到新的主服务器。
那么这个架构的真正难点在于：
如何保障数据一致性；
如何发现主服务器宕机；
故障转移逻辑的处理；
可以通过 MySQL 提供的无损复制技术，来保障“数据一致性”。而“发现主服务器宕机”“处理故障转移逻辑”要由数据库高可用套件完成(后面进行描述)
基于业务层的数据库高可用架构
第二种“基于业务层的数据库高可用架构设计”则完全基于业务实现，数据库只是用于存储数据。
当一台数据库主服务器不可用，业务直接写另一台数据库主服务器就可以了。这个架构如下：
从上图可以看到，Service 服务写入 Master1 主服务器失败后，不用等待故障转移程序启用主从切换，而是直接把数据写入 Master2 主服务器。
这看似是一种非常简单、粗暴的高可用架构实现方式，但能符合这样设计的业务却并不多，因为该设计前提是状态可修改。
比如电商中的订单服务，其基本逻辑就是存储电商业务中每笔订单信息，核心逻辑就是往表 Orders 中插入数据，即：
INSERT INTO Orders(o_orderkey, ... ) VALUES (...)
这里 o_orderkey 是主键。为了实现基于业务层的数据库高可用，可以在主键生成过程中加入额外信息，比如服务器编号，这样订单的主键设计变为了：
PK = 有序UUID-服务器编号
这样的话，当写入服务器编号 1 时失败了，业务层会把订单的主键修改为服务器编号 2，这样就实现了业务层的高可用，电商中的这种订单号生成方式也称为“跳单”。
而当查询订单信息时，由于主键中包含了服务器编号，那么业务知道该笔订单存储在哪台服务器，就可以非常快速地路由到指定的服务器。
但这样设计的前提是整个服务的写入主键是可以进行跳单设计，且查询全部依赖主键进行搜索。
融合的高可用架构设计
刚刚“基于业务层的数据库高可用架构”中，虽然通过跳单设计，可以实现写入业务的高可用实现。但这时订单服务的查询功能会受到极大影响。在上面的例子中，当发生宕机时，服务器编号为 1 的订单无法查询。
所以，作者给出了另一种业务和数据层相结合的高可用设计。这个架构可以解决宕机后，查询服务受限的问题。其架构图如下所示：
上图中，将不同编号的订单根据不同的数据库进行存放，比如服务器编号为 1 的订单存放在数据库 DB1 中，服务器编号为 2 的订单存放在数据库 DB2 中。
此外，这里也用到了 MySQL 复制中的部分复制技术，即左上角的主服务器仅将 DB1 中的数据同步到右上角的服务器。同理，右上角的主服务器仅将 DB2 中的数据同步到左上角的服务器。下面的两台从服务器不变，依然从原来的 MySQL 实例中同步数据。
这样做得好处是：
在常态情况下，上面两台 MySQL 数据库是双活的，都可以有数据的写入，业务的性能得到极大提升。
订单数据是完整的，服务器编号为 1 和 2 的数据都在一个 MySQL 实例上。
更重要的是，这样当发生宕机时，Service 服务的写入不受到影响，写入服务器编号为 1 的订单通过跳单设计写入 DB2。同时，对于订单读取也不会受到影响，因为数据都是一个实例上。
思考题
针对本讲所学习的数据库三大高可用架构设计，请讲述它们的优缺点。
金融级高可用架构
对有状态的数据库服务来说，在一些核心业务系统中，比如电商、金融等，还要保证数据一致性。
这里的“数据一致性”是指在任何灾难场景下，一条数据都不允许丢失（一般也把这种数据复制方式叫作“强同步”）。
复制类型的选择
在银行、保险、证券等核心业务，需要严格保障数据一致性。那么要想实现数据的强同步，在进行复制的配置时，就要使用无损半同步复制模式。
在 MySQL 内部就是要把参数 rpl_semi_sync_master_wait_point 设置成 AFTER_SYNC 。
但是在高可用设计时，当数据库 FAILOVER 完后，有时还要对原来的主机做额外的操作，这样才能保证主从数据的完全一致性。
来看这样一张图：
从图中可以看到，即使启用无损半同步复制，依然存在当发生主机宕机时，最后一组事务没有上传到从机的可能。图中宕机的主机已经提交事务到 101，但是从机只接收到事务 100。如果这个时候 Failover，从机提升为主机，那么这时：
可以看到当主从切换完成后，新的 MySQL 开始写入新的事务 102，如果这时老的主服务器从宕机中恢复，则这时事务 101 不会同步到新主服务器，导致主从数据不一致。
但设置 AFTER_SYNC 无损半同步的好处是，虽然事务 101 在原主机已经提交，但是在从机没有收到并返回 ACK 前，这个事务对用户是不可见的，所以，用户感受不到事务已经提交了。
所以，在做高可用设计时，当老主机恢复时，需要做一次额外的处理，把事务 101 给“回滚”。
这里只要记住，设计数据强一致的高可用方案时，要选择无损半同步复制，另外在发生宕机 FAILOVER 后，若老主机恢复，还需要额外处理老主机上已提交但还未发送到从机的数据。
容灾级别
高可用用于处理各种宕机问题，而宕机可以分成服务器宕机、机房级宕机，甚至是一个城市发生宕机。
机房级宕机： 机房光纤不通/被挖断，机房整体掉电（双路备用电源也不可用）；
城市级宕机： 一般指整个城市的进出口网络，骨干交换机发生的故障（这种情况发生的概率很小）。
如果综合考虑的话，高可用就成了一种容灾处理机制，对应的高可用架构的评判标准就上升了。
机房内容灾： 机房内某台数据库服务器不可用，切换到同机房的数据库实例，保障业务连续性；
同城容灾： 机房不可用，切换到同城机房的数据库实例，保障业务连续性；
跨城容灾： 单个城市机房都不可用，切换到跨城机房的数据库实例，保障业务连续性。
前面谈到的高可用设计，都只是机房内的容灾。也就是说，主服务器和从服务器都在一个机房内，现在来看一下同城和跨城的容灾设计（不论是机房内容灾、同城容灾，还是跨城容灾，都是基于 MySQL 的无损半同步复制，只是物理部署方式不同，解决不同的问题）。
对于同城容灾，有很多这样的设计：
这种设计没有考虑到机房网络的抖动。如果机房 1 和机房 2 之间的网络发生抖动，那么因为事务提交需要机房 2 中的从服务器接收日志，所以会出现事务提交被 hang 住的问题。
而机房网络抖动非常常见，所以核心业务同城容灾务要采用三园区的架构，如下图所示：
该架构称为“三园区的架构”，如果三个机房都在一个城市，则称为“ 一地三中心”，如果在相邻两个城市，那么就叫“两地三中心”。但这种同城/近城容灾，要求机房网络之间的延迟不超过 5ms。
在三园区架构中，一份数据被存放在了 3 个机房，机房之间根据半同步复制。这里将 MySQL 的半同步复制参数 rpl_semi_sync_master_wait_for_slave_count 设置为 1，表示只要有 1 个半同步备机接收到日志，主服务器上的事务就可以提交。
这样的设计，保证除主机房外，数据在其他机房至少一份完整的数据。
另外，即便机房 1 与机房 2 发生网络抖动，因为机房 1 与机房 3 之间的网络很好，不会影响事务在主服务器上的提交。如果机房 1 的出口交换机或光纤发生故障，那么这时高可用套件会 FAILOVER 到机房 2 或机房 3，因为至少有一份数据是完整的。
机房 2、机房 3 的数据用于保障数据一致性，但是如果要实现读写分离，或备份，还需要引入异步复制的备机节点。所以整体架构调整为：
从图中可以看到，加入两个异步复制的节点，用于业务实现读写分离，另外再从机房 3 的备机中，引入一个异步复制的延迟备机，用于做数据误删除操作的恢复。
当设计成类似上述的架构时，才能认为自己的同城容灾架构是合格的！
另一个重要的点：因为机房 1 中的主服务器要向四个从服务器发送日志，这时网卡有成为瓶颈的可能，所以请务必配置万兆网卡。
在明白三园区架构后，要实现跨城容灾也就非常简单了， 只要把三个机房放在不同城市就行。但这样的设计，当主服务器发生宕机时，数据库就会切到跨城，而跨城之间的网络延迟超过了 25 ms。所以，跨城容灾一般设计成“三地五中心”的架构，如下图所示：
在上图中：机房 1、机房 2 在城市 1 中；机房 3、机房 4 在城市 2 中；机房 5 在城市 3 中，三个城市之间的距离超过 200 公里，延迟超过 25ms。
由于有五个机房，所以 ACK 设置为 2，保证至少一份数据在两个机房有数据。这样当发生城市级故障，则城市 2 或城市 3 中，至少有一份完整的数据。
在真实的互联网业务场景中，“三地五中心”应用并不像“三园区”那样普遍。这是因为 25ms 的延迟对业务的影响非常大，一般这种架构应用于读多写少的场景，比如用户中心。
另外，真实的互联网业务场景中，实现跨城容灾，一般基于同城容灾架构，然后再由业务层来保障跨城的数据一致性。
兜底策略：数据核对
到目前为止，前面的高可用是基于 MySQL 的复制技术。但这可能出现这样几个问题：
万一数据库的复制有 Bug 呢？导致最终的数据在逻辑上不一致呢？
主从的数据一定一致吗？你如何判断一定一致呢？
所以，除了高可用的容灾架构设计，还要做一层兜底服务，用于判断数据的一致性。这里要引入数据核对，用来解决以下两方面的问题。
数据在业务逻辑上一致： 这个保障业务是对的；
主从服务器之间的数据一致： 这个保障从服务器的数据是安全的、可切的。
业务逻辑核对由业务的同学负责编写， 从整个业务逻辑调度看账平不平。例如“今天库存的消耗”是否等于“订单明细表中的总和”，“在途快递” + “已收快递”是否等于“已下快递总和”。总之，这是个业务逻辑，用于对账。
主从服务器之间的核对，是由数据库团队负责的。 需要额外写一个主从核对服务，用于保障主从数据的一致性。这个核对不依赖复制本身，也是一种逻辑核对。思路是：将最近一段时间内主服务器上变更过的记录与从服务器核对，从逻辑上验证是否一致。
那么现在的难题是：如何判断最近一段时间内主服务器上变更过的记录？这里有两种思路：
表结构设计规范中，每张表有一个
last_modify_date，用于记录每条记录的最后修改时间，按照这个条件过滤就能查出最近更新的记录，然后每条记录比较即可。核对服务扫描最近的二进制日志，筛选出最近更新过记录的表和主键，然后核对数据。这种的实现难度会更大一些，但是不要求在数据库上进行查询。
如果在核对过程中，记录又在主上发生了变化，但是还没有同步到从机，可以加入复核逻辑，按理来说多复核几次，主从数据应该就一致了。如果复核多次不一致，那么大概率，主从数据就已经是不一致的了。
思考题
对于跨城容灾，有什么优化技术可以减少耗时增大带来的性能影响呢？
MySQL 相关的一些高可用套件
前面介绍了 MySQL 数据库的高可用解决方案，并且学习了怎么根据金融业务的要求，通过无损半同步复制的方式进行三园区的同城容灾设计，以及三地务中心的跨城容灾设计。但是当数据库发生宕机时，MySQL 的主从复制并不会自动地切换，这需要高可用套件对数据库主从进行管理。
高可用套件所需的透明切换机制
MySQL 的高可用套件用于负责数据库的 Failover 操作，也就是当数据库发生宕机时，MySQL 可以剔除原有主机，选出新的主机，然后对外提供服务，保证业务的连续性。
可以看到，MySQL 复制是高可用的技术基础，用于将数据实时同步到从机。高可用套件是 MySQL 高可用实现的解决方案，负责切换新主机。
为了不让业务感知到数据库的宕机切换，这里要用到 VIP（Virtual IP）技术。其中，VIP 不是真实的物理 IP，而是可以随意绑定在任何一台服务器上。
业务访问数据库，不是服务器上与网卡绑定的物理 IP，而是这台服务器上的 VIP。当数据库服务器发生宕机时，高可用套件会把 VIP 插拔到新的服务器上。数据库 Failover后，业务依旧访问的还是 VIP，所以使用 VIP 可以做到对业务透明。
从上图可以看到，MySQL 的主服务器的 IP 地址是 192.168.1.10，两个从服务器的 IP 地址分别为 192.168.1.20、192.168.1.30。
上层服务访问数据库并没有直接通过物理 IP 192.168.1.10，而是访问 VIP，地址为192.168.1.100。这时，如果 MySQL 数据库主服务器发生宕机，则请求会变成这样子：
可以看到，当发生 Failover 后，由于上层服务访问的是 VIP 192.168.1.100，所以切换对服务来说是透明的，只是在切换过程中，服务会收到连接数据库失败的提示。但是通过重试机制，当下层数据库完成切换后，服务就可以继续使用了。所以，上层服务一定要做好错误重试的逻辑，否则就算启用 VIP，也无法实现透明的切换。
但是 VIP 也是有局限性的，仅限于同机房同网段的 IP 设定。如果是之前设计的三园区同城跨机房容灾架构，VIP 就不可用了。这时就要用名字服务，常见的名字服务就是 DNS（Domain Name Service），如下所示：
从上图可以看到，这里将域名m1.insidemysql.com 对应的 IP 指向为了 192.168.1.10，上层业务通过域名进行访问。当发生宕机，将会进行机房的切换，会把 DNS 指向的域名切换到机房2：
虽然使用域名或其他名字服务可以解决跨机房的切换问题，但是引入了新的组件。新组件的高可用的问题也需要特别注意。在架构设计时，请咨询公司提供名字服务的小组，和他们一起设计高可用的容灾架构。
几款常用的高可用套件
MHA
MHA(Master High Availability)是一款开源的 MySQL 高可用程序，它为 MySQL 数据库主从复制架构提供了 automating master failover 的功能。
MHA 是由业界大名鼎鼎的 Facebook 工程师 Yoshinorim 开发，开源地址为：https://github.com/yoshinorim/mha4mysql-manager。它由两大组件所组成，MHA Manger 和 MHA Node：
MHA Manager 通常部署在一台服务器上，用来判断多个 MySQL 高可用组是否可用。当发现有主服务器发生宕机，就发起 failover 操作。MHA Manger 可以看作是 failover 的总控服务器。
而 MHA Node 部署在每台 MySQL 服务器上，MHA Manager 通过执行 Node 节点的脚本完成 failover 切换操作。
MHA Manager 和 MHA Node 的通信是采用 ssh 的方式，也就是需要在生产环境中打通 MHA Manager 到所有 MySQL 节点的 ssh 策略，那么这里就存在潜在的安全风险。
另外，ssh 通信，效率也不是特别高。所以，MHA 比较适合用于规模不是特别大的公司，所有MySQL 数据库的服务器数量不超过 20 台。
Orchestrator
Orchestrator 是另一款开源的 MySQL 高可用套件，除了支持 failover 的切换，还可通过 Orchestrator 完成 MySQL 数据库的一些简单的复制管理操作。Orchestrator 的开源地址为：https://github.com/openark/orchestrator
你可以把 Orchestrator 当成 MHA 的升级版，而且提供了 HTTP 接口来进行相关数据库的操作，比起 MHA 需要每次登录 MHA Manager 服务器来说，方便很多。
下图显示了 Orchestrator 的高可用设计架构：
其基本实现原理与 MHA 是一样的，只是把元数据信息存储在了元数据库中，并且提供了 HTTP 接口和命令的访问方式，使用上更为友好。
但是由于管控节点到下面的 MySQL 数据库的管理依然是 ssh 的方式，依然存在 MHA 一样的短板问题，总的来说，关于 Orchestrator 依然只建议使用在较小规模的数据库集群。
数据库管理平台
虽然 MHA 和 Orchestrator 都可以完成 MySQL 高可用的 failover 操作，但是，在生产环境中如果需要管理成千乃至上万的数据库服务器，由于它们的通信仅采用 ssh 的方式，并不能满足生产上的安全性和性能的要求。
所以，几乎每家互联网公司都会自研一个数据库的管理平台，用于管理公司所有的数据库集群，以及数据库的容灾切换工作。
下图显示了数据库管理平台大致的实现框架：
上图中的数据库管理平台是用户操作数据库的入口。对数据库的大部分操作，比如数据库的初始化、数据查询、数据备份等操作、后续都能在这个平台完成，不用登录数据库服务器，这样的好处是能大大提升数据库操作的效率。
数据库管理平台提供了 HTTP API 的方式，可用前后端分离的方式支持 Web、手机等多种访问方式。
元数据库用于存储管理 MySQL 数据库所有的节点信息，比如 IP 地址、端口、域名等。
数据库管理平台 Manager 用来实际控制下面的所有 MySQL 节点，Manager 和后端 MySQL 的通信通过 MySQL 服务器上部署的 agent 方式进行。两者通过 BP 协议以 grpc 的方式通信。这样解决了 ssh 的不安全性以及性能。
其中，agent 用来上报数据库各节点的状态给 Manager，管理节点 Manager 通过上报的信息判断数据库是否宕机，是否需要进行切换，切换到哪个节点。
上图的设计，能完成一个比较基本的数据库管理平台。另外，每个公司有自己的一些需求，也可以做到数据库管理平台中，比如安全要求、审计需求、工单系统等。
所以，有了数据库管理平台，数据库的高可用切换、数据库日常管理和访问，都可以由平台自动完成。有了数据库管理平台，才能真正实现数据库管理的无人驾驶。
思考题
上图切换完成后，还存在什么缺陷？如何进行架构设计，避免此问题呢？
InnoDB Cluster
前面介绍的所有切换判断都是通过一组外部的心跳检查机制完成，这依赖于高可用套件自身的能力，如果高可用套件本身不可靠，就意味着高可用的不可靠性。比如，当数据库真的发生宕机时，数据库是否一定能切换成功呢？
最后，数据库复制技术的瓶颈在于：只能在一个节点完成写入，然后再将日志同步各个节点，这样单点写入会导致数据库性能无法进行扩展。那么能不能有一种技术，能实现 MySQL 多个节点写入，并且保证数据同步的能力呢？
有的，这就是 InnoDB Cluster，它的底层是由 MySQL Group Replication（下面简称MGR）实现。
MGR技术
MGR 是官方在 MySQL 5.7 版本推出的一种基于状态机的数据同步机制。与半同步插件类似，MGR 是通过插件的方式启用或禁用此功能。
注意，对于 MGR，不要简单认为它是一种新的数据同步技术，而是应该把它理解为高可用解决方案，而且特别适合应用于对于数据一致性要求极高的金融级业务场景。
首先，MGR 之间的数据同步并没有采用复制技术，而是采用 GCS（Group Communication System）协议的日志同步技术。
GSC 本身是一种类似 Paxos 算法的协议，要求组中的大部分节点都接收到日志，事务才能提交。所以，MRG 是严格要求数据一致的，特别适合用于金融级的环境。由于是类 Paxos 算法，集群的节点要求数量是奇数个，这样才能满足大多数的要求。
那之前介绍的无损半同步也能保证数据强一致的要求吗？
是的，虽然通过无损半同步复制也能保证主从数据的一致性，但通过 GCS 进行数据同步有着更好的性能：当启用 MGR 插件时，MySQL 会新开启一个端口用于数据的同步，而不是如复制一样使用 MySQL 服务端口，这样会大大提升复制的效率。
其次，MGR 有两种模式：
单主（Single Primary）模式；
多主（Multi Primary）模式。
单主模式只有 1 个节点可以写入，多主模式能让每个节点都可以写入。而多个节点之间写入，如果存在变更同一行的冲突，MySQL 会自动回滚其中一个事务，自动保证数据在多个节点之间的完整性和一致性。
最后，在单主模式下，MGR 可以自动进行 Failover 切换，不用依赖外部的各种高可用套件，所有的事情都由数据库自己完成，比如最复杂的选主（Primary Election）逻辑，都是由 MGR 自己完成，用户不用部署额外的 Agent 等组件。
说了这么多 MGR 的优势，那么它有没有缺点或限制呢？ 当然有，主要是这样几点：
仅支持 InnoDB 表，并且每张表一定要有一个主键；
目前一个 MGR 集群，最多只支持 9 个节点；
有一个节点网络出现抖动或不稳定，会影响集群的性能。
第 1、2 点问题不大，因为目前用 MySQL 主流的就是使用 InnoDB 存储引擎，9 个节点也足够用了。
而第 3 点需要注意，和复制不一样的是，由于 MGR 使用的是 Paxos 协议，对于网络极其敏感，如果其中一个节点网络变慢，则会影响整个集群性能。而半同步复制，比如 ACK 为1，则 1 个节点网络出现问题，不影响整个集群的性能。所以，在决定使用 MGR 后，切记一定要严格保障网络的质量。
而多主模式是一种全新的数据同步模式，接下来看看在多主模式时，该做哪些架构上的调整，从而充分发挥 MGR 多主的优势。
多主模式的注意事项
冲突检测
MGR 多主模式是近几年数据库领域最大的一种创新，而且目前来看，仅 MySQL 支持这种多写的 Share Nothing 架构。
多主模式要求每个事务在本节点提交时，还要去验证其他节点是否有同样的记录也正在被修改。如果有的话，其中一个事务要被回滚。
比如两个节点同时执行下面的 SQL 语句：
-- 节点1
UPDATE User set money = money - 100 WHERE id = 1;
-- 节点2
UPDATE User set money = money + 300 WHERE id = 1;
如果一开始用户的余额为 200，当节点 1 执行 SQL 后，用户余额变为 100，当节点 2 执行SQL，用户余额变味了 500，这样就导致了节点数据的不同。所以 MGR 多主模式会在事务提交时，进行行记录冲突检测，发现冲突，就会对事务进行回滚。
在上面的例子中，若节点 2 上的事务先提交，则节点 1 提交时会失败，事务会进行回滚。
所以，如果要发挥多主模式的优势，就要避免写入时有冲突。最好的做法是：每个节点写各自的数据库，比如节点 1 写 DB1，节点 2 写 DB2，节点 3 写 DB3，这样集群的写入性能就能线性提升了。
不过这要求在架构设计时，就做好这样的考虑，否则多主不一定能带来预期中的性能提升。
自增处理
在多主模式下，自增的逻辑发生了很大的变化。简单来说，自增不再连续自增。
因为，如果连续自增，这要求每次写入时要等待自增值在多个节点中的分配，这样性能会大幅下降，所以 MGR 多主模式下，我们可以通过设置自增起始值和步长来解决自增的性能问题。看下面的参数：
group_replication_auto_increment_increment = 7
假设 MGR 有 3 个节点 Node1、Node2、Node3，对应的 server-id 分别是 1、2、3, 如果这时多主插入自增的顺序为 Node1、Node1、Node2、Node3、Node1，则自增值产生的结果为：
可以看到，由于是多主模式，允许多个节点并发的产生自增值。所以自增的产生结果为1、8、16、17、22，自增值不一定是严格连续的，而仅仅是单调递增的，这与单实例 MySQL 有着很大的不同。
在前面已经说过，尽量不要使用自增值做主键，在 MGR 存在问题，在后续分布式架构中也一样存在类似的自增问题。所以，对于核心业务表，还是使用有序 UUID 的方式更为可靠，性能也会更好。
总之，使用 MGR 技术后，所有高可用事情都由数据库自动完成。那么，业务该如何利用 MGR的能力，是否还需要 VIP、DNS 等机制保证业务的透明性呢？接下来看一下，业务如何利用 MGR 的特性构建高可用解决方案。
InnoDB Cluster
MGR 是基于 Paxos 算法的数据同步机制，将数据库状态和日志通过 Paxos 算法同步到各个节点，但如果要实现一个完整的数据库高可用解决方案，就需要更高一层级的 InnoDB Cluster 完成。
一个 InnoDB Cluster 由三个组件组成：MGR 集群、MySQL Shell、MySQL Router。具体如下图所示：
其中，MySQL Shell 用来管理 MGR 集群的创建、变更等操作。以后我们最好不要手动去管理 MGR 集群，而是通过 MySQL Shell 封装的各种接口完成 MGR 的各种操作。如：
mysql-js> cluster.status()
{
"clusterName": "myCluster",
"defaultReplicaSet": {
"name": "default",
"primary": "ic-2:3306",
"ssl": "REQUIRED",
"status": "OK",
"statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
"topology": {
"ic-1:3306": {
"address": "ic-1:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"ic-2:3306": {
"address": "ic-2:3306",
"mode": "R/W",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
},
"ic-3:3306": {
"address": "ic-3:3306",
"mode": "R/O",
"readReplicas": {},
"role": "HA",
"status": "ONLINE"
}
}
},
"groupInformationSourceMember": "mysql://root@localhost:6446"
}
MySQL Router 是一个轻量级的代理，用于业务访问 MGR 集群中的数据，当 MGR 发生切换时（这里指 Single Primary 模式），自动路由到新的 MGR 主节点，这样业务就不用感知下层MGR 数据的切换。
为了减少引入 MySQL Router 带来的性能影响，官方建议 MySQL Router 与客户端程序部署在一起，以一种类似 sidecar 的方式进行物理部署。这样能减少额外一次额外的网络开销，基本消除引入 MySQL Router 带来的影响。
所以，这里 MySQL Router 的定位是一种轻量级的路由转发，而不是一个数据库中间件，主要解决数据库切换后，做到对业务无感知。
思考题
请问 2 个节点的 MGR 集群能工作吗？
数据库备份
除了高可用设计外，对架构师来说，还要做好备份架构的设计。因为我们要防范意外情况的发生，比如黑客删除了数据库中所有的核心数据；又或者某个员工有意也罢、无意也好，删除了线上的数据。
数据库备份
复制技术（Replication）或 InnoDB Cluster 只负责业务的可用性，保障数据安全除了线上的副本数据库，我们还要构建一个完整的离线备份体系。这样即使线上数据库被全部破坏，用户也可以从离线备份恢复出数据。
所以，第一步要做好：线上数据库与离线备份系统的权限隔离。
也就是说，可以访问线上数据库权限的同学一定不能访问离线备份系统，反之亦然。否则，如果两边的数据都遭受破坏，依然无法恢复数据。
而对于 MySQL 数据库来说，数据库备份分为全量备份、增量备份。
全量备份
指备份当前时间点数据库中的所有数据，根据备份内容的不同，全量备份可以分为逻辑备份、物理备份两种方式。
逻辑备份
指备份数据库的逻辑内容，就是每张表中的内容通过 INSERT 语句的形式进行备份。
MySQL 官方提供的逻辑备份工具有 mysqldump 和 mysqlpump。通过 mysqldump 进行备份，可以使用以下 SQL 语句：
mysqldump -A --single-transaction > backup.sql
上面的命令就是通过 mysqldump 进行全量的逻辑备份：
- 参数
-A表示备份所有数据库； - 参数
--single-transaction表示进行一致性的备份。
特别强调，参数 --single-transaction 是必须加的参数，否则备份文件的内容不一致，这样的备份几乎没有意义。
如果总忘记参数 --single-transaction，可以在 MySQL 的配置文件中加上如下提示：
# my.cnf
[mysqldump]
single-transaction
按上面配置，每当在服务器上运行命令时 mysqldump 就会自动加上参数 --single-transaction，也就不会再忘记了。
在上面的命令中，最终的备份文件名为 backup.sql，打开这个文件，会看到类似的内容：
-- MySQL dump 10.13 Distrib 8.0.23, for Linux (x86_64)
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 8.0.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50606 SET @OLD_INNODB_STATS_AUTO_RECALC=@@INNODB_STATS_AUTO_RECALC */;
/*!50606 SET GLOBAL INNODB_STATS_AUTO_RECALC=OFF */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `mysql`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mysql`;
...
可以看到，文件 backup.sql 本质就是一个文本文件，里面记录的就是一条条 SQL 语句，而这就是逻辑备份。要恢复逻辑备份非常简单，就是执行文件中的 SQL 语句，这时可以使用下面的 SQL：
mysql < backup.sql
虽然 mysqldump 简单易用，但因为它备份是单线程进行的，所以速度会比较慢，于是 MySQL 推出了 mysqlpump 工具。
命令 mysqlpump 的使用几乎与 mysqldump 一模一样，唯一不同的是它可以设置备份的线程数，如：
mysqlpump -A --single-transaction --default-parallelism=8 > backup.sql
Dump progress: 1/1 tables, 0/0 rows
Dump progress: 25/37 tables, 881632/42965650 rows
Dump progress: 25/37 tables, 1683132/42965650 rows
......
上面的命令显示了通过 mysqlpump 进行备份。参数 --default-parallelism 表示设置备份的并行线程数。此外，与 mysqldump 不同的是，mysqlpump 在备份过程中可以查看备份的进度。
不过在真正的线上生产环境中，并不推荐使用 mysqlpump， 因为当备份并发线程数超过 1 时，它不能构建一个一致性的备份。见 mysqlpump 的提示：
另外，mysqlpump 的备份多线程是基于多个表的并行备份，如果数据库中存在一个超级大表，那么对于这个表的备份依然还是单线程的。那么有没有一种基于记录级别的并行备份，且支持一致性的逻辑备份工具呢？
有的，那就是开源的 mydumper 工具，地址：https://github.com/maxbube/mydumper。mydumper 的强大之处在于：
- 支持一致性的备份；
- 可以根据表中的记录进行分片，从而进行多线程的备份；
- 对于恢复操作，也可以是多线程的备份；
- 可以指定单个表进行多线程的恢复。
可以看到，mydumper 几乎是一个完美的逻辑备份工具，是构建备份系统的首选工具。这里提供一个简单的 mydumper 的使用方法：
mydumper -o /bak -r 100000 --trx-consistency-only -t 8
上面的命令表示，将备份文件保存到目录 /bak 下，其中：
- 参数
-r表示每张表导出 100000 条记录后保存到一张表； - 参数
--trx-consistency-only表示一致性备份； - 参数
-t表示 8 个线程并行备份。
可以看到，即便对于一张大表，也可以以 8 个线程，按照每次 10000 条记录的方式进行备份，这样大大提升了备份的性能。
物理备份
当然，逻辑备份虽然好，但是它所需要的时间比较长，因为本质上逻辑备份就是进行 INSERT ... SELECT ... 的操作。
而物理备份直接备份数据库的物理表空间文件和重做日志，不用通过逻辑的 SELECT 取出数据。所以物理备份的速度，通常是比逻辑备份快的，恢复速度也比较快。
但它不如 mydumper 的是，物理备份只能恢复整个实例的数据，而不能按指定表进行恢复。MySQL 8.0 的物理备份工具可以选择官方的 Clone Plugin。
Clone Plugin 是 MySQL 8.0.17 版本推出的物理备份工具插件，在安装完插件后，就可以对MySQL 进行物理备份了。而我们要使用 Clone Plugin 就要先安装 Clone Plugin 插件，推荐在配置文件中进行如下设置：
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT
这时进行物理备份可以通过如下命令：
mysql> CLONE LOCAL DATA DIRECTORY = '/path/to/clone_dir';
可以看到，在 mysql 命令行下输入 clone 命令，就可以进行本地实例的 MySQL 物理备份了。
Clone Plugin 插件强大之处还在于其可以进行远程的物理备份，命令如下所示：
CLONE INSTANCE FROM 'user'@'host':port
IDENTIFIED BY 'password'
[DATA DIRECTORY [=] 'clone_dir']
[REQUIRE [NO] SSL];
从上面的命令可以看到，Clone Plugin 支持指定的用户名密码，备份远程的物理备份到当前服务器上，根据 Clone Plugin 可以非常容易地构建备份系统。
对于 MySQL 8.0 之前的版本，可以使用第三方开源工具 Xtrabackup，官方网址：https://github.com/percona/percona-xtrabackup。
不过，物理备份实现机制较逻辑备份复制很多，需要深入了解 MySQL 数据库内核的实现，强烈建议使用 MySQL 官方的物理备份工具，开源第三方物理备份工具只作为一些场景的辅助手段。
增量备份
前面所讲的逻辑备份、物理备份都是全量备份，也就是对整个数据库进行备份。然而，数据库中的数据不断变化，不可能每时每分对数据库进行增量的备份。
所以，需要通过“全量备份 + 增量备份”的方式，构建完整的备份策略。增量备份就是对日志文件进行备份，在 MySQL 数据库中就是二进制日志文件。
因为二进制日志保存了对数据库所有变更的修改，所以“全量备份 + 增量备份”，就可以实现基于时间点的恢复（point in time recovery），也就是“通过全量 + 增量备份”可以恢复到任意时间点。
全量备份时会记录这个备份对应的时间点位，一般是某个 GTID 位置，增量备份可以在这个点位后重放日志，这样就能实现基于时间点的恢复。
如果二进制日志存在一些删库的操作，可以跳过这些点，然后接着重放后续二进制日志，这样就能对极端删库场景进行灾难恢复了。
想要准实时地增量备份 MySQL 的二进制日志，可以使用下面的命令：
mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000001
可以看到，增量备份就是使用之前了解的 mysqlbinlog，但这次额外加上了参数 --read-from-remote-server，表示可以从远程某个 MySQL 上拉取二进制日志，这个远程 MySQL 就是由参数 --host 指定。
参数 --raw 表示根据二进制的方式进行拉取，参数 --stop-never 表示永远不要停止，即一直拉取一直保存，参数 binlog.000001 表示从这个文件开始拉取。
MySQL 增量备份的本质是通过 mysqlbinlog 模拟一个 slave 从服务器，然后主服务器不断将二进制日志推送给从服务器，利用之前介绍的复制技术，实现数据库的增量备份。
增量备份的恢复，就是通过 mysqlbinlog 解析二进制日志，然后进行恢复，如：
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
备份策略
在掌握全量备份、增量备份的知识点后，就能构建自己的备份策略了。
首先，要设置全量备份的频率，因为全量备份比较大，所以建议设置 1 周 1 次全量备份，实时增量备份的频率。这样最坏的情况就是要恢复 7 天前的一个全备，然后通过 7 天的增量备份恢复。
对于备份文件，也需要进行备份。不能认为备份文件的存储介质不会损坏。所以，至少在 2 个机房的不同存储服务器上存储备份文件，即备份文件至少需要 2 个副本。至于备份文件的保存期限，取决于每个公司自己的要求（比如有的公司要求永久保存，有的公司要求保留至少近 3 个月的备份文件）。
所有的这些备份策略，都需要自己的备份系统进行调度，这个并没有什么特别好的开源项目，需要根据自己的业务需求，定制开发。
备份文件的检查
备份系统非常关键，并不亚于线上的高可用系统。
前面说到线上主从复制的高可用架构，还需要进行主从之间的数据核对，用来确保数据是真实一致的。
同样，对于备份文件，也需要进行校验，才能确保备份文件的正确的，当真的发生灾难时，可通过备份文件进行恢复。因此，备份系统还需要一个备份文件的校验功能。
牢记，只有当核对是 OK 的，才能证明你的备份文件是安全的。所以备份文件同样要检查。
思考题
“全备+增量备份”的策略虽然很好，但在极端场景恢复所需的时间比较长，如何进一步提升恢复的速度呢？
分布式架构篇
分布式数据库架构
分布式数据库的概念
Wiki 官方对分布式数据库的定义为：
A distributed database is a database in which data is stored across different physical locations. It may be stored in multiple computers located in the same physical location (e.g. a data centre); or maybe dispersed over a network of interconnected computers.
从定义来看，分布式数据库是一种把数据分散存储在不同物理位置的数据库。
对比之前学习的数据库，数据都是存放在一个实例对应的物理存储上，而在分布式数据库中，数据将存放在不同的数据库实例上。
从图中可以看到，在分布式数据库下，分布式数据库本身分为计算层、元数据层和存储层：
- 计算层就是之前单机数据库中的 SQL 层，用来对数据访问进行权限检查、路由访问，以及对计算结果等操作。
- 元数据层记录了分布式数据库集群下有多少个存储节点，对应 IP、端口等元数据信息是多少。当分布式数据库的计算层启动时，会先访问元数据层，获取所有集群信息，才能正确进行 SQL 的解析和路由等工作。另外，因为元数据信息存放在元数据层，那么分布式数据库的计算层可以有多个，用于实现性能的扩展。
- 存储层用来存放数据，但存储层要和计算层在同一台服务器上，甚至不求在同一个进程中。
可以看到，分布式数据库的优势是把数据打散到不同的服务器上，这种横向扩展的 Scale Out 能力，能解决单机数据库的性能与存储瓶颈。
从理论上来看，分布式数据库的性能可以随着计算层和存储层的扩展，做到性能的线性提升。
从可用性的角度看，如果存储层发生宕机，那么只会影响 1/N 的数据，N 取决于数据被打散到多少台服务器上。所以，分布式数据库的可用性对比单机会有很大提升，单机数据库要实现99.999% 的可用性或许很难，但是分布式数据库就容易多了。
当然，分布式数据库也存在缺点：正因为数据被打散了，分布式数据库会引入很多新问题，比如自增实现、索引设计、分布式事务等。
接下来，看一看分布式 MySQL 数据库的整体架构。
分布式MySQL架构
原先单机的 MySQL 架构中，客户端是通过 MySQL 通信协议访问 MySQL 数据库，MySQL 数据库会通过高可用技术做多副本，当发生宕机进行切换。
对于分布式 MySQL 数据库架构，其整体架构如下图所示：
从上图可以看到，这时数据将打散存储在下方各个 MySQL 实例中，每份数据叫“分片（Shard）”。
在分布式 MySQL 架构下，客户端不再是访问 MySQL 数据库本身，而是访问一个分布式中间件。
这个分布式中间件的通信协议依然采用 MySQL 通信协议（因为原先客户端是如何访问的MySQL 的，现在就如何访问分布式中间件）。分布式中间件会根据元数据信息，自动将用户请求路由到下面的 MySQL 分片中，从而将存储存取到指定的节点。
另外，分布式 MySQL 数据库架构的每一层都要由高可用，保证分布式数据库架构的高可用性。
对于上层的分布式中间件，是可以平行扩展的：即用户可以访问多个分布式中间件，如果其中一个中间件发生宕机，那么直接剔除即可。
因为分布式中间件是无状态的，数据保存在元数据服务中，它的高可用设计比较容易。
对于元数据来说，虽然它的数据量不大，但数据非常关键，一旦宕机则可能导致中间件无法工作，所以，元数据要通过副本技术保障高可用。
最后，每个分片存储本身都有副本，通过提到的高可用技术，保证分片的可用性。也就是说，如果分片 1 的 MySQL 发生宕机，分片 1 的从服务器会接替原先的 MySQL 主服务器，继续提供服务。
但由于使用了分布式架构，那么即使分片 1 发生宕机，需要 60 秒的时间恢复，这段时间对于业务的访问来说，只影响了 1/N 的数据请求。
可以看到，分布式 MySQL 数据库架构实现了计算层与存储层的分离，每一层都可以进行 Scale Out 平行扩展，每一层又通过高可用技术，保证了计算层与存储层的连续性，大大提升了MySQL 数据库的性能和可靠性，为海量互联网业务服务打下了坚实的基础。
思考题
Oracle 数据库在 12c 版本中提供了 Oracle Sharding 的分布式数据库功能，请对比我们今天学习的 MySQL 分布式数据库架构，思考一下，你会考虑在生产环境中使用 Oracle Sharding 功能吗？为什么？
分布式数据库表结构设计：数据分片
选出分片键
在对表中的数据进行分片时，首先要选出一个分片键（Shard Key），即用户可以通过这个字段进行数据的水平拆分。
对于之前使用的电商业务的订单表 orders，其表结构如下所示：
CREATE TABLE `orders` (
`O_ORDERKEY` int NOT NULL,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) NOT NULL,
PRIMARY KEY (`O_ORDERKEY`),
KEY `idx_custkey_orderdate` (`O_CUSTKEY`,`O_ORDERDATE`),
KEY `ORDERS_FK1` (`O_CUSTKEY`),
KEY `idx_custkey_orderdate_totalprice` (`O_CUSTKEY`,`O_ORDERDATE`,`O_TOTALPRICE`),
KEY `idx_orderdate` (`O_ORDERDATE`),
KEY `idx_orderstatus` (`O_ORDERSTATUS`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`O_CUSTKEY`) REFERENCES `customer` (`C_CUSTKEY`)
) ENGINE=InnoDB
对于类似淘宝、京东、拼多多这样业务体量的应用来说，单实例 MySQL 数据库在性能和存储容量上肯定无法满足“双 11、618 ”大促的要求，所以要改造成分布式数据库架构。
而第一步就是要对表选出一个分片键，然后进行分布式架构的设计。
对于上面的表orders，可以选择的分片键有：o_orderkey、o_orderdate、也可以是 o_custkey。在选出分片键后，就要选择分片的算法，比较常见的有 RANGE 和 HASH 算法。
比如，表 orders，选择分片键 o_orderdate，根据函数 YEAR 求出订单年份，然后根据RANGE 算法进行分片，这样就能设计出基于 RANGE 分片算法的分布式数据库架构：
从图中可以看到，采用 RANGE 算法进行分片后，表 orders 中，1992 年的订单数据存放在分片 1 中、1993 年的订单数据存放在分片 2 中、1994 年的订单数据存放在分片 3中，依次类推，如果要存放新年份的订单数据，追加新的分片即可。
不过，RANGE 分片算法在分布式数据库架构中，是一种非常糟糕的算法，因为对于分布式架构，通常希望能解决传统单实例数据库两个痛点：
性能可扩展，通过增加分片节点，性能可以线性提升；
存储容量可扩展，通过增加分片节点，解决单点存储容量的数据瓶颈。
那么对于订单表 orders 的 RANGE 分片算法来说，会发现以上两点都无法实现，因为当年的数据依然存储在一个分片上（即热点还是存在于一个数据节点上）。
如果继续拆细呢？比如根据每天进行 RANGE 分片？这样的确会好一些，但是对“双 11、618”这样的大促来说，依然是单分片在工作，热点依然异常集中。
所以在分布式架构中，RANGE 分区算法是一种比较糟糕的算法。但它也有好处：可以方便数据在不同机器间进行迁移（migrate），比如要把分片 2 中 1992 年的数据迁移到分片 1，直接将表进行迁移就行。
而对海量并发的 OLTP 业务来说，一般推荐用 HASH 的分区算法。这样分片的每个节点都可以有实时的访问，每个节点负载都能相对平衡，从而实现性能和存储层的线性可扩展。
来看表 orders 根据 o_orderkey 进行 HASH 分片，分片算法如下：
在上述分片算法中，分片键是 o_orderkey，总的分片数量是 4（即把原来 1 份数据打散到 4 张表中），具体来讲，分片算法是将 o_orderkey 除以 4 进行取模操作。
最终，将表orders 根据 HASH 算法进行分布式设计后的结果如下图所示：
可以看到，对于订单号除以 4，余数为 0 的数据存放在分片 1 中，余数为 1 的数据存放在分片 2 中，余数为 2 的数据存放在分片 3 中，以此类推。
这种基于 HASH 算法的分片设计才能较好地应用于大型互联网业务，真正做到分布式数据库架构弹性可扩展的设计要求。
但是，表 orders 分区键选择 o_orderkey 是最好地选择吗？并不是。
看一下库中的其他表，如表 customer、lineitem，这三张表应该是经常一起使用的，比如查询用户最近的订单明细。
如果用 o_orderkey 作分区键，那么 lineitem 可以用 l_orderkey 作为分区键，但这时会发现表customer 并没有订单的相关信息，即无法使用订单作为分片键。
如果表 customer 选择另一个字段作为分片键，那么业务数据无法做到单元化，也就是对于表customer、orders、lineitem，分片数据在同一数据库实例上。
所以，如果要实现分片数据的单元化，最好的选择是把用户字段作为分区键，在表 customer 中就是将 c_custkey 作为分片键，表orders 中将 o_custkey 作为分片键，表 lineitem 中将 l_custkey 作为分片键：
这样做的好处是：根据用户维度进行查询时，可以在单个分片上完成所有的操作，不用涉及跨分片的访问，如下面的 SQL：
SELECT * FROM orders
INNER JOIN lineitem ON o_orderkey = l_orderkey
INNER JOIN customer ON o_custkey = c_custkey
WHERE o_custkey = 1
ORDER BY o_orderdate DESC LIMIT 10
所以，分布式数据库架构设计的原则是：选择一个适合的分片键和分片算法，把数据打散，并且业务的绝大部分查询都是根据分片键进行访问。
那为什么互联网业务这么适合进行分布式架构的设计呢？因为互联网业务大部分是 To C 业务，分片键就是用户的 ID，业务的大部分访问都是根据用户 ID 进行查询，比如：
查看某个用户下的微博/短视频；
查看某个用户的商品信息/购买记录；
查看某个用户自己的余额信息。
说完分片键的选择后，接着就是规划分片，也就我们经常提到的分库分表。
分库分表
说了这么久分片，分片到底是什么呢？其实，前面说的分片本质是一张张表，而不是数据库实例，只是每个分片是在 MySQL 数据库实例中，严格来说：
分片 = 实例 + 库 + 表 = ip@port:db_name:table_name
对于前面的表 orders，假设根据 HASH 算法进行分片，那么可以进行如下的分库分表设计：
每个分片的表名库名都一样，如库 tpch，表名 orders；
每个分片的库名不一样，表名一样，如库名 tpch01、tpch02、tpch03、tpch04，表名 orders；
每个分片的表名不一样，库名一样，如库名 tpch，表名分别为 orders01、orders02、orders03、orders04；
每个分片的库名不一样，表名也不一样，如分片 1 的表在库名 tpch01下，表名为oders01；分片 2 的表名在库名 tpch02，表名为 orders02；分片 3 的表名在库名tpch03，表名为 orders03；分片 3 的表名在库名 tpch04，表名为 orders04。
在这 4 种分库分表规则中，最推荐的是第 4 种，也是我们通常意义说的分库分表，这样做的好处有以下几点：
不同分片的数据可以在同一 MySQL 数据库实例上，便于做容量的规划和后期的扩展；
同一分片键的表都在同一库下，方便做整体数据的迁移和扩容。
如果根据第 4 种标准的分库分表规范，那么分布式 MySQL 数据库的架构可以是这样：
按上面这样的分布式设计，数据分片完成后，所有的库表依然是在同一个 MySQL实例上！！！
牢记，分布式数据库并不一定要求有很多个实例，最基本的要求是将数据进行打散分片。接着，用户可以根据自己的需要，进行扩缩容，以此实现数据库性能和容量的伸缩性。这才是分布式数据库真正的魅力所在。
对于上述的分布式数据库架构，一开始将 4 个分片数据存储在一个 MySQL 实例上，但是如果遇到一些大促活动，可以对其进行扩容，比如把 4 个分片扩容到 4 个MySQL实例上：
如果完成了大促活动，又可以对资源进行回收，将分片又都放到一台 MySQL 实例上，这就是对资源进行缩容。
总的来说，对分布式数据库进行扩缩容在互联网公司是一件常见的操作，比如对阿里来说，每年下半年 7 月开始，他们就要进行双 11 活动的容量评估，然后根据评估结果规划数据库的扩容。
一般来说，电商的双 11 活动后，还有双 12、新年、春节，所以一般会持续到过完年再对数据库进行缩容。接下来，来看看如何进行扩缩容。
扩缩容
在 HASH 分片的例子中，把数据分片到了 4 个节点，然而在生产环境中，为了方便之后的扩缩容操作，推荐一开始就把分片的数量设置为不少于 1000 个。
不用担心分片数量太多，因为分片 1 个还是 1000 个，管理方式都是一样的，但是 1000 个，意味着可以扩容到 1000 个实例上，对于一般业务来说，1000 个实例足够满足业务的需求了（BTW，网传阿里某核心业务的分布式数据库分片数量为 10000个）。
如果到了 1000 个分片依然无法满足业务的需求，这时能不能拆成 2000 个分片呢？从理论上来说是可以的，但是这意味着需要对一张表中的数据进行逻辑拆分，这个工作非常复杂，通常不推荐。
所以，一开始一定要设计足够多的分片。在实际工作中，我遇到很多次业务将分片数量从 32、64 拆成 256、512。每次这样的工作，都是扒一层皮，太不值得。所以，做好分布式数据库设计的工作有多重要！
那么扩容在 MySQL 数据库中如何操作呢？其实，本质是搭建一个复制架构，然后通过设置过滤复制，仅回放分片所在的数据库就行，这个数据库配置在从服务器上大致进行如下配置：
# 分片1从服务器配置
replicate_do_db ="tpch01"
所以在进行扩容时，首先根据下图的方式对扩容的分片进行过滤复制的配置：
然后再找一个业务低峰期，将业务的请求转向新的分片，完成最终的扩容操作：
至于缩容操作，本质就是扩容操作的逆操作。
思考题
标准 tpch 库的 lineitem 表结构如下：
Create Table: CREATE TABLE `lineitem` (
`l_orderkey` int NOT NULL,
`L_PARTKEY` int NOT NULL,
`L_SUPPKEY` int NOT NULL,
`L_LINENUMBER` int NOT NULL,
`L_QUANTITY` decimal(15,2) NOT NULL,
`L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
`L_DISCOUNT` decimal(15,2) NOT NULL,
`L_TAX` decimal(15,2) NOT NULL,
`L_RETURNFLAG` char(1) COLLATE utf8mb4_general_ci NOT NULL,
`L_LINESTATUS` char(1) COLLATE utf8mb4_general_ci NOT NULL,
`L_SHIPDATE` date NOT NULL,
`L_COMMITDATE` date NOT NULL,
`L_RECEIPTDATE` date NOT NULL,
`L_SHIPINSTRUCT` char(25) COLLATE utf8mb4_general_ci NOT NULL,
`L_SHIPMODE` char(10) COLLATE utf8mb4_general_ci NOT NULL,
`L_COMMENT` varchar(44) COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB
看看上面的表结构该如何修改，才能更好地符合分片的要求？
分布式数据库索引设计
在分布式数据库架构下，索引的设计也需要做调整，否则无法充分发挥分布式架构线性可扩展的优势。
主键选择
对主键来说，要保证在所有分片中都唯一，它本质上就是一个全局唯一的索引。如果用大部分同学喜欢的自增作为主键，就会发现存在很大的问题。
因为自增并不能在插入前就获得值，而是要通过填 NULL 值，然后再通过函数 last_insert_id() 获得自增的值。所以，如果在每个分片上通过自增去实现主键，可能会出现同样的自增值存在于不同的分片上。
比如，对于电商的订单表 orders，其表结构如下（分片键是o_custkey，表的主键是o_orderkey）：
CREATE TABLE `orders` (
`O_ORDERKEY` int NOT NULL auto_increment,
`O_CUSTKEY` int NOT NULL,
`O_ORDERSTATUS` char(1) NOT NULL,
`O_TOTALPRICE` decimal(15,2) NOT NULL,
`O_ORDERDATE` date NOT NULL,
`O_ORDERPRIORITY` char(15) NOT NULL,
`O_CLERK` char(15) NOT NULL,
`O_SHIPPRIORITY` int NOT NULL,
`O_COMMENT` varchar(79) NOT NULL,
PRIMARY KEY (`O_ORDERKEY`),
KEY (`O_CUSTKEY`)
......
) ENGINE=InnoDB
如果把 o_orderkey 设计成上面所示的自增，那么很可能 o_orderkey 同为 1 的记录在不同的分片出现。
所以，在分布式数据库架构下，尽量不要用自增作为表的主键，这也是在第一模块“表结构设计”中强调过的：自增性能很差、安全性不高、不适用于分布式架构。
那么该如何设计主键呢？依然还是用全局唯一的键作为主键，比如 MySQL 自动生成的有序 UUID；业务生成的全局唯一键（比如发号器）；或者是开源的 UUID 生成算法，比如雪花算法（但是存在时间回溯的问题）。
索引设计
通过分片键可以把 SQL 查询路由到指定的分片，但是在现实的生产环境中，业务还要通过其他的索引访问表。
还是以前面的表 orders 为例，如果业务还要根据 o_orderkey 字段进行查询，比如查询订单 ID 为 1 的订单详情：
SELECT * FROM orders WHERE o_orderkey = 1
可以看到，由于分片规则不是分片键，所以需要查询 4 个分片才能得到最终的结果，如果下面有 1000 个分片，那么就需要执行 1000 次这样的 SQL，这时性能就比较差了。
但是， o_orderkey 是主键，应该只有一条返回记录，也就是说，o_orderkey 只存在于一个分片中。这时，可以有以下两种设计：
- 同一份数据，表 orders 根据 o_orderkey 为分片键，再做一个分库分表的实现；
- 在索引中额外添加分片键的信息。
这两种设计的本质都是通过冗余实现空间换时间的效果，否则就需要扫描所有的分片，当分片数据非常多，效率就会变得极差。
而第一种做法通过对表进行冗余，对于 o_orderkey 的查询，只需要在 o_orderkey = 1 的分片中直接查询就行，效率最高，但是设计的缺点又在于冗余数据量太大。
所以，改进的做法之一是实现一个索引表，表中只包含 o_orderkey 和分片键 o_custkey，如：
CREATE TABLE idx_orderkey_custkey （
o_orderkey INT
o_custkey INT,
PRIMARY KEY (o_orderkey)
)
如果这张索引表很大，也可以将其分库分表，但是它的分片键是 o_orderkey，如果这时再根据字段 o_orderkey 进行查询，可以进行类似二级索引的回表实现：先通过查询索引表得到记录 o_orderkey = 1 对应的分片键 o_custkey 的值，接着再根据 o_custkey 进行查询，最终定位到想要的数据，如：
SELECT * FROM orders WHERE o_orderkey = 1
=>
# step 1
SELECT o_custkey FROM idx_orderkey_custkey
WHERE o_orderkey = 1
# step 2
SELECT * FROM orders
WHERE o_custkey = ? AND o_orderkey = 1
这个例子是将一条 SQL 语句拆分成 2 条 SQL 语句，但是拆分后的 2 条 SQL 都可以通过分片键进行查询，这样能保证只需要在单个分片中完成查询操作。不论有多少个分片，也只需要查询 2个分片的信息，这样 SQL 的查询性能可以得到极大的提升。
通过索引表的方式，虽然存储上较冗余全表容量小了很多，但是要根据另一个分片键进行数据的存储，依然显得不够优雅。
因此，最优的设计，不是创建一个索引表，而是将分片键的信息保存在想要查询的列中，这样通过查询的列就能直接知道所在的分片信息。
如果将订单表 orders 的主键设计为一个字符串，这个字符串中最后一部分包含分片键的信息，如：
o_orderkey = string（o_orderkey + o_custkey）
那么这时如果根据 o_orderkey 进行查询：
SELECT * FROM Orders
WHERE o_orderkey = '1000-1';
由于字段 o_orderkey 的设计中直接包含了分片键信息，所以可以直接知道这个订单在分片1 中，直接查询分片 1 就行。
同样地，在插入时，由于可以知道插入时 o_custkey 对应的值，所以只要在业务层做一次字符的拼接，然后再插入数据库就行了。
这样的实现方式较冗余表和索引表的设计来说，效率更高，查询可以提前知道数据对应的分片信息，只需 1 次查询就能获取想要的结果。
这样实现的缺点是，主键值会变大一些，存储也会相应变大。但正如 05 讲说的，只要主键值是有序的，插入的性能就不会变差。而通过在主键值中保存分片信息，却可以大大提升后续的查询效率，这样空间换时间的设计，总体上看是非常值得的。
当然，这里谈的设计都是针对于唯一索引的设计，如果是非唯一的二级索引查询，那么非常可惜，依然需要扫描所有的分片才能得到最终的结果，如：
SELECT * FROM Orders
WHERE o_orderate >= ? o_orderdate < ?
因此，分布式数据库架构设计的要求是业务的绝大部分请求能够根据分片键定位到 1 个分片上。
如果业务大部分请求都需要扫描所有分片信息才能获得最终结果，那么就不适合进行分布式架构的改造或设计。
全局表
在分布式数据库中，有时会有一些无法提供分片键的表，但这些表又非常小，一般用于保存一些全局信息，平时更新也较少，绝大多数场景仅用于查询操作。
例如 tpch 库中的表 nation，用于存储国家信息，但是在 1SQL 关联查询中，又经常会使用到这张表，对于这种全局表，可以在每个分片中存储，这样就不用跨分片地进行查询了。如下面的设计：
唯一索引
最后来谈谈唯一索引的设计，与主键一样，如果只是通过数据库表本身唯一约束创建的索引，则无法保证在所有分片中都是唯一的。
所以，在分布式数据库中，唯一索引一样要通过类似主键的 UUID 的机制实现，用全局唯一去替代局部唯一，但实际上，即便是单机的 MySQL 数据库架构，也推荐使用全局唯一的设计。因为你不知道，什么时候，业务就会升级到全局唯一的要求了。
分布式数据库架构选型
前面了解了分布式数据库的分片设计、表结构设计、索引设计等，已经有能力构建一个分布式数据库系统了。
但现在数据分好了，索引也设计好了，但是如果访问这些数据和索引呢？
访问分布式数据库有两种模式：
- 业务直接根据分库分表访问 MySQL 数据库节点；
- 根据中间件访问。
分库分表直接访问
在设计分片时，已经明确了每张表的分片键信息，所以业务或服务可以直接根据分片键对应的数据库信息，直接访问底层的 MySQL 数据节点，比如在代码里可以做类似的处理：
void InsertOrders(String orderKey, int userKey...) {
int shard_id = userKey % 4;
if (shard_id == 0) {
conn = MySQLConncetion('shard1',...);
conn.query(...);
} else if (shard_id == 1) {
conn = MySQLConncetion('shard2',...);
conn.query(...);
} else if (shard_id == 2) {
conn = MySQLConncetion('shard3',...);
conn.query(...);
} else if (shard_id == 3) {
conn = MySQLConncetion('shard4',...);
conn.query(...);
}
}
从这段代码中可以看到，在业务代码中会嵌入分库分表的路由逻辑，在业务层计算出对应分片的信息，然后访问数据库：
- 这种处理方式的好处是与单实例数据库没有太大的不同，只是多了一次计算分片的操作，没有额外的开销，性能非常好（听说支付宝的分布式数据库为了最求极致的性能，用的就是直接访问分片的方式）。
- 这种处理逻辑的缺点是业务需要知道分片信息，感知分片的变化。对于上面的例子，如果分片 shard1 发生变化，又或者进行了扩容，业务就需要跟着修改。
为了解决这个缺点，比较好的处理方式是使用名字服务，而不要直接通过 IP 访问分片。这样当分片发生切换，又或者扩容缩容时，业务也不需要进行很大的改动。
又因为业务比较多，需要访问分布式数据库分片逻辑的地方也比较多。所以，可以把分片信息存储在缓存中，当业务启动时，自动加载分片信息。比如，在 Memcached 或 Redis 中保存如下的分片信息，key 可以是分库分表的表名，value通过 JSON 或字典的方式存放分片信息：
{
'key': 'orders',
'shard_info' : {
'shard_key' : 'o_custkey',
'shard_count' : 4，
'shard_host' : ['shard1.xxx.com','shard2.xxx.com','...']，
‘shard_table' : ['tpch00/orders01','tpch01/orders02','...'],
}
}
如果要进行跨分片的访问，则需要业务自己处理相关逻辑。不过前面已经说过，分布式数据库设计要求单元化，绝大部分操作需要在一个分片中完成。如果不能，那么可能都不推荐分布数据库的改造。
总之，分库分表的直接访问方式，要求业务控制一切有关分布式数据库的操作，需要明确每个分片的具体信息，做好全流程的把控。
使用中间件技术
另一种比较流行的分布式数据库访问方式是通过分布式数据库中间件。数据库中间件本身模拟成一个 MySQL 数据库，通信协议也都遵循 MySQL 协议：业务之前怎么访问MySQL数据库的，就如何访问MySQL分布式数据库中间件。
这样做的优点是：业务不用关注分布式数据库中的分片信息，把它默认为一个单机数据库使用就好了。这种模式也是大部分同学认为分布式数据库该有的样子，如下面的图：
可以看到，通过分布式 MySQL 中间件，用户只需要访问中间件就行，下面的数据路由、分布式事务的实现等操作全部交由中间件完成。所以，分布式数据库中间件变成一个非常关键的核心组件。
业界比较知名的 MySQL 分布式数据库中间件产品有：ShardingShpere、DBLE、TDSQL 等。
ShardingSphere于 2020 年 4 月 16 日成为 Apache 软件基金会的顶级项目、社区熟度、功能支持较多，特别是对于分布式事务的支持，有多种选择（ShardingSphere 官网地址）。
DBLE 是由知名 MySQL 服务商爱可生公司开源的 MySQL 中间件产品，已用于四大行核心业务，完美支撑传统银行去 IOE，转型分布式架构的探索。除了中间件技术外，爱可生公司还有很多关于 MySQL 数据库、分布式数据库设计等方面的综合经验。
TDSQL MySQL 版（TDSQL for MySQL）是腾讯打造的一款分布式数据库产品，具备强一致高可用、全球部署架构、分布式水平扩展、高性能、企业级安全等特性，同时提供智能 DBA、自动化运营、监控告警等配套设施，为客户提供完整的分布式数据库解决方案。
目前 TDSQL 已经为超过500+的政企和金融机构提供数据库的公有云及私有云服务，客户覆盖银行、保险、证券、互联网金融、计费、第三方支付、物联网、互联网+、政务等领域。TDSQL MySQL 版亦凭借其高质量的产品及服务，获得了多项国际和国家认证，得到了客户及行业的一致认可。
要注意，使用数据库中间件虽好，但其存在一个明显的缺点，即多了一层中间层的访问，单个事务的访问耗时会有上升，对于性能敏感的业务来说，需要有这方面的意识和考虑。
重要的一点是，虽然使用分布式数据库中间件后，单个事务的耗时会有所上升，但整体的吞吐率是不变的，通过增大并发数，可以有效提升分布式数据库的整体性能。
如何选型
那么，选择业务直连分布式数据库？还是通过数据库中间件访问？这是一个架构选型要考虑的问题。
根据作者的经验来说，对于较小业务（高峰期每秒事务不超过 1000 的业务），选择通过数据库中间件访问分布式数据库是比较优的方式。
因为这样的业务通常处于爬升期，满足业务的各项功能或许是业务的主要目标。通过分布式中间件屏蔽下面的分片信息，可以让开发同学专注于业务的开发。
另一方面，通过使用中间件提供的分布式事务就能满足简单的跨分片交易，解决分布式数据库中最难的问题。
但如果业务是一个海量互联网业务，中间件的瓶颈就会显现，单个事务的耗时会上升，低并发下，性能会有一定下降。而且中间件提供的 2PC 分布式事务性能就更不能满足业务的需求了。所以类似支付宝、阿里这样的业务，并没有使用分布式数据库中间件的架构，而是采用了业务直连的模式。
很多同学或许会问，如果不用数据库中间件，怎么解决 JOIN 这些问题呢？业务层去实现还是很麻烦的。的确，中间件可以完成这部分的功能。但如果真是数据量比较大，跨分片的场景，中间件也不能满足你的要求。
分布式数据库全链路条带化设计
当提到分布式架构时，除了数据库要完成分布式架构的改造，业务层也要完成分布式架构的改造，最终完成条带化的设计。
条带化是存储的一种技术，将磁盘进行条带化后，可以把连续的数据分割成相同大小的数据块，简单的说，条带化就是把每段数据分别写入阵列中不同磁盘上的方法。
可以看到，条带化的本质是通过将数据打散到多个磁盘，从而提升存储的整体性能，这与分布式数据库的分片理念是不是非常类似呢？下图显示了 RAID0 的条带化存储：
从图中可以看到，进行 RAID 条带化后，数据存放在了三块磁盘上，分别是磁盘 1、磁盘 2、磁盘 3，存储的数据也进行了打散，分别存储在了条带 1、条带 2、条带 3 上。
这样一来，当访问某一个数据的时候，可以并行地从 3 个磁盘上取出数据，写入也可以同时写入 3 个磁盘，提升了存储的性能。
了解完条带化的基础知识之后，分布式数据库架构的“条带化”的访问情况又是怎么样的呢？
全链路的条带化设计
在前面已经讲过分布式数据库的本质是：将数据根据某个或几个列（称之为“分片键”），然后依据预先设定的算法（分片算法）进行打散，形成一个个的分片。
更重要的是，分布式数据库中的表，要能选出一个统一的分片键，即大部分表都能根据这个分片键打散数据，这样当后续业务进行访问数据时，可以在一个分片中完成单元化的闭环操作，不用涉及跨分片的访问。
下图显示了对于 tpch 分布式架构改造后的分片效果：
从图中可以看到，这与之前所提倡的条带化的思想比较类似，即数据打散，性能得到提升，对于分布式数据库来说，分片越多，性能上限也就越高。
但是，这只是对数据库层做了条带化，没有站在全链路的角度上进行条带化设计，假设是电商中比较重要的订单服务，并且对表 orders 进行了分布式的条带化设计：
可以看到，订单服务可以根据字段 o_custkey 访问不同分片的数据，这也是大部分业务会进行的设计（由于服务层通常是无状态的，因此这里不考虑高可用的情况）。但是，这样的设计不符合全链路的条带化设计思想。
全链路的设计思想，要将上层服务也作为条带的一部分进行处理，也就是说，订单服务也要跟着分片进行分布式架构的改造。
所以，如果进行全链路的条带化设计，那么上面的订单服务应该设计成：
可以看到，如果要进行分布式的条带化设计时，上层业务服务也需要进行相应的分布式改造，将1个“大”订单服务层也拆分成多个“小”订单服务，其中每个订单服务访问自己分片的数据。
这样设计的好处在于：
- 安全性更好，每个服务可以校验访问用户是否本分片数据；
- 上层服务跟着数据分片进行条带化部署，业务性能更好；
- 上层服务跟着数据分片进行条带化部署，可用性更好；
第1点通常比较好理解，但是 2、3点 就不怎么好理解了。为什么性能也会更好呢？这里请考虑一下业务的部署情况，也就是，经常听说的多活架构设计。
多活架构
在前面的高可用的章节中已经说过，对于高可用的架构设计要做到跨机房部署，实现的方式是无损半同复制，以及最新的 MySQL Group Rreplication 技术。数据库实例通过三园区进行部署。这样，当一个机房发生宕机，可以快速切换到另一个机房。再来回顾下三园区的架构设计：
图中显示了通过无损半同步复制方式进行的三园区高可用架构设计，从而实现同城跨机房的切换能力。但这只是单实例 MySQL 数据库架构，如果到分布式架构呢？所有分片都是在一个机房吗？
如果所有分片都在一个机房，你会发现，这时机房 2、机房3 中的数据库都只是从机，只能进行读取操作，而无法实现写入操作，这就是我们说的单活架构。
与单活架构不同，多活架构是指不同地理位置上的系统，都能够提供业务读/写服务。这里的“活”是指实时提供读/写服务的意思，而不仅仅只是读服务。多活架构主要是为了提升系统的容灾能力，提高系统的可用性，保障业务持续可用。
要实现多活架构，首先要进行分布式数据库的改造，然后是将不同数据分片的主服务器放到不同机房，最后是实现业务条带化的部署。如下面的这张图：
可以看到，对于上一节的订单服务和订单数据分片，通过将其部署在不同的机房，使得订单服务1 部署在机房 1，可以对分片1进行读写；订单服务 2 部署在机房 1，可以对分片 2 进行读写；订单服务 3 部署在机房 3，可以对分片 3 进行读写。
这样每个机房都可以有写入流量，每个机房都是“活”的，这就是多活架构设计。
若一个机房发生宕机，如机房 1 宕机，则切换到另一个机房，上层服务和数据库跟着一起切换，切换后上层服务和数据库依然还是在一个机房，访问不用跨机房访问，依然能提供最好的性能和可用性保障。
分布式事务
分布式事务概念
事务的概念相信已经非常熟悉了，事务就是要满足 ACID 的特性，总结来说。
- A（Atomicity） 原子性：事务内的操作，要么都做，要么都不做；
- C（Consistency） 一致性：事务开始之前和事务结束以后，数据的完整性没有被破坏；如唯一性约束，外键约束等；
- I（Isolation）隔离性：一个事务所做的操作对另一个事务不可见，好似是串行执行；
- D（Durability）持久性：事务提交后，数据的修改是永久的。即使发生宕机，数据也能修复；
特别需要注意的是，当前数据库的默认事务隔离级别都没有达到隔离性的要求，MySQL、Oracle、PostgreSQL等关系型数据库都是如此。大多数数据库事务隔离级别都默认设置为 READ-COMMITTED，这种事务隔离级别没有解决可重复度和幻读问题。
但由于在绝大部分业务中，都不会遇到这两种情况。若要达到完全隔离性的要求，性能往往又会比较低。因此在性能和绝对的隔离性前，大多数关系型数据库选择了一种折中。
那什么是分布式事务呢？简单来说，就是要在分布式数据库的架构下实现事务的ACID特性。
前面讲了分布式数据库架构设计的一个原则，即大部分的操作要能单元化。即在一个分片中完成。如对用户订单明细的查询，由于分片键都是客户ID，因此可以在一个分片中完成。那么他能满足事务的ACID特性。
但是，如果是下面的一个电商核心业务逻辑，那就无法实现在一个分片中完成，即用户购买商品，其大致逻辑如下所示：
START TRANSATION;
INSERT INTO orders VALUES (......);
INSERT INTO lineitem VALUES (......);
UPDATE STOCK SET COUNT = COUNT - 1 WHERE sku_id = ?
COMMIT;
可以看到，在分布式数据库架构下，表orders、linitem的分片键是用户ID。但是表stock是库存品，是商品维度的数据，没有用户ID的信息。因此stock的分片规则肯定与表orders和lineitem不同。
所以，上述的事务操作大部分情况下并不能在一个分片中完成单元化，因此就是一个分布式事务，它要求用户维度的表 orders、lineitem 和商品维度的表 stock 的变更，要么都完成，要么都完成不了。
常见的分布式事务的实现就是通过 2PC（two phase commit 两阶段提交）实现，接着来看下 2PC。
2PC的分布式事务实现
2PC 是数据库层面实现分布式事务的一种强一致性实现。在 2PC 中，引入事务协调者的角色用于协调管理各参与者（也可称之为各本地资源）的提交和回滚。而 2PC 所谓的两阶段是指parepare（准备）阶段和 commit（提交）两个阶段。
在 2PC 的实现中，参与者就是分钟的 MySQL 数据库实例，那事务协调者是谁呢？这取决于分布式数据库的架构。若分布式数据库的架构采用业务通过分库分表规则直连分片的话，那么事务协调者就是业务程序本身。如下图所示：
若采用数据库中间件的模式，那么事务协调者就是数据库中间件。如下图所示：
从上图可以发现，使用分布式数据库中间件后，可以对上层服务屏蔽分布式事务的实现，服务不需要关心下层的事务是本地事务还是分布式事务，就好像是单机事务本身一样。
但是 2PC 的一个难点在于 prepare 都成功了，但是在进行第二阶段 commit 的时候，其中一个节点挂了。这时挂掉的那个节点在恢复后，或进行主从切换后，节点上之前执行成功的prepare 事务需要人为的接入处理，这个事务就称之为悬挂事务。
用户可以通过命令 XA_RECOVER 查看节点上事务有悬挂事务：
如果有悬挂事务，则这个事务持有的锁资源都是没有释放的。可以通过命令 SHOW ENGINE INNODB STATUS 进行查看：
从上图可以看到，事务 5136 处于 PREPARE状态，已经有 218 秒了，这就是一个悬挂事务，并且这个事务只有了两个行锁对象。
可以通过命令 XA RECOVER 人工的进行提交：
到这里应该都了了分布式事务的 2PC 实现和使用方法。它是一种由数据库层实现强一致事务解决方案。其优点是使用简单，当前大部分的语言都支持 2PC 的实现。若使用中间件，业务完全就不用关心事务是不是分布式的。
然而，他的缺点是，事务的提交开销变大了，从 1 次 COMMIT 变成了两次 PREPARE 和COMMIT。而对于海量的互联网业务来说，2PC 的性能是无法接受。因此，这就有了业务级的分布式事务实现，即柔性事务。
柔性事务
柔性事务是指分布式事务由业务层实现，通过最终一致性完成分布式事务的工作。可以说，通过牺牲了一定的一致性，达到了分布式事务的性能要求。
业界常见的柔性事务有 TCC、SAGA、SEATA 这样的框架、也可以通过消息表实现。它们实现原理本身就是通过补偿机制，实现最终的一致性。柔性事务的难点就在于对于错误逻辑的处理。
为了讲述简单，这里用消息表作为柔性事务的案例分享。对于上述电商的核心电商下单逻辑，用消息表就拆分为 3 个阶段：
阶段1：
START TRANSACTION;
# 订单号，订单状态
INSERT INTO orders VALUES (...)
INSERT INTO lineitem VALUES (...)
COMMIT;
阶段2：
START TRANSACTION;
UPDATE stock SET count = count -1 WHERE sku_id = ?
# o_orderkey是消息表中的主键，具有唯一约束
INSERT INTO stock_message VALUES (o_orderkey, ... )
COMMIT;
阶段3：
UPDATE orders SET o_orderststus = 'F' WHERE o_orderkey = ?
上面的柔性事务中，订单表中的列 o_orderstatus 用于记录柔性事务是否完成，初始状态都是未完成。表 stock_message 记录对应订单是否已经扣除过相应的库存。若阶段 2 完成，则柔性事务必须完成。阶段 3 就是将柔性事务设置为完成，最终一致性的确定。
接着我们来下，若阶段 2 执行失败，即执行过程中节点发生了宕机。则后台的补偿逻辑回去扫描订单表中 o_orderstatus 为未完成的超时订单有哪些，然后看一下是否在对应的表stock_message 有记录，若有，则执行阶段 3。若无，可选择告知用户下单失败。
若阶段 3 执行失败，处理逻辑与阶段 2 基本一致，只是这时 2 肯定是完成的，只需要接着执行阶段 3 即可。
所以，这里的补偿逻辑程序就是实时/定期扫描超时订单，通过消息表判断这个柔性事务是继续执行还是执行失败，执行失败又要做哪些业务处理。
上面介绍的框架实现的柔性事务原理大致如此，只不过对于补偿的逻辑处理有些不同，又或者使用上更为通用一些。
对于海量的互联网业务来说，柔性事务性能更好，因此支付宝、淘宝等互联网业务都是使用柔性事务完成分布式事务的实现。
实战篇
MySQL 数据库开发规范
表结构设计的 10 个规范
- INT 类型不使用 unsigned 无符号属性，容易引入额外的计算问题。
- 自增用 8 字节 BIG INT，不要使用 4 字节 INT，且自增在 MySQL 8.0 版本前有回溯问题，请考虑是否业务有影响。
- 字符集使用 UTF8MB4 字符编码，不推荐 GBK、UTF-8 等其他字符集。
- 日期类型用 DATETIME 类型，需要精确到毫秒用 DATETIME(6)，不要使用 INT、TIMESTAMP。
- 类型 JSON 可用于存储非结构化数据，典型场景为用户标签，不要将 JSON 用于频繁更新的字段场景。
- 每张表一定要有一个主键，这样至少满足一范式的要求，核心业务表用全局唯一字段（雪花算法、有序UUID）做主键，不要使用自增做主键。
- 对于日志类的流水表、报警表、日志表，可以使用压缩设计，提升存储效率。MySQL 5.7 版本开始推荐使用透明页压缩，不要使用传统的 KEY_BLOCK_SIZE 的页压缩。
- 类别设计，用 ENUM+CHECK 约束，不要使用 INT 类型的设计。
- 敏感字段需加密，如账户密码、信用卡号等存储使用：动态盐 + 非固定加密算法（MD5/AES256等） + 多轮加密，不要简单使用 MD5 算法加密，容易被暴力破解。
- MySQL 可以通过 KV 的方式访问表中的数据，若业务只是简单的 SET、GET 请求，可考虑将其转化为 Memcached 的 KV 访问方式，减少 SQL 解析的开销，性能可以有至少 50% 的提升。
索引设计的 10 个规范
- 不要陷入设置单表行数、列数限制的固有印象，其他关系型数据库没有行数、列数限制，MySQL 也没有，大表的缺点不是性能，而是后续的 DDL 管理问题，随着 MySQL 8.0 快速加列功能的上线，大表 DDL 问题基本已解决。
- MySQL 是索引组织表，表中的数据以 B+ 树索引结构，根据主键逻辑排序，由于 B+ 树索引的特点是树的高度为 3~4 层，所以从数十亿的记录中，通过主键查询一条记录只需要 3、4 次 I/O，当前到 SSD 存储设备设置每秒至少能完成 10000 次的 I/O 查询，不要担心通过索引查询一条或几条记录的性能，每秒百万次查询并不难。
- MySQL 是索引组织表，二级索引只存储（键值、主键值），因此需要再通过一次主键索引查询得到记录，这种方式成为回表。在核心业务中，使用索引覆盖技术，提升索引查询性能，对于回表记录数比较大的场景，甚至可以有 10 倍的性能提升；
- 对类似
WHERE a = ? ORDER BY b这样的查询，一定要创建（a、b）组合索引，这样可以避免一次额外排序，提升查询性能。 - MySQL 优化器是 CBO（Cost-based Optimizer），所有查询基于成本而不是规则，若发现 SQL 执行计划发生变化，不要怀疑 MySQL 出错，请先分析数据特点、索引创建是否合理，是否可以通过直方图校准数据。
- MySQL JOIN 支持 NLJ（Nested Loop Join）和 NHJ（Nested Hash Join）两种方式。对于 OLTP 业务，放心大胆使用 JOIN，但一定要做好索引的设计和索引覆盖的考虑（不考虑分布式数据库场景）；对于 OLAP 业务，MySQL 8.0 版本开始，支持 Hash Join，对于大数据量的关联，性能提升非常多，可以在不超过 10T 的数仓场景中考虑使用，超过 10T数据量，请一定使用大数据产品，如 Hive、Spark、麒麟等产品。
- MySQL 5.7 版本开始子查询优化已经做得不错，但是编写的子查询不能是关联子查询，上线前一定需要确认，若发现关联子查询，请改写子查询为 JOIN 或其他方式。
- 不要因为数据量大，使用分区表，MySQL 是索引组织表，数据量再大，定位记录也只需要3、4 次 I/O。可以考虑分区表唯一的应用场景是：需要定期清理历史流水类数据，但如果业务可以按月、按天做分表，那么当前 MySQL 8.0 版本，分区表也不推荐使用。
- 业务上线或新版本发布前，DBA 一定要进行所有 SQL Review，确保 SQL 走索引，否则不予上线，或由业务以邮件等正式方式，通知 DBA 该 SQL 不会引起线上事故，业务方承担后续责任。
- DBA 每天要对数据库进行巡检，及早发现慢查询或潜在数据库风险，将任何潜在问题尽早抛出，否则后续自己承担相关责任。
高可用架构设计的 10 个规范
- MySQL 高可用的基石是利用二进制日志的复制技术，核心业务一定要使用无损半同步方式，但凡不适用无损半同步的高可用架构，请业务方业务以邮件等正式方式回复，数据丢失等后续问题自己承担相关责任。
- MySQL 5.7 版本开始，一定要使用基于 WRITESET 的从机回放，避免主从延迟。
- 当前 MySQL 发生主从延迟的可能性主要是存在大事务，比如定期计算收益等操作，这类大事务，一定要通知业务方将大事务拆成小事务，否则不予上线；若要上线，请业务方以邮件等方式回复，自己承担后续主从延迟带来的后续一系列问题。
- MySQL 8.0 版本开始推荐金融业务使用 MGR(MySQL Group Replication），通过 Paxos 协议保证数据一致性，并自己完成选主的逻辑，也可以使用多主模式，数据不冲突的情况下，可以大幅提升写入性能。
- 对于核心业务，必须遵循互不信任原则，数据一致性不单单依赖 MySQL 复制本身，DBA 这里需要通过逻辑的方式，对主从数据进行核对，业务这里也需要一套业务层的逻辑进行“对账”。
- 核心业务，务必使用一地三中心，两地三中心的跨机房复制架构，这样发生机房级故障，可以切换到另一个机房，保证业务可用性。
- 同城容灾架构一定要评估切换到另一个机房后业务访问的性能，多次跨机房访问 DB，虽然每次只多了 2~3ms，但也存在业务雪崩问题，推荐 DB 切换机房，上层业务跟着一起联动切换。
- 对于有跨城容灾需求的业务，可以考虑使用三地五中心架构，但是由于 30ms 延迟，业务需要进行评估，对于核心业务，务必使用业务层的跨城机制，将数据层的多次网络耗时合并为一次，这样能大大提升业务的性能。
- 业界的 MHA、Ochestrator 等高可用套件都是基于 ssh 访问 MySQL，稳定性、安全性不高，不推荐大厂使用；自己开发一个数据库管理平台，通过 agent 的模式管理高可用和 MySQL 数据库的日常操作更为安全、有效。
- 一定做好数据备份架构的设计，全备 + 增量备份 + 延迟备机（可选），做到可以基于任何一点恢复和回滚，同时，遵循互不信任原则，备份文件一定要进行检查，确保需要时一定能够进行恢复。
分布式架构设计的 10 个规范
- 分布式数据库的本质就是根据某几个列的规则，将数据水平打散，存在不同的实例中。数据拆分的列就成为分区键，分区键一定是业务大部分访问（超过 80%）的表都会使用的列。若选不出合适的分区键，那就一定不要进行分布式数据库架构的设计；互联网业务绝大部分分区键的选择是用户维度。
- 分区算法绝大部分场景使用 Hash算法，这样数据的存储和访问可以平均到下面多个实例，真正的做到可扩展性，Range 算法通常无法解决热点问题，会是灾难，但 Range 算法可以但实例中使用，作为二级拆分数据的规则。
- 分布式数据库分片时，一开始就设计为不少于 1000 个分片的规则，不用担心分片过多的问题，管理 1 个分片和 1000 个分片的成本是一样的，但为后续的扩展做好了充足的准备。
- 分布式数据库扩缩容就是通过部分过滤的复制技术，按库或按表进行数据同步，分库分表设计推荐库名、表都不同，做到全局唯一，方便后续拆分。
- 分布式数据库索引设计中，非分区键的唯一索引一定带入分区键信息，这样业务查询时可以直接定位到数据所在分片，提升查询效率。
- 分布式数据库索引设计中，数据库层的唯一约束只在单个实例中保证，若要保证全局唯一，一定要使用全局唯一的索引设计。
- 直接使用 JOIN 请确认一定可以单元化在一个分片中完成，如果涉及跨分片的 JOIN，请通知业务修改成多条 SQL 的访问方式，只访问指定分片而不是所有分片。
- 分布式数据库可以进行业务层的分库分表访问，和通过数据库中间件的访问，对于业务耗时敏感的业务，推荐业务层直接根据路由规则访问数据，否则使用数据库中间件，简单易用。
- 对于耗时敏感的核心业务，推荐使用最终一致的业务层柔性事务，数据库层的 2PC 分布式事务耗时较大，性能较为一般，但是 2PC 使用简单，能满足大部分业务的使用。
- 一定要利用好分布式数据库架构的特点，设计多活架构，每个机房都可以有写入流量，提升资源使用率和业务连续性，请 DBA 和业务方一起做好全链路的架构设计。
删除生产环境中的大表
引起 MySQL 数据库性能抖动的原因有很多，比如大事务、定时批量查询等，而这些原因一般都会注意到。但是，有一个引起性能抖动的原因却经常被忽视，那就是在生产环境删除无用的大表，即 DROP TABLE。
生产环境中，为什么要 DROP TABLE？绝大部分原因是为了释放空间。
生产环境大多数是已经确定的库表，一般不会进行 DROP TABLE 这么重的操作，甚至大部分DBA 都不应该拥有 DROP 权限。
但是随着数据无限增长，在某些系统中，需要将数据归档到历史数据库，删除生产数据库中的表，从而释放存储空间，典型的就是偏日志、流水类的数据。
不过，当谈一个数据库多大的时候，单纯讨论存储容量意义并不大，我们更希望得到的是活跃记录有多少。
银行系统的 Oracle 数据库，虽然单库都可以数 T，但大部分都是日志数据或流水数据。
生产环境的流水表，单表容量可能几百 G，上 T，怎样在不影响业务的情况下，优雅地 DROP TABLE 呢（这里优雅的定义为业务耗时上升不超过20%）？
BP 互斥锁
在 MySQL 5.5.23 版本前，当删除一张表时，会去遍历 BP（Buffer Pool 缓冲池）中所有该表对应的页，这时需要持有 BP 的互斥锁，而这是一把巨大的锁，对于 BP 的访问都需要持有该锁才能继续。
随着硬件的发展，128G、256G 的内存都已是常态，若 BP 非常大，如 100G，则遍历 BP 所需要的时间就会非常长，可能是 5 秒，甚至更长。这时，业务对于数据库的请求会直接掉底，直接变为 0。显然，这不符合我们对于优雅的定义。
MySQL 5.5.23 版本开始，做了两点优化：
遍历脏页列表（flush list）时，只持有一小段时间，之后就释放 BP 的大锁，并且不需要真正刷新脏页，这样的处理能让其他业务请求有机会获得大锁的可能，从而业务的请求不会掉底；
不处理 LRU 列表中干净的页。因为 LRU 中不使用的页会慢慢被淘汰，因此删除表，不用同步地去处理对应的页，可以通过 LRU 机制，异步最终淘汰机制。
MySQL 5.6 版本支持了多个 BP 拆分，5.7 版本支持多个脏页列表，因此对于上述 DROP TABLE 的处理，又得到进一步优化，即持有大锁的时间越来越少，对业务的影响也就越来越少。
IO优化
上面的这些优化是针对内存中的锁进行优化，在 DROP TABLE 的过程中，让持有的大锁尽可能的时间缩短，从而避免 QPS 掉底的问题。
然而，想要对业务操作的耗时响应时间控制在 20%，还需要考虑物理 I/O 的影响。
例如，删除一张 100G 的表，意味着要删除 100G 的物理文件，在删除过程中，I/O 的开销也将会是非常巨大，从而影响业务的耗时。
常见优化的思想是在 DROP TABLE 前，对 ibd 文件创建硬链接，这样在 DROP TABLE 时，只会删除 ibd 链接，不会真正删除 ibd 文件，也就不会释放空间。
这样 DROP TABLE 的操作速度就能得到进一步提升。不过，这时存储空间并没有第一时间释放，还是需要选择低峰期删除文件，方能最终释放存储空间。
但同样的问题，虽然异步删除物理文件，但又如何做到最业务的耗时影响最小呢？这时，可以考虑使用 ionice 这个工具，选择在业务低峰期进行 ibd 文件的删除，如：
ionice -c 2 -n 6 rm xxx.ibd
自适应哈希功能
自适应哈希（Adaptive Hash Index，下简称AHI）特性作为一个几乎透明的功能，其实一般用户无需关心，基本可以认为 AHI 是即开即用的功能。
默认 AHI 参数的设置也是比较合理的，例如参数 innodb_adaptive_hash_index_parts 设置为 8 。然而，AHI 存在一个副作用：当删除大表，且缓冲池（Buffer Pool，下简称 BP）比较大，如超过 32G，则 MySQL 数据库可能会有短暂被 hang 住的情况发生。
这时会对业务线程造成一定影响，从而导致业务系统的抖动。
产生这个问题的原因是在删除表的时候，InnoDB 存储引擎会将该表在 BP 中的内存都淘汰掉，释放可用空间。这其中包括数据页、索引页、自适应哈希页等。
当 BP 比较大时，扫描 BP 中 flush_list 链表需要比较长的时间，因此会产生系统的抖动。
所以在海量的互联网并发业务中，删除表操作需要做精细的逻辑控制，如：
业务低峰期删除大表；
删除表前禁用 AHI 功能；
控制脏页链表长度，只有长度小于一定阈值，才发起删除操作；
删除表后启用 AHI 功能。
不过，这么麻烦的处理在 MySQL 8.0.23 版本之后，就都不再需要了。因为官方已经彻底修复了这个问题。
终极删除优化
除了前面的这些对于版本的选择、I/O 的优化、自适应哈希算法的调整，还可以有进一步减小删除大表时发生抖动的可能性。
当前 MySQL 数据库对于删除时内存的控制已经做的很好了，接下去的问题在于还需要进行 I/O操作，从磁盘上删除操作。ionice 等命令依然有 I/O 开销，性能抖动依然不可避免。
如果根据第一性原理，那是不是可以实现不删除表文件，也能释放存储空间呢？为什么要这么辛苦地去释放空间？只需要把原来的物理存储空间给新表使用不就好了吗？
是的，很多人会想到系统共享表空间（System Tablespace），每张表的表空间都存放到系统的系统共享表空间 ibdata1中，这样删除操作就不需要删除物理空间，而释放的空间可以给到其他表使用。
推荐在初始化时，就完成 ibdata1 的空间分配，即固定单个 MySQL 实例能使用到存储空间上限。这样可以省去后续磁盘空间的申请释放，进一步提升在业务高峰期，数据库耗时的平稳性。
innodb_file_per_table = 0
innodb_data_file_path=ibdata1:500G
有可能觉得这样不够灵活，因为用户可能只想日志表、流水表等数据放在一个表空间中，这样 DROP TABLE 时不需要删除物理空间。
这样的话，可以使用 MySQL 中的通用表空间（General Tablespace）：
CREATE TABLESPACE `ts_log` ADD DATAFILE 'ts_log.ibd'
CREATE TABLE log_2020_01 ( ... ) tablespace = ts_log;
CREATE TABLE log_2020_02 ( ... ) tablespace = ts_log;
这样一来，在生产系统上，通过将表存储在共享表空间文件中，这时删除表只是将存储空间释放，并不删除表空间文件本身。
数据库系统迁移
历史数据库
对于海量的互联网业务来说，数据量是非常巨大的。
假设一天 10 亿笔交易，每笔交易至少需要有一条记录，用于记录该笔交易，这就是通常所谓的流水数据。再假设一笔流水占用 1K 存储空间，那么每日流水占用存储空间 1000G，365 天就需要占用 365T 的存储空间。
即便做了分布式数据库架构，有 1000 个分片，每个分片 1 年的流水数据就需要 365G，这还不包括其他数据，仅仅只是一笔交易的流水。而线上的库表和业务就更为复杂了。
有同学或许会说，为什么要存储 1 年以上的数据呢？超过一定期限删除不就可以了吗？想一想，历史微博是不是都是能看的？淘宝历史交易也都是能查询的？对于金融业务来说，每笔历史交易是不是也都可以可以回溯的？
所以，即便是历史的数据，也要求是可以查询的。所以，对于海量的互联网业务来说，除了线上的数据库，还需要有一套对应的历史数据库，用于查询历史的数据。
注意，这里谈论的历史数据库，不是用于数据分析的系统，而仅仅是一个历史查询数据库。
历史数据库架构
从架构角度看，线上数据库系统与历史数据库系统的架构如下图所示：
可以看到，业务会将最新的数据变更插入在线的 MySQL 数据库，但 MySQL 数据库仅保留最近半年的数据，超过半年的数据会存放在历史数据库系统中。对于历史数据库的选型来说，一般可以是（分布式）MySQL 数据库、HBase、ElasticSearch。
历史数据库是 MySQL，那么基本和线上架构一致，只是存储的容量会更大。但缺点是，即便使用分布式架构，数据节点的扩容需要搬迁数据，这部分工作相对比较麻烦一些。
HBase 是 Hadoop 生态的大数据系统，是一个 KV 系统，非常适合进行点查询，其对于数据的扩容也是非常容易。但是它的缺点是不支持事务，没有二级索引，所以在使用时会有诸多限制，比如二级索引需要自己创建索引表，索引表和主表的数据一致性有可能会存在问题。ElasticSearch 是文档数据库，其他特性与 HBase 类似，支持数据节点动态的扩缩容，查询支持也更为丰富一些。
历史数据库的选型需要结合业务具体分析，通常个人更倾向于使用 ElasticSearch，简单易用。
但是，如何将线上的数据迁移到历史数据库呢？这时就需要迁移系统了。
迁移系统
简单来说，迁移系统需要做以下两件事情：
- 将线上数据尽可能地准实时同步到历史数据；
- 清理线上数据库系统，仅保留最近一段时间的数据，如半年的数据。
这时，整体的数据库系统架构如下图所示：
上图中，DTS 是 Data Transfer Service，数据迁移服务，负责将在线数据准实时地同步到历史数据库。
DTS 的实现原理是订阅 MySQL 的二进制日志变化，将数据库的变更同步到历史数据库。虽然原理并不复杂，但是由于是异构数据库系统，在数据消费这里还需要做不少的工作。此外，要做到准实时要求，如仅 30 秒内的延迟，对于 DTS 也是一种考验。
业界的 DTS 有 Maxwell、Canal。但通常这些 DTS 仅负责订阅二进制日志，消费需要用户自己实现存储到不同的数据库。
DAS 是 Data Archive Service，数据归档服务，负责清理超过一定时间的线上数据，同时需要确保清理的数据都已经在历史数据库中。所以，DAS 又需要做两件事情：
获取超过某个阈值的数据，确保这些数据都已经在历史数据库中；
根据调度，在非业务高峰期，从 MySQL 数据库中删除超过某个阈值的数据。
由于在前面的库表结构设计中，要求每张业务核心表都要有一个最后修改时间的字段，因此那些数据已经超过某一时间点，如半年，是比较容易知道的。然后，再进行线上数据与历史数据库的核对即可。
第二个步骤会真实删除线上数据库中的数据，风险较高，但通过多次数据校验和逻辑判断，DAS 可以确保删除数据的正确性，从而正确删除超过阈值的数据。
同时，为了不影响删除时对于业务的影响，DAS 还需要做调度，删除是小批量的，并且是在非业务高峰期执行的操作。
MySQL 修改列为 auto_increment 时存在的问题
在这里再多描述一个作者在 MySQL 官方提交的一个 bug，详细地址如下：column value seems to be wrong when modifying column to auto_increment
该 bug 里描述了在 MySQL 中，当对一个列的属性进行修改了，如果原来的列已经具备了 auto_increment 属性，且原来的列中留有 0 值，则修改后会保留 0 值。但是，如果把一个非 auto_increment 的列修改成 auto_increment，则该过程会将原列的 0 值修改成 1：
mysql> create table x ( a bigint primary key );
Query OK, 0 rows affected (0.50 sec)
mysql> insert into x values (-1),(0),(2);
Query OK, 3 rows affected (0.15 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from x;
+----+
| a |
+----+
| -1 |
| 0 |
| 2 |
+----+
3 rows in set (0.00 sec)
mysql> ALTER TABLE x MODIFY COLUMN a bigint auto_increment;
Query OK, 3 rows affected (2.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from x;
+----+
| a |
+----+
| -1 |
| 1 |
| 2 |
+----+
3 rows in set (0.00 sec)
如果开启 NO_AUTO_VALUE_ON_ZERO 模式，则不会出现这种错误：
mysql> set sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.04 sec)
mysql> create table x ( a bigint primary key );
Query OK, 0 rows affected (0.47 sec)
mysql> insert into x values (-1),(0),(2);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from x;
+----+
| a |
+----+
| -1 |
| 0 |
| 2 |
+----+
3 rows in set (0.00 sec)
mysql> ALTER TABLE x MODIFY COLUMN a bigint auto_increment;
Query OK, 3 rows affected (2.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from x;
+----+
| a |
+----+
| -1 |
| 0 |
| 2 |
+----+
3 rows in set (0.00 sec)
作者认为这是官方的一个 bug，并针对这个 bug 提出了修复意见。
这是原先的相关代码：
/*
If we are going to copy contents of one auto_increment column to
another auto_increment column it is sensible to preserve zeroes.
This condition also covers case when we are don't actually alter
auto_increment column.
*/
if (def->field == from->found_next_number_field)
thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO;
代码仅涵盖将表从 auto_increment 列更改为 auto_increment 列，它不包括将表从非 auto_increment 列更改为 auto_increment 列，就像前面展示的一样。作者认为这里应该修改如下：
- if (def->field == from->found_next_number_field)
- thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO;
+ //if (def->field == from->found_next_number_field)
+ thd->variables.sql_mode |= MODE_NO_AUTO_VALUE_ON_ZERO;
但是MySQL 官方则认为这不是一个错误，而是为了转换的安全考虑，因为存在如果将有符号更改为无符号，则不能有负值这样的场景。目前这个问题尚存在争议。