MySQL索引

#什么是索引
要理解MySQL中索引是如何工作的，最简单的办法就是看一本书的“索引”部分，就像给你一本新华字典，然后要你找出“爵”字，具体做法是根据拼音找出j开头的部分，然后再进一步定位到“爵”的页码，从而找出“爵”。MySQL索引的工作原理也一样。
#B-Tree索引
当谈论索引的时候，如果没有特别申明，那多半是指`B-Tree`索引。B-Tree树意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。在innoDB存储引擎中，抽象可以用以下图表示B-Tree树索引
<img src="https://leanote.com/api/file/getImage?fileId=5724bfccab64413fd7001875" width="500">
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)
)
```
##二级索引组织方式
则在B-Tree索引中，因为建立了一个last_name，first_name，dob的索引，注意key申明的索引为`二级索引`。数据的组织方式可以用以下图表示
<img src="https://leanote.com/api/file/getImage?fileId=5724c0edab64413fd7001877" width="500">
特别注意的是，索引对多个值进行排序是按照create table定义语句中的定义索引的顺序来的，如果第一列一样，则排第二列，以此类推。按照这个数据组织方式，B-tree索引适合`全键值`，`键值范围`，`键值前缀`查找
以下查询都可以使用到索引
1. 查询一个姓名为Allen Cuba并且生日为1960-01-01的人
2. 查询所有姓Allen的人
3. 查询所有以A为开头自行姓名的人; last_name like 'A%'
4. 查询姓Allen和Balnger之间的人
5. 查询姓Allen并且以字母C开头的人; lastname=Alllen and first like 'C%'
##聚族索引
在InnoDB中，通过`primary key`申明的为主键索引，也就是`聚族索引`的索引列。聚族索引不是一种单独的索引类型，它是一种数据组织方式。下图可以展示聚族索引下InnoDB引擎中数据的组织方式
<img src="https://leanote.com/api/file/getImage?fileId=5724d9e0ab64413fd7001930" width="500">
这就是InnoDB中数据组织方式，索引列就是我们的建表时候定义的主键，所以，在InnoDB引擎中，必须要有主键，如果没有，MySQL会帮我们创建一个不可见的主键。因为没有主键，InnoDB中的数据就没有办法组织
#覆盖索引
由于MySQL中InnoDB独特的组织数据的方式，可以说，很大部分的查询都需要走`二次查询`才能找到正确的数据行，第一次通过我们的二级所以找到主键（`聚簇索引`），第二次通过主键找到我们需要的数据行。当我们需要查询的数据就是索引列的数据时，只需要一次查询就可以返回我们需要的结果，因为索引列中包含了我们需要的数据，这在InnoDB中称为`覆盖索引`。比如,假设一张表的user中包含列名为name的二级索引,下面这个SQL需要进行二次查询
```
select * from user where name='yongxiong'
```
然而下面这条语句使用`覆盖索引`只进行一次查询
```
select name from user where name='yongxiong'
```
#哈希索引
哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同健值的行计算出来的哈希码是不一致的。
在MySQL中只有Memory支持哈希索引，假设有如下表定义：
```
create table testhash(
    fname varchar(50) not null,
    lname carchar(50) not null,
    key using hash(fname)
)engine=memory
```
假设表中插入以下数据：
<img src="https://leanote.com/api/file/getImage?fileId=5724c64fab644141b10017f4" width="300">
假设索引使用的哈希函数伪f(x)，则返回下面的值
<img src="https://leanote.com/api/file/getImage?fileId=5724c6a7ab644141b10017f9" width="300">
则哈希索引的数据结构如下：
<img src="https://leanote.com/api/file/getImage?fileId=5724c6e3ab64413fd70018b2" width="300">
在hash索引的基础上，比如我们做下面这个查询
```
select * from testhash where fname='Peter'
```
MySQL会先计算'Peter'的hash值，并使用该值找对应的指针。因为f('Peter') = 8784，所以MySQL在索引中寻找8784，可以找到指向第三行的指针。最后一步是比较第三行的指针是否为'Peter'，确保我们需要寻找的行。基于哈希索引的这宗结构，我们可以得出如下结论：
1. hash索引只包含hash值和索引行，所以不能使用索引中的值来避免读取行
2. 哈希索引的值无法用于排序，因为存储的值不是按照行的顺序
3. 哈希索引不支持部分索引列查找
4. 哈希索引只能用于等值比较查询，不支持任何范围查询
虽然hash索引有非常多的缺点，但是我们经常使用的InnoDB索引吸取了这种索引的精华部分，所以InnoDB引擎有一个特殊的功能`自适应哈希索引`，当InnoDB注意到某些索引值使用非常频繁时，它会在内存中基于B-tree之上再建立一个哈希索引，这样利用hash的快速查找可以加快访问速度。注意InnoDB的这种行为是内部的，我们无法干预。
#全文索引
全文索引是一种特殊类型的索引，他查找的是文本中的关键字，而不是直接比较索引中的值。全文索引更类似于搜索引擎要做的事情，而不是直接使用where条件查找。常见的例子就是搜索文章中的关键字，这种情况下MySQL中的索引就显得有些吃力。不过，目前市面上也有解决全文索引的方案，`Sphinx`就是在这种场景下横空出世的。
#InnoDB查询
在InnoDB引擎中，一个简单的查询可能会经过以下步骤
![title](https://leanote.com/api/file/getImage?fileId=582ddaf7ab644137810021f6)

Navigation

Recent Posts

Friend Links