快速插入100w條數(shù)據(jù)到mysql

表結(jié)構(gòu):

CREATE TABLE `t_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `creator` varchar(16) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'admin' COMMENT '創(chuàng)建人',
  `editor` varchar(16) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'admin' COMMENT '修改人',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間',
  `edit_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  `version` bigint(20) NOT NULL DEFAULT '1' COMMENT '版本號',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '軟刪除標識',
  `order_id` varchar(32) COLLATE utf8mb4_general_ci NOT NULL COMMENT '訂單ID',
  `amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '訂單金額',
  `payment_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '支付時間',
  `order_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '訂單狀態(tài),0:處理中,1:支付成功,2:支付失敗',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_id` (`order_id`),
  KEY `idx_payment_time` (`payment_time`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='訂單表';

存儲過程:

CREATE DEFINER=`root`@`%` PROCEDURE `proc_user`(init_num BIGINT, target BIGINT )
BEGIN
DECLARE v_num int(4) DEFAULT 0;
SET autocommit = 0;
while init_num <= target DO
INSERT INTO `t_order` ( `creator`, `editor`, `create_time`, `edit_time`, `version`, `deleted`, `order_id`, `amount`, `payment_time`, `order_status` )
VALUES
    ( '1', 'admin', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1, 0, init_num, 1.00, CURRENT_TIMESTAMP, 0 );

  SET init_num = init_num + 1;
  SET v_num = v_num + 1;
  IF v_num >= 100 THEN
   SET v_num = 0;
   COMMIT;
  END IF;
end while;
commit;

END

這里涉及到需要調(diào)整mysql的兩個參數(shù):
innodb_flush_log_at_trx_commit
sync_binlog
默認情況下
innodb_flush_log_at_trx_commit=1
sync_binlog=1
我們來了解下這兩個參數(shù)的定義:
innodb_flush_log_at_trx_commit
提交事務(wù)的時候?qū)?redo 日志寫入磁盤中闺阱,所謂的 redo 日志纲菌,就是記錄下來你對數(shù)據(jù)做了什么修改枉长,比如對 “id=10 這行記錄修改了 name 字段的值為 xxx”,這就是一個日志撒蟀。如果我們想要提交一個事務(wù)了,此時就會根據(jù)一定的策略把 redo 日志從 redo log buffer 里刷入到磁盤文件里去。此時這個策略是通過 innodb_flush_log_at_trx_commit 來配置的捐迫,他有幾個選項。
值為0 : 提交事務(wù)的時候籽腕,不立即把 redo log buffer 里的數(shù)據(jù)刷入磁盤文件的嗡呼,而是依靠 InnoDB 的主線程每秒執(zhí)行一次刷新到磁盤。此時可能你提交事務(wù)了皇耗,結(jié)果 mysql 宕機了南窗,然后此時內(nèi)存里的數(shù)據(jù)全部丟失。
值為1 : 提交事務(wù)的時候郎楼,就必須把 redo log 從內(nèi)存刷入到磁盤文件里去万伤,只要事務(wù)提交成功,那么 redo log 就必然在磁盤里了呜袁。注意敌买,因為操作系統(tǒng)的“延遲寫”特性,此時的刷入只是寫到了操作系統(tǒng)的緩沖區(qū)中阶界,因此執(zhí)行同步操作才能保證一定持久化到了硬盤中虹钮。
值為2: 提交事務(wù)的時候,把 redo 日志寫入磁盤文件對應(yīng)的 os cache 緩存里去荐操,而不是直接進入磁盤文件芜抒,可能 1 秒后才會把 os cache 里的數(shù)據(jù)寫入到磁盤文件里去。
可以看到托启,只有1才能真正地保證事務(wù)的持久性宅倒,但是由于刷新操作 fsync() 是阻塞的,直到完成后才返回屯耸,我們知道寫磁盤的速度是很慢的拐迁,因此 MySQL 的性能會明顯地下降。如果不在乎事務(wù)丟失疗绣,0和2能獲得更高的性能线召。

image.png

從場景來看,我們需要快速插入多矮,可以將innodb_flush_log_at_trx_commit=2缓淹;

sync_binlog
該參數(shù)控制著二進制日志寫入磁盤的過程。
該參數(shù)的有效值為0 塔逃、1讯壶、N:
0:默認值。事務(wù)提交后湾盗,將二進制日志從緩沖寫入磁盤伏蚊,但是不進行刷新操作(fsync()),此時只是寫入了操作系統(tǒng)緩沖格粪,若操作系統(tǒng)宕機則會丟失部分二進制日志躏吊。

