L8 Operation Interview

1 表结构设计篇 - 数据类型

1-1 数字类型

不推荐使用整型类型的属性 Unsigned，若非要使用，参数 sql_mode 务必额外添加上选项 NO_UNSIGNED_SUBTRACTION；
在海量并发的互联网业务中使用，金额字段的设计并不推荐使用 DECIMAL 类型，而更推荐使用 INT 整型类型
自增整型类型做主键，务必使用类型 BIGINT，而非 INT，后期表结构调整代价巨大；
MySQL 8.0 版本前，自增整型会有回溯问题，做业务开发的你一定要了解这个问题；
- 自增值并不持久化，可能会有回溯现象（MySQL 8.0 版本前）
当达到自增整型类型的上限值时，再次自增插入，MySQL 数据库会报重复错误；
不要再使用浮点类型 Float、Double，MySQL 后续版本将不再支持上述两种类型；
- 类型 DECIMAL 是通过二进制实现的一种编码方式，计算效率远不如整型来的高效。因此，推荐使用 BIG INT 来存储金额相关的字段。
账户余额字段，设计是用整型类型，而不是 DECIMAL 类型，这样性能更好，存储更紧凑。

1-2 字符串类型：不能忽略的 COLLATION

CHAR 和 VARCHAR 虽然分别用于存储定长和变长字符，但对于变长字符集（如 GBK、UTF8MB4），其本质是一样的，都是变长，设计时完全可以用 VARCHAR 替代 CHAR；
- 所以在 MySQL 数据库下，绝大部分场景使用类型 VARCHAR 就足够了
推荐 MySQL 字符集默认设置为 UTF8MB4，可以用于存储 emoji 等扩展字符；
排序规则很重要，用于字符的比较和排序，但大部分场景不需要用区分大小写的排序规则；
- SHOW CHARSET 来查看
修改表中已有列的字符集，使用命令 ALTER TABLE ... CONVERT TO ....；
- ALTER TABLE emoji_test CONVERT TO CHARSET utf8mb4;
用户性别，运行状态等有限值的列，MySQL 8.0.16 版本直接使用 CHECK 约束机制，之前的版本可使用 ENUM 枚举字符串类型，外加 SQL_MODE 的严格模式；
- 'sex' tinyint DEFAULT NULL,. 以前的版本
- 'sex' enum('M','F') COLLATE utf8mb4_general_ci DEFAULT NULL, MySQL 8.0.16 版本
业务隐私信息，如密码、手机、信用卡等信息，需要加密。切记简单的MD5算法是可以进行暴力破解，并不安全，推荐使用动态盐+动态加密算法进行隐私数据的存储。
- $salt$cryption_algorithm$value
- $salt：表示动态盐，每次用户注册时业务产生不同的盐值，并存储在数据库中。若做得再精细一点，可以动态盐值 + 用户注册日期合并为一个更为动态的盐值。
- $cryption_algorithm：表示加密的算法，如 v1 表示 MD5 加密算法，v2 表示 AES256 加密算法，v3 表示 AES512 加密算法等。
- $value：表示加密后的字符串。

1-3 日期类型：TIMESTAMP 可能是巨坑

MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒；
DATETIME 占用 8 个字节，TIMESTAMP 占用 4 个字节，DATETIME(6) 依然占用 8 个字节，TIMESTAMP(6) 占用 7 个字节；
TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07，业务用 TIMESTAMP 存在风险；
使用 TIMESTAMP 必须显式地设置时区，不要使用默认系统时区，否则存在性能问题，推荐在配置文件中设置参数 time_zone = '+08:00'；

[mysqld]

time_zone = "+08:00"

强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段，并设置修改自动更新机制，即便标识每条记录最后修改的时间。

last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型；

1-4 非结构存储：用好 JSON

JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题

使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；
JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
JSON 数据类型推荐使用在不经常更新的静态数据存储。

2 表结构设计篇 - 表设计

2-1 忘记范式准则

在 MySQL 海量并发的工程实践上，表结构设计应遵循这样几个规范：

