MySQL复合索引性能比较

复合索引是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. selecta.*fromdlog_userainnerjoindlog_friendbona.username=b.friend_usernamewhereb.username='ld'orderbya.online_statusdesc,a.username

也就是ORDER BY的时候,两个字段的排序方式相反,这时不管怎么调整索引,执行此SQL语句都要进行全表查询以及 user filesort.

结论:

1.ORDER BY 中的字段必须按照SQL语句中的顺序来建索引.

2.ORDER BY 中的字段的排序顺序必须一直,否则索引无效.

3.建了索引不一定就有效，用实际的SQL检查一下.

下面用几个例子对比查询条件的不同对性能影响.

1. createtabletest(aint,bint,cint,KEYa(a,b,c));
2. --phpfensi.com
3. 优:select*fromtestwherea=10andb>50
4. 差:select*fromtestwherea50
5. 优:select*fromtestwhereorderbya
6. 差:select*fromtestwhereorderbyb
7. 差:select*fromtestwhereorderbyc
8. 优:select*fromtestwherea=10orderbya
9. 优:select*fromtestwherea=10orderbyb
10. 差:select*fromtestwherea=10orderbyc
11. 优:select*fromtestwherea>10orderbya
12. 差:select*fromtestwherea>10orderbyb
13. 差:select*fromtestwherea>10orderbyc
14. 优:select*fromtestwherea=10andb=10orderbya
15. 优:select*fromtestwherea=10andb=10orderbyb
16. 优:select*fromtestwherea=10andb=10orderbyc
17. 优:select*fromtestwherea=10andb=10orderbya
18. 优:select*fromtestwherea=10andb>10orderbyb
19. 差:select*fromtestwherea=10andb>10orderbyc

索引原则

1.索引越少越好

原因:主要在修改数据时,第个索引都要进行更新,降低写速度.

2.最窄的字段放在键的左边

3.避免file sort排序,临时表和表扫描.