1目前mysql现状

初期的 MySQL 存储方案

在系统设计之初，基于对数据量的预估以及简化实现方案考虑，我们选用了高可用的 MySQL RDS 存储方案，当时的匹配逻辑主要通过 SQL 语句来实现，包含了很多联表查询和聚合操作。当数据量在千万级别左右，系统运行良好，基本响应还在一分钟内。

基本架构

MySQL 使用的现状与问题

随着业务的发展，部门内各应用服务产生的数据量也在快速增长。业务落地数据量不断激增，导致单机 MySQL 不可避免地会出现性能瓶颈。主要体现在以下几个方面：

容量
- 单机 MySQL 实例存储空间有限，想要维持现有架构就得删除和轮转旧数据，达到释放空间的目的；
- 某些场景单表容量达到 70GB 以上，某些数据需永久保存，同时也需要保持在线实时查询，按照之前的存储设计会出现明显的瓶颈。
性能
- 最大单表 1 亿行，行数过大，导致读写性能受到影响。
扩展性
- MySQL 无法在线灵活扩展，无法解决存储瓶颈。
SQL 复杂
- 大表轮转后出现多个分表，联合查询时需要 join 多个分表，SQL 非常复杂并难以维护；
- 单机 MySQL 缺乏大规模数据分析的能力。

根据数据量的增长情况来看，分布式数据库会是很好的解决方案。首先考虑的是业务的垂直及水平拆分或者基于 MySQL 的数据库中间件方案和一些主流的 NoSQL 方案。

但是仔细评估后，最先排除掉的是业务水平拆分的方案，因为业务逻辑中包含大量的关联查询和子查询，如果拆表后这些查询逻辑就没有办法透明的兼容，而且是比较核心的业务系统，时间精力的关系也不允许整体做大的重构。中间件的问题和分库分表的问题类似，虽然解决了大容量存储和实时写入的问题，但是查询的灵活度受限，而且多个 MySQL 实例的维护成本也需要考虑。

二、数据库选型

2.1 调研目标

针对目前存储架构存在的问题，有需要使用其他存储方案的可能。考虑到目前的业务与 MySQL 高度耦合，对数据库选型的主要要求有：

必须兼容 MySQL 协议；
支持事务，保证任务以事务为维度来执行或遇错回滚；
支持索引，尤其是二级索引；
扩展性，支持灵活在线扩展能力，包括性能扩展和容量扩展。

其他要求：

稳定性和可靠性；
备份和恢复；
容灾等。

2.2 可选方案

序号	方案	说明
1	MySQL 分库分表	基于 MySQL
2	MySQL Cluster	基于 MySQL
3	MySQL + Vitess	基于 MySQL
4	MySQL + MyCAT	基于 MySQL
6	TiDB	兼容 MySQL 协议

2.3 测试

2.3.1 基于 MySQL 的解决方案

一开始仍然是倾向使用基于 MySQL 的解决方案，比如 MySQL InnoDB Cluster 或 MySQL + 中间件的方案。

我们测试了 MySQL 集群 5.7.25 版本对比 8.0.12 版本，在 128 并发写各 1000 万行的 10 个表，比较单节点、3 节点和 5 节点下的情况，如下图所示：

图 3 对比结果

在测试中发现，使用 MySQL InnoDB 集群的方案写性能比单机 MySQL 差约 30%，其他的读写测试结果也不甚满意。之后陆续测试 MySQL InnoDB Cluster 或 MySQL + 中间件的方案，不是测试结果性能不达要求，就是需要修改大量代码。

因此我们得出了基于 MySQL InnoDB Cluster 或 MySQL + 中间件的方案的不满足我们的业务场景的结论。总结来说，我们不使用 MySQL 分库分表、中间件或 MySQL 集群，原因主要是以下两点：

方案过于复杂
需要改业务代码

仔细分析来看，其实基于 MySQL InnoDB Cluster 或 MySQL + 中间件的方案，本质上是 MySQL 主从结构的延伸，并非真正的分布式拓展，像是以打“补丁”的方式来实现横向扩展，很多功能特性自然也难以让人满意。

TiDb

2.3.3 最终选型

综合对比结果如下表：

数据库	扩展	TP	AP	文档程度	社区活跃度
MySQL	否	是	否	丰富	高
PostgreSQL	否	是	是	丰富	高
MySQL InnoDB Cluster	否	是	否	少	低
MysQL + 中间件 Vitess	是	是	否	丰富	中
TiDB	是	是	是	丰富	高

经过谨慎的考量，我们选择了 TiDB。

图 9 选择 TiDB

选择 TiDB 的重要理由

三、TiDB 的使用

