《Mysql應(yīng)用Mysql性能優(yōu)化案例研究-覆蓋索引和SQL_NO_CACHE》要點(diǎn)：
本文介紹了Mysql應(yīng)用Mysql性能優(yōu)化案例研究-覆蓋索引和SQL_NO_CACHE，希望對(duì)您有用。如果有疑問(wèn)，可以聯(lián)系我們。

場(chǎng)景MYSQL學(xué)習(xí)

產(chǎn)品中有一張圖片表pics,數(shù)據(jù)量將近100萬(wàn)條,有一條相關(guān)的查詢(xún)語(yǔ)句,由于執(zhí)行頻次較高,想針對(duì)此語(yǔ)句進(jìn)行優(yōu)化MYSQL學(xué)習(xí)

表結(jié)構(gòu)很簡(jiǎn)單,主要字段：MYSQL學(xué)習(xí)

一個(gè)用戶(hù)會(huì)有多條圖片記錄,現(xiàn)在有一個(gè)根據(jù)user_id建立的索引：uid,查詢(xún)語(yǔ)句也很簡(jiǎn)單：取得某用戶(hù)的圖片集合：
MYSQL學(xué)習(xí)

優(yōu)化前

執(zhí)行查詢(xún)語(yǔ)句(為了查看真實(shí)執(zhí)行時(shí)間,強(qiáng)制不使用緩存,為了防止在測(cè)試時(shí)因?yàn)樽x取了緩存造成對(duì)時(shí)間上的差別)
MYSQL學(xué)習(xí)

執(zhí)行了10次,平均耗時(shí)在40ms左右

使用explain進(jìn)行分析：
MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

使用了user_id的索引,并且是const常數(shù)查找,表示性能已經(jīng)很好了MYSQL學(xué)習(xí)

優(yōu)化后MYSQL學(xué)習(xí)

因?yàn)檫@個(gè)語(yǔ)句太簡(jiǎn)單,sql自己沒(méi)有什么優(yōu)化空間,就考慮了索引MYSQL學(xué)習(xí)

修改索引結(jié)構(gòu),建立一個(gè)(user_id,picname,smallimg)的聯(lián)合索引：uid_picMYSQL學(xué)習(xí)

重新執(zhí)行10次,平均耗時(shí)降到了30ms左右MYSQL學(xué)習(xí)

使用explain進(jìn)行分析MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

看到使用的索引變成了剛剛建立的聯(lián)合索引,并且Extra部分顯示使用了'Using Index'MYSQL學(xué)習(xí)

總結(jié)MYSQL學(xué)習(xí)

‘Using Index'的意思是“覆蓋索引”,它是使上面sql性能提升的關(guān)鍵MYSQL學(xué)習(xí)

一個(gè)包括查詢(xún)所需字段的索引稱(chēng)為“覆蓋索引”MYSQL學(xué)習(xí)

MySQL只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù),而不必在查到索引之后進(jìn)行回表操作,減少I(mǎi)O,提高了效率MYSQL學(xué)習(xí)

例如上面的sql,查詢(xún)條件是user_id,可以使用聯(lián)合索引,要查詢(xún)的字段是picname smallimg,這兩個(gè)字段也在聯(lián)合索引中,這就實(shí)現(xiàn)了“覆蓋索引”,可以根據(jù)這個(gè)聯(lián)合索引一次性完成查詢(xún)工作,所以提升了性能.MYSQL學(xué)習(xí)

擴(kuò)展研究MYSQL學(xué)習(xí)

一、Mysql緩存,SQL_NO_CACHE和SQL_CACHE 的區(qū)別MYSQL學(xué)習(xí)

上邊在進(jìn)行測(cè)試的時(shí)候,為了防止讀取緩存造成對(duì)實(shí)驗(yàn)結(jié)果的影響使用到了SQL_NO_CACHE這個(gè)功能,對(duì)于SQL_NO_CACHE的介紹官網(wǎng)如下：MYSQL學(xué)習(xí)

當(dāng)我們想用SQL_NO_CACHE來(lái)禁止結(jié)果緩存時(shí)發(fā)現(xiàn)結(jié)果和我們的預(yù)期不一樣,查詢(xún)執(zhí)行的結(jié)果仍然是緩存后的結(jié)果.其實(shí),SQL_NO_CACHE的真正作用是禁止緩存查詢(xún)結(jié)果,但并不意味著cache不作為結(jié)果返回給query.MYSQL學(xué)習(xí)

在說(shuō)白點(diǎn)就是,不是本次查詢(xún)不使用緩存,而是本次查詢(xún)結(jié)果不做為下次查詢(xún)的緩存.MYSQL學(xué)習(xí)

還有就是,mysql本身是有對(duì)sql語(yǔ)句緩存的機(jī)制的,合理設(shè)置我們的mysql緩存可以降低數(shù)據(jù)庫(kù)的io資源,因此,這里我們有必要再看一下如何控制這個(gè)比較安適的功能.MYSQL學(xué)習(xí)

看圖如下：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

