初识MySQL索引

Bertha 。 2023-10-09 14:51 70阅读 0赞

**参考链接：**

*  [MySql之索引\_猎户星座。的博客-CSDN博客\_mysql索引][MySql_-CSDN_mysql]
 *  [一文搞懂MySQL索引所有知识点（建议收藏）\_敖 丙的博客-CSDN博客][MySQL_ _-CSDN]
 *  [MySQL 索引 | 菜鸟教程][MySQL _ _]
 *  [聚簇索引和非聚簇索引的区别][Link 1]

## 1. 索引简介 ##

**在数据之外，数据库系统还维护着满足特定查找算法的数据结构**，包括B+树或者Hash表，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。**索引是帮助MySql高效获取数据的数据结构**。更通俗的说，数据库索引好比是一本书前面的目录，**能加快数据库的查询速度**。

我们通常所说的索引，包括聚簇索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

**由于存储引擎表示的是数据在磁盘上面的不同的组织形式，所以索引底层采用哪种数据结构是跟数据库的存储引擎相关的。如果是MyIsam或者是InnoDB存储引擎，那么对应的底层的数据结构为B+树；如果是Memory存储引擎，那么对应的底层的数据结构为Hash表。采用B+树的最根本的原因是由于二叉树的树太高，树太高则直接影响到磁盘IO的次数，影响数据查询的效率，采用B+树的数据结构，可以在某个数据节点里面尽可能多的存储数据，使树的高度尽量的变低，提高效率。日常开发过程中，遇到的比较多的可能就是聚簇索引和联合索引，里面又涉及到了覆盖索引，最左匹配，回表，索引下推等各方面的知识点，在编写SQL语句的时候，我们就可以利用这些点来进行优化，提高数据的查询效率。**

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySql的性能优化问题。一般来说索引本身也很大，不可能全部存储在内存中，因此**索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）**。索引是数据库中用来提高性能的最常用的工具。索引使用案例如下：

![img][]

左边是数据表，一共有2列7条记录，最左边的是数据记录的物理地址 ( **注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的** ) 。为了加快Col的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和—个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

## 2. 使用索引的优缺点 ##

### 2.1 优点 ###

*  索引类似于书籍的目录，可以提高数据检索的效率，降低数据库的IO成本。
 *  通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
    
     *  被索引的列会自动进行排序，包括单列索引和组合索引，只是组合索引的排序要复杂一些。
     *  如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

### 2.2 缺点 ###

*  实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用磁盘空间的。
 *  虽然索引大大地提高了查询效率，却也同时会降低更新表的效率，比如每次对表进行增删改操作时，MySQL不仅要保存数据，还要保存或者更新对应的索引文件。

## 3. 索引底层数据结构 ##

**实现索引时需要考虑到这两个特点：**

1.  存储的数据是非常非常多的
2.  并且还不断的动态变化

我们需要找出一个最合适的数据结构算法来实现查找功能。索引是在MySql的存储引擎层中实现的，而不是在服务器层实现，所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。

**从存储结构上来划分：B-Tree，B+Tree，Hash索引**

**从应用层次来分：普通索引，唯一索引，复合索引（组合索引）**

**从数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引（聚簇索引），非聚集索引（非聚簇索引）**

### 3.1 Hash索引 ###

**定义：**哈希索引是一种基于哈希表的索引结构，它是一种需要精确匹配才能生效的索引结构。在Java中的HashMap、TreeMap就是Hash表结构，以键值对的方式存储数据。我们可以使用Hash表存储表数据，Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

**实现原理：**对索引列计算哈希值，按照哈希值把记录映射到哈希槽中，然后指向对应记录行的地址。因此，在查询的时候只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。哈希索引示例如下，其中，左侧为哈希槽，右侧为对应的数据列：

![img][img 1]

**相比于B-Tree索引而言，哈希索引有不少的局限性：**

*  **哈希索引不支持排序**
 *  **哈希索引不支持部分列索引查找**
 *  **哈希索引只支持等值查询，无法提供范围查询功能**

**哈希索引的查找效率是非常高的，大多数时候都能在O(1)的时间内找到记录，除非哈希冲突很高，但不适合作为经常需要查找和范围查找的数据库索引使用。**

### 3.2 二叉查找树 ###

**演示链接：**[Data Structure Visualization][]

**二叉树特点：**

*  一个节点最多有两个分叉/子节点（左子节点、右子节点），即一个节点的度不能超过2，其中，顶端的点称为父节点，没有子节点的节点称为叶子节点；
 *  节点中存储的值大小依次为左、父、右；
 *  上述特点就是为了保证每次查找都可以折半，而减少IO次数。

**举例：**

![img][img 2]

在上面二叉树的结构中，通过计算比较 3 次就可以检索到 id=7 的数据（0004-->0006-->0007，即最右侧子树），相对于直接遍历查询省了一半的时间，从检索效率上能做到高速检索。

此外二叉树的结构还能提供范围查找功能，上图二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找比较容易实现。

**优点：**查询时间短，检索效率高，支持范围查找。

**缺点：**主键自增情或第一个根节点取值不恰当的况下，二叉树会退化为线性链表，二叉树不再进行分叉，二分查找也会退化为遍历查找（全盘扫描），检索性能急剧下降。如下图所示：

