本系列文章主要是本人在游戲服務(wù)端開(kāi)發(fā)過(guò)程中啊奄，遇到的一些不那么為人熟知但我又覺(jué)得比較重要的MySQL知識(shí)的介紹莫辨。希望里面淺薄的文字能為了提供一點(diǎn)點(diǎn)的幫助食零。

慢查詢

從我有限的從業(yè)經(jīng)驗(yàn)來(lái)看震缭，大多數(shù)數(shù)據(jù)庫(kù)導(dǎo)致的游戲服務(wù)器問(wèn)題嘶窄，十有八九慢查詢哭廉。比如：SQL寫的亂七八糟導(dǎo)致的慢查詢脊僚、表數(shù)據(jù)太多導(dǎo)致的慢查詢、沒(méi)有使用索引導(dǎo)致的慢查詢、突然大并發(fā)量導(dǎo)致的慢查詢等等辽幌。數(shù)據(jù)庫(kù)一出現(xiàn)以上這些問(wèn)題增淹，開(kāi)發(fā)團(tuán)隊(duì)免不了要背大鍋。所以我認(rèn)為數(shù)據(jù)庫(kù)慢查詢應(yīng)該成為每個(gè)服務(wù)器開(kāi)發(fā)人員必知必會(huì)的知識(shí)點(diǎn)乌企。

慢查詢事故分享：

這里我分享一下我參與的第一次慢查詢定位和優(yōu)化經(jīng)歷虑润，這是一次非常慘痛的經(jīng)歷：

當(dāng)時(shí)我所在的項(xiàng)目組上線了一個(gè)零點(diǎn)秒殺的活動(dòng)，秒殺活動(dòng)開(kāi)啟的第一個(gè)晚上有大量玩家投訴卡死在活動(dòng)界面加酵。我們當(dāng)時(shí)按直覺(jué)判斷肯定是太多人秒殺導(dǎo)致游戲服務(wù)器壓力過(guò)大處理不過(guò)來(lái)拳喻，然后就決定發(fā)公告宣布延遲活動(dòng)和重啟游戲服務(wù)器。重啟服務(wù)器之后猪腕，玩家再次進(jìn)行秒殺問(wèn)題還是如此冗澈。這時(shí)有個(gè)同事說(shuō)，壓力應(yīng)該主要在MySQL服務(wù)器上應(yīng)該重啟MySQL服務(wù)器陋葡。然后我們又又發(fā)公告宣布延遲活動(dòng) + 重啟游戲服務(wù)器 + 重啟MySQL服務(wù)器亚亲。可能是因?yàn)榇蟛糠滞婕叶挤艞壍脑虬筛停@次問(wèn)題終于解決捌归。

后期我們進(jìn)行了復(fù)盤發(fā)現(xiàn)問(wèn)題是：游戲服務(wù)器后臺(tái)會(huì)在零點(diǎn)執(zhí)行一個(gè)定時(shí)統(tǒng)計(jì)任務(wù)，其中包含許多復(fù)雜的SQL語(yǔ)句（各種join岭粤、全表查詢等等）惜索。剛好這些SQL所操作的表又和秒殺活動(dòng)需要操作的表完全重合，最終導(dǎo)致數(shù)據(jù)庫(kù)大量慢查詢剃浇、MySQL服務(wù)器假死巾兆、線上玩家卡在秒殺界面。而出問(wèn)題那會(huì)項(xiàng)目組成員甚至不知道慢查詢是個(gè)什么東西偿渡，只是單純認(rèn)為是并發(fā)量太大導(dǎo)致服務(wù)器壓力太大負(fù)載不下臼寄，然后在沒(méi)有準(zhǔn)確定位根本原因的情況下就天真地用重啟游戲服務(wù)器和MySQL服務(wù)器來(lái)解決問(wèn)題。下圖說(shuō)明了但是MySQL服務(wù)器面臨的兩方面的壓力：

image.png

