mysql中數據插入優化方法之concurrent_insert的示例分析

小編給大家分享一下mysql中數據插入優化方法之concurrent_insert的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

當一個線程對一個表執行一個DELAYED語句時，如果不存在這樣的處理程序，一個處理器線程被創建以處理對于該表的所有DELAYED語句。

通常來說，在MyISAM里讀寫操作是串行的，但當對同一個表進行查詢和插入操作時，為了降低鎖競爭的頻率，根據concurrent_insert的設置，MyISAM是可以并行處理查詢和插入的：

當concurrent_insert=0時，不允許并發插入功能。
當concurrent_insert=1時，允許對沒有洞洞的表使用并發插入，新數據位于數據文件結尾（缺省）。
當concurrent_insert=2時，不管表有沒有洞洞，都允許在數據文件結尾并發插入。

這樣看來，把concurrent_insert設置為2是很劃算的，至于由此產生的文件碎片，可以定期使用OPTIMIZE TABLE語法優化。

max_write_lock_count：

缺省情況下，寫操作的優先級要高于讀操作的優先級，即便是先發送的讀請求，后發送的寫請求，此時也會優先處理寫請求，然后再處理讀請求。這就造成一 個問題：一旦我發出若干個寫請求，就會堵塞所有的讀請求，直到寫請求全都處理完，才有機會處理讀請求。此時可以考慮使用 max_write_lock_count：

max_write_lock_count=1

有了這樣的設置，當系統處理一個寫操作后，就會暫停寫操作，給讀操作執行的機會。

low-priority-updates：

我們還可以更干脆點，直接降低寫操作的優先級，給讀操作更高的優先級。

low-priority-updates=1

綜合來看，concurrent_insert=2是絕對推薦的，至于max_write_lock_count=1和low-priority- updates=1，則視情況而定，如果可以降低寫操作的優先級，則使用low-priority-updates=1，否則使用 max_write_lock_count=1。

set-variable = max_allowed_packet=1M
set-variable = net_buffer_length=2K

在myisam engine下

1. 盡量使用insert into table_name values (…), (…..),(…..)這樣形式插入數據，避免使用inset into table_name values (); inset into table_name values (); inset into table_name values ();

2 增加bulk_insert_buffer_size(默認8M)

3 如果是非空表，使用alter table table_name disable keys，然后load data infile,導入完數據在執行：

alter table table_name enable keys. 如果是空表，就不需要這個操作，因為myisam表在空表中導入數據時，是先導入數據然后建立indexs。

4 在插入數據時考慮使用：insert delayed….這樣操作實際mysql把insert操作放到隊列里面，進行相對集中的插入，速度更快。

5. 使用load data infile 比使用insert 操作快近20倍，盡量使用此操作。

在innodb engine下

1.導入數據之前執行set unique_checks=0來禁止對唯一索引的檢查，數據導入完成之后再運行set unique_checks=1.

2. 導入數據之前執行set foreign_key_checks=0來禁止對外鍵的檢查，數據導入完成之后再執行set foreign_key_checks=1.

3.導入數據之前執行set autocommit=0禁止自動事務的自動提交，數據導入完成之后，執行set autocommit=1 恢復自動提交操作。

使用innodb engine的表，物理存儲都是按PK的順序存的。不能使用類似于myisam一樣disable keys.

硬件上提高磁盤的I/0對插入速度很有好處（所以如果進行大數據量的導入導出工作，盡量在比較NB的硬件上進行，能縮減完成的時間，已經防止出現問題）。

當一個線程對一個表執行一個DELAYED語句時，如果不存在這樣的處理程序，一個處理器線程被創建以處理對于該表的所有DELAYED語句。

線程檢查處理程序是否已經獲得了一個DELAYED鎖；如果沒有，它告訴處理程序去獲得。即使其他的線程有在表上的一個READ或WRITE鎖，也能獲得 DELAYED鎖。然而，處理程序將等待所有ALTER TABLE鎖或FLUSH TABLES以保證表結構是最新的。

線程執行INSERT語句，但不是將行寫入表，它把最后一行的副本放進被處理器線程管理的一個隊列。任何語法錯誤都能被線程發覺并報告給客戶程序。

