一文搞懂 MySQL 中的索引

古城微笑少年丶 2023-10-01 08:00 23阅读 0赞

### 1. 什么是索引 ###

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：**索引是数据结构**。

举一个例子，平时看任何一本书，首先看到的都是目录，通过目录去查询书籍里面的内容会非常的迅速，如下：

![image-20211124200643776][]

书籍的目录是按顺序放置的，有第一章，第二章…，它本身就是一种顺序存放的数据结构，是一种顺序结构。

但是如果我们要去图书馆找一本书怎么办？最好的方式就是有如下的的指引：

![image-20211125091959077][]

从上面可见，整个索引结构是一棵倒挂着的树，其实它就是一种数据结构，这种数据结构比前面讲到的线性目录更好的增加了查询的速度，否者你可能的一层楼一层楼的去找。

MySql 中的索引其实也是这么一回事，我们可以在数据库中建立一系列的索引，比如创建主键 ID 的时候默认会创建主键索引，当我们通过 ID 来查询内容的时候，首先去查索引库，查到索引后能快速的根据索引定位数据的具体位置。

所以索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响，而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。

**注**：以下关于索引的介绍，在没有特殊说明主要都是指的 InnoDB 中的索引。

### 2. 索引的分类 ###

不知道你们有没有这样一种情况，就是每次背面试题的时候发现好像有各种索引，怎么都理不清楚，但实际上我们对索引进行分类。

**数据结构的角度**

*  B+Tree 索引
 *  Hash 索引
 *  全文索引（Full-texts）

**物理存储角度**

*  聚集索引（聚簇索引）
 *  辅助索引（二级索引，非聚集索引）

**逻辑角度**

*  主键索引
 *  唯一索引
 *  普通索引
 *  前缀索引

**实际使用角度**

*  单列索引
 *  联合索引（复合索引）

### 3. 索引的数据结构 ###

InnoDB 存储引擎支持以下几种常见的索引：**B+Tree 索引**、**全文索引**、**哈希索引**，其中比较关键的是 B+Tree 索引，

B+Tree 索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引。B+Tree 索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。注意B+Tree 中的 B 不是代表二叉(binary)，而是代表平衡(balance)，因为 B+Tree 是从最早的平衡二叉树演化而来，但是 B+Tree 不是一个二叉树。

**知识补充**

#### 3.1 [二叉树][Link 1] ####

树中节点的子节点不超过2的**有序树**，通俗来说就是节点**左小右大**。

![image-20210804135913978][]

#### 3.2 [二叉查找树][Link 2] ####

二叉查找树（BST，Binary Sort Tree），也称二叉排序树，或二叉搜索树。一棵二叉查找树满足以下条件：

*  首先它是一棵二叉树
 *  左子树的所有值均小于根节点的值
 *  右子树的所有值均大于根节点的值
 *  左右子树同时也满足以上两点

![image-20211124205820679][]

#### 3.3 [平衡二叉树][Link 2] ####

**通过二叉查找树的查找操作可以发现，一棵二叉查找树的查找效率取决于树的高度，如果使树的高度最低，那么树的查找效率也会变高**。

如下面一个二叉树，全部由右子树构成：

![image-20211124210054316][]

此时可以看到，这个二叉树查找树实际上**退化**为**类似链表**的结构了，如果我们要找 60 就得一直遍历到最后一个才能找到，查找时间复杂度为 O(n)，所以效率并不会高。

**那么什么是平衡二叉树（AVL）**？

平衡二叉树，是一个二叉排序树，同时任意节点左右两个子树的高度差（或平衡因子，简称**BF**）的绝对值不超过1，并且左右两个子树也满足，平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(logn)，因此查找性能必然高于普通二叉查找树。

![image-20211124210447468][]  
对于时间复杂度，我之前也写过一篇文章：[时间复杂度和空间复杂度][Link 3]  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXl1ZeOAgQ_size_20_color_FFFFFF_t_70_g_se_x_16]

那么问题来了，既然平衡二叉树查找效率高，为什么不用它而要用 B+Tree 呢？

