mysql查詢優(yōu)化教程實(shí)戰(zhàn)發(fā)現(xiàn)問(wèn)題+解決問(wèn)題步驟

歡迎閱讀本文檔，希望本文檔能對(duì)您有所幫助!歡迎閱讀本文檔，希望本文檔能對(duì)您有所幫助!歡迎閱讀本文檔，希望本文檔能對(duì)您有所幫助!歡迎閱讀本文檔，希望本文檔能對(duì)您有所幫助!歡迎閱讀本文檔，希望本文檔能對(duì)您有所幫助!歡迎閱讀本文檔，希望本文檔能對(duì)您有所幫助!mysql查詢優(yōu)化教程實(shí)戰(zhàn)發(fā)現(xiàn)問(wèn)題+解決問(wèn)題步驟背景那啥，你過(guò)來(lái)一下！怎么了，我代碼都單元測(cè)試了的，沒(méi)出問(wèn)題?。∥乙荒樸卤婆艿竭\(yùn)維大佬旁邊。你看看！你看看！多少條報(bào)警，趕快優(yōu)化一下！運(yùn)維大佬短信列表里面好多MySQLCPU100%報(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-ccomeaccess.logcome這個(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è)置NOTNULLNOTIN、!=等負(fù)向條件查詢?cè)谟蠳ULL值的情況下返回永遠(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ù)與數(shù)據(jù)之間的偏移是固定的，很容易計(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)榭帐褂肔IKE使用不等操作符（lt;、!=）查詢含義isnull的列在非索引列上使用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)使用LIMIT1，如果數(shù)據(jù)過(guò)多，請(qǐng)適當(dāng)設(shè)定LIMIT，分頁(yè)查詢千萬(wàn)不要ORDERBYRAND()，性能極低上面是我總結(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*fromlinkwhereplayer_id=#39;15298635#39;ANDgameid=#39;10389#39;ANDappid=#39;200#39;ANDaction=#39;open#39;ANDcreator=#39;android_sdk#39;ANDtransport=#39;{name:uusama,age:20}#39;上面這條語(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è)谥饕侄紊辖⑺饕纯?。比如在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ǔ)句代替即可。selectid,player_idfromlinkwhereplayer_id=#39;15298635#39;查詢到的記錄條數(shù)在100條以下，大部分就只用幾十條記錄，我完全可以在代碼里面在把查詢結(jié)果遍歷一遍判斷即可。這樣不知道有多快呢！再看下面的這個(gè)例子：select*frombrowserwheredevice_id=#39;52#39;ANDcreated=#39;1513735322#39;orderbyiddesc我只是想查一下這個(gè)表里面某個(gè)時(shí)間以后的數(shù)據(jù)。問(wèn)題大了！created字段是timestamp類型，這樣用是不對(duì)的，而且沒(méi)有限定行數(shù)，這條語(yǔ)句會(huì)把數(shù)據(jù)庫(kù)所有的device_id=#39;52#39;的數(shù)據(jù)搞出來(lái)。還好device_id字段設(shè)置了索引，要不然必然會(huì)導(dǎo)致全表掃描