mysql 优化（2）索引优化策略

1:索引类型

索引： 作用快速查询；

  

  节点第1层  ，  2的0次方

  节点第1层  ，  2的1次方

  节点第3层  ，  2的2次方

  节点第4层  ，  2的3次方

  节点第5层  ，  2的4次方

.。。。

  。。。

    。。。

  节点第31层  ，  2的32次方

加起来 42亿

也就是说 42 亿个数字 最多查 32 次就可以了

普通查询要查21亿次

  

这就是-----》  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个.

alter table add index(cat_id)

alter table add index(price)

alter table add index(goods_id)              ---------------------------同时只能用一个  所以。。。。 联合索引  把多个列看成整体的值

index（cat_id ,goods_name, price）     --------------------------- 把多个列看成整体的值

 

 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,左前缀及范围查询,则木板长5米,

 

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

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

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

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

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