第一次我們只重啟游戲服務(wù)器溜宽，非但沒(méi)有減輕MySQL服務(wù)器的壓力吉拳，反而導(dǎo)致其壓力變得更大了。這是因?yàn)椋?/p>

1. 游戲服務(wù)器重啟有大量在線玩家被強(qiáng)制下線适揉，這些玩家的數(shù)據(jù)需要刷進(jìn)數(shù)據(jù)庫(kù)留攒，加重服務(wù)器壓力（本來(lái)上面那些慢查詢還沒(méi)執(zhí)行完，現(xiàn)在又來(lái)新的了）嫉嘀；
2. 游戲服務(wù)器重啟之后炼邀，零點(diǎn)執(zhí)行的那個(gè)定時(shí)統(tǒng)計(jì)任務(wù)會(huì)自主判斷是否要繼續(xù)執(zhí)行（由于上一次重啟游戲服務(wù)器導(dǎo)致今天的任務(wù)中斷，所以判斷結(jié)果是還要執(zhí)行一遍）剪侮，這樣對(duì)于MySQL而言就算是同時(shí)執(zhí)行兩個(gè)這個(gè)定時(shí)統(tǒng)計(jì)任務(wù)了拭宁。

在知道慢查詢?nèi)罩具@個(gè)東西之后洛退，我們還嘗試去線上找這個(gè)日志來(lái)看看，但由于數(shù)據(jù)庫(kù)默認(rèn)不開(kāi)啟慢查詢?nèi)罩镜拇蛴〗鼙辏Y(jié)果可想而知兵怯。不過(guò)我們還是模擬了當(dāng)時(shí)線上的情況重新測(cè)試一遍（這次開(kāi)了慢查詢），表現(xiàn)和當(dāng)時(shí)線上的狀況一致腔剂。最終才將這個(gè)問(wèn)題全部復(fù)盤媒区。

有上面這個(gè)案例打底之后，接下就開(kāi)始慢查詢的介紹

慢查詢是指那些執(zhí)行時(shí)間過(guò)長(zhǎng)且涉及行數(shù)過(guò)多的語(yǔ)句（一般超過(guò)配置的慢查詢時(shí)間和涉及行數(shù)才算）掸犬。慢查詢并不是一定是SELECT語(yǔ)句袜漩，其實(shí)所有的DML類型的SQL（INSERT、UPDATE湾碎、REPLACE宙攻、DELETE）都可能是慢查詢。根據(jù)官網(wǎng)的說(shuō)明胜茧，慢查詢?nèi)罩镜谋O(jiān)聽(tīng)和打印可能會(huì)是一個(gè)耗時(shí)任務(wù)粘优，所以MySQL服務(wù)器是默認(rèn)不開(kāi)啟的慢查詢仇味。

慢查詢配置（官方文檔）：

以上可以通過(guò)SHOW VARIABLES LIKE '%***%'這種形式進(jìn)行查詢缤言，并且可以通過(guò)SET GLOBAL ***=?來(lái)進(jìn)行動(dòng)態(tài)修改宝当。這些變量均是全局變量，對(duì)MySQL所有庫(kù)所有用戶生效胆萧。
查詢配置示例：

image.png

我在上面介紹information_schema庫(kù)的時(shí)候就有說(shuō)過(guò)庆揩，能用SHOW VARIABLES LIKE '%***%'查詢到的東西基本上也可以在information_schema庫(kù)查到。而且上面這些配置都是全局的，所以通過(guò)information_schema庫(kù)的GLOBAL_VARIABLES表也能查到（某些版本的MySQL可能是在performance_schema庫(kù)中订晌，可以通過(guò)：SELECT table_schema FROM information_schema.TABLES WHERE table_name = 'GLOBAL_VARIABLES'來(lái)查詢到底在哪個(gè)庫(kù)）犬性，如下圖：

image.png

image.png

慢查詢的判斷流程：

