場(chǎng)景

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

表結(jié)構(gòu)很簡(jiǎn)單，主要字段：

一個(gè)用戶會(huì)有多條圖片記錄，現(xiàn)在有一個(gè)根據(jù)user_id建立的索引：uid，查詢語句也很簡(jiǎn)單：取得某用戶的圖片集合：

優(yōu)化前

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

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

使用explain進(jìn)行分析：

使用了user_id的索引，并且是const常數(shù)查找，表示性能已經(jīng)很好了

優(yōu)化后

因?yàn)檫@個(gè)語句太簡(jiǎn)單，sql本身沒有什么優(yōu)化空間，就考慮了索引

修改索引結(jié)構(gòu)，建立一個(gè)(user_id,picname,smallimg)的聯(lián)合索引：uid_pic

重新執(zhí)行10次，平均耗時(shí)降到了30ms左右

使用explain進(jìn)行分析

看到使用的索引變成了剛剛建立的聯(lián)合索引，并且Extra部分顯示使用了'Using Index'

總結(jié)

‘Using Index'的意思是“覆蓋索引”，它是使上面sql性能提升的關(guān)鍵

一個(gè)包含查詢所需字段的索引稱為“覆蓋索引”

MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，而不必在查到索引之后進(jìn)行回表操作，減少IO，提高了效率

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

擴(kuò)展研究

一、Mysql緩存，SQL_NO_CACHE和SQL_CACHE 的區(qū)別

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

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

在說白點(diǎn)就是，不是本次查詢不使用緩存，而是本次查詢結(jié)果不做為下次查詢的緩存。

還有就是，mysql本身是有對(duì)sql語句緩存的機(jī)制的，合理設(shè)置我們的mysql緩存可以降低數(shù)據(jù)庫的io資源，因此，這里我們有必要再看一下如何控制這個(gè)比較安逸的功能。

看圖如下：

其中各項(xiàng)的含義為：

1、have_query_cache
是否支持查詢緩存區(qū) “YES”表是支持查詢緩存區(qū)

2、query_cache_limit
可緩存的Select查詢結(jié)果的最大值 1048576 byte /1024 = 1024kB 即最大可緩存的select查詢結(jié)果必須小于 1024KB

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

4、query_cache_size
如果你希望禁用查詢緩存，設(shè)置 query_cache_size=0。禁用了查詢緩存，將沒有明顯的開銷

5、query_cache_type
查詢緩存的方式(默認(rèn)是 ON)

1、完整查詢的過程如下

當(dāng)查詢進(jìn)行的時(shí)候，Mysql把查詢結(jié)果保存在qurey cache中，但是有時(shí)候要保存的結(jié)果比較大，超過了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è)塊，依此類推，也就是說，有可能在一次查詢中，mysql要進(jìn)行多次內(nèi)存分配的操作，而我們應(yīng)該知道，頻繁操作內(nèi)存都是要耗費(fèi)時(shí)間的。

2、內(nèi)存碎片的產(chǎn)生

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

3.內(nèi)存塊的概念

先看下這個(gè)：

Qcache_total_blocks 表示所有的塊

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

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

優(yōu)化提示：

如果Qcache_lowmem_prunes 值比較大，表示查詢緩存區(qū)大小設(shè)置太小，需要增大。
如果Qcache_free_blocks 較多，表示內(nèi)存碎片較多，需要清理，flush query cache

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

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

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

二、覆蓋索引（偷懶整理一下，來自百度百科）

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

作用：

如果你想要通過索引覆蓋select多列，那么需要給需要的列建立一個(gè)多列索引，當(dāng)然如果帶查詢條件，where條件要求滿足最左前綴原則。

Innodb的輔助索引葉子節(jié)點(diǎn)包含的是主鍵列，所以主鍵一定是被索引覆蓋的。

（1）例如，在sakila的inventory表中，有一個(gè)組合索引(store_id,film_id)，對(duì)于只需要訪問這兩列的查 詢，MySQL就可以使用索引，如下：

（2）再比如說在文章系統(tǒng)里分頁顯示的時(shí)候，一般的查詢是這樣的：

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

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

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