其中各項(xiàng)的含義為：MYSQL學(xué)習(xí)

1、have_query_cache
是否支持查詢(xún)緩存區(qū) “YES”表是支持查詢(xún)緩存區(qū)MYSQL學(xué)習(xí)

2、query_cache_limit
可緩存的Select查詢(xún)結(jié)果的最大值 1048576 byte /1024 = 1024kB 即最大可緩存的select查詢(xún)結(jié)果必須小于 1024KBMYSQL學(xué)習(xí)

3、query_cache_min_res_unit
每次給query cache結(jié)果分配內(nèi)存的大小 默認(rèn)是 4096 byte 也即 4kBMYSQL學(xué)習(xí)

4、query_cache_size
如果你希望禁用查詢(xún)緩存,設(shè)置 query_cache_size=0.禁用了查詢(xún)緩存,將沒(méi)有明顯的開(kāi)銷(xiāo)MYSQL學(xué)習(xí)

5、query_cache_type
查詢(xún)緩存的方式(默認(rèn)是 ON)MYSQL學(xué)習(xí)

1、完整查詢(xún)的過(guò)程如下MYSQL學(xué)習(xí)

當(dāng)查詢(xún)進(jìn)行的時(shí)候,Mysql把查詢(xún)結(jié)果保留在qurey cache中,但是有時(shí)候要保留的結(jié)果比較大,超過(guò)了query_cache_min_res_unit的值 ,這時(shí)候mysql將一邊檢索結(jié)果,一邊進(jìn)行慢慢保留結(jié)果,所以,有時(shí)候并不是把所有結(jié)果全部得到后再進(jìn)行一次性保留,而是每次分配一塊query_cache_min_res_unit 大小的內(nèi)存空間保留結(jié)果集,使用完后,接著再分配一個(gè)這樣的塊,如果還不不夠,接著再分配一個(gè)塊,依此類(lèi)推,也就是說(shuō),有可能在一次查詢(xún)中,mysql要進(jìn)行多次內(nèi)存分配的操作,而我們應(yīng)該知道,頻繁操作內(nèi)存都是要耗費(fèi)時(shí)間的.MYSQL學(xué)習(xí)

2、內(nèi)存碎片的產(chǎn)生MYSQL學(xué)習(xí)

當(dāng)一塊分配的內(nèi)存沒(méi)有完全使用時(shí),MySQL會(huì)把這塊內(nèi)存Trim掉,把沒(méi)有使用的那部分歸還以重復(fù)利用.比如,第一次分配4KB,只用了3KB,剩1KB,第二次連續(xù)操作,分配4KB,用了2KB,剩2KB,這兩次連續(xù)操作共剩下的1KB+2KB=3KB,不足以做個(gè)一個(gè)內(nèi)存單元分配,這時(shí)候,內(nèi)存碎片便產(chǎn)生了.MYSQL學(xué)習(xí)

3.內(nèi)存塊的概念MYSQL學(xué)習(xí)

先看下這個(gè)：MYSQL學(xué)習(xí)

MYSQL學(xué)習(xí)

Qcache_total_blocks 表示所有的塊MYSQL學(xué)習(xí)

Qcache_free_blocks 表示未使用的塊
這個(gè)值比較大,那意味著,內(nèi)存碎片比較多,用flush query cache清理后,為被使用的塊其值應(yīng)該為1或0 ,因?yàn)檫@時(shí)候所有的內(nèi)存都做為一個(gè)連續(xù)的快在一起了.MYSQL學(xué)習(xí)

Qcache_free_memory 表示查詢(xún)緩存區(qū)現(xiàn)在還有多少的可用內(nèi)存
Qcache_hits 表示查詢(xún)緩存區(qū)的命中個(gè)數(shù),也就是直接從查詢(xún)緩存區(qū)作出響應(yīng)處理的查詢(xún)個(gè)數(shù)
Qcache_inserts 表示查詢(xún)緩存區(qū)此前總過(guò)緩存過(guò)多少條查詢(xún)命令的結(jié)果
Qcache_lowmem_prunes 表示查詢(xún)緩存區(qū)已滿(mǎn)而從其中溢出和刪除的查詢(xún)結(jié)果的個(gè)數(shù)
Qcache_not_cached 表示沒(méi)有進(jìn)入查詢(xún)緩存區(qū)的查詢(xún)命令個(gè)數(shù)
Qcache_queries_in_cache 查詢(xún)緩存區(qū)當(dāng)前緩存著多少條查詢(xún)命令的結(jié)果MYSQL學(xué)習(xí)

優(yōu)化提示：MYSQL學(xué)習(xí)

如果Qcache_lowmem_prunes 值比較大,表示查詢(xún)緩存區(qū)大小設(shè)置太小,需要增大.
如果Qcache_free_blocks 較多,表示內(nèi)存碎片較多,需要清理,flush query cacheMYSQL學(xué)習(xí)

關(guān)于query_cache_min_res_unit大小的調(diào)優(yōu),書(shū)中給出了一個(gè)計(jì)算公式,可以供調(diào)優(yōu)設(shè)置參考：
MYSQL學(xué)習(xí)

