MySQL索引原理及实战

怼烎@ 2023-09-26 21:26 34阅读 0赞

### MySQL索引原理及实战 ###

*  一、一条Select语句
 *  二、索引介绍
 *   *  1、索引是什么
     *  2、索引的优势和劣势
     *   *  优势：
         *  劣势：
 *  三、索引的使用
 *   *  1、索引的类型
     *   *  \`普通索引：\`
         *  \`唯一索引：\`
         *  \`全文索引：\`
         *  \`空间索引\`：
         *  \`前缀索引\` ：
         *  按照索引列的数量\*
     *  2、删除索引
     *  3、查看索引
 *  四、索引的数据结构
 *   *  1、索引的要求
     *  2、数据结构的选用
     *   *  1）Hash表
         *  2）二叉查找树
         *  3）平衡二叉查找树
         *  4）B树：改造二叉树
         *  5）B+树：改造B树 （一）
         *  6) b+树(二)
 *  五、MySQL索引实现
 *   *  1、MyISAM索引
     *   *  1）主键索引
         *  2）辅助索引
     *  2、InnoDB索引
     *   *  1）InnoDB索引简介
         *  2）主键索引
         *   *  等值查询数据
             *  范围查询数据
         *  3）辅助索引
         *   *  等值查询数据
             *  范围查询数据
         *  4）组合索引
         *   *  1.组合索引存储结构
             *  2.组合索引的查找方式
             *  3.最左前缀匹配原则
             *  4.组合索引创建原则
         *  5）覆盖索引
         *  6）索引条件下推ICP
 *  六、索引创建原则
 *   *  1、哪些情况需要创建索引
     *  2、索引优化建议
 *  七 sql索引优化实战总结

# 一、一条Select语句 #

select * from city WHERE city_id=1

执行流程：  
![在这里插入图片描述][8d365cccd5454740ac0ff0c704d7869c.png]

# 二、索引介绍 #

## 1、索引是什么 ##

*  官方介绍索引是帮助MySQL**高效获取数据的数据结构**。更通俗的说，数据库索引好比是一本书前面 的目录，能**加快数据库的查询速度**。
 *  一般来说索引本身也很大，不可能全部存储在内存中，因此**索引往往是存储在磁盘上的**文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）
 *  **我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。**

## 2、索引的优势和劣势 ##

### 优势： ###

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

### 劣势： ###

*  **索引会占据磁盘空间**
 *  **索引虽然会提高查询效率，但是会降低更新表的效率**。比如每次对表进行增删改操作，MySQL不仅 要保存数据，还有保存或者更新对应的索引文件。

# 三、索引的使用 #

## 1、索引的类型 ##

*  `主键索引：`索引列中的值必须是唯一的，不允许有空值。

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

### `普通索引：` ###

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

ALTER TABLE table_name ADD INDEX index_name (column_name) ;

### `唯一索引：` ###

索引列中的值必须是唯一的，但是允许为空值。

CREATE UNIQUE INDEX index_name ON table(column_name) ;

### `全文索引：` ###

只能在文本类型`CHAR`,`VARCHAR`,`TEXT`类型字段上创建全文索引。字段长度比较大时， 如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。

> MyISAM和InnoDB中都可以使用全文索引。  
> InnoDB全文索引，[官网介绍][Link 1]  
> 全文搜索时候，全文索引一般很少使用，数据量比较少或者并发度低的时候可以用。但是数据量 大或者并发度高的时候一般是用专业的工具lucene,es，solr。

#创建表时，创建全文索引 
    CREATE TABLE `t_fulltext` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `content` varchar(100) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    FULLTEXT KEY `idx_content` (`content`) 
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
    #创建全文索引 
    ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX `idx_content`(`content`);

可以使用MATCH() … AGAINST语法执行全文搜索。

### `空间索引`： ###

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

### `前缀索引` ： ###

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

ALTER TABLE table_name ADD INDEX index_name (column1(length));

### 按照索引列的数量\* ###

单列索引：索引中只有一个列。  
组合索引：使用2个以上的字段创建的索引。

> 组合索引的使用，需要遵循**最左前缀原则**（最左匹配原则，后面详细讲解）。  
> 一般情况下，建议使用组合索引代替单列索引（主键索引除外，具体原因后面知识点讲解）。

ALTER TABLE table_name ADD INDEX index_name (column1,column2);

## 2、删除索引 ##

DROP INDEX index_name ON table

## 3、查看索引 ##

SHOW INDEX FROM table_name

# 四、索引的数据结构 #

## 1、索引的要求 ##

索引的数据结构，至少需要支持两种最常用的查询需求：

1.  等值查询：根据某个值查找数据，比如：

select * from t_user where age=76;

1.  范围查询：根据某个范围区间查找数据，比如：

select * from t_user where age>=76 and age<=86;

同时需要考虑时间和空间因素。在执行时间方面，我们希望通过索引，查询数据的时间尽可能小； 在存储空间方面，我们希望索引不要消耗太多的内存空间和磁盘空间。

## 2、数据结构的选用 ##

常用的数据结构：Hash表，二叉树，平衡二叉查找树（红黑树是一个近似平衡二叉树），B树，B+树。 [数据结构示例网站][Link 2]：可以通过动画看到操作过程，非常好的一个网站。

### 1）Hash表 ###

Hash表，Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash 表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率 很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

### 2）二叉查找树 ###

我们在来看看二叉树，二叉树是过我们最常用的一种树结构。  
**二叉树特点：** 每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。  
将age列构建二叉树，检索age=76的数据，只需要三次IO就可以查询到结果：  
30->86->76，查询效率貌似看是提升了一倍，可以看到二叉树的检索复杂度和树高相关。 ![在这里插入图片描述][89b98494a0644446b90d4056c96539fb.png]  
那是不是任何列使用二叉树效率都会提升呢？答案是否定的。比如，将id列构建二叉树，可以看到二叉 树退化为一个单向链表，查询id=6的数据，需要全表扫描。 ![在这里插入图片描述][07316daddd6e4529b0906747b5bccc7f.png]

### 3）平衡二叉查找树 ###

平衡二叉树是采用二分法思维，平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个 子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很 高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。查询id=6，只需要两次 IO。

![在这里插入图片描述][b7af3f7bbff94ca3b77ab92fa3198993.png]  
**平衡二叉树存在的问题**

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

### 4）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树，B树是一种多叉平衡查找树，如下图主要特点：

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

以下面的B树为例，我们的键值为表主键，具备唯一性。

![在这里插入图片描述][595cd7424e6e475ca27cbcc55876c62c.png]  
下面我们看一下，如何使用B树查询数据。  
**查询**  
**假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块7。**  
第一次磁盘IO： 将磁盘块1加载到内存中，在内存中从头遍历比较，15<17，走左路，到磁盘寻址磁 盘块2。  
第二次磁盘IO： 将磁盘块2加载到内存中，在内存中从头遍历比较，12<15，到磁盘中寻址定位到磁 盘块7。  
第三次磁盘IO： 将磁盘块7加载到内存中，在内存中从头遍历比较，15=15，找到15，取出data， 如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中 取出数据，查询终止。

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

**B树的缺点：**

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

### 5）B+树：改造B树 （一） ###

在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于 **非叶子节点是否存储数据**的问题

*  B树：非叶子节点和叶子节点都会存储数据。
 *  B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树的最底层叶子节点包含所有索引项。

B+树查找数据，由于数据都存放在叶子节点，所以每次查找都需要检索到叶子节点，才能查询到数据。 B树查找数据时，如果在内节点中查找到数据，可以立即返回，比如查找值等于17的数据，在根节点中直接就可以找到，不需要再向下查找，具备中路返回的特点。  
![在这里插入图片描述][353bc707084640bd9a377c834202e822.png]  
下面我们看一下，如何使用B+树如何查询数据。

**等值查询**  
**假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块5。**  
第一次磁盘IO： 将磁盘块1加载到内存中，在内存中从头遍历比较，15<28，走左路，到磁盘寻址磁 盘块2。  
第二次磁盘IO： 将磁盘块2加载到内存中，在内存中从头遍历比较，10<15<17，到磁盘中寻址定位 到磁盘块5。  
第三次磁盘IO： 将磁盘块5加载到内存中，在内存中从头遍历比较，15=15，找到15，取出data， 如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中 取出数据，查询终止。

**范围查询**  
**假如我们想要查找15和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块5。**

1.  首先查找值等于15的数据，将值等于15的数据缓存到结果集。这一步和前面等值查询流程一样，发 生了三次磁盘IO。
2.  查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块5，键值15开始向后遍历筛选所有 符合筛选条件的数据。
3.  第四次磁盘IO：根据磁盘5后继指针到磁盘中寻址定位到磁盘块6，将磁盘6加载到内存中，在内存 中从头遍历比较，15<17<26，15<26<=26，将data缓存到结果集。
4.  主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。  
    可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。下面 我们一起来看看InnoDB和MyISAM中是如何使用B+树构建索引的。

### 6) b+树(二) ###

个人觉得此分析比较好理解  
来源：[MySQL索引原理及慢查询优化][MySQL]  
![在这里插入图片描述][f727912c39304c84b15bae6f9ab5568b.png]  
如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

**b+树的查找过程**  
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

**b+树性质**

1.  通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.  当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

[最左前缀有手就会，那索引下推呢？][Link 3]

# 五、MySQL索引实现 #

## 1、MyISAM索引 ##

我们以`t_user_myisam`为例来说明。`t_user_myisam`的`id列为主键`，`age列为普通索引`。

CREATE TABLE `t_user_myisam` ( 
    `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;

