如何處理mysql數(shù)據(jù)庫并發(fā)更新問題

現(xiàn)象

成都創(chuàng)新互聯(lián)長期為上1000家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為北湖企業(yè)提供專業(yè)的網(wǎng)站設(shè)計(jì)制作、做網(wǎng)站，北湖網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

Sysbench對MySQL進(jìn)行壓測, 并發(fā)數(shù)過大(5k)時(shí), Sysbench建立連接的步驟會(huì)超時(shí).

猜想

猜想: 直覺上這很簡單, Sysbench每建立一個(gè)連接, 都要消耗一個(gè)線程, 資源消耗過大導(dǎo)致超時(shí).

驗(yàn)證: 修改Sysbench源碼, 調(diào)大超時(shí)時(shí)間, 仍然會(huì)發(fā)生超時(shí).

檢查環(huán)境

猜想失敗, 回到常規(guī)的環(huán)境檢查:

MySQL error log 未見異常.

syslog 未見異常.

tcpdump 觀察網(wǎng)絡(luò)包未見異常, 連接能完成正常的三次握手; 只觀察到在出問題的連接中, 有一部分的TCP握手的第一個(gè)SYN包發(fā)生了重傳, 另一部分沒有發(fā)生重傳.

自己寫一個(gè)簡單的并發(fā)發(fā)生器, 替換sysbench, 可重現(xiàn)場景. 排除sysbench的影響

猜想2

懷疑 MySQL 在應(yīng)用層因?yàn)槟撤N原因, 沒有發(fā)送握手包, 比如卡在某一個(gè)流程上:

檢查MySQL堆棧未見異常, 仿佛MySQL在應(yīng)用層沒有看到新連接進(jìn)入.

通過strace檢查MySQL, 發(fā)現(xiàn)?accept()?調(diào)用確實(shí)沒有感知到新連接.

懷疑是OS的原因, Google之, 得到參考文檔:?A TCP “stuck” connection mystery【】

分析

參考文檔中的現(xiàn)象跟目前的狀況很類似, 簡述如下:

正常的TCP連接流程:

Client 向 Server 發(fā)起連接請求, 發(fā)送SYN.

Server 預(yù)留連接資源, 向 Client 回復(fù)SYN-ACK.

Client 向 Server 回復(fù)ACK.

Server 收到 ACK, 連接建立.

在業(yè)務(wù)層上, Client和Server間進(jìn)行通訊.

當(dāng)發(fā)生類似SYN-flood的現(xiàn)象時(shí), TCP連接的流程會(huì)使用SYN-cookie, 變?yōu)?

Client 向 Server 發(fā)起連接請求, 發(fā)送SYN.

Server 不預(yù)留連接資源, 向 Client 回復(fù)SYN-ACK, 包中附帶有簽名A.

Client 向 Server 回復(fù)ACK, 附帶 f(簽名A) (對簽名進(jìn)行運(yùn)算的結(jié)果).

Server 驗(yàn)證簽名, 分配連接資源, 連接建立.

在業(yè)務(wù)層上, Client和Server間進(jìn)行通訊.

當(dāng)啟用SYN-cookie時(shí), 第3步的ACK包因?yàn)?某種原因?丟失, 那么:

從Client的視角, 連接已經(jīng)建立.

從Server的視角, 連接并不存在, 既沒有建立, 也沒有”即將建立” (若不啟用SYN-cookie, Server會(huì)知道某個(gè)連接”即將建立”)

發(fā)生這種情況時(shí):

若業(yè)務(wù)層的第一個(gè)包應(yīng)是從 Client 發(fā)往 Server, 則會(huì)進(jìn)行重發(fā)或拋出連接錯(cuò)誤

若業(yè)務(wù)層的第一個(gè)包應(yīng)是從 Server 發(fā)往 Client的, Server不會(huì)發(fā)出第一個(gè)包. MySQL的故障就屬于這種情況.

TCP握手的第三步ACK包為什么丟失

參考文檔中, 對于TCP握手的第三步ACK包的丟失原因, 描述為:

Some of these packets get lost because some buffer somewhere overflows.

我們可以通過Systemtap進(jìn)一步探究原因.?通過一個(gè)簡單的腳本:

probe kernel.function("cookie_v4_check").return

{

source_port = @cast($skb-head + $skb-transport_header, "struct tcphdr")-source

printf("source=%d, return=%d\n",readable_port(source_port), $return)

}

function readable_port(port) {

return (port ((19)-1)) 8 | (port 8)

}

