百度360必应搜狗淘宝本站头条
当前位置:网站首页 > 技术文章 > 正文
40 秒到 0.5 秒!分表优化历史数据查询,这波操作绝了

40 秒到 0.5 秒!分表优化历史数据查询,这波操作绝了

  • 网站名称:40 秒到 0.5 秒!分表优化历史数据查询,这波操作绝了
  • 网站分类:技术文章
  • 收录时间:2025-08-03 01:41
  • 网站地址:

进入网站

“40 秒到 0.5 秒!分表优化历史数据查询,这波操作绝了” 网站介绍

用户画像系统差点栽在 1.2 亿行的行为表里 —— 查条历史数据要 40 秒,系统卡到濒临瘫痪!还好分表这招救了场,性能直接原地起飞,连服务器成本都砍了一半。

一、要命的性能坑

  • 数据量疯长:3 年攒了 1.2 亿行数据,单表体积飙到 80GB,其中 url 这类大字段占了 40% 空间,像块赘肉拖慢整个查询。
  • 慢查询暴击:运营查 "用户 2023 年 Q1 的购买记录",SQL 长这样:
SELECT * FROM user_behavior 
WHERE user_id = 10086 
AND create_time BETWEEN '2023-01-01' AND '2023-03-31';

明明加了索引,还是要扫大半个表,40 秒的等待时间里,数据库连接池全满,整个系统差点宕机。更糟的是:索引文件占了 22GB,备份一次要 3 小时,运维小哥天天吐槽 "像在搬砖"。

二、分表神操作,三步搞定

(一)按季度 "剁表",瞬间瘦身

把 80GB 的大表按 "年 + 季度" 拆成小块,比如user_behavior_2023Q1,单表控制在 1000-1500 万行,还狠心砍掉 url 等大字段,只留核心字段:

CREATE TABLE user_behavior_2023Q1 (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  behavior_type VARCHAR(20) NOT NULL,
  create_time DATETIME NOT NULL,
  KEY idx_user_time (user_id, create_time) -- 只留必要索引
) ENGINE=InnoDB;


光这一步,单表体积就从 80GB 降到 20GB,索引文件直接瘦了 75%!

(二)冷热数据 "分家",各就各位

  • 热数据(近 6 个月):放user_behavior_current表,随便读写,满足日常高频查询。
  • 温数据(6-12 个月):存季度表,设为只读,每天凌晨自动同步热表数据:
-- 同步热数据到季度表
INSERT INTO user_behavior_2024Q2 
SELECT id, user_id, behavior_type, create_time 
FROM user_behavior_current 
WHERE create_time >= '2024-04-01' AND create_time < '2024-07-01';
  • 冷数据(1 年以上):扔年度表,用便宜服务器存,反正查得少。

(三)加个 "导航",查询不迷路

写了个自动路由工具,输入时间就知道该查哪个表,业务代码不用改:

// 自动定位表名的核心代码
String quarter = "Q" + ((month - 1) / 3 + 1); // 自动算季度
return "user_behavior_" + year + quarter;

就像给查询装了 GPS,再也不用在全表里瞎转悠。

三、效果炸到离谱

  • 速度起飞:查用户历史购买记录,冷数据从 40 秒压到 2.8 秒,热数据直接飙到 0.5 秒!
  • 成本大降:冷数据用低价服务器,一年省 60% 存储费,老板笑开了花。
  • 运维减负:备份时间从 3 小时砍到 15 分钟,运维小哥终于不用熬夜了。

四、3 个避坑金句

  1. 跨表统计别硬拼:建个汇总表存每日统计结果,查起来秒出,比 union all 8 个表快 10 倍!
  1. 冷表必须锁死:给冷表加只读锁(ALTER TABLE ... READ_ONLY=1),实习生再也删不了老数据。
  1. 分表别太碎:按月分表 3 年要管 36 张表,按季度分表 12 张足够,维护成本降一半。

五、这些场景直接抄作业

日志表、交易记录、传感器数据…… 只要是按时间查得多的表,这套分表方案闭眼用!你有啥更野的优化招?评论区亮出来瞅瞅~