mysql innodb 如何优化行数 9700w+ 的表

[haihongblog]

单表行数超过 9700w，由于业务需求导致查询效率较低（代码不是楼主写的，也不想动，查询代码比较简单，索引加的也 ok ）

实际业务需求只需使用最近一段的数据，估计约 500w 行，所以现打算把历史数据删除并创建自动删除任务，以为这样就可以优化性能。

但用 delete 删除历史数据以后发现 select * from table order by id(primary) asc 的速度变得十分慢（删数据之前很快），查资料发现 mysql delete 操作只是修改标记，还需要 optimize table 才行。

然而该操作会锁表，而且时间很长（几分钟到几小时不等）。mysql 官方建议是复制要留下的数据到新表，然后改名替换旧表。但线上有多个服务依赖这个表，暂停服务成本不小，想请问高贵的 v 友们，有什么办法能解决这个问题？

问题简述： 优化数据库性能，原 9700w 行表保留最近 500w

• 优化过程可以锁表，但最好在几分钟以内
• 不能停线上服务

[wshcdr]

关注这个问题吧，不过，几千万的数据不容易

[rrfeng]

有很多工具可以做大表的改动，原理是复制到一张新表，并通过内置的机制保持访问不中断。

github 有一个（不是 github 上存的，是他们自己用的）
percona 也有一个
具体叫什么名字我不记得了
主要用途是在线 alter 表结构，但我觉得应该能解决你的问题。

[iyaozhen]

这个我有相关经验，单表上亿
先说结论，几分钟肯定不行，你要在原表删数据，然后 optimize 这个时间不可预估，很有可能还 optimize 失败，我反正没成功过。

但用 delete 删除历史数据以后，变慢了。这个你应该只是删了一部分数据，不然早就锁表卡死了。变慢了这个我这边没有遇到过，理论上应该也不会。

建议还是停服务，弄吧。而且也是导出数据再导入弄。
其实要看业务上怎么依赖，不重要的话影响一会儿也没啥（你 load 新数据进去之前表是空的，只是没数据，程序不会崩）

[akira]

锁表几分钟 和 暂停服务几分钟 没啥区别啊

[liprais]

关键词 online ddl

[love]

mysql 只是做标记数据实际没删除？你真的确定？？？

[mcfog]

改表名难道不是毫秒级的暂停么？

id 是自增或者趋势递增的么？你测试用 asc 测试和业务只用新数据是矛盾的
如果是 uuid4 这类分散的，更没有理由 order by id 了

[SoulSleep]

难道不是新建一张表把这 500 万数据直接插入吗 create table select *...这种

然后 rename 表

[SoulSleep]

@love #5 他说的应该是对的，大概就是降水位

[love]

@SoulSleep 什么降水位？空的空间绝对是可以回收的。我的小机做爬虫每天插入几万删除几万过期数据，运行了几年也没磁盘爆满而是维持在一个定值

[robinlovemaggie]

记得当年做淘宝网的时候，sun 优化了一版 Mysql，但是具体版本开没开源不知道。mysql 被 oracle 收编之后就走下坡路了，这是常识。

[sadfQED2]

5 楼正解，原理就是建新表，触发器实现双写，脚本同步数据，两边表数据一致后 rename

[echo404]

如果库中剩下空间大可以找下 online ddl 的工具

[haihongblog]

特别感谢诸位，现在查询已经比较迅速，猜测刚删完慢的原因可能是还没做回收或者数的平衡操作还没触发之类的

online ddl 学到了新姿势，非常感谢