面试官：如果单表数据量过大怎么办？

要回答这个问题，首先我们要明确这个表的数据是否全部有用？使用MySQL的过程，经常会遇到一个问题，比如说某张”log”表，用于保存某种记录，随着时间的不断的累积数据，但是只有最新的一段时间的数据是有用的；这个时候会遇到性能和容量的瓶颈，需要将表中的历史数据进行归档。

也就是说，大部分情况，我们做数据归档就足以解决这个问题。只有那些全部很重要的业务数据，才需要做分库分表。

利用存储过程和事件来定期进行数据的导出删除操作

1 、创建一个新表，表结构和索引与旧表一模一样

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;

end

3、创建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，应急解决方案如下：

1、执行show full processlist;查看所有MySQL线程。

2、执行SELECT * FROM information_schema.INNODB_TRX; 查看是否有错误线程，即线程id在show full processlist;的结果中，状态为sleep的线程。

3、kill进程id。

另外写存储过程的时候可以控制事务的大小，比如说可以根据时间字段每次归档一天或者更小时间段的数据，这样就不会有大事务的问题，里面还可以加入日志表，每次归档操作的行为都写入日志表，以后查起来也一目了然。

实战

首先，查看一下哪些表数据量特别大：

SELECT   
    TABLE_NAME AS '表名',  
    TABLE_ROWS AS '记录数'  
FROM   
    information_schema.TABLES   
WHERE   
    TABLE_SCHEMA = 'tms' and TABLE_ROWS > 1000;  -- 这里替换为你的数据库名

如图，我要对原数据库中的single_packaging表进行归档，就先新建一个用于归档的数据库doc_history:

然后建一张一模一样的表在这个数据库，编写归档的存储过程：

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

注意老库和新库的区别。

最后，设置事件，每天定时跑：

create event if not exists event_temp

on schedule every 1 day

on completion preserve

do call sp();

这样就OK了。