Mysql中B+Tree索引相关知识点

索引有很多种类型，可以为不同的场景提供更好的性能。在Mysql中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准：不同存储引擎的索引工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层实现也可能不同。

实际上很多存储引擎使用的是B+Tree，Mysql5.x后，默认使用的InnoDB存储引擎使用的也是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)
)

索引中包含了last_name,first_name,dob三个列的值，下图显示了该索引是如何组织数据的存储的：

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

B-Tree索引使用与全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

• 全值匹配
  对索引中的所有列进行全匹配条件。
• 匹配最左前缀
  只使用索引的第一列做匹配，如：查找所有姓为Allen的人。
• 匹配列前缀
  也可以只匹配某一列（必须符合以上匹配最左的前提）的值的开头部分（不难理解，因为字符串的比较是从左往右进行的）。
• 匹配范围值
  第一列索引（如例子中的last_name）做范围匹配。
• 精确匹配某一列并范围匹配另外一列
  第一列last_name全匹配，第二列first_name范围匹配。

由以上的规则，可总结出B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引，类似地，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。
• 如果查询中由某个列的范围查询，则其右边所有列都无法使用索引优化查找。

因此，索引列的顺序非常重要，这些限制都和索引列的顺序有关

索引的优点

总结下来，索引有如下三个优点：

1. 索引大大减少了服务器需要扫描的数据量。
2. 索引可以帮助服务器避免排序和临时表。
3. 索引可以将随机I/O变为顺序I/O。

结语

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增大。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条几里路一条记录地匹配。例如可以使用分区技术（本文不做介绍）。