色哟哟视频在线观看-色哟哟视频在线-色哟哟欧美15最新在线-色哟哟免费在线观看-国产l精品国产亚洲区在线观看-国产l精品国产亚洲区久久

0
  • 聊天消息
  • 系統消息
  • 評論與回復
登錄后你可以
  • 下載海量資料
  • 學習在線課程
  • 觀看技術視頻
  • 寫文章/發帖/加入社區
會員中心
創作中心

完善資料讓更多小伙伴認識你,還能領取20積分哦,立即完善>

3天內不再提示

MySQL在執行批量操作的時候一次插入多少數據才合適呢?

jf_ro2CN3Fa ? 來源:CSDN ? 2023-01-31 14:09 ? 次閱讀

一、前言

我們在操作大型數據表或者日志文件的時候經常會需要寫入數據到數據庫,那么最合適的方案就是數據庫的批量插入。只是我們在執行批量操作的時候,一次插入多少數據才合適呢?

假如需要插入的數據有百萬條,那么一次批量插入多少條的時候,效率會高一些呢?這里博主和大家一起探討下這個問題,應用環境為批量插入數據到臨時表。

基于 Spring Boot + MyBatis Plus + Vue & Element 實現的后臺管理系統 + 用戶小程序,支持 RBAC 動態權限、多租戶、數據權限、工作流、三方登錄、支付、短信、商城等功能

二、批量插入前準備

博主本地原本是循環查出來的數據,然后每1000條插入一次,直至完成插入操作。但是為什么要設置1000條呢,實不相瞞,這是因為項目里的其他批量插入都是一次插1000條。。汗,博主不服,所以想要測試下。

首先是查看當前數據庫的版本,畢竟各個版本之間存在差異,脫離版本講數據庫就是耍流氓(以前沒少耍啊):

mysql>selectversion();
+------------+
|version()|
+------------+
|5.6.34-log|
+------------+
1rowinset(0.00sec)

1、插入到數據表的字段

對于手動創建的臨時表來說,字段當然是越少越好,而且字段占用的空間要盡量小一些,這樣臨時表不至于太大,影響表操作的性能。這里需要插入的字段是:

字段1int(10)
字段2int(10)
字段3int(10)
字段4varchar(10)

我們一共插入四個字段,分別是3個int類型的,一個varchar類型的,整體來說這些字段都比較小,占用的內存空間會小一些。

2、計算一行字段占用的空間

對于innodb引擎來說,int類型可以存儲4個字節,里面的Int(M)并不會影響存儲字節的大小,這個M只是數據的展示位數,和mysql的ZEROFILL屬性有關,即在數字長度不夠的數據前面填充0,以達到設定的長度。此處不多說,想要了解的朋友可以百度一下,還是很有意思的。

varchar(10)代表可以存儲10個字符,不管是英文還是中文,最多都是10個,這部分假設存儲的是中文,在utf-8mb4下,10個中文占用10*4 = 40個字節那么一行數據最多占用:4+4+4+40 = 52字節

3、在數據里做插入操作的時候,整體時間的分配

鏈接耗時(30%)
發送query到服務器(20%)
解析query(20%)
插入操作(10%*詞條數目)
插入index(10%*Index的數目)
關閉鏈接(10%)

從這里可以看出來,真正耗時的不是操作,而是鏈接,解析的過程。單條sql的話,會在鏈接,解析部分耗費大量的時間,因此速度會很慢,所以我們一般都是采用批量插入的操作,爭取在一次鏈接里面寫入盡可能多的數據,以此來提升插入的速度。但是這個盡可能多的數據是多少呢?一次到底插入多少才合適呢?

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實現的后臺管理系統 + 用戶小程序,支持 RBAC 動態權限、多租戶、數據權限、工作流、三方登錄、支付、短信、商城等功能

三、批量插入數據測試

開始測試,但是一開始插入多少是合適的呢,是否有上限?查詢mysql手冊,我們知道sql語句是有大小限制的。

1、SQL語句的大小限制

