高性能MYSQL---索引（一）

• 索引

在数据库中也称作是key，是存储引擎用于快速找到记录的一种数据结构。其实索引可以理解为书的目录，就好比我们看书的时候会先找书的目录，通过目录找到对应的记录，而索引也是如此。数据库的存储引擎会先使用索引找到对应的值，然后根据匹配的索引记录找到对应的数据行。
例如： SELECT * FROM sakila.actor WHERE actor_id = 5;
我们在actor_id的列上建立索引，这样子数据库引擎查询的时候就会现在索引上按值进行查询，然后返回所有包含该值的数据行

索引也不是越多越好，当数据量大的时候，合适的索引会提高效率，不恰当的索引会使性能急剧下降。即使使用的使ORM技术，也应该考虑索引

1. 索引基础

1.1 索引的类型

首先我们需要注意的一点是索引是在存储引擎层实现的而不是服务器层实现的。所以没有统一的索引标准。也就是说：不同的索引引擎的工作方式并不相同，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，但是其底层的实现也可能不相同。

• B-Tree索引

这种索引就是我们通常所说的索引，如果没有明确指明类型，大部分说的就是这种索引。使用B-Tree的数据结构来存储数据。大多数MYSQL引擎都是支持这种类型的索引的。

注意：
Archive这种数据引擎是一个例外，在5.1之前，这种数据引擎不支持任何索引，直到5.1之后才开始支持单个的自增列索引。

其实我们说的B-Tree，底层的存储引擎可能使用的是不同的存储结构。

NDB集群存储引擎内部使用的是T-Tree，InnoDB则使用的是B+Tree，但是他们对外都叫做是B-Tree索引

因为存储引擎实现索引的方式不同，所以效率也会不同。

MyISAM使用前缀压缩技术使得索引更小，但InnoDB则按照原数据格式存储。
MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则是根据主键引用被索引的行。（所以InnoDB一定要有主键）

B-Tree通常表示了所有的值都是按照顺序存储的，并且每一个叶子页到根的距离是相同的。

下图是InnoDB的B-Tree的索引抽象表示

B-Tree索引可以加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取数据，应该从索引的根节点进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。

树的深度和表的大小直接相关，上图只描述了一层根节点和叶子页，其实中间应该有很多层。并且叶子节点指的是被索引的数据，而不是其他的节点页。

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

全值匹配
全值匹配指的是和索引中的所有的列进行匹配。比如查找姓名是Allen的人。

匹配最左前缀
只使用索引的第一列。

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

匹配范围值
可以查找某一个值在一个范围之间的人。这里也是只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列
例如可以查找出所有的姓为Allen，名字是K开头的人。即第一列是全匹配，第二列是范围匹配。

只访问索引的查询
就是说查询的时候只需要访问索引，无需访问数据行。

索引都是有序的，所以索引还可以用于order by的操作（按顺序查找）。如果order by子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

• B-Tree索引的限制

1. 如果不是按照索引的最左列开始查找，则无法使用索引。（类似于复合索引，查找的时候必须用到最开始定义索引的地方）
2. 不能跳过索引的列；比如建立复合索引A,B,C，不能查找条件是A和C的，这样子只会使用到A索引
3. 如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询（like between > <都属于范围查询）
   eg。WHERE last_name = ‘Smith’ AND first_name LIKE ‘J%’ AND dob = '1997-12-23’这种情况只能使用索引的前两列。通常这种情况可以使用多个等于条件来代替范围条件。

总结

其实上面的索引顺序可以这么理解，建立了复合索引（A,B,C）就是相当于建立了
A索引
A,B索引
A,B,C索引
一个复合索引（A,B,C）就相当于建立了三个索引

索引列的顺序相当重要，索引存在限制，这些限制是MYSQL优化器和存储引擎使用索引的方式导致的。