顧客不能報告結果行的重復次數或AUTO_INCREMENT值；它不能從服務器獲得它們，因為INSERT在插入操作完成前返回。如果你使用C API，同樣原因，mysql_info()函數不返回任何有意義的東西。

當行被插入到表中時，更新日志有處理器線程更新。在多行插入的情況下，當第一行被插入時，更新日志被更新。
在每寫入delayed_insert_limit行后，處理器檢查是否任何SELECT語句仍然是未完成，如果這樣，在繼續之前允許執行這些語句。

當處理器在它的隊列中沒有更多行時，表被解鎖。如果在delayed_insert_timeout秒內沒有收到新的INSERT DELAYED命令，處理器終止。

如果已經有多于delayed_queue_size行在一個特定的處理器隊列中未解決，線程等待直到隊列有空間。這有助于保證mysqld服務器對延遲的內存隊列不使用所有內存。

處理器線程將在Command列的MySQL進程表中顯示delayed_insert。如果你執行一個FLUSH TABLES命令或以KILL thread_id殺死它，它將被殺死，然而，它在退出前首先將所有排隊的行存進表中。在這期間，這次它將不從其他線程接受任何新的INSERT命令。如 果你在它之后執行一個INSERT DELAYED，將創建一個新的處理器線程。

注意，上述意味著，如果有一個INSERT DELAYED處理器已經運行，INSERT DELAYED命令有比正常INSERT更高的優先級！其他更新命令將必須等到INSERT DELAY排隊變空、殺死處理器線程(用KILL thread_id)或執行FLUSH TABLES。

下列狀態變量提供了關于INSERT DELAYED命令的信息： Delayed_insert_threads 處理器線程數量

Delayed_writes 用INSERT DELAYED被寫入的行的數量
Not_flushed_delayed_rows 等待被寫入的行數字

高并發insert語句的解決方法

前言

1、防止數據多次修改

1.1 、insert方案

1、添加uniqpue進行解決(重復則是更新)

insert一般沒什么問題，直接控制好unique就可以，這樣的話，就不會插入兩條(如果重復了則，進行更新操作)

2、update方案

1、redis分布式鎖、消息隊列(每次只插入一個)

2、mysql鎖(更新可以使用樂觀鎖)

2、高并發下的安全性

1、在線的網站上去執行一個大的DELETE或INSERT查詢，要避免你的操作讓你的整個網站停止相應。因為這兩個操作是會鎖表的(update也是，如果沒有指定唯一主鍵或者索引的話，會鎖表)，表一鎖住了，別的操作都進不來了。所以一定要非常小心

2、如果你把你的表鎖上一段時間，比如30秒鐘，那么對于一個有很高訪問量的站點來說，這30秒所積累的訪問進程/線程，數據庫鏈接，打開的文件數，可能不僅僅會讓你泊WEB服務Crash，還可能會讓你的整臺服務器馬上掛了。 >

2.1、解決方案

2.1.1、表的調整

把表按列變成幾張表的方法，這樣可以降低表的復雜度和字段的數目，從而達到優化的目的。(如果有一百多個字段是很恐怖的)

示例一：

在Users表中有一個字段是家庭地址，這個字段是可選字段，相比起，而且你在數據庫操作的時候除了個人信息外，你并不需要經常讀取或是改寫這個字段。那么，為什么不把他放到另外一張表中呢？這樣會讓你的表有更好的性能，大家想想是不是，大量的時候，我對于用戶表來說，只有用戶ID，用戶名，口令，用戶角色等會被經常使用。小一點的表總是會有好的性能。

示例二：

你有一個叫“last_login”的字段，它會在每次用戶登錄時被更新。但是，每次更新時會導致該表的查詢緩存被清空。所以，你可以把這個字段放到另一個表中，這樣就不會影響你對用戶ID，用戶名，用戶角色的不停地讀取了，因為查詢緩存會幫你增加很多性能。hp程序員之家

另外，需要注意的是，這些被分出去的字段所形成的表，我們是認為不會經常是join的，否則，這樣的性能會比不分割表的時候時還要差，而且，會是極數級的下降

以上是“mysql中數據插入優化方法之concurrent_insert的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！