背景
“那啥，你過(guò)來(lái)一下晚伙！”
“怎么了吮龄，我代碼都單元測(cè)試了的，沒(méi)出問(wèn)題芭亓啤漓帚！”我一臉懵逼跑到運(yùn)維大佬旁邊。
“你看看午磁！你看看尝抖！多少條報(bào)警毡们，趕快優(yōu)化一下！”
運(yùn)維大佬短信列表里面50多條MySQL CPU 100%報(bào)警短信昧辽。再看看項(xiàng)目名稱不就是我前幾天剛發(fā)布的項(xiàng)目嗎Ｑ萌邸？
我心底一沉搅荞，趕快賠上笑臉红氯。“這個(gè)一定優(yōu)化取具，馬上優(yōu)化脖隶！那個(gè)，能不能看下數(shù)據(jù)庫(kù)監(jiān)控日志...”
運(yùn)維大佬又?jǐn)?shù)落了我?guī)拙湎炯欤缓笳{(diào)開(kāi)了數(shù)據(jù)庫(kù)監(jiān)控日志产阱。
那家伙...每秒300多的連接數(shù)，幾乎快要封頂?shù)娜頀呙钄?shù)块仆，還有大紅色CPU警報(bào)构蹬。。悔据。
“那個(gè)庄敛，能不能看看nginx訪問(wèn)日志...我看下訪問(wèn)量...”我弱弱地說(shuō)到。
運(yùn)維大佬不情愿的跑了下下面的語(yǔ)句：
grep -c come access.log
come這個(gè)接口是其中一個(gè)請(qǐng)求量比較大的接口科汗，結(jié)果是600多萬(wàn)藻烤。那個(gè)時(shí)候才中午，周末高峰期估計(jì)一天得有上千萬(wàn)吧头滔，
我撇了撇嘴怖亭，心里想著這么高的請(qǐng)求量，當(dāng)初那么摳門只給我一臺(tái)低配數(shù)據(jù)庫(kù)還好意思說(shuō)坤检，不過(guò)嘴上肯定是：“好好好兴猩，請(qǐng)求量不是很大，看來(lái)是數(shù)據(jù)庫(kù)問(wèn)題早歇，我立刻去優(yōu)化一下倾芝！”
“給它弄一個(gè)讀寫分離不就行了嗎！箭跳？”這時(shí)另外一個(gè)運(yùn)維大佬湊了過(guò)來(lái)晨另，隨意地?fù)]了揮手。谱姓。拯刁。
你問(wèn)我DBA去哪兒了？DBA當(dāng)時(shí)有點(diǎn)忙逝段，只說(shuō)讓我自己檢查一下垛玻。。奶躯。
優(yōu)化思路
我這個(gè)項(xiàng)目由于上線之前比較趕帚桩，所以前期并沒(méi)有管數(shù)據(jù)庫(kù)設(shè)計(jì)方面的一些問(wèn)題，如今隨著游戲接入嘹黔，請(qǐng)求量劇增才暴露出來(lái)账嚎。（其實(shí)是前期加班加煩了懶得搞）
這個(gè)問(wèn)題，并不需要增加數(shù)據(jù)庫(kù)硬件配置和增加讀寫分離這種高端手段就能解決儡蔓，我自個(gè)兒挖了多少坑郭蕉，心里還是有點(diǎn)碧樹(shù)的。
詳細(xì)的MySQL優(yōu)化步驟如下：
- 檢查數(shù)據(jù)表結(jié)構(gòu)喂江，改善不完善設(shè)計(jì)
- 跑一遍主要業(yè)務(wù)召锈，收集常用的數(shù)據(jù)庫(kù)查詢SQL
- 分析查詢SQL，適當(dāng)拆分获询，添加索引等優(yōu)化查詢
- 優(yōu)化SQL的同時(shí)涨岁，優(yōu)化代碼邏輯
- 添加本地緩存和redis緩存
這個(gè)項(xiàng)目是原生PHP寫的，以上這些只能自己做了吉嚣。
檢查數(shù)據(jù)表結(jié)構(gòu)
因?yàn)楸容^菜梢薪，回去看設(shè)計(jì)的表結(jié)構(gòu)，真是慘不忍睹尝哆。
盡可能不要使用NULL值
因?yàn)榻ū淼臅r(shí)候秉撇，如果不對(duì)創(chuàng)建的值設(shè)置默認(rèn)值，MySQL都會(huì)設(shè)置默認(rèn)為NULL秋泄。那么為啥用NULL不好呢琐馆？
-
NULL使得索引維護(hù)更加復(fù)雜，強(qiáng)烈建議對(duì)索引列設(shè)置NOT NULL -
NOT IN印衔、!=等負(fù)向條件查詢?cè)谟?code>NULL值的情況下返回永遠(yuǎn)為空結(jié)果啡捶，查詢?nèi)菀壮鲥e(cuò) -
NULL列需要一個(gè)額外字節(jié)作為判斷是否為NULL的標(biāo)志位 - 使用
NULL時(shí)和該列其他的值可能不是同種類型，導(dǎo)致問(wèn)題奸焙。（在不同的語(yǔ)言中表現(xiàn)不一樣） - MySQL難以優(yōu)化對(duì)可為
NULL的列的查詢
所以對(duì)于那些以前偷懶的字段瞎暑，手動(dòng)設(shè)置一個(gè)默認(rèn)值吧，空字符串呀与帆，0呀補(bǔ)上了赌。
雖然這種方法對(duì)于MySQL的性能來(lái)說(shuō)沒(méi)有提升多少，但是這是一個(gè)好習(xí)慣玄糟，而且以小見(jiàn)大勿她，不要忽略這些細(xì)節(jié)。
添加索引
對(duì)于經(jīng)常查詢的字段阵翎，請(qǐng)加上索引逢并，有索引和沒(méi)有索引的查詢速度相差十倍甚至更多之剧。
- 一般來(lái)說(shuō)，每張表都需要有一個(gè)主鍵
id字段 - 常用于查詢的字段應(yīng)該設(shè)置索引
-
varchar類型的字段砍聊，在建立索引的時(shí)候背稼，最好指定長(zhǎng)度 - 查詢有多個(gè)條件時(shí)，優(yōu)先使用具有索引的條件
- 像
LIKE條件這樣的模糊搜索對(duì)于字段索引是無(wú)效的玻蝌，需要另外建立關(guān)鍵詞索引來(lái)解決 - 請(qǐng)盡量不要在數(shù)據(jù)庫(kù)層面約束表和表之間的關(guān)系蟹肘，這些表之間的依賴應(yīng)該在代碼層面去解決
當(dāng)表和表之間有約束時(shí)，雖然增刪查的SQL語(yǔ)句變簡(jiǎn)單了俯树，但是帶來(lái)的負(fù)面效果是插入等操作數(shù)據(jù)庫(kù)都會(huì)去檢查約束（雖然可以手動(dòng)設(shè)置忽略約束）帘腹，這樣相當(dāng)于把一些業(yè)務(wù)邏輯寫到了數(shù)據(jù)庫(kù)層，不便于維護(hù)许饿。
優(yōu)化表字段結(jié)構(gòu)
數(shù)據(jù)庫(kù)中那些可以用整形表示的數(shù)據(jù)就不要使用字符串類型阳欲，到底是用varchar還是char要看字段的可能值。
這種優(yōu)化往往在數(shù)據(jù)庫(kù)中有大量數(shù)據(jù)以后是不可行的米辐，最好在數(shù)據(jù)庫(kù)設(shè)計(jì)之前就設(shè)計(jì)好胸完。
- 對(duì)于那些可能值很有限的列，使用
tinyint代替VARCHAR翘贮，- 比如記錄移動(dòng)設(shè)備平臺(tái)赊窥，只有兩個(gè)值：android，ios狸页，那么就可以使用0表示android锨能，1表示ios，這種列一定要寫好注釋
- 為什么不用
ENUM呢芍耘？ENUM擴(kuò)展困難址遇，比如后來(lái)移動(dòng)平臺(tái)又增加了一個(gè)ipad，那豈不是懵逼了斋竞，而tinyint加個(gè)2就行倔约，而且ENUM在代碼里面處理起來(lái)特別奇怪，是當(dāng)成整形呢還是字符串坝初，各個(gè)語(yǔ)言不一樣浸剩。 - 這種方式，一定要在數(shù)據(jù)庫(kù)注釋或者代碼里面寫明各個(gè)值的含義
- 對(duì)于那些定長(zhǎng)字符串鳄袍，可以使用
char绢要，比如郵編，總是5位 - 對(duì)于那些長(zhǎng)度未知的字符串拗小，使用
varchar - 不要濫用
bigint重罪，比如記錄文章數(shù)目的表id字段，用int就行了，21億篇文章上限夠了 - 適當(dāng)打破數(shù)據(jù)庫(kù)范式添加冗余字段剿配，避免查詢時(shí)的表連接
查詢的時(shí)候搅幅，肯定int類型比varchar快，因?yàn)檎麛?shù)的比較直接調(diào)用底層運(yùn)算器就可以實(shí)現(xiàn)呼胚，而字符串比較要逐個(gè)字符比較盏筐。
定長(zhǎng)數(shù)據(jù)比變長(zhǎng)數(shù)據(jù)查詢快，因?yàn)楸容^定長(zhǎng)數(shù)據(jù)字節(jié)與字節(jié)之間的偏移是固定的砸讳，很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移。而變長(zhǎng)數(shù)據(jù)則還需要多一步去查詢下一個(gè)數(shù)據(jù)的偏移量界牡。不過(guò)簿寂。定長(zhǎng)數(shù)據(jù)可能會(huì)浪費(fèi)更多的存儲(chǔ)空間。
大表拆分
對(duì)于那些數(shù)據(jù)量可能近期會(huì)超過(guò)500W或者增長(zhǎng)很快的表宿亡，一定要提前做好垂直分表或者水平分表常遂，當(dāng)數(shù)據(jù)量超過(guò)百萬(wàn)以后，查詢速度會(huì)明顯下降挽荠。
分庫(kù)分表盡量在數(shù)據(jù)庫(kù)設(shè)計(jì)初期敲定方案克胳，否則后期會(huì)極大增加代碼復(fù)雜性而且不易更改。
垂直分表是按照日期等外部變量進(jìn)行分表圈匆，水平分表是按照表中的某些字段關(guān)系漠另，使用hash映射等分表。
分庫(kù)分表的前提條件是在執(zhí)行查詢語(yǔ)句之前跃赚，已經(jīng)知道需要查詢的數(shù)據(jù)可能會(huì)落在哪一個(gè)分庫(kù)和哪一個(gè)分表中笆搓。
優(yōu)化查詢語(yǔ)句
這個(gè)才是很多系統(tǒng)數(shù)據(jù)庫(kù)瓶頸的始作俑者。
- 請(qǐng)盡量使用簡(jiǎn)單的查詢纬傲，避免使用表鏈接
- 請(qǐng)盡量避免全表掃描满败，包括但不限于：
- where子句條件橫真或?yàn)榭?/li>
- 使用LIKE
- 使用不等操作符（<>、!=）
- 查詢含義is null的列
- 在非索引列上使用or
- 多條件查詢時(shí)叹括，請(qǐng)把簡(jiǎn)單查詢條件或則索引列查詢置于前面
- 請(qǐng)盡量指定需要查詢的列算墨，不要偷懶使用select *
- 如果不指定，一方面會(huì)返回多余的數(shù)據(jù)汁雷，占用寬帶等
- 另一方面MySQL執(zhí)行查詢的時(shí)候净嘀，沒(méi)有字段時(shí)會(huì)先去查詢表結(jié)構(gòu)有哪些字段
- 大些的查詢關(guān)鍵字比小寫快一點(diǎn)點(diǎn)
- 使用子查詢會(huì)創(chuàng)建臨時(shí)表，會(huì)比鏈接（JOIN）和聯(lián)合（UNION）稍慢
- 在索引字段上查詢盡量不要使用數(shù)據(jù)庫(kù)函數(shù)摔竿，不便于緩存查詢結(jié)果
- 當(dāng)只要一行數(shù)據(jù)時(shí)面粮，請(qǐng)使用LIMIT 1，如果數(shù)據(jù)過(guò)多继低，請(qǐng)適當(dāng)設(shè)定LIMIT熬苍，分頁(yè)查詢
- 千萬(wàn)不要 ORDER BY RAND()，性能極低
上面是我總結(jié)的一些小tips，這些規(guī)則是死的柴底，但是業(yè)務(wù)場(chǎng)景是活的婿脸，在實(shí)際使用的過(guò)程中，比如數(shù)據(jù)統(tǒng)計(jì)柄驻，可以適當(dāng)犧牲性能換取便利狐树。
添加緩存
使用redis等緩存，還有本地文件緩存等鸿脓，可以極大地減少數(shù)據(jù)庫(kù)查詢次數(shù)抑钟。緩存這個(gè)東西，一定要分析自己系統(tǒng)的數(shù)據(jù)特點(diǎn)野哭，適當(dāng)選擇在塔。
- 對(duì)于一些常用的數(shù)據(jù)，比如配置信息等拨黔，可以放在緩存中
- 可以在本地緩存數(shù)據(jù)庫(kù)的表結(jié)構(gòu)
- 緩存的數(shù)據(jù)一定要注意及時(shí)更新糖权，還有設(shè)置有效期
- 增加緩存務(wù)必會(huì)增加系統(tǒng)復(fù)雜性搁拙，一定要注意權(quán)衡
優(yōu)化實(shí)例
下面舉幾個(gè)簡(jiǎn)單的優(yōu)化查詢例子筐喳。首先就是跑一下主要業(yè)務(wù)岭妖，把主要的查詢語(yǔ)句打印到一個(gè)文件里面，然后分析這些語(yǔ)句零截。
補(bǔ)充一下麸塞，在查詢語(yǔ)句前使用關(guān)鍵字explain可以查看查詢執(zhí)行的具體情況。
看下面的這個(gè)查詢語(yǔ)句
select *
from link
where player_id='15298635' AND gameid='10389' AND appid='200'
AND action='open' AND creator='android_sdk' AND transport='{"name":"uusama","age":20}'
上面這條語(yǔ)句毛病挺多的
- select * 沒(méi)有指定查詢列瞻润，這個(gè)表有20個(gè)字段喘垂，其實(shí)我用到的就幾個(gè)
- 查詢列沒(méi)有索引，造成全表掃描
- 查詢條件過(guò)于冗余绍撞，可以適當(dāng)拆分
- 只需要一條查詢結(jié)果正勒，但是沒(méi)有限定查詢結(jié)果大小
顯然查詢條件很多，而且很多列都是不定長(zhǎng)的varchar類型傻铣，如果要建立索引章贞，是不是要建立聯(lián)合索引呢？
顯然沒(méi)有必要非洲，索引的字段越多鸭限，MySQL維護(hù)的時(shí)候越復(fù)雜，對(duì)性能也會(huì)有損耗两踏，像這樣的SQL查詢語(yǔ)句败京，我們?cè)谥饕侄紊辖⑺饕纯伞１热缭?code>player_id字段梦染、gameid字段赡麦、appid字段上建立索引就夠了朴皆。
這樣的查詢語(yǔ)句要結(jié)合具體的業(yè)務(wù)場(chǎng)景來(lái)進(jìn)行分析，比如在我當(dāng)前的系統(tǒng)中泛粹，我是期望上面的語(yǔ)句能夠查詢相同的參數(shù)下是否有記錄遂铡。其實(shí)沒(méi)必要使用這么多條件的查詢。
我只需要使用下面的這條更簡(jiǎn)單的查詢語(yǔ)句代替即可晶姊。
select id,player_id
from link
where player_id='15298635'
查詢到的記錄條數(shù)在100條以下扒接，大部分就只用幾十條記錄，我完全可以在代碼里面在把查詢結(jié)果遍歷一遍判斷即可们衙。這樣不知道有多快呢钾怔！
再看下面的這個(gè)例子：
select *
from browser
where device_id='52' AND created>='1513735322' order by id desc
我只是想查一下這個(gè)表里面某個(gè)時(shí)間以后的數(shù)據(jù)。問(wèn)題大了蒙挑！
created字段是timestamp類型蒂教，這樣用是不對(duì)的，而且沒(méi)有限定行數(shù)脆荷，這條語(yǔ)句會(huì)把數(shù)據(jù)庫(kù)所有的device_id='52'的數(shù)據(jù)搞出來(lái)。
還好device_id字段設(shè)置了索引懊悯，要不然必然會(huì)導(dǎo)致全表掃描蜓谋。
修改后的查詢?nèi)缦拢?/p>
select *
from browser
where device_id='52' AND created>='2018-03-27 00:00:00' order by id desc
我的系統(tǒng)總沒(méi)有使用復(fù)雜的像表連接和聯(lián)合這樣的查詢，這類查詢一定要謹(jǐn)慎使用炭分，能夠拆分的話盡量拆分桃焕。
記住下面的速度優(yōu)先級(jí)，兩兩之間相差2個(gè)以上數(shù)量級(jí)
CPU運(yùn)行速度 > 內(nèi)存訪問(wèn)速度 > 磁盤io訪問(wèn)速度 > 網(wǎng)絡(luò)請(qǐng)求速度
已同步到個(gè)人博客
