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) 为例,
语句 索引是否发挥作用
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 / 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列的索引不发挥作用.
多列索引经典题目:
http://www.zixue.it
/thread
-9218-1-4.html
假设某个表有一个联合索引（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
create table t4 (
c1 tinyint(
1)
not null default 0,
c2 tinyint(
1)
not null default 0,
c3 tinyint(
1)
not null default 0,
c4 tinyint(
1)
not null default 0,
c5 tinyint(
1)
not null default 0,
index c1234(c1,c2,c3,c4)
);
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都能用上. 如下:
mysql> explain
select * from t4
where c1
=1 and c2
=2 and c4
>3 and c3
=3 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: range
possible_keys: c1234
key: c1234
key_len: 4 #可以看出c1,c2,c3,c4索引都用上
ref: NULL
rows: 1
Extra: Using where
对于B: select * from t4
where c1
=1 and c2
=2 and c4
=3 order by c3
c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
而c4没发挥作用.
mysql> explain
select * from t4
where c1
=1 and c2
=2 and c4
=3 order by c3 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row
in set (
0.00 sec)
mysql> explain
select * from t4
where c1
=1 and c2
=2 and c4
=3 order by c5 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where; Using filesort
1 row
in set (
0.00 sec)
对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
mysql> explain
select * from t4
where c1
=1 and c4
=2 group by c3,c2 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
ref: const
rows: 1
Extra: Using where; Using
temporary; Using filesort
1 row
in set (
0.00 sec)
mysql> explain
select * from t4
where c1
=1 and c4
=2 group by c2,c3 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row
in set (
0.00 sec)
D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
因此,没用到filesort
mysql> explain
select * from t4
where c1
=1 and c5
=2 order by c2,c3 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 1
ref: const
rows: 1
Extra: Using where
1 row
in set (
0.00 sec)
E: 这一句等价与 elect * from t4
where c1
=1 and c2
=3 and c5
=2 order by c3;
因为c2的值既是固定的,参与排序时并不考虑
mysql> explain
select * from t4
where c1
=1 and c2
=3 and c5
=2 order by c2,c3 \G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: t4
type: ref
possible_keys: c1234
key: c1234
key_len: 2
ref: const,const
rows: 1
Extra: Using where
1 row
in set (
0.00 sec)
转载于:https://www.cnblogs.com/hgj123/p/5011412.html
