1 为什么是B+树

这一小节我不会和大家科普目前InnoDB目前支持Hash索引、全文索引他们目前是怎么使用，我主要会在这小节通过对比来看为什么B+树是我们目前常用的索引树

1.1 哈希索引

InnoDB是支持自适应Hash索引的，但是这个我不会展开说，在《MySQL技术内幕：InnoDB存储引擎（第2版）》中我们知道自适应Hash索引由数据库自身创建并使用，DBA本身并不能对其进行干预。所以建议我们可以查看当前的数据库的索引使用情况并且建议关闭。

# 查看数据库是否使用自适应hash索引
show variables like 'innodb_adaptive_hash_index' #如果value为ON则证明开启
# 设置自适应hash索引为off
set global innodb_adaptive_hash_index=off

这小节我也介绍一下，为什么更多情况下哈希索引不适合作为我们的索引来使用

  • 模糊查找不支持

哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话， 如果我们要进行模糊查找的话， 显然哈希表这种结构是不支持的， 只能遍历这个表。 

  • 范围查找不支持
  • 哈希碰撞问题

索引字段通过哈希映射成哈希码， 如果字段值都刚好映射到相同值的哈希码的话，我们称这样的情况为哈希碰撞，在数据库中我们我们常用链接法，即相同的哈希内容将为以链表串联在一起，这里又会引申一个问题就是，当碰撞的数据越来越多，那么查询的速度也会变慢。

1.2 二叉树 & B树

B+树作为我们现在索引常用的数据结构，它是通过二叉查找树、平衡二叉树、B树演化而来。

  • 二叉查找树

作为一种经典的数据结构，他的缺点也很明显，构造选用的根节点和数据的分布会很大的影响，树的查询速度。

  • 平衡二叉树

平衡二叉树定义必须满足任何节点的两个子树节点最大差为。他能有效的提高查询速度，但是维护一颗平衡二叉树的代价非常大。

  • B树

B树是一颗多路平衡查找树，不过在结构上，一方面因为B树的设计特点，非叶子节点都会携带数据因此在不同情况下不同的查询条件会使查询效率变得不稳定。另外一方面也是备受诟病就是我们经常说的回旋查询，当我们进行范围查询0005到0007的时候，当我们访问0005后之后还要退后回0006再进行0007的查询。

mysql支持hash缩影 mysql设置hash索引_数据

 因此在索引数据结构上，我们是否有更好的数据结构来做我们的数据库索引?

2 相对其他数据结构，我觉得B+树的优势

mysql支持hash缩影 mysql设置hash索引_数据_02

从我《非DBA人员从零到一，MySQL InnoDB数据库调优之路(一)-建表》给大家画了B+树的数据结构，从图中我们可以知道两点信息

  1. 所有数据会写入在叶子节点，因为数据位置固定，因此我们查询某条数据的效率是固定的，并不会出现B树因为数据在不同层级节点导致效率飘忽不定。
  2. 此外当我需要查询图片中范围内所有磁盘块，如果使用其他数据结构就会出现回旋问题，但是B+树的数据特点就是数据块中的索引项会进行排序，另外数据块之间可以通过指针去访问，从而解决了回旋问题。

3 explain索引分析

mysql支持hash缩影 mysql设置hash索引_数据_03

explain是我们去做索引分析的命令，我们通常根据explain的结果对SQL进行调优，下面我会对我以往关注的字段进行介绍

  • select_type 表示查询的类型。常用的值如下：
  • SIMPLE ： 表示查询语句不包含子查询或union
  • PRIMARY：表示此查询是最外层的查询
  • UNION：表示此查询是UNION的第二个或后续的查询
  • UNION RESULT：UNION的结果
  • SUBQUERY：SELECT子查询语句
  • DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。

最常见的查询类型是SIMPLE，一般使用我们使用别的查询类型的时候，我们考虑是否能在客户端或者业务上避免复杂类型的出现，这也是互联网开发中常用的优化思想。

  • type 表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。
  • ALL：表示全表扫描，性能最差。
  • index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。
  • range：表示使用索引范围查询。使用>、>=、<、<=、in等等。
  • ref：表示使用非唯一索引进行单值查询。
  • eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。
  • const：表示使用主键或唯一索引做等值查询，常量查询。
  • NULL：表示不用访问表，速度最快。

type字段用于判断我们使用的索引能使查询优化到什么程度

  • key 表示查询时真正使用到的索引，显示的是索引名称。

key字段用于判断在实际查询中是否使用了我们所建立的索引，因为索引由优化器进行选择，通过约束条件来引导优化器进行索引选取，通常能用于纠正我们的查询约束条件。

  • Extra Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
  • Using where 表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。这个一般发生在MySQL服务器，而不是存储引擎层。一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。
  • Using index 表示查询需要通过索引，索引就可以满足所需数据。
  • Using Index Condition 在MySQL 5.6版本后加入的新特性（Index Condition Pushdown）;会先条件过滤索引，过滤完索引后找到所有符合索引条件的数据行，随后用 WHERE 子句中的其他条件去过滤这些数据行；
  • Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。
  • Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作。

当进行explain出现Using where、Using filesort、Using temprorary时，我们就要考虑建立索引优化查询。

4 常见索引知识

4.1 覆盖索引

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

mysql支持hash缩影 mysql设置hash索引_mysql支持hash缩影_04

mysql支持hash缩影 mysql设置hash索引_数据_05

4.2 索引下推 

MySQL 5.6引入了索引下推：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

select * from tuser where name like '张%' and age=10 and ismale=1;

联合索引（name, age）

5.6之前的版本

mysql支持hash缩影 mysql设置hash索引_字段_06

在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。

5.6之后

mysql支持hash缩影 mysql设置hash索引_字段_07

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

5 你不知道的查询，但是又与索引相关的

5.1 如何优化“order by”

假设我们有这样的表，目前我们对city建立索引

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

当进行排序查询的时候

select city,name from t where city='杭州' order by name limit 1000 ;

我们的排序过程以如图所示进行

mysql支持hash缩影 mysql设置hash索引_MySQL_08

由上图可见MySQL 做排序是一个成本比较高的操作，有没方法可以减少order by的排序操作呢？通过了解我们知道，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的，又我们在第3小节知道，b+树会对索引下的数据节点的索引字段进行排序，当我们使用city和name建立联合索引的时候：

alter table t add index city_name(city, name);

在我们定位'杭州'数据的时候已经额外为我们的name字段进行排序，具体过程如下

mysql支持hash缩影 mysql设置hash索引_MySQL_09

因此我们可以把order by的优化问题看作为一个索引覆盖问题，本篇博文中我主要是对order by在索引下的运作方式，在《16 | “order by”是怎么工作的？-极客时间》有更详细的优化过程。

6 一些我们经常搜索的索引相关问题

使用In时是否使用索引？

不要过多索引

7 结语

索引在数据库调优中是一个庞大的内容体系，第一到第三小节我主要对索引本身的结构进行理论上的分析，第四到第六小节是我日常在工作中遇到的问题进行的总结，随着对知识的不断积累我会对本博客的内容不断的拓展和完善。