V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
ChenKevin
V2EX  ›  问与答

Mysql 优化问题

  •  
  •   ChenKevin · 2019-08-26 10:07:31 +08:00 · 1717 次点击
    这是一个创建于 1676 天前的主题,其中的信息可能已经有所发展或是发生改变。
    直奔主题了:
    1.有个表 tabA 记录终端的活跃时间 tabA ( id,tid,time )
    2.这个表日新增 200w 条数据
    3.每天凌晨清理七天前的数据
    问题:
    1.原先设计有问题,只有给 tid 添加索引(有一些查询语句有根据终端查询)。
    2.但是每次凌晨删除数据的时候,耗时,会导致插入时 lock time out。
    个人想法:
    1.给 time 加上索引,但是目前线上有个表大概 2000W 数据,这可能不好上索引。还有就是会导致插入的效率
    2.分表,但是目前经量不考虑这个方案,因为维护成本等比较高
    大家给给建议,谢谢
    13 条回复    2020-05-11 10:27:22 +08:00
    opengps
        1
    opengps  
       2019-08-26 10:12:14 +08:00
    这种硬件传感器采集类业务的表不适合有单独主键,直接 time 列聚集索引即可。
    参考下我曾经用过的关系型数据库存 gps 坐标点表 https://www.opengps.cn/Blog/View.aspx?id=284&from=v2ex
    liprais
        2
    liprais  
       2019-08-26 10:15:04 +08:00 via iPhone
    分区表直接 drop 分区
    TomVista
        3
    TomVista  
       2019-08-26 10:39:48 +08:00
    第一个想到的就是,按天分表,
    不着急的话,可以慢慢删,比如每次删一点,2 个小时删完,插入时 lock time out 出现的机率就会降低,根据实际调整删除条数和删除间隔,
    ChenKevin
        4
    ChenKevin  
    OP
       2019-08-26 10:41:58 +08:00
    @opengps 谢谢!但是线上已经有 2000W 数据,如何上索引呢?
    ChenKevin
        5
    ChenKevin  
    OP
       2019-08-26 10:42:51 +08:00
    @liprais 非常感谢分区表确实是个好方法,但是有的客户是低版本的 sql。这个我们考虑下。
    ChenKevin
        6
    ChenKevin  
    OP
       2019-08-26 10:43:25 +08:00
    @TomVista 因为没给 time 建索引,删一点也是很耗时的
    wkzq
        7
    wkzq  
       2019-08-26 10:45:53 +08:00
    id 和 time 应该是严格递增, 首先找到七天对应的 id, 然后 delete id < xx 来删除呢?
    ChenKevin
        8
    ChenKevin  
    OP
       2019-08-26 13:48:43 +08:00
    @wkzq 这个方法也还可以哈,但不是很严紧哈,谢谢
    opengps
        9
    opengps  
       2019-08-26 13:56:11 +08:00
    @ChenKevin 7 楼的方法很好,用索引的列代替了不索引的列,把整个都慢改为只慢一个小地方
    既然数据只存 7 天,可以考虑使用一个新的表按照新结构同时存,坚持扛过 7 天后淘汰旧表
    ChenKevin
        10
    ChenKevin  
    OP
       2019-08-26 14:43:40 +08:00
    @opengps 嗯。7 楼的确实是不错。你说的新表同时存?这里是每天凌晨删除 7 天之前的数据,(一直要保持最近 7 天的数据)
    opengps
        11
    opengps  
       2019-08-26 15:00:05 +08:00
    @ChenKevin 用 7 天作为过渡期。当新数据来了,同时按照新旧结构存新表和旧表,过了这 7 天,就把读取功能切换到新结构上去,旧表作废
    省掉了换新结构需要导数据的过程
    ChenKevin
        12
    ChenKevin  
    OP
       2019-08-29 15:13:27 +08:00
    @liprais 你好,针对分区咨询你个问题!
    我把原先表按星期几分区后
    ALTER TABLE tb_heartbeat PARTITION BY RANGE(WEEKDAY(hbTime))(
    PARTITION p1 VALUES less than (1),
    PARTITION p2 VALUES less than (2),
    PARTITION p3 VALUES less than (3),
    PARTITION p4 VALUES less than (4),
    PARTITION p5 VALUES less than (5),
    PARTITION p6 VALUES less than (6),
    PARTITION p7 VALUES less than (MAXVALUE)
    );

    但是在查询今天数据的时候,它会遍历所有分区。
    EXPLAIN PARTITIONS SELECT * from tb_heartbeat where hbtime < '2019-08-29 23:59:59' and hbtime > '2019-08-29 00:00:20'

    这个应该是什么问题,看了文档没找到相关的
    谢谢
    kanepan19
        13
    kanepan19  
       2020-05-11 10:27:22 +08:00
    2000W 上索引,应该不慢的。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4810 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 09:57 · PVG 17:57 · LAX 02:57 · JFK 05:57
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.