索引优化策略

索引类型

1.B-tree索引

  注:名叫btree索引,大的方面看,都用的平衡树,但具体的实现上,各引擎稍有不同,

比如,严格的说,NDB引擎,使用的是T-tree

  Myisam,innodb中,默认用B-tree索引

 

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

 

2 hash索引

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

hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,

比主键为id为例,那么随着id的增长, id对应的行,在磁盘上随机放置.

左前缀原则

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

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

以 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是,使用了abcWhere b=3 / where c=4否Where a=3 and c=4a列能发挥索引,c不能Where a=3 and b>10 and c=7A能利用,b能利用, C不能利用同上,where a=3 and b like ‘xxxx%’ and c=7A能用,B能用,C不能用

filesort 二次排序

一般而言，分组统计要先按分组字段，有序排列

用临时表来排序

联合索引反而快了，主键索引反而慢了 ----》 聚簇索引，索引覆盖 ， innodb与myisam索引的区别

innodb的次索引指向对主键的引用

myisam的次索引和主索引 都指向物理行

Myisam

存在物理行中。。。。。。

主键索引 普通索引

innodb的主索引文件上，直接存放该行数据，称为聚簇索引，次索引指向对主键的应用；

myisam中，主索引和次索引，都指向物理行

注意:innodb来说,

1:主键索引 既存储索引值,又在叶子中存储行的数据

2:如果没有主键,则会Unique key做主键

3:如果没有unique,则系统生成一个内部的rowid做主键.

4:像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

高性能索引策略

0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.

对于innodb的主键,尽量用整型,而且是递增的整型.

如果是无规律的数据,将会产生的页的分裂,影响速度.

实验innodb 顺序和乱序的执行效率

索引覆盖:

索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.

这种查询速度非常快,称为”索引覆盖”

如果有 Using index则说明是索引覆盖

innodb沿着主键走，因为是在叶子上，如果挂的数据比较多的情况下，查询数据跨的块太多，所以查询的比较慢，但是如果在innodb上是复合索引的话，id.ver 是二级索引，是次索引，所以是按照所以来走，因此查询的比较快，不是主键索引的话，就没有下面的数据块，只取id，不需要去id下面的数据。因为myisam是存在磁盘上，所以两者差别很小。

索引的长度和区分度是相互矛盾的，索引长度越长，区分度越高，所以应该，分析数据，寻找最合适的位置。

用crc函数来构造伪哈希列

把字符串的列，转成整形，来降低索引的长度，从而提高查询效率