my.ini 里有 max_allowed_packet 這個參數控制通信的 packet 大小。mysql默認的sql語句的最大限制是1M(mysql5.7的客戶端默認是16M,服務端默認是4M),可以根據設置查看。官方解釋是適當增大 max_allowed_packet 參數可以使client端到server端傳遞大數據時,系統能夠分配更多的擴展內存來處理。

2、查看服務器上的參數:

mysql>showvariableslike'%max_allowed_packet%';
+--------------------------+------------+
|Variable_name|Value|
+--------------------------+------------+
|max_allowed_packet|33554432|
|slave_max_allowed_packet|1073741824|
+--------------------------+------------+
2rowsinset(0.00sec)

33554432字節 = 32M ,也就是規定大小不能超過32M。

3、計算一次能插入的最大行記錄

1M計算的話,(1024*1024)/52 ≈ 20165 ,為了防止溢出,最大可一次性插入20000條(根據自己的配置和sql語句大小計算)。那么32M的話就是:20000 *32 = 640000 也就是64W條。

4、測試插入數據比對

(1)插入11W條數據,按照每次10,600,1000,20000,80000來測試:

+---------------+
|count(c1.uin)|
+---------------+
|110000|
+---------------+

有個博客說一次插入10條最快,,我覺得一次插的有點少,咱們試試

這個博主測試后,認為一次插10條是性能最快的,他的每條記錄是3kb,相當于我的59行數據,取個整數60,那么對于這個博主是插入10條,對我來說插入:600,這幾個值都試試。

耗時:

11W的數據,每次插入10條。耗時:2.361s
11W的數據,每次插入600條。耗時:0.523s
11W的數據,每次插入1000條。耗時:0.429s
11W的數據,每次插入20000條。耗時:0.426s
11W的數據,每次插入80000條。耗時:0.352s

從這部分看,隨著批量插入的增加,速度略有提升,最起碼一次插10條應該不是最佳的。插入數據量多,減少了循環的次數,也就是在數據庫鏈接部分的耗時有所減少,只是這個8W并不是極限數據,具體一次插入多少條,還有待參考。

(2)加大數據量到24w

+---------------+
|count(c1.uin)|
+---------------+
|241397|
+---------------+

耗時:

24W的數據,每次插入10條。耗時:4.445s
24W的數據,每次插入600條。耗時:1.187s
24W的數據,每次插入1000條。耗時:1.13s
24W的數據,每次插入20000條。耗時:0.933s
24W的數據,每次插入80000條。耗時:0.753s

一次插入24W反而性能最佳,這么代表我們的測試數據量依然不夠。

(3)加大測試量到42W

+---------------+
|count(c1.uin)|
+---------------+
|418859|

耗時:

42W的數據,每次插入1000條。耗時:2.216s
42W的數據,每次插入80000條。耗時:1.777s
42W的數據,每次插入16W條。耗時:1.523s
42W的數據,每次插入20W條。耗時:1.432s
42W的數據,每次插入30W條。耗時:1.362s
42W的數據,每次插入40W條。耗時:1.764s

隨著插入量的增加,批量插入條數多了之后,性能是有所提升的。但是在達到30W以上之后,效率反而有所下降。這部分我的理解是mysql是要分配一定的內存給傳過來的數據包使用,當批量插入的數據量到達一定程度之后,一次插入操作的開銷就很耗費內存了。

個人感覺,最佳大小是max_allowed_packet的一半,也就是極限能插入64W,選用32W也許性能會更好一些,同時也不會對mysql的其他操作產生太大的影響。

5、如果插入的值就是sql語句限制的最大值,那么性能真的好嗎?

博主瘋狂谷歌百度,都沒有找到有人來具體的說一下這個問題,不過在高性能mysql里面發現一句話:

客戶端用一個單獨的數據包將查詢請求發送給服務器,所以當查詢語句很長的時候,需要設置max_allowed_packet參數。但是需要注意的是,如果查詢實在是太大,服務端會拒絕接收更多數據并拋出異常。與之相反的是,服務器響應給用戶的數據通常會很多,由多個數據包組成。

但是當服務器響應客戶端請求時,客戶端必須完整的接收整個返回結果,而不能簡單的只取前面幾條結果,然后讓服務器停止發送。因而在實際開發中,盡量保持查詢簡單且只返回必需的數據,減小通信間數據包的大小和數量是一個非常好的習慣,這也是查詢中盡量避免使用SELECT *以及加上LIMIT限制的原因之一。

