MySQL 分区表性能测试

在 Keeper 任务调度工具中，任务实例表是访问比较频繁的表，且数据增量较大。为了测试在大数据量的访问效率，特意做了这个实验。这个实验分别测试 MySQL 大表分区和不分区下的常用 3 种查询的速度。关于如何分担大表的负载，可以查看另一篇文章：MySQL 中大数据量高访问量的表负载分担策略。

测试表数据结构

分为两个表test1和test2，其中test1表不分区，test2表使用主键id分区，共分为 10 个区。其中 job_id 为索引。

测试数据

实验过程中，每 500 万数据测试一下，最大数据量 5000 万数据。假如 Keeper 共有 2000 个调度任务，这 2000 个调度任务每天生成 5000 个实例，如果不使用任务自动清理的话，达到 5000 万数据量需要 1 万天。嗯，足够了。

数据表中 job_id 取 1 ~ 2000 之间的随机数，title 字段为 32 位随机字符，title字段和create_time字段与测试基本无关。

硬件环境

两种方案均在工作机上进行测试，16G 内存，6 核 12 线程 CPU，SSD 硬盘。测试过程中尽量不使用电脑，保证不同测试的硬件无差异。

测试方法

每种方案下共进行三种查询，第一种为按 job_id 进行分组计数，每二种查询为按 job_id 取前 100 条数据，第三种查询为按主键id查找单条数据。job_id和id均按其值范围随机生成对应的数字。每种查询进行 100000 次，最后取均值，最多保留小数点后 3 位，时间单位为毫秒。

测试结果

数据量	COUNT 计数		SELECT 100		主键查询
数据量	不分区	分区	不分区	分区	不分区	分区
500万	1.287	1.962	3.408	4.233	0.298	0.341
1000万	1.833	2.705	3.095	3.842	0.641	0.637
1500万	2.894	3.494	3.561	4.536	0.735	0.758
2000万	3.216	4.251	3.202	5.117	0.771	0.682
2500万	4.072	5.43	3.27	4.616	0.765	0.796
3000万	4.73	6.356	3.23	5.384	0.84	0.922
3500万	5.515	7.852	3.622	5.586	0.925	0.88
4000万	6.905	9.683	3.013	6.499	0.898	0.914
4500万	6.36	14.171	3.043	4.878	0.749	0.809
5000万	7.271	9.016	2.808	5.101	0.785	0.781

测试结论分析如下：

在 5000 万及以下数据量下，不分区综合性能要大于分区性能。也许在更大的数据量下，分区性能才能显著体现。
对于索引键计数，查询效率随数据量增大而线性递减，但 5000 万数据量下的查询性能7.271ms可以接受。
对于索引键 TOP 100，因为数据是顺序存储的，查询也是按顺序匹配的。所以，这个场景下的数据不具备太多参考价值。因为索引键只有 2000 个，且每个索引键下的数据量基本平均分配。可以考虑在数据有倾斜的情况再进行测试。
对于主键查询，在 1000 万数据量以上时，查询效率基本波动不大，不存在线性关系。
在 5000 万及以下数据量下，不分区情况下的查询效率依然优秀，不建议分区。
此结论不一定具有普适性，仅供参考。

插入数据源代码

源代码使用 Scala 编写，其中用到了 Qross PQL 中相关的类。

查询计数源代码

源代码使用 Scala 编写，其中用到了 Qross PQL 中相关的类。

import io.qross.jdbc.DataSource
import scala.util.Random

object QueryTest {

def main(args: Array[String]): Unit = {

val table = "test1"

val ds = DataSource.QROSS
        val todo = 100000
        var times = 0L

print("COUNT: ")
        for (i <- 1 to todo) {
            val stamp = System.currentTimeMillis()
            ds.executeResultSet(s"SELECT COUNT(0) FROM $table WHERE job_id=${Random.nextInt(2000)}")
            times += System.currentTimeMillis() - stamp
        }
        print(times)
        print("ms / ")
        print(times / todo.toDouble)
        println("ms")

times = 0
        print("SELECT: ")
        for (i <- 1 to todo) {
            val stamp = System.currentTimeMillis()
            ds.executeResultSet(s"SELECT id, title FROM $table WHERE job_id=${Random.nextInt(2000)} LIMIT 100")
            times += System.currentTimeMillis() - stamp
        }
        print(times)
        print("ms / ")
        print(times / todo.toDouble)
        println("ms")

times = 0
        print("PRIMARY KEY: ")
        for (i <- 1 to todo) {
            val stamp = System.currentTimeMillis()
            ds.executeResultSet(s"SELECT * FROM $table WHERE id=${Random.nextInt(50000000)}")
            times += System.currentTimeMillis() - stamp
        }
        print(times)
        print("ms / ")
        print(times / todo.toDouble)
        println("ms")

ds.close()
    }
}

参考链接