平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一棵[最优二叉树][Link 1]，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需建立一棵平衡二叉树即可。

平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，为了保持平衡，需要 1 次或多次**左旋**和**右旋**来得到插入、更新和删除后树的平衡性。（关于AVL的调整可以我之前写的：[AVL 树的调整][Link 2]）。

**平衡二叉树对一个数据库来说，有什么问题**？

因为二叉树每个节点**最多只有两个子节点**，所以当节点数比较多时，二叉树的高度增长很快，比如 1000 个节点时，树的高度差不多有 9 到 10 层。我们知道数据库是持久化的，数据是要从磁盘上读取的，一般的机械磁盘每秒至少可以做 100 次 IO，一次 IO 的时间基本上在 0.01 秒，1000 个节点在查找时就需要 0.1 秒，如果是 10000 个节点，100000 个节点呢？所以对数据库来说，为了减少树的高度，提出了 B+Tree 的数据结构，在这之前，先看看B-Tree。

#### 3.4 B-Tree ####

在二叉树中，每个节点有一个数据项（可以理解为节点的值，key），且每个节点有最多有 2 个子节点，如果允许每个节点可以有更多的数据项和子节点，就是**多叉树**，如果这棵树还是平衡树，那么就可以叫他 B-Tree（B树，不叫 B 减树），B树的主要特点如下：

1.  B树的节点中存储着多个元素，每个内节点有多个分叉；
2.  节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据；
3.  父节点当中的元素不会出现在子节点中；
4.  所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

如 2-3-4 树就属于B树的一种。

2-3-4 树是一种阶（**阶，可以理解为节点的分支**）为 4 的 B树，所以也可称它为 4 叉树，主要具备以下性质：

1.  有 1 个 key 的节点有2个子节点，有 2 个 key 的有 3 个子节点，有 3 个 key 的有 4 个子节点，对应 key 的节点分别称为**2节点**，**3节点**，**4节点**，这也是为什么叫 2-3-4 树的原因；
2.  所有的叶子节点总在同一层；
3.  每个节点的值从左到右保持了从小到大的顺序，两个 key 之间的子树中所有的 key 一定大于它的父节点的左key，小于父节点的右 key。如下：
    
    ![image-20211124212423853][]

如下面这棵 2-3-4 树：

![image-20211124212447179][]

很明显，节点多，高度就降低了，比如：有1-100个数，二叉树一次只能分两个范围，0-50和51-100，而B树，分成4个范围 1-25， 25-50，51-75，76-100一次就能筛选走四分之三的数据。那么为什么选择 B+Tree 而不是 B-Tree 呢？

我们先看这样一棵 B-Tree ：

![image-20211126155034700][]

我们知道，**数据库索引是存储在磁盘上的**，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个**磁盘页**（对应索引树的节点）。所以我们要减少 IO 次数，对于树来说，IO 次数就是树的高度。

比如在上图中我们要查目标元素 8 的值：

1.  第一次 IO，把 5 和 20 所在的节点加载到内存中，把目标元素与 5 和 20 比较，定位到中间区域 p2 指针所对应的节点；
2.  第二次 IO，将 7 和 10 所在节点加载到内存中，把目标元素与 7 和 10 比较，定位到中间区域 p2 指针所对应的节点；
3.  第三次 IO，将节点 8 和 9 加载到内存中，把目标元素与 8和 9 比较发现存在目标值，于是找到目标元素。

可以看到，B-Tree 在查询时的比较次数并不比二叉树少，尤其是节点中的数非常多时，但是**内存的比较速度非常快，耗时可以忽略**，所以只要树的高度低，IO 少，就可以提高查询性能，这是B-Tree的优势之一。

在来看看 B+Tree。

#### 3.5 B+Tree索引 ####

B+树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树由 **B-Tree和索引顺序**访问方法演化而来，但是在现实使用过程中几乎已经没有使用 B 树的情况了。

B+Tree 的定义在很多数据结构书中都能找到，非常复杂，我们概略它的定义：

B+Tree 是 B-Tree 的一种变形形式，B+Tree 上的叶子结点存储关键字以及相应记录的地址，**叶子结点以上各层作为索引使用**。一棵 m 阶的 B+Tree定义如下：

