索引类型

1. 索引类型

1.1. B-tree索引

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

 

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

1.2. Hash索引

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

1.3. Hash的查找如此高效,为什么不都用Hash索引?

1:hash函数计算后的结果是随机的,如果是在磁盘上放置数据,比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.

2: 无法对范围查询进行优化.

3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引

查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)

而hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机

4: 排序也无法优化.

5: 必须回行.就是说通过索引拿到数据位置,必须回到表中取数据

1.4. B-tree索引的常见误区

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

  例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品

  误: cat_id上,和, price上都加上索引.

  错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.

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

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

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

2. 聚簇索引与非聚簇索引

2.1. Myisam与innodb引擎,索引文件的异同

MyISAM索引实现

MyISAM引擎使用B树作为索引结构，叶节点的data域存放的是数据记录的地址。

MyISAM主键索引

这里设表一共有三列，假设我们以Col1为主键，Col2为辅助索引。则下图是一个MyISAM表的主索引（Primary key）和辅助索引（Secondary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。则此索引的结构如下图所示：

InnoDB索引实现

InnoDB使用B+Tree作为索引结构。

 

1.InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按【主键聚集】，所以InnoDB要求表必须有主键（Col1），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

 

2.InnoDB的所有【辅助索引】都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

下图为定义在Col3上的一个辅助索引：

 

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

myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,

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

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

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

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

2.2. 聚簇索引 

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

聚簇索引使用随机值导致页频繁分裂影响速度

1: innodb的buffer_page 很强大.

2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,

3: 不要用随机字符串或UUID，否则会造成大量的页分裂与页移动.