一范式要求所有属性都是不可分的基本数据项；\ 二范式解决部分依赖； \ 三范式解决传递依赖。
每张表一定要有一个主键（方法有自增主键设计、UUID 主键设计、业务自定义生成主键）；自增主键设计
每张表一定要有一个主键；
自增主键只推荐用在非核心业务表，甚至应避免使用；
- 所以，如果你想获得自增值的最大并发性能，把参数 innodb_autoinc_lock_mode 设置为2。
核心业务表推荐使用 UUID 或业务自定义主键；
- UUID（Universally Unique Identifier）代表全局唯一标识 ID。显然，由于全局唯一性，你可以把它用来作为数据库的主键。
- UUID = 时间低（4字节）- 时间中高+版本（4字节）- 时钟序列 - MAC地址
- 需要特别注意的是，在存储时间时，UUID 是根据时间位逆序存储，也就是低时间低位存放在最前面，高时间位在最后，即 UUID 的前 4 个字节会随着时间的变化而不断“随机”变化，并非单调递增。
一份数据应尽可能保留一份，通过主键关联进行查询，避免冗余数据；
在一些场景下，可以通过 JSON 数据类型进行反范式设计，提升存储效率；

2-2 表压缩：不仅仅是空间压缩

MySQL 中的压缩都是基于页的压缩；
- 而基于页的压缩，既能提升压缩效率，又能在性能之间取得一种平衡。
- 借助页压缩技术，MySQL 可以把一个 16K 的页压缩为 8K，甚至 4K，这样在从磁盘写入或读取时
COMPRESS 页压缩适合用于性能要求不高的业务表，如日志、监控、告警表等；压缩比例通常能达到 50% 左右。
- COMPRESS 页压缩是 MySQL 5.7 版本之前提供的页压缩功能。只要在创建表时指定ROW_FORMAT=COMPRESS，并设置通过选项 KEY_BLOCK_SIZE 设置压缩的比例。
- 下面这是一张日志表，ROW_FROMAT 设置为 COMPRESS，表示启用 COMPRESS 页压缩功能，KEY_BLOCK_SIZE 设置为 8，表示将一个 16K 的页压缩为 8K。

ROW_FORMAT=COMPRESSED
KEY_BLOCK_SIZE=8

COMPRESS 页压缩内存缓冲池存在压缩和解压的两个页，会严重影响性能
对存储有压缩需求，又希望性能不要有明显退化，推荐使用 TPC 压缩；
TPC（Transparent Page Compression）是 5.7 版本推出的一种新的页压缩功能，其利用文件系统的空洞（Punch Hole）特性进行压缩。可以使用下面的命令创建 TPC 压缩表：
- COMPRESSION=ZLIB | LZ4 | NONE;
通过 ALTER TABLE 启用 TPC 压缩后，还需要执行命令 OPTIMIZE TABLE 才能立即完成空间的压缩。

ALTER TABLE Transaction202102 COMPRESSION=ZLIB；

OPTIMIZE TABLE Transaction202102;

2-3 表的访问设计 SQL 还是 NoSQL

通过 SQL、Memcache 协议、X Protocol 访问 MySQL 中的表，即我们可以将 MySQL 打造成一个关系型数据库、KV 数据库、文档数据库，但底层都是通过表格的方式进行数据的存储，并且数据都存储在 InnoDB 引擎中。

还在使用 Memcached、MongoDB 数据库的同学可以考虑将数据迁移到 MySQL，这样能在兼容原有业务的前提下，使用到 InnoDB 存储引擎的高并发、事务安全、数据复制等高级功能。

通过 Memcached 协议访问表
- 基于 Memcached 的 KV 访问，可以绕过 SQL 解析，通过映射关系，直接访问存储在 InnoDB 引擎中的数据，这样数据库的整体性能会在不花费额外成本的前提下得到极大的提升。
- 基于 Memcached 的 KV 访问方式比传统的 SQL 方式要快54.33%，而且CPU 的开销反而还要低20%。
通过 X Protocol 访问表
同时，MySQL 也提供了 X Protocol 这样的 NoSQL 访问方式，所以，现在我们 MySQL 打造成一个SQL & NoSQL的文档数据库。

3 索引优化

3-1 索引：排序

