MySQL 中大数据量高访问量的表负载分担策略

这篇文章承接上文 MySQL 分区表性能测试。本来想着通过分区表来解决大数据量场景下（千万级）有可能的慢查询问题，实际证明只要索引建好，5000 万查询性能也没有多少影响。但是在 Keeper 调度工具中，任务实例表是非常关键的表，又是查询非常多的表，且查询方式多样。不改分区，为了应对将来有可能发生的查询问题，又不能什么都不做。

主要优化思路如下：

统计所有的查询条件。
分析有可能存在的问题。
冗余式拆表，分散查询压力。
优化索引。
修改代码。

统计所有的查询条件

通过全局查找，涉及到两个项目。与任务实例表qross_tasks相关的查询共有169个，涉及到字段共9个。这次整理完了，才发现自己之前写 SQL 语句真是随心所欲。因为项目周期跨了几年，WHERE 条件中各种写法都有，很多时候都没有考虑性能问题，只是实现了逻辑就好了，觉得该建索引了，就加一个，这 9 个字段一共建了8个索引，也没考虑索引建的合适不合适，鄙视自己一下。

与查询相关的字段有：

id 主键。
job_id 调度作业 id。
status 任务状态，使用最频率的字段。
checked 异常任务检查。
task_time 任务时间。
record_time 记录时间。
to_be_start_time 即将重启时间。
create_time 任务创建时间。
update_time 任务更新时间。

分析有可能存在的问题

不再考虑已经建好的索引是否合适，重新分析查询条件，再为这些条件重新确定怎么建索引。

id 主键基本无问题。
job_id 上一篇文章测试过，基本也没什么问题。
status 最可能会出问题的字段，这个字段的枚举值只有14个，且分布非常不平衡，最多的success应该占90%以上，其他的有的甚至个位数。状态字段在任务运行过程中会不断变化。且80%以上的查询与这个字段相关。
checked 只有3个值，说实话，建索引也效果不明显。嗯，这个字段占了一个索引。
task_time 不会单独使用，会与其他字段一起查。
record_time 不会单独使用，会与其他字段一起查。
to_be_start_time 正常运行中，这个字段只在不到千分之一的数据中有值，使用率很低。它也占了一个索引。
create_time 有查询用到且会使用范围查询。
update_time 有查询用到且会使用范围查询。

解决

经过分析和思考，最终有了解决方案。

status是最有可能有问题的字段，枚举值少，数据分散不均，查询频率还高。根据查询场景，作了数据冗余。把qross_tasks表又拆出两个表qross_tasks_living和qross_tasks_abnormal。其中qross_tasks_living表存储正在执行中的任务，所有与正在执行中任务状态的查询都使用这个表做处理。对执行中的任务查询反而是最频繁的，基本占60%左右。这个表qrosss_tasks_abnormal的数据量基本维持在十位数甚至个位数，一般很少会出现百位数。表qross_tasks_abnormal存储所有异常任务记录，所有与异常任务状态相关的查询都使用这个表进行查询，这个表的数据量一般占总数据量的3%以下，查询量占总查询量的20%左右。而且，checked字因为与异常状态完全相关，也放到这个表中，极大的提升了这个字段的查询效率。至此，80%可能的查询问题已解决。
另一个使用率比较低的字段to_be_start_time因为与其他字段没多少关联，在实际生产中使用也较少但是比较关键，所以也专门建了一个表qross_tasks_to_be_start，这个数据量基本在个数位最多十位数。
主键id查询基本无问题，在查询时需要把主键放在条件中的第一位。单条数据关联查询时，优先使用一次子查询而不是直接 JOIN。 SELECT A.task_id, A.job_id, B.title, B.owner, A.task_time, A.record_time, A.start_mode FROM (SELECT id AS task_id, job_id, task_time, record_time, start_mode FROM qross_tasks WHERE id=$taskId) A INNER JOIN qross_jobs B ON B.id=$jobId AND A.job_id=B.id
job_id 经常跟其他字段一起使用，如task_time、status等。status相关的去相关的表去查，另外创建job_id+task_time的唯一键索引。这个索引可以提高创建任务实例语句的效率。
其他的优化都在改变查询字段的位置，一般按主键、索引键和非索引键排序，顺序固定。

总结