這里想強(qiáng)調(diào)的是：MySQL服務(wù)器是否打印慢查詢?nèi)罩荆皇侵煌ㄟ^(guò)SQL語(yǔ)句的執(zhí)行速度來(lái)判斷的腾仅，還和上面這些參數(shù)有關(guān)（源碼請(qǐng)戳）乒裆。這里上一張判斷流程圖說(shuō)明一下：

image.png

image.png

日志格式（官方文檔）：

我把long_query_time設(shè)置為0（SET GLOBAL long_query_time=0），打印格式設(shè)置為FILE和TABLE兩種模式（SET GLOBAL log_output='FILE,TABLE'）推励。下面截圖分別是慢查詢?nèi)罩竞吐樵儽淼臄?shù)據(jù)：

慢查詢?nèi)罩?png

慢查詢表.png

這里說(shuō)明一下幾個(gè)關(guān)鍵的字段：

• Query_time：該SQL語(yǔ)句執(zhí)行時(shí)長(zhǎng)（秒）鹤耍，精確到微秒；
• Lock_time：該SQL持有鎖的時(shí)長(zhǎng)（秒）验辞，精確到微秒稿黄。單純的select語(yǔ)句不會(huì)持有鎖（這時(shí)該值為0）；
• Rows_sent：發(fā)送給客戶端的數(shù)據(jù)行數(shù)
• Rows_examined：Server層掃描的數(shù)據(jù)行數(shù)

注意不管是慢查詢?nèi)罩具€是慢查詢表跌造，記錄的信息都是一條一條的慢查詢SQL語(yǔ)句杆怕。這樣的形式直接進(jìn)行分析會(huì)非常麻煩（比如想知道某一條慢查詢具體的耗時(shí)、鎖持有時(shí)間壳贪，或者對(duì)某一張表的慢查詢分析）陵珍。這里介紹一下業(yè)界比較常用的慢查詢?nèi)罩痉治龉ぞ撸?/p>

• mysqldumpslow：官方慢查詢分析工具，會(huì)分析指定的慢查詢?nèi)罩静⒔y(tǒng)計(jì)出同一慢查詢SQL出現(xiàn)的次數(shù)违施、總耗時(shí)等信息（如下圖）互纯，操作十分簡(jiǎn)單（Windows的需要安裝perl才行，使用說(shuō)明）磕蒲。

image.png

• pt-query-digest：業(yè)界常用的慢查詢?nèi)罩痉治龉ぞ吡袅省?duì)分表分庫(kù)的情況有很好的支持；定制化功能很多辣往；并且該工具不止能分析慢查詢?nèi)罩就迷海€能分析一般SQL日志（general_log）和binlog日志等。但是使用門檻比較高站削，如果應(yīng)用在線上環(huán)境需要一定的上手時(shí)間坊萝。
• Anemometer：圖形化界面，需要在pt-query-digest的基礎(chǔ)上才能工作钻哩。搭建繁瑣

業(yè)界對(duì)慢查詢的通用的處理流程：

• 如果是使用云服務(wù)器屹堰，可以在MySQL云服務(wù)器上設(shè)置慢查詢?nèi)罩痉治鯽nd告警，做到異常就郵件街氢、釘釘甚至是電話告警扯键，云服務(wù)廠商都有這個(gè)功能（阿里慢查詢告警設(shè)置）；
• 每周甚至是每天匯總一份慢查詢的統(tǒng)計(jì)自動(dòng)發(fā)郵件給DBA珊肃、運(yùn)維和開(kāi)發(fā)同學(xué)荣刑；
• 對(duì)有問(wèn)題的慢查詢做分析（有一些慢查詢是偶發(fā)性的馅笙，可以忽略），然后做歸零處理厉亏。

如何避免和解決慢查詢：

如何避免：

當(dāng)數(shù)據(jù)庫(kù)壓力過(guò)大時(shí)（CPU和I/O）董习，更容易出現(xiàn)慢查詢。本來(lái)一些正常的操作也會(huì)因?yàn)樽枞兂陕樵儼弧Ｓ行Ы档蛿?shù)據(jù)庫(kù)壓力就是一個(gè)標(biāo)本兼治的方案皿淋，慢查詢的避免倒是順帶的。降低數(shù)據(jù)庫(kù)壓力有很多種恬试，這里介紹下我認(rèn)為比較有效的幾種給大家參考一下：