*  每个节点最多可以有 m 个元素；
 *  除了根节点外，每个节点最少有 (m/2) 个元素；
 *  如果根节点不是叶节点，那么它最少有 2 个孩子节点；
 *  所有的叶子节点都在同一层；
 *  一个有 k 个孩子节点的非叶子节点有 (k-1) 个元素，按升序排列；
 *  某个元素的左子树中的元素都比它小，右子树的元素都大于或等于它；
 *  非叶子节点只存放关键字和指向下一个孩子节点的索引，记录只存放在叶子节点中；
 *  **相邻的叶子节点之间用指针相连**。

概念很繁琐，但是我们可以总结出 B+Tree 和 B-Tree 的**区别**：

1.  B+Tree 非叶子节点不会存储数据；
2.  B+Tree 叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

看这样一棵 B+Tree：

![image-20211126161238129][]

如果要找目标元素 8，肯定也是要进行 3 次 IO 操作（同 B-Tree），这是等值查询的情况，如果我们要进行范围查询呢？比如我们要查询 8~25 之间的数据。

1.  第一步肯定是先查询到最小值 8 的元素，此时进行了 3 次 IO；
2.  因为各叶子节点通过指针连接，是一个有序的链表，因此只需要遍历由叶子结点组成的链表即可，直到找到最大值 25。

那么对比 B-Tree 的范围查找呢？

因为 B-Tree 的每个节点都是存在数据的，所以，当在叶子节点找到 8 时，但是确找不到最大值就需要在回头进行遍历查找，也就是需要进行**中序遍历**。

树的遍历有三种方法。前序（preorder），中序（inorder），后序（postorder）。

![image-20211125214538255][]

**所以，对于MySQL的索引为什么要使用 B+Tree 而不是 B-Tree**？

总的来说，影响mysql查找性能的主要还是磁盘 IO 的次数，而 B-Tree 不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少，指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。

#### 3.6 Hash索引 ####

Hash索引 和 B+Tree主要存在以下的区别：

1.  单条数据查询时，Hash索引查询时间复杂度为O(1)， B+Tree索引时间复杂度为O(logN)；
2.  Hash索引只适合等值查询，但是无法进行范围查询，因为是随机分布的；
3.  哈希索引没有办法利用索引完成排序，还是因为数据是随机分布的；
4.  哈希索引不支持联合索引的最左匹配规则；
5.  如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在**哈希碰撞**，数据量越大，哈希碰撞几率越高。

#### 3.7 全文索引 ####

> 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

了解了索引的数据结构，再来了解各索引的区别。

### 4. 物理存储角度分类 ###

#### 4.1 聚集索引（聚簇索引） ####

InnoDB 中的索引自然也是按照 B+树来组织的，前面我们说过 B+树的叶子节点用来放数据的，但是放什么数据呢？索引自然是要放的，因为 B+树的作用本来就是就是为了快速检索数据而提出的一种数据结构，不放索引放什么呢？但是数据库中的表，数据才是我们真正需要的数据，索引只是辅助数据，甚至于一个表可以没有自定义索引。那么 InnoDB 中的数据到底是如何组织的？

InnoDB 中使用了聚集索引，就是将表的主键用来构造一棵 B+树，并且将整张表的**行记录数据**存放在该 B+Tree 的叶子节点中，即**叶子节点存储着「主键和当前行的数据」**。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以**每张表只能拥有一个聚集索引**。

![image-20211129104929990][]

B+Tree 结构

![image-20211129114440364][]

聚集索引的叶子节点就是数据页。换句话说，**数据页上存放的是完整的每行记录**。因此聚集索引的的优点就是：

1.  通过聚集索引能获取完整的整行数据；
2.  对于主键的排序查找和范围查找速度非常快。

如果我们没有定义主键呢？MySQL 会使用唯一性索引，没有唯一性索引，MySQL 也会创建一个隐含列 RowID 来做主键，然后用这个主键来建立聚集索引。

#### 4.2 辅助索引（二级索引） ####

