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语句如下：
| 代码如下 | |
|
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检查一下。
下面用几个例子对比查询条件的不同对性能影响.
create table test( a int, b int, c int, KEY a(a,b,c) );
| 代码如下 | |
|
优: select * from test where a=10 and b>50 差: select * from test where a50 优: select * from test where order by a 差: select * from test where order by b 差: select * from test where order by c 优: select * from test where a=10 order by a 优: select * from test where a=10 order by b 差: select * from test where a=10 order by c 优: select * from test where a>10 order by a 差: select * from test where a>10 order by b 差: select * from test where a>10 order by c 优: select * from test where a=10 and b=10 order by a 优: select * from test where a=10 and b=10 order by b 优: select * from test where a=10 and b=10 order by c 优: select * from test where a=10 and b=10 order by a 优: select * from test where a=10 and b>10 order by b 差: select * from test where a=10 and b>10 order by c |
|
索引原则
1.索引越少越好
原因：主要在修改数据时，第个索引都要进行更新，降低写速度。
2.最窄的字段放在键的左边
3.避免file sort排序，临时表和表扫描.