![在这里插入图片描述][2c551ad38f5a4ab68f0dd1c49ddca176.png]

MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的 键值为索引列的值，数据为索引所在行的磁盘地址。

### 1）主键索引 ###

![在这里插入图片描述][296038b98ec9420995705b2df6ec31f2.png]  
表`t_user_myisam`的索引存储在索引文件`t_user_myisam.MYI`中，数据文件存储在数据文件 `t_user_myisam.MYD`中。

**等值查询数据**

select * from t_user_myisam where id=30;

> 1.  先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
> 2.  将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
> 3.  检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项。（1 次磁盘IO）
> 4.  从索引项中获取磁盘地址，然后到数据文件t\_user\_myisam.MYD中获取对应整行记录。（1次磁盘 IO）
> 5.  将记录返给客户端。  
>     **磁盘IO次数：3+1次。**

**范围查询数据**

select * from t_user_myisam where id between 30 and 49;

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

MyISAM在查询时，会将索引节点缓存在MySQL缓存中，而数据缓存依赖于操作系统自身的缓存。

### 2）辅助索引 ###

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记 录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也 需要按照范围查询的方式在辅助索引树中检索数据。

## 2、InnoDB索引 ##

### 1）InnoDB索引简介 ###

[InnoDB索引-官方文档：][InnoDB_-]

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

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

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

