likes
comments
collection
share

在线变更MySQL千万级表结构实战

作者站长头像
站长
· 阅读数 17

1. 背景

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

相关概念

  • 会话

在线变更MySQL千万级表结构实战

  • 拉取会话

在线变更MySQL千万级表结构实战

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 去查询

在线变更MySQL千万级表结构实战

// SQL

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 表结构而不锁表

方案对比

在线变更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 表删除、删除触发器。

在线变更MySQL千万级表结构实战

  • 新方案直接命中索引，能够做到精确查询，查询速度很快
-- 新

SELECT * FROM chat_table WHERE splic_user_id IN ('1_2','1_3');

4.效果比对

优化前：

在线变更MySQL千万级表结构实战

优化后：查询速度提升 10 倍以上

在线变更MySQL千万级表结构实战

5. 总结

  • 遇到问题尽量不要闭门造车，埋头苦想，可以先在网上找下有没有其他人遇到过相似的问题，这样可以避免自己掉进坑里越陷越深，站在前人经验的基础上去解决问题可以取得事半功倍的效果
  • 实践证明 percona-toolkit 实时修改表结构真的很强大，千万级数据量表添加字段，对业务没有产生任何影响