觀察結(jié)果, 可以確認(rèn)cookie_v4_check?(syn cookie機(jī)制進(jìn)行包簽名檢查的函數(shù))會(huì)返回 NULL(0). 即驗(yàn)證是由于syn cookie驗(yàn)證不通過, 導(dǎo)致TCP握手的第三步ACK包不被接受.

之后就是對其中不同條件進(jìn)行觀察, 看看是哪個(gè)條件不通過. 最終原因是accept隊(duì)列滿(sk_acceptq_is_full):

static inline bool sk_acceptq_is_full(const struct sock ?*sk){ ? return sk-sk_ack_backlog sk- ? sk_max_ack_backlog;}

恢復(fù)故障與日志的正關(guān)聯(lián)

在故障處理的一開始, 我們就檢查了syslog, 結(jié)論是未見異常.

當(dāng)整個(gè)故障分析完成, 得知了故障與syn cookie有關(guān), 回頭看syslog, 里面是有相關(guān)的信息, 只是和故障發(fā)生的時(shí)間不匹配, 沒有正關(guān)聯(lián), 因此被忽略.

檢查Linux源碼:

if (!queue-synflood_warned

sysctl_tcp_syncookies != 2

xchg(queue-synflood_warned, 1) == 0)

pr_info("%s: Possible SYN flooding on port %d. %s.

Check SNMP counters.\n",

proto, ntohs(tcp_hdr(skb)-dest), msg);

可以看到日志受到了抑制, 因此日志與故障的正關(guān)聯(lián)被破壞.

粗看源碼, 每個(gè)listen socket只會(huì)發(fā)送一次告警日志, 要獲得日志與故障的正關(guān)聯(lián), 必須每次測試重啟MySQL.

解決方案

這種故障一旦形成, 難以檢測; 系統(tǒng)日志中只會(huì)出現(xiàn)一次, 在下次重啟MySQL之前就不會(huì)再出現(xiàn)了; Client如果沒有合適的超時(shí)機(jī)制, 萬劫不復(fù).

解決方案:

1. 修改MySQL的協(xié)議, 讓Client先發(fā)握手包. 顯然不現(xiàn)實(shí).

2. 關(guān)閉syn_cookie. 有安全的人又要跳出來了.

3. 或者調(diào)高syn_cookie的觸發(fā)條件 (syn backlog長度). 降低系統(tǒng)對syn flood的敏感度, 使之可以容忍業(yè)務(wù)的syn波動(dòng).

有多個(gè)系統(tǒng)參數(shù)混合影響syn backlog長度, 參看【】

下圖為精華總結(jié)

請點(diǎn)擊輸入圖片描述

如何使用mysql數(shù)據(jù)庫解決高并發(fā)

使用mysql異步查詢,需要使用mysqlnd作為PHP的MySQL數(shù)據(jù)庫驅(qū)動(dòng)。 使用MySQL異步... 如果創(chuàng)建的線程過多,則會(huì)造成線程切換引起系統(tǒng)負(fù)載過高。Swoole中的異步MySQL其...

如何處理數(shù)據(jù)庫并發(fā)問題

想要知道如何處理數(shù)據(jù)并發(fā)，自然需要先了解數(shù)據(jù)并發(fā)。

什么是數(shù)據(jù)并發(fā)操作呢？

就是同一時(shí)間內(nèi)，不同的線程同時(shí)對一條數(shù)據(jù)進(jìn)行讀寫操作。

在互聯(lián)網(wǎng)時(shí)代，一個(gè)系統(tǒng)常常有很多人在使用，因此就可能出現(xiàn)高并發(fā)的現(xiàn)象，也就是不同的用戶同時(shí)對一條數(shù)據(jù)進(jìn)行操作，如果沒有有效的處理，自然就會(huì)出現(xiàn)數(shù)據(jù)的異常。而最常見的一種數(shù)據(jù)并發(fā)的場景就是電商中的秒殺，成千上萬個(gè)用戶對在極端的時(shí)間內(nèi)，搶購一個(gè)商品。針對這種場景，商品的庫存就是一個(gè)需要控制的數(shù)據(jù)，而多個(gè)用戶對在同一時(shí)間對庫存進(jìn)行重寫，一個(gè)不小心就可能出現(xiàn)超賣的情況。

針對這種情況，我們?nèi)绾斡行У奶幚頂?shù)據(jù)并發(fā)呢？

第一種方案、數(shù)據(jù)庫鎖

