原文来源：https://tidb.net/blog/0279fbc9

作者：靳献旗，汽车之家 DBA

1. 背景

用户行为浏览历史记录表是用于存放手机登录汽车之家 APP 的信息，例如我今天使用 iPhone 11 浏览了汽车之家 10 个页面，则会记录到这个表 10 条记录 (如下图所示)。每天大约有 1 亿多条数据会记录到表中，只需要保留 30 天的数据即可。因此我们刚开始采取的是分区表的方案，使用 Range 分区，每天自动创建 7 天之后的分区，删除 30 天之前的分区。Range 分区的优点这里就不多说了，其中之一就是删除数据非常方便，且避免带来性能问题，一条命令可以删除一亿条数据，即一个分区。Hash 分区有利于打散数据，提高读取性能。

本文通过一个分区表线上的优化案例，来分析不同类型分区表的特点，执行细节，希望对选择 TiDB 并需要使用分区表的用户，提供设计思路。

2. 集群信息

本节简单介绍下承载用户行为历史记录表的 TiDB 集群信息，当然，集群上还承载了很多业务，如好友粉丝，关注业务等。

集群主要组件如下：

集群架构如下：

说明

LVS 后端有多个 TiDB 节点用于业务访问，1 个 Drainer 实时同步 TiDB 集群数据到磁盘。

3. 分区表简介

既然本文是关于分区表优化的，这里简单介绍下 TiDB 的分区表。

3.1 分区类型

TiDB 3.0 支持的分区类型包括 Range 分区和 Hash 分区。Range 分区可以用于解决业务中大量删除带来的性能问题，支持快速删除分区。Hash 分区则可以用于大量写入场景下的数据打散。

一个表按 range 分区是指，对于表的每个分区中包含的所有行，按分区表达式计算的值都落在给定的范围内。Range 的范围之间必须是连续的，并且不能有重叠，通过使用 VALUES LESS THAN 操作进行定义。

Hash 分区主要用于保证数据均匀地分散到一定数量的分区里面。在 range 分区中你必须为每个分区指定值的范围；在 hash 分区中，你只需要指定分区的数量。

3.2 兼容性

1. 目前 TiDB 里面只实现了 Range 分区和 Hash 分区，其它的 MySQL 分区类型比如 List 分区和 Key 分区尚不支持。

2. 对于 Range Columns 类型的分区表，目前只支持单列的场景。

3. 分区管理方面，只要底层实现可能会涉及数据挪动的操作，目前都暂不支持。包括且不限于：调整 Hash 分区表的分区数量，修改 Range 分区表的范围，合并分区，交换分区等。

4. 对于暂不支持的分区类型，在 TiDB 中建表时会忽略分区信息，以普通表的形式创建，并且会报 Warning。

5. 系统表 INFORMATION_SCHEMA.PARTITION 暂不支持。

6. Load Data 暂时不支持分区选择，普通的 Load Data 操作在 TiDB 中是支持的。

7. 对于分区表，select * from t 的返回结果是分区之间无序的。这跟 MySQL 不同，MySQL 的返回结果是分区之间有序，分区内部无序。

4. 遇到的问题

使用了一段时间 Range 分区后，随着数据量越来越多，访问量的增大，业务端接口 TP 99 不达标，达不到公司要求。起初我们只是通过 Range 分区实现了相关功能，性能未能达到要求，还需要进一步优化。

下面是业务场景上常用的一个 SQL ：

select column_1,column_2,column_3 from table_name_range where device_id = '862756047529759' AND ( user_id = 123 OR user_id = 0 ) AND view_mark IN ( '01' );

因为按天分区，而 SQL 没带分区字段，所以需要扫描所有分区，响应时间是 0.02 秒，下面是执行计划:

5. 解决办法

既然 Range 分区无法过滤特定分区，我们可以尝试改造为 Hash 分区，因为每一个查询里都会带 device id ，因此，我们只需要在查询语句里把 device id 的 hash 值 (分区键) 也带上即可，这样就避免了扫描所有分区，主要步骤如下：

(1) 创建 Hash 分区表，按照 device_id 的 Hash 值分 30 个区，表结构如下

CREATE TABLE `table_name_hash` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',  `device_id` varchar(100) DEFAULT NULL COMMENT 'xxxxx',  `hc_deviceid` bigint(20) NOT NULL DEFAULT '1' COMMENT 'device_id 的 hashcode',   ....这里省略了部分字段  PRIMARY KEY (`id`,`hc_deviceid`),   ....这里省略了部分索引) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin SHARD_ROW_ID_BITS=4 AUTO_INCREMENT=8033064588 COMMENT='用户行为历史表'PARTITION BY HASH( `hc_deviceid` )PARTITIONS 30;

(2) 程序端双写，同时写 Range 分区表和 Hash 分区表，写 Hash 分区表时带上 device_id 的 Hash 值

相应的，查询 SQL 改造为：

select column_1,column_2,column_3 from table_name_hash where device_id = '862756047529759' AND hc_deviceid=2051717202 AND ( user_id = 123 OR user_id = 0 ) AND view_mark IN ( '01' );

改造后的执行计划如下:

从执行计划看到，只扫描了 p12 分区，理论上应该比 Range 分区速度要快，接下来我们测试一下，看下实际表现如何。

6. 测试

程序端双写 30 天后，Range 分区表和 Hash 分区表的数据量满足了业务需求，如下表所示：

6.1 手动测试

下面是手动测试结果，每个设备 ID 连续查询三次，记录响应时间

从测试结果可以得知：Hash 分区的性能明显优于 Range 分区，性能提升一倍左右。

6.2 性能测试

下面是使用 60 个 device id 在线上接口 50 个并发压测的结果：

(1)Range 分区，平均响应时间是 47ms，99% 响应时间是 193ms

(2)Hash 分区，平均响应时间是 19ms，99% 响应时间是 73ms

下面是 tidb 集群 duration 监控图，可以看到 Hash 分区压测时的尖刺明显小于 Range 分区的尖刺

SQL 80 响应时间更明显

从以上测试可以看到，由 Range 分区改造为 Hash 分区后，响应时间提升非常明显 (至少一倍)，满足接口 TP 99 要求。

Hash 分区响应时间有明显优势，同时也带来了新的问题，即每天删除一亿数据有点麻烦，目前我们是这样做的:

表数据的删除工作放在凌晨进行，经过多轮测试，使用 10 个线程并发，每个线程批量删除 100 行数据，对集群响应时间影响很小，可以接受，删除一亿数据大约需要 6 个小时。注意并发和批量不要设置的太高，否则会对集群的响应时间影响较大。

并且由于 TiDB 是多版本 MVCC 存储，删除的数据空洞会自动通过 compaction 操作自动回收，不会出现 MySQL 需要定期处理数据空洞的问题。

6.3 上线后的效果

下面是接口响应时间，可以看出非常明显且平稳

tidb 响应时间也有改善

7. 总结

Range 分区表和 Hash 分区表各有优缺点，可以根据实际需求取舍，从线上压测和实际运行情况来看，Hash 分区的响应时间优势非常明显相比 Range 分区。

本文记录了一次由 Range 分区改造为 Hash 分区的优化实践，希望对其他使用 TiDB 分区表的用户提供设计思路

从官方得知，TiDB 4.0 版本对分区表进行了较多的 Bug 修复、功能增强和性能提升，期待 TiDB 4.0 release。