還要注意一點(diǎn)的是,FLUSH QUERY CACHE 命令可以用來(lái)整理查詢(xún)緩存區(qū)的碎片,改善內(nèi)存使用狀況,但不會(huì)清理查詢(xún)緩存區(qū)的內(nèi)容,這個(gè)要和RESET QUERY CACHE相區(qū)別,不要混淆,后者才是清除查詢(xún)緩存區(qū)中的所有的內(nèi)容.
可以在 SELECT 語(yǔ)句中指定查詢(xún)緩存的選項(xiàng),對(duì)于那些肯定要實(shí)時(shí)的從表中獲取數(shù)據(jù)的查詢(xún),或者對(duì)于那些一天只執(zhí)行一次的查詢(xún),我們都可以指定不進(jìn)行查詢(xún)緩存,使用 SQL_NO_CACHE 選項(xiàng).
對(duì)于那些變化不頻繁的表,查詢(xún)操作很固定,我們可以將該查詢(xún)操作緩存起來(lái),這樣每次執(zhí)行的時(shí)候不實(shí)際拜訪(fǎng)表和執(zhí)行查詢(xún),只是從緩存獲得結(jié)果,可以有效地改善查詢(xún)的性能,使用 SQL_CACHE 選項(xiàng).
下面是使用 SQL_NO_CACHE 和 SQL_CACHE 的例子：

注意：查詢(xún)緩存的使用還需要配合相應(yīng)得服務(wù)器參數(shù)的設(shè)置.

二、覆蓋索引(偷懶整理一下,來(lái)自百度百科)MYSQL學(xué)習(xí)

理解方式一：就是select的數(shù)據(jù)列只用從索引中就能夠取得,不必讀取數(shù)據(jù)行,換句話(huà)說(shuō)查詢(xún)列要被所建的索引覆蓋.
理解方式二：索引是高效找到行的一個(gè)辦法,但是一般數(shù)據(jù)庫(kù)也能使用索引找到一個(gè)列的數(shù)據(jù),因此它不必讀取整個(gè)行.畢竟索引葉子節(jié)點(diǎn)存儲(chǔ)了它們索引的數(shù)據(jù)；當(dāng)能通過(guò)讀取索引就可以得到想要的數(shù)據(jù),那就不需要讀取行了.一個(gè)索引包含了(或覆蓋了)滿(mǎn)足查詢(xún)結(jié)果的數(shù)據(jù)就叫做覆蓋索引.
理解方式三：是非聚集復(fù)合索引的一種形式,它包括在查詢(xún)里的Select、Join和Where子句用到的所有列(即建索引的字段正好是覆蓋查詢(xún)條件中所涉及的字段,也即,索引包含了查詢(xún)正在查找的數(shù)據(jù)).MYSQL學(xué)習(xí)

作用：MYSQL學(xué)習(xí)

如果你想要通過(guò)索引覆蓋select多列,那么需要給需要的列建立一個(gè)多列索引,當(dāng)然如果帶查詢(xún)條件,where條件要求滿(mǎn)足最左前綴原則.MYSQL學(xué)習(xí)

Innodb的輔助索引葉子節(jié)點(diǎn)包括的是主鍵列,所以主鍵一定是被索引覆蓋的.MYSQL學(xué)習(xí)

(1)例如,在sakila的inventory表中,有一個(gè)組合索引(store_id,film_id),對(duì)于只需要拜訪(fǎng)這兩列的查 詢(xún),MySQL就可以使用索引,如下：
MYSQL學(xué)習(xí)

(2)再比如說(shuō)在文章系統(tǒng)里分頁(yè)顯示的時(shí)候,一般的查詢(xún)是這樣的：

通常這樣的查詢(xún)會(huì)把索引建在created字段(其中id是主鍵),不過(guò)當(dāng)LIMIT偏移很大時(shí),查詢(xún)效率仍然很低,改變一下查詢(xún)：

此時(shí),建立復(fù)合索引”created, id”(只要建立created索引就可以吧,Innodb是會(huì)在輔助索引里面存儲(chǔ)主鍵值的),就可以在子查詢(xún)里利用上Covering Index,快速定位id,查詢(xún)效率嗷嗷的MYSQL學(xué)習(xí)

注：本文是參考《Mysql性能優(yōu)化案例 - 覆蓋索引》 的一篇文章借題發(fā)揮,參考了原文的知識(shí)點(diǎn),本身做了一點(diǎn)的發(fā)揮和研究,原文被多次轉(zhuǎn)載,不知作者何許人也,也不知出處在哪個(gè),如需原文請(qǐng)自行搜索.MYSQL學(xué)習(xí)

維易PHP培訓(xùn)學(xué)院每天發(fā)布《Mysql應(yīng)用Mysql性能優(yōu)化案例研究-覆蓋索引和SQL_NO_CACHE》等實(shí)戰(zhàn)技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養(yǎng)人才。

轉(zhuǎn)載請(qǐng)注明本頁(yè)網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/9492.html