從鎖的基本屬性來說，可以分為兩種：一種是共享鎖（S），一種是排它鎖（X）。在MySQL的數(shù)據(jù)庫中，是有四種隔離級(jí)別的，會(huì)在讀寫的時(shí)候，自動(dòng)的使用這兩種鎖，防止數(shù)據(jù)出現(xiàn)混亂。

這四種隔離級(jí)別分別是：

讀未提交（Read Uncommitted）

讀提交（Read Committed）

可重復(fù)讀（Repeated Read）

串行化（Serializable）

當(dāng)然，不同的隔離級(jí)別，效率也是不同的，對于數(shù)據(jù)的一致性保證也就有不同的結(jié)果。而這些可能出現(xiàn)的又有哪些呢？

臟讀（dirty read）

當(dāng)事務(wù)與事務(wù)之間沒有任何隔離的時(shí)候，就可能會(huì)出現(xiàn)臟讀。例如：商家想看看所有的訂單有哪些，這時(shí)，用戶A提交了一個(gè)訂單，但事務(wù)還沒提交，商家卻看到了這個(gè)訂單。而這時(shí)就會(huì)出現(xiàn)一種問題，當(dāng)商家去操作這個(gè)訂單時(shí)，可能用戶A的訂單由于部分問題，導(dǎo)致數(shù)據(jù)回滾，事務(wù)沒有提交，這時(shí)商家的操作就會(huì)失去目標(biāo)。

不可重復(fù)讀（unrepeatable read）

一個(gè)事務(wù)中，兩次讀操作出來的同一條數(shù)據(jù)值不同，就是不可重復(fù)讀。

例如：我們有一個(gè)事務(wù)A，需要去查詢一下商品庫存，然后做扣減，這時(shí)，事務(wù)B操作了這個(gè)商品，扣減了一部分庫存，當(dāng)事務(wù)A再次去查詢商品庫存的時(shí)候，發(fā)現(xiàn)這一次的結(jié)果和上次不同了，這就是不可重復(fù)讀。

幻讀（phantom problem）

一個(gè)事務(wù)中，兩次讀操作出來的結(jié)果集不同，就是幻讀。

例如：一個(gè)事務(wù)A，去查詢現(xiàn)在已經(jīng)支付的訂單有哪些，得到了一個(gè)結(jié)果集。這時(shí)，事務(wù)B新提交了一個(gè)訂單，當(dāng)事務(wù)A再次去查詢時(shí)，就會(huì)出現(xiàn)，兩次得到的結(jié)果集不同的情況，也就是幻讀了。

那針對這些結(jié)果，不同的隔離級(jí)別可以干什么呢？

“讀未提（Read Uncommitted）”能預(yù)防啥？啥都預(yù)防不了。

“讀提交（Read Committed）”能預(yù)防啥？使用“快照讀（Snapshot Read）”方式，避免“臟讀”，但是可能出現(xiàn)“不可重復(fù)讀”和“幻讀”。

“可重復(fù)讀（Repeated Red）”能預(yù)防啥？使用“快照讀（Snapshot Read）”方式，鎖住被讀取記錄，避免出現(xiàn)“臟讀”、“不可重復(fù)讀”，但是可能出現(xiàn)“幻讀”。

“串行化（Serializable）”能預(yù)防啥？有效避免“臟讀”、“不可重復(fù)讀”、“幻讀”，不過運(yùn)行效率奇差。

好了，鎖說完了，但是，我們的數(shù)據(jù)庫鎖，并不能有效的解決并發(fā)的問題，只是盡可能保證數(shù)據(jù)的一致性，當(dāng)并發(fā)量特別大時(shí)，數(shù)據(jù)庫還是容易扛不住。那解決數(shù)據(jù)并發(fā)的另一個(gè)手段就是，盡可能的提高處理的速度。

因?yàn)閿?shù)據(jù)的IO要提升難度比較大，那么通過其他的方式，對數(shù)據(jù)進(jìn)行處理，減少數(shù)據(jù)庫的IO，就是提高并發(fā)能力的有效手段了。

最有效的一種方式就是：緩存

想要減少并發(fā)出現(xiàn)的概率，那么讀寫的效率越高，讀寫的執(zhí)行時(shí)間越短，自然數(shù)據(jù)并發(fā)的可能性就變小了，并發(fā)性能也有提高了。

還是用剛才的秒殺舉例，我們?yōu)榈木褪潜ＷC庫存的數(shù)據(jù)不出錯(cuò)，賣出一個(gè)商品，減一個(gè)庫存，那么，我們就可以將庫存放在內(nèi)存中進(jìn)行處理。這樣，就能夠保證庫存有序的及時(shí)扣減，并且不出現(xiàn)問題。這樣，我們的數(shù)據(jù)庫的寫操作也變少了，執(zhí)行效率也就大大提高了。

