MySQL索引

首先，什么是索引？我们假设下面一个场景，当你拿到一本很厚的工具书进行有目的的查找内容的时候，你会怎么做？你肯定不会对着这本书从头到尾地去找你想要找的内容（虽然这一定也可以找到），因为这太耗费时间了。你会做的肯定是先查找书本的目录，找到你想要阅读的章节的页码，然后再到对应的页码去查找你想要的具体的内容，这显然是一种快得多的方案，特别是在书本的页数非常多的情况下。同理，数据库的索引扮演的就是一种类似与目录的角色，它通过使用一些特殊的数据结构对数据库的数据进行一些特殊的排列，使得当我们在按照一定的要求查找数据的时候能够获得更快的相应。

一、索引的类型

B-Tree索引

B-Tree索引是当前使用最广泛的一种索引的形式，他的底层实现其实是一种叫B+Tree的数据结构，这是一种特殊的树型数据结构。首先我们看看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)
);

上面的sql语句创建了一个表，并且对表中的last_name,first_name和dob列生成了索引：

我们可以看到这个索引中的数据就会按照我们给定的要求，对last_name,first_name和dob三列进行排序存储，值得注意的是，我们观察途中最右下角的两个数据节点，会发现他们的last_name,first_name列的内容都是一样的，便按照dob进行排序，即对于一个多列的索引，排序也是有优先级的，这个优先级就是我们在定义索引的时候的顺序。
而如果在一个使用了B-Tree索引，我们使用怎样的查询方式能够支持呢？

可以使用B-Tree索引的查询类型：

• 全值匹配

全值匹配指的是和索引中的所有列进行匹配，如上述条件中，查找一个姓名为Cuba Allen，出生于1960-01-01的人。

• 匹配最左前缀

即对索引的第一列进行查找，上面例子的索引的第一列是last_name，比如我们要查找一个姓为Allen的人。

• 匹配列前缀

也可以只匹配某一列的开头部分，如查找一个姓开头为J的人，这里也使用了索引的第一列。

• 匹配范围值

例如我们可以查找，姓为Allen和Barrymore之间的人，这里也使用了索引的第一列。

• 精确匹配某一列，范围匹配另外一列

我们可以查找姓为Allen，名是K开头的人，即索引第一列全匹配，第二列范围匹配。

B-Tree索引的一些限制：

• 如果不是按照最左列进行查找，则无法使用索引

例如，上述索引无法用于匹配名字为Bill的人，这种情况下会退化为全表扫描。

• 不能跳过索引中的列

例如，上述索引无法用于匹配一个姓为Allen且生日在1960-01-01的人，即必须按照从左到右的顺序对索引的列进行匹配，不能中间跳过。

• 如果对索引中的一个列进行范围查找，则它右边的列都无法利用索引进行匹配

如前面例子中我们匹配一个查找姓为Allen，名是K开头的人，因为first_name已经是范围匹配了，因此它右边的dob列就无法利用索引了。

我们从上述的限制可以看出，对于B-Tree索引，索引的顺序会对这个索引的作用的发挥和我们查询语句的书写有着非常大的影响。

哈希索引

哈希索引包含两个数据，一个是哈希值，一个是指向数据行的指针。哈希值是通过数据的每一列进行哈希计算得出的。因此每一行的数据都有一个独一无二的哈希值。因为哈希索引只存储哈希值，因此结构可以存储得比较紧凑，也因此提高了哈希索引的速度。
但是哈希索引也有它的限制：

• 哈希索引的数据是按照哈希值排列的而并不是按照数据本身排列的，因此不能用于排序。
• 哈希索引不支持部分索引列的匹配。因为哈希值是根据数据的每一列计算得出的，要得到哈希值就必须要有全部索引列的数据，因此如果一个数据有A,B两列，此时只有一个数据A是无法通过哈希索引匹配到相关的数据的。
• 哈希索引只支持等值比较，不支持范围查询。
• 当出现哈希冲突（即多个数据计算出来的哈希值相同）的时候，这些数据以链表的形式存储在对应的哈希值下。
• 哈希冲突很多的时候，会对哈希索引的效率造成比较大的影响，特别是在要删除一行数据的时候。

二、索引的优点

索引可以让服务器快速定位到我们需要的表的位置。但这并不是索引带来的唯一的好处。总结下来索引的优点主要有以下三个：

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