《Mysql應用Mysql性能優化案例研究-覆蓋索引和SQL_NO_CACHE》要點：
本文介紹了Mysql應用Mysql性能優化案例研究-覆蓋索引和SQL_NO_CACHE，希望對您有用。如果有疑問，可以聯系我們。

場景MYSQL學習

產品中有一張圖片表pics,數據量將近100萬條,有一條相關的查詢語句,由于執行頻次較高,想針對此語句進行優化MYSQL學習

表結構很簡單,主要字段：MYSQL學習

一個用戶會有多條圖片記錄,現在有一個根據user_id建立的索引：uid,查詢語句也很簡單：取得某用戶的圖片集合：
MYSQL學習

優化前

執行查詢語句(為了查看真實執行時間,強制不使用緩存,為了防止在測試時因為讀取了緩存造成對時間上的差別)
MYSQL學習

執行了10次,平均耗時在40ms左右

使用explain進行分析：
MYSQL學習

MYSQL學習

使用了user_id的索引,并且是const常數查找,表示性能已經很好了MYSQL學習

優化后MYSQL學習

因為這個語句太簡單,sql自己沒有什么優化空間,就考慮了索引MYSQL學習

修改索引結構,建立一個(user_id,picname,smallimg)的聯合索引：uid_picMYSQL學習

重新執行10次,平均耗時降到了30ms左右MYSQL學習

使用explain進行分析MYSQL學習

MYSQL學習

看到使用的索引變成了剛剛建立的聯合索引,并且Extra部分顯示使用了'Using Index'MYSQL學習

總結MYSQL學習

‘Using Index'的意思是“覆蓋索引”,它是使上面sql性能提升的關鍵MYSQL學習

一個包括查詢所需字段的索引稱為“覆蓋索引”MYSQL學習

MySQL只需要通過索引就可以返回查詢所需要的數據,而不必在查到索引之后進行回表操作,減少IO,提高了效率MYSQL學習

例如上面的sql,查詢條件是user_id,可以使用聯合索引,要查詢的字段是picname smallimg,這兩個字段也在聯合索引中,這就實現了“覆蓋索引”,可以根據這個聯合索引一次性完成查詢工作,所以提升了性能.MYSQL學習

擴展研究MYSQL學習

一、Mysql緩存,SQL_NO_CACHE和SQL_CACHE 的區別MYSQL學習

上邊在進行測試的時候,為了防止讀取緩存造成對實驗結果的影響使用到了SQL_NO_CACHE這個功能,對于SQL_NO_CACHE的介紹官網如下：MYSQL學習

當我們想用SQL_NO_CACHE來禁止結果緩存時發現結果和我們的預期不一樣,查詢執行的結果仍然是緩存后的結果.其實,SQL_NO_CACHE的真正作用是禁止緩存查詢結果,但并不意味著cache不作為結果返回給query.MYSQL學習

在說白點就是,不是本次查詢不使用緩存,而是本次查詢結果不做為下次查詢的緩存.MYSQL學習

還有就是,mysql本身是有對sql語句緩存的機制的,合理設置我們的mysql緩存可以降低數據庫的io資源,因此,這里我們有必要再看一下如何控制這個比較安適的功能.MYSQL學習

看圖如下：MYSQL學習

MYSQL學習

其中各項的含義為：MYSQL學習

1、have_query_cache
是否支持查詢緩存區 “YES”表是支持查詢緩存區MYSQL學習

2、query_cache_limit
可緩存的Select查詢結果的最大值 1048576 byte /1024 = 1024kB 即最大可緩存的select查詢結果必須小于 1024KBMYSQL學習

3、query_cache_min_res_unit
每次給query cache結果分配內存的大小 默認是 4096 byte 也即 4kBMYSQL學習

4、query_cache_size
如果你希望禁用查詢緩存,設置 query_cache_size=0.禁用了查詢緩存,將沒有明顯的開銷MYSQL學習

5、query_cache_type
查詢緩存的方式(默認是 ON)MYSQL學習

1、完整查詢的過程如下MYSQL學習

當查詢進行的時候,Mysql把查詢結果保留在qurey cache中,但是有時候要保留的結果比較大,超過了query_cache_min_res_unit的值 ,這時候mysql將一邊檢索結果,一邊進行慢慢保留結果,所以,有時候并不是把所有結果全部得到后再進行一次性保留,而是每次分配一塊query_cache_min_res_unit 大小的內存空間保留結果集,使用完后,接著再分配一個這樣的塊,如果還不不夠,接著再分配一個塊,依此類推,也就是說,有可能在一次查詢中,mysql要進行多次內存分配的操作,而我們應該知道,頻繁操作內存都是要耗費時間的.MYSQL學習

