?

1 、一個(gè)簡(jiǎn)單的 sql 語句問題

??? 假設(shè)當(dāng)前我們有一個(gè)表記錄用戶信息，結(jié)構(gòu)如下：

??? a)????? 表結(jié)構(gòu)

CREATE TABLE `u` (

? `id` int(11) NOT NULL DEFAULT ’0′,

? `regdate` int(1) unsigned,

? …..

? PRIMARY KEY (`id`),

? KEY `regdate` (`regdate`)

) ENGINE=InnoDB DEFAULT CHARSET=gbk

說明：1) 由于需要按照注冊(cè)時(shí)間單獨(dú)查詢，建了一個(gè)regdate的索引

??????? ??? 2) 其他信息未列出， 一行長(zhǎng)度100字節(jié)左右，表行數(shù)百萬級(jí)。?

b)????? 需求：需要一個(gè)語句查出表中id為10000整數(shù)倍的記錄總數(shù)。

?

2 、常規(guī)答案

??? 一個(gè)正常想到的語句是 select sum(id % 10000 = 0) from u; —— (SQL1)

??? 我們來看這個(gè)語句的執(zhí)行流程：

a)????? 遍歷所有數(shù)據(jù)，取出id字段

b)????? 計(jì)算id%10000=0的值并通過sum累計(jì)。

?????????? 在構(gòu)造的環(huán)境中這個(gè)語句的執(zhí)行時(shí)間為 2.6s.

?

3 、查的多，查得快

??? 假設(shè)我們同時(shí)要查出注冊(cè)時(shí)間在2007年之前的用戶總數(shù)，我們自然得到這個(gè)語句

??? ?select sum(id % 10000 = 0), sum(regdate<1167667200) from sbtest;—-(SQL2)

??? 執(zhí)行結(jié)果發(fā)現(xiàn)這個(gè)語句執(zhí)行時(shí)間約 0.5s ?。 這個(gè)語句查的數(shù)據(jù)結(jié)果比SQL1多，但執(zhí)行時(shí)間卻降為1/5.

?

4 、分析 ?

??? 可以直接從執(zhí)行期間的磁盤參數(shù)，或者在os/os0file.c中將程序讀取的數(shù)據(jù)量輸出結(jié)果查看，直觀結(jié)果是SQL1讀取了更多的磁盤數(shù)據(jù)。

問題1： 在SQL1執(zhí)行過程中，遍歷所有數(shù)據(jù)，InnoDB只從磁盤讀取了id這個(gè)字段，還是全部讀入？

??? 實(shí)際上由于id是聚簇索引，并沒有一個(gè)單獨(dú)的索引樹存id，因此在磁盤上，id索引樹的葉節(jié)點(diǎn)上就是數(shù)據(jù)。 InnoDB以page為單位讀取，在取id的過程中，必須將所有的數(shù)據(jù)讀入。

??? 于是我們發(fā)現(xiàn)，在SQL1中，我們只需要id字段，而每行額外讀入了幾百字節(jié)的數(shù)據(jù)。

問題2： SQL2避免了讀全數(shù)據(jù)？

??? 確實(shí)如此。

??? 我們對(duì)比兩個(gè)語句的explain結(jié)果, 發(fā)現(xiàn)僅有的不同是選用的key結(jié)果不同。

SQL1	SQL2
key: PRIMARY	key: regdate

??? 由于regdate是非聚簇(secondary index)索引，單獨(dú)存于另一棵樹。 我們知道使用非聚簇索引時(shí)， 需要讀行數(shù)據(jù)的時(shí)候 ，需要再到聚簇索引中取得。顯然SQL2不會(huì)再讀一遍全數(shù)據(jù)(否則性能必然低于SQL1)。

??? 而其原因是覆蓋索引(covering index)。 非聚簇索引的葉節(jié)點(diǎn)上是聚簇索引的字段值，需要取數(shù)據(jù)時(shí)，根據(jù)這個(gè)值再去聚簇索引上取。而這時(shí)InnoDB變“聰明”了， 需要取的值只是id，而id作為聚簇索引的key信息，已經(jīng)得到， 不需要再到聚簇索引中 讀取數(shù)據(jù)。

