MySQL 大數(shù)據(jù)量快速插入方法和語句優(yōu)化分享
更新時間：2012年04月14日 02:32:42 作者：
對于事務表，應使用BEGIN和COMMIT代替LOCK TABLES來加快插入
鎖定也將降低多連接測試的整體時間，盡管因為它們等候鎖定最大等待時間將上升。例如：
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用鎖定，2、3和4將在1和5前完成。如果使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應該快大約40%。
INSERT、UPDATE和DELETE操作在MySQL中是很快的，通過為在一行中多于大約5次連續(xù)不斷地插入或更新的操作加鎖，可以獲得更好的整體性能。如果在一行中進行多次插入，可以執(zhí)行LOCK TABLES，隨后立即執(zhí)行UNLOCK TABLES(大約每1000行)以允許其它的線程訪問表。這也會獲得好的性能。
INSERT裝載數(shù)據(jù)比LOAD DATA INFILE要慢得多，即使是使用上述的策略。
為了對LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通過增加key_buffer_size系統(tǒng)變量來擴大 鍵高速緩沖區(qū)。
INSERT語法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
一、DELAYED 的使用
使用延遲插入操作DELAYED調節(jié)符應用于INSERT和REPLACE語句。當DELAYED插入操作到達的時候，服務器把數(shù)據(jù)行放入一個隊列中，并立即給客戶端返回一個狀態(tài)信息，這樣客戶端就可以在數(shù)據(jù)表被真正地插入記錄之前繼續(xù)進行操作了。如果讀取者從該數(shù)據(jù)表中讀取數(shù)據(jù)，隊列中的數(shù)據(jù)就會被保持著，直到沒有讀取者為止。
接著服務器開始插入延遲數(shù)據(jù)行（delayed-row）隊列中的數(shù)據(jù)行。在插入操作的同時，服務器還要檢查是否有新的讀取請求到達和等待。如果有，延遲數(shù)據(jù)行隊列就被掛起，允許讀取者繼續(xù)操作。當沒有讀取者的時候，服務器再次開始插入延遲的數(shù)據(jù)行。這個過程一直進行，直到隊列空了為止。
幾點要注意事項：
INSERT DELAYED應該僅用于指定值清單的INSERT語句。服務器忽略用于INSERT DELAYED...SELECT語句的DELAYED。服務器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE語句的DELAYED。
因為在行被插入前，語句立刻返回，所以您不能使用LAST_INSERT_ID()來獲取AUTO_INCREMENT值。AUTO_INCREMENT值可能由語句生成。
對于SELECT語句，DELAYED行不可見，直到這些行確實被插入了為止。
DELAYED在從屬復制服務器中被忽略了，因為DELAYED不會在從屬服務器中產生與主服務器不一樣的數(shù)據(jù)。注意，目前在隊列中的各行只保存在存儲器中，直到它們被插入到表中為止。這意味著，如果您強行中止了mysqld(例如，使用kill -9)或者如果mysqld意外停止，則所有沒有被寫入磁盤的行都會丟失。
復制代碼 代碼如下:
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用鎖定，2、3和4將在1和5前完成。如果使用鎖定，2、3和4將可能不在1或5前完成，但是整體時間應該快大約40%。
INSERT、UPDATE和DELETE操作在MySQL中是很快的，通過為在一行中多于大約5次連續(xù)不斷地插入或更新的操作加鎖，可以獲得更好的整體性能。如果在一行中進行多次插入，可以執(zhí)行LOCK TABLES，隨后立即執(zhí)行UNLOCK TABLES(大約每1000行)以允許其它的線程訪問表。這也會獲得好的性能。
INSERT裝載數(shù)據(jù)比LOAD DATA INFILE要慢得多，即使是使用上述的策略。
為了對LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通過增加key_buffer_size系統(tǒng)變量來擴大 鍵高速緩沖區(qū)。
INSERT語法
復制代碼 代碼如下:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：
復制代碼 代碼如下:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
或：
復制代碼 代碼如下:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
一、DELAYED 的使用
使用延遲插入操作DELAYED調節(jié)符應用于INSERT和REPLACE語句。當DELAYED插入操作到達的時候，服務器把數(shù)據(jù)行放入一個隊列中，并立即給客戶端返回一個狀態(tài)信息，這樣客戶端就可以在數(shù)據(jù)表被真正地插入記錄之前繼續(xù)進行操作了。如果讀取者從該數(shù)據(jù)表中讀取數(shù)據(jù)，隊列中的數(shù)據(jù)就會被保持著，直到沒有讀取者為止。
接著服務器開始插入延遲數(shù)據(jù)行（delayed-row）隊列中的數(shù)據(jù)行。在插入操作的同時，服務器還要檢查是否有新的讀取請求到達和等待。如果有，延遲數(shù)據(jù)行隊列就被掛起，允許讀取者繼續(xù)操作。當沒有讀取者的時候，服務器再次開始插入延遲的數(shù)據(jù)行。這個過程一直進行，直到隊列空了為止。
幾點要注意事項：
INSERT DELAYED應該僅用于指定值清單的INSERT語句。服務器忽略用于INSERT DELAYED...SELECT語句的DELAYED。服務器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE語句的DELAYED。
因為在行被插入前，語句立刻返回，所以您不能使用LAST_INSERT_ID()來獲取AUTO_INCREMENT值。AUTO_INCREMENT值可能由語句生成。
對于SELECT語句，DELAYED行不可見，直到這些行確實被插入了為止。
DELAYED在從屬復制服務器中被忽略了，因為DELAYED不會在從屬服務器中產生與主服務器不一樣的數(shù)據(jù)。注意，目前在隊列中的各行只保存在存儲器中，直到它們被插入到表中為止。這意味著，如果您強行中止了mysqld(例如，使用kill -9)或者如果mysqld意外停止，則所有沒有被寫入磁盤的行都會丟失。
您可能感興趣的文章:
相關文章
mysql免安裝版本的制作方法，需要的朋友可以參考下。2010-08-08
這篇文章主要給大家介紹了關于MySQL磁盤碎片整理的相關資料,為什么數(shù)據(jù)庫會產生碎片,以及如何清理磁盤碎片，還有一些清理磁盤碎片的注意事項,需要的朋友可以參考下2022-04-04
這篇文章主要介紹了mysql如何導出服務器內所有的數(shù)據(jù)庫問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-10-10
這篇文章主要介紹了MySQL中如何添加新字段方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04
這篇文章主要介紹了MySQL對中文進行排序詳解及實例的相關資料,需要的朋友可以參考下2017-05-05
這篇文章主要介紹了MySQL錯誤代碼2058和2059的解決辦法,2058和2059的錯誤碼核心都是你用的客戶端工具和mysql版本的密碼插件不匹配,下面就來介紹一下解決方法,感興趣的可以了解一下2025-03-03
這篇文章主要介紹了MySQL中JSON數(shù)據(jù)類型的各種函數(shù),包括JSON_EXTRACT、JSON_UNQUOTE、JSON_SEARCH、JSON_SET、JSON_CONTAINS、JSON_REMOVE和JSON_REPLACE等,涵蓋了JSON數(shù)據(jù)的提取、更新、刪除和查詢等功能,需要的朋友可以參考下2025-02-02
這篇文章主要介紹了MySQL常見的底層優(yōu)化操作教程及相關建議,包括對運行操作系統(tǒng)的硬件方面及存儲引擎參數(shù)的調整等零碎方面的小整理,需要的朋友可以參考下2015-12-12
這篇文章主要介紹了Linux下mysql異地自動備份的方法，本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-06-06
MySQL基礎教程第一篇 mysql5.7.18安裝和連接教程
這篇文章主要為大家詳細介紹了MySQL基礎教程第一篇，mysql5.7.18安裝和連接教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-05-05