后面通過各種百度,博主覺得最大只是代表傳輸數據包的最大長度,但性能是不是最佳就要從各個方面來分析了。比如下面列出的插入緩沖,以及插入索引時對于緩沖區的剩余空間需求,以及事務占有的內存等,都會影響批量插入的性能。

四、其他影響插入性能的因素

1、首先是插入的時候,要注意緩沖區的大小使用情況

在分析源碼的過程中,有一句話:如果buffer pool余量不足25%,插入失敗,返回DB_LOCK_TABLE_FULL。這個錯誤并不是直接報錯:max_allowed_packet 不夠大之類的,這個錯誤是因為對于innodb引擎來說,一次插入是涉及到事務和鎖的,在插入索引的時候,要判斷緩沖區的剩余情況,所以插入并不能僅僅只考慮max_allowed_packet的問題,也要考慮到緩沖區的大小。

2、插入緩存

另外對于innodb引擎來說,因為存在插入緩存(Insert Buffer)這個概念,所以在插入的時候也是要耗費一定的緩沖池內存的。當寫密集的情況下,插入緩沖會占用過多的緩沖池內存,默認最大可以占用到1/2的緩沖池內存,當插入緩沖占用太多緩沖池內存的情況下,會影響到其他的操作。

也就是說,插入緩沖受到緩沖池大小的影響,緩沖池大小為:

mysql>showvariableslike'innodb_buffer_pool_size';
+-------------------------+-----------+
|Variable_name|Value|
+-------------------------+-----------+
|innodb_buffer_pool_size|134217728|
+-------------------------+-----------+

換算后的結果為:128M,也就是說,插入緩存最多可以占用64M的緩沖區大小。這個大小要超過咱們設置的sql語句大小,所以可以忽略不計。

詳細解釋:

我們都知道,在InnoDB引擎上進行插入操作時,一般需要按照主鍵順序進行插入,這樣才能獲得較高的插入性能。當一張表中存在非聚簇的且不唯一的索引時,在插入時,數據頁的存放還是按照主鍵進行順序存放,但是對于非聚簇索引葉節點的插入不再是順序的了,這時就需要離散的訪問非聚簇索引頁,由于隨機讀取的存在導致插入操作性能下降。

InnoDB為此設計了Insert Buffer來進行插入優化。對于非聚簇索引的插入或者更新操作,不是每一次都直接插入到索引頁中,而是先判斷插入的非聚集索引是否在緩沖池中,若在,則直接插入;若不在,則先放入到一個Insert Buffer中。

看似數據庫這個非聚集的索引已經查到葉節點,而實際沒有,這時存放在另外一個位置。然后再以一定的頻率和情況進行Insert Buffer和非聚簇索引頁子節點的合并操作。這時通常能夠將多個插入合并到一個操作中,這樣就大大提高了對于非聚簇索引的插入性能。

3、使用事務提升效率

還有一種說法,使用事務可以提高數據的插入效率,這是因為進行一個INSERT操作時,MySQL內部會建立一個事務,在事務內才進行真正插入處理操作。通過使用事務可以減少創建事務的消耗,所有插入都在執行后才進行提交操作。大概如下:

STARTTRANSACTION;
INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)
VALUES('0','userid_0','content_0',0);
INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)
VALUES('1','userid_1','content_1',1);
...
COMMIT;

參考:https://my.oschina.net/songhongxu/blog/163063

事務需要控制大小,事務太大可能會影響執行的效率。MySQL有innodb_log_buffer_size配置項,超過這個值會把innodb的數據刷到磁盤中,這時,效率會有所下降。所以比較好的做法是,在數據達到這個這個值前進行事務提交。

查看:show variables like '%innodb_log_buffer_size%';

+------------------------+----------+
|Variable_name|Value|
+------------------------+----------+
|innodb_log_buffer_size|67108864|
+------------------------+----------+

大概是:64M