![img][img 3]

此时检索 id=7 的数据的所需要计算的次数已经变为 7 了，因此 不能直接用于实现 Mysql 底层索引。**显然我们在设计索引时要避免出现这种不稳定的情况，因此二叉树并不适合作为索引结构使用。**

### 3.3 平衡二叉树（AVL） ###

由于二叉树存在不平衡问题，为了保持其始终处于基本平衡的状态，出现了基于自调整平衡状态的平衡二叉树（AVL）和红黑树。

**平衡二叉树：**采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。

**平衡二叉树（AVL）特点：**

*  在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。
 *  具有很好的查找性能，不存在极端的低效查找的情况。
 *  可以实现范围查找、数据排序。

**举例：**

![img][img 4]

**平衡二叉树查找过程如下：**

![img][img 5]

AVL树顺序插入 1~16 个节点，查找 id=16 需要比较的节点数为 4（0008-->0014-->0015-->0016)。从查找效率而言，AVL树查找的速度要高于红黑树的查找效率（AVL 树是 4 次比较，红黑树是 6 次比较）。

虽然上面可以达到二叉树的理想情况，但是也存在一些问题：

*  平衡二叉树的时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms，在表数据量大时，查询性能就会很差（1百万的数据量，log2n约等于20次磁盘IO，时间20\*10=0.2s），很消耗时间。在设计数据库索引时需要首先考虑怎么尽可能减少磁盘IO的次数。
 *  平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

**因此不能直接将平衡二叉树用于实现Mysql底层索引。**

### 3.4 红黑树 ###

**红黑树特点：**每个节点的最长子树不超过最短子树的两倍。

**举例：**

![img][img 6]

上图所示，左图在插入数值为3时，红黑树的算法发现有偏向，就会重新调整树结构；调整到右边保持平衡，如持续递增，之前的数据1～7持续递增的树，会变成如下图所示：

![img][img 7]

递增插入过程中红黑树会自动左旋、右旋节点以及节点变色来调整树的形态，使其保持基本的平衡状态，也就保证了查找效率不会明显减低。

**红黑树很好的解决了线性链表问题，但红黑树问题也比较大。由于每次插入都要检查规则，再把树进行重新平衡，这个非常消耗时间，数据量大的话，红黑树的深度会比较深，并且产生“右倾”，树一旦深就代表着读取磁盘次数就会增加，因此 不能直接用于实现 Mysql 底层索引。**

### 3.5 改造二叉树-->B-树 ###

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点都会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如key为bigint=8字节，每个节点有两个指针，每个指针为4个字节，一个节点占用的空间16个字节（8+4 \* 2=16）。因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000\*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

即：如果把有序二叉树变成有序多叉树，就能降低树的高度，这个就是基于红黑树演变出来的B树的核心思想。由于**从磁盘读取1B 数据和 1KB 数据所消耗的时间基本是一样的(空间局部性与时间局部性决定)**，根据这个思路，可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就尽可能多的加载数据到内存。影响数据查询时间的是树的高度，高度越高，比较的次数越多，尽量把树的高度降低，这就是B树的设计原理了。B树简单来说就是一种多叉平衡查找树，每个叶子都会存储数据和指向下一个节点的指针。

**特点：**

*  B树的节点中存储着多个元素，每个内节点有多个分叉。
 *  节点中的元素包含键值和数据，节点中的键值从左向右递增排序。也就是说，在所有的节点都储存数据。
 *  父节点当中的元素不会出现在子节点中，所有的元素不重复。
 *  所有的叶子节点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

**举例1：**

![img][img 8]

**查找9的步骤如下：**

1.  将9先与根节点的关键字 (17，35）进行比较，由于9 小于 17 ，得到指针 P1；
2.  按照指针 P1 指向找到磁盘块 2，将9再与该节点的关键字（8，12）进行比较，由于 9 在 8 和 12 之间，得到指针 P2；
3.  按照指针 P2 指向找到磁盘块 6，将9再与该节点的关键字为（9，10）进行比较，从而找到关键字 9。

**举例2：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center]

**查找10的步骤如下：**查询路径磁盘块1-->磁盘块2-->磁盘块5

1.  第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，由于10<15，走左路指针P1，找到磁盘寻址磁盘块2。
2.  第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，由于7<10，走中路指针P2，找到磁盘寻址磁盘块5。
3.  第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，由于10=10，找到10，取出data，如果data中存储的是行记录，取出data，查询结束；如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比于二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

**具体实现过程如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 1]

**优点：**

*  优秀的检索速度
 *  尽可能少的磁盘 IO操作，加快了检索速度；
 *  可以支持范围查找。

**虽然上面B树的查询效果很理想，但是还有可以优化的地方：**

*  B树不支持范围查询的快速查找：如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
 *  如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

### 3.5 改造B-树-->B+树 ###

**实现原理：**从B-树可知，一个树节点应该尽可能多的包含子节点，但又不能超过一个磁盘页(InnoDB中页的默认大小是16KB)的大小。由于B树的节点中还包含着一些关键字的信息data（对于聚簇索引来说，data存的是数据行；对于非聚簇索引来说，data存的是主键的值），这个data占据着一定的数据量，如果把data去掉，这样就又能多加很多子节点了，这也就是B+树的核心思想。

