009 MySQL中B+Tree对索引列是顺序存储的，适合查找范围数据，匹配原则

学软件技术，读第一手资料，去官方网站：MySQL 5.7参考手册

也可以看看比较经典的书籍，如《高性能MySQL》等

---

1、索引列是多个列，这种索引是如何组织数据的存储的

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

在MySQL中建立上面一张表，索引中包含了last_name、first_name和dob列的值，下图显示了该索引是如何组织数据的存储的。

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

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

2、索引列是多个列，按照顺序存储，对如下类型的查询有效

2.1 全值匹配

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

2.2 匹配最左前缀

前面提到的索引可用于查找所有姓（last_name）为Allen的人，即只使用索引的第一列。

2.3 匹配列前缀

也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。

2.4 匹配范围值

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

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

前面提到的索引也可用于查找所有姓为Allen,并且名字是字母K开头(比如Kim.Karl等)的人。即第一列last_name 全匹配，第二列first_ name 范围匹配。

2.6 只访问索引的查询

B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。后面我们将单独讨论这种“覆盖索引”的优化。

2.7 ORDER BY 操作

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

3、索引列是多个列，按照顺序存储，对如下类型的查询无效

3.1 如果不是按照索引的最左列开始查找，则无法使用索引。

例如上面例子中的索引无法用于查找名字（first_name）为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。

3.2 不能跳过索引中的列。

也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名(first_ name)， 则MySQL只能使用索引的第一列。

3.3 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

例如有查询WHERE last_ name= ' Smith' AND first name LIKE 'J%' AND dob = '1976-12-23'，这个查询只能使用索引的前两列，因为这里LIKE是-一个范围条件(但是服务器可以把其余列用于其他目的)。如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。