索引研究：（二）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文件排序。