索引

一、复合索引

1、对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找。

 

2、感受复合索引

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

3、复合索引的比喻—电话簿。

复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

 

二、复合索引对排序的优化

复合索引只对和索引中排序相同或相反的order by 语句优化。

在创建复合索引时，每一列都定义了升序或者是降序。如定义一个复合索引

CREATE INDEX idx_example
ON table1 (col1 ASC, col2 DESC, col3 ASC)

其中 有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执行两个查询
1：Select
col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
和索引顺序相同
2：Select
col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
和索引顺序相反
查询1，2 都可以别复合索引优化。
如果查询为：
Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC

排序结果和索引完全不同时，此时的查询不会被复合索引优化。

 

三、查询优化器在在where查询中的作用

如果一个多列索引存在于 列 Col1 和 Col2 上，则以下语句：Select * from table where col1=val1 AND col2=val2 查询优化器会试图通过决定哪个索引将找到更少的行。之后用得到的索引去取值。
1． 如果存在一个多列索引，任何最左面的索引前缀能被优化器使用。所以联合索引的顺序不同，影响索引的选择，尽量将值少的放在前面。
如：一个多列索引为 (col1 ，col2， col3)
那么在索引在列 (col1) 、(col1 col2) 、(col1 col2 col3) 的搜索会有作用。

SELECT * FROM tb WHERE col1 = val1
SELECT * FROM tb WHERE col1 = val1 and col2 = val2
SELECT * FROM tb WHERE col1 = val1 and col2 = val2 AND col3 = val3

 

2、如果列不构成索引的最左面前缀，则建立的索引将不起作用。

SELECT * FROM tb WHERE col3 = val3
SELECT * FROM tb WHERE col2 = val2
SELECT * FROM tb WHERE col2 = val2 and col3=val3

 

3．如果一个 Like 语句的查询条件不以通配符起始则使用索引。

如：%车 或 %车% 不使用索引。
车% 使用索引。

 

四、索引的缺点：
1). 占用磁盘空间。
2). 增加了插入和删除的操作时间。一个表拥有的索引越多，插入和删除的速度越慢。如 要求快速录入的系统不宜建过多索引。

参考:http://webnoties.blog.163.com/blog/static/183525141201310182313851/