下面我们一起来看一看这两种索引的实现。

我们以t\_user\_innodb为例，来说明。t\_user\_innodb的id列为主键，age列为普通索引。

t\_user\_innodb的表结构和数据与MyISAM引擎表t\_user\_myisam完全一致。

CREATE TABLE `t_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;

![在这里插入图片描述][a26d765811854cc59a6976e037ceb0c2.png]  
InnoDB的数据和索引存储在一个文件t\_user\_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。

### 2）主键索引 ###

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。  
InnoDB要求表必须有一个主键索引(MyISAM 可以没有)。  
![在这里插入图片描述][6383e7c54f6a430aa36d06164bc8b385.png]

#### 等值查询数据 ####

select * from t_user_innodb where id=30;

1.  先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
2.  将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
3.  检索到叶节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，直接 可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）  
    ![在这里插入图片描述][d57e9606426d4bb8811a48c79a6d0dc1.png]  
    **磁盘IO次数：3次。**

#### 范围查询数据 ####

select * from t_user_innodb where id between 30 and 49;

1.  先在主键树中从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（1次磁盘IO）
2.  将左子树节点加载到内存中，比较20<30<49，向下检索。（1次磁盘IO）
3.  检索到叶节点，将节点加载到内存中遍历比较20<30，30<=30<49。查找到值等于30的索引项。获 取行数据缓存到结果集中。（1次磁盘IO）
4.  向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，30<49<=49，获取行数据缓存到 结果集中。（1次磁盘IO）
5.  最后得到2条符合筛选条件，将查询结果集返给客户端。

可以看到，因为在主键索引中直接存储了行数据，所以InnoDB在使用主键查询时可以快速获取行数 据。当表很大时，与在索引树中存储磁盘地址的方式相比，因为不用再去磁盘中获取数据，所以聚簇索 引通常可以节省磁盘IO操作。  
**磁盘IO次数：2次+检索叶子节点数量。**

### 3）辅助索引 ###

**除聚簇索引之外的所有索引都称为辅助索引，InnoDB的辅助索引只会存储主键值而非磁盘地址。**  
以表t\_user\_innodb的age列为例，age索引的索引结果如下图。  
![在这里插入图片描述][9a148b3ffed64607877d8995dd94aeb9.png]  
底层叶子节点的按照（age，id）的顺序排序，先按照age列从小到大排序，age列相同时按照id列从 小到大排序。  
使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记 录。

#### 等值查询数据 ####

select * from t_user_innodb where age=22;

1.  先在索引树中从根节点开始检索，将根节点加载到内存，比较22<77，走左路。（1次磁盘IO）
2.  将左子树节点加载到内存中，比较22<34，向下检索。（1次磁盘IO）
3.  检索到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）  
    第一项5： 5<22不符合要求，丢弃。  
    第二项22： 等于22，符合要求，获取主键id=18，去主键索引树中检索id=18的数据放入结果集 中。（回表：3次磁盘IO）。  
    第三项22： 等于22，符合要求，获取主键id=49，去主键索引树中检索id=49的数据放入结果集 中。（回表：3次磁盘IO）  
    22=22,22=22。查找到值等于30的索引项，直接可以获取整行数据。将改记录返回给客户端。
4.  向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较。（1次磁盘IO）  
    第一项34： 34>22不符合要求，丢弃。查询结束。
5.  最后得到2条符合筛选条件，将查询结果集返给客户端。

**根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询**  
![在这里插入图片描述][2e36fcd37c9b469a996e57ba570948ce.png]  
**磁盘IO次数：2次+检索叶子节点数量+记录数\*3。**

#### 范围查询数据 ####

select * from t_user_innodb where age between 30 and 49;

辅助索引的范围查询流程和等值查询基本一致，先使用辅助索引到叶子节点检索到第一个符合条件的 索引项，然后向后遍历，直到遇到第一个不符合条件的索引项，终止。

检索过程中需要将符合筛选条件的id值，依次到主键索引检索将检索的数据放入结果集中。

最后将查询结果返回客户端。

### 4）组合索引 ###

#### 1.组合索引存储结构 ####

我们在使用索引时，组合索引是我们常用的索引类型。那组合索引是如何构建的，查找的时候又是如何 进行查找的呢？  
`表t_multiple_index`，`id为主键列`，创建了一个`联合索引idx_abc(a,b,c)`，**构建的B+树索引结构如图所示**。索引树中节点中的索引项按照`（a，b，c）的顺序从大到小排列`，先按照a列排序，a列相同时按照b 列排序，b列相同按照c列排序。在最底层的叶子节点中，如果两个索引项的a，b，c三列都相同，索引项按照主键id排序。 所以组合索引的最底层叶子节点中不存在完全相同的索引项。

CREATE TABLE `t_multiple_index` ( 
    `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;