當(dāng)然，常用的分布式緩存方式有：Redis和Memcache，Redis可以持久化到硬盤，而Memcache不行，應(yīng)該怎么選擇，就看具體的使用場景了。

當(dāng)然，緩存畢竟使用的范圍有限，很多的數(shù)據(jù)我們還是必須持久化到硬盤中，那我們就需要提高數(shù)據(jù)庫的IO能力，這樣避免一個(gè)線程執(zhí)行時(shí)間太長，造成線程的阻塞。

那么，讀寫分離就是另一種有效的方式了

當(dāng)我們的寫成為了瓶頸的時(shí)候，讀寫分離就是一種可以選擇的方式了。

我們的讀庫就只需要執(zhí)行讀，寫庫就只需要執(zhí)行寫，把讀的壓力從主庫中分離出去，讓主庫的資源只是用來保證寫的效率，從而提高寫操作的性能。

mysql 更新依賴查詢 怎么保證并發(fā)

關(guān)于mysql處理百萬級(jí)以上的數(shù)據(jù)時(shí)如何提高其查詢速度的方法

最近一段時(shí)間由于工作需要，開始關(guān)注針對Mysql數(shù)據(jù)庫的select查詢語句的相關(guān)優(yōu)化方法。

由于在參與的實(shí)際項(xiàng)目中發(fā)現(xiàn)當(dāng)mysql表的數(shù)據(jù)量達(dá)到百萬級(jí)時(shí)，普通SQL查詢效率呈直線下降，而且如果where中的查詢條件較多時(shí)，其查詢速度簡直無法容忍。曾經(jīng)測試對一個(gè)包含400多萬條記錄（有索引）的表執(zhí)行一條條件查詢，其查詢時(shí)間竟然高達(dá)40幾秒，相信這么高的查詢延時(shí)，任何用戶都會(huì)抓狂。因此如何提高sql語句查詢效率，顯得十分重要。以下是網(wǎng)上流傳比較廣泛的30種SQL查詢語句優(yōu)化方法：

1、應(yīng)盡量避免在 where 子句中使用!=或操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

2、對查詢進(jìn)行優(yōu)化，應(yīng)盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引。

3、應(yīng)盡量避免在 where 子句中對字段進(jìn)行 null 值判斷，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒有null值，然后這樣查詢：

select id from t where num=0

4、盡量避免在 where 子句中使用 or 來連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10

union all

select id from t where num=20

5、下面的查詢也將導(dǎo)致全表掃描：(不能前置百分號(hào))

select id from t where name like ‘%c%’

若要提高效率，可以考慮全文檢索。

6、in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3)

對于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用參數(shù)，也會(huì)導(dǎo)致全表掃描。因?yàn)镾QL只有在運(yùn)行時(shí)才會(huì)解析局部變量，但優(yōu)化程序不能將訪問計(jì)劃的選擇推遲到運(yùn)行時(shí)；它必須在編譯時(shí)進(jìn)行選擇。然 而，如果在編譯時(shí)建立訪問計(jì)劃，變量的值還是未知的，因而無法作為索引選擇的輸入項(xiàng)。如下面語句將進(jìn)行全表掃描：

select id from t where num=@num

可以改為強(qiáng)制查詢使用索引：

select id from t with(index(索引名)) where num=@num

8、應(yīng)盡量避免在 where 子句中對字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

9、應(yīng)盡量避免在where子句中對字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where substring(name,1,3)=’abc’–name以abc開頭的id

select id from t where datediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

應(yīng)改為:

select id from t where name like ‘a(chǎn)bc%’

select id from t where createdate=’2005-11-30′ and createdate’2005-12-1′

10、不要在 where 子句中的“=”左邊進(jìn)行函數(shù)、算術(shù)運(yùn)算或其他表達(dá)式運(yùn)算，否則系統(tǒng)將可能無法正確使用索引。

11、在使用索引字段作為條件時(shí)，如果該索引是復(fù)合索引，那么必須使用到該索引中的第一個(gè)字段作為條件時(shí)才能保證系統(tǒng)使用該索引，否則該索引將不會(huì)被使 用，并且應(yīng)盡可能的讓字段順序與索引順序相一致。

12、不要寫一些沒有意義的查詢，如需要生成一個(gè)空表結(jié)構(gòu)：

select col1,col2 into #t from t where 1=0

