面试题:如果单表数据量过大怎么办?
要回答这个问题,首先我们要明确这个表的数据是否全部有用?使用MySQL的过程,经常会遇到一个问题,比如说某张”log”表,用于保存某种记录,随着时间的不断的累积数据,但是只有最新的一段时间的数据是有用的;这个时候会遇到性能和容量的瓶颈,需要将表中的历史数据进行归档。也就是说,大部分情况,我们做数据归档就足以解决这个问题。只有那些全部很重要的业务数据,才需要做分库分表。
create table table_new like table_old;2 、新建存储过程,查询30天的数据并归档进新数据库,然后把30天前的旧数据从旧表里删除
delimiter $ create procedure sp() begin insert into tb_new select * from table_old where rectime < NOW() - INTERVAL 30 DAY; delete from db_smc.table_old where rectime < NOW() - INTERVAL 30 DAY; end3、创建EVENT,每天晚上凌晨00:00定时执行上面的存储过程
create event if not exists event_temp on schedule every 1 day on completion preserve do call sp();备注:第一次执行存储过程的时候因为历史数据过大, 可能发生意外让该次执行没有成功。重新执行时会遇到报错ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction,应急解决方案如下:
SELECT TABLE_NAME AS '表名', TABLE_ROWS AS '记录数' FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'tms' and TABLE_ROWS > 1000; -- 这里替换为你的数据库名
delimiter $ create procedure sp() begin insert into doc_history.single_packaging select * from old_schema.single_packaging where create_time < NOW() - INTERVAL 7 DAY; delete from old_schema.single_packaging where create_time < NOW() - INTERVAL 7 DAY; end注意老库和新库的区别。
<!-- 堆代码 duidaima.com --> create event if not exists event_temp on schedule every 1 day on completion preserve do call sp();