MySql定期备份数据到历史表的解决方案

前言

互联网项目最大不瓶颈还是在于数据库，80%的数据请求只针对20%的数据；特别是电商项目，体现的更为明显，大量的数据请求，即使MySql在一主多从，读写分离，使用了Elasticsearch、Memcached、Redis、MongoDB等等后，在海量用户请求的情况下，数据库仍然无法支撑。

需求

商城数据库的订单数据一个月差不多就可以产生100w数据，订单明细表数据更多,导致订单表的数据量一直增大,即便有索引查询已经慢。
解决方案
    1、自动迁移程序定期迁移数据
    优点；稳定，
    缺点；难维护，并且频繁的对订单表移除历史数据和对历史表插入会导致大量的索引更新，这些操作会导致binglog增加，主从复制变慢,延迟加大
    
    2、通过改表名生成历史表,并生成一个新的表来存储数据
    优点；速度快，高效,产生的binglog少,不影响主从复制
    缺点；分表相比单表来说，读取数据麻烦

表复制

DROP PROCEDURE IF EXISTS P_BackupDataTable;  -- 删除之前的存储过程
CREATE PROCEDURE `P_BackupDataTable`(`copy_tablename` varchar(255))
BEGIN
  if exists (select * from information_schema.statistics where table_name = copy_tablename ) then
    set @oldTable = CONCAT(copy_tablename,"_",date_format(now(), '%Y%m%d'));	
    if not exists (select * from information_schema.statistics where table_name = @oldTable ) then
      set @beginNum=(SELECT IFNULL(AUTO_INCREMENT,0) as a FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='dataTableName' AND TABLE_NAME=copy_tablename LIMIT 1);
      set @newTable=CONCAT("new_",copy_tablename);
			
      CALL P_Statement(CONCAT("drop table if exists ",@newTable,";"));
      CALL P_Statement(CONCAT("CREATE TABLE ",@newTable," LIKE ",copy_tablename,";"));
      CALL P_Statement(CONCAT("alter table ",@newTable," auto_increment=",@beginNum,";"));
      CALL P_Statement(CONCAT("RENAME TABLE ",copy_tablename," TO ",@oldTable,",",@newTable," TO ",copy_tablename,";"));
    else 
      SELECT '备份表已存在' as Message;
    end if;
  ELSE
    SELECT '复制的表不存在' as Message;
  end if;	
END;


DROP PROCEDURE IF EXISTS P_Statement;
CREATE PROCEDURE P_Statement(IN dynamic_statement TEXT)
BEGIN
  SET @dynamic_statement := dynamic_statement;
  PREPARE prepared_statement FROM @dynamic_statement; -- 预编译一条sql语句，并命名为prepared_statement
  EXECUTE prepared_statement;-- 执行预编译sql					
  DEALLOCATE PREPARE prepared_statement;-- 要解除分配生成的预准备语句 PREPARE
END ;

CALL P_BackupDataTable('tbale_Name');   -- 执行存储过程