![在这里插入图片描述][3ca7c9633b6848cd84f1b221fe992ec3.png]  
![在这里插入图片描述][91f43a8a66e645daa3dc9ce5d80c5737.png]

#### 2.组合索引的查找方式 ####

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

1.  先在索引树中从根节点开始检索，将根节点加载到内存，先比较a列，a=14，14>13,走左路。（1 次磁盘IO）
2.  将左子树节点加载到内存中，先比较a列，a=13，比较b列b=14，14<16，走右路，向下检索。（1 次磁盘IO）
3.  达到叶节点，将节点加载到内存中从前往后遍历比较。（1次磁盘IO）  
    第一项（13,14,3,id=4）： 先比较a列，a=13，比较b列b=14，b!=16不符合要求，丢弃。  
    第二项（13,14,4,id=1）： 一样的比较方式，a=13，b=16，c=4 满足筛选条件。取出索引data值 即主键id=1，再去主键索引树中检索id=1的数据放入结果集中。（回表：3次磁盘IO）  
    第三项（13,14,5,id=3）： a=13，b=16，c!=4 不符合要求，丢弃。查询结束。
4.  最后得到1条符合筛选条件，将查询结果集返给客户端。

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

#### 3.最左前缀匹配原则 ####

**最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。**  
在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，b列 只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。  
所以当我们使用 `where a=13 and b=16 and c=4`去查询数据的时候，B+树会先比较a列来确定下一步 应该搜索的方向，往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列，B+树就不知道 第一步应该从哪个节点查起。![在这里插入图片描述][babc81a36e1a45ed8b25143ae87584c8.png]  
所以联合索引只能从第一列开始查找，比如以下三个查询都可以使用`idx_abc索引树`，检索数据。