这个优化思路并不是常用的拆表或建分区的优化方式，主要还是分析查询条件、根据查询条件建立不同的冗余表。优点是可以极大的提高查询效率，缺点是数据冗余必然会增大“增删改”的复杂度。

优化后的数据表结构如下：

CREATE TABLE IF NOT EXISTS qross_tasks (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'task id',
    job_id INT,
    node_address VARCHAR(100) DEFAULT '',
    task_time VARCHAR(100) COMMENT 'task time, e.g. yyyy-MM-dd HH:mm:00',
    record_time VARCHAR(100) COMMENT 'record time, every execution time, yyyy-MM-dd HH-mm-ss',
    status VARCHAR(100) DEFAULT 'new' COMMENT 'new/initialized=checking/no_commands/checking_limit/ready/executing/finished/incorrect/failed/timeout/success/interrupted',
    creator INT DEFAULT 0 COMMENT '0 means #keeper',
    create_mode VARCHAR(100) DEFAULT 'schedule' COMMENT 'schedule/instant/complement/interval/trigger',
    start_mode VARCHAR(100) DEFAULT 'auto_start' COMMENT 'auto_start/manual_start/auto_restart/manual_restart',
    ready_time DATETIME,
    waiting_times INT DEFAULT 0 COMMENT 'waiting times before executing',
    start_time DATETIME COMMENT 'start time of executing',
    finish_time DATETIME COMMENT 'finish time of executing',
    readiness INT COMMENT 'readiness time, ready_time - create_time, seconds',
    latency INT COMMENT 'latency time, start_time - ready_time, seconds',
    spent INT COMMENT 'spent of executing, finish_time - start_time, seconds',
    starter INT DEFAULT 0 COMMENT '0 means #keeper',
    killer INT DEFAULT 0  COMMENT '0 means #keeper',
    mender INT DEFAULT 0  COMMENT '0 means #keeper',
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW() ON UPDATE NOW(),
    KEY `idx_qross_tasks_job_id` (`job_id`),    
    KEY `idx_qross_tasks_status` (`status`),
    UNIQUE KEY `idx_qross_tasks_job_id_task_time` (`job_id`, `task_time`),
    KEY `idx_qross_tasks_create_time` (`create_time`),
    KEY `idx_qross_tasks_update_time` (`update_time`)
);

CREATE TABLE IF NOT EXISTS qross_tasks_living (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    node_address VARCHAR(100) DEFAULT '',
    job_id INT,
    task_id INT,
    task_time VARCHAR(100),
    record_time VARCHAR(100),
    start_mode VARCHAR(100) DEFAULT 'auto_start',
    status VARCHAR(100) DEFAULT 'new',
    KEY `idx_qross_tasks_living_task_id` (`task_id`),
    UNIQUE KEY `idx_qross_tasks_living_task_id_record_time` (`task_id`, `record_time`),
    KEY `idx_qross_tasks_living_status` (`status`),
    KEY `idx_qross_tasks_living_job_id_status` (`job_id`, `status`)
);

CREATE TABLE IF NOT EXISTS qross_tasks_abnormal (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_id INT,
    task_id BIGINT,
    task_time VARCHAR(100),
    record_time VARCHAR(100),
    status VARCHAR(100),
    checked VARCHAR(50) DEFAULT 'no' COMMENT 'will be yes/no on exception',
    mender INT DEFAULT 0,
    create_time DATETIME DEFAULT NOW(),
    update_time DATETIME DEFAULT NOW() ON UPDATE NOW(),
    KEY `idx_qross_tasks_abnormal_job_id` (`job_id`),
    KEY `idx_qross_tasks_abnormal_checked` (`checked`),
    KEY `idx_qross_tasks_abnormal_task_id` (`task_id`),
    KEY `idx_qross_tasks_abnormal_status` (`status`),
    KEY `idx_qross_tasks_abnormal_create_time` (`create_time`)
);

CREATE TABLE IF NOT EXISTS qross_tasks_to_be_start (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    job_id INT,
    task_id BIGINT,
    task_time VARCHAR(100),
    record_time VARCHAR(100),
    status VARCHAR(100),
    to_be_start_time VARCHAR(100),
    KEY `idx_qross_tasks_to_be_start_time` (`to_be_start_time`)
);

参考链接

Keeper 任务调度工具