索引是加快查询的一种数据结构，其原理是插入时对数据排序，缺点是会影响插入的性能；
MySQL 当前支持 B+树索引、全文索引、R 树索引；
B+ 树索引的高度通常为 3~4 层，高度为 4 的 B+树能存放 50 亿左右的数据；
由于 B+ 树的高度不高，查询效率极高，50 亿的数据也只需要插叙 4 次 I/O；
MySQL 单表的索引没有个数限制，业务查询有具体需要，创建即可，不要迷信个数限制；
可以通过表 sys.schema_unused_indexes 和索引不可见特性，删除无用的索引。

SELECT * FROM schema_unused_indexes

WHERE object_schema != 'performance_schema';

而 MySQL 8.0 版本推出了索引不可见（Invisible）功能。

ALTER TABLE t1 

ALTER INDEX idx_name INVISIBLE/VISIBLE;

3-2 索引组织表

从 MySQL 5.7 版本开始，MySQL 就开始支持创建函数索引（即索引键是一个函数表达式）。函数索引有两大用处：

优化业务 SQL 性能；
配合虚拟列（Generated Column)

你应该了解到MySQL InnoDB 存储引擎是索引组织表，以及索引组织表和堆表之间的区别。

索引组织表主键是聚集索引，索引的叶子节点存放表中一整行完整记录；
- 除了主键索引外，其他的索引都称之为二级索引（Secondeary Index），或非聚集索引（None Clustered Index）。
除主键索引外的索引都是二级索引，索引的叶子节点存放的是（索引键值，主键值）；
- 通过二级索引 idx_name 只能定位主键值，需要额外再通过主键索引进行查询，才能得到最终的结果。

PRIMARY KEY(id), -- 主键索引
KEY idx_name(name) -- 二级索引

由于二级索引不存放完整记录，因此需要通过主键值再进行一次回表才能定位到完整数据；
- 这种“二级索引通过主键索引进行再一次查询”的操作叫作“回表”
索引组织表对比堆表，在海量并发的OLTP业务中能有更好的性能表现；
- “索引组织表，数据即索引，索引即数据”。那么为了便于理解二级索引，你可以将二级索引按照一张表来进行理解，比如索引 idx_name 可以理解成一张表
每种不同数据，对二级索引的性能开销影响是不一样的；
有时通过函数索引可以快速解决线上SQL的性能问题；
虚拟列不占用实际存储空间，在虚拟列上创建索引本质就是函数索引。

3-3 组合索引

组合索引（Compound Index）是指由多个列所组合而成的 B+树索引，这和我们之前介绍的B+ 树索引的原理完全一样，只是之前是对一个列排序，现在是对多个列排序。

归纳组合索引的三大优势

覆盖多个查询条件，如（a，b）索引可以覆盖查询 a = ?或者 a = ? and b = ?；
避免 SQL 的额外排序，提升 SQL 性能，如 WHERE a = ? ORDER BY b 这样的查询条件；
利用组合索引包含多个列的特性，可以实现索引覆盖技术，提升 SQL 的查询性能，用好索引覆盖技术，性能提升 10 倍不是难事。

3-4 索引出错

MySQL 优化器是 CBO 的；，这种优化器称之为：CBO（Cost-based Optimizer，基于成本的优化器）。
MySQL 优化器认为从磁盘读取的开销是内存开销的 4 倍。
MySQL 会选择成本最低的执行计划，你可以通过 EXPLAIN 命令查看每个 SQL 的成本；
一般只对高选择度的字段和字段组合创建索引，低选择度的字段如性别，不创建索引；
低选择性，但是数据存在倾斜，通过索引找出少部分数据，可以考虑创建索引；
若数据存在倾斜，可以创建直方图，让优化器知道索引中数据的分布，进一步校准执行计划

4 查询优化

MySQL 数据库中支持 JOIN 连接的算法有 Nested Loop Join 和 Hash Join 两种，前者通常用于 OLTP 业务，后者用于 OLAP 业务。

在 OLTP 可以写 JOIN，优化器会自动选择最优的执行计划。但若使用 JOIN，要确保 SQL 的执行计划使用了正确的索引以及索引覆盖，因此索引设计显得尤为重要，这也是DBA在架构设计方面的重要工作之一。

Left Join 来说，驱动表就是左表 R； Right Join 中，驱动表就是右表 S。这是 JOIN 类型决定左表或右表的数据一定要进行查询。但对于 INNER JOIN，驱动表可能是表 R，也可能是表 S。

