通过Mysql统计千万级数据

发表于 更新于
  • 工作中接到一个统计投票用户总数和统计新用户数的需求,逻辑很简单
  • 主要涉及有Mysql的3张表
    • 投票表(vote),存放创建的投票主题,做需求时有不到40条数据
    • 投票详情表(vote_detail),存放用户投票记录,用户每对投票主题下的投票选项投一票就有一条记录
    • 用户表(user),存放用户信息,该需求主要根据用户注册时间与投票活动开始时间作对比,判断是否为新用户
  • 原本认为这就和统计几万与几十万的数据差不多,直接统计就完了
    代码中使用的是多层嵌套查询,
    先根据页面条件获取到投票主题,
    根据投票主题的id列表进行循环,
    根据每个voteId到投票详情表(vote_detail)获取数据并收集userId列表,
    根据userId去重列表数量distinctUserIdList作为该投票的用户总数,
    根据distinctUserIdList去重列表从用户表获取注册时间并与活动开始时间比对作,注册时间在活动开始时间后的统计为新用户.

第一次优化

  • 代码写完,
    统计几个数据为万级别的投票很顺畅,
    可遇到了10万级数据的统计体验就变差了,
    几个10万级投票数据的统计加在一起差不多有100万,
    单是查库的时间就非常迟钝了,
    实时统计所有投票并不可取,
    想到有部分投票已结束,
    而结束的投票数据基本不会变化,
    即使活动重新开始那么活动的结束时间肯定会变化,
  • 创建一个统计表(statistic)
  • 存放vote_id,统计数据,以及投票结束时间,投票状态
  • 投票状态 用于记录当前投票是否为进行中,如果是进行中的投票每次查询都会重新统计
  • 投票结束时间 用于记录已统计完成的投票活动的结束时间,如果投票结束后因业务原因重新开启,统计会判断投票统计的投票结束时间与该投票目前的结束时间是否一致,不一致则说明当前统计数据不准确,删掉再统计

第二次优化

  • 将已结束的投票活动统计结果存放到统计表(statistic)后查询速度优化了不少,
    可问题又来了,
    统计投票的数据不止十万级别,
    也有百万和千万级别,
    目前投票详情表(vote_detail)有约5000W条数据,
    之前没意识到这个数据量的可怕性,
    以为只是查得慢而已,
    当jvm抛出了内存溢出异常,
    才真正发现问题,
    由于数据量太大,
    单次查询数量级太大,
    使得jvm没办法分配更多内存.
    于是决定用pageHelper分页去查,
    10w10w地查,应该不会出什么问题了吧

第三次优化

  • 优化成分页查询后,
    问题并没有解决,
    我也明显发现Mysql的分页查询越到后面越慢,
    而且同样也抛出了内存溢出异常,
    于是去找Mysql分页的原理,
    原来Mysql分页不是只去查找需要的那段数据,
    而是从第一条数据开始,把到分页的最后一条查出来再过滤,
    举个栗子
    • 查询投票详情第20页(每页10W条) select * from vote_detail limit 2000000,100000
    • Mysql的做法是查前210W条数据,再剔除掉前200W数据,得出200W~210W的数据
      这样的”分页”查询解决不了查询数据太大造成的内存溢出问题,
      在网上找到了一种Mysql比较高效的分页查询方法,
      只查单个字段比查整列快很多,
      又举个栗子
    • 查从100W条开始的500条整列数据select * from vote_detail where vote_id = 36 limit 1000000,500;
      • 耗时大约7000ms
    • 查从100W条开始的500条idselect id from vote_detail where vote_id = 36 limit 1000000,500;
      • 耗时约700ms
    • 从结果看出查单列可以比查全列快很多倍,数据量越大越明显
      表的id是主键有索引而且是基本连续的,
      用id>的方法进行分页查询Mysql就不需要从第一条开始查到最后一条了,
      写个子查询,根据上次分页查询到的最后一个id作为下次分页的第一条数据
    • SELECT * from vote_detail where id >= (select id from vote_detail where vote_id = #{voteId} order by id limit #{startSize},1) limit #{pageSize}

经过测试,用这个sql可以保证分页查询耗时不会因为页数越大变得越慢

第四次优化

  • 优化分页查询后,
    喜滋滋地一下子初始化所有统计数据就回家看电影了,
    第二天回来一看日志,
    初始化不到100W条数据jvm还是报错了GC overhead limit exceeded,
    于是我请教了公司的文神,
    他教会我一个命令jconsole调出java监视和管理控制台,
    在这个控制台可以看到java进程的内存分配情况, 通过监视发现就算优化了sql,代码没有及时把userId列表释放掉,
    越堆越多造成老年代满了于是报出GC overhead limit exceeded错误,
    于是优化代码逻辑,
    不把userId列表统一起来再查了,
    而是每次从投票详情表(vote_detail)分页获取到userId列表就到用户表(user)统计,
    这样便能释放掉userId列表

第五次优化

  • 优化列表释放问题后,
    测试过后程序上线了,
    可上线后我很快发现一个问题,
    由于统计大量的投票数据是很慢的,
    这样就会触发一个程序员闻风丧胆的问题:重复写入,
    并不是同一条数据的并发,
    当有N个用户统计同一条需要耗时比较长的统计,
    就会出现插入N条同样的统计,
    但实际上这个很好解决,
    不需要从插入处阻断,
    查询统计表(statistic)时再根据voteId 及顺序的id 查是否为单独一条,
    如果不是就删除当前数据,
    这样能保证即使统计表(statistic)有多条统计数据,
    下一次查询的时候会清除冗余数据