• 使用緩存降低數(shù)據(jù)庫(kù)壓力：對(duì)于游戲服務(wù)器來(lái)說(shuō)窝趣，更新和讀取數(shù)據(jù)的頻率是非常驚人的（很多炸服的游戲就是栽在這上面）。很多時(shí)候玩家一個(gè)操作可能涉及大量數(shù)據(jù)的更新或者是查詢（比如：玩家登錄需要加載大量玩家數(shù)據(jù)训柴；戰(zhàn)斗結(jié)束需要發(fā)放獎(jiǎng)品和更新戰(zhàn)斗記錄等等）哑舒。如果這些操作都直接落到數(shù)據(jù)庫(kù)上面，那么數(shù)據(jù)庫(kù)的壓力將十分巨大幻馁。
  通過(guò)引用緩存中間件（MongoDB洗鸵、Redis）和游戲服務(wù)器進(jìn)程緩存來(lái)合并玩家操作產(chǎn)生的數(shù)據(jù)庫(kù)SQL、延遲回寫數(shù)據(jù)庫(kù)（延遲回寫這個(gè)不同項(xiàng)目可能有不同要求）仗嗦，可以很大程度上改善這種情況膘滨。并且引入緩存之后，玩家讀取相關(guān)數(shù)據(jù)也會(huì)命中緩存而不是直接打到數(shù)據(jù)庫(kù)儒将，這一點(diǎn)對(duì)降低服務(wù)器壓力幫助是非常有效的吏祸。如果不想引入緩存中間件，那直接使用進(jìn)程緩存也是可以的钩蚊，不過(guò)要注意內(nèi)存空間和釋放的問(wèn)題。許多大廠商對(duì)他們的產(chǎn)品（自研或代理）在擊穿數(shù)據(jù)庫(kù)這方面（即服務(wù)器繞過(guò)緩存直接訪問(wèn)數(shù)據(jù)庫(kù)加載玩家數(shù)據(jù)）都有嚴(yán)格的要求和限制蹈矮。他們都會(huì)極力避免代碼中出現(xiàn)直接操作數(shù)據(jù)庫(kù)的行為砰逻，最最重要的原因還是為了防止流量洪峰對(duì)數(shù)據(jù)庫(kù)的直接沖擊（即使是MySQL自己也有內(nèi)置的采用LRU策略的buff pool內(nèi)存緩存）。
  注意泛鸟！雖然引入緩存有好處蝠咆，但是也會(huì)有一定的風(fēng)險(xiǎn)。增加緩存不僅會(huì)增加系統(tǒng)復(fù)雜度北滥，同時(shí)也意味著數(shù)據(jù)冗余刚操，這很可能導(dǎo)致數(shù)據(jù)不一致。事實(shí)也是如此再芋，大部分的回檔菊霜、數(shù)據(jù)錯(cuò)亂等問(wèn)題都是緩存部分代碼有bug導(dǎo)致的，所以決定引入緩存之前要預(yù)留好充足的測(cè)試和壓測(cè)的時(shí)間济赎。

• 多服務(wù)器分?jǐn)倝毫?/strong>：上面關(guān)于緩存這一點(diǎn)更多是從客戶端（相對(duì)于數(shù)據(jù)庫(kù)）來(lái)解決問(wèn)題鉴逞，同時(shí)我們也可以通過(guò)增強(qiáng)數(shù)據(jù)庫(kù)服務(wù)器性能這一點(diǎn)來(lái)實(shí)現(xiàn)记某。比如使用主從模式下的讀寫分離、分庫(kù)分表等方法來(lái)將壓力分?jǐn)偟蕉嗯_(tái)數(shù)據(jù)庫(kù)服務(wù)器上构捡。