Mysql 中怎么優化insert性能

136次閱讀

共計 1914 個字符，預計需要花費 5 分鐘才能閱讀完成。

本篇文章為大家展示了 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 ndash;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 ndash;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 的值。