上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为 B+Tree 中的数据都是按照主键进行排序的，那如果我们想以别的列作为搜索条件怎么办？我们一般会建立多个索引，这些索引被称为**辅助索引**（二级索引，或者非聚集索引），**叶子节点存储着「主键和当前索引列值」**。

对于辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签( bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

对 name 创建索引，则叶子节点存储数据如下：

![image-20211129114630171][]

#### 4.3 回表 ####

由于辅助索引的节点没有存储全部的数据，当通过 name 要查询全部数据的时候怎么办？

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为**回表**。也就是根据辅助索引的值查询一条完整的用户记录需要使用到 2 棵 B+Tree ：一次辅助索引，一次聚集索引，如下：

![image-20211129134623236][]

#### 4.4 覆盖索引 ####

很明显，回表需要额外的 B+Tree 搜索过程，必然增大查询耗时。那什么情况下不需要回表呢？

我们知道在辅助索引的叶子节点中存储着主键和当前索引列值，如果我们只需要查询叶子结点中的值，那么就不需要回表，这种情况称为覆盖索引或称触发了索引覆盖。

select id,name from account where name='H';

覆盖索引严格来说并不是一种索引结构，可以理解为是一种优化手段，比如建立联合索引。

### 5. 逻辑角度分类 ###

#### 5.1 主键索引 ####

建立在主键字段上的索引，索引列中的值必须是唯一的，不允许有空值。

如：订单表（订单编号，名称，价格等等），订单编号是唯一标识的，可以作为主键。

#### 5.2 唯一索引 ####

建立在 UNIQUE 字段上的索引就是唯一索引，一张表可以有**多个**唯一索引，索引列值允许为 NULL，避免同一个表中某数据列中的值重复。

如：身份证号等。

#### 5.3 普通索引 ####

主键索引和唯一索引对字段的要求是要求字段为主键或 UNIQUE字段，而那些建立在普通字段上的索引叫做普通索引，既不要求字段为主键也不要求字段为 UNIQUE，index 和 key 关键字都可以设置普通索引。

#### 5.4 前缀索引 ####

前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个 bytes 建立的索引，而不是在整个字段上建索引。

例如，可以对上表中的 name 字段的前5个字符建立索引。

![image-20211129151907913][]

前缀索引可以建立在类型为`char、varchar、text、binary、varbinary`的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

### 6. 实际使用角度 ###

上面的索引都是建立在一个列上的，因此可以称为单列索引，而在实际使用过程中我们经常会建立2个甚至多个列的索引。

#### 6.1 联合索引 ####

将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引。

如：`index(a,b)`就是将 `a,b` 两个列组合起来构成一个索引。

建立联合索引只会建立 1 棵 B+Tree，多个列**分别建立索引**会分别以每个列建立 B+Tree，有几个列就有几个 B+Tree，比如，index(a)、 index(b)，就分别对 a，b 两个列各构建了一个索引，2 棵 B+Tree。

而`index(a,b)`在索引构建上，包含了两个意思：

1.  先把各个记录按照 a 列进行排序;
2.  在记录的 a 列相同的情况下，采用 b 列进行排序。

因此，对于一棵联合索引的 B+Tree 的其中节点如下：

![image-20211129211840463][]

按照上述说法，可以看到 a 的值是有顺序的，即`1,1,2,2,3,3`，而 b 的值为`1,2,1,4,1,2`，是无序的。

同时我们还可以发现在 a 值相等的情况下，b 值又是按顺序排列的，但是这种顺序是**相对**的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如 `a = 1 and b = 2`， `a,b`字段都可以使用索引，因为在 a 值确定的情况下 b 是相对有序的，而`a>1 and b=2`，a 字段可以匹配上索引，但 b 值不可以，因为a的值是一个范围，在这个范围中 b 是无序的。

#### 6.2 最左匹配原则 ####

上面的这种匹配方式，称之为**最左匹配原则**：

> 最左优先，以最左边的为起点任何**连续的索引**都能匹配上。同时遇到范围查询`(>、<、between、like)`就会停止匹配。

假设我们建立了这样一个联合索引`index(a,b,c)`，相当于创建了a、a-b、a-b-c 三个索引。

**1、全值匹配查询**

SELECT * FROM users WHERE a=1 AND b=3 AND c=1;
    
    SELECT * FROM users WHERE b=3 AND a=1 AND c=1;
    
    SELECT * FROM users WHERE c=1 AND a=1 AND b=3;

通过执行计划分析是否使用到了索引

EXPLAIN SELECT * FROM users WHERE c=1 AND a=1 AND b=3;
    ...

经过测试发现都使用到了索引：

![image-20211129214455464][]

不是说是最左原则吗？为什么 c 在最左边也能使用索引呢？

这是因为 Mysql 中有查询优化器，会自动优化查询顺序，它会判断纠正这条 sql 语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

**2、匹配左边的列**

等值查询的情况：

① 遵循最左原则，用到了索引。

select * from users where a = '1' 
    
    select * from users where a = '1' and b = '2'  
    
    select * from users where a = '1' and b = '2' and c = '3'

② 不遵循最左原则，没用到索引，全表扫描。

select * from users where  b = '2'; 
    
    select * from users where  c = '3';
    
    select * from users where  b = '1' and c = '3';

③ 如果是不连续的，则只用到了 a 的索引。

select * from users where a = '1' and c = '3';

**3、匹配范围值**

范围查询的情况：

① 对最左边的列进行范围查询，用到了索引。

select * from users where  a > 1 and a < 3;

② 多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到 B+Tree 索引，也就是只有 a 用到索引，在`1<a<3`的范围内 b 是无序的，不能用索引，找到`1<a<3`的记录后，只能根据条件 `b > 1`继续逐条过滤。

select * from users where  a > 1 and a < 3 and b > 1;

③ 左边的列等值匹配，范围匹配另外一列，用到了索引。

select * from users where  a = 1 and b > 3;

### 7. 其他索引 ###

#### 7.1 自适应哈希索引 ####

InnoDB 存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知道 B+树的查找次数，取决于 B+树的高度，在生产环境中，B+Tree的高度一般为 3~4 层,故需要 3~4 次的 IO 查询。

所以在 InnoDB 存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个 hash 索引，称之为自适应哈 希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引， 那么直接通过 hash 算法推导出记录的地址，直接一次就能查到数据，比重复去 B+Tree 索引中查询三四次节点的效率高了不少。

InnoDB 存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并**不能对其进行干预**。

通过命令 `show engine innodb status\G` 可以看到当前自适应哈希索引的使用状况。

### 8. MyISAM 中的索引 ###

我们知道 InnoDB 中索引即数据，也就是聚集索引的那棵 B+Tree 的叶子节点中已经把所有完整的用户记录都包含了，而 MyISAM 的索引方案虽然也使用树形结构，但是却将索引和数据分开存储的。

MyISAM 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。

由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

**使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中**。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值＋行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录。

这一点和 InnoDB 是完全不相同的，在 InnoDB 存储引擎中，我们只需要根据主键值对聚集索引进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次回表操作，意味着 MyISAM 中建立的索引相当于全部都是二级索引，如果有需要的话，我们也可以对其它的列分别建立索引或者建立联合索引，原理和 InnoDB 中的索引差不多，不过在叶子节点处存储的是相应的**列＋行号**，这些索引也全部都是二级索引。

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

### 9. 索引创建策略 ###

正确地创建和使用索引是实现高性能查询的基础。前面了解了索引相关的数据结构，各种类型的索引及其对应的优缺点，那么该如何正确的创建和使用索引呢？

#### 9.1 索引列的类型大小 ####

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有 TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增，我们这里所说的**类型大小指的就是该类型表示的数据范围的大小**。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不 要使用 BIGINT，能使用 NEDIUMINT 就不要使用 INT，这是因为:

*  数据类型越小，在查询时进行的比较操作越快（CPU 层次)
 *  数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 IO 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 IO。

