2011年，我開展了“3分鐘測試你對SQL效能知道多少?”的測試活動。其中包含五個問題，它們是這樣的：每個問題有一個query/index查詢，問你這樣是否正確使用了索引。至今， 這個測試已經成了 Use The Index, Luke網站上的一個熱點。這個測試已經被回答了28,000次。
提醒一下：也許你不想被我劇透，你可以提前自己測試一下自己。
儘管這個測試是為了教育，我很好奇自己是否可以從中找到一些規律，我認為可以的。當你看這些結果時，要記住幾點，第一，這些測試因為很出人意料才惹人眼球，也就是說，有的測試看著效能很高，其實效能不高。有的反之。只有一個問題答案符合你的第一印象。很有意思的是，這個測試並不知道參與者是誰，所有人都可以參與，為了獲得一個好的分數，你也可以再來一遍。要曉得這個測試不是為了對索引進行科學研究。然而，我認為結果仍可以給人一些啟示。
下面我對每個問題展示兩個不同的統計圖。第一，每個問題平均沒正確回答多少次。第二，對於MySQL, Oracle, PostgreSQL 和 SQL Server統計資料有什麼不同。也就是說，是否MySQL 使用者會比PostgreSQL 使用者更懂索引呢？我很幸運獲得這樣的統計資料，原因是不同的資料庫提供商有自己獨特的語法定義。像MySQL和PostgreSQL 中的 LIMIT 到了SQL Server中就成了 TOP。因此參與者開始時要選擇一種資料庫，問題是針對所選資料庫的。
問題一：WHERE語句中的函式
從效能上來看，下面的SQL語句是好的實踐嗎？
查詢出所有2012年的行：
|
1 2 3 4 5 |
CREATE INDEX tbl_idx ON tbl (date_column); SELECT text, date_column FROM tbl WHERE TO_CHAR(date_column, 'YYYY') = '2012'; |
這個例子 SQL語句使用了Oracle和PostgreSQL 的特有函式，在MYSQL中這個問題就使用YEAR(date_column)，在SQL SERVER中則為datepart(yyyy, date_column)。當然我可以使用EXTRACT(YEAR date_column)，但我覺得還是使用通用一點的語法好一點。
參與者有兩個選項：
- 好的實踐 ，沒有大的效能改進可以採用了
- 壞的實踐，有大的效能改進可以採用
答案是“壞實踐”，原因是雖然在date_column上有索引，但 是在date_column欄位上加了函式以後，索引就失效了。你如果不信，你可以看看一些可以證明我的結論的指令碼和最後的解釋說明。詳細的解釋都在 Use The Index, Luke網站的相關頁面上。
如果你不知道在欄位上加函式時怎麼吧索引的功能給抹殺了，很多人都和你一樣。只有2/3的人給出了正確答案。算上有些人選了兩次，有些人是蒙的。這樣說來差不多隻有一半的人答對，無疑是很少的。我用下面這張圖強調一下
這是我平時工作中最常見的一個問題，當你在VARCHAR 型別的欄位上使用UPPER, TRIM等函式時同樣會碰到這個問題。請記住，當你對WHERE語句中使用的欄位加上函式的時候，它的索引功能就失去了作用。
儘管這個結果很令人失望——只比隨便碰對的概率高17%，但這都沒讓我感到驚奇。讓我驚奇的是在不同資料庫使用者中結果的不同。
實施上 MYSQL使用者只得到了55%的分數——就像純粹蒙一樣低。PostgreSQL 使用者卻獲得了83%的分數。
也許產生這個結果的原因是MYSQL不支援function-based indexes而Oracle 和 PostgreSQL支援。Function-based 索引允許你使用索引表示式像TO_CHAR(date_column, ‘YYYY’)，雖然對這個測試來說，這樣做不是推薦的解決方案。但僅僅是這個特性的存在讓Oracle 和 PostgreSQL使用者對這個問題更有意識。SQL Server提供了類似的特性，雖然不能直接使用索引表示式，但是你可以建立所謂的computed column，這個列是可以被索引的。
雖然以上可以解釋為什麼MySQL 使用者的效率比較低，但這不是藉口。不管支援function-based indexes與否，那個 query/index句子總之效率很低。很有效果的改進是不在索引欄位上使用函式：
|
1 2 3 4 |
SELECT text, date_column FROM tbl WHERE date_column >= TO_DATE('2012-01-01', 'YYYY-MM-DD') AND date_column < TO_DATE('2013-01-01', 'YYYY-MM-DD'); |
索引欄位不必改變。這種解決方案很靈活，因為它支援廣泛的型別——星期或月份。這是我推薦的解決方案。
我很好奇，我想知道那些正確回答問題的人怎麼在function-based索引上考慮複合索引。我最好把這種回答認為是正確了一半。
問題二：索引過之後的TOP-N查詢
從效能上來看是好的實踐還是壞的實踐？
按時間遠近排行：
|
1 2 3 4 5 6 7 |
CREATE INDEX tbl_idx ON tbl (a, date_column); SELECT id, a, date_column FROM tbl WHERE a = ? ORDER BY date_column DESC LIMIT 1; |
注意，那個問號是個佔位符。因為我經常推薦開發者使用繫結變數。
參與者有兩個選項：
- 好的實踐 ，沒有大的效能改進可以採用了
- 壞的實踐，有大的效能改進可以採用
這個問題看著有效能危險，但其實不是。一般看來order by一定會對資料排序，然而這個索引，使你沒有沒有必要對整個資料集排序，所以它就像查詢唯一索引鍵一樣快。
正確率接近與 “隨便蒙” ，我認為人們對這個問題基本上沒有概念。
這個結果讓人難以接受， 我看到人們平時建立快取表，恰恰為了避免我們介紹這種查詢，經常被計劃任務填滿。有趣的是這種日常任務經常引起效能問題，因為它需要在很小的時間間隔內確認快取表中是否存在新的資料。然而，正確的索引應該是你的第一選擇。
這裡，我要提一下Oracle 資料庫使用者要特別注意一下這個技巧。到12c 版本的Oracle資料庫仍然沒有提供像LIMIT or TOP等便利的語法糖。你可以使用ROWNUM的偽式的資料列。
|
1 2 3 4 5 6 7 8 |
SELECT * FROM ( SELECT id, date_column FROM tbl WHERE a = :a ORDER BY date_column DESC ) WHERE rownum <= 1; |
這個多餘的複雜度讓Oracle使用者得到了錯誤的結果，比“隨便蒙”對的概率還低。
對於這個問題回饋的另一個爭論是如果包含ID列將允許 index-only scan，儘管這是正確的，但我不認為不這樣做就是一個“壞實踐”。因為查詢的只有一行。index-only scan可以避免單表訪問，很多情況下你可以使用它提高效能，但一般情況下我認為這是一種過早優化，這是隻是我的觀點。但這個爭論可以讓我們看到PostgreSQL 使用者獲得最好的分數。PostgreSQL直到9.2版本才有index-only scans。在2012年九月才釋出這個特性。因此PostgreSQL 沒有掉入認為只有index-only scan才能提高效能的陷阱。
問題三：索引列的順序
從效能上來看是好的實踐還是壞的實踐
兩個查詢語句：
|
1 2 3 4 5 6 7 8 9 10 |
CREATE INDEX tbl_idx ON tbl (a, b); SELECT id, a, b FROM tbl WHERE a = ? AND b = ?; SELECT id, a, b FROM tbl WHERE b = ?; |
參與者有兩個選項：
- 好的實踐 ，沒有大的效能改進可以採用了
- 壞的實踐，有大的效能改進可以採用
答案是壞實踐，因為第二個查詢語句沒有正確地使用索引。把索引列的順序改為(b, a)可以使兩個查詢語句都能使用索引從而獲得很高的效能。在b上再加一個索引，從而無緣無故的帶來了很大的效能開銷。不幸地是我看到很多人都這麼做。
結果是令人失望的，但是我已經猜到了。比“隨便蒙”只高12.5% 。
這也是一個我每天都遇到的問題，人們就是不知道複合索引是怎麼工作的。
不同資料庫的使用者的回答很接近，可能是因為（不同資料庫）沒有很大語法區別和的特性影響回答的結果。Oracle的不為人知的Skip Scan特性有很小的影響。通常來講index-only scan 的意識可能有影響，但這次它的影響是讓參與者更有可能回答對問題。
總之，統計表明，一些資料庫的使用者比另一下更瞭解索引。有趣的是PostgreSQL 使用者第三次獲得最高分。
問題四：模糊查詢
從效能上來看是好的實踐還是壞的實踐？
查詢一個句子：
|
1 2 3 4 5 |
CREATE INDEX tbl_idx ON tbl (text); SELECT id, text FROM tbl WHERE text LIKE '%TERM%'; |
我這次給出了不一樣的答案：
- 銀彈 ，總是執行的很快
- 噩夢，有效能危險
正確答案是噩夢因為匹配符中使用了字首萬用字元，反之如果使用匹配符“TERM%”就會更有效率。大部分人都能回答對這個問題。我可以說大部分人還是知道LIKE 不是用來全文搜尋的。
這個與眾不同的結果各種資料庫使用者的正確率相差無幾。
這一次PostgreSQL 使用者不是那麼牛逼了。我們仔細審視一下PostgreSQL 面對的問題就知道為什麼了。
|
1 2 3 4 5 |
CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops); SELECT id, text FROM tbl WHERE text LIKE '%TERM%'; |
注意我們對索引欄位的補充修飾（varchar_pattern_ops），在PostgreSQL中這個操作符類使的索引對字尾萬用字元無效。我加上這個是想知道人們是否意識到在模糊查詢是字首萬用字元會帶來問題。沒有操作符類，它不工作有兩個原因：（1）字首萬用字元；（2）沒有操作符類，我認為這是顯然的。
問題五a Index-only scan
第五個問題有點棘手，因為在這個測試開始時，PostgresSQL不支援 index-only scans。因此我稍微調整，兩組的這個問題不一樣。 MySQL, Oracle and SQL Server中是關於index-only scan。另一個是針對PostgresSQL 使用者出的關於索引列的順序問題。我把結果都展示在這裡。先看關於index-only scans:的問題。
從第一個到第二個查詢效能會怎麼改變？
從一百萬行中選出一百行：
|
1 2 3 4 5 6 |
CREATE INDEX tab_idx ON tbl (a, date_column); SELECT date_column, count(*) FROM tbl WHERE a = 123 GROUP BY date_column; |
從一百萬行中選出十行
|
1 2 3 4 5 |
SELECT date_column, count(*) FROM tbl WHERE a = 123 AND b = 42 GROUP BY date_column; |
這個問題有點不同，因為我給了四個答案：
- 查詢效能大體相同
- 依賴資料的不同
- 查詢會變很慢（影響>10%）
- 查詢會變很快（影響>10%）
在我出這個測試的時候，我十分曉得五五分的答案沒有什麼意義，要在讓參與者快速抓住要點並回答和給出準確答案之間做權衡。
簡單來說，正確答案是查詢會變的很慢，因為原來的查詢使用了index-only scan，這個查詢只使用了索引中的資料就能給出答案而不需要到實際的表中獲取資料。第二個查詢需要檢查資料列B，而資料列B不在索引中，因此資料庫要花費多餘的開銷到拿出候選的行來判斷是否符合條件，它要從表中取出100行，這正是第一個查詢中要返回的資料行數。因為有group by操作，估計要取出更多的資料行，會使查詢變的很慢。
因為有多個選項，總體分數明顯下降，掉到了比“隨便蒙”低39% or 14%。
我會說有39%的參與者知道正確答案這個結論是錯誤的，它們雖然給出了正確答案，但是我估計有25% 的人是蒙的。
分開各種資料庫使用者後，結果更是無聊。
但是，我們仍然要看一下人們是怎麼回答的：
我非常吃驚，“大體相同” 和 “依賴具體的資料”這兩個選項都獲得了25%的選擇——它們可能都是猜的。這是否表明一半的參與者只是在胡亂猜。還是因為這是最後一個問題，很多人都想快點做完看看答案，恩，很有可能。然而正確答案“會變的很慢”獲得了38.8%的選擇，導致只有10.9%的人選擇“會變的很快”選項。
我的本意是把人誤導選擇“會變的很快”，因為後者資料量更少——只有使用了index-only scan的情況下會變得不同，但是我假設我得到這個結果是因為人們通常會認為很明顯的答案肯定是錯的。這樣的話，我想驗證多少人會知道index-only scan的本意根本沒有得到證明。
問題5b：索引列順序和範圍操作符
這個問題只是給PostgreSQL 使用者的。
從效能上來看是好的實踐還是壞的實踐？
查詢狀態的X並且不超過五年的實體。
|
1 2 3 4 5 6 7 8 |
CREATE INDEX tbl_idx ON tbl (date_column, state); SELECT id, date_column, state FROM tbl WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR AND state = 'X'; (365 rows) |
資料分佈如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT count(*) FROM tbl WHERE date_column >= CURRENT_DATE - INTERVAL '5' YEAR; count ------- 1826 SELECT count(*) FROM tbl WHERE state = 'X'; count ------- 10000 |
參與者有兩個選項：
- 好的實踐 ，沒有大的效能改進可以採用了。
- 壞的實踐，有大的效能改進可以採用。
正確答案是“壞實踐”，因為索引的資料列的順序不對。通常的索引列排序是規律是，如果等號運算子放在左邊就經常有很高的效能，過濾之後，再使用範圍操作符也很有效率。然而，如果範圍操作符放在左邊，就會喪失索引的好處，之後的的索引列也不能高效率的使用。
像以上沒有修改的查詢語句，我們要在索引中找出1826個實體（它們都符合date_column 列的過濾），然後對它們進行state 列過濾。如果過濾順序改變一下，資料庫就使得兩次過濾都很有效，直接把要過濾的行數限制在了365 行內。
人們是這樣回答的：
等一下，竟然比隨便猜猜的正確概率還低，人們不僅對次沒有意識，而且大多數人都有了錯誤的理解。然而我得承認這個”大多數“是有水分的。當我執行這個例子時，快的不只是一倍，竟然加速了70%。
總體分數：多少人通過了測試？
單獨看每個例子很有趣，但是那不能讓你知道有多少人答對了5個題目，下面的圖可以告訴你。
最後，我想把這張圖歸結為一個數字：到底多少人通過了測試？
考慮到只有五個問題，並且每個問題只有兩個選項，公平的說，我想答對三個不足以說明你通過了測試，答對五個又明顯要求過高。答對四個通過測試，我覺得這樣界定是很明智的。使用這個定義，38.2%通過了測試。多說一句，隨便猜通過的概率為12.5%。