mysql复合索引、普通索引总结

mysql复合索引、普通索引总结  

对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。下面用几个例子对比查询条件的不同对性能影响.

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排序，临时表和表扫描.