3.1 TiDB 使用架构

TiDB 的架构设计如下：

图 10 基于 TiDB 的架构设计

整个集群分为 TiDB、TiKV 和 PD 3 个模块分层部署；
使用 Nginx 作为前端负载均衡。

3.2 TiDB 解决了哪些需求

需求	解决方案
大容量存储	TiDB 在线实时伸缩，无需停机和频繁清理数据
性能需求	TiDB 支持多点读写，QPS 随节点数量增加而增长，理论上无限制
高可用需求	TiDB 强一致性多副本存储，节点宕机不影响服务
数据备份和容灾	数据多副本；支持跨机房、跨地域部署；Mydumper + Loader 多线程并发导出和恢复
分库分表	无需分库分表，天然支持超大表的高效读写
打破数据壁垒	支持

四、最佳实践分享

4.1 集群管理

K8s
- 使用 TiDB Operator 可以在私有云和公有云上一键管理。

4.2 运维实践

4.2.1 Prometheus 监控

官方集成了 Prometheus + Grafana 的实时监控平台，从集群的各个方面进行了完善的监控，包括：

服务器基础资源的监控：内存、CPU、存储空间、IO 等；
集群组件的监控：TiDB、PD、TiKV 等；
数据监控：实时同步流、上下游数据一致性检验等。

PD 监控示意图如下，集群管理员可以很方便地掌握集群的最新状态，包括集群的空间 Region 等所有情况。

图 11 最佳运维实践：Prometheus 实时监控

如果集群运行过程出错，在监控面板上很容易就发现，下图是使用过程中的一个案例：

图 12 最佳运维实践案例

应用访问 TiDB 写入数据时发现特别慢，读请求正常。排查后，根据 TiKV 面板发现 Raft Store CPU 这项指标异常。深入了解原因是因为数据库副本复制是单线程操作，目前已经到了集群的瓶颈。解决办法有以下两点：

Region 数量过多，Raft Store 还要处理 heartbeat message。

解决方法：删除过期数据。
Raft Store 单线程处理速度跟不上集群写入速度。

解决方法：从 2.1.5 升级到 2.1.15，开启自动 Region Merge 功能。

4.2.2 部分运维问题及解决方案

问题	问题版本	原因及解决方案
大表建索引时对业务有影响	2.0	官方建议在业务低峰期操作，在 2.1 版本中已经增加了操作优先级以及并发读的控制，情况有改善。
上下游表结构不一致，同步异常	2.1	TiDB 下游表比上游 MySQL 多增加几列时，DM 同步异常，其无法按指定列同步(insert into A (a,b,c) values ...)。官方表示已经在增加该功能，预计 2019 年 Q4 推出支持上下游表结构不一致的版本。
在一个 DDL 里不能对多个列或者多个索引做操作	2.1	ADD/DROP INDEX/COLUMN 操作不支持同时创建或删除多个索引或列，需要拆分单独执行，官方表示 3.0 版本有计划改进。
重启 PD 节点，业务报 PD server timeout	2.1	重启 Leader 节点前需手动切换 Leader。官方建议通过重启前做 Leader 迁移来减缓，后续也会对通讯相关参数进行优化。
建表语句执行速度相比 MySQL 较慢。	2.0 & 2.1	多实例 TiDB 部署时，DDL Owner 和接收Create 语句的 Server 不是同一个时间，可能比 MySQL慢一些，耗时约 0.5s，官方表示会再完善。
Delete 大量数据，GC 跟不上	2.1	GC 是单线程的，当删除数据量非常大时会导致 GC 速度较慢，很可能 GC 的速度跟不上写入，可通过扩容和缩短 GC 周期间隔解决，长期需要实现分布式 GC，官方表示对此已经在 3.0 版本实现。
存储空间放大	2.1 & 2.0	该问题属于 RocksDB。RocksDB 的空间放大系数最理想的值为 1.111。官方建议在合适场景下通过 TiKV 开启 RocksDB 的 dynamic-level-bytes 以减少空间放大。
Truncate Table 空间无法完全回收	2.0	Truncate 一张大表后，发现 2 个现象：一是空间回收较慢，二是最终也没有完全回收。目前 2.1 版本优化底层 RocksDB 的机制，使用 DeleteFilesInRange 接口删除整个表占用的空间，然后清理少量残留数据，已经解决。

4.4 数据迁移

4.4.1 MySQL 到 TiDB

图 14 数据从 MySQL 迁移到 TiDB

MySQL 数据库迁移到 TiDB 分为两个部分：全量和增量。

