电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

MySQL 億級數據導入導出及遷移筆記

瀏覽:43日期:2023-10-01 16:50:21

最近MySQL的筆記有點多了,主要是公司Oracle比較穩定維護較少,上周被安排做了一個MySQL億級數據的遷移,趁此記錄下學習筆記;

數據遷移,工作原理和技術支持數據導出、BI報表之類的相似,差異較大的地方是導入和導出數據量區別,一般報表數據量不會超過幾百萬,而做數據遷移,如果是互聯網企業經常會涉及到千萬級、億級以上的數據量。

導入和導出是兩個過程,即使做數據遷移我們也要分開來看,同時,導入/導出方式又分為:

1、MySQL自帶導入/導出方式

2、各類客戶端導入/導出方式

先總結下導出:

1、導出對于字段較少/字段內容較少的數據,通過客戶端方式可以采用navicat等工具導出,我這里本次導出三個字段,都是11位數字以內的值,用navicat導出每分鐘大約250萬數據,

2、MySQL自帶的導出語句:select into outfile語句;

SELECT ... FROM TABLE_A --可以加where條件INTO OUTFILE '/path/to/file' --導出文件位置FIELDS TERMINATED BY ’,’ OPTIONALLY ENCLOSED BY ’'’ -- 字段分割符和包含符LINES TERMINATED BY ’n’;--換行符

這里fields之前很簡單都可看懂,不做說明,講下fields之后的:

FIELDS TERMINATED BY ’,’ 代表我字段和字段之間用 逗號 分開 ,如:字段 A 字段 B,導出時候顯示格式為:A,B

OPTIONALLY ENCLOSED BY ’'’ 代表字段內容用雙引號包含,導出格式如: 'A','B'

LINES TERMINATED BY ’n’;每條數據換行區分,導出格式如:

'A','B'

'A1','B1'

當然,字段區分和包含符號可以自行定義,定義為:’ # 都可以

用MySQL自帶導出/導入優點是速度極快,缺點是:只能導出文件是在服務器主機所在的本機地址,對于bi之類拿到不數據庫主機權限的同事這個方式可能奢望了。不過好在對于字段/內容較少的報表第三方客戶端工具導出速度也不算特別慢;

導入:

重點記錄導入,導入主要是dba做數據遷移了,方式也分客戶端和MySQL自帶方式:

這里極度推薦用MySQL導入方式,原因是我之前要遷移1.3億數據,用navicat客戶端導入數據要22小時,耗時太長且不確定太多,本身navicat等工具就會有假死風險的存在,所不建議超過1萬以上的數據通過navicat導入;

MySQL自帶導入方式:

--官方文檔定義如下,注釋是我自己理解添加的:

LOAD DATA 、[LOW_PRIORITY | CONCURRENT]--無人使用數據庫再執行/立即執行[LOCAL]--帶這個參數指服務端即不是服務器主機上讀取文件,不帶這個參數是默認在服務器主機上讀取文件INFILE ’file_name’ --讀取文件地址、文件名 [REPLACE | IGNORE]--遇到重復數據是:替換/重復寫入,建議使用ignore重復寫入 INTO TABLE tbl_name --導入到那個表 [PARTITION (partition_name [, partition_name] ...)]--這行參數可以不要,建議用后面的fields [CHARACTER SET charset_name]--設定導入內容字符格式,utf-8還是GBK等都可以指定 [{FIELDS | COLUMNS} --fields標識符[TERMINATED BY ’string’] --系統字段通過什么符號區分[[OPTIONALLY] ENCLOSED BY ’char’]--系統字段本身的起始和結束用什么符號區分[ESCAPED BY ’char’]--轉義符,如果是文本文件,在文本字段中有特殊字符如雙引號,可通過定義轉義符忽略文本文件特殊字符 ] [LINES --lines標識符[STARTING BY ’string’] --定義行開頭的字符串,如果行開頭沒有字符標識,一般可以不寫[TERMINATED BY ’string’]--行結束字符串標識,通過定義字符來區分行與行的數據 ] [IGNORE number {LINES | ROWS}]--忽略文件中前面多少行,一般都不寫--后面都是指定插入到哪些字段 [(col_name_or_user_var[, col_name_or_user_var] ...)] [SET col_name={expr | DEFAULT},[, col_name={expr | DEFAULT}] ...]

MySQL 億級數據導入導出及遷移筆記

原文上說的用load data能速度極快的導入數據到數據庫中,但是如果要使用fields和lines參數,則必須要帶一個參數值且fields必須在lines參數之前;

本次我使用的語句是:

load data infile ’/data/files/T_CUST_INFO.txt’ --默認指定服務器文件夾ignore into table t_dq_user --允許重復記錄插入fields terminated by ’,’ --判斷字段通過逗號標識來分隔開lines terminated by ’n’(CustID,DeviceNo,logintype);--通過換行標識來解析成為每一條數據和插入到我指定的字段