1:事務(wù)提交后氛改,將二進制文件寫入磁盤并立即執(zhí)行刷新操作,相當于是同步寫入磁盤比伏,不經(jīng)過操作系統(tǒng)的緩存胜卤。

N:每寫N次操作系統(tǒng)緩沖就執(zhí)行一次刷新操作。

將這個參數(shù)設(shè)為1以上的數(shù)值會提高數(shù)據(jù)庫的性能凳怨,但同時會伴隨數(shù)據(jù)丟失的風險瑰艘。
二進制日志文件涉及到數(shù)據(jù)的恢復(fù),以及想在主從之間獲得最大的一致性肤舞,那么應(yīng)該將該參數(shù)設(shè)置為1紫新,但同時也會造成一定的性能損耗。

從場景來看李剖,我們需要快速插入芒率,可以將sync_binlog=500;
測試結(jié)果:

參數(shù)場景 (100w) 耗時 (s)
innodb_flush_log_at_trx_commit=1, sync_binlog=1 577.491
innodb_flush_log_at_trx_commit=2, sync_binlog=500 55.534
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末篙顺,一起剝皮案震驚了整個濱河市偶芍,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌德玫,老刑警劉巖匪蟀,帶你破解...
    沈念sama閱讀 211,639評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件,死亡現(xiàn)場離奇詭異宰僧,居然都是意外死亡材彪,警方通過查閱死者的電腦和手機,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,277評論 3 385
  • 文/潘曉璐 我一進店門琴儿,熙熙樓的掌柜王于貴愁眉苦臉地迎上來段化,“玉大人,你說我怎么就攤上這事造成∠匝” “怎么了?”我有些...
    開封第一講書人閱讀 157,221評論 0 348
  • 文/不壞的土叔 我叫張陵晒屎,是天一觀的道長喘蟆。 經(jīng)常有香客問我,道長鼓鲁,這世上最難降的妖魔是什么履肃? 我笑而不...
    開封第一講書人閱讀 56,474評論 1 283
  • 正文 為了忘掉前任,我火速辦了婚禮坐桩,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘封锉。我一直安慰自己绵跷,他們只是感情好膘螟,可當我...
    茶點故事閱讀 65,570評論 6 386
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著碾局,像睡著了一般荆残。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上净当,一...
    開封第一講書人閱讀 49,816評論 1 290
  • 那天内斯,我揣著相機與錄音,去河邊找鬼像啼。 笑死俘闯,一個胖子當著我的面吹牛,可吹牛的內(nèi)容都是我干的忽冻。 我是一名探鬼主播真朗,決...
    沈念sama閱讀 38,957評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼僧诚!你這毒婦竟也來了遮婶?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,718評論 0 266
  • 序言:老撾萬榮一對情侶失蹤湖笨,失蹤者是張志新(化名)和其女友劉穎旗扑,沒想到半個月后,有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體慈省,經(jīng)...
    沈念sama閱讀 44,176評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡臀防,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,511評論 2 327
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了辫呻。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片清钥。...
    茶點故事閱讀 38,646評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖放闺,靈堂內(nèi)的尸體忽然破棺而出祟昭,到底是詐尸還是另有隱情,我是刑警寧澤怖侦,帶...
    沈念sama閱讀 34,322評論 4 330
  • 正文 年R本政府宣布篡悟,位于F島的核電站,受9級特大地震影響匾寝,放射性物質(zhì)發(fā)生泄漏搬葬。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 39,934評論 3 313
  • 文/蒙蒙 一艳悔、第九天 我趴在偏房一處隱蔽的房頂上張望急凰。 院中可真熱鬧,春花似錦猜年、人聲如沸抡锈。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,755評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽床三。三九已至一罩,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間撇簿,已是汗流浹背聂渊。 一陣腳步聲響...
    開封第一講書人閱讀 31,987評論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留四瘫,地道東北人汉嗽。 一個月前我還...
    沈念sama閱讀 46,358評論 2 360
  • 正文 我出身青樓,卻偏偏與公主長得像莲组,于是被迫代替她去往敵國和親诊胞。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當晚...
    茶點故事閱讀 43,514評論 2 348

推薦閱讀更多精彩內(nèi)容