mysql 索引

 来源：

(53条消息) MySQL索引_Qiumin~的博客-CSDN博客

Mysql索引（一篇就够le） - 一寸HUI - 博客园 (cnblogs.com)

(53条消息) 一文搞懂MySQL索引（清晰明了）_Free Joe的博客-CSDN博客_mysql 索引

(53条消息) MySQL体系构架、存储引擎和索引结构_Free Joe的博客-CSDN博客_mysql 存储引擎结构MySQL索引总结 - 知乎 (zhihu.com)(53条消息) MySQL体系构架、存储引擎和索引结构_Free Joe的博客-CSDN博客_mysql 存储引擎结构

面试前必须要掌握的MySQL索引最左前缀匹配原则 - 知乎 (zhihu.com)

什么是 MySQL 的“回表”？ - 文章详情 (itpub.net)

理解MySQL回表 - 昊晗远昂 - 博客园 (cnblogs.com)

避免写出致命 SQL，搞懂 MySQL 回表 - 掘金 (juejin.cn)

1. mysql索引介绍

        索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

例子：简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。 

    2.索引的优缺点

   优点

1. 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
2. 索引可以帮助服务器避免排序和创建临时表
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
5. 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
6. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
7. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
8. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
9. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
    

缺点 

1.  创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2. 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
3. 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
4. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
5. 对于非常小的表，大部分情况下简单的全表扫描更高效；  

3.索引存储类型  

  

 3.1 B-Tree

  B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。事实上，B-tree就是指的B树

B树是一种多路搜索树，一棵m阶的B树满足下列条件：

1. 树中每个结点至多有m个孩子
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子结点的关键字个数 = 指向子节点的指针个数-1；
6. 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8. 所有叶子结点位于同一层；
    

 

B树的特征：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；
B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；
 

 3.2 B+Tree

    B+树是B-树的变体，也是一种多路搜索树

1. 树中每个结点至多有m个孩子
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；

     ❀ 非叶子结点的子树指针与关键字个数相同；

     ❀ 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树；（B树是开区         间）；

     ❀ 为所有叶子结点增加一个链指针；

 

B+树的特征：

1. 所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2. 不可能在非叶子结点命中；
3. 非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4. 每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
5. 更适合文件索引系统；
6. B+树的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；
    

 深入学习

图解：什么是B树？（心中有 B 树，做人要虚心）一文读懂B-树 - 知乎 (zhihu.com)

 3.3 HASH

 哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

 

 Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。
　　
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
 

 4.索引分类

 1.功能划分

• 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；
• 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；
• 全文索引：它查找的是文本中的关键词，主要用于全文检索。

 2.按列数划分

• 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
• 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

 最左原则！！！

在面试中，经常会遇到这种问题，如果我们设置联合索引的顺序是(a, b, c)， 那么如果我们在查询时的顺序却是(a, c, b) 会走索引吗？我们就深入探究一下，应该如何回答这一类问题，并且探寻他背后的原理，那就是最左匹配原则

 原理

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

 要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

 举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

 

 可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

 示例：

首先创建一个表

 该表中对id列.name列.age列建立了一个联合索引 id_name_age_index，实际上相当于建立了三个索引（id）（id_name）（id_name_age）。

 下面介绍下可能会使用到该索引的几种情况：

 1.全值匹配查询时

 

 

 

 通过观察上面的结果图可知，where后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引，可能有同学会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引。另外通过观察上面三个图中的key_len字段，也可说明在搜索时使用的联合索引中的（id_name_age）索引，因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，又使用的是latin1编码，所以占11个字节，age为int型允许null，所以也占用5个字节，所以该索引长度为21（5+11+5），而上面key_len的值也正好为21，可证明使用的（id_name_age）索引。

 2.匹配最左边的列时

 

 该搜索是遵循最左匹配原则的，通过key字段也可知，在搜索过程中使用到了联合索引，且使用的是联合索引中的（id）索引，因为key_len字段值为5，而id索引的长度正好为5（因为id为int型，允许null，所以占5个字节）。

 

 由于id到name是从左边依次往右边匹配，这两个字段中的值都是有序的，所以也遵循最左匹配原则，通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id_name）索引，因为key_len字段值为16，而(id_name)索引的长度正好为16（因为id为int型，允许null，所以占5个字节，name为char(10)，允许null，又使用的是latin1编码，所以占11个字节）。

 

