Mysql 优化问题

[ChenKevin]

直奔主题了：
1.有个表 tabA 记录终端的活跃时间 tabA （ id，tid，time ）
2.这个表日新增 200w 条数据
3.每天凌晨清理七天前的数据
问题：
1.原先设计有问题，只有给 tid 添加索引（有一些查询语句有根据终端查询）。
2.但是每次凌晨删除数据的时候，耗时，会导致插入时 lock time out。
个人想法：
1.给 time 加上索引，但是目前线上有个表大概 2000W 数据，这可能不好上索引。还有就是会导致插入的效率
2.分表，但是目前经量不考虑这个方案，因为维护成本等比较高
大家给给建议，谢谢

[opengps]

这种硬件传感器采集类业务的表不适合有单独主键，直接 time 列聚集索引即可。
参考下我曾经用过的关系型数据库存 gps 坐标点表 https://www.opengps.cn/Blog/View.aspx?id=284&from=v2ex

[liprais]

分区表直接 drop 分区

[TomVista]

第一个想到的就是，按天分表，
不着急的话，可以慢慢删，比如每次删一点，2 个小时删完，插入时 lock time out 出现的机率就会降低，根据实际调整删除条数和删除间隔，

[ChenKevin]

@opengps 谢谢！但是线上已经有 2000W 数据，如何上索引呢？

[ChenKevin]

@liprais 非常感谢分区表确实是个好方法，但是有的客户是低版本的 sql。这个我们考虑下。

[ChenKevin]

@TomVista 因为没给 time 建索引，删一点也是很耗时的

[wkzq]

id 和 time 应该是严格递增, 首先找到七天对应的 id, 然后 delete id < xx 来删除呢?

[ChenKevin]

@wkzq 这个方法也还可以哈，但不是很严紧哈，谢谢

[opengps]

@ChenKevin 7 楼的方法很好，用索引的列代替了不索引的列，把整个都慢改为只慢一个小地方
既然数据只存 7 天，可以考虑使用一个新的表按照新结构同时存，坚持扛过 7 天后淘汰旧表

[ChenKevin]

@opengps 嗯。7 楼的确实是不错。你说的新表同时存？这里是每天凌晨删除 7 天之前的数据，（一直要保持最近 7 天的数据）

[opengps]

@ChenKevin 用 7 天作为过渡期。当新数据来了，同时按照新旧结构存新表和旧表，过了这 7 天，就把读取功能切换到新结构上去，旧表作废
省掉了换新结构需要导数据的过程

[ChenKevin]

@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]

2000W 上索引，应该不慢的。