**举例1：**

![img][img 9]

**查找关键字16的步骤如下：**

1.  将16与根节点的关键字 (1，18，35) 进行比较，发现16 在 1 和 18 之间，得到指针 P1，该指针指向磁盘块 2；
2.  将16与磁盘块2的关键字（1，8，14）进行比较，发现16大于14，得到指针 P3，该指针指向磁盘块 7；
3.  将16与磁盘块7的关键字（14，16，17）进行比较，找到了关键字 16，从而得到关键字 16 所对应的数据。

**B+树特点：**

*  非叶子节点不存储data，只存储索引(冗余)，可以存放更多的索引；
 *  叶子节点包含所有索引字段；
 *  叶子节点用单向指针相连，提高区间访问性；

**B-树与B+树对比：**

*  B+树非叶子节点不存储数据的，仅存储键值(索引地址)，而B-树节点中不仅存储键值，也会存储数据。B+树之所以这么做是因为在数据库中页的大小是固定的，InnoDB中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，这样查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快 。
 *  B+树索引的所有数据均存储在叶子节点，且数据是按照顺序排列的。B+树使得范围查找、排序查找、分组查找以及去重查找变得简单高效。
 *  B+树各个页之间通过双向链表连接，叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

即：B+树是通过二叉树、平衡二叉树、B树和索引顺序访问演化而来，是对B树的进一步优化。简单地说是：只有叶子节点才存数据，非叶子节点是存储的指针；所有叶子节点构成一个有序链表。

**举例2：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 2]

从上图可以看出，B+树的最底层叶子节点包含了所有的索引项。B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据时，每次的磁盘的IO都跟树高有着直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟着增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

**举例3：**等值查询：查询等于9的数据：查询路径磁盘块1->磁盘块2->磁盘块6

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 3]

1.  第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，由于9<15，走左路P1，找到磁盘寻址磁盘块2。
2.  第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，由于7<9<12，走中路P2，找到磁盘中寻址磁盘块6。
3.  第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束；如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址）。:

**举例4：**范围查询：查找9和26之间的数据：查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 4]

1.  首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。
2.  查找到9之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。
3.  第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。
4.  由于主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

**可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。**

### 3.6 MySql中的B+Tree ###

在MySQL中分别创建以MYISAM和InnoDB作为存储引擎的数据表。

*  **InnoDB创建表后生成的文件有：**
    
     *  **frm-创建表的语句**
     *  **idb-表里面的数据+索引文件；**
 *  **MyISAM创建表后生成的文件有：**
    
     *  **frm-创建表的语句**
     *  **MYD-表里面的数据文件（myisam data）**
     *  **MYI-表里面的索引文件（myisam index）**

通过上面可以发现，**MyISAM索引与数据分开存储，两个文件无法做到一致性，因此不支持事务。**

#### 3.6.1 MyISAM引擎的底层实现 ####

**举例1：**

![img][img 10]

从上图可知，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址，MyISAM在建表时以主键作为KEY来建立主索引B+树，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址，拿到这个物理地址后，就可以到MyISAM数据文件中直接定位到具体的数据记录了。

**举例2：**等值查询

以一个简单的user表为例，user表存在两个索引，id列为主键索引，age列为普通索引：

CREATE TABLE `user`(
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(20) DEFAULT NULL,
       `age` int(11) DEFAULT NULL,
       PRIMARY KEY (`id`) USING BTREE,
       KEY `idx_age` (`age`) USING BTREE
     ) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

**插入数据：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 5]

**主键索引：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 6]

表user的索引存储在索引文件`user.MYI`中，数据文件存储在数据文件 `user.MYD`中。

**等值查询分析：**

select * from user where id = 28;

**查询过程如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 7]

1.  先在主键树中从根节点开始检索，将根节点加载到内存，优于28<75，走左路P1，向下检索（1次磁盘IO）；
2.  将左子树节点加载到内存中，由于16<28<47，走中路P2，向下检索（1次磁盘IO）；
3.  检索到叶节点，将节点加载到内存中遍历，由于16<28、18<28、28=28，查找到值等于28的索引项（1次磁盘IO）；
4.  从索引项中获取磁盘地址，然后到数据文件user.MYD中获取对应整行记录（1次磁盘IO）；
5.  将记录返给客户端。

**磁盘IO次数：**3次索引检索 + 1次记录数据检索。

**举例3：**范围查询

select * from user where id between 28 and 47;

**查询过程如下:**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 8]

1.  先在主键树中从根节点开始检索，将根节点加载到内存，由于28<75，走左路P1（1次磁盘IO）；
2.  将左子树节点加载到内存中，由于16<28<47，走中路P2，向下检索（1次磁盘IO）；
3.  检索到叶节点，将节点加载到内存中遍历，由于16<28、18<28、28=28<47，查找到值等于28的索引项（1次磁盘IO）；
4.  由于我们的查询语句是范围查找，需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，47=47，查找到值等于47的索引项（1次磁盘IO）；
5.  根据磁盘地址从数据文件中获取行记录缓存到结果集中（1次磁盘IO）。
6.  最后得到两条符合筛选条件，将查询结果集返给客户端。

