接著,來個稍微復雜一點的,如果有個ORDER BY字句呢?不管你信不信,大多數的數據庫在使用order by的時候,都將會從索引中受益。
SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
有點迷惑了吧?很簡單,就象為where字句中的字段建立一個索引一樣,也為ORDER BY的字句中的字段建立一個索引:
CREATE INDEX mytable_categoryid_userid_adddate
ON mytable (category_id,user_id,adddate);
注意: "mytable_categoryid_userid_adddate" 將會被截短為
"mytable_categoryid_userid_addda"
CREATE
EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY adddate DESC;
NOTICE: QUERY PLAN:
Sort (cost=2.03..2.03 rows=1 width=16)
-> Index Scan using mytable_categoryid_userid_addda
on mytable (cost=0.00..2.02 rows=1 width=16)
EXPLAIN
看看EXPLAIN的輸出,好象有點恐怖啊,數據庫多做了一個我們沒有要求的排序,這下知道性能如何受損了吧,看來我們對於數據庫的自身運作是有點過於樂觀了,那麼,給數據庫多一點提示吧。
為了跳過排序這一步,我們並恍枰?渌?硗獾乃饕??灰??檠?锞渖暈⒏囊幌隆U饫镉玫氖?ostgres,我們將給該數據庫一個額外的提示--在ORDER BY語句中,加入where語句中的字段。這只是一個技術上的處理,並不是必須的,因為實際上在另外兩個字段上,並不會有任何的排序操作,不過如果加入,postgres將會知道哪些是它應該做的。
EXPLAIN SELECT * FROM mytable
WHERE category_id=1 AND user_id=2
ORDER BY category_id DESC,user_id DESC,adddate DESC;
NOTICE: QUERY PLAN:
Index Scan Backward using
mytable_categoryid_userid_addda on mytable
(cost=0.00..2.02 rows=1 width=16)
EXPLAIN
現在使用我們料想的索引了,而且它還挺聰明,知道可以從索引後面開始讀,從而避免了任何的排序。
以上說得細了一點,不過如果你的數據庫非常巨大,並且每日的頁面請求達上百萬算,我想你會獲益良多的。不過,如果你要做更為復雜的查詢呢,例如將多張表結合起來查詢,特別是where限制字句中的字段是來自不止一個表格時,應該怎樣處理呢?我通常都盡量避免這種做法,因為這樣數據庫要將各個表中的東西都結合起來,然後再排除那些不合適的行,搞不好開銷會很大。