注：在 InnoDB 里，默认定义的 B+Tree 的节点大小是 16KB，这就是说，假如一个 Key 是 8 个字节，那么一个节点可以存放大约 1000 个 Key，意味着 B+Tree 可以有 1000 个分叉。同时 InnoDB 每一次磁盘 IO，读取的都是 16KB 的 整数倍的数据。也就是说 InnoDB 在节点的读写上是可以充分利用磁盘顺序 IO 的高速读写特性。

#### 9.2 搜索、排序或分组 ####

一般可以为**只为用于搜索、排序或分组的列创建索引**。

也就是说，只为出现在 `WHERE` 子句中的列、连接子句中的连接列创建索引， 而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引；又或者为出现在 `ORDER BY` 或 `GROUP BY` 子句中的列创建索引。

#### 9.3 索引选择性 ####

在《高性能MySql》一书中的介绍：

> 索引的选择性是指，不重复的索引值（也称为基数）和数据表中的记录总数（\#T）的比值，范围从 1/\#T 到 1 之间。

**索引的选择性越高则查询效率越高**，因为选择性高的索引可以让Mysql在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好。

很差的索引选择性就是列中的数据重复度很高，比如性别字段，一般情况下，只有两种可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来。

比如下面这个表：

![image-20211130101256057][]

