B-Tree索引的学习记录

本文摘自高性能MYSQL

假设有如下数据库：

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)
);

B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

全值匹配：

全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人

匹配最左前缀：

只根据last_name查找，即只使用索引的第一列

匹配列前缀：

只匹配某一列的值的开头部分，例如前面的索引可用于查找所有以J开头的人

匹配范围值：

例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人，这里也只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列：

前面提到的索引也可以用于查找姓为Allen，并且名字是字母K开头的人，即第一列全匹配，第二列范围匹配。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY 字句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name），则mysql只能使用索引的第一列。
• 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查询。例如有查询 WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob="1976-12-23",这个查询只能使用索引的前两列。