**磁盘IO次数：**4次索引检索+1次记录数据检索。

**备注：**以上分析仅供参考，MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。

**辅助索引**：

*  在MyISAM中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。
 *  查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

#### 3.6.2 InnoDB引擎的底层实现 ####

每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。

**InnoDB创建索引的具体规则如下：**

*  在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
 *  如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
 *  如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引，在InnoDB中，辅助索引中的叶子节点存储的数据是该行的主键值。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

**举例1：**

![img][img 11]

从上图可知，InnoDB在建表的时候会根据主键ID作为KEY建立索引B+树，B+树的叶子节点存储的是主键ID对应的数据，这就是为什么MySQL在建表时必须指定主键的原因。

当为表中某个字段xxx添加索引时，就会建立基于字段xxx的索引B+树，其中，节点中存的是字段xxx这个KEY，叶子节点存储的是主键KEY，拿到主键KEY后，InnoDB才会去主键索引树根据字段xxx索引B+树找到的主键KEY查找对应的数据。**注意：InnoDB检索过程中有“回表操作”。**

**举例2：**主键索引等值查询

以user\_innodb为例，user\_innodb的id列为主键，age列为普通索引：

CREATE TABLE `user_innodb`(
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `username` varchar(20) DEFAULT NULL,
       `age` int(11) DEFAULT NULL,
       PRIMARY KEY (`id`) USING BTREE,
       KEY `idx_age` (`age`) USING BTREE
     ) ENGINE = InnoDB;

**插入数据：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 9]

由于InnoDB的数据和索引存储在一个文件t\_user\_innodb.ibd中，所以InnoDB的数据组织方式是聚簇索引。主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

**主键索引：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 10]

**以等值查询id=28为例：**

select * from user_innodb where id = 28;

**查询过程如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 11]

1.  先在主键树中从根节点开始检索，将根节点加载到内存，由于28<75，走左路P1，向下检索（1次磁盘IO）；
2.  将左子树节点加载到内存中，发现16<28<47，走中路P2，向下检索（1次磁盘IO）；
3.  检索到叶节点，将节点加载到内存中遍历，由于16<28、18<28、28=28，查找到值等于28的索引项，直接可以获取整行数据。将该行记录返回给客户端（1次磁盘IO）。

**磁盘IO次数：**3次索引检索+0次记录数据检索。

**辅助索引：**

除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。

以表user\_innodb的age列为例，age索引的索引结果如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 12]

**底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从小到大排序。使用辅助索引时需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主键索引中检索获得记录。**

**举例4：辅助索引（单列索引）等值查询**

select * from t_user_innodb where age=19;

**查询过程如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 13]

1.  先在辅助索引树中从根节点开始检索，将根节点加载到内存，由于19<25，走左路P1，向下检索（1次磁盘IO）；
2.  将左子树节点加载到内存中，由于18<19，走右路P2，向下检索（1次IO操作）；
3.  检索到叶节点，将节点加载到内存中遍历，由于18<19、19=19，查找到值等于19的索引项，得到主键值47（1次IO操作）；
4.  根据在辅助索引树获得的主键ID，回到主键索引树，在主键索引树中从根节点开始检索，将根节点加载到内存，由于47<75，走左路P1，向下检索（回表：1次磁盘IO）；
5.  将左子树节点加载到内存中，由于16<47=47，走中路P2，向下检索（回表：1次IO操作）；
6.  检索到叶节点，将节点加载到内存中遍历，由于47=47，查找到值等于47的索引项，直接可以获取整行数据，将该行记录返回给客户端（回表：1次磁盘IO）。

**磁盘IO数：辅助索引3次+获取记录回表3次**

根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为**回表**查询。

**注意：非主键索引存储的是主键索引位置，会扫描两棵树 （主键索引, 非主键索引）**

**数据表字段加索引的原则：**

*  较频繁的作为查询条件的字段应该创建索引；
 *  唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件；
 *  更新非常频繁的字段不适合创建索引；

**举例5：辅助索引（组合索引）**

以自己创建的一个表为例：表 abc\_innodb，id为主键索引，创建了一个联合索引idx\_abc(a,b,c)。

CREATE TABLE `abc_innodb`(
       `id` int(11) NOT NULL AUTO_INCREMENT,
       `a`  int(11) DEFAULT NULL,
       `b`  int(11) DEFAULT NULL,
       `c`  varchar(10) DEFAULT NULL,
       `d`  varchar(10) DEFAULT NULL,
       PRIMARY KEY (`id`) USING BTREE,
       KEY `idx_abc` (`a`, `b`, `c`)
     ) ENGINE = InnoDB;

**数据排序：**

select * from abc_innodb order by a, b, c, id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 14]

**组合索引的数据结构：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 15]

**等值查询分析：**

select * from abc_innodb where a = 13 and b = 16 and c = 4;

**查询过程如下：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 16]