分区表

当前 MySQL 的分区表支持 RANGE、LIST、HASH、KEY、COLUMNS 的分区算法；
分区表的创建需要主键包含分区列；
在分区表中唯一索引仅在当前分区文件唯一，而不是全局唯一；
分区表唯一索引推荐使用类似 UUID 的全局唯一实现；
分区表不解决性能问题，如果使用非分区列查询，性能反而会更差；
推荐分区表用于数据管理、速度快、日志小。

分区表并不是用于提升性能的一种手段，它是方便管理数据的一种方式。

5 高可用架构 - MySQL 复制

5-1 MySQL 复制架构

二进制日志记录了所有对于 MySQL 变更的操作；
- MySQL 数据库是基于二进制日志（binary log）进行数据增量同步，而二进制日志记录了所有对于 MySQL 数据库的修改操作。
- 在默认 ROW 格式二进制日志中，一条 SQL 操作影响的记录会被全部记录下来，比如一条 SQL语句更新了三行记录，在二进制日志中会记录被修改的这三条记录的前项（before image）和后项（after image）。
- 你可以通过二进制日志记录看到被删除记录的完整信息，还有每个列的属性，比如列的类型，是否允许为 NULL 值等。
- 如果是 UPDATE 操作，二进制日志中还记录了被修改记录完整的前项和后项，
可以通过命令 SHOW BINLOG EVENTS IN ... FROM ... 查看二进制日志的基本信息；
可以通过工具 mysqlbinlog 查看二进制日志的详细内容；
复制搭建虽然简单，但别忘记配置 crash safe 相关参数，否则可能导致主从数据不一致；
- Master 服务器会把数据变更产生的二进制日志通过 Dump 线程发送给 Slave 服务器；
- Slave 服务器中的 I/O 线程负责接受二进制日志，并保存为中继日志；
- SQL/Worker 线程负责并行执行中继日志，即在 Slave 服务器上回放 Master 产生的日志。
异步复制用于非核心业务场景，不要求数据一致性；
无损半同步复制用于核心业务场景，如银行、保险、证券等核心业务，需要严格保障数据一致性；
- 半同步复制并不是 MySQL 内置的功能，而是要安装半同步插件，并启用半同步复制功能，设置 N 个 Slave 接受二进制日志成功
多源复制可将多个 Master 数据汇总到一个数据库示例进行分析；
延迟复制主要用于误操作防范，金融行业要特别考虑这样的场景。
- 而延迟复制却允许Slave 延迟回放接收到的二进制日志
- CHANGE MASTER TO master_delay = 3600

5-2 高可用设计

高可用是系统所能提供无故障服务的一种能力，度量单位是几个 9；
线上系统高可用目标应不低于 99.995%，否则系统频繁宕机，用户体验不好；
高可用实现基础是：冗余 + 故障转移；
无状态服务的高可用设计较为简单，直接故障转移或剔除就行；
数据库作为有状态的服务，设计比较复杂（冗余通过复制技术实现，故障转移需要对应的高可用套件）；
- 为了在故障转移后对 Service 服务无感知，所以需要引入 VIP（Virtual IP）虚拟 IP 技术，当发生宕机时，VIP 也需要漂移到新的主服务器。
数据库高可用有三大架构设计，请务必牢记这几种设计。
- 基于数据层的数据库高可用架构
- 基于业务层的数据库高可用架构
- 融合的高可用架构设计

5-3 读写分离设计

基于主从复制机制搭建一个读写分离架构，总的来说：