插入是很簡單的語句,這里我不做具體舉例操作,我要分享的是如何提高插入效率;

因為在我第一次用語句插入的時候,從晚上12點開始執行,到第二天11點還沒有執行完。所以不是說用了load不配置其他的就一定很快;

本次我插入的數據格式如圖:

MySQL 億級數據導入導出及遷移筆記

文本格式如下:

MySQL 億級數據導入導出及遷移筆記

一共有1億4千萬條數據,以文本文檔的形式導出的,有4.3G大小;通過ftp軟件上傳到服務器/data/files文件夾中;

吐槽點1:

由于項目要求三個字段都要有索引,所以我在建表的時候就加了索引,導致耗時遙遙無期;

原因:

索引是要占空間的,如果導入三個字段都要加索引,代表了我要每個字段都寫入索引一次,耗時比不加索引多了幾倍;

優化方法:

導入前把表的索引去掉留自增id一個,等導入完之后再添加

吐槽點2:

engine的選擇:

MySQL的engine對如load寫入是不一樣的,特別是有master-slave主從備份的機制:

對MyISAM引擎: (1)對master服務器進行 ‘load’ 操作, (2)在master上所操作的load.txt文件,會同步傳輸到slave上,并在tmp_dir 目錄下生成 load.txt文件master服務器插入了多少,就傳給slave多少 (3)當master上的load操作完成后,傳給slave的文件也結束時,即:在slave上生成完整的 load.txt文件此時,slave才開始從 load.txt 讀取數據,并將數據插入到本地的表中

對innodb引擎: (1)主數據庫進行 ‘Load’ 操作 (2)主數據庫操作完成后,才開始向slave傳輸 load.txt文件,slave接受文件,并在 tmp_dir 目錄下生成 load.txt 文件接受并生成完整的load.txt 后,才開始讀取該文件,并將數據插入到本地表中

所以追求極致速度,幾十億數據的,可以考慮選擇myisam引擎,MySQL默認應該是innodb;不過本次我并沒有更改引擎,本人不推薦更改默認的innodb引擎,畢竟Oracle官方主推引擎,綜合性最強,除非有特殊性,不推薦使用myisam。如果用了myisam,注意一下兩點:

用了myisam,可以調整幾個session值擴大讀取內存,提高讀取數據,語句如下:

SET SESSION BULK_INSERT_BUFFER_SIZE = 256217728 ;SET SESSION MYISAM_SORT_BUFFER_SIZE = 256217728 ;

對于MyISAM引擎,導入前的唯一校驗可以先關閉,之后再打開:

SET UNIQUE_CHECKS=0 --關閉SET UNIQUE_CHECKS=1 --打開吐槽點3:

雖然MySQL支持本地客戶端讀取文件,但是由于各種網絡原因,在幾十幾百條數據的情況下沒有什么影響,但是到了億級數據量,即使1毫秒的影響也會放的特別大,所以建議用ftp傳到服務器上進行讀取

吐槽點4:

經驗分享,導入之后看看服務器狀態:top 命令看看主機cpu MySQL占用情況,理論上會占用較多cpu,我的第一次耗時賊長的那次,cpu占用10%,這是極度不正常的導入,第二次正常導入cpu占用到了110%,這才是再急速寫入的狀態;最后1.4億數據只耗時:7分多中,所以一定要在執行語句后監控下服務器,否則語句不一定在正常執行。

MySQL 億級數據導入導出及遷移筆記

cpu占用:

MySQL 億級數據導入導出及遷移筆記

注意:load和insert最大的區別是:load只操作語法一次,之后就是一直是數據批量插入,而insert 是每一個數據操作一次,也遍歷一次字段索引,所以insert本身對于大數據來說是極慢的。

總結:

本次優化我感覺最大最明顯的變化是,去除索引后,導入速度極快,索引,重要的事情再說一遍:

導入時候可以先去掉索引,導入完之后再添加。

2020.7.3更新

MySQL導入大數據時一定要注意max最大事物限制,前幾個月在做數據遷移時,在MySQL8.0 MGR集群上發生了大事物限制導致實例出問題重啟了MySQL,默認配置應該是一億五千萬的事物限制,當時導入的數據比較大也沒做參數擴展同時也沒做數據切分導入或者限流導入,導致數據庫堵塞重啟,按照公司要求7*24*365機制,這算是事故了,如果高要求的公司,建議導入的時候注意MySQL本身配置或者導入進行事物提交限制;

