索引研究:(二)B-Tree索引

  • 时间:
  • 浏览:
  • 来源:互联网

索引研究:(二)B-Tree索引:

B-Tree索引

InnoDB使用的是B+Tree。

B+Tree:每个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历。

B-Tree:通常意味着所有的值都是按照顺序存储的,并且每个叶子页到根的距离相同,很适合查找范围数据。

B-Tree:可以对<, <=,=,>,>=,BETWEEN,IN,以及不以通配符开始的Like使用索引。

索引查找

可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询,但是必须保证按索引的最左边前缀(LeftMost prefix of the index)来进行查询:

  1. 查询必须从索引的最左边开始的列开始,否则无法使用索引。例如你不能利用索引查找在某一天出生的人。
  2. 不能跳过某一索引列。例如,你不能利用索引查找last_name为Smith且出生于某一天的人。
  3. 存储引擎不能使用索引中范围条件右边的列。例如:如果你的查询语句为WHERE last_name = “Smith” AND first_name like ‘J%’ AND dob ='1976-12-23 ',则该查询只会使用索引中的前两列,因为LIKE是范围查询。

查询的几种常见情况:

  1. 匹配全值(Match the full value):对索引中的所有列都制定具体的值。例如,上图中索引可以帮助查找出生于19960-01-01的Cuba Allen。
  2. 匹配最左前缀(Match a leftmost prefix):你可以利用索引查找last_name 为 Allen 的人,仅仅使用索引中的第一列。
  3. 匹配列前缀(match a column prefix):可以利用索引查找last_name以J开始的人,这仅仅使用索引中的第一列。
  4. 匹配值的范围查找(Match a range of values):可以利用索引查找last_name 在Allen 和Barrymore 之间的人,仅仅使用索引中的第一列。
  5. 匹配部分精确而其他部分进行范围匹配(Match one part exactly and match a range on another part): 可以利用所以查找last_name为Allen,而first_name以字母k开始的人。
  6. 仅对索引进行查询(Index-only queries):如果查询的列都位于索引中,则不需要再多一次I/0回读元组。(覆盖索引:索引的叶子节点中已经包含要查询的数据,那么就没有必要再回表查询了,如果索引包含满足查询的所有数据,就成为覆盖索引)

索引排序:

也可以利用B-Tree索引进行索引排序(对查询结果进行ORDER BY),必须保证ORDER BY按索引的最左边前缀(LeftMost prefix of the index)来进行。

MySql中,有两种方式生成有序结果集:

  • 使用filesort
  • 按索引顺序扫描

如果explain出来的 type列的值为“index”,则说明MYSQL使用了索引扫描来做排序。

按索引顺序扫描:

可以利用同一索引同时进行查找和排序操作:

  • 当索引的顺序与ORDER BY中的列顺序相同,且所有的列是同一方向(全部升序或者降序)时,可以使用索引来排序。
  • ORDER BY子句和查询型子句的限制是一样的:需要满足索引的最左边前缀的要求,有一种情况下ORDER BY子句可以不满足索引的最做前缀要求,那就是前导列为常量时:WHERE子句或者JOIN子句中对前导列指定了常量。

能够使用索引的排序方式

Key a_b_c (a,b,c)

order by 能使用索引最左前缀
// 直接使用
order by a
order by a,b
order by a,b,c
// 一致的降序
order by a desc,b desc,c desc

如果WHERE使用索引的最左前缀定义为常量,则 order by 能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const order by b
where a = const and b > const order by b,c

不能够使用索引进行排序

Key a_b_c (a,b,c)

// 排序不一致
order by a ASC , b DESC , c DESC  
// 丢失a索引
where g = const order by b,c
// 丢失b索引
where a = const order by c
// d不是索引的一部分
where a = const order by a,d
// 由于排序来说,多个相等条件也是范围查询
where a in (...) order by b,c

如果查询时连接多个表,晋档order by 中所有列都是第一个表的列时才会使用索引。其他情况都会使用filesort文件排序。

本文链接http://metronic.net.cn/metronic/show-22236.html