MySQL中的索引----B-Tree索引

B-Tree索引使用B-Tree数据结构来存储数据，这意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。下图展示了B-Tree索引的抽象表示：

B-Tree索引之所以能加快访问数据的速度，是因为存储引擎不需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。能够看到会通过不停比较来向下寻找，最后要么找到，要么判断不存在。其中的叶子节点较特别，他们的指针是直接指向被索引的数据，而不是其他的节点页。

B-Tree对索引列时顺序组织存储的，所以很适合查找范围数据。例如有如下数据表，表中每一行数据索引都包含了last_name、first_name和dob列的值，下图则展示了该索引如何存储数据的。

CREATE TABLE People (
	last_name varchar(50) not null,
	first_name varchar(50) not null,
	dob date not null,
	gender enum('m', 'f')not null,
	key(last_name, first_name, dob)
);

要知道索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都一样，则根据他们的出生日期来排序。

接下来还要说一下可以使用B-Tree索引的查询类型。B-Tree索引适合用于全键值、键值范围或键前缀查找，而键前缀查找只适用于根据最左前缀的查找。也就是说除了这些查找外，其他类型的查找可能不会使用索引，或只能使用部分索引。

• 全值匹配：指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。
• 匹配最左前缀：前面的索引可用于查找所有姓为Allen的人，即只是用索引的第一列
• 匹配列前缀：只匹配某一列的值的开头部分。前面索引可用于查找所有以J开头的姓的人。也只用了索引的第一列。
• 匹配范围值：前面的索引可查找姓在Allen和Barrymore之间的人。这里也只是用了索引的第一列。
• 精确匹配某一列并范围匹配另一列：前面索引可查找所有姓为Allen，并且名字是字母L开头的人。即第一例last_name全匹配，第二列first_name范围匹配。
• 只访问索引的查询：查询只需要访问索引，而无需访问数据行。

当然既然索引树中的节点是有序的，除了能按值查找外还可用于查询中的ORDER BY操作。即若B-Tree能按照某种方式查找到值，那也能按照这种方式用于排序。所以上面几种查询类型对于排序也是可以满足的。

当然B-Tree索引有一些限制：

• 若不是索引的最左列开始查找，则无法使用索引。例如上面例子中索引无法查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。前面的索引无法查找姓为Smith并且在某个特定日期出生的人。若不指定名，则MySQL只能使用索引的第一列。
• 若查询中有某个列的范围查询，则其右边所有列都是无法使用索引优化查找。例如有查询WHERE last_name=‘Smith’ AND first_name LIKE ‘J%’ AND dob = ‘1976-12-23’，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。若范围查询列值的数量有限，可通过使用多个等于条件来代替范围条件。

所以索隐列的顺序是很重要的，优化性能的时候可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。