MYSQL学习笔记之索引

首先先看一下索引这个词在词典中的定义：指将文献中具有检索意义的事项（可以是人名、地名、词语、概念、或其他事项）按照一定方式有序编排起来，以供检索的工具书。

从上面的标红的关键字来看索引有两个特点：

1、索引是供使用者搜索时所用的工具。

2、索引是有序的

在MYSQL中索引是存储引擎快速找到记录的一种数据结构。

一、B-Tree索引：

首先假设有一个索引为（name,age,birthday）

适用于全键值、键值范围、键前缀查找。

下面的为B-Tree索引的有效范围：

1、全值匹配

是指对索引中的所有列进行匹配。如查找姓名为张三，年龄为20，出生日期为1990-02-11的人。

2、匹配最左前列：

即只使用索引的第一列。例如查找名为张三的人。

3、匹配列前缀

只匹配某一列的开头部分。可以查找所有姓张的人。这里也只用了索引的第一列。

4、匹配范围值

索引可以用于查找姓名在李四与张三之间的人(注意这里只是类别，实际中只对英文范围查找)。这里也只用了索引的第一列。

5、精确匹配某一列并范围匹配另一列

姓名为张三，年龄在10-20岁之间的人。

6、只访问索引的查询

查找的列只有name,age,birthday。这就是我们常说的索引覆盖。

B-Tree索引的限制：

1、如果不是按照索引的最左列查找，则无法使用索引。如无法查找年龄为20岁，生日为1990-02-11。

2、不能跳过索引中的列。如查找姓名为张三，出生日期为1990-02-11的人。

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

B-Tree索引是有序的，所以除了查找值外，索引还可以用于查询中的 ORDER BY 操作。

哈希索引：

哈希索引是基于哈希表实现的，存储引擎会对所有的索引列计算一个哈希码，不同的键值行计算出的哈希码也不一样，哈希索引将所有的哈希码存储在索引中，在mysql中只有Memory引擎显示的支持哈希索引。

如果多个列的哈希值相同，索引会以链表的方式存放多个记录的指针到同一哈希条目中。当查询语句计算出的哈希值相同时（哈希冲突），会遍历所有链表对比where语句中的值，如果相同则取出。(SELECT name,age From user WHERE name="张三")

哈希索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不存在索引覆盖来避免读取行。

哈希索引数据并不是按照索引值顺序存储的，所以无法用于排序。

哈希索引也不支持部分索引列进行查找，因为哈希索引的哈希值是将索引列一起起算得出的，例如（name,age）不能单独使用name 或者age查找。

哈希索引只支持等值查找。

发生哈希冲突很多的话，一些索引维护代价也会很高。