MYSQL索引

索引是什么？

作为一个PHP程序员，面试的时候经常会谈到Mysql数据库，面试官经常问你会不会做数据库优化、查询优化之类的问题，每到这个时候，我的第一直觉都是建索引，因为建立一个有意义的索引，对查询速度的优化速度，可能是几十倍到几百倍甚至更多，索引到底是个什么东西呢，其实索引只是一个数据结构，这个数据结构可能是Hash(哈希)，也可能是B+树(B+ Tree)，还有其它的，但是这里不进行讲述。

索引分类

常见的索引有上述提到的哈希索引和B+ Tree索引，目前用得比较多的是B+ Tree，顺便提一下，数据库引擎Innodb的默认索引类型就是B+ Tree，哈希索引一般使用得比较少，只有一些特定的情况会使用。

了解B+ Tree索引建议先了解一下二叉树、完全二叉树、B-Tree和B+Tree，可以搜罗一下相关的视频或者文档看一下，个人建议是看视频，结合视频的动态讲解，会比较好理解一些。

建个表插入一些数据，后面举例子用

create table T(
	id int primary key,
	k int not null, 
	name varchar(16),
	index (k)
)engine=InnoDB;

插入以下数据

id	k	name
100	1	张三
200	2	李四
300	3	张三
400	1	张四

哈希索引的原理及优缺点

哈希索引就是对索引的值进行hash加密，然后得到一个hash码去对应相应的数据行的地址（就是一个HashMap），假如上述字段k建立的索引是哈希索引，那索引的数据结构是这样

1的hash值 => 第一行数据的地址
2的hash值 => 第二行数据的地址
3的hash值 => 第三行数据的地址
1的hash值 => 第四行数据的地址

1. 当执行 select * from T where k = 2 这句SQL语句的时候，就将2转换为hash值，然后去这份哈希表里查数据，这样子看起来数据还是挺快的，但是假如我们是要查 k<= 2 的数据的话，那哈希索引就没办法做到了，或者我们想要查 select * from T order by k 哈希索引也没办法实现排序的功能。

2. 假如现在表里有一百万条数据，其中五十万条的k值都是1，然后去查 k=1 的数据你就会发现存在大量的hash值一样的行，因此存在处理效率极低的情况。

3. 假如现在我们建了一个联合索引，索引字段是(k,name)，然后再执行 select * from T where k = 2 ，会发现这句sql没有使用到索引，因为哈希索引是不支持最左原则的，它的数据结构是这样的 k值和name值的hash值 => 数据行地址，所以如果只进行一个字段的查询，是匹配不到数据的

总结一下哈希索引的特点：

1. 只能进行等值查询
2. 不能进行排序
3. 不能进行范围查询
4. 存在哈希碰撞问题，导致效率低下
5. 联合索引不支持最左原则

那什么时候可以用哈希索引呢，当字段的值是唯一的时候，并且不存在以上不允许的查询方式，可以建立哈希索引

联合索引的最左原则

例如联合索引有三个索引字段（A,B,C）

查询条件：

（A，，）—会使用索引

（A，B，）—会使用索引

（A，B，C）—会使用索引

（，B，C）—不会使用索引

（，，C）—不会使用索引

B+ Tree索引

B+ Tree索引是要讲的重点内容，同样我觉得学后端的小伙伴很有必要了解一下这个索引，因为用得比较多，还是以字段k为例子，假如字段k的索引为B+ Tree，那数据结构如下：

相关的查询原理这里就不讲了，这个在学B+ Tree的时候大家都能明白，跟hash索引相对比，B+ Tree支持等值查询，范围查询，排序以及联合索引的最左原则

聚簇索引、非聚簇索引、覆盖索引

B+ Tree的叶子节点存放的数据会根据索引不同而不同，主键索引的叶子节点数据存放的是改行数据，而普通索引的叶子节点数据存放的是行数据的主键值（如果该表没有主键，数据库会以隐藏的方式建一个自增的字段）。

当我们查主键时，直接从主键索引上取数据即可，这种就叫做聚簇索引，但当我们查普通索引时，是先取得主键值，然后再去主键索引的B+ Tree上去查找，这个过程叫做回表，但不是所有普通索引都需要回表操作。

假如：select k from T where k <= 2

这个查询的值在普通索引本身就能查到，所以就不需要再回表查询，这种情况较索引覆盖。

索引下推

索引下推跟联合索引有关，假如我们现在新建了一个联合索引(k, name)，然后我们执行 select * from T where k = 1 and name = ‘张三’

• 数据库5.6以前：
  当执行这句查询语句时，会先从联合索引上查到 k=1 的主键，然后再去主键索引上查到相关的行数据（回表操作，这里需要回表2次），再去比对name字段的值

• 数据库版本 >= 5.6：
  执行这个查询语句时，会直接从联合索引上匹配出 k=1 并且 name = ‘张三’ 的主键，然后再回表查询得到数据，可以看出这里只需要回表一次，减少了回表次数提升了查询效率。

查询优化器

创建的索引有时候并不会生效，一般我们可以通过EXPLAIN语句查看语句是否有使用到索引，但是为什么创建的索引会用不上呢，这就跟查询优化器有关了，Mysql数据库执行语句的过程如下

客户端 => 连接器（管理连接，权限校验） => 分析器（词法解析，语法解析） => 优化器（执行计划生成，索引选择）=> 执行器（操作引擎，返回结果） => 存储引擎Innodb（从硬盘取数据）

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。

在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下：

1、根据搜索条件，找出所有可能使用的索引

2、计算全表扫描的代价

3、计算使用不同索引执行查询的代价

4、对比各种执行方案的代价，找出成本最低的那一个