MySQL 二进制日志是一种逻辑日志，便于将数据同步到异构的数据平台；
逻辑日志在事务提交时才写入，若存在大事务，则提交速度很慢，也会影响主从数据之间的同步；
在 MySQL 中务必将大事务拆分成小事务处理，这样才能避免主从数据延迟的问题；
- 设计时，把 DELETE 删除操作转化为 DROP TABLE/PARTITION 操作；
- 业务设计时，把大事务拆成小事务。
通过配置 MTS 并行复制机制，可以进一步缩短主从数据延迟的问题，推荐使用 MySQL 5.7版本，并配置成基于 WRITESET 的复制；
主从复制延迟监控不能依赖 Seconds_Behind_Master 的值，最好的方法是额外配置一张心跳表；
- 想要实时准确地监控主从复制延迟，可以在主服务器上引入一张心跳表 heartbeat，用于定期更新时间（比如每 3 秒一次）。于主从复制机制，主机上写入的时间会被复制到从机，这时对于主从复制延迟的判断可以根据如下规则：
- 主从延迟 = 从机当前时间 - 表 heartbeat 中的时间
读写分离是一种架构上非常常见的方法，你一定要掌握，并做好读写分离架构失效情况下的兜底设计。
- 读写分离设计是指：把对数据库的读写请求分布到不同的数据库服务器上。对于写入操作只能请求主服务器，而对读取操作则可以将读取请求分布到不同的从服务器上
- 读写分离设计的前提是从机不能落后主机很多，最好是能准实时数据同步，务必一定要开始并行复制，并确保线上已经将大事务拆成小事务。

6 MySQL高可用架构

6-1 金融级高可用架构 - 数据核对

核心业务复制务必设置为无损半同步复制；
- 一条数据都不允许丢失（一般也把这种数据复制方式叫作“强同步”）。么要想实现数据的强同步，在进行复制的配置时，就要使用无损半同步复制模式。
- 在 MySQL 内部就是要把参数 rpl_semi_sync_master_wait_point 设置成 AFTER_SYNC 。
同城容灾使用三园区架构，一地三中心，或者两地三中心，机房见网络延迟不超过 5ms；
跨城容灾使用“三地五中心”，跨城机房距离超过 200KM，延迟超过 25ms；
跨城容灾架构由于网络耗时高，因此一般仅用于读多写少的业务，例如用户中心；
除了复制进行数据同步外，还需要额外的核对程序进行逻辑核对；
数据库层的逻辑核对，可以使用 last_modify_date 字段，取出最近修改的记录。
- 数据在业务逻辑上一致：这个保障业务是对的；
- 主从服务器之间的数据一致：这个保障从服务器的数据是安全的、可切的。
- 主从服务器之间的核对，是由数据库团队负责的。

6-2 高可用套件

MHA(Master High Availability)是一款开源的 MySQL 高可用程序，它为 MySQL 数据库主从复制架构提供了 automating master failover 的功能。
MHA Manager 和 MHA Node 的通信是采用 ssh 的方式，也就是需要在生产环境中打通 MHA Manager 到所有 MySQL 节点的 ssh 策略，那么这里就存在潜在的安全风险。
Orchestrator 是另一款开源的 MySQL 高可用套件，除了支持 failover 的切换，还可通过Orchestrator 完成 MySQL 数据库的一些简单的复制管理操作
其基本实现原理与 MHA 是一样的，只是把元数据信息存储在了元数据库中，并且提供了HTTP 接口和命令的访问方式，使用上更为友好。

6-3 InnoDB Cluster

InnoDB Cluster。这种高可用解决方案大概率会成为下一代金融场景的标准数据库高可用解决方案，InnoDB Cluster 底层是 MGR，通过类 Paoxs 算法进行数据同步，性能更好，且能保证数据的完整性。

结合管理工具 MySQL Shell，路由工具 MySQL Router 能构建一个完整的 MySQL 高可用解决方案。

对于金融用户来说，我非常推荐这种高可用解决方案。当然，我建议在最新的 MySQL 8.0 版本中使用 InnoDB Cluster。

6-4 数据库备份

第一步要做好：线上数据库与离线备份系统的权限隔离。
逻辑备份
- mysqldump -A --single-transaction > backup.sql
要恢复逻辑备份非常简单，就是执行文件中的 SQL 语句，这时可以使用下面的 SQL：'
- mysql < backup.sql
物理备份直接备份数据库的物理表空间文件和重做日志，不用通过逻辑的 SELECT 取出数据。所以物理备份的速度，通常是比逻辑备份快的，恢复速度也比较快。
增量备份: “全量备份 + 增量备份”的方式，构建完整的备份策略。增量备份就是对日志文件进行备份，在 MySQL 数据库中就是二进制日志文件。就可以实现基于时间点的恢复（point in time recovery），也就是“通过全量 + 增量备份”可以恢复到任意时间点。