復合索引是mysql中常用的一種數據優化形式了,下面我來給大家詳細介紹在mysql中復合索引的性能比較,各位朋友不防進入參考.

我們來看一些測試實例,代碼如下:

select * from dlog_user order by online_status, username

先看上面這個內聯的SQL語句,username是dlog_user表的主鍵,dlog_friend有一個由 username和friend_username組合而成的復合主鍵.

測試條件一:dlog_user 和 dlog_friend 兩個表除了主鍵外沒有建任何索引，對這條SQL語句EXPLAIN的結果是 dlog_user 做了全表查詢(type=ALL)，Extra信息是use filesort

測試條件二:dlog_user 增加復合索引,代碼如下:

create index idx_online_status on dlog_user(username,online_status);

再次EXPLAIN SQL語句,還是全表查詢以及 use filesort.

測試條件三:

修改復合索引,將 username 和 online_status 順序調換一下,這回得到的結果是:type=index,Extra=空

索引起作用了.

測試條件四:

更改SQL語句如下:

1. select a.* from dlog_user a inner join dlog_friend b on a.username=b.friend_username where b.username='ld' order by a.online_status desc,a.username 

也就是ORDER BY的時候,兩個字段的排序方式相反,這時不管怎么調整索引,執行此SQL語句都要進行全表查詢以及 user filesort.

結論:

1.ORDER BY 中的字段必須按照SQL語句中的順序來建索引.

2.ORDER BY 中的字段的排序順序必須一直,否則索引無效.

3.建了索引不一定就有效，用實際的SQL檢查一下.

下面用幾個例子對比查詢條件的不同對性能影響.

1. create table test( a int, b int, c int, KEY a(a,b,c) ); 
2. --Vevb.com 
3. 優: select * from test where a=10 and b>50 
4. 差: select * from test where a50 
5. 優: select * from test where order by a 
6. 差: select * from test where order by b 
7. 差: select * from test where order by c 
8. 優: select * from test where a=10 order by a 
9. 優: select * from test where a=10 order by b 
10. 差: select * from test where a=10 order by c 
11. 優: select * from test where a>10 order by a 
12. 差: select * from test where a>10 order by b 
13. 差: select * from test where a>10 order by c 
14. 優: select * from test where a=10 and b=10 order by a 
15. 優: select * from test where a=10 and b=10 order by b 
16. 優: select * from test where a=10 and b=10 order by c 
17. 優: select * from test where a=10 and b=10 order by a 
18. 優: select * from test where a=10 and b>10 order by b 
19. 差: select * from test where a=10 and b>10 order by c 

索引原則

1.索引越少越好

原因:主要在修改數據時,第個索引都要進行更新,降低寫速度.

2.最窄的字段放在鍵的左邊

3.避免file sort排序,臨時表和表掃描.