這種寫法和批量寫入的效果差不多,只不過sql語句還是單句的,然后統一提交。一個瓶頸是SQL語句的大小,一個瓶頸是事務的大小。當我們在提交sql的時候,首先是受到sql大小的限制,其次是受到事務大小的限制。在開啟事務的情況下使用批量插入,會節省不少事務的開銷,如果要追求極致的速度的話,建議是開著事務插入的。

不過需要注意一下,內存是有限且共享的,如果批量插入占用太多的事務內存,那么勢必會對其他的業務操作等有一定的影響。

4、通過配置提升讀寫性能

也可以通過增大innodb_buffer_pool_size 緩沖區來提升讀寫性能,只是緩沖區是要占用內存空間的,內存很珍貴,所以這個方案在內存富裕,而性能瓶頸的時候,可以考慮下。

5、索引影響插入性能

如果表中存在多個字段索引,當對表中的數據進行增加、刪除和修改的時候,索引也要動態的維護。這樣就降低了數據的插入速度。對于普通的數據表,主鍵索引是肯定要有的,想要加快性能的話,就是要有序插入,每次插入記錄都在索引的最后面,索引的定位效率很高,并且對索引調整較小。如果插入的記錄在索引中間,需要B+tree進行分裂合并等處理,會消耗比較多計算資源,并且插入記錄的索引定位效率會下降,數據量較大時會有頻繁的磁盤操作。

五、總結

博主經過測試+谷歌,最終是選用的一次批量插入數據量為max_allowed_packet大小的一半。只是在不斷的搜索中,發現影響插入性能的地方挺多的,如果僅僅是拿max_allowed_packet這個參數作為分析,其實是沒有意義的,這個參數只是設置最大值,但并不是最佳性能。

不過需要注意,由于sql語句比較大,所以才執行完插入操作之后,一定要釋放變量,不要造成無謂的內存損耗,影響程序性能。

對于我們的mysql來說也是一樣的,mysql的最佳性能是建立在各個參數的合理設置上,這樣協同干活兒的效果最佳。如果其他設置不到位的話,就像是木桶原理一樣,哪怕內存緩沖區設置的很大,但是性能取決的反而是設置最差的那個配置。







審核編輯:劉清

聲明:本文內容及配圖由入駐作者撰寫或者入駐合作網站授權轉載。文章觀點僅代表作者本人,不代表電子發燒友網立場。文章及其配圖僅供工程師學習之用,如有內容侵權或者其他違規問題,請聯系本站處理。 舉報投訴
  • MySQL
    +關注

    關注

    1

    文章

    804

    瀏覽量

    26533
  • RBAC
    +關注

    關注

    0

    文章

    44

    瀏覽量

    9962

原文標題:MySQL 批量操作,一次插入多少行數據效率最高?

文章出處:【微信號:芋道源碼,微信公眾號:芋道源碼】歡迎添加關注!文章轉載請注明出處。