select * from  t_multiple_index where a=13; 
    select * from  t_multiple_index where a=13 and b=16; 
    select * from  t_multiple_index where a=13 and b=16 and c=4; 
    select * from  t_multiple_index where a=13 and b>13; 
    select * from  t_multiple_index where a>11 and b=14; 
    select * from  t_multiple_index where a=16 and c=4;

而如果查`询条件不包含a列`，比如筛选条件只有`(b，c)或者c列是无法使用组合索引`的。下面的查询没有 用到索引。

select * from  t_multiple_index where b=16 and c=4;
    select * from  t_multiple_index where c=4;

所以创建的`idx_abc(a,b,c)索引`，相当于创建了`(a)`、`（a,b）`、`（a,b,c）`三个索引。

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

**另外，我们还需要注意的是，书写SQL条件的顺序，不一定是执行时候的where条件顺序。优化器会帮 助我们优化成索引可以识别的形式**。比如:

select * from  t_multiple_index where b=16 and c=4 and a=13; 
    #等价于下面的sql，优化器会按照索引的顺序优化 
    select * from t_multiple_index where b=16 and c=4 and a=13;

一颗索引树等价与三颗索引树，从另一方面了说，组合索引也为我们节省了磁盘空间。**所以在业务中 尽量选用组合索引，能使用组合索引就不要使用单列索引。**

> **索引使用口诀**  
> 全值匹配我最爱，最左前缀要遵守；  
> 带头大哥不能死，中间兄弟不能断；  
> 索引列上不计算，范围之后全失效；  
> Like百分写最右，覆盖索引不写星；  
> 不等空值还有OR，索引失效要少用。

#### 4.组合索引创建原则 ####

1.  频繁出现在where条件中的列，建议创建组合索引。
2.  频繁出现在order by和group by语句中的列，建议按照顺序去创建组合索引。  
    order by a,b 需要组合索引列顺序（a,b）。如果索引的顺序是（b,a），是用不到索引的。
3.  常出现在select语句中的列，也建议创建组合索引。

对于第1种情况和第3种情况，组合索引创建的顺序对其来说是等价的，这种情况下组合索引中的 顺序，是很重要的。由于组合索引会使用到最左前缀原则，使用频繁的列在创建索引时排在前面。

**大家思考个问题，这个是咱们同学遇到的一个面试题。下面的SQL语句除了建ab联合索引，还有更好 的方案吗？**

select * from t where a=1 and b>2 order by c

可以考虑`建立 （a,c）联合索引`：select \* from xxx where a=1 and b>2 order by c 这样 a等值查询 c就是已经排好序的了。这种情况实际上比较的是b的区分度和c的区分度，如果b的区分度比较差，建议 使用ac。如果c的区分度比较差，建议使用a,b。

### 5）覆盖索引 ###

根据在辅助索引树查询数据时，首先通过辅助索引找到主键值，然后需要再根据主键 值到主键索引中找到主键对应的数据。这个过程称为回表。

