MySql分区表性能测试及切换案例

背景

互联网公司的业务变化很快，数据库表结构设计相对比较直接，很少会在前期设计的很完善。当业务存活并发展起来后，就需要在扩展性、安全性等方面进行改进。

比如，我们一张记录用户状态的表，存储在RDS for MySql（InnoDB存储引擎）中。此业务表最近膨胀到1.5亿条记录，存储占用30多G，且数据还在不断增长。

虽然目前整体性能表现尚可，但部分操作耗时越来越长，锁表冲突事件也开始出现。考虑到数据量的快速增长，以及数据库本身的雪崩特点，我们认为这张表存在很大的性能风险，急需优化。

性能分析

下面我们深入分析MySql InnoDB表数据量大小对CRUD及DDL操作的性能影响：

• 插入数据：由于使用自增列作为主键（大数据表比较推荐这种方式，索引占用存储空间会大幅减少），增加业务数据的消耗时间为O(1)。但由于各索引数据需要排序，因此增加索引数据的消耗时间为O(logN)；
• 查询数据：如果通过索引查询，消耗时间为O(logN)；如果不通过索引查询，消耗时间为O(N)；
• 删除数据：消耗时间与查询数据表现一致，但需要特别注意锁的问题。虽然我们经常说InnoDB是行锁，但这种说法是针对MyISAM的表锁而言。实际上，InnoDB的“行锁”是“索引键锁”，其锁机制的实现是基于索引键实现的。如果删除数据时没有匹配到索引键，或即使匹配上索引键，但如果索引键匹配的数据记录过多，依然会导致大范围加锁从而引起访问冲突，极大的降低系统性能；
• 更新数据：消耗时间和加锁分析基本与删除数据一致，只是多了个索引的重排序；
• DDL操作：MySql 从5.6开始支持在线DDL操作。其原理是进行DDL操作前，将原始数据文件拷贝到新文件中，然后执行DDL操作。同时，将DML操作日志存储到缓存中，待DDL执行完成后再执行到新的数据文件中。那么，当表数据量较大时，数据文件拷贝时间必然较长，如果这期间存在大量的DML操作，超过缓存上限，则DDL操作会失败。

• DML(Data Manipulation Language)，数据操纵语言，包括UPDATE、INSERT、DELETE，表示对数据记录层面的操作
• DDL(Data Definition Language)，数据定义语言，主要包括CREATE、ALTER、DROP等，表示对表结构层面的操作

优化方案

通过上面的分析，我们可以看出，InnoDB表数据量过大对各种操作都存在较大的性能影响。针对这些问题，有以下三种优化方案：

将此业务表切换到分布式数据库产品中

此方案最简单直接。但我们的业务中，只有此表数据量较大且需要查询详细单据。仅为了一张表就引入一种存储机制，考虑到运维和经济成本，总觉得不划算。另外，此表还与其他表有一定的联合查询操作，分离出去后会增加应用层的复杂度；

对此业务表分库分表

分库分表是处理大数据表的利器。但我们数据库系统的CPU和IO资源很富余（CPU仅10%，IOPS仅300多），完全没有分库的必要。而分表会使得应用层的修改工作量巨大，代码的可读性也会变差。如果为了业务层的逻辑清晰再引入中间件进行代理访问，又有杀鸡用牛刀之感；

使用MySql自带的分区表

分区表是MySql 5.1引入的特性。根据官网alter-table-partition-operations的介绍，其本质是将分库分表直接集成到MySql中。我们知道，传统的分库分表功能，存在业务层、中间件、数据库三层：业务层通过调用中间件的API访问数据库，不知道具体的物理存储细节；中间件将一张很大的逻辑表映射到数据库中多张较小的物理表，并对业务层的访问请求进行分解后分别放到对应物理库中执行，再将执行结果在中间件合并后返回给业务层，从而对业务层屏蔽物理存储细节；数据库则提供实际的物理存储。

而MySql的分区表，借助MySql本身的逻辑架构，将分库分表功能进行了下沉。MySql逻辑架构中的客户端即对应业务层，Server层对应中间件层，存储引擎层对应物理存储层。简单的说，分库表就是我们在数据库层面看到是一张表，但物理上是分成多个文件独立存储。

逻辑上分析，分区表的优点很明显：既能解决大数据量的性能问题，又能对应用层无缝切换。但是，其真实性能表现和稳定性到底怎么样? 还是得通过测试来验证。

分区表性能测试

为方便说明，我们将此业务表逻辑结构简化为只包含以下4列：id(自增列)，depart_id(部门ID)，user_id(员工ID)，mark(员工业绩)。

