sql排查方式

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

具體語(yǔ)法：

explain select * from table_name;

讓不走索引的sql走索引

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

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

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

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

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

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

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

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

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

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

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

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

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

突破瓶頸，分庫(kù)分表

分庫(kù)

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

分表

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

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

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

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

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

削峰操作，臨時(shí)緩存

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

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

以慢打快，異步處理

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

更換思路，曲線(xiàn)救國(guó)

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

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

最新評(píng)論