當(dāng)一個(gè)線程對(duì)一個(gè)表執(zhí)行一個(gè)DELAYED語(yǔ)句時(shí),如果不存在這樣的處理程序,一個(gè)處理器線程被創(chuàng)建以處理對(duì)于該表的所有DELAYED語(yǔ)句。
通常來(lái)說(shuō),在MyISAM
里讀寫操作是串行的,但當(dāng)對(duì)同一個(gè)表進(jìn)行查詢和插入操作時(shí),為了降低鎖競(jìng)爭(zhēng)的頻率,根據(jù)concurrent_insert的設(shè)置,MyISAM是可以并行處理查詢和插入的:
當(dāng)concurrent_insert=0時(shí),不允許并發(fā)插入功能。
當(dāng)concurrent_insert=1時(shí),允許對(duì)沒(méi)有洞洞的表使用并發(fā)插入,新數(shù)據(jù)位于數(shù)據(jù)文件結(jié)尾(缺省)。
當(dāng)concurrent_insert=2時(shí),不管表有沒(méi)有洞洞,都允許在數(shù)據(jù)文件結(jié)尾并發(fā)插入。
這樣看來(lái),把concurrent_insert設(shè)置為2是很劃算的,至于由此產(chǎn)生的文件碎片,可以定期使用OPTIMIZE TABLE語(yǔ)法優(yōu)化。
max_write_lock_count:
缺省情況下,寫操作的優(yōu)先級(jí)要高于讀操作的優(yōu)先級(jí),即便是先發(fā)送的讀請(qǐng)求,后發(fā)送的寫請(qǐng)求,此時(shí)也會(huì)優(yōu)先處理寫請(qǐng)求,然后再處理讀請(qǐng)求。這就造成一 個(gè)問(wèn)題:一旦我發(fā)出若干個(gè)寫請(qǐng)求,就會(huì)堵塞所有的讀請(qǐng)求,直到寫請(qǐng)求全都處理完,才有機(jī)會(huì)處理讀請(qǐng)求。此時(shí)可以考慮使用 max_write_lock_count:
max_write_lock_count=1
有了這樣的設(shè)置,當(dāng)系統(tǒng)處理一個(gè)寫操作后,就會(huì)暫停寫操作,給讀操作執(zhí)行的機(jī)會(huì)。
low-priority-updates:
我們還可以更干脆點(diǎn),直接降低寫操作的優(yōu)先級(jí),給讀操作更高的優(yōu)先級(jí)。
low-priority-updates=1
綜合來(lái)看,concurrent_insert=2是絕對(duì)推薦的,至于max_write_lock_count=1和low-priority- updates=1,則視情況而定,如果可以降低寫操作的優(yōu)先級(jí),則使用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(默認(rèn)8M)
3 如果是非空表,使用alter table table_name disable keys,然后load data infile,導(dǎo)入完數(shù)據(jù)在執(zhí)行:
alter table table_name enable keys. 如果是空表,就不需要這個(gè)操作,因?yàn)閙yisam表在空表中導(dǎo)入數(shù)據(jù)時(shí),是先導(dǎo)入數(shù)據(jù)然后建立indexs。
4 在插入數(shù)據(jù)時(shí)考慮使用:insert delayed….這樣操作實(shí)際mysql把insert操作放到隊(duì)列里面,進(jìn)行相對(duì)集中的插入,速度更快。
5. 使用load data infile 比使用insert 操作快近20倍,盡量使用此操作。
在innodb engine下
1.導(dǎo)入數(shù)據(jù)之前執(zhí)行set unique_checks=0來(lái)禁止對(duì)唯一索引的檢查,數(shù)據(jù)導(dǎo)入完成之后再運(yùn)行set unique_checks=1.
2. 導(dǎo)入數(shù)據(jù)之前執(zhí)行set foreign_key_checks=0來(lái)禁止對(duì)外鍵的檢查,數(shù)據(jù)導(dǎo)入完成之后再執(zhí)行set foreign_key_checks=1.
3.導(dǎo)入數(shù)據(jù)之前執(zhí)行set autocommit=0禁止自動(dòng)事務(wù)的自動(dòng)提交,數(shù)據(jù)導(dǎo)入完成之后,執(zhí)行set autocommit=1 恢復(fù)自動(dòng)提交操作。
使用innodb engine的表,物理存儲(chǔ)都是按PK的順序存的。不能使用類似于myisam一樣disable keys.
硬件上提高磁盤的I/0對(duì)插入速度很有好處(所以如果進(jìn)行大數(shù)據(jù)量的導(dǎo)入導(dǎo)出工作,盡量在比較NB的硬件上進(jìn)行,能縮減完成的時(shí)間,已經(jīng)防止出現(xiàn)問(wèn)題)。
當(dāng)一個(gè)線程對(duì)一個(gè)表執(zhí)行一個(gè)DELAYED語(yǔ)句時(shí),如果不存在這樣的處理程序,一個(gè)處理器線程被創(chuàng)建以處理對(duì)于該表的所有DELAYED語(yǔ)句。
線程檢查處理程序是否已經(jīng)獲得了一個(gè)DELAYED鎖;如果沒(méi)有,它告訴處理程序去獲得。即使其他的線程有在表上的一個(gè)READ或WRITE鎖,也能獲得 DELAYED鎖。然而,處理程序?qū)⒌却蠥LTER TABLE鎖或FLUSH TABLES以保證表結(jié)構(gòu)是最新的。
線程執(zhí)行INSERT語(yǔ)句,但不是將行寫入表,它把最后一行的副本放進(jìn)被處理器線程管理的一個(gè)隊(duì)列。任何語(yǔ)法錯(cuò)誤都能被線程發(fā)覺(jué)并報(bào)告給客戶程序。
顧客不能報(bào)告結(jié)果行的重復(fù)次數(shù)或AUTO_INCREMENT值;它不能從服務(wù)器獲得它們,因?yàn)镮NSERT在插入操作完成前返回。如果你使用C API,同樣原因,mysql_info()函數(shù)不返回任何有意義的東西。
當(dāng)行被插入到表中時(shí),更新日志有處理器線程更新。在多行插入的情況下,當(dāng)?shù)谝恍斜徊迦霑r(shí),更新日志被更新。
在每寫入delayed_insert_limit行后,處理器檢查是否任何SELECT語(yǔ)句仍然是未完成,如果這樣,在繼續(xù)之前允許執(zhí)行這些語(yǔ)句。
當(dāng)處理器在它的隊(duì)列中沒(méi)有更多行時(shí),表被解鎖。如果在delayed_insert_timeout秒內(nèi)沒(méi)有收到新的INSERT DELAYED命令,處理器終止。
如果已經(jīng)有多于delayed_queue_size行在一個(gè)特定的處理器隊(duì)列中未解決,線程等待直到隊(duì)列有空間。這有助于保證mysqld服務(wù)器對(duì)延遲的內(nèi)存隊(duì)列不使用所有內(nèi)存。
處理器線程將在Command列的MySQL進(jìn)程表中顯示delayed_insert。如果你執(zhí)行一個(gè)FLUSH TABLES命令或以KILL thread_id殺死它,它將被殺死,然而,它在退出前首先將所有排隊(duì)的行存進(jìn)表中。在這期間,這次它將不從其他線程接受任何新的INSERT命令。如 果你在它之后執(zhí)行一個(gè)INSERT DELAYED,將創(chuàng)建一個(gè)新的處理器線程。
注意,上述意味著,如果有一個(gè)INSERT DELAYED處理器已經(jīng)運(yùn)行,INSERT DELAYED命令有比正常INSERT更高的優(yōu)先級(jí)!其他更新命令將必須等到INSERT DELAY排隊(duì)變空、殺死處理器線程(用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ù)字
高并發(fā)insert語(yǔ)句的解決方法
前言
1、防止數(shù)據(jù)多次修改
1.1 、insert方案
1、添加uniqpue進(jìn)行解決(重復(fù)則是更新)
insert一般沒(méi)什么問(wèn)題,直接控制好unique就可以,這樣的話,就不會(huì)插入兩條(如果重復(fù)了則,進(jìn)行更新操作)
2、update方案
1、redis分布式鎖、消息隊(duì)列(每次只插入一個(gè))
2、mysql鎖(更新可以使用樂(lè)觀鎖)
2、高并發(fā)下的安全性
1、在線的網(wǎng)站上去執(zhí)行一個(gè)大的DELETE或INSERT查詢,要避免你的操作讓你的整個(gè)網(wǎng)站停止相應(yīng)。因?yàn)檫@兩個(gè)操作是會(huì)鎖表的(update也是,如果沒(méi)有指定唯一主鍵或者索引的話,會(huì)鎖表),表一鎖住了,別的操作都進(jìn)不來(lái)了。所以一定要非常小心
2、如果你把你的表鎖上一段時(shí)間,比如30秒鐘,那么對(duì)于一個(gè)有很高訪問(wèn)量的站點(diǎn)來(lái)說(shuō),這30秒所積累的訪問(wèn)進(jìn)程/線程,數(shù)據(jù)庫(kù)鏈接,打開的文件數(shù),可能不僅僅會(huì)讓你泊WEB服務(wù)Crash,還可能會(huì)讓你的整臺(tái)服務(wù)器馬上掛了。 >
2.1、解決方案
2.1.1、表的調(diào)整
把表按列變成幾張表的方法,這樣可以降低表的復(fù)雜度和字段的數(shù)目,從而達(dá)到優(yōu)化的目的。(如果有一百多個(gè)字段是很恐怖的)
示例一:
在Users表中有一個(gè)字段是家庭地址,這個(gè)字段是可選字段,相比起,而且你在數(shù)據(jù)庫(kù)操作的時(shí)候除了個(gè)人信息外,你并不需要經(jīng)常讀取或是改寫這個(gè)字段。那么,為什么不把他放到另外一張表中呢?這樣會(huì)讓你的表有更好的性能,大家想想是不是,大量的時(shí)候,我對(duì)于用戶表來(lái)說(shuō),只有用戶ID,用戶名,口令,用戶角色等會(huì)被經(jīng)常使用。小一點(diǎn)的表總是會(huì)有好的性能。
示例二:
你有一個(gè)叫“l(fā)ast_login”的字段,它會(huì)在每次用戶登錄時(shí)被更新。但是,每次更新時(shí)會(huì)導(dǎo)致該表的查詢緩存被清空。所以,你可以把這個(gè)字段放到另一個(gè)表中,這樣就不會(huì)影響你對(duì)用戶ID,用戶名,用戶角色的不停地讀取了,因?yàn)椴樵兙彺鏁?huì)幫你增加很多性能。hp程序員之家
另外,需要注意的是,這些被分出去的字段所形成的表,我們是認(rèn)為不會(huì)經(jīng)常是join的,否則,這樣的性能會(huì)比不分割表的時(shí)候時(shí)還要差,而且,會(huì)是極數(shù)級(jí)的下降
您可能感興趣的文章:- 詳細(xì)聊聊MySQL中慢SQL優(yōu)化的方向
- 淺談MySQL之select優(yōu)化方案
- Mysql縱表轉(zhuǎn)換為橫表的方法及優(yōu)化教程
- MySQL千萬(wàn)級(jí)數(shù)據(jù)表的優(yōu)化實(shí)戰(zhàn)記錄
- MySql子查詢IN的執(zhí)行和優(yōu)化的實(shí)現(xiàn)
- 帶你快速搞定Mysql優(yōu)化
- mysql優(yōu)化之query_cache_limit參數(shù)說(shuō)明
- MySQL優(yōu)化之如何寫出高質(zhì)量sql語(yǔ)句
- mysql查詢優(yōu)化之100萬(wàn)條數(shù)據(jù)的一張表優(yōu)化方案
- MYSQL 的10大經(jīng)典優(yōu)化案例場(chǎng)景實(shí)戰(zhàn)