Mysql 中怎么優化insert性能

本篇文章為大家展示了Mysql 中怎么優化insert性能，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

Mysql優化之加速INSERT插入一條記錄花費的時間由以下幾個因素決定，數字表示影響的比例：
 
連接：（3）
 
發送查詢給服務器：（2）
 
解析查詢：（2）
 
插入記錄：（1*記錄大小）
 
插入索引：（1*索引數量）
 
關閉：（1）
 
此處沒有考慮初始化時打開數據表的開銷，因為每次運行查詢只會做這么一次。
 
如果是B-tree索引，隨著索引數量的增加，插入記錄的速度以logN的比例下降。
 
可以用以下幾種方法來提高插入速度：
 
如果要在同一個客戶端在同一時間內插入很多記錄，可以使用INSERT語句附帶有多個values值。這種做法比使用單一值的INSERT語句快多了（在一些情況下比較快）。如果是往一個非空數據表增加記錄，可以調整變量bulk_insert_buffer_size的值使其更快。
 
如果要從不用的客戶端插入大量記錄，使用INSERT DELAYED語句也可以提高速度。
 
對應MyISAM，可以在SELECT語句正在運行時插入記錄，只要這時候沒有正在刪除記錄。
 
想要將一個文本文件加載到數據表中，可以使用LOAD DATA INFILE。這通常是使用大量INSERT語句的20倍。
 
通過一些額外工作，就可以讓LOAD DATA INFILE在數據表有大量索引的情況下運行更快。步驟如下：
 
用create table隨表建一個表
 
執行FLUSH TABLES語句或admin flush-tables命令
 
執行myisamchk –keys-used=0 -rq /path/to/db/tbl_name命令，刪除數據表所有索引。
 
執行LOAD DATA INFILE，數據插入到表中，由于無需更新表索引，因此這將非常快。
 
如果將來只是讀取該表，運行myisampack讓數據表更小。
 
運行myisamchk -r -q /path/to/db/tbl_name重建索引。創建的索引樹在寫入磁盤前先保存在內存中，這省去了磁盤磁盤搜索，因此速度快很多。重建后的索引樹分布非常均衡。
 
執行FLUSH TABLES語句或mysqladmin flush-tables命令
 
注意，在Mysql 4.0起，可以運行ALTER TABLE tbl_name DISABLE KEYS來代替myisamchk –keys-used=0 -rq /path/to/db/tbl_name.運行ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name.這么做就可以省去FLUSH TABLES步驟。
 
 
 
可以在鎖表后，一起執行幾個語句來加速INSERT操作：
 
LOCK TABLES a WRITE;
 
INSERT INTO a VALUES(1,23),(2,23);
 
INSERT INTO a VALUES(8,7);
 
UNLOCK TABLES;
 
這對性能提高的好處在于：直到所有的INSERT語句都完成之后，索引緩存一次性刷新到磁盤中。通常情況下，有多少次INSERT語句就會有多少次索引緩存刷新到磁盤中的開銷。如果能在一個語句中一次性插入多個值的話，顯然鎖表操作也沒有必要了。對于事務表而言，用BEGIN/COMMIT代替LOCK TABLES來提高速度。鎖表也會降低多次連接測試的總時間，盡管每個獨立連接為了等待鎖的最大等待時間也會增加。
 
Connection 1 does 1000 inserts
 
Connection 2,3 and 4 do 1 insert
 
Connection 5 does 1000 inserts
 
如果沒有鎖表，則連接2，3，4會在1，5之前完成。如果鎖表了，則連接2，3，4可能在1，5之后才能完成，但總時間可能只需要40%。Mysql的INSERT、UPDATE、DELETE操作都非常快，不過在一個語句中如果超過5個插入或者更新時最好加鎖以得到更好的性能。如果要一次性做很多次插入，最好在每個循環的前后加上LOCK TABLES和UNLOCK TABLES，從而讓其他進程也能訪問數據表；這么做性能依然不錯。INSERT總比LOAD DATA INFILE插入數據慢，因為二者實現策略有分明的不同。
 
想要MyISAM表更快，在LOAD DATA INFILE和INSERT時都可以增加系統變量key_buffer_size的值。
 

上述內容就是Mysql 中怎么優化insert性能，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。