sql排查方式

首先，如何排查sql是否走了索引，通過explain關(guān)鍵字找出sql的執(zhí)行計劃，通過在對執(zhí)行計劃的分析，找出某表關(guān)聯(lián)部分是否走了索引。

具體語法：

explain select * from table_name;

讓不走索引的sql走索引

mysql有個特別神奇的東西叫做索引，一個sql走或不走索引，查詢效率差別極大，因此大部分的查詢效率問題都是不走索引造成的，也就意味著，只要找到不走索引的原因，就能解決大部分查詢效率的問題。

因此專門開了一個文章羅列不走索引的情況，歡迎大家補(bǔ)充或指正。 不走索引的情況及一些規(guī)避方案

聯(lián)合索引，避免回表

眾所周知，聯(lián)合索引的葉子節(jié)點(diǎn)存儲了索引字段的值，因此，合理的設(shè)計聯(lián)合索引，可以避免回表操作。

比如：我的表中有四十個字段，但是查詢頻率比較高的只有a，b，c三個字段，這時候，如果我想進(jìn)一步提升效率，我可以將abc三個字段設(shè)計一個聯(lián)合索引，查詢的速度就會有進(jìn)一步提升。

特殊優(yōu)化，深度分頁

深度分頁： 指的是在分頁查詢的時候，頁數(shù)標(biāo)記的特別高（如查詢第10000頁的數(shù)據(jù)），這樣，如果正常查詢的sql會出現(xiàn) select * from table_name limit 10000,10 的sql出現(xiàn)，會造成極大的性能問題。

因此深度分頁問題需要有一定的優(yōu)化。

• 方案一：從業(yè)務(wù)方面搞定，因?yàn)楹芏鄻I(yè)務(wù)根本用不到那么多分頁，那么就可以限制最大分頁的頁數(shù)，比如百度的最大顯示頁碼就只有76頁。

業(yè)務(wù)方面如果搞不定，只能通過技術(shù)手段搞定，那么我們就分析一下問題出在哪？

我們能夠想到，性能瓶頸出在了limit頁碼過大的問題上，我們知道，索引的結(jié)構(gòu)（B+樹）有一個特性，就是葉子節(jié)點(diǎn)之間依靠雙向鏈表連接，這個特性主要是針對范圍查詢做的優(yōu)化，因此在進(jìn)行分頁查詢的時候，我們會直接通過鏈表進(jìn)行查詢，問題就出在了這里，由于頁碼過大，而且查詢字段過多，每次查詢時候需要回表，所以鏈表在遍歷的時候時間過長，造成了性能瓶頸。因此引申出了下面兩個優(yōu)化方案。

• 方案二：分頁查詢時將id作為查詢條件傳過來，然后往后查十條，這樣可以先通過索引鎖定到第一條數(shù)據(jù)，然后通過鏈表往后遍歷十條數(shù)據(jù)，從而達(dá)到優(yōu)化的效果。 如果你的已有的方案不是那么方便改，可以考慮下邊的方案。
• 方案三：在sql中先分頁查詢到id（不需回表查詢速度較快），然后在進(jìn)行表關(guān)聯(lián)進(jìn)行分頁查詢，具體sql如下（類似）：

select table.* from table_name as table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;

突破瓶頸，分庫分表

分庫

每個數(shù)據(jù)庫是有一個最大連接數(shù)的，超過這個連接數(shù)會查詢超時，所以，在你的項(xiàng)目的并發(fā)量超過一定量級之后，你就需要考慮分庫了，至于具體分多少個庫，看實(shí)際QPS量有多少，一個庫默認(rèn)的連接數(shù)是100，按照具體QPS推算需要分出多少個庫。

分表

分表又分為水平分表和垂直分表。

水平分表： 是將一張表的字段拆分到兩張表上，從而達(dá)到單表數(shù)據(jù)存儲量降低的目的。一般應(yīng)用的場景都是按照業(yè)務(wù)含義拆分，可以拆分成多張一對一的表，各個表通過主鍵關(guān)聯(lián)，比如將sku表拆分成sku基礎(chǔ)信息表，sku擴(kuò)展表，sku狀態(tài)表，等等。

垂直分表： 是單表數(shù)據(jù)過多后，會導(dǎo)致數(shù)據(jù)查詢時候讀盤次數(shù)增加，從而查詢效率降低，這時候就考慮將數(shù)據(jù)分到多張表上，通常做法是確定一個拆分鍵，將拆分鍵進(jìn)行一定的算法（比如：hash運(yùn)算后按照分表數(shù)取模），最終落到某張表中。

基本上單表量級超過兩千萬就可以考慮分表了，分多少表按照 最大量級除以500-1000萬 左右數(shù)據(jù)計算。

至于為什么最大閾值是兩千萬，可以看下 mysql B+樹 索引機(jī)制 及 InnoDB一棵B+樹可以存放多少行數(shù)據(jù)？ 深入了解一下。

削峰操作，臨時緩存

有一種慢查詢的情況是出在了一種峰值查詢的時候，就是有時候會出現(xiàn)某個時間段有些業(yè)務(wù)幾種出，但是定時任務(wù)過于集中，導(dǎo)致某一個短暫的時間段QPS暴增，從而數(shù)據(jù)庫扛不住壓力，出現(xiàn)了慢查詢甚至于數(shù)據(jù)庫崩潰，由于是峰值的問題，擴(kuò)容機(jī)器又很浪費(fèi)，不擴(kuò)容又扛不住壓力，為了避免這類問題，可以考慮引入緩存機(jī)制進(jìn)行削峰操作，通過緩存機(jī)制短暫的保存數(shù)據(jù)庫信息，讓峰值的請求都壓在緩存上，從而間接的避免了慢查詢的情況。

當(dāng)然，無論是使用緩存中間件Redis，或者內(nèi)存中間件MemoryCache都可以解決這類問題。

以慢打快，異步處理

同樣是慢查詢出現(xiàn)定時任務(wù)批量處理數(shù)據(jù)的情況，但是定時任務(wù)過于集中，導(dǎo)致某一個短暫的時間段QPS暴增，這時候更好的方案是通過mq異步處理數(shù)據(jù)，通過控制mq的消費(fèi)密度來減輕qps，從而達(dá)到減輕數(shù)據(jù)庫的壓力，以時間換空間，以慢打快，效果奇好。

更換思路，曲線救國

如果是在查詢效率提升不上去，就考慮更換查詢中間件吧，使用大數(shù)據(jù)的處理方式，比如，落ElasticSearch查詢。

到此這篇關(guān)于SQL慢查詢優(yōu)化方案詳解的文章就介紹到這了,更多相關(guān)SQL慢查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論