1. 非分区表 table_no_par：物理包含以上4列，其中 id 为主键，depart_id+user_id存在唯一键索引，mark列上有索引；
2. 分区表 table_par：物理包含以上4列，其中id为索引，depart_id+user_id存在唯一键索引，mark列上有索引。分区表以depart_id作为分区键，hash水平分成100个区。

• 分区表中id作为索引而不是主键，原因是由于MySql要求分区键必须包含主键和唯一键索引，但实际上id作为自增列并不具有业务意义，不适合作为分区键。同时为了保证分区表和非分区表逻辑结构一致，需要在分区表创建id列，并在其上创建索引及自增。
• 尽管分区表的user_id字段在业务上不应为NULL, 但为了避免 depart_id+user_id 的唯一键索引被MySql用作聚簇索引，也需要将其设置可为NULL。这样一方面可以减少分区表占用的存储空间；另一方面可以避免数据在DML时频繁的进行页面分裂、合并、重组，优化写入性能。

下面分别针对 插入、查询、DDL、存储空间 等几个关键性能指标进行测试（更新和删除数据的性能表现与查询数据比较一致，不单独分析）。测试结果如下：

可以看出：

1. 分区表的插入性能较非分区表要差11%~15%，但这个差距随着记录数的增多而减少。也就是说，随着数据量的增大，分区表和非分区表的插入性能越来越接近。
2. 查询性能方面，如果按照分区键查询，分区表比非分区表的性能要好20%，而且数据量越大性能差距越大；如果按照非分区键的索引查询，非分区表的性能表现更好。
3. DDL性能，通过新增索引的执行时间来看分区表优于非分区表，这是由于分区表的索引是针对单个分区，排序的基数少。尤其是针对大数据量表，N条数据排序消耗时间为O(N·logN)，大于100次N/100条数据排序100·O(N/100·logN/100)。至于DDL执行失败的情况，测试环境未模拟出。但在实际生产环境中，确实是存在这个风险的。这一点分区表和非分区表是一致的。
4. 存储空间方面，分区表会比非分区表占用多一些，但随着数据量的增大，差距越来越小。一方面是由于每条记录非分区表比分区表少6字节的聚簇索引；另一方面是由于在存储引擎层面，分区表是100张表，在申请页面时各自独立申请，导致页面空间中未分配空间要更多。但大数据表两者差异不大。

通过以上的分析，我们得到以下的结论：

针对大数据表，分区表的插入性能、存储空间与非分区表基本一致，查询性能在分区键上比非分区键好，DDL执行时间比非分区表短。

于是，我们认为可以通过将非分区表切换到分区表来降低该数据表存在的性能风险。

分区表切换

为了避免业务中断，我们参考pt-online-schema-change的模式进行切换。

MySql在5.5及以前的版本，对Online DDL支持不太好，会导致锁表。因此，percona推出pt-online-schema-change，利用触发器实现在DDL过程中不会造成读写阻塞。

具体步骤如下：

1. 整改数据表的使用模式，所有操作均必须带上拟作为分区键的字段；
2. 创建分区表，其逻辑结构与非分区表完全一致；
3. 在非分区表上对update、delete动作创建触发器，将其update、delete动作在分区表也执行一次；
4. 根据自增列ID，将非分区表中的数据分批次顺序拷贝到分区表中；
5. 最后一批次数据拷贝完成后，立即通过rename将分区表与非分区表的表名互换；
6. 删除触发器，切换完成。

切换完成后，我们进行了一周的性能观察：CPU维持在10%，IOPS有8%左右的下降，存储空间有3%的上升。
单纯从整体的性能指标来看，切换前后变化并不是特别明显。但之前耗时越来越长的操作，耗时稳定了下来，锁表冲突事件也基本没有再出现。通过PARTITIONS的分析，最大的一个分区也只有500万行。即使数据量再扩大10倍，最大分区的数据量也才5000万，对于单个存储引擎文件来说，这完全无压力，理论上性能表现也不会出现大幅下滑。但如果放到非分区表，估计业务高峰流量稍微一冲击，或者硬件性能出现波动（在资源共享的云计算环境中，较为常见），就有崩溃的风险。

目前，分区表已经稳定在生产环境运行了近一个月。

总体来看，切换分区表比较好的解决了我们当前对于数据量快速增长的数据库性能的担忧，至少数据量再增长2、3倍应该是能扛住的。但它是否能如我们预期的在高并发下支持10倍数据量（即单表15亿记录）而性能表现依然稳定，仍有待实践证明。