SQL慢查詢優(yōu)化方案詳解
sql排查方式
首先,如何排查sql是否走了索引,通過explain關(guān)鍵字找出sql的執(zhí)行計(jì)劃,通過在對執(zhí)行計(jì)劃的分析,找出某表關(guān)聯(lián)部分是否走了索引。
具體語法:
explain select * from table_name;
讓不走索引的sql走索引
mysql有個(gè)特別神奇的東西叫做索引,一個(gè)sql走或不走索引,查詢效率差別極大,因此大部分的查詢效率問題都是不走索引造成的,也就意味著,只要找到不走索引的原因,就能解決大部分查詢效率的問題。
因此專門開了一個(gè)文章羅列不走索引的情況,歡迎大家補(bǔ)充或指正。 不走索引的情況及一些規(guī)避方案
聯(lián)合索引,避免回表
眾所周知,聯(lián)合索引的葉子節(jié)點(diǎn)存儲(chǔ)了索引字段的值,因此,合理的設(shè)計(jì)聯(lián)合索引,可以避免回表操作。
比如:我的表中有四十個(gè)字段,但是查詢頻率比較高的只有a,b,c三個(gè)字段,這時(shí)候,如果我想進(jìn)一步提升效率,我可以將abc三個(gè)字段設(shè)計(jì)一個(gè)聯(lián)合索引,查詢的速度就會(huì)有進(jìn)一步提升。
特殊優(yōu)化,深度分頁
深度分頁: 指的是在分頁查詢的時(shí)候,頁數(shù)標(biāo)記的特別高(如查詢第10000頁的數(shù)據(jù)),這樣,如果正常查詢的sql會(huì)出現(xiàn) select * from table_name limit 10000,10
的sql出現(xiàn),會(huì)造成極大的性能問題。
因此深度分頁問題需要有一定的優(yōu)化。
- 方案一:從業(yè)務(wù)方面搞定,因?yàn)楹芏鄻I(yè)務(wù)根本用不到那么多分頁,那么就可以限制最大分頁的頁數(shù),比如百度的最大顯示頁碼就只有76頁。
業(yè)務(wù)方面如果搞不定,只能通過技術(shù)手段搞定,那么我們就分析一下問題出在哪?
我們能夠想到,性能瓶頸出在了limit頁碼過大的問題上,我們知道,索引的結(jié)構(gòu)(B+樹)有一個(gè)特性,就是葉子節(jié)點(diǎn)之間依靠雙向鏈表連接,這個(gè)特性主要是針對范圍查詢做的優(yōu)化,因此在進(jìn)行分頁查詢的時(shí)候,我們會(huì)直接通過鏈表進(jìn)行查詢,問題就出在了這里,由于頁碼過大,而且查詢字段過多,每次查詢時(shí)候需要回表,所以鏈表在遍歷的時(shí)候時(shí)間過長,造成了性能瓶頸。因此引申出了下面兩個(gè)優(yōu)化方案。
- 方案二:分頁查詢時(shí)將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;
突破瓶頸,分庫分表
分庫
每個(gè)數(shù)據(jù)庫是有一個(gè)最大連接數(shù)的,超過這個(gè)連接數(shù)會(huì)查詢超時(shí),所以,在你的項(xiàng)目的并發(fā)量超過一定量級之后,你就需要考慮分庫了,至于具體分多少個(gè)庫,看實(shí)際QPS量有多少,一個(gè)庫默認(rèn)的連接數(shù)是100,按照具體QPS推算需要分出多少個(gè)庫。
分表
分表又分為水平分表和垂直分表。
水平分表: 是將一張表的字段拆分到兩張表上,從而達(dá)到單表數(shù)據(jù)存儲(chǔ)量降低的目的。一般應(yīng)用的場景都是按照業(yè)務(wù)含義拆分,可以拆分成多張一對一的表,各個(gè)表通過主鍵關(guān)聯(lián),比如將sku表拆分成sku基礎(chǔ)信息表,sku擴(kuò)展表,sku狀態(tài)表,等等。
垂直分表: 是單表數(shù)據(jù)過多后,會(huì)導(dǎo)致數(shù)據(jù)查詢時(shí)候讀盤次數(shù)增加,從而查詢效率降低,這時(shí)候就考慮將數(shù)據(jù)分到多張表上,通常做法是確定一個(gè)拆分鍵,將拆分鍵進(jìn)行一定的算法(比如:hash運(yùn)算后按照分表數(shù)取模),最終落到某張表中。
基本上單表量級超過兩千萬就可以考慮分表了,分多少表按照 最大量級除以500-1000萬 左右數(shù)據(jù)計(jì)算。
至于為什么最大閾值是兩千萬,可以看下 mysql B+樹 索引機(jī)制 及 InnoDB一棵B+樹可以存放多少行數(shù)據(jù)? 深入了解一下。
削峰操作,臨時(shí)緩存
有一種慢查詢的情況是出在了一種峰值查詢的時(shí)候,就是有時(shí)候會(huì)出現(xiàn)某個(gè)時(shí)間段有些業(yè)務(wù)幾種出,但是定時(shí)任務(wù)過于集中,導(dǎo)致某一個(gè)短暫的時(shí)間段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)定時(shí)任務(wù)批量處理數(shù)據(jù)的情況,但是定時(shí)任務(wù)過于集中,導(dǎo)致某一個(gè)短暫的時(shí)間段QPS暴增,這時(shí)候更好的方案是通過mq異步處理數(shù)據(jù),通過控制mq的消費(fèi)密度來減輕qps,從而達(dá)到減輕數(shù)據(jù)庫的壓力,以時(shí)間換空間,以慢打快,效果奇好。
更換思路,曲線救國
如果是在查詢效率提升不上去,就考慮更換查詢中間件吧,使用大數(shù)據(jù)的處理方式,比如,落ElasticSearch查詢。
到此這篇關(guān)于SQL慢查詢優(yōu)化方案詳解的文章就介紹到這了,更多相關(guān)SQL慢查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL綠色版設(shè)置編碼以及1067錯(cuò)誤詳解
這篇文章主要介紹了MySQL綠色版設(shè)置編碼,以及1067錯(cuò)誤的相關(guān)資料,需要的朋友可以參考下2017-05-05Mysql8.4.3LTS中離線部署的實(shí)現(xiàn)示例
本文詳細(xì)介紹了在Ubuntu 24.04系統(tǒng)上離線部署MySQL 8.4.3 LTS,包括環(huán)境配置、安裝步驟等步驟,對小白有一定的幫助,感興趣的可以了解一下2024-11-11淺談mysql中concat函數(shù),mysql在字段前/后增加字符串
下面小編就為大家?guī)硪黄獪\談mysql中concat函數(shù),mysql在字段前/后增加字符串。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-02-02