mysql 數(shù)據(jù)插入優(yōu)化方法
更新時間:2010年12月17日 11:11:14 作者:
當(dāng)一個線程對一個表執(zhí)行一個DELAYED語句時,如果不存在這樣的處理程序,一個處理器線程被創(chuàng)建以處理對于該表的所有DELAYED語句。
通常來說,在MyISAM里讀寫操作是串行的,但當(dāng)對同一個表進行查詢和插入操作時,為了降低鎖競爭的頻率,根據(jù)concurrent_insert的設(shè)置,MyISAM是可以并行處理查詢和插入的:
當(dāng)concurrent_insert=0時,不允許并發(fā)插入功能。
當(dāng)concurrent_insert=1時,允許對沒有洞洞的表使用并發(fā)插入,新數(shù)據(jù)位于數(shù)據(jù)文件結(jié)尾(缺?。?。
當(dāng)concurrent_insert=2時,不管表有沒有洞洞,都允許在數(shù)據(jù)文件結(jié)尾并發(fā)插入。
這樣看來,把concurrent_insert設(shè)置為2是很劃算的,至于由此產(chǎn)生的文件碎片,可以定期使用OPTIMIZE TABLE語法優(yōu)化。
max_write_lock_count:
缺省情況下,寫操作的優(yōu)先級要高于讀操作的優(yōu)先級,即便是先發(fā)送的讀請求,后發(fā)送的寫請求,此時也會優(yōu)先處理寫請求,然后再處理讀請求。這就造成一 個問題:一旦我發(fā)出若干個寫請求,就會堵塞所有的讀請求,直到寫請求全都處理完,才有機會處理讀請求。此時可以考慮使用 max_write_lock_count:
max_write_lock_count=1
有了這樣的設(shè)置,當(dāng)系統(tǒng)處理一個寫操作后,就會暫停寫操作,給讀操作執(zhí)行的機會。
low-priority-updates:
我們還可以更干脆點,直接降低寫操作的優(yōu)先級,給讀操作更高的優(yōu)先級。
low-priority-updates=1
綜合來看,concurrent_insert=2是絕對推薦的,至于max_write_lock_count=1和low-priority- updates=1,則視情況而定,如果可以降低寫操作的優(yōu)先級,則使用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 (...), (.....),(.....)這樣形式插入數(shù)據(jù),避免使用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,導(dǎo)入完數(shù)據(jù)在執(zhí)行:
alter table table_name enable keys. 如果是空表,就不需要這個操作,因為myisam表在空表中導(dǎo)入數(shù)據(jù)時,是先導(dǎo)入數(shù)據(jù)然后建立indexs。
4 在插入數(shù)據(jù)時考慮使用:insert delayed....這樣操作實際mysql把insert操作放到隊列里面,進行相對集中的插入,速度更快。
5. 使用load data infile 比使用insert 操作快近20倍,盡量使用此操作。
在innodb engine下
1.導(dǎo)入數(shù)據(jù)之前執(zhí)行set unique_checks=0來禁止對唯一索引的檢查,數(shù)據(jù)導(dǎo)入完成之后再運行set unique_checks=1.
2. 導(dǎo)入數(shù)據(jù)之前執(zhí)行set foreign_key_checks=0來禁止對外鍵的檢查,數(shù)據(jù)導(dǎo)入完成之后再執(zhí)行set foreign_key_checks=1.
3.導(dǎo)入數(shù)據(jù)之前執(zhí)行set autocommit=0禁止自動事務(wù)的自動提交,數(shù)據(jù)導(dǎo)入完成之后,執(zhí)行set autocommit=1 恢復(fù)自動提交操作。
使用innodb engine的表,物理存儲都是按PK的順序存的。不能使用類似于myisam一樣disable keys.
硬件上提高磁盤的I/0對插入速度很有好處(所以如果進行大數(shù)據(jù)量的導(dǎo)入導(dǎo)出工作,盡量在比較NB的硬件上進行,能縮減完成的時間,已經(jīng)防止出現(xiàn)問題)。
當(dāng)一個線程對一個表執(zhí)行一個DELAYED語句時,如果不存在這樣的處理程序,一個處理器線程被創(chuàng)建以處理對于該表的所有DELAYED語句。
線程檢查處理程序是否已經(jīng)獲得了一個DELAYED鎖;如果沒有,它告訴處理程序去獲得。即使其他的線程有在表上的一個READ或WRITE鎖,也能獲得 DELAYED鎖。然而,處理程序?qū)⒌却蠥LTER TABLE鎖或FLUSH TABLES以保證表結(jié)構(gòu)是最新的。
線程執(zhí)行INSERT語句,但不是將行寫入表,它把最后一行的副本放進被處理器線程管理的一個隊列。任何語法錯誤都能被線程發(fā)覺并報告給客戶程序。
顧客不能報告結(jié)果行的重復(fù)次數(shù)或AUTO_INCREMENT值;它不能從服務(wù)器獲得它們,因為INSERT在插入操作完成前返回。如果你使用C API,同樣原因,mysql_info()函數(shù)不返回任何有意義的東西。
當(dāng)行被插入到表中時,更新日志有處理器線程更新。在多行插入的情況下,當(dāng)?shù)谝恍斜徊迦霑r,更新日志被更新。
在每寫入delayed_insert_limit行后,處理器檢查是否任何SELECT語句仍然是未完成,如果這樣,在繼續(xù)之前允許執(zhí)行這些語句。
當(dāng)處理器在它的隊列中沒有更多行時,表被解鎖。如果在delayed_insert_timeout秒內(nèi)沒有收到新的INSERT DELAYED命令,處理器終止。
如果已經(jīng)有多于delayed_queue_size行在一個特定的處理器隊列中未解決,線程等待直到隊列有空間。這有助于保證mysqld服務(wù)器對延遲的內(nèi)存隊列不使用所有內(nèi)存。
處理器線程將在Command列的MySQL進程表中顯示delayed_insert。如果你執(zhí)行一個FLUSH TABLES命令或以KILL thread_id殺死它,它將被殺死,然而,它在退出前首先將所有排隊的行存進表中。在這期間,這次它將不從其他線程接受任何新的INSERT命令。如 果你在它之后執(zhí)行一個INSERT DELAYED,將創(chuàng)建一個新的處理器線程。
注意,上述意味著,如果有一個INSERT DELAYED處理器已經(jīng)運行,INSERT DELAYED命令有比正常INSERT更高的優(yōu)先級!其他更新命令將必須等到INSERT DELAY排隊變空、殺死處理器線程(用KILL thread_id)或執(zhí)行FLUSH TABLES。
下列狀態(tài)變量提供了關(guān)于INSERT DELAYED命令的信息: Delayed_insert_threads 處理器線程數(shù)量
Delayed_writes 用INSERT DELAYED被寫入的行的數(shù)量
Not_flushed_delayed_rows 等待被寫入的行數(shù)字
當(dāng)concurrent_insert=0時,不允許并發(fā)插入功能。
當(dāng)concurrent_insert=1時,允許對沒有洞洞的表使用并發(fā)插入,新數(shù)據(jù)位于數(shù)據(jù)文件結(jié)尾(缺?。?。
當(dāng)concurrent_insert=2時,不管表有沒有洞洞,都允許在數(shù)據(jù)文件結(jié)尾并發(fā)插入。
這樣看來,把concurrent_insert設(shè)置為2是很劃算的,至于由此產(chǎn)生的文件碎片,可以定期使用OPTIMIZE TABLE語法優(yōu)化。
max_write_lock_count:
缺省情況下,寫操作的優(yōu)先級要高于讀操作的優(yōu)先級,即便是先發(fā)送的讀請求,后發(fā)送的寫請求,此時也會優(yōu)先處理寫請求,然后再處理讀請求。這就造成一 個問題:一旦我發(fā)出若干個寫請求,就會堵塞所有的讀請求,直到寫請求全都處理完,才有機會處理讀請求。此時可以考慮使用 max_write_lock_count:
max_write_lock_count=1
有了這樣的設(shè)置,當(dāng)系統(tǒng)處理一個寫操作后,就會暫停寫操作,給讀操作執(zhí)行的機會。
low-priority-updates:
我們還可以更干脆點,直接降低寫操作的優(yōu)先級,給讀操作更高的優(yōu)先級。
low-priority-updates=1
綜合來看,concurrent_insert=2是絕對推薦的,至于max_write_lock_count=1和low-priority- updates=1,則視情況而定,如果可以降低寫操作的優(yōu)先級,則使用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 (...), (.....),(.....)這樣形式插入數(shù)據(jù),避免使用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,導(dǎo)入完數(shù)據(jù)在執(zhí)行:
alter table table_name enable keys. 如果是空表,就不需要這個操作,因為myisam表在空表中導(dǎo)入數(shù)據(jù)時,是先導(dǎo)入數(shù)據(jù)然后建立indexs。
4 在插入數(shù)據(jù)時考慮使用:insert delayed....這樣操作實際mysql把insert操作放到隊列里面,進行相對集中的插入,速度更快。
5. 使用load data infile 比使用insert 操作快近20倍,盡量使用此操作。
在innodb engine下
1.導(dǎo)入數(shù)據(jù)之前執(zhí)行set unique_checks=0來禁止對唯一索引的檢查,數(shù)據(jù)導(dǎo)入完成之后再運行set unique_checks=1.
2. 導(dǎo)入數(shù)據(jù)之前執(zhí)行set foreign_key_checks=0來禁止對外鍵的檢查,數(shù)據(jù)導(dǎo)入完成之后再執(zhí)行set foreign_key_checks=1.
3.導(dǎo)入數(shù)據(jù)之前執(zhí)行set autocommit=0禁止自動事務(wù)的自動提交,數(shù)據(jù)導(dǎo)入完成之后,執(zhí)行set autocommit=1 恢復(fù)自動提交操作。
使用innodb engine的表,物理存儲都是按PK的順序存的。不能使用類似于myisam一樣disable keys.
硬件上提高磁盤的I/0對插入速度很有好處(所以如果進行大數(shù)據(jù)量的導(dǎo)入導(dǎo)出工作,盡量在比較NB的硬件上進行,能縮減完成的時間,已經(jīng)防止出現(xiàn)問題)。
當(dāng)一個線程對一個表執(zhí)行一個DELAYED語句時,如果不存在這樣的處理程序,一個處理器線程被創(chuàng)建以處理對于該表的所有DELAYED語句。
線程檢查處理程序是否已經(jīng)獲得了一個DELAYED鎖;如果沒有,它告訴處理程序去獲得。即使其他的線程有在表上的一個READ或WRITE鎖,也能獲得 DELAYED鎖。然而,處理程序?qū)⒌却蠥LTER TABLE鎖或FLUSH TABLES以保證表結(jié)構(gòu)是最新的。
線程執(zhí)行INSERT語句,但不是將行寫入表,它把最后一行的副本放進被處理器線程管理的一個隊列。任何語法錯誤都能被線程發(fā)覺并報告給客戶程序。
顧客不能報告結(jié)果行的重復(fù)次數(shù)或AUTO_INCREMENT值;它不能從服務(wù)器獲得它們,因為INSERT在插入操作完成前返回。如果你使用C API,同樣原因,mysql_info()函數(shù)不返回任何有意義的東西。
當(dāng)行被插入到表中時,更新日志有處理器線程更新。在多行插入的情況下,當(dāng)?shù)谝恍斜徊迦霑r,更新日志被更新。
在每寫入delayed_insert_limit行后,處理器檢查是否任何SELECT語句仍然是未完成,如果這樣,在繼續(xù)之前允許執(zhí)行這些語句。
當(dāng)處理器在它的隊列中沒有更多行時,表被解鎖。如果在delayed_insert_timeout秒內(nèi)沒有收到新的INSERT DELAYED命令,處理器終止。
如果已經(jīng)有多于delayed_queue_size行在一個特定的處理器隊列中未解決,線程等待直到隊列有空間。這有助于保證mysqld服務(wù)器對延遲的內(nèi)存隊列不使用所有內(nèi)存。
處理器線程將在Command列的MySQL進程表中顯示delayed_insert。如果你執(zhí)行一個FLUSH TABLES命令或以KILL thread_id殺死它,它將被殺死,然而,它在退出前首先將所有排隊的行存進表中。在這期間,這次它將不從其他線程接受任何新的INSERT命令。如 果你在它之后執(zhí)行一個INSERT DELAYED,將創(chuàng)建一個新的處理器線程。
注意,上述意味著,如果有一個INSERT DELAYED處理器已經(jīng)運行,INSERT DELAYED命令有比正常INSERT更高的優(yōu)先級!其他更新命令將必須等到INSERT DELAY排隊變空、殺死處理器線程(用KILL thread_id)或執(zhí)行FLUSH TABLES。
下列狀態(tài)變量提供了關(guān)于INSERT DELAYED命令的信息: Delayed_insert_threads 處理器線程數(shù)量
Delayed_writes 用INSERT DELAYED被寫入的行的數(shù)量
Not_flushed_delayed_rows 等待被寫入的行數(shù)字
您可能感興趣的文章:
- MYSQL開發(fā)性能研究之批量插入數(shù)據(jù)的優(yōu)化方法
- MySql批量插入優(yōu)化Sql執(zhí)行效率實例詳解
- mysql如何優(yōu)化插入記錄速度
- MySQL實現(xiàn)批量插入以優(yōu)化性能的教程
- 解析優(yōu)化MySQL插入方法的五個妙招
- 深入mysql并發(fā)插入優(yōu)化詳解
- MySQL 大數(shù)據(jù)量快速插入方法和語句優(yōu)化分享
- MySql中把一個表的數(shù)據(jù)插入到另一個表中的實現(xiàn)代碼
- mysql中迅速插入百萬條測試數(shù)據(jù)的方法
- MySQL批量SQL插入性能優(yōu)化詳解
相關(guān)文章
MySQL入門教程(五)之表的創(chuàng)建、修改和刪除
MySQL 為關(guān)系型數(shù)據(jù)庫(Relational Database Management System), 本文給大家介紹MySQL入門教程(五)之表的創(chuàng)建、修改和刪除,需要的朋友一起學(xué)習(xí)吧2016-04-04window10系統(tǒng)下mysql5.7安裝審計插件(親測有用)
mysql有沒oracle這樣的審計功能,突然想在mysql做審計怎么辦,下面帶大家從零開始給mysql安裝審計插件,親測絕對可用哦,需要的朋友可以參考下2022-09-09PHP mysqli擴展庫 預(yù)處理技術(shù)的使用分析
本篇文章,介紹了PHP mysqli擴展庫 預(yù)處理技術(shù)的使用分析。需要的朋友參考下2013-05-05