那么上表那个适合做索引呢，当然是姓名字段，因为里面的数据没有任何重复，性别字段是最不适合做索引的，因为数据的重复度非常高。

怎么计算索引的选择性？如上表：

性别的选择性：

![image-20211130114248586][]

姓名的选择性：

![image-20211130114334377][]

#### 9.4 建立联合索引 ####

创建联合索引最重要的一点就是索引的顺序，对于如何选择索引的列顺序有一个经验法则：**把频繁使用的列、区分度高的列放在前面**。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找，在这种情况下，这样设计的索引确实能够最快地过滤出需要的行。

另外，如果还能使用到覆盖索引，也是推荐建立联合索引的。

PS：什么叫区分度？ 我的理解其实就是选择性，只不过这涉及到前缀索引。

#### 9.5 建立前缀索引 ####

前面讲到对于字符类型的可以设立前缀索引，一般会对字段的前面几个字符设置索引，也就是说索引的长度不是越长越好。

举个栗子：王，王二，王二蛋

![image-20211130152925568][]

*  如果说前缀索引的长度为 1 的话，那么每一行的索引都是 王 这个字，就完全没有区分度；
 *  索引的长度为 3 ，区分度为 100%。

前缀索引的选择性（区分度）计算：

select count(distinct left(field,5))/count(*) from table;

![image-20211130152942155][]

而对于一些特殊的字段，如上表中的 url，都是已`http://www.` 开头，很显然这里的前缀索引需要超过 11，索引的长度过长不是我们所期望的，那么有没有后缀索引呢？

MySQL 原生并不支持反向索引，但是我们可以**把字符串反转后存储**，并基于此建立前缀索引也是可以的。

另外，MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

#### 9.6 冗余和重复索引 ####

MySQL 允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL 需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

#### 9.7 删除未使用的索引 ####

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议考虑删除。

### 10. 总结 ###

尽管索引能帮助我们提升查询效率，并不意味着它一定很好，或者什么时候都需要用索引，因此我们要明确它的优缺点。

*  优点：可以大大加快数据的检索速度
 *  缺点：
    
     *  时间方面：创建索引和维护索引需要消耗时间
     *  空间方面：索引需要占物理空间

**哪些情况下需要创建索引**：

1.  主键自动建立唯一索引
2.  频繁作为查询条件的字段
3.  多表关联查询中的关联字段
4.  排序的字段
5.  频繁查找的字段，可以建立联合索引，进行覆盖索引
6.  查询中统计或者分组字段

**哪些情况下不需要创建索引**：

1.  表记录太少
2.  经常进行增删改查操作的字段
3.  where条件里使用频率不高的字段

### 11. 参考 ###

*  [b树和b+树的区别][b_b]
 *  [浅谈 MySQL 索引的分类][MySQL]
 *  [Mysql最左匹配原则][Mysql]

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

