最近要处理一张1.2亿行数据的mysql表,很多坑,记录一下
偏向于个人经验,无操作上的具体步骤
1、mysql单表数据量
网上查,使用普通服务器,单表最多2千万,调优5千万
我自己使用的感觉是,最好保持在5百万以下,整体数据文件大小在4G左右,再往上走,响应时间,插入速度变慢,即便你能调优,改配置,也只是稍有改进
2、索引
表一定要加索引,不管是更新还是查询,加索引与不加索引,数据处理速度相差很大很大,几千行的数据处理你不用加,如果是几千万,上亿的数据,一定要加
3、1.2亿行数据的处理
本人的原始数据要处理一下才能用,一个是计算两列的时间差,放到第三列中,这个update执行的时间尚可忍受
另一个是根据用户表的userid,username对应关系,把原始表中userid相同行的username设置为相应值,换成sql语句就是
update A as a,B as b set a.username=b.username where a.userid=b.userid
问题就出在这里,执行太慢了,用户表不到3K行,我把原始表1.2亿数据切分成若干个小表,其中一张表包含780万行,更新早上9点开始执行,服务器运行,到第二天才结束,不能接受
所以把数据导入mongodb处理,mongodb的数据处理速度的确是快很多,但是mysql里我一句话能执行的内容,到mongodb里要写脚本,抽取数据逐个处理,唉,后来也放弃了
3、导入导出数据
我要将处理好的数据从mysql多张临时表导出成sql语句,然后再一起插入正式表
-
mysqldump导出sql文件
如果你不加参数,会发现导出的文件包含了很多备注,还有create table, drop table语句,我只想要insert语句,所以语句应该是
mysqldump -ueee -p11111 --compact -t -c jobs htcondor>/home/mycat/temp/tablename.sql
参数1:–compact 不要注释语句
参数2:-t 表示只要数据,不要结构
参数3:-c 表示要加列名
mycat在导入数据的时候必须要列名
参数4: --compact --extended-insert=false 这个很不建议用,默认一条insert语句会插入4K左右的数据,加上这个参数,一条insert语句只插入一行数据,这样导入太慢了,插入1000万条数据花了10个小时,如果不用这个参数1.2亿数据花了4个小时~~~~~
导入导出,加上mycat,这其中有很多坑,不是你随便导出个数据就能导入的,比如我数据量太大,导入mycat时总是报错,修改了文件缓存大小,让其支持2G文件,导入还报错连接时长超时等等。这时就得一个一个排除尝试,费时间费力气
好在最后结果是好的
步骤1:
写了mysql函数,按照时间把原始数据切分成小表,并做好数据处理
步骤2:
写了mysql函数,批量导出mysqldump多个表的数据
步骤3:
写了mysql函数,批量导入mycat
以后类似的数据处理会探索一下用其他办法,问过做spark,ES的同事,处理也需要几个小时的时间,有好经验的同学请留言
看过其他大公司的一些数据库PPT,数据处理转换这块都没有仔细写