实践了5千万的数据表和重建索引,学到了!
背景
项目中有一张历史记录表,主要用于记录一些接口调用流水,因为该表的地位不是那么重要,当初的创建者并未对核心字段创建索引。
不知不觉这张表的数据已经有5千万数据了,由于没有索引,在排查问题时,发现这种表根本查不动。
于是,决定下手进行分表并建立索引。这张表在系统中只负责插入,影响范围极小,正好拿来练手。
解决思路
我们知道,在Mysql 5.5及之前版本,在运行的生成环境中对大表执行alter操作,会引起表的重建和锁表,影响业务正常运转。
从Mysql 5.6开始,Online DDL特性被引进,运行alter操作时同时允许运行select、insert、update、delete语句。
在数据量小于100w时,可以考虑直接修改表结构建立索引,正常几秒钟就可以完成。但当表的数据量超过百万,无论Mysql 5.6及之前版本的锁表行为、Mysql 5.6中因慢SQL引起的等待,都不允许直接在生产库中进行alter table操作。
目前,五千万的数据,直接修改表来建立索引,肯定是不可行的,弄不好还把数据库给弄崩了。只能想另外的方法。
解决方案
鉴于这张表本身的影响范围有限,想到的解决方案就分表。无论是将所有数据一个区间一个区间的拆分出去,还是将整个表都换成新表,然后再处理历史数据,基本上都要做拆分处理。
基本解决思路:
第一步:创建一张数据结构一样的新表(补全索引),将业务切换至新表,这样新生成的数据便有了索引;
第二步:对旧表数据进行备份,已被后续处理过程中有问题进行恢复;
第三步:按照数据ID,1000万条数据拆分一个表,新拆分的表(补全索引);
对于分表的数据,数据库访问层并未使用,如果业务中有其他地方使用,则可考虑在数据库访问层根据请求时间区间或ID等来切换数据库表名。
基本操作
备份数据
数据库基于阿里云的云服务,导出数据有多种方式,比如直接copy出一张表、基于Navicat导出、基于mysqldump导出等。
copy出一张新表语句如下:
create table account_log_1 select * from account_log;
在测试环境上验证了一下,粗略估计该方式得1小时左右才能执行完数据的备份。
由于没有安装Mysql的linux生产服务器可用,就没采用mysqldump方式导致。
最终,采用在堡垒机上通过Navicat的导出功能,导出内容为SQL语句。
结果也很令人失望,5千万的数据:导出耗时1小时22分钟,导出SQL语句磁盘空间占用38.5G。还好在导出过程中,通过监控查看数据库的整体性能还比较平稳。
为了节省堡垒机的磁盘空间,又花费了十多分钟将38.5G的数据进行压缩,最终占用3.8G的存储空间。
Navicat与mysqldump性能对比
Navicat导出的数据是一条条的insert语句,每一行一条插入语句。
mysqldump导出的数据,多行数据合并成一行插入。批插入减少SQL语法词法解析,减少插事务(最大的开销),较少数据的传输;
数据分区
完成了数据备份,剩下的就是创建不同的新表,并安装分区将数据导入了。
复制表结构
执行表结构的copy:
create table account_log_1 like account_log;
创建一个结构一样的不带数据的表,并对表添加索引。然后再基于添加过索引的表,创建出account_log_2、account_log_3等表。
不同的表机构复制方式有所区别,复制完成之后,注意检查一下新表的主键、索引等是否存在。
由于该表并为具体的实际业务,而且表在设计时缺少创建时间字段,因此就以ID为区分,每1000w条数据一张表。
迁移数据
执行以下语句,直接将前1000w条数据插入到第一张表中:
INSERT INTO account_log_1 SELECT * FROM account_log WHERE id <= 10000000;
执行1000w条数据,用时205秒,大概3分钟25秒。粗略估算,5000万数据如果通过此种方式将全表数据备份,也只需要18分钟左右。
因此,上面到导出操作算是走的弯路,也见证了一下通过Navicat导出的性能问题。
验证数据
执行两条查询语句,验证一下导入新表的数据与原始数据的数据量是否一致:
select count(1) from account_log_1;
select count(1) from account_log WHERE id <= 10000000;
数据条数一致,验证无误。
删除历史数据
已经导入新表的历史数据(备份数据)是可以进行删除的,提升续查询速度。当然,如果该表已经不使用,则也可以暂时保留。
删除语句:
delete from account_log where id <= 10000000;
这里就暂时不删除了;
循环执行导入
后续操作就是循环执行导入操作了,将id的条件区间进行扩展:
INSERT INTO account_log_2 SELECT * FROM account_log WHERE id <= 20000000 and id > 10000000;
然后循环进行验证、删除等操作,直至整个大表被拆分完毕。
在循环查询插入的时候发现:未删除数据记录的情况下,处于中间部分的数据迁移耗时最长,主要原因就是查询时索引的特性决定的。
性能验证
验证count语句耗时:
select count(1) from account_log_2;
耗时,1.8秒查出结果;
顺便验证了一下count(id)、count(*)的查询,发现在1000w数据的情况下,性能差别并不明显。
select count(*) from account_log_2;
select count(id) from account_log_2;
在实验的过程中发现,Mysql可能进行了缓存处理,在第一次查的时候时间较长,后续再查就比较快了。
后续有验证了根据索引查询的效率,1000w数据中查询记录,800毫秒能能查询出结果来,提升效率非常显著;
大表数据迁移思考
经过此次大表数据迁移的实践,对大表迁移有了新的认知和直观感受。单纯的只看技术文章,感觉一切都轻而易举可以实施,但真正实践时才会发现有很懂可提升和改进的地方。
学到和一些值得思考的问题:
大表导出不仅要考虑导出的时间问题,还需要考虑导出数据的空间问题,以及衍生出来的存储和传输问题;
大数据读取与插入是否会造成表的死锁。一般,导出数据没有表锁,导出会对表加锁;
监控导出操作是否会对服务器实例的IO、带宽、内存造成影响,造成内存溢出等。
迁移的数据特殊类型例如(blob)会不会在导入的时候丢失;
不同的引擎之间是否会对导入数据有影响;
数据清空和表空间没有被释放
结果可以发现表数据被清空后,表所占空间大小并没有变化,这就验证了上面的结论,delete 操作并没有真正删除数据,表的空间并没有被释放。
这些被删除的记录行,只是被标记删除,是可以被复用的,下次有符合条件的记录是可以直接插入到这个被标记的位置的。
比如我们在 id 为 300-600 之间的记录中删除一条 id=500 的记录,这条记录就会被标记为删除,等下一次如果有一条 id=400 的记录要插入进来,那么就可以复用 id=500 被标记删除的位置,这种情况叫行记录复用。
还有一种情况是数据页复用,就是指整个数据页都被标记删除了,于是这整个数据页都可以被复用了,和行记录复用不同的是,数据页复用对要插入的数据几乎没有条件限制。
还以上面那个插入为例,假如要插入的记录是 id=1000,那么就不能复用 id=500 这个位置了,但如果有一整个数据页可复用的话,那么无论 id 值为多少都可以被复用在这个页上。
这些被标记删除的记录,其实就是一个空洞,有种占着茅坑不拉屎的感觉,浪费空间不说,还会影响查询效率。
因为你要知道,mysql 在底层是以数据页为单位来存储和读取数据的,每次向磁盘读一次数据就是读一个数据页,然而每访问一个数据页就对应一次磁盘 IO 操作,磁盘 IO 相对内存访问速度是相当慢的。
所以你想想,如果一个表上存在大量的数据空洞,原本只需一个数据页就保存的数据,由于被很多空洞占用了空间,不得不需要增加其他的数据页来保存数据,相应的,mysql 在查询相同数据的时候,就不得不增加磁盘 IO 操作,从而影响查询速度。
其实不仅仅是删除操作会造成数据空洞,插入和更新同样也会造成空洞,这里就不细说了,你知道就行。
因此,一个数据表在经过大量频繁的增删改之后,难免会产生数据空洞,浪费空间并影响查询效率,通常在生产环境中会直接表现为原本很快的查询会变得越来越慢。
对于这种情况,我们通常可以使用下面这个命令就能解决数据空洞问题。
optimize table t
这个命令的原理就是重建表,就是建立一个临时表 B,然后把表 A(存在数据空洞的表) 中的所有数据查询出来,接着把数据全部重新插入到临时表 B 中,***再用临时表 B 替换表 A 即可,这就是重建表的过程。
我们再来试验一下,看看效果。
mysql> optimize table t;
±-------±---------±---------±------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
±-------±---------±---------±------------------------------------------------------------------+
| test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status | OK |
±-------±---------±---------±------------------------------------------------------------------+
2 rows in set (0.39 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),‘M’) from tables where table_schema=‘test’ AND table_name=‘t’;
±------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),‘M’) |
±------------------------------------------------+
| 0.02M |
±------------------------------------------------+
1 row in set (0.00 sec)
可以看到表文件大小已经变成 0.02M了,说明表空间被释放了,这个 0.02M 应该是定义表结构文件的大小了。
另外下面这个命令也可以实现重建表,可以达到跟上面一样的效果,而且推荐大家使用下面这个命令,大家可以试试。
alter table t engine=InnoDB
注意本文内容是基于 InnoDB 引擎,对于其他引擎可能存在一些差异。原创不易,如果文章对你有启发,就点个在看吧,有疑问也可以在下面留言交流,也可以