mysql 多列索引

1 索引类型

    1.1 B-tree索引

         注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,比如,严格的说,NDB引擎,使用的是T-tree，  Myisam,innodb中,默认用B-tree索引

     但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.  

    1.2 hash索引

        在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)

       疑问: 既然hash的查找如此高效,为什么不都用hash索引?

1. hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
2.  无法对范围查询进行优化.
3. 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引) ，因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
4. 无法优化排序.
5. 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据

2 btree索引的常见误区

 2.1 在where条件常用的列上都加上索引

  例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
  误区: cat_id上,和, price上都加上索引.只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

 2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用

   误区: 多列索引上,索引发挥作用,需要满足左前缀要求.

   以 index(a,b,c) 为例

 

sql 语句	索引情况
where a=3	使用到了a列索引
where a=3 and b=5	使用到了a,b列索引
Where a=3 and b=5 and c=4	使用到了a,b列索引
where b=3  or  where c=4	没有使用到索引
Where a=3 and c=4	a列能发挥索引c列不能
Where a=3 and b>10 and c=7	a 列能使用索引,b列能使用, c列不能
where a=3 and b like ‘xxxx%’ and c=7	a 列能使用索引,b列能使用,c不能用

    注：在mysql 中，范围查询后面的条件列无法使用索引。

3.实验

       建表同时建立复合索引

create database test default charset = utf8;
use  test;
drop table multi_column_index;
create table multi_column_index (
c1 char(1) not null default '',
c2 char(1) not null default '',
c3 char(1) not null default '',
c4 char(1) not null default '',
c5 char(1) not null default '',
index c1234(c1,c2,c3,c4)
)engine myisam charset utf8;

        随便插入一些数据：

insert into multi_column_index values('a','b','c','d','e');
insert into multi_column_index values('b','c','d','e','f');
insert into multi_column_index values('c','d','e','f','g');
insert into multi_column_index values('d','e','f','g','h');
insert into multi_column_index values('e','f','g','h','i');
insert into multi_column_index values('f','g','h','i','j');
insert into multi_column_index values('g','h','i','j','k');
insert into multi_column_index values('h','i','j','k','l');
insert into multi_column_index values('i','h','i','j','k');
insert into multi_column_index values('j','6','=','w','q');
insert into multi_column_index values('4','i','2','o','v');
insert into multi_column_index values('e','0','6','d','s');
insert into multi_column_index values('h','i','2',',','2');
insert into multi_column_index values('y','3','1','v','p');
insert into multi_column_index values('a','k','-','8','-');
insert into multi_column_index values('c','p','0','3','8');
insert into multi_column_index values('d','a','p','.','5');
insert into multi_column_index values('e','e',']','d','2');

复制表数据(非必要)：

insert into multi_column_index select * from multi_column_index;

 

分析：

explain select * from multi_column_index where c1 = 'a' and c2 = 'b' and c4 > 'd' ;

可以看到使用到了索引c1234 这个复合索引(key:c1234) ，而且key_len 为6，使用到了c1和c2两列索引(在utf8 下，一个字符占3个字节)。

explain select * from multi_column_index where c1 = 'a' and c2 = 'b' and c4 > 'd'  and c3 = '-';

mysql 会自动智能的调节检索条件顺序，使其尽可能高效的检索数据。这里用到了4列索引，type为range 是因为c4列出现了>号。

explain select * from multi_column_index where c1 = 'a' and c2 = 'b' and c4 > 'd' and c3 > '1' ;

使用到了3列索引(c1,c2,c3)，由于c3使用了范围检索，所以导致c4列索引失效!!!

explain select * from multi_column_index where c1= 'g' and c2= '6' and c4= '3' order by c5 \G  ;
explain select * from multi_column_index where c1= 'g' and c2= '6' and c4= '3' order by c3 \G ;

上面两条sql 都使用到了2列索引,不同的是按照c5列排序使用了filesort(并不一定是文件排序，有可能是在内存排序，理解为2次排序即可)，而c3不用(因为c3是索引列，自然就已经是排好序的)。

explain select * from multi_column_index where c1='a' and c5='q' group by c2,c3 \G
explain select * from multi_column_index where c1='a' and c5='q' group by c3,c2 \G

上面两个sql 语句使用到了1列索引(c1),group by 后面的c2,c3顺序不一样，检索方式也不一致，c2在后的sql 语句(第2条sql 语句)，使用到了临时表和filesort 排序,因为先用c3后用c2分组,导致c2,c3索引没发挥作用。

注：在explain 中只有发挥了检索作用的索引才会被计算，而发挥排序(order by )，分组(group by)的索引则不计算在内。

tips:在建立了复合索引后，在索引的使用上一定要满足左前缀要求，否则有可能会导致失效。