由于上面三个搜索都是从最左边id依次向右开始匹配的，所以都用到了id_name_age_index联合索引。 

那如果不是依次匹配呢？ 

 通过key字段可知，在搜索过程中也使用到了联合索引，但使用的是联合索引中的（id）索引，从key_len字段也可知。因为联合索引树是按照id字段创建的，但age相对于id来说是无序的，只有id只有序的，所以他只能使用联合索引中的id索引。

 

通过观察发现上面key字段发现在搜索中也使用了id_name_age_index索引，可能许多同学就会疑惑它并没有遵守最左匹配原则，按道理会索引失效，为什么也使用到了联合索引？因为没有从id开始匹配，且name单独来说是无序的，所以它确实不遵循最左匹配原则，然而从type字段可知，它虽然使用了联合索引，但是它是对整个索引树进行了扫描，正好匹配到该索引，与最左匹配原则无关，一般只要是某联合索引的一部分，但又不遵循最左匹配原则时，都可能会采用index类型的方式扫描，但它的效率远不如最做匹配原则的查询效率高，index类型类型的扫描方式是从索引第一个字段一个一个的查找，直到找到符合的某个索引，与all不同的是，index是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。 

 

 

 这两个结果跟上面的是同样的道理，由于它们都没有从最左边开始匹配，所以没有用到联合索引，使用的都是index全索引扫描。

 3.匹配列前缀

 如果id是字符型，那么前缀匹配用的是索引，中坠和后缀用的是全表扫描。

 select * from staffs where id like 'A%';//前缀都是排好序的，使用的都是联合索引 select * from staffs where id like '%A%';//全表查询 select * from staffs where id like '%A';//全表查询

 4.匹配范围值

 

 在匹配的过程中遇到<>=号，就会停止匹配，但id本身就是有序的，所以通过possible_keys字段和key_len 字段可知，在该搜索过程中使用了联合索引的id索引（因为id为int型，允许null，所以占5个字节），且进行的是rang范围查询。

 

 由于不遵循最左匹配原则，且在id<4的范围中，age是无序的，所以使用的是index全索引扫描。

 

 不遵循最左匹配原则，但在数据库中id<2的只有一条（id），所以在id<2的范围中，age是有序的，所以使用的是rang范围查询。

 

 不遵循最左匹配原则，而age又是无序的，所以进行的全索引扫描。

5.准确匹配第一列并范围匹配其他某一列 

由于搜索中有id=1，所以在id范围内age是无序的，所以只使用了联合索引中的id索引。 

3.物理分类

 分为聚簇索引和非聚簇索引（有时也称辅助索引或二级索引）

 聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。
 

 优点

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快

 缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
 

5.回表 

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度。

大家知道，MySQL 中的索引有很多中不同的分类方式，可以按照数据结构分，可以按照逻辑角度分，也可以按照物理存储分，其中，按照物理存储方式，可以分为聚簇索引和非聚簇索引。

我们日常所说的主键索引，其实就是聚簇索引（Clustered Index）;主键索引之外，其他的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引。

对于主键索引和非主键索引，使用的数据结构都是 B+Tree，唯一的区别在于叶子结点中存储的内容不同：

• 主键索引的叶子结点存储的是一行完整的数据。

• 非主键索引的叶子结点存储的则是主键值。

这就是两者最大的区别。

所以，当我们需要查询的时候：

1. 如果是通过主键索引来查询数据，例如 select * from user where id=100，那么此时只需要搜索主键索引的 B+Tree 就可以找到数据。

