mysql實戰優化之五： 更新/插入優化 sql優化

通常情況下，當通路某張表的時候，讀取者首先必須擷取該表的鎖，如果有寫入操作到達，那麼寫入者一直等待讀取者完成操作（查詢開始之後就不能中斷，是以允許讀取者完成操作）。當讀取者完成對表的操作的時候，鎖就會被解除。如果寫入者正在等待的時候，另一個讀取操作到達了，該讀取操作也會被阻塞（block），因為預設的排程政策是寫入者優先于讀取者。當第一個讀取者完成操作并解放鎖後，寫入者開始操作，并且直到該寫入者完成操作，第二個讀取者才開始操作。

通過LOCK TABLES和UNLOCK TABLES語句可以顯式地擷取或釋放鎖，但是在通常情況下，伺服器的鎖管理器會自動地在需要的時候擷取鎖，在不再需要的時候釋放鎖。擷取的鎖的類型依賴于用戶端是寫入還是讀取操作。

對某張表進行寫入操作的用戶端必須擁有獨占的（排他的）通路權的鎖。操作在進行的過程中，該資料表處于不一緻的（inconsistent）狀态，因為資料記錄在删除、添加或修改的時候，資料表上的索引也可能需要更新以互相比對。允許兩個用戶端同時寫入一張資料表是不利的，因為這樣的操作會很快使資料表中的資訊成為一堆無用的垃圾。同時允許用戶端讀取變化之中的資料表也不正确，因為正在讀取的位置中的資料可能正在變化（修改），讀取的結果可能并不是真實的。是以對某張表執行讀取操作的用戶端也必須擷取一個鎖，防止在讀取的過程中，其它的用戶端寫入或改變表。但是這個鎖不需要獨占的通路權。因為讀取操作不會改變資料，是以沒有理由讓某個讀取者阻止其它的讀取者通路這張表。故讀取鎖可允許其它的用戶端在同一時刻讀取這張表。

雖然通過鎖機制，可以實作多線程同時對某個表進行操作，但當某個線程作更新操作時，首先要獲得獨占的通路權。在更新的過程中，所有其它想要通路這個表的線程必須要等到其更新完成為止。此時就會導緻鎖競争的問題，進而導緻使用者等待時間的延長。

要提高MySQL的更新/插入效率，應首先考慮降低鎖的競争，減少寫操作的等待時間。

1．同時插入多行記錄時，宜使用多個值表的INSERT 語句

如果可以同時從同一客戶插入很多行時，宜使用多個值表的INSERT 語句。多個值表的 INSERT 語句 ，可以大大縮減用戶端與資料庫之間的連接配接、文法分析等消耗，使得效率比分開執行的單個 INSERT 語句快很多。

如批量插入:

INSERT INTO tb (fa, fb, fc) VALUES ('1', '12', '13'), ('2', '22', '23'), ('3', '32', '33'),

多值的 INSERT語句還可以通過調整 bulk_insert_buffer_size 參數來提高資料插入的效率，這個參數設定的是 bulk insert 的緩存大小，預設是 8M 。注意，這隻能對myisam表使用。

3)可以對myisam表并行插入Concurrent_insert系統變量可以被設定用于修改concurrent-insert處理。該變量預設設定為1。如果concurrent_insert被設定為0，并行插入就被禁用。如果該變量被設定為2，在表的末端可以并行插入，即便該表的某些行已經被删除。

2．考慮使用replace 語句代替insert語句

根據應用情況可以使用replace 語句代替insert/update語句。例如：如果一個表在一個字段上建立了唯一索引，當向這個表中使用已經存在的鍵值插入一條記錄，将會抛出一個主鍵沖突的錯誤。如果我們想用新記錄的值來覆寫原來的記錄值時，就可以使用REPLACE語句。

使用REPLACE插入記錄時，如果記錄不重複（或往表裡插新記錄），REPLACE功能與INSERT一樣，如果存在重複記錄，REPLACE就使用新記錄的值來替換原來的記錄值。使用REPLACE的最大好處就是可以将DELETE和INSERT合二為一，形成一個原子操作。這樣就可以不必考慮同時使用DELETE和INSERT時添加事務等複雜操作了。

在使用REPLACE時，表中必須有唯一有一個PRIMARY KEY或UNIQUE索引，否則，使用一個REPLACE語句沒有意義。

mysql replace語句：

用法1：replace into：

replace into table (id,name) values('1','aa'),('2','bb')

此語句的作用是向表table中插入兩條記錄。如果主鍵id為1或2不存在就相當于插入語句：

insert into table (id,name) values('1','aa'),('2','bb')

如果存在相同的值則不會插入資料。

用法2：replace(object, search, replace)

作用是把object中出現search的全部替換為replace，例：

select replace(‘abc’, ‘b’, ‘x’);

例：把表table中的name字段中的aa替換為bb

update table set name=replace(name,'aa','bb')

注意：UPDATE和REPLACE的差別：

1）UPDATE在沒有比對記錄時什麼都不做，而REPLACE在有重複記錄時更新，在沒有重複記錄時插入。

2）UPDATE可以選擇性地更新記錄的一部分字段。而REPLACE在發現有重複記錄時就将這條記錄徹底删除，再插入新的記錄。也就是說，将所有的字段都更新了。

3．在插入大量資料之前，可以先将表鎖定（Lock Tables）

為了提高資料插入的效率，可以考慮在插入之前先将表鎖定。這主要是因為直到所有的INSERT語句都完成之後，索引緩存一次性重新整理到磁盤中。通常情況下，有多少次INSERT語句就會有多少次索引緩存重新整理到磁盤中的開銷。為此在資料插入之前，将資料表進行鎖定，就可以大幅度的提高資料插入的效率。當然，如果你可以用一個插入語句實作所有行的插入，則無需使用顯式鎖定語句。（針對非事務性表）

