結果：我勉強算答對一題
保留起來。以後隨時複習
=============================================================
http://blog.jobbole.com/60800/
本文由 伯樂在線 - sunbiaobiao 翻譯自 MarkusWinand。歡迎加入技術翻譯小組。轉載請參見文章末尾處的要求。
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'; |
參與者有兩個選項：
- 好的實踐 ，沒有大的性能改進可以採用了
- 壞的實踐，有大的性能改進可以採用
如果你不知道在字段上加函數時怎麼吧索引的功能給抹殺了，很多人都和你一樣。只有2/3的人給出了正確答案。算上有些人選了兩次，有些人是蒙的。這樣說來差不多只有一半的人答對，無疑是很少的。我用下面這張圖強調一下
VARCHAR 類型的字段上使用UPPER, TRIM等函數時同樣會碰到這個問題。請記住，當你對WHERE語句中使用的字段加上函數的時候，它的索引功能就失去了作用。儘管這個結果很令人失望——只比隨便碰對的概率高17%，但這都沒讓我感到驚奇。讓我驚奇的是在不同數據庫使用者中結果的不同。
也許產生這個結果的原因是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; |
參與者有兩個選項：
- 好的實踐 ，沒有大的性能改進可以採用了
- 壞的實踐，有大的性能改進可以採用
正確率接近與 「隨便蒙」 ，我認為人們對這個問題基本上沒有概念。
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 = ?; |
- 好的實踐 ，沒有大的性能改進可以採用了
- 壞的實踐，有大的性能改進可以採用
結果是令人失望的，但是我已經猜到了。比「隨便蒙」只高12.5% 。
這也是一個我每天都遇到的問題，人們就是不知道復合索引是怎麼工作的。
總之，統計表明，一些數據庫的使用者比另一下更瞭解索引。有趣的是PostgreSQL 使用者第三次獲得最高分。
問題四：模糊查詢
從性能上來看是好的實踐還是壞的實踐？
查詢一個句子：
1
2
3
4
5
| CREATE INDEX tbl_idx ON tbl (text);SELECT id, text FROM tbl WHERE text LIKE '%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%'; |
問題五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%。
分開各種數據庫使用者後，結果更是無聊。
我的本意是把人誤導選擇「會變的很快」，因為後者數據量更少——只有使用了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------- 1826SELECT count(*) FROM tbl WHERE state = 'X'; count------- 10000 |
- 好的實踐 ，沒有大的性能改進可以採用了。
- 壞的實踐，有大的性能改進可以採用。
像以上沒有修改的查詢語句，我們要在索引中找出1826個實體（它們都符合
date_column 列的過濾），然後對它們進行state 列過濾。如果過濾順序改變一下，數據庫就使得兩次過濾都很有效，直接把要過濾的行數限制在了365 行內。人們是這樣回答的：
總體分數：多少人通過了測試？
單獨看每個例子很有趣，但是那不能讓你知道有多少人答對了5個題目，下面的圖可以告訴你。
考慮到只有五個問題，並且每個問題只有兩個選項，公平的說，我想答對三個不足以說明你通過了測試，答對五個又明顯要求過高。答對四個通過測試，我覺得這樣界定是很明智的。使用這個定義，38.2%通過了測試。多說一句，隨便猜通過的概率為12.5%。
原文鏈接： MarkusWinand 翻譯： 伯樂在線 - sunbiaobiao
譯文鏈接： http://blog.jobbole.com/60800/
[ 轉載必須在正文中標註並保留原文鏈接、譯文鏈接和譯者等信息。]
沒有留言:
張貼留言