?

1 、一個簡單的 sql 語句問題

??? 假設當前我們有一個表記錄用戶信息，結構如下：

??? a)????? 表結構

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) 由于需要按照注冊時間單獨查詢，建了一個regdate的索引

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

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

?

2 、常規(guī)答案

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

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

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

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

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

?

3 、查的多，查得快

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

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

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

?

4 、分析 ?

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

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

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

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

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

??? 確實如此。

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

SQL1	SQL2
key: PRIMARY	key: regdate

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

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

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

?

5 、其他 ?

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

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

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

??? 當然有些時候效果會很理想，比如 select id from u where regdate=xxx 就比select * from u where regdate=xxx快很多，原因已說明。

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

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

select * from t order by a limit 100000, 10; 可以改進為

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

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

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

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

6、結束

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

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

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

轉載地址： http://rdc.taobao.com/blog/cs/?p=406

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

?

附：什么叫覆蓋索引？

?

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

覆蓋索引的示例?

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

?

index_name1 就是一個覆蓋性索引。

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

?

一個例子與InnoDB索引的幾個概念