是以如果一個表的更新頻率比較高時，那麼可以使用Lock Tables選項來提高更新速度。

對于事務性表，要想更快地進行表插入，可以使用START TRANSACTION和COMMIT語句代替LOCK TABLES來提高更新速度。

4．可以對myisam表并行插入Concurrent_insert系統變量可以被設定用于修改concurrent-insert處理。

該變量預設設定為1。如果concurrent_insert被設定為0，并行插入就被禁用。如果該變量被設定為2，在表的末端可以并行插入，即便該表的某些行已經被删除。

5．使用插入延遲

如果客戶無需等待插入完成的時候（即使用者對插入資料的即時性要求可能并不是很高），此時就可以考慮采用插入延遲特性。Delayed的含義是讓insert語句馬上執行并傳回，而資料被放在記憶體的隊列中等待被插入，并沒有真正的寫入磁盤；這比每條語句都分别插入要快的多。

使用插入延遲的另一個好處就是從多個客戶插入的情況會被綁定并記錄在同一個block中。

預設情況下，在MySQL資料庫中，更新操作比Select查詢有更高的優先級。MySQL的預設的排程政策可用總結如下：

• 寫入操作優先于讀取操作。

• 對一張資料表的寫入操作同一時刻隻能發生一次，寫入請求按照它們到達的次序來處理。

• 對一張資料表的多個讀取操作可以同時地進行。

MySQL允許改變語句排程的優先級，它可以使來自多個用戶端的查詢更好地協作，這樣單個用戶端就不會由于鎖定而等待很長時間。改變優先級還可以確定特定類型的查詢被處理得更快。通過以下三種方式來修改它的排程政策：

• LOW_PRIORITY關鍵字應用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。這個屬性可以将某個特定的語句的優先級降低。如可以調低某個特定的更新語句或者插入語句的優先級。不過需要注意的是，這個屬性隻有對特定的語句有用。即其作用域隻針對某個特定的語句，而不會對全局造成影響。

例：UPDATE [LOW_PRIORITY] tbl_name SET col_name1=expr1,col_name2=expr2,...

mysql中update用low_priority讓update不鎖定表

• HIGH_PRIORITY關鍵字應用于SELECT和INSERT語句。這個屬性可以用來提高某個特定的Select查詢語句的優先級。LOW_PRIORITY剛好相反，在所有其他使用者對表的讀寫完成後才進行插入。這裡需要注意，跟上面這個屬性一樣，這個作用域也隻限于特定的查詢語句。而不會對沒有加這個參數的其他查詢語句産生影響。也就是說，其他查詢語句如果沒有加這個屬性，那麼其優先級别仍然低于更新程序。

• DELAYED關鍵字應用于INSERT和REPLACE語句。

LOW_PRIORITY和HIGH_PRIORITY調節符影響那些使用資料表鎖的存儲引擎（例如MyISAM和MEMORY）。DELAYED調節符作用于MyISAM和MEMORY資料表。

通常情況下，某張資料表正在被讀取的時候，如果有寫入操作到達，那麼寫入者一直等待讀取者完成操作（查詢開始之後就不能中斷，是以允許讀取者完成操作）。如果寫入者正在等待的時候，另一個讀取操作到達了，該讀取操作也會被阻塞（block），因為預設的排程政策是寫入者優先于讀取者。當第一個讀取者完成操作的時候，寫入者開始操作，并且直到該寫入者完成操作，第二個讀取者才開始操作。

如果寫入操作是一個LOW_PRIORITY（低優先級）請求，那麼系統就不會認為它的優先級高于讀取操作。在這種情況下，如果寫入者在等待的時候，第二個讀取者到達了，那麼就允許第二個讀取者插到寫入者之前。隻有在沒有其它的讀取者的時候，才允許寫入者開始操作。理論上，這種排程修改暗示着，可能存在LOW_PRIORITY寫入操作永遠被阻塞的情況。如果前面的讀取操作在進行的過程中一直有其它的讀取操作到達，那麼新的請求都會插入到LOW_PRIORITY寫入操作之前。

SELECT查詢的HIGH_PRIORITY（高優先級）關鍵字也類似。它允許SELECT插入正在等待的寫入操作之前，即使在正常情況下寫入操作的優先級更高。另外一種影響是，高優先級的SELECT在正常的SELECT語句之前執行，因為這些語句會被寫入操作阻塞。

如果希望某一連接配接支援LOW_PRIORITY選項來處理，那麼通過Set LOW_PRIORIT_UPDATES=1來設定連接配接變量，通過這個設定可以制定具體連接配接中的所有更新程序都是用比較低的優先級。但注意這個選項隻針對特定的連接配接有用。對于其他的連接配接，就不适用。

如果希望所有支援LOW_PRIORITY選項的語句都預設地按照低優先級來處理，那麼請使用--low-priority-updates選項來啟動伺服器。采用這個選項啟動資料庫時，系統會給資料庫中所有的更新語句比較低的優先級。通過使用INSERT HIGH_PRIORITY來把INSERT語句提高到正常的寫入優先級，可以消除該選項對單個INSERT語句的影響。

6．使用LOAD DATA INFILE從文本下載下傳資料将比使用插入語句快20倍。

Load Date Infile是從一個檔案中導入資料。如果采用這種方式的話，使用者需要預先準備一個固定格式的檔案。如果插入的資料量比較多，例如軟體運作環境配置時可能會導入大量預配置資料時，此時使用Load Date Infile的方式能夠取得比較好的性能。

7．将大表分為多個小表來降低鎖競争

8.将索引檔案和資料檔案分在不同的磁盤上存放（利用建表中的選項）。