全量
- 使用工具（Mydumper 或 MySQL Dump 等）从 MySQL 导出数据，并且记录当前数据的 binlog 位置；
- 使用工具（Loader 或 Lightning 等）将数据导入到 TiDB 集群；
- 可以用作数据的备份和恢复操作。
增量
- TiDB 伪装成为上游 MySQL 的一个 Slave，通过工具（Syncer 或 DM）实时同步 binlog 到 TiDB 集群；
- 通常情况上游一旦有数据更新，下游就会实时同步过来。同步速度受网络和数据量大小的影响。

4.4.2 数据迁出 TiDB

图 15 数据迁出 TiDB

如果数据需要反向导入或同步，可以利用 TiDB Binlog 工具将 TiDB 集群的 binlog 同步到 MySQL。TiDB Binlog 支持以下功能场景：

数据同步：同步 TiDB 集群数据到其他数据库；
实时备份和恢复：备份 TiDB 集群数据，同时可以用于 TiDB 集群故障时恢复。

导入的方式：

全量：TiDB 兼容 MySQL 协议，在 MySQL 容量足够大的情况下，也可用工具将数据从 TiDB 导出后再导入 MySQL。
增量：打开 TiDB 的 binlog 开关，部署 binlog 收集组件（Pump+Drainer），可以将 binlog 数据同步到下游存储架构（MySQL、TiDB、Kafka、S3 等）。

4.5 优雅地「去分库分表」

图 16 去分库分表举例

举例：一个超级大表按天分表，现在打算查询某个账号一年间的信息。

上游 MySQL

SELECT xx FROM HFeeall join HFee20190101 join ... join ...join ... join HFee20190917 WHERE xx;
复制代码

需要连接 N 个 join 条件，查询需要等待较长时间。

下游 TiDB
```
SELECT xx  FROM SuperHfeeall WHERE xx ;
复制代码
```
应用此方案，最大单表 700+GB，13+ 亿行，索引查询秒返回。

五、总结与展望

TiDB 兼容 MySQL 协议，支持 TP/AP 事务且扩展性好，能很好地解决业务大容量、高可用等问题。

TIDB的读缓存效率不大，所有的读取操作基本上可以认为是走物理IO的。所以不是一个资源友好的系统。TIDB 的优势在于可扩展，分布式。动态扩容。

TIDB 申请的原则

1、单MYSQL库可以承接的应用，不上TIDB.

2、数据量在1TB以上， 1Tb 以下的库单库MYSQL 可以支撑，不允许上TIDB

3 、写冲突较多的应用（存在大量并发更新同样记录的场景），不允许上TIDB .

4、压力非常大的OLAP 业务，不能上TIDB

主键冲突问题	问题：wntd 插入发现事务冲突，逻辑上不可能，实际上出现了跟官方确认为BUG, 因为自增id和隐藏主键使用同一个分配器，在定义自增主键而实际应用生成主键时会触发BUG
mfmdb效率问题	MFMDB 大量全表扫描， TIKV 主机CPU使用率较高。做主动优化。 1、SQL性能问题，优化后解决。 2、集群热点问题，经过测试， SQL性能问题是由分区表导致。取消后，性能上升10倍
----------	------------------------------------------------------------------------------------------------

mfmdbSQL存在索引但是不走索引	经多次分析，语句中存在强类型转换， TIDB 的CBO 不会生成较优的执行计划	绝对禁止在语句中存在强类型转换，会导致索引失效

[TIDB 坑之一：执行计划固化 BINDING 不生效]

TIDB 的优化器做的不好，经常会有走错索引，关联顺序搞错的问题， TIDB 官方也意识到了这个问题，所以在4.0 以后的版本上，加上了通过BINDING 执行计划固化的功能。让DBA 有手段去介入优化器内部的行为，让SQL 走上正确的执行计划。这个BINGDING 初衷是好的，但是在日常运维中，会发现即使有时候虽然创建了BINDING ，执行计划仍然走错的。生产的一套环境上面，就发现了类似问题。

1、在BINDING 不生效的情况下，首先考虑解决问题，所以对SQL 涉及的表做了统计信息分析, 希望优化器能给力点，计划搞正确。

analyze table tt_define_info;

analyze table tt_matter_base_info;

总结：

这个案例，可以得到两个结论

1、TIDB 的优化器算法，真的烂，尤其是在表连接的时候，生成执行计划就简直就是在碰运气, 所以能不用还是尽量不要用表连接

2、如果绑定计划，需要特别注意创建BINDING 时的客户端和表的字符集，使用高版本的客户端，连接使用的字符集跟表的字符集保持一致。。必要的时候可以查看BINDING的文本，要满足标准化后文本一致的条件。

mysql和TiDb选型