使用辅助索引查询比基于主键索引的查询多检索了一棵索引树，那是不是所有使用辅助索引的查询都 需要回表查询呢？

表t\_multiple\_index，组合索引idx\_abc(a,b,c)的叶子节点中包含(a,b,c,id)四列的值，对于以下查询语句

select a from  t_multiple_index where a=13 and b=16; 
    select a,b from  t_multiple_index where a=13 and b=16;
    select a,b,c from  t_multiple_index where a=13 and b=16; 
    select a,b,c,id from  t_multiple_index where a=13 and b=16;

select中列数据，如果可以直接在辅助索引树上全部获取，也就是说索引树已经“覆盖”了我们的查询需 求，这时MySQL就不会白费力气的回表查询，这中现象就是覆盖索引。使用explain工具查看执行计 划，可以看到extra中“Using index”，代表使用了覆盖索引。

![在这里插入图片描述][074a402a4c214c7784e19f6fa3131cd7.png]  
**将上面的语句，改为如下语句。大家猜猜这时会不会用到组合索引？**

select a,b from  t_multiple_index where  b=16;

![在这里插入图片描述][e804f57fc6014d1d8a320fa29e707faa.png]  
上面的查询语句用到了覆盖索引进行全表扫描。MySQL基于成本考虑，会使用了覆盖索引进行全表扫 描，使用覆盖索引可以减少了磁盘IO次数，显著提升查询性能。

覆盖索引相比与主键索引一个索引项占用的空间少，覆盖索引一个叶子节点中的就可以比主键索引存 放更多的数据量，相应的存放数据用到的总叶子树很少一些。

**覆盖索引是一种很常用的优化手段。**

### 6）索引条件下推ICP ###

官方索引条件下推： Index Condition Pushdown,简称ICP。是MySQL5.7对使用索引从表中检索行的 一种优化。可以通过参数optimizer\_switch控制ICP的开始和关闭。

#optimizer_switch优化相关参数开关 
    mysql> show VARIABLES like 'optimizer_switch'\G; 
    #关闭ICP 
    SET optimizer_switch = 'index_condition_pushdown=off';
    #开启ICP 
    SET optimizer_switch = 'index_condition_pushdown=on';

ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。可用于 InnoDB 和 MyISAM表，对于InnoDB表ICP仅用于辅助索引。

我们以InnoDB的辅助索引为例，来讲解ICP的作用。

大家有没有一个疑问，MySQl在使用组合索引在检索数据时是使用最左前缀原则来定位记录，那不满 足最左前缀的索引列，MySQL会怎么处理？

select * from  t_multiple_index where a=13 and b>=15  and c=5 and d='pdf';

![在这里插入图片描述][b64efb9beaea41c09fce19913a4d9079.png]  
根据最左前缀匹配原则，这个SQL语句会使用组合索引idx\_abc(a,b,c)的（a,b）两列来检索记录。

MySQL首先会在组合索引中定位到第一个满足a=13 and b>=15的索引项，MySQL之后会怎么处理 呢？使用explain工具，查看执行计划，extra列中的“Using index condition”执行器表示使用了索引条件 下推ICP。

ICP是MySQL 5.6引入的新特性，在使用ICP和不使用ICP时MySQL的执行情况会有所不同。

在MySQL 5.6之前， 不使用ICP时，MySQL只能从索引项（13,16,4,1）开始，一个个回表查询找到行 数据，然后再在服务层过滤后，返回给客户端。

关闭ICP，使用explain工具，查看执行计划，extra列中的“Using where”执行器表示没有使用了索引 条件下推ICP。 ![在这里插入图片描述][de3f6d2aafaf4a0c95a3618d22922071.png]  
![在这里插入图片描述][0866d1b268f240e9b4efaa61ea2e3081.png]  
具体步骤如下：