2. 如果是通过非主键索引来查询数据，例如 select * from user where username='javaboy'，那么此时需要先搜索 username 这一列索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

对于第二种查询方式而言，一共搜索了两棵 B+Tree，第一次搜索 B+Tree 拿到主键值后再去搜索主键索引的 B+Tree，这个过程就是所谓的回表。

从上面的分析中我们也能看出，通过非主键索引查询要扫描两棵 B+Tree，而通过主键索引查询只需要扫描一棵 B+Tree，所以如果条件允许，还是建议在查询中优先选择通过主键索引进行搜索。

 一定会回表吗？

那么不用主键索引就一定需要回表吗？

不一定！

如果查询的列本身就存在于索引中，那么即使使用二级索引，一样也是不需要回表的。

 举个例子，我有如下一张表：

 

 uname 和 address 字段组成了一个复合索引，那么此时，虽然这是一个二级索引，但是索引树的叶子节点中除了保存主键值，也保存了 address 的值。

 

 可以看到，此时使用到了 uname 索引，但是最后的 Extra 的值为 Using index，这就表示用到了索引覆盖扫描（覆盖索引），此时直接从索引中过滤不需要的记录并返回命中的结果，这一步是在 MySQL 服务器层完成的，并且不需要回表。

 6.MySQL 覆盖索引

 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

 我们知道 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢。覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

如何实现索引覆盖？ 

常见的方法是：将被查询的字段，建立到联合索引里去（或者说 查询的字段都已经建立了索引）。

我们用 EXPLAIN 关键词分析来看下结果。

CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL, `sex` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL, `flag` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_name`(`name`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Compact; 

第一个sql 

 EXPLAIN SELECT id, name FROM user WHERE name='shenjian';

 能够命中 name 索引，索引叶子节点存储了主键 id，通过 name 的索引树即可获取 id 和 name，无需回表，符合索引覆盖，效率较高。

 第二个SQL语句：

 EXPLAIN SELECT id, name, sex FROM user WHERE name='shenjian';

 

能够命中 name 索引，索引叶子节点存储了主键 id，但 sex 字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过 id 值扫码聚集索引获取 sex 字段，效率会降低。 

 如果把 (name) 单列索引升级为联合索引 (name, sex) 就不同了：

 ALTER TABLE `user` DROP INDEX `idx_name`, ADD INDEX `idx_name`(`name`, `sex`) USING BTREE;

 

 再次执行，第二个SQL语句：

 EXPLAIN SELECT id, name, sex FROM user WHERE name='shenjian';

 

 能够命中 联合索引，索引叶子节点存储了主键 id，通过 联合索引 的索引树即可获取 name 和 sex，无需回表，符合索引覆盖，效率较高。

7.  key 与 index 含义

key具有两层含义：1.约束（约束和规范数据库的结构完整性）2.索引

index：索引

 key 种类

key：等价普通索引 key 键名 (列)

primary key：

约束作用（constraint），主键约束（unique，not null，一表一主键，唯一标识记录），规范存储主键和强调唯一性
为这个key建立主键索引
unique key：

约束作用（constraint），unique约束（保证列或列集合提供了唯一性）
为这个key建立一个唯一索引；
foreign key：

约束作用（constraint），外键约束，规范数据的引用完整性
为这个key建立一个普通索引；

8. 创建索引准则

 索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

•  应该创建索引的列

1.  在经常需要搜索的列上，可以加快搜索的速度
2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3. 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
4. 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
5. 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

•  不该创建索引的列

1.  对于那些在查询中很少使用或者参考的列不应该创建索引。
2. 若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
3. 对于那些只有很少数据值或者重复值多的列也不应该增加索引。
4. 这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
5. 对于那些定义为text, image和bit数据类型的列不应该增加索引。
6. 这些列的数据量要么相当大，要么取值很少。
7. 当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）