這類代碼不會(huì)返回任何結(jié)果集，但是會(huì)消耗系統(tǒng)資源的，應(yīng)改成這樣：

create table #t(…)

13、很多時(shí)候用 exists 代替 in 是一個(gè)好的選擇：

select num from a where num in(select num from b)

用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14、并不是所有索引對查詢都有效，SQL是根據(jù)表中數(shù)據(jù)來進(jìn)行查詢優(yōu)化的，當(dāng)索引列有大量數(shù)據(jù)重復(fù)時(shí)，SQL查詢可能不會(huì)去利用索引，如一表中有字段 sex，male、female幾乎各一半，那么即使在sex上建了索引也對查詢效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率，因?yàn)?insert 或 update 時(shí)有可能會(huì)重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個(gè)表的索引數(shù)最好不要超過6個(gè)，若太多則應(yīng)考慮一些不常使用到的列上建的索引是否有 必要。

16.應(yīng)盡可能的避免更新 clustered 索引數(shù)據(jù)列，因?yàn)?clustered 索引數(shù)據(jù)列的順序就是表記錄的物理存儲(chǔ)順序，一旦該列值改變將導(dǎo)致整個(gè)表記錄的順序的調(diào)整，會(huì)耗費(fèi)相當(dāng)大的資源。若應(yīng)用系統(tǒng)需要頻繁更新 clustered 索引數(shù)據(jù)列，那么需要考慮是否應(yīng)將該索引建為 clustered 索引。

17、盡量使用數(shù)字型字段，若只含數(shù)值信息的字段盡量不要設(shè)計(jì)為字符型，這會(huì)降低查詢和連接的性能，并會(huì)增加存儲(chǔ)開銷。這是因?yàn)橐嬖谔幚聿樵兒瓦B接時(shí)會(huì) 逐個(gè)比較字符串中每一個(gè)字符，而對于數(shù)字型而言只需要比較一次就夠了。

18、盡可能的使用 varchar/nvarchar 代替 char/nchar ，因?yàn)槭紫茸冮L字段存儲(chǔ)空間小，可以節(jié)省存儲(chǔ)空間，其次對于查詢來說，在一個(gè)相對較小的字段內(nèi)搜索效率顯然要高些。

19、任何地方都不要使用 select * from t ，用具體的字段列表代替“*”，不要返回用不到的任何字段。

20、盡量使用表變量來代替臨時(shí)表。如果表變量包含大量數(shù)據(jù)，請注意索引非常有限（只有主鍵索引）。

21、避免頻繁創(chuàng)建和刪除臨時(shí)表，以減少系統(tǒng)表資源的消耗。

22、臨時(shí)表并不是不可使用，適當(dāng)?shù)厥褂盟鼈兛梢允鼓承├谈行?，例如，?dāng)需要重復(fù)引用大型表或常用表中的某個(gè)數(shù)據(jù)集時(shí)。但是，對于一次性事件，最好使 用導(dǎo)出表。

23、在新建臨時(shí)表時(shí)，如果一次性插入數(shù)據(jù)量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數(shù)據(jù)量不大，為了緩和系統(tǒng)表的資源，應(yīng)先create table，然后insert。

24、如果使用到了臨時(shí)表，在存儲(chǔ)過程的最后務(wù)必將所有的臨時(shí)表顯式刪除，先 truncate table ，然后 drop table ，這樣可以避免系統(tǒng)表的較長時(shí)間鎖定。

25、盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過1萬行，那么就應(yīng)該考慮改寫。

26、使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來解決問題，基于集的方法通常更有效。

27、與臨時(shí)表一樣，游標(biāo)并不是不可使用。對小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。在結(jié)果集中包括“合計(jì)”的例程通常要比使用游標(biāo)執(zhí)行的速度快。如果開發(fā)時(shí) 間允許，基于游標(biāo)的方法和基于集的方法都可以嘗試一下，看哪一種方法的效果更好。

28、在所有的存儲(chǔ)過程和觸發(fā)器的開始處設(shè)置 SET NOCOUNT ON ，在結(jié)束時(shí)設(shè)置 SET NOCOUNT OFF 。無需在執(zhí)行存儲(chǔ)過程和觸發(fā)器的每個(gè)語句后向客戶端發(fā)送 DONE_IN_PROC 消息。

29、盡量避免向客戶端返回大數(shù)據(jù)量，若數(shù)據(jù)量過大，應(yīng)該考慮相應(yīng)需求是否合理。

30、盡量避免大事務(wù)操作，提高系統(tǒng)并發(fā)能力。