上一篇Mysql已有亿级数据大表按时间分区,介绍了亿级数据大表如何按时间分区,也留下了一个问题:备份亿级数据大表要耗时多久。本篇将就如何备份亿级数据大表展开讨论。
注意:我这里所说的备份指的是数据从一张表拷贝到另外一张表,也就是说单表备份。
创建原表t_send_message_send的sql:
CREATE TABLE `t_send_message_send` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`plan_id` bigint(20) DEFAULT NULL,
`job_uuid` varchar(36) DEFAULT NULL,
`send_port` varchar(16) DEFAULT NULL,
`mobile` varchar(16) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
`product_code` varchar(16) DEFAULT 'HELP',
`fake` bit(1) DEFAULT b'0',
`date_push` datetime DEFAULT NULL,
`activity_id` bigint(20) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `mobile` (`mobile`),
KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
原表一个自增主键id,两个索引mobile、date_push,数据量如下图:
创建新表的t_send_message_send2的sql:
CREATE TABLE `t_send_message_send2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`plan_id` bigint(20) DEFAULT NULL,
`job_uuid` varchar(36) DEFAULT NULL,
`send_port` varchar(16) DEFAULT NULL,
`mobile` varchar(16) DEFAULT NULL,
`content` varchar(200) DEFAULT NULL,
`product_code` varchar(16) DEFAULT 'HELP',
`fake` bit(1) DEFAULT b'0',
`date_push` datetime NOT NULL,
`activity_id` bigint(20) DEFAULT '0',
PRIMARY KEY (`id`,`date_push`),
KEY `mobile` (`mobile`),
KEY `date_push` (`date_push`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(date_push)
(PARTITION p2016 VALUES LESS THAN ('2017-01-01') ENGINE = InnoDB,
PARTITION p2017 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
PARTITION p2018 VALUES LESS THAN ('2019-01-01') ENGINE = InnoDB,
PARTITION p2019 VALUES LESS THAN ('2020-01-01') ENGINE = InnoDB,
PARTITION p2020 VALUES LESS THAN ('2021-01-01') ENGINE = InnoDB);
新表一个联合主键(id,date_push),两个索引mobile、date_push,5个分区,字段和结构跟原表一样,数据量为0。
上一篇提供了两类备份方式:①在线备份;②离线备份。
1.在线备份;
数据一直在数据库中不离线。
insert into t_send_message_send2 (select * from t_send_message_send);
sql很简单,意思很明确,就是将select的查询结果插入到t_send_message_send2。这个过程我跑了一个多小时,没跑完,被我中止了。用navicate查看t_send_message_send2的对象信息,看到有500多万行记录,打开t_send_message_send2表,里面一行记录都没有,空的。应该是请求中止了,数据还没提交。好吧,看下为什么慢,解析下:
EXPLAIN
insert into t_send_message_send2 (select * from t_send_message_send);
执行结果:
"id" "select_type" "table" "partitions" "type" "possible_keys" "key" "key_len" "ref" "rows" "filtered" "Extra"
"1" "INSERT" "t_send_message_send2" "p2016,p2017,p2018,p2019,p2020" "ALL" "" "" "" "" "" "" ""
"1" "SIMPLE" "t_send_message_send" "" "ALL" "" "" "" "" "100568970" "100.00" ""
好家伙,第5列type都是ALL。type表示MySQL在表中找到所需行的方式,又称“访问类型”。常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从前往后,性能从差到好)。ALL,Full Table Scan, MySQL将遍历全表以找到匹配的行。明白了吧,每次插入全表扫描,这能不慢吗?
2. 离线备份
数据先导出到本地,再从本地导回数据库。
1)数据导出(数据备份)
离线备份也分为冷备和热备。
冷备:数据库处于关闭的状态下的备份,优点是:①保证数据库的完整性;②备份过程简单并且恢复速度快。缺点是:①关闭数据库,意味着相关的业务无法正常进行,用户无法访问你的业务,一般冷备用于不是很重要、非核心业务上面。
冷备显然是不满足我的业务需求的,冷备是全库备份,而我只是单表备份。
热备:数据库处于运行状态下的备份,不影响现有业务的进行。热备又分为裸文件备份和逻辑备份。裸文件备份:基于底层数据文件的copy datafile。进入到数据库的数据目录,再进入到你的库目录,你会发现在这个目录下有很多.frm文件和.ibd文件,.frm文件是表的结构文件,.ibd文件是表的数据文件。逻辑备份:备份成SQL语句或者其他文件(如csv),恢复时执行SQL,实现数据库数据的重现。
裸文件备份显然也是全库备份,也是不满足我的业务需求的,下面讨论逻辑备份。
逻辑备份常见的两种方式:
①mysqldump
mysqldump -u root -p marketing t_send_message_send > e:/mysql/marketing_t_send_message_send.sql;
哈哈哈,暴露了在windows上操作的。
mysqldump导出相当快,亿级的记录,50多个G数据量,大概仅用了40分钟左右。没记录到具体时间,是因为执行这个脚本不需要登录到mysql,命令行就可以了,而命令行不会提示执行脚本花了多长时间,如果登录mysql,每次执行都会提示执行脚本好了多长时间。
②select … into outfile …;
mysql> use marketing;
Database changed
mysql> select * from t_send_message_send into outfile 'e:/mysql/t_send_message_send.csv';
Query OK, 110900005 rows affected (34 min 10.22 sec)
mysql>
亿级的记录,50多个G数据量,仅需要34分钟,就问你快不快?
2)数据导入(数据恢复)
①mysqldump方式导出的
mysql> use marketing;
Database changed
mysql> source e:/mysql/marketing_t_send_message_send.sql
或者
mysql -uroot -p marketing < e:/mysql/marketing_t_send_message_send.sql
mysqldump方式不满足我的业务需求的,mysqldump备份了整个t_send_message_send表,包括表结构,而表结构是我不需要的,如果恢复的话,只会是恢复成t_send_message_send,数据不会恢复到t_send_message_send2中。
②select … into outfile …;导出的
mysql> use marketing;
Database changed
mysql> load data infile 'e:/mysql/t_send_message_send.csv' into table t_send_message_send2;
或者
将备份的t_send_message_send.csv重命名为t_send_message_send2.csv,然后命令行里面执行:
mysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csv
很遗憾,这种方式不可行,我从凌晨1点开始执行,到早上9点多还没执行完。七八个小时,插入了2700多万记录,13个G数据量,1.7个G索引。
之前我一直觉得应该是可行的,开始执行的那一刻我就感觉不对。分析下了原因,大概是因为有索引。我的理解是这样的:索引相当于排序,插入数据前,还得先全表扫描下,才晓得数据应该插入到哪个位置,插入一亿条记录,就得一亿次全表扫描,这能不慢吗?那既然这样,先把索引删了,先不排序,数据直接插到最后面,等数据插完之后再排序,再建索引,这样应该会快一些。开搞,先删除索引:
##先truncate掉t_send_message_send2##
TRUNCATE TABLE t_send_message_send2;
ALTER TABLE t_send_message_send2 DROP INDEX mobile;
ALTER TABLE t_send_message_send2 DROP INDEX date_push;
然后再次导入。
C:\Users\maanjun>mysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csv
Enter password: ******
marketing.t_send_message_send2: Records: 110900005 Deleted: 0 Skipped: 0 Warnings: 0
耗时3个多小时,跟Mysql数据库快速插入亿级数据差不多。最后,再重建索引:
ALTER TABLE `t_send_message_send2` ADD INDEX (mobile);
ALTER TABLE `t_send_message_send2` ADD INDEX (date_push);
重建两个索引,一个varchar类型,一个datetime类型,建一个索引差不多二三十分钟,加上数据导入过程耗时,数据导入、重建索引总共耗时4个小时。
回过头来想,插入数据前删除索引,然后插入数据,最后重建索引,不管是哪种导入方式差不多都是耗时3个多小时,加上重建索引的时间,整个恢复过程差不多4个小时。再加上导出耗费的时间,5个小时内亿级记录表单表备份是可以的。当然这说的离线备份,其实如果顺利的话,在线备份花费的时间会更短,因为在线备份也可以是删除索引–>插入数据–>重建索引这个过程,况且在线备份不需要耗费导出数据这段时间。其次,在线备份也不需要占用本地几十个G的中转空间。但是在线备份一定好吗?未必!在线备份频繁地查询原表,会不会影响线网业务?我是在本机测试的,直接操作数据库,没有业务在跑,当然没有关系,如果是线网那就值得考虑下啦。再者,我在用navicate进行在线备份过程中连接无故中断了。
[SQL]insert into t_send_message_send2 (select * from t_send_message_send);
[Err] 2013 - Lost connection to MySQL server during query
在数据导出导入过程中还踩了一些,这些坑在百度上搜一下,都有解决方法。下一篇,将对整个mysql亿级数据大表分区的过程做个总结。
附:
type
表示MySQL在表中找到所需行的方式,又称“访问类型”。
常用的类型有: ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
index: Full Index Scan,index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行,使用一个索引来选择行
ref: 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
更多explain解释,参见MySQL Explain详解
1、https://www.cnblogs.com/xuanzhi201111/p/4175635.html
2、https://blog.csdn.net/weixin_44297303/article/details/99197637
3、https://www.jianshu.com/p/c64b857a9996