在线变更亿级 MySQL 表结构实战-优快云博客

文章讲述了IM应用中的会话查询性能问题，通过分析商业背景和现有表结构缺陷，提出使用percona-toolkit实时修改表结构，添加splic_user_id字段来解决查询效率低下的问题，优化后查询速度提升10倍以上。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.背景

IM作为社交应用的基础服务，承担着即时聊天、群聊、直播、多人语音等功能。即时聊天又是众多功能模块中最重要的一个，需要维护两个用户的会话(多对多)关系，随着应用推广用户量级在不断增加会话关系呈指数级增长，获取会话信息接口调用频率呈指数级增长，由于应用立项比较匆忙，对应用发展速度预估比较悲观，且初期人力资源有限没有引入分库分表，单表数据指数增长所以响应速度肉眼可见的变慢，优化会话表迫在眉睫

2.问题现状

假设每个活跃用户会话数是10个，当日活为100时，总会话数大约是在1000左右；当日活到达百万时, 每日的会话数量就是一个较大的数量级，在实际业务场景中只要用户发生对话并且在会话列表中没有当前聊天会话就会调用接口(qps为1000，rt为400ms左右)获取会话信息，调用频率高响应速度慢导致用户体验很差，用户活跃高峰期常因该接口导致服务器cpu长时间处于极高负载状态，严重时直接导致整个系统瘫痪

3.解决方案

问题点

uid是会话发起方id，to_uid是会话接收方id，uid在实际业务中是付费者的角色，to_uid是获益者的角色，uid找to_uid聊天需要花费一定的费用。在这样的商业背景下设计出了uid和to_uid的表结构，虽然这种结构满足了商业要求，但是仍然存在业务缺陷，如：
查询用户1(id = 1)与用户2(id = 2)、用户3(id = 3)的会话信息，对应如下SQL，没办法明确两个用户谁是uid谁是to_uid，只能通过OR去查询

SELECT * FROM chat_table 
WHERE 
    (uid = 1 AND to_uid IN (2,3)) 
    OR 
    (uid IN (2,3) AND to_uid = 2);

由于是单表且数据量达到了3000w+，仅SQL查询就要300ms左右，加上业务处理时间单次接口调用需要耗时400ms+
优化过程不能停机维护，不能影响用户正常使用

优化思路

既然原表结构在知道两个用户id的情况下无法明确谁是uid，只能通过OR去查询两者的会话。可以在表中添加uid、to_uid按照<小的用户id_大的用户id>成的splic_user_id字段，由于uid和to_uid都是唯一确定的，拼接成的splic_user_id也是唯一确定的，即达到了通过uid、to_uid快速确定一条会话的目的
明确优化思路后，需要考虑不能停机维护，直接通过DDL语句直接给表添加字段必然会锁表导致IM服务直接瘫痪，应该如何做到既不停机维护，在不影响业务的情况下完成优化呢？
- 方案一：创建一张添加了splic_user_id字段的新表(chat_table_new)，chat_table 向 chat_table_new实时写入数据，等数据基本同步完成后，发布代码查询chat_table_new表，此方案有明显很费时
- 方案二：找到一款可以在线修改表结构而不锁表的工具，这样不需要修改代码成本很小
  通过查询资料得知有一款工具(percona-toolkit)可以在线修改MySQL表结构而不锁表

方案对比

在这里插入图片描述

percona-toolkit

percona-toolkit 源自 Maatkit 和 Aspersa 工具，这两个工具是管理 MySQL 的最有名的工具，但 Maatkit 已经不维护了，全部归并到 percona-toolkit。Percona Toolkit 是一组高级的命令行工具，用来管理 MySQL 和系统任务，主要包括：

验证主节点和复制数据的一致性
有效的对记录行进行归档
找出重复的索引
总结 MySQL 服务器
从日志和 tcpdump 中分析查询
问题发生时收集重要的系统信息
在线修改表结构

在业务不断迭代的情况下，不可避免对数据表进行 DDL 操作，修改、添加、删除字段、索引，对于 MySQL 而言，DDL 是一个需要非常谨慎使用的功能，因为在 MySQL 中在对表进行 DDL 时会锁表，表数据量越大，影响程度越大。在 5.1 之前 DDL 是非常耗时耗力的，在 5.1 之后随着 Plugin innodb 的出现在线加索引的速度提高了很多，但是还有影响（时间缩短了）; 在 5.6 可以避免上面的情况，目前 InnoDB 引擎是通过以下步骤来进行 DDL 的：

按照原始表（original_table）的表结构和 DDL 语句，新建一个不可见的临时表（tmp_table）
在原表上加 write lock，阻塞所有更新操作（insert、delete、update 等）
执行 insert into tmp_table select * from original_table
rename original_table 和 tmp_table，最后 drop original_table
释放 write lock。
我们可以看见在 InnoDB 执行 DDL 的时候，原表是只能读不能写的。为此 perconal 推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。

pt-online-schema-change 工作原理

如果存在外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理。没有使用 alter-foreign-keys-method 指定特定的值，该工具不予执行
创建一个新的空表，其命名规则是：下划线 + 原表名 +_new—-_原表名_new
根据 alter 语句，更新新表的表结构；
创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。如果表中已经定义了触发器这个工具就不能工作了。
拷贝数据，从源数据表中拷贝数据到新表中。
修改外键相关的子表，根据修改后的数据，修改外键关联的子表。
rename 源数据表为 old 表，把新表 rename 为源表名，其通过一个 RENAME TABLE 同时处理两个表，实现原子操作。（RENAME TABLE dbteamdb. chat_table TO dbteamdb._chat_table_old, dbteamdb._chat_table_new TO dbteamdb. chat_table）
将 old 表删除、删除触发器。
新方案直接命中索引，能够做到精确查询，查询速度很快
– 新
SELECT * FROM chat_table WHERE splic_user_id IN (‘1_2’,‘1_3’);