1.  执行器使用索引(a,b,c)，筛选条件a=13 and b>=15，调用存储引擎"下一行"接口。根据最左前缀原 则联合索引检索定位到索引项（13,16,4,id=1），然后使用id=1回表查询，获得id=1的行记录。返 回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf’过滤，不符合要求，直接丢弃。
2.  执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=3）,使用id=3回表获得id=3 的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf’过滤，符合要求， 缓存到结果集。
3.  执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,使用id=6回表获得id=6 的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件c=5 and d='pdf’过滤，不符合要 求，直接丢弃。
4.  执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行 器终止查询。
5.  最终获取一条记录，返回给客户端。  
    可以看到，在不使用ICP时，回表查询了3次，然后在服务层筛选后（筛选3次），最后返回客户端。  
    在MySQL 5.6 引入了ICP，可以在索引遍历过程中，对where中包含的索引条件先做判断，只有满足条件 的才会回表查询读取行数据。这么做可以减少回表查询，从而减少磁盘IO次数。

![在这里插入图片描述][3d96ff4a21aa4e9da3be1e153d34d7a2.png]  
**使用ICP时，具体步骤如下：**

1.  执行器使用索引(a,b,c)，筛选条件a=13 and b>=15 and c=5，调用存储引擎"下一行"接口。根据最 左前缀原则联合索引检索定位到索引项（13,16,4,id=1），然后使用ICP下推条件c=5判断，不满足 条件，直接丢弃。  
    向后遍历判断索引项（13,16,5,id=3），满足筛选条件a=13 and b>=15 and c=5，使用id=3回表获 得id=3的行记录。返回给MySQL服务层，MySQL服务层使用剩余条件d='pdf’过滤，符合要求，缓 存到结果集。
2.  执行器调用"下一行"接口，存储引擎遍历向后找到索引项（13,16,5,id=6）,满足筛选条件a=13 and b>=15 and c=5，使用id=6回表获得id=6的行记录。返回给MySQL服务层，MySQL服务层使用剩 余条件d='pdf’过滤，不符合要求，直接丢弃。
3.  执行器调用"下一行"接口，存储引擎遍历向后找到索引项（14,14,14,id=8）不满足筛选条件，执行 器终止查询。
4.  最终获取一条记录，返回给客户端。  
    可以看到，在使用ICP时，回表查询了2次，然后在服务层筛选后（筛选2次），最后返回客户端。

不使用ICP，不满足最左前缀的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server 层进行的。  
使用ICP，所有的索引条件的比较是在存储引擎层进行的，非索引条件的比较是在Server层进行的。  
对比使用ICP和不使用ICP，可以看到使用ICP可以有效减少回表查询次数和返回给服务层的记录数，从而 减少了磁盘IO次数和服务层与存储引擎的交互次数。

# 六、索引创建原则 #

## 1、哪些情况需要创建索引 ##

1.  频繁出现在where 条件判断，order排序，group by分组字段
2.  select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
3.  多表join关联查询，on字段两边的字段都要创建索引

## 2、索引优化建议 ##

1.  表记录很少不需创建索引 （索引是要有存储的开销）.
2.  一个表的索引个数不能过多。  
    （1）空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。  
    （2）时间：  
    更新（插入/Delete/Update）变慢。需要更新所有的索引树。  
    太多的索引也会增加优化器的选择时间。  
    所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。
3.  频繁更新的字段不建议作为索引。  
    频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。
4.  区分度低的字段，不建议建索引。（仅供参考）  
    比如性别，男，女；比如状态。区分度太低时，会导致扫描行数过多，再加上回表查询的消耗。 如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。  
    姓名，手机号就非常适合建索引。
5.  在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段。  
    主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查 询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越 长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。
6.  不建议用无序的值作为索引。例如身份证、UUID  
    更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。
7.  尽量创建组合索引，而不是单列索引。  
    优点：  
    （1）1个组合索引等同于多个索引效果，节省空间。  
    （2）可以使用覆盖索引  
    创建原则：组合索引应该把把频繁的列，区分度高的值放在前面。频繁使用代表索引的利用率高， 区分度高代表筛选粒度大，可以尽量缩小筛选范围。

# 七 sql索引优化实战总结 #

[sql索引优化实战总结][sql]