1.  先在辅助索引树中从根节点开始检索，将根节点加载到内存，由于(13,14,3)<(13,16,4)，走右路P2，向下检索（1次磁盘IO）；
2.  将右子树节点加载到内存中，由于(13,16,4)<(14,14,14)，走左路P1，向下检索（1次IO操作）；
3.  检索到叶节点，将节点加载到内存中遍历，由于(13,14,3)<(13,16,4)、(13,16,4)=(13,16,4)，查找到值等于(13,16,4)的索引项，得到主键值4（1次IO操作）；
4.  根据在辅助索引树获得的主键ID，回到主键索引树，回表进行搜索，得到数据。

**磁盘IO数：辅助索引3次+获取记录回表n次**

**InnoDB存储引擎特点:**

*  表本身是按B+Tree组织的一个索引结构文件；
 *  叶子节点包含了完整的数据记录；
 *  非主键索引结构叶子节点存储的是主键的值，使其保持一致性和节省空间。

**InnoDB等值查询：**

引擎会自动使用哈希索引进行查询，存储引擎会监控表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应哈希索引。自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

**为什么InnoDB只在主键索引树的叶子节点存储了具体数据?**

为节省存储空间，一个表里可能有很多个索引，InnoDB都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。因为InnoDB支持聚簇索引（主键索引），聚簇索引就是表，所以InnoDB不用像MyISAM那样需要独立的行存储。也就是说，InnoDB的数据文件本身就是索引文件。

**为什么MySQL不推荐使用uuid作为主键?**

*  **使用自增id的优点：**

自增主键的值是顺序的，所以InnoDB把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候InnoDB默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)：

① 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费；

② 新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗；

③减少了页分裂和碎片的产生。

*  **使用自增id的缺点：**

① 别人一旦爬取你的数据库，就可以根据数据库的自增id获取到你的业务增长信息，很容易分析出你的经营情况；

② 对于高并发的负载，innodb在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争；

③ Auto\_Increment锁机制会造成自增锁的抢夺，有一定的性能损失。对于Auto\_increment的锁争抢问题，如果要改善，需要调优innodb\_autoinc\_lock\_mode的配置。

*  **使用uuid作为主键引发的问题：**

![在这里插入图片描述][48f6d7dc5b4bca13511a35ffcd0867d1.png]

因为uuid的顺序相对于自增id来说毫无规律可言，新行的值不一定比之前的主键的值大，所以innodb无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置，从而来分配新的空间。这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

①写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO；

②因为写入是乱序的，innodb不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上；

③由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。

在把随机值（uuid和雪花id）载入到聚簇索引(innodb默认的索引类型)以后，有时候会需要做一次OPTIMEIZE TABLE来重建表并优化页的填充，这将又需要一定的时间消耗。

**结论：**使用InnoDB时应该尽可能的按主键的自增顺序插入，并且尽可能使用单调增加的聚簇键的值来插入新行。

**虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。因为InnoDB支持聚簇索引（主键索引），聚簇索引就是表，所以InnoDB不用像MyISAM那样需要独立的行存储。也就是说，InnoDB的数据文件本身就是索引文件。**

## 4. 常见索引类型 ##

### 4.1 主键索引 ###

主键索引（即主键）必须是唯一的，不允许有空值，通常将表的ID设置为主键索引，一个表只能有一个主键索引，这是它与唯一索引的区别。

创建方式如下：

create table User(
     `name` varchar(50) not null,
     `uid` int(4) not null,
     `gender` int(2) not null,
      primary key(`uid`)
     );

### 4.2 普通索引 ###

MySQL中基本的索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。它有以下几种创建方式：

1.创建表的时候直接指定普通索引

CREATE TABLE mytable(  
     ID INT NOT NULL,   
     username VARCHAR(16) NOT NULL,  
     INDEX [indexName] (username(length))  
     );

2.在现有表的基础上添加普通索引

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

3.通过修改表结构添加普通索引

ALTER table tableName ADD INDEX indexName(columnName)

删除索引语句：

DROP INDEX [indexName] ON mytable;

### 4.3 唯一索引 ###

唯一索引主要用于业务上的唯一约束，它与主键索引的区别是，一个表可以有多个唯一索引。 索引列的值必须唯一的，但允许有空值。如果是组合索引，则列值的组合必须唯一。创建方式如下：

1.创建表的时候直接指定唯一索引

create table User(
     `name` varchar(50) not null,
     `uid` int(4) not null,
     `gender` int(2) not null,
      unique key(`name`)
     );

或

CREATE TABLE mytable(  
     ID INT NOT NULL,   
     username VARCHAR(16) NOT NULL,  
     UNIQUE [indexName] (username(length))  
     );

2.在现有表的基础上添加唯一索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

3.通过修改表结构添加唯一索引

ALTER table mytable ADD UNIQUE [indexName] (username(length))

### 4.4 全文索引 ###

**只能在文本类型为CHAR、VARCHAR、TEXT类型的字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。**

### 4.5 空间索引 ###

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

### 4.6 前缀索引 ###

在文本类型如CHAR、VARCHAR、TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

### 4.7 按索引数量分类 ###

#### 4.7.1 单列索引 ####

单列索引即以某一个字段建立索引。创建方式如下：

1.在建表的时候创建

create table user(
     `name` varchar(50) not null,
     `uid` int(4) not null,
     `gender` int(2) not null,
      key(`name`)
     );

2.在建好表的基础上创建