到此這篇關于MySQL 億級數據導入導出及遷移筆記的文章就介紹到這了,更多相關MySQL 億級數據導入導出及遷移內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 气动绞车,山东气动绞车,气动绞车厂家-烟台博海石油机械有限公司 气动隔膜泵厂家-温州永嘉定远泵阀有限公司 | 不锈钢散热器,冷却翅片管散热器厂家-无锡市烨晟化工装备科技有限公司 | 烟台金蝶财务软件,烟台网站建设,烟台网络推广 | 济南网站策划设计_自适应网站制作_H5企业网站搭建_济南外贸网站制作公司_锐尚 | 进口试验机价格-进口生物材料试验机-西安卡夫曼测控技术有限公司 | 车充外壳,车载充电器外壳,车载点烟器外壳,点烟器连接头,旅行充充电器外壳,手机充电器外壳,深圳市华科达塑胶五金有限公司 | 【北京写字楼出租_写字楼租赁_办公室出租网/出售】-远行地产官网 | 合肥白癜风医院_合肥治疗白癜风医院_合肥看白癜风医院哪家好_合肥华研白癜风医院 | 储能预警-储能消防系统-电池舱自动灭火装置-四川千页科技股份有限公司官网 | 礼至家居-全屋定制家具_一站式全屋整装_免费量房设计报价 | 神超官网_焊接圆锯片_高速钢锯片_硬质合金锯片_浙江神超锯业制造有限公司 | 超声波焊接机,振动摩擦焊接机,激光塑料焊接机,超声波焊接模具工装-德召尼克(常州)焊接科技有限公司 | 济南展厅设计施工_数字化展厅策划设计施工公司_山东锐尚文化传播有限公司 | 智能电表|预付费ic卡水电表|nb智能无线远传载波电表-福建百悦信息科技有限公司 | 世界箱包品牌十大排名,女包小众轻奢品牌推荐200元左右,男包十大奢侈品牌排行榜双肩,学生拉杆箱什么品牌好质量好 - Gouwu3.com | 深圳希玛林顺潮眼科医院(官网)│深圳眼科医院│医保定点│香港希玛林顺潮眼科中心连锁品牌 | 超声波清洗机_大型超声波清洗机_工业超声波清洗设备-洁盟清洗设备 | 北京模型公司-工业模型-地产模型-施工模型-北京渝峰时代沙盘模型制作公司 | 【365公司转让网】公司求购|转让|资质买卖_股权转让交易平台 | 北京百度网站优化|北京网站建设公司-百谷网络科技 | 苏州工作服定做-工作服定制-工作服厂家网站-尺品服饰科技(苏州)有限公司 | 真空搅拌机-行星搅拌机-双行星动力混合机-广州市番禺区源创化工设备厂 | 防火阀、排烟防火阀、电动防火阀产品生产销售商-德州凯亿空调设备有限公司 | 济南货架定做_仓储货架生产厂_重型货架厂_仓库货架批发_济南启力仓储设备有限公司 | 耐力板-PC阳光板-PC板-PC耐力板 - 嘉兴赢创实业有限公司 | 丝杆升降机-不锈钢丝杆升降机-非标定制丝杆升降机厂家-山东鑫光减速机有限公司 | 干法制粒机_智能干法制粒机_张家港市开创机械制造有限公司 | 老房子翻新装修,旧房墙面翻新,房屋防水补漏,厨房卫生间改造,室内装潢装修公司 - 一修房屋快修官网 | ph计,实验室ph计,台式ph计,实验室酸度计,台式酸度计 | 机床导轨_导轨板_滚轮导轨-上海旻佑精密机械有限公司 | 合肥升降机-合肥升降货梯-安徽升降平台「厂家直销」-安徽鼎升自动化科技有限公司 | COD分析仪|氨氮分析仪|总磷分析仪|总氮分析仪-圣湖Greatlake | 多米诺-多米诺世界纪录团队-多米诺世界-多米诺团队培训-多米诺公关活动-多米诺创意广告-多米诺大型表演-多米诺专业赛事 | 污水/卧式/潜水/钻井/矿用/大型/小型/泥浆泵,价格,参数,型号,厂家 - 安平县鼎千泵业制造厂 | 深圳湾1号房价_深圳湾1号二手房源| 杭州荣奥家具有限公司-浙江办公家具,杭州办公家具厂 | 过滤器_自清洗过滤器_气体过滤器_苏州华凯过滤技术有限公司 | 黑田精工电磁阀-CAMMOZI气缸-ROSS电磁-上海茂硕机械设备有限公司 | 3D全息投影_地面互动投影_360度立体投影_水幕灯光秀 | 广东银虎 蜂窝块状沸石分子筛-吸附脱硫分子筛-萍乡市捷龙环保科技有限公司 | 定坤静电科技静电消除器厂家-除静电设备 | 成人纸尿裤,成人尿不湿,成人护理垫-山东康舜日用品有限公司 |