2、內存碎片的產生MYSQL學習

當一塊分配的內存沒有完全使用時,MySQL會把這塊內存Trim掉,把沒有使用的那部分歸還以重復利用.比如,第一次分配4KB,只用了3KB,剩1KB,第二次連續操作,分配4KB,用了2KB,剩2KB,這兩次連續操作共剩下的1KB+2KB=3KB,不足以做個一個內存單元分配,這時候,內存碎片便產生了.MYSQL學習

3.內存塊的概念MYSQL學習

先看下這個：MYSQL學習

MYSQL學習

Qcache_total_blocks 表示所有的塊MYSQL學習

Qcache_free_blocks 表示未使用的塊
這個值比較大,那意味著,內存碎片比較多,用flush query cache清理后,為被使用的塊其值應該為1或0 ,因為這時候所有的內存都做為一個連續的快在一起了.MYSQL學習

Qcache_free_memory 表示查詢緩存區現在還有多少的可用內存
Qcache_hits 表示查詢緩存區的命中個數,也就是直接從查詢緩存區作出響應處理的查詢個數
Qcache_inserts 表示查詢緩存區此前總過緩存過多少條查詢命令的結果
Qcache_lowmem_prunes 表示查詢緩存區已滿而從其中溢出和刪除的查詢結果的個數
Qcache_not_cached 表示沒有進入查詢緩存區的查詢命令個數
Qcache_queries_in_cache 查詢緩存區當前緩存著多少條查詢命令的結果MYSQL學習

優化提示：MYSQL學習

如果Qcache_lowmem_prunes 值比較大,表示查詢緩存區大小設置太小,需要增大.
如果Qcache_free_blocks 較多,表示內存碎片較多,需要清理,flush query cacheMYSQL學習

關于query_cache_min_res_unit大小的調優,書中給出了一個計算公式,可以供調優設置參考：
MYSQL學習

還要注意一點的是,FLUSH QUERY CACHE 命令可以用來整理查詢緩存區的碎片,改善內存使用狀況,但不會清理查詢緩存區的內容,這個要和RESET QUERY CACHE相區別,不要混淆,后者才是清除查詢緩存區中的所有的內容.
可以在 SELECT 語句中指定查詢緩存的選項,對于那些肯定要實時的從表中獲取數據的查詢,或者對于那些一天只執行一次的查詢,我們都可以指定不進行查詢緩存,使用 SQL_NO_CACHE 選項.
對于那些變化不頻繁的表,查詢操作很固定,我們可以將該查詢操作緩存起來,這樣每次執行的時候不實際拜訪表和執行查詢,只是從緩存獲得結果,可以有效地改善查詢的性能,使用 SQL_CACHE 選項.
下面是使用 SQL_NO_CACHE 和 SQL_CACHE 的例子：

注意：查詢緩存的使用還需要配合相應得服務器參數的設置.

二、覆蓋索引(偷懶整理一下,來自百度百科)MYSQL學習

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

作用：MYSQL學習

如果你想要通過索引覆蓋select多列,那么需要給需要的列建立一個多列索引,當然如果帶查詢條件,where條件要求滿足最左前綴原則.MYSQL學習

Innodb的輔助索引葉子節點包括的是主鍵列,所以主鍵一定是被索引覆蓋的.MYSQL學習

(1)例如,在sakila的inventory表中,有一個組合索引(store_id,film_id),對于只需要拜訪這兩列的查 詢,MySQL就可以使用索引,如下：
MYSQL學習

(2)再比如說在文章系統里分頁顯示的時候,一般的查詢是這樣的：

通常這樣的查詢會把索引建在created字段(其中id是主鍵),不過當LIMIT偏移很大時,查詢效率仍然很低,改變一下查詢：

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

注：本文是參考《Mysql性能優化案例 - 覆蓋索引》 的一篇文章借題發揮,參考了原文的知識點,本身做了一點的發揮和研究,原文被多次轉載,不知作者何許人也,也不知出處在哪個,如需原文請自行搜索.MYSQL學習

維易PHP培訓學院每天發布《Mysql應用Mysql性能優化案例研究-覆蓋索引和SQL_NO_CACHE》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/9492.html