mysql 索引 左前缀_MySQL的索引类型和左前缀索引

1.索引类型：

1.1B-tree索引：

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

但我们抽象一下---B-tree系统，可以理解成为“排好序的快速查找结构”

1.2hash索引

在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)为例：

语句

索引是否发挥作用

where a=3

是，只是用了a列的索引

Where a=3 and b=5

是,使用了a,b列

Where a=3 and b=5 and c=4

是,使用了abc

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不能用

为便于理解,假设ABC各10米长的木板,河面宽30米.

全值索引是则木板长10米,

Like,左前缀及范围查询,则木板长6米,

自己拼接一下,能否过河对岸,就知道索引能否利用上.

如上例中, where a=3 and b>10, and c=7,

A板长10米,A列索引发挥作用

A板正常接B板, B板索引发挥作用

B板短了,接不到C板, C列的索引不发挥作用.

假设某个表有一个联合索引(c1,c2,c3,c4)一下——只能使用该联合索引的c1,c2,c3部分

A where c1=x and c2=x and c4>x and c3=x

B where c1=x and c2=x and c4=x order by c3

C where c1=x and c4= x group by c3,c2

D where c1=x and c5=x order by c2,c3

E where c1=x and c2=x and c5=? order by c2,c3

1 create tablet4 (2 c1 tinyint(1) not null default 0,3 c2 tinyint(1) not null default 0,4 c3 tinyint(1) not null default 0,5 c4 tinyint(1) not null default 0,6 c5 tinyint(1) not null default 0,7 indexc1234(c1,c2,c3,c4)8 );9 insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);

对于A:

c1=x and c2=x and c4>x and c3=x  <==等价==> c1=x and c2=x and c3=x and c4>x

因此 c1,c2,c3,c4都能用上.如下:

1 mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3\G2 *************************** 1. row ***************************

3 id: 1

4 select_type: SIMPLE5 table: t46 type: range7 possible_keys: c12348 key: c12349 key_len: 4 #可以看出c1,c2,c3,c4索引都用上

对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3

c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.

而c4没发挥作用.

1 mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order byc3 \G2 *************************** 1. row ***************************

3 id: 1

4 select_type: SIMPLE5 table: t46 type: ref7 possible_keys: c12348 key: c12349 key_len: 2

10 ref: const,const11 rows: 1

12 Extra: Using where

13 1 row in set (0.00sec)14

15 mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order byc5 \G16 *************************** 1. row ***************************

17 id: 1

18 select_type: SIMPLE19 table: t420 type: ref21 possible_keys: c123422 key: c123423 key_len: 2

24 ref: const,const25 rows: 1

26 Extra: Using where; Using filesort27 1 row in set (0.00 sec)

对于 C:只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引

1 mysql> explain select * from t4 where c1=1 and c4=2 group byc3,c2 \G2 *************************** 1. row ***************************

3 id: 1

4 select_type: SIMPLE5 table: t46 type: ref7 possible_keys: c12348 key: c12349 key_len: 1#只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用10 ref: const11 rows: 1

12 Extra: Using where; Using temporary; Using filesort13 1 row in set (0.00sec)14

15 mysql> explain select * from t4 where c1=1 and c4=2 group byc2,c3 \G16 *************************** 1. row ***************************

17 id: 1

18 select_type: SIMPLE19 table: t420 type: ref21 possible_keys: c123422 key: c123423 key_len: 1

24 ref: const25 rows: 1

26 Extra: Using where

27 1 row in set (0.00 sec)

D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.因此,没用到filesort

1 mysql> explain select * from t4 where c1=1 and c5=2 order byc2,c3 \G2 *************************** 1. row ***************************

3 id: 1

4 select_type: SIMPLE5 table: t46 type: ref7 possible_keys: c12348 key: c12349 key_len: 1

10 ref: const11 rows: 1

12 Extra: Using where

13 1 row in set (0.00 sec)

E: 这一句等价与elect * from t4 where c1=1 and c2=3 and c5=2 order by c3;因为c2的值既是固定的,参与排序时并不考虑

1 mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order byc2,c3 \G2 *************************** 1. row ***************************

3 id: 1

4 select_type: SIMPLE5 table: t46 type: ref7 possible_keys: c12348 key: c12349 key_len: 2

10 ref: const,const11 rows: 1

12 Extra: Using where

13 1 row in set (0.00 sec)

上面就是一个比较经典的左前缀的匹配案例，因此我们在工作中应该在经常使用的列上加索引。