[8d365cccd5454740ac0ff0c704d7869c.png]: https://img-blog.csdnimg.cn/8d365cccd5454740ac0ff0c704d7869c.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-ind%20ex.html
[Link 2]: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
[89b98494a0644446b90d4056c96539fb.png]: https://img-blog.csdnimg.cn/89b98494a0644446b90d4056c96539fb.png
[07316daddd6e4529b0906747b5bccc7f.png]: https://img-blog.csdnimg.cn/07316daddd6e4529b0906747b5bccc7f.png
[b7af3f7bbff94ca3b77ab92fa3198993.png]: https://img-blog.csdnimg.cn/b7af3f7bbff94ca3b77ab92fa3198993.png
[595cd7424e6e475ca27cbcc55876c62c.png]: https://img-blog.csdnimg.cn/595cd7424e6e475ca27cbcc55876c62c.png
[353bc707084640bd9a377c834202e822.png]: https://img-blog.csdnimg.cn/353bc707084640bd9a377c834202e822.png
[MySQL]: https://tech.meituan.com/2014/06/30/mysql-index.html
[f727912c39304c84b15bae6f9ab5568b.png]: https://img-blog.csdnimg.cn/f727912c39304c84b15bae6f9ab5568b.png
[Link 3]: https://blog.csdn.net/m0_64355285/article/details/121874502
[2c551ad38f5a4ab68f0dd1c49ddca176.png]: https://img-blog.csdnimg.cn/2c551ad38f5a4ab68f0dd1c49ddca176.png
[296038b98ec9420995705b2df6ec31f2.png]: https://img-blog.csdnimg.cn/296038b98ec9420995705b2df6ec31f2.png
[InnoDB_-]: https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
[a26d765811854cc59a6976e037ceb0c2.png]: https://img-blog.csdnimg.cn/a26d765811854cc59a6976e037ceb0c2.png
[6383e7c54f6a430aa36d06164bc8b385.png]: https://img-blog.csdnimg.cn/6383e7c54f6a430aa36d06164bc8b385.png
[d57e9606426d4bb8811a48c79a6d0dc1.png]: https://img-blog.csdnimg.cn/d57e9606426d4bb8811a48c79a6d0dc1.png
[9a148b3ffed64607877d8995dd94aeb9.png]: https://img-blog.csdnimg.cn/9a148b3ffed64607877d8995dd94aeb9.png
[2e36fcd37c9b469a996e57ba570948ce.png]: https://img-blog.csdnimg.cn/2e36fcd37c9b469a996e57ba570948ce.png
[3ca7c9633b6848cd84f1b221fe992ec3.png]: https://img-blog.csdnimg.cn/3ca7c9633b6848cd84f1b221fe992ec3.png
[91f43a8a66e645daa3dc9ce5d80c5737.png]: https://img-blog.csdnimg.cn/91f43a8a66e645daa3dc9ce5d80c5737.png
[c9ad76d754ef4e9481025067531e05f2.png]: https://img-blog.csdnimg.cn/c9ad76d754ef4e9481025067531e05f2.png
[babc81a36e1a45ed8b25143ae87584c8.png]: https://img-blog.csdnimg.cn/babc81a36e1a45ed8b25143ae87584c8.png
[074a402a4c214c7784e19f6fa3131cd7.png]: https://img-blog.csdnimg.cn/074a402a4c214c7784e19f6fa3131cd7.png
[e804f57fc6014d1d8a320fa29e707faa.png]: https://img-blog.csdnimg.cn/e804f57fc6014d1d8a320fa29e707faa.png
[b64efb9beaea41c09fce19913a4d9079.png]: https://img-blog.csdnimg.cn/b64efb9beaea41c09fce19913a4d9079.png
[de3f6d2aafaf4a0c95a3618d22922071.png]: https://img-blog.csdnimg.cn/de3f6d2aafaf4a0c95a3618d22922071.png
[0866d1b268f240e9b4efaa61ea2e3081.png]: https://img-blog.csdnimg.cn/0866d1b268f240e9b4efaa61ea2e3081.png
[3d96ff4a21aa4e9da3be1e153d34d7a2.png]: https://img-blog.csdnimg.cn/3d96ff4a21aa4e9da3be1e153d34d7a2.png
[sql]: https://blog.csdn.net/weixin_43811057/article/details/124984631