create index idx_name on table(name);
     create index idx_status on table(status);
     create index idx_address on table(address);

#### 4.7.2 联合索引 ####

**将两个或两个以上字段联合组成一个索引，使用时需要注意满足最左前缀匹配原则(最左匹配原则)！**一般情况下，在条件允许时，可以使用组合索引替代多个单列索引使用。

在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a\_b\_c），是不是等于有了索引：a，a\_b，a\_b\_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a\_b，a\_b\_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

**联合索引的创建原则：**

在创建联合索引的时候，应该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引。

**联合索引的使用：**

*  考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
 *  当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

**创建方式如下：**

create table user(
     `name` varchar(50) not null,
     `uid` int(4) not null,
     `gender` int(2) not null,
      key(`name`,`uid`)
     );

#### 4.7.3 联合索引中的最左前缀匹配原则 ####

**最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。**

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列只有在a列值相等的情况下，小范围内递增有序，而c列只能在a、b两列相等的情况下，小范围内递增有序。B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同，再比较b列；但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建的idx\_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。即查询从联合索引的最左列开始，并且不跳过索引中的列。如下：

select * from user where name=xx and city=xx ;

查询的时候如果两个条件都用上了，但是顺序不同，如city=xx and name＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。**尽量使用联合索引，而少使用单列索引。**

**创建联合索引举例：**

create index idx_name_sta_address on table(name,status,address);

上面这条SQL语句相当于创建了3个索引：

name

name+status

name+status+address

**数据库只会选择一个最优的索引来使用，并不会使用全部索引。**

**组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。**

### 4.8 (非索引结构)覆盖索引(优化手段） ###

**定义：**指索引包含了所有需要查询的字段。**覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。**因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况，在上面abc\_innodb表中的组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表，这种情况就是覆盖索引。

**举例1：**

create table user(
     `name` varchar(50) not null,
     `uid` int(4) not null,
     `gender` int(2) not null,
      key(`uid`,`name`)
     );

假如表User有三个字段user(name,uid,gender)，且有个联合索引key(name,uid)，那么执行如下面这条sql查询时就用到了覆盖索引：

select name,uid from User where name in ('a','b') and uid >= 98 and uid <=100 ;

上面这条sql语句使用了联合索引key(name,uid)，并且只需查找 name,uid两个字段，所以使用了覆盖索引。如果我们只需查询(name,uid)两个字段的话，从索引树就能得到我们需要查的数据，不需要回表。

**举例2：**查看执行计划

*  覆盖索引的情况：

![在这里插入图片描述][20201024115203337.png_pic_center]

*  未使用到覆盖索引：

![在这里插入图片描述][20201024115218222.png_pic_center]

**覆盖索引有什么好处呢？**

1.  避免了对主键索引(聚簇)的二次查询；
2.  由于不需要回表查询(从表数据文件)，所以大大提升了Mysql缓存的负载，提升了读取数据的性能。

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？使用索引覆盖。

**举例：**

现有user表（id(PK),name(key),sex,address,hobby…），如果在一个场景下，select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立name字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据，这样就可以有效地避免了回表再获取sex的数据。

**以上就是一个典型的使用覆盖索引的优化策略减少回表的情况。**

### 4.9 聚簇索引与非聚簇索引 ###

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。聚集索引存储记录是物理上连续存在，物理存储按照索引排序，而非聚集索引是逻辑上的连续，物理存储并不连续，物理存储不按照索引排序。

*  聚簇索引的顺序就是数据的物理存储顺序，而对非聚簇索引的解释是:索引顺序与数据物理排列顺序无关。正是因为如此，所以一个表最多只能有一个聚簇索引。在SQL Server中，索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
 *  索引是一种特殊的数据结构。微软的SQL Server提供了两种索引：聚集索引（Clustered Index，也称聚类索引、簇集索引、聚簇索引）和非聚集索引（Nonclustered Index，也称非聚类索引、非簇集索引）。创建的索引，如复合索引、前缀索引、唯一索引，都是属于非聚簇索引，在有的书籍中，又将其称为辅助索引(secondary index)。在后文中，我们称其为非聚簇索引，其数据结构为B+树。
 *  聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

#### 4.9.1 非聚簇索引之MyISAM索引 ####

MyISAM存储引擎的索引文件和数据文件是分开的，MyISAM引擎按照数据插入顺序，将数据文件存储在磁盘上，例如下图中99条记录从上到下依次存储。MyISAM引擎使用 B+ 树作为索引结构，叶节点存放的是数据记录的行指针，图中为了方便阅读以行号代替。

![img][img 12]

在MyISAM引擎中，对主键列建立的主索引和对其他列建立的辅助索引在结构上没有区别，主键索引就是一个名为Primary的唯一非空索引。

**总结：**MyISAM引擎中索引查询的步骤为，先按照B+树查询到叶子节点，如果指定的键值存在，则取出其对应的行指针的值，然后通过行指针，读取相应数据行的记录。

#### 4.9.2 聚簇索引之InnoDB索引 ####

同 MyISAM 引擎不同，InnoDB 的数据文件本身就是索引文件，表数据文件本身就是按 B+ 树组织的一个索引结构，其叶子节点的键值就是表的主键，这种数据存储方式也被称为聚簇索引。由此可见，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。