收藏 人收藏

    評論

    相關推薦

    labview插入數據MySQL數據

    最近在用labview寫入數據MySQL數據庫,遇到個問題:(如圖片所示)利用insert指令插入
    發表于 12-26 16:52

    NRF24L01一次最多可以發送多少數據

    1,NRF24L01一次最多可以發送多少數據?2.他的FIFO寄存器有多大?3.#define RX_PLOAD_WIDTH32 ,這句是定義的是我發送的
    發表于 07-15 08:02

    幾種數據庫的大數據批量插入解決方法

    之前只知道SqlServer支持數據批量插入,殊不知道Oracle、SQLite和MySql也是支持的,不過Oracle需要使用Orace
    發表于 11-04 07:59

    為什么STM32串口中斷服務函數的數據只能執行一次

    為什么STM32串口中斷服務函數的數據只能執行一次?其解決方法是什么?
    發表于 12-07 07:52

    如何才能讓串口收完數據一次中斷

    如何才能讓串口收完數據一次中斷?怎樣采用串口空閑中斷和DMA接收來實現這個功能
    發表于 12-08 06:08

    MySQL數據庫:如何操作禁止重復插入數據

    MySQL進行數據插入操作時,總是會考慮是否會插入重復數據
    的頭像 發表于 10-08 14:15 ?3311次閱讀
    <b class='flag-5'>MySQL</b><b class='flag-5'>數據</b>庫:如何<b class='flag-5'>操作</b>禁止重復<b class='flag-5'>插入</b><b class='flag-5'>數據</b>

    MySQL 批量插入不重復數據的解決方法

    業務很簡單:需要批量插入數據數據來源可能是其他數據庫的表,也可能是
    的頭像 發表于 07-02 15:28 ?2274次閱讀
    <b class='flag-5'>MySQL</b> <b class='flag-5'>批量</b><b class='flag-5'>插入</b>不重復<b class='flag-5'>數據</b>的解決方法

    MyBatis批量插入數據的3種方法你知道幾種

    分別是: 循環單插入; MP 批量插入功能; 原生批量插入功能。 準備工作 開始之前我們先來創
    的頭像 發表于 12-08 17:56 ?4248次閱讀
    MyBatis<b class='flag-5'>批量</b><b class='flag-5'>插入</b><b class='flag-5'>數據</b>的3種方法你知道幾種

    路由器多久關機一次合適

    路由器多久關機一次合適 路由器天關一次好嗎
    發表于 09-27 14:23 ?0次下載

    MySQL批量插入數據的四種方案(性能測試對比)

    本文記錄個人使用MySQL插入數據總結較實用的方案,通過對常用插入數據的4種方式進行測試,即for循環單條、拼接SQL、
    的頭像 發表于 10-28 09:43 ?2705次閱讀

    MyBatis批量插入別再亂用foreach了

    MySql Docs中也提到過這個trick,如果要優化插入速度時,可以將許多小型操作組合到個大型
    的頭像 發表于 03-13 09:47 ?1283次閱讀

    mysql個表能存多少數據

    mysql個表能存多少數據 MySQL種關系型數據庫管理系統(RDBMS),它允許用戶
    的頭像 發表于 08-28 17:15 ?985次閱讀

    實時操作系統的滴答Tick設置多少合適

    是指操作系統運行一次的時間。實時操作系統中,Tick的設置是個非常關鍵的問題。合適的Tick
    的頭像 發表于 10-29 16:33 ?893次閱讀

    oracle如何一次添加多行數據

    INTO語句用于向表中插入數據,可以一次插入行或多行數據。INSERT ALL語句可以
    的頭像 發表于 11-21 14:15 ?5355次閱讀

    查詢SQLmysql內部是如何執行

    我們知道mySQL客戶端,輸入條查詢SQL,然后看到返回查詢的結果。這條查詢語句 MySQL 內部到底是如何
    的頭像 發表于 01-22 14:53 ?563次閱讀
    查詢SQL<b class='flag-5'>在</b><b class='flag-5'>mysql</b>內部是如何<b class='flag-5'>執行</b>?
    主站蜘蛛池模板: 国产亚洲精品精品精品| 中国二级毛片| 芭乐视频免费资源在线观看| 精品无码久久久久久动漫| 日韩亚洲欧美中文高清| 18禁在线无遮挡羞羞漫画| 含羞草传媒在线观看| 天天国产在线精品亚洲| Y8848高清私人影院软件优势| 热综合一本伊人久久精品| 97国产人妻精品无码AV在线| 久久aa毛片免费播放嗯啊| 亚洲精品蜜夜内射| 国产精品内射久久久久欢欢 | 边摸边吃奶玩乳尖视频| 男女肉大捧进出全过程免费| 伊人狼人久久精品热9| 果冻传媒在线观看进入窗口| 一个人看www| 久久久久久久久性潮| 亚洲男人的天堂久久精品麻豆| 国产亚洲欧美ai在线看片| 香蕉精品国产高清自在自线| 俄罗斯12一15处交| 人妻中文字幕无码系列| 国产美女久久久久久久久久久| 小玲被公扒开腿| 久久免费精品国产72精品剧情| 在线a视频| 麻豆成人AV久久无码精品| 中文字幕亚洲第一| 日本内射精品一区二区视频| 国产AV国片精品无套内谢无码 | gay吊粗大双龙| 免费亚洲视频| 国产a视频视卡在线| 伊人久久大香线蕉avapp下载| 欧美性猛交xxxxxxxx软件| xxx69欧美| 天天久久狠狠色综合| 久久99国产视频|