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

歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!歡迎閱讀本文檔，希望本文檔能對您有所幫助!mysql查詢優(yōu)化教程實(shí)戰(zhàn)發(fā)現(xià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ù)庫監(jiān)控日志...運(yùn)維大佬又?jǐn)?shù)落了我?guī)拙?，然后調(diào)開了數(shù)據(jù)庫監(jiān)控日志。那家伙...每秒300多的連接數(shù)，幾乎快要封頂?shù)娜頀呙钄?shù)，還有大紅色CPU警報(bào)。。。那個(gè)，能不能看看nginx訪問日志...我看下訪問量...我弱弱地說到。運(yùn)維大佬不情愿的跑了下下面的語句：grep-ccomeaccess.logcome這個(gè)接口是其中一個(gè)請求量比較大的接口，結(jié)果是600多萬。那個(gè)時(shí)候才中午，周末高峰期估計(jì)一天得有上千萬吧，我撇了撇嘴，心里想著這么高的請求量，當(dāng)初那么摳門只給我一臺(tái)低配數(shù)據(jù)庫還好意思說，不過嘴上肯定是：好好好，請求量不是很大，看來是數(shù)據(jù)庫問題，我立刻去優(yōu)化一下！給它弄一個(gè)讀寫分離不就行了嗎?。窟@時(shí)另外一個(gè)運(yùn)維大佬湊了過來，隨意地?fù)]了揮手。。。你問我DBA去哪兒了？DBA當(dāng)時(shí)有點(diǎn)忙，只說讓我自己檢查一下。。。優(yōu)化思路我這個(gè)項(xiàng)目由于上線之前比較趕，所以前期并沒有管數(shù)據(jù)庫設(shè)計(jì)方面的一些問題，如今隨著游戲接入，請求量劇增才暴露出來。（其實(shí)是前期加班加煩了懶得搞）這個(gè)問題，并不需要增加數(shù)據(jù)庫硬件配置和增加讀寫分離這種高端手段就能解決，我自個(gè)兒挖了多少坑，心里還是有點(diǎn)碧樹的。詳細(xì)的MySQL優(yōu)化步驟如下：檢查數(shù)據(jù)表結(jié)構(gòu)，改善不完善設(shè)計(jì)跑一遍主要業(yè)務(wù)，收集常用的數(shù)據(jù)庫查詢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í)候，如果不對創(chuàng)建的值設(shè)置默認(rèn)值，MySQL都會(huì)設(shè)置默認(rèn)為NULL。那么為啥用NULL不好呢？NULL使得索引維護(hù)更加復(fù)雜，強(qiáng)烈建議對索引列設(shè)置NOTNULLNOTIN、!=等負(fù)向條件查詢在有NULL值的情況下返回永遠(yuǎn)為空結(jié)果，查詢?nèi)菀壮鲥e(cuò)NULL列需要一個(gè)額外字節(jié)作為判斷是否為NULL的標(biāo)志位使用NULL時(shí)和該列其他的值可能不是同種類型，導(dǎo)致問題。（在不同的語言中表現(xiàn)不一樣）MySQL難以優(yōu)化對可為NULL的列的查詢所以對于那些以前偷懶的字段，手動(dòng)設(shè)置一個(gè)默認(rèn)值吧，空字符串呀，0呀補(bǔ)上。雖然這種方法對于MySQL的性能來說沒有提升多少，但是這是一個(gè)好習(xí)慣，而且以小見大，不要忽略這些細(xì)節(jié)。添加索引對于經(jīng)常查詢的字段，請加上索引，有索引和沒有索引的查詢速度相差十倍甚至更多。一般來說，每張表都需要有一個(gè)主鍵id字段常用于查詢的字段應(yīng)該設(shè)置索引varchar類型的字段，在建立索引的時(shí)候，最好指定長度查詢有多個(gè)條件時(shí)，優(yōu)先使用具有索引的條件像LIKE條件這樣的模糊搜索對于字段索引是無效的，需要另外建立關(guān)鍵詞索引來解決請盡量不要在數(shù)據(jù)庫層面約束表和表之間的關(guān)系，這些表之間的依賴應(yīng)該在代碼層面去解決當(dāng)表和表之間有約束時(shí)，雖然增刪查的SQL語句變簡單了，但是帶來的負(fù)面效果是插入等操作數(shù)據(jù)庫都會(huì)去檢查約束（雖然可以手動(dòng)設(shè)置忽略約束），這樣相當(dāng)于把一些業(yè)務(wù)邏輯寫到了數(shù)據(jù)庫層，不便于維護(hù)。優(yōu)化表字段結(jié)構(gòu)數(shù)據(jù)庫中那些可以用整形表示的數(shù)據(jù)就不要使用字符串類型，到底是用varchar還是char要看字段的可能值。這種優(yōu)化往往在數(shù)據(jù)庫中有大量數(shù)據(jù)以后是不可行的，最好在數(shù)據(jù)庫設(shè)計(jì)之前就設(shè)計(jì)好。對于那些可能值很有限的列，使用tinyint代替VARCHAR，比如記錄移動(dòng)設(shè)備平臺(tái)，只有兩個(gè)值：android，ios，那么就可以使用0表示android，1表示ios，這種列一定要寫好注釋為什么不用ENUM呢？ENUM擴(kuò)展困難，比如后來移動(dòng)平臺(tái)又增加了一個(gè)ipad，那豈不是懵逼了，而tinyint加個(gè)2就行，而且ENUM在代碼里面處理起來特別奇怪，是當(dāng)成整形呢還是字符串，各個(gè)語言不一樣。這種方式，一定要在數(shù)據(jù)庫注釋或者代碼里面寫明各個(gè)值的含義對于那些定長字符串，可以使用char，比如郵編，總是5位對于那些長度未知的字符串，使用varchar不要濫用bigint，比如記錄文章數(shù)目的表id字段，用int就行了，21億篇文章上限夠了適當(dāng)打破數(shù)據(jù)庫范式添加冗余字段，避免查詢時(shí)的表連接查詢的時(shí)候，肯定int類型比varchar快，因?yàn)檎麛?shù)的比較直接調(diào)用底層運(yùn)算器就可以實(shí)現(xiàn)，而字符串比較要逐個(gè)字符比較。定長數(shù)據(jù)比變長數(shù)據(jù)查詢快，因?yàn)楸容^定長數(shù)據(jù)與數(shù)據(jù)之間的偏移是固定的，很容易計(jì)算下一個(gè)數(shù)據(jù)的偏移。而變長數(shù)據(jù)則還需要多一步去查詢下一個(gè)數(shù)據(jù)的偏移量。不過。定長數(shù)據(jù)可能會(huì)浪費(fèi)更多的存儲(chǔ)空間。大表拆分對于那些數(shù)據(jù)量可能近期會(huì)超過500W或者增長很快的表，一定要提前做好垂直分表或者水平分表，當(dāng)數(shù)據(jù)量超過百萬以后，查詢速度會(huì)明顯下降。分庫分表盡量在數(shù)據(jù)庫設(shè)計(jì)初期敲定方案，否則后期會(huì)極大增加代碼復(fù)雜性而且不易更改。垂直分表是按照日期等外部變量進(jìn)行分表，水平分表是按照表中的某些字段關(guān)系，使用hash映射等分表。分庫分表的前提條件是在執(zhí)行查詢語句之前，已經(jīng)知道需要查詢的數(shù)據(jù)可能會(huì)落在哪一個(gè)分庫和哪一個(gè)分表中。優(yōu)化查詢語句這個(gè)才是很多系統(tǒng)數(shù)據(jù)庫瓶頸的始作俑者。請盡量使用簡單的查詢，避免使用表鏈接請盡量避免全表掃描，包括但不限于：where子句條件橫真或?yàn)榭帐褂肔IKE使用不等操作符（lt;、!=）查詢含義isnull的列在非索引列上使用or多條件查詢時(shí)，請把簡單查詢條件或則索引列查詢置于前面請盡量指定需要查詢的列，不要偷懶使用select*如果不指定，一方面會(huì)返回多余的數(shù)據(jù)，占用寬帶等另一方面MySQL執(zhí)行查詢的時(shí)候，沒有字段時(shí)會(huì)先去查詢表結(jié)構(gòu)有哪些字段大些的查詢關(guān)鍵字比小寫快一點(diǎn)點(diǎn)使用子查詢會(huì)創(chuàng)建臨時(shí)表，會(huì)比鏈接（JOIN）和聯(lián)合（UNION）稍慢在索引字段上查詢盡量不要使用數(shù)據(jù)庫函數(shù)，不便于緩存查詢結(jié)果當(dāng)只要一行數(shù)據(jù)時(shí)，請使用LIMIT1，如果數(shù)據(jù)過多，請適當(dāng)設(shè)定LIMIT，分頁查詢千萬不要ORDERBYRAND()，性能極低上面是我總結(jié)的一些小tips，這些規(guī)則是死的，但是業(yè)務(wù)場景是活的，在實(shí)際使用的過程中，比如數(shù)據(jù)統(tǒng)計(jì)，可以適當(dāng)犧牲性能換取便利。添加緩存使用redis等緩存，還有本地文件緩存等，可以極大地減少數(shù)據(jù)庫查詢次數(shù)。緩存這個(gè)東西，一定要分析自己系統(tǒng)的數(shù)據(jù)特點(diǎn)，適當(dāng)選擇。對于一些常用的數(shù)據(jù)，比如配置信息等，可以放在緩存中可以在本地緩存數(shù)據(jù)庫的表結(jié)構(gòu)緩存的數(shù)據(jù)一定要注意及時(shí)更新，還有設(shè)置有效期增加緩存務(wù)必會(huì)增加系統(tǒng)復(fù)雜性，一定要注意權(quán)衡優(yōu)化實(shí)例下面舉幾個(gè)簡單的優(yōu)化查詢例子。首先就是跑一下主要業(yè)務(wù)，把主要的查詢語句打印到一個(gè)文件里面，然后分析這些語句。補(bǔ)充一下，在查詢語句前使用關(guān)鍵字explain可以查看查詢執(zhí)行的具體情況?？聪旅娴倪@個(gè)查詢語句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;上面這條語句毛病挺多的select*沒有指定查詢列，這個(gè)表有20個(gè)字段，其實(shí)我用到的就幾個(gè)查詢列沒有索引，造成全表掃描查詢條件過于冗余，可以適當(dāng)拆分只需要一條查詢結(jié)果，但是沒有限定查詢結(jié)果大小顯然查詢條件很多，而且很多列都是不定長的varchar類型，如果要建立索引，是不是要建立聯(lián)合索引呢？顯然沒有必要，索引的字段越多，MySQL維護(hù)的時(shí)候越復(fù)雜，對性能也會(huì)有損耗，像這樣的SQL查詢語句，我們在主要字段上建立索引即可。比如在player_id字段、gameid字段、appid字段上建立索引就夠了。這樣的查詢語句要結(jié)合具體的業(yè)務(wù)場景來進(jìn)行分析，比如在我當(dāng)前的系統(tǒng)中，我是期望上面的語句能夠查詢相同的參數(shù)下是否有記錄。其實(shí)沒必要使用這么多條件的查詢。我只需要使用下面的這條更簡單的查詢語句代替即可。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ù)。問題大了！created字段是timestamp類型，這樣用是不對的，而且沒有限定行數(shù)，這條語句會(huì)把數(shù)據(jù)庫所有的device_id=#39;52#39;的數(shù)據(jù)搞出來。還好device_id字段設(shè)置了索引，要不然必然會(huì)導(dǎo)致全表掃描