mysql索引详解（十分钟时间搞定）

一、简述索引发展过程

为什么数据库要有索引？
索引其实就是它的“目录”。
一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，那我估计你可得找一会儿。就像你看到我这篇帖子一样，查找完全随缘。
一个数据库的索引到底应该怎么设计呢？

1. 早期的索引底层设计其实是一个hash表结构，设计出一个hash函数，根据函数求出相应的值即为hash表对应的键值，但是根据数据结构查找章节的内容，我们知道，其实利用hash表来存储数据，对于hash函数的设计要求非常高，对于数据量比较大一不小心就会产生大量的冲突，再利用线性探测法、拉链法去解决冲突，耗时耗力，完全违背了我们简单查询的目的。
   

2. 后来，我们想到可以用二叉树来解决啊！当有新结点的产生，我们就把他插入到树中去。但是二叉树又没有顺序，我们就选择设计一个二叉排序树去解决，数据左小右大，暂时解决！
   

3. 但是后来我们又发现，当数据量过大，二叉排序树一直插入节点，会出现其中某些子树的深度变得特别的深，导致在搜索的时候出现查询最深节点的数据时，要经过整个树的高度。有点小烦！于是我们又引入了平衡二叉树，来降低树的高度，但是又引发了新问题，平衡二叉树节点只能插入一条数据，会不断的进行平衡旋转，还是有点烦！

4. 之后，我们就引入B树来解决上面问题，虽然B树解决了节点存储，不断调整平衡的问题，但还是有点小瑕疵，比如范围查找，当我找到一个值时，不能最大效率的找到他前后的N个值。
   

5. 最后引入了B+树来解决这种问题，让搜索的效率变得更高效。
   

二、索引分类

• 从功能逻辑上说，索引可以分为：普通索引、唯一索引、主键索引、全文索引。
• 按照物理实现方式，索引可以分为：聚簇索引和非聚簇索引。
• 按照作用字段个数进行划分，分成单列索引和联合索引。

三、索引介绍

1. 普通索引和主键索引

（1）简述
- 其中主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引。
- 非主键索引也即普通索引，它的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引。
- 普通索引和主键索引两个要放在一起进行介绍，因为他俩涉及到回表问题，下面会说到。
（2）创建过程
一般一张表设计出来的时候他的主键就已经是确定的，因此主键索引也是默认创建好的，如下：

create table iam_group
(
    id                  varchar(255) not null
        primary key,
    name                varchar(255) null,
    parent_group_id     varchar(255) null,
);

普通索引：

create index iam_group_index
    on iam_group (parent_group_id);

其中iam_group_index为索引名，iam_group为表名，parent_group_id为要在表的这个列上加索引

2. 单列索引和联合索引

• 上面普通索引的举例就是单列索引，只在单列上加了索引。
• 联合索引是在多列上加了索引，举例：

create index IDX_AM_ACCESS_LOGIN_NAME_UNION
    on iam_user (login_name, type, group_id);

表示对iam_user这张表的login_name，type，group_id加了一个联合索引

四、不得不提的索引高阶

首先我们先创建一张表，进行举例：

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

说明：上面新建了一张表，主键是ID，列有k和s，k上加了普通索引，然后又插入了一批数据。
这个时候创建出来的索引是这样的结构

1.回表

首先和大家探讨下回表的问题，在表 T 中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
我们一起来看看这条 SQL 查询语句的执行流程：

• 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
• 再到 ID 索引树查到 ID=300 对应的 R3；
• 在 k 索引树取下一个值 k=5，取得 ID=500；
• 再回到 ID 索引树查到 ID=500 对应的 R4；
• 在 k 索引树取下一个值k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。

2. 覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

3. 最左前缀原则

假如为每一种查询都设计一个索引，索引是不是太多了，因此就需要设计联合索引，但是在建立联合索引的时候，如何安排索引内的字段顺序呢？
这里我们的评估标准是，索引的复用能力。就出现了最左前缀原则，如果查询时使用了联合索引中的一部分列，那么这些列必须按照联合索引中的顺序出现，从左到右进行匹配。

• like aa%后模糊查询索引有效，like %aa模糊查询索引无效。
• mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引
• InnoDB会把主键字段放到索引定义字段后面， 当然同时也会去重。 所以，当主键是(a,b)的时候， 定义为c的索引，实际上是（c,a,b); 定义为(c,a)的索引，实际上是(c,a,b) 你看着加是相同的 ps 定义为(c,b）的索引，实际上是（c,b,a)

4. 索引下推

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

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

说明：建立了tuser表，在表中建立了name和age的联合索引
执行下面这条查询语句时

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

无索引下推的情况：

有索引下推的情况：
可以看到，有索引下推的时候，过滤掉了两条age不等于10的回表情况，减少了两次回表，提高了效率！
最后想要对一个查询语句进行分析，可以使用explain关键字进行分析，该文就不一一举例了