聚簇索引的叶子节点都包含主键值、事务 ID、用于事务 MVCC 的回滚指针以及所有的剩余列。

![img][img 13]

#### 4.9.3 非聚簇索引之InnoDB索引 ####

辅助索引也叫非聚簇索引、二级索引等。同 MyISAM 引擎的辅助索引实现不同，InnoDB 的辅助索引，其叶子节点存储的不是行指针而是主键值，得到主键值再要查询具体行数据的话，要去聚簇索引中再查找一次，也叫回表。这样的策略优势是减少了当出现行移动或者数据页分裂时二级索引的维护工作。

![img][img 14]

**总结：**

*  **聚簇索引：通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据。**
 *  **非聚簇索引：又称二级索引，就是我们常用的普通索引，叶子节点存储了索引值和主键值，再根据主键从聚簇索引中查。**

### 4.10 索引条件下推（(Index Condition Pushdown)，简称ICP） ###

这是MySQL5.6新添加的功能，用于优化数据的查询。当你不使用ICP，通过使用非主键索引（普通索引or二级索引）进行查询，存储引擎通过索引检索数据，然后返回给MySQL服务器，服务器再判断是否符合条件。使用ICP时，当存在索引的列作为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

**适用场景：**

*  当需要整表扫描，e.g.:range,ref,eq\_ref....
 *  适用InnoDB引擎和MyISAM引擎查询（5.6版本不适用分区查询，5.7版本可以用于分区表查询）。
 *  InnoDB引擎仅仅适用二级索引（原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区）。
 *  子查询条件不能下推。
 *  触发条件不能下推
 *  调用存储过程条件不能下推。

**举例：**

当我们创建一个用户表(userinfo)，其中有字段：id，name，age，addr，将name,age建立联合索引。

select * from userinfo where name like "ming%" and age=20;

对于MySQL5.6之前：我们在索引内部首先通过name进行查找，在联合索引name,age树形查询结果可能存在多个，然后再拿着id值去回表查询，整个过程需要回表多次。

对于MySQL5.6之后：我们是在索引内部就判断age是否等于20，对于不等于20跳过。因此在联合索引name,age索引树只匹配一个记录，此时拿着这个id去主键索引树种回表查询全部数据，整个过程就回一次表。

**当Extra值为Using index condition时，表示使用了索引下推。**

**关闭索引下推命令：set optimizer\_switch='index\_condition\_pushdown=off';**

**优点：**通过索引下推可以对非主键索引进行优化，可有效减少回表次数，从而提高效率。

### 4.11 索引相关指令 ###