[image-20211124200643776]: https://img-blog.csdnimg.cn/img_convert/06790e16bad09724d3e9adbdec736af7.png
[image-20211125091959077]: https://img-blog.csdnimg.cn/img_convert/6907176bfe0d968051f5f6f172c2a518.png
[Link 1]: https://javatv.blog.csdn.net/article/details/118741724
[image-20210804135913978]: https://img-blog.csdnimg.cn/img_convert/bf7353b5af8641722f613236a8a64bf8.png
[Link 2]: https://javatv.blog.csdn.net/article/details/118941644
[image-20211124205820679]: https://img-blog.csdnimg.cn/img_convert/e600393ce2bb3fb210009176a660d4dc.png
[image-20211124210054316]: https://img-blog.csdnimg.cn/img_convert/6136f020121d4d1779e0f8e37b534177.png
[image-20211124210447468]: https://img-blog.csdnimg.cn/img_convert/e31fbf3855d45ec46443f16c47376940.png
[Link 3]: https://javatv.blog.csdn.net/article/details/118705580
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXl1ZeOAgQ_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/b6c20f4bd0d240a99497f6eea0c231dc.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQXl1ZeOAgQ==,size_20,color_FFFFFF,t_70,g_se,x_16
[image-20211124212423853]: https://img-blog.csdnimg.cn/img_convert/a7d27c1e6ccd1cbe9114cfd106725255.png
[image-20211124212447179]: https://img-blog.csdnimg.cn/img_convert/6d97859cee628f0010939e9321efd8db.png
[image-20211126155034700]: https://img-blog.csdnimg.cn/img_convert/eb2b7d2ddcccc2c11bd2e3e62ca8cad1.png
[image-20211126161238129]: https://img-blog.csdnimg.cn/img_convert/152cd043531eedf851d32c0568ff830b.png
[image-20211125214538255]: https://img-blog.csdnimg.cn/img_convert/db195c58f509e90727ca549841a5252d.png
[image-20211129104929990]: https://img-blog.csdnimg.cn/img_convert/b6ff8f909a7d5c4121b7ccd83a1817f6.png
[image-20211129114440364]: https://img-blog.csdnimg.cn/img_convert/410ca179b33c2a362d16c9f2c38c9f83.png
[image-20211129114630171]: https://img-blog.csdnimg.cn/img_convert/4a2715dc5f5c3cf757ed625e0a1276ef.png
[image-20211129134623236]: https://img-blog.csdnimg.cn/img_convert/d08cb143692bfc96db29772f92632263.png
[image-20211129151907913]: https://img-blog.csdnimg.cn/img_convert/da499eff8122ff458dfa827904556aaa.png
[image-20211129211840463]: https://img-blog.csdnimg.cn/img_convert/7ebfdfbf224ed13a840f27e6a0509101.png
[image-20211129214455464]: https://img-blog.csdnimg.cn/img_convert/9b40812cc5bb8f7601279a653298abfc.png
[image-20211130101256057]: https://img-blog.csdnimg.cn/img_convert/e3c0328b09103e4bd0d787f82593f865.png
[image-20211130114248586]: https://img-blog.csdnimg.cn/img_convert/aecd034e4e5ae49059d69c19f65a0d5e.png
[image-20211130114334377]: https://img-blog.csdnimg.cn/img_convert/9c4b7e800aacfb79d24ae83824f3eff9.png
[image-20211130152925568]: https://img-blog.csdnimg.cn/img_convert/e3182a072d58a4398c55f7919bc5d5e9.png
[image-20211130152942155]: https://img-blog.csdnimg.cn/img_convert/7ff04471d85f3f1eb795f79eade7a319.png
[b_b]: https://blog.csdn.net/login_sonata/article/details/75268075
[MySQL]: https://blog.csdn.net/qq36846776/article/details/111524202
[Mysql]: https://blog.csdn.net/sinat_41917109/article/details/88944290
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXl1ZeOAgQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: https://img-blog.csdnimg.cn/f7162edba3a44aa78a45e5bd54c5e6d5.jpg?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQXl1ZeOAgQ==,size_20,color_FFFFFF,t_70,g_se,x_16