??? 由于regdate索引樹上只有regdate和主鍵(id)的信息，因此數(shù)據(jù)量遠(yuǎn)小于全表數(shù)據(jù)，因此SQL2的讀盤量小于SQL1，執(zhí)行速度快。

?

5 、其他 ?

??? 這個(gè)例子涉及到幾個(gè)概念， 聚簇索引(cluster index)、非聚簇索引(secondary index), 覆蓋索引(covering index)，還有磁盤的數(shù)據(jù)存放。都算是一些基本的內(nèi)容，卻是平時(shí)見到的一些優(yōu)化的理論基礎(chǔ)。舉幾個(gè)例子如下：

1)????? 我們經(jīng)常被告誡select之后只填最必須的字段

??? 其中的一個(gè)原因是減少網(wǎng)絡(luò)傳輸。但不一定能夠提升服務(wù)器執(zhí)行性能。比如例子中的表，select? * from u where id = n; 與select user_name from u where id =n一樣。

??? 當(dāng)然有些時(shí)候效果會(huì)很理想，比如 select id from u where regdate=xxx 就比select * from u where regdate=xxx快很多，原因已說明。

2)????? 查詢符合條件的第10w個(gè)記錄開始的10個(gè)記錄。

??? 這個(gè)例子在其他博文上被多次提及，

select * from t order by a limit 100000, 10; 可以改進(jìn)為

select * from t where a>=(select a from t order by a limit 100000,1) limit 10;

??? 在筆者環(huán)境中性能提升約1000倍。

??? 原因即在于， 改進(jìn)語句中，子查詢中的排序只在非聚餐索引a上執(zhí)行，由于覆蓋索引，排序過程不需要訪問聚簇索引。實(shí)際讀讀取全數(shù)據(jù)的只有10條記錄，而原語句則需要讀所有記錄的全數(shù)據(jù)。

??? 當(dāng)然執(zhí)行排序的過程消耗是一樣的。?

6、結(jié)束

??? 回到開頭，如果只需要查id滿足特定條件的記錄總數(shù)，可以使用select sum(id % 10000 = 0) from u force index (`regdate`);??

??? 把sum(id %10000=0)換成其他操作對(duì)執(zhí)行效率均沒有影響。?

??? 但若查詢內(nèi)容中出現(xiàn)除id和regdate外的其他字段，則force index優(yōu)化無效，可自行分析。

轉(zhuǎn)載地址： http://rdc.taobao.com/blog/cs/?p=406

------------------------------------------------------------------------------------------------

?

附：什么叫覆蓋索引？

?

覆蓋索引是在選擇條件和 WHERE 謂詞上均滿足 SQL 查詢的所有列的基礎(chǔ)上建立的非聚集索引。覆蓋索引可以節(jié)省大量的 I/O，因此可極大地改善查詢的性能。但是有必要在新建索引（以及與它相關(guān)的 B 樹索引結(jié)構(gòu)維護(hù)，如：數(shù)據(jù)量增多，更新速度較慢）所需要的代價(jià)和覆蓋索引所帶來的 I/O 性能增益之間進(jìn)行權(quán)衡。如果覆蓋索引對(duì)于 MySQL上經(jīng)常運(yùn)行的查詢極其有利，那么創(chuàng)建覆蓋索引是值得的。?

覆蓋索引的示例?

　　Select col1,col3 from table1 where col2 = 'value'.?
　　Create index index_name1 on table1(col2,col1,col3).

?

index_name1 就是一個(gè)覆蓋性索引。

覆蓋索引(covering index)，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，而不必在查到索引之后再去查詢數(shù)據(jù)，所以那是相當(dāng)?shù)目欤。〉峭瑫r(shí)也要求所查詢的字 段必須被索引所覆蓋到，在Explain的時(shí)候，輸出的Extra信息中如果有“Using Index”，就表示這條查詢使用了覆蓋索引。

?

一個(gè)例子與InnoDB索引的幾個(gè)概念