*  **使用ALTER 命令添加索引：**
    
     *  ALTER TABLE tbl\_name ADD PRIMARY KEY (column\_list): 该语句添加一个主键索引，这意味着索引值必须是唯一的，且不能为NULL。
     *  ALTER TABLE tbl\_name ADD INDEX index\_name (column\_list): 添加普通索引，索引值可出现多次。
     *  ALTER TABLE tbl\_name ADD UNIQUE index\_name (column\_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
     *  ALTER TABLE tbl\_name ADD FULLTEXT index\_name (column\_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
        
        例如：ALTER TABLE testalter\_tbl ADD INDEX (c);
 *  **使用ALTER 命令删除索引：**ALTER TABLE testalter\_tbl DROP INDEX c;

*  **使用 ALTER 命令添加主键：**
    
    主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。
 *  **使用 ALTER 命令删除主键：**删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
    
    举例：ALTER TABLE testalter\_tbl DROP PRIMARY KEY;
 *  **显示索引信息：**可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \\G 来格式化输出信息。
    
    例如：SHOW INDEX FROM table\_name\\G

## 5.最佳索引使用策略 ##

### 5.1 索引失效的情况 ###

*  **范围查询右边的列，不能使用索引**
    
    select \* from t where name ='test' and status >'1' and address='北京市'
    
    前面的两个字段name、status查询是走索引的，但是最后一个条件address没有用到索引
 *  **在索引列上进行运算操作，索引将失效**
    
    举例：select \* from t where substring(name,3,2)='科技'
 *  **字符串不加单引号，造成索引失效**
    
    举例：select \* from t where name ='test' and status =1
 *  **用or分隔的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。**
    
    举例：select \* from t where name ='test' or createtime='2020-04-05 12:00:00'
    
    name是索引列，createtime不是索引列，之间or进行连接，那么会导致name列也不走索引。
 *  **以%开头的like模糊查询，索引失效**
    
    举例：select name from t where name like '%test'
    
    如果仅仅是尾部的模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效；但是如果使用覆盖索引，那么索引仍然会生效。
 *  **如果MySql评估使用索引比全表扫描更慢，则不使用索引，如is null，is not null 有时索引失效**
    
    is null：如果数据库中，该字段为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引；
    
    is not null：如果数据库中，该字段不为空值的记录数更多，那么MySql评估使用索引比全表扫描更慢，则不使用索引。
 *  **in 走索引，not in 索引失效**
 *  **使用不等于(!=或者<>)的时候，索引失效，会导致全表扫描**
    
    举例：select name from t where name != 'test'
 *  **MYSQL针对函数或存储过程中传递进的参数，如果是varchar类型时，则默认会进行转换字符集，校对规则与数据库保持一致，这个时候如果数据库编码和表编码不一致时（比如utf8和utf8mb4），就会出现索引失效的情况。**
    
    客户端直接发sql查询的话，不会存在这种问题，因为这个时候默认的是表字段的编码。

### 5.2 最佳索引使用策略 ###

*  **对查询频次较高，且数据量比较大的表建立索引**
 *  **索引字段的选择：**最佳候选列应当从 where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合
 *  **索引可以有效的提升查询数据的效率，但索引数昰不是多多益善，索引越多，维护索引的代价自然也就水涨船高。**对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DM操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
 *  **独立的列**
    
    注意：独立的列不是指单列索引，而是指索引列不能是表达式的一部分或者是函数的一部分。
    
    举例：
    
    select \* FROM test where col1 + 1 =100; // 不能是表达式一部分
    
    select \* FROM test where ABS(col1) =100; // 不能是函数一部分
 *  **最左匹配原则**
    
    假如有个联合索引 key (col1,col2)。那么以下查询是索引无效的：
    
    select \* from test where col2 = 3;
    
    select \* from test where col1 like '%3';

对于最左匹配原则，大家想一下B+树的叶子节点的关联就差不多知道为啥需要最左匹配原则了，因为B+的叶子结点，从左到右以链表的形式关联的，索引查询的时候要么范围查询，**要么有明确的左边一个开始的索引值**，不能跳过或者不明确如 like '%XYZ'这种查询。

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如：col2= xx and col1 ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

*  **使用聚簇索引和覆盖索引大大提升读取性能**
    
    因为聚簇索引和覆盖索引的索引树上就有了需要的字段，所以不需要回表文件查询，所以提升了查询速度。
 *  **使用短索引**
    
    如果很长的字符串进行查询，只需匹配一个前缀长度，这样能够节省大量索引空间。
 *  **尽量使用覆盖索引，避免使用select \***。

[MySql_-CSDN_mysql]: https://blog.csdn.net/qq_24313635/article/details/102924190
[MySQL_ _-CSDN]: https://aobing.blog.csdn.net/article/details/109257302
[MySQL _ _]: https://www.runoob.com/mysql/mysql-index.html
[Link 1]: https://baijiahao.baidu.com/s?id=1724541015786316514&wfr=spider&for=pc
[img]: https://img-blog.csdnimg.cn/20200404214937790.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 1]: https://img-blog.csdnimg.cn/20210209195545421.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[Data Structure Visualization]: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
[img 2]: https://img-blog.csdnimg.cn/20210209195621615.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 3]: https://img-blog.csdnimg.cn/2021020919565433.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 4]: https://img-blog.csdnimg.cn/20210209195835452.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 5]: https://img-blog.csdnimg.cn/20210209195852396.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 6]: https://img-blog.csdnimg.cn/20210209195730973.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 7]: https://img-blog.csdnimg.cn/20210209195749675.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[img 8]: https://img-blog.csdnimg.cn/20210209212226248.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20201024113818483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201024113920770.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[img 9]: https://img-blog.csdnimg.cn/20210209212247938.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201024114004740.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201024114108759.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201024114145717.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[img 10]: https://img-blog.csdnimg.cn/20210209200131231.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/20201024114258829.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 6]: https://img-blog.csdnimg.cn/20201024114325883.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 7]: https://img-blog.csdnimg.cn/20201024114404727.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 8]: https://img-blog.csdnimg.cn/20201024114510253.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[img 11]: https://img-blog.csdnimg.cn/20210209200140463.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI0MzEzNjM1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 9]: https://img-blog.csdnimg.cn/20201024114600471.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 10]: https://img-blog.csdnimg.cn/202010241146330.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 11]: https://img-blog.csdnimg.cn/20201024114716460.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 12]: https://img-blog.csdnimg.cn/20201024114750255.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 13]: https://img-blog.csdnimg.cn/2020102411481097.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 14]: https://img-blog.csdnimg.cn/20201024114931382.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 15]: https://img-blog.csdnimg.cn/20201024114900213.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky_size_16_color_FFFFFF_t_70_pic_center 16]: https://img-blog.csdnimg.cn/20201024115012887.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTkwNDky,size_16,color_FFFFFF,t_70#pic_center
[48f6d7dc5b4bca13511a35ffcd0867d1.png]: https://img-blog.csdnimg.cn/img_convert/48f6d7dc5b4bca13511a35ffcd0867d1.png
[20201024115203337.png_pic_center]: https://img-blog.csdnimg.cn/20201024115203337.png#pic_center
[20201024115218222.png_pic_center]: https://img-blog.csdnimg.cn/20201024115218222.png#pic_center
[img 12]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS81LzgvMTZhOTUzMjcyOWFjMDJhOQ?x-oss-process=image/format,png
[img 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS81LzgvMTZhOTUzMjcyZWI2YTI1Zg?x-oss-process=image/format,png
[img 14]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly91c2VyLWdvbGQtY2RuLnhpdHUuaW8vMjAxOS81LzgvMTZhOTUzMjc4OWM4YjI5ZQ?x-oss-process=image/format,png