云网站功能,江门网站seo关键词排名优化,代理网关app未运行 歌华,赤蚁网站建设上一篇Mysql已有亿级数据大表按时间分区#xff0c;介绍了亿级数据大表如何按时间分区#xff0c;也留下了一个问题#xff1a;备份亿级数据大表要耗时多久。本篇将就如何备份亿级数据大表展开讨论。注意#xff1a;我这里所说的备份指的是数据从一张表拷贝到另外一张表介绍了亿级数据大表如何按时间分区也留下了一个问题备份亿级数据大表要耗时多久。本篇将就如何备份亿级数据大表展开讨论。注意我这里所说的备份指的是数据从一张表拷贝到另外一张表也就是说单表备份。创建原表t_send_message_send的sqlCREATE 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 b0,date_push datetime DEFAULT NULL,activity_id bigint(20) DEFAULT 0,PRIMARY KEY (id),KEY mobile (mobile),KEY date_push (date_push)) ENGINEInnoDB DEFAULT CHARSETutf8;原表一个自增主键id两个索引mobile、date_push数据量如下图 创建新表的t_send_message_send2的sqlCREATE 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 b0,date_push datetime NOT NULL,activity_id bigint(20) DEFAULT 0,PRIMARY KEY (id,date_push),KEY mobile (mobile),KEY date_push (date_push)) ENGINEInnoDB DEFAULT CHARSETutf8PARTITION 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);新表一个联合主键(iddate_push)两个索引mobile、date_push5个分区字段和结构跟原表一样数据量为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表里面一行记录都没有空的。应该是请求中止了数据还没提交。好吧看下为什么慢解析下EXPLAINinsert into t_send_message_send2 (select * from t_send_message_send);执行结果idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1INSERTt_send_message_send2p2016,p2017,p2018,p2019,p2020ALL1SIMPLEt_send_message_sendALL100568970100.00好家伙第5列type都是ALL。type表示MySQL在表中找到所需行的方式又称“访问类型”。常用的类型有 ALL, index, range, ref, eq_ref, const, system, NULL(从前往后性能从差到好)。ALLFull Table Scan MySQL将遍历全表以找到匹配的行。明白了吧每次插入全表扫描这能不慢吗2. 离线备份数据先导出到本地再从本地导回数据库。1)数据导出(数据备份)离线备份也分为冷备和热备。冷备数据库处于关闭的状态下的备份优点是①保证数据库的完整性②备份过程简单并且恢复速度快。缺点是①关闭数据库意味着相关的业务无法正常进行用户无法访问你的业务一般冷备用于不是很重要、非核心业务上面。冷备显然是不满足我的业务需求的冷备是全库备份而我只是单表备份。热备数据库处于运行状态下的备份不影响现有业务的进行。热备又分为裸文件备份和逻辑备份。裸文件备份基于底层数据文件的copy datafile。进入到数据库的数据目录再进入到你的库目录你会发现在这个目录下有很多.frm文件和.ibd文件.frm文件是表的结构文件.ibd文件是表的数据文件。逻辑备份备份成SQL语句或者其他文件(如csv)恢复时执行SQL实现数据库数据的重现。裸文件备份显然也是全库备份也是不满足我的业务需求的下面讨论逻辑备份。逻辑备份常见的两种方式①mysqldumpmysqldump -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 changedmysql 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 changedmysql source e:/mysql/marketing_t_send_message_send.sql或者mysql -uroot -p marketing e:/mysql/marketing_t_send_message_send.sqlmysqldump方式不满足我的业务需求的mysqldump备份了整个t_send_message_send表包括表结构而表结构是我不需要的如果恢复的话只会是恢复成t_send_message_send数据不会恢复到t_send_message_send2中。②select … into outfile …;导出的mysql use marketing;Database changedmysql 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\maanjunmysqlimport -u root -p marketing e:/mysql/t_send_message_send2.csvEnter 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(从左到右性能从差到好)ALLFull Table Scan MySQL将遍历全表以找到匹配的行index: Full Index Scanindex与ALL区别为index类型只遍历索引树range:只检索给定范围的行使用一个索引来选择行ref: 表示上述表的连接匹配条件即哪些列或常量被用于查找索引列上的值eq_ref: 类似ref区别就在使用的索引是唯一索引对于每个索引键值表中只有一条记录匹配简单来说就是多表连接中使用primary key或者 unique key作为关联条件const、system: 当MySQL对查询某部分进行优化并转换为一个常量时使用这些类型访问。如将主键置于where列表中MySQL就能将该查询转换为一个常量,system是const类型的特例当查询的表只有一行的情况下使用systemNULL: MySQL在优化过程中分解语句执行时甚至不用访问表或索引例如从一个索引列里选取最小值可以通过单独索引查找完成。更多explain解释参见MySQL Explain详解1、https://www.cnblogs.com/xuanzhi201111/p/4175635.html2、https://blog.csdn.net/weixin_44297303/article/details/991976373、https://www.jianshu.com/p/c64b857a9996
