【MySQL】015-MySQL索引

偏执的太偏执、 2024-02-20 13:09 26阅读 0赞

## 【MySQL】015-MySQL索引 ##

#### 文章目录 ####

*  【MySQL】015-MySQL索引
 *  一、MySQL索引是什么
 *   *  1、概述
     *  2、好处
     *  3、缺点
     *  4、索引的使用场景
     *  5、常用命令
     *   *  查看数据表上建的索引
 *  二、索引的分类
 *   *  1、三大类
     *  2、为什么使用B+树作为索引
     *   *  在疑问为什么使用B+树这种数据结构之前，先想想什么样的数据结构才最适合MySQL？
         *  了解了MySQL对数据结构的需求后，来看看为什么B+树符合？
         *  B+树的优点
         *  相比于其他数据结构
     *  3、聚簇索引和非聚簇索引的区别
     *   *  聚簇索引和非聚簇索引的区别
         *  主键索引
         *  ★ MySQL主键索引的作用
         *  ★ MySQL创建主键索引的常见方式
         *  二级索引
         *  ★ MySQL创建唯一索引的常见方式
         *  ★ MySQL创建普通索引的常见方式
         *  ★ MySQL创建前缀索引的常见方式
     *  4、回表/覆盖索引/索引下推
     *   *  什么是回表？
         *  二级索引查找记录一定要进行回表查询吗？
         *  什么是索引下推
     *  5、联合索引
     *   *  概述
         *  联合索引是否存在失效情况呢？
         *  联合索引的最左前缀原则
 *  三、索引创建的时机和优化
 *   *  1、创建索引的时机
     *   *  适合创建索引的场景
         *  不适合创建索引的场景
     *  2、索引优化的方式
     *   *  使用前缀索引替换普通索引
         *  使用覆盖索引优化回表次数
         *  主键索引中主键设置为自增
 *  四、索引失效问题
 *   *  1、如何查看是否命中索引
     *   *  \`type\` 连接类型
         *  \`key\` 实际使用的索引
         *  \`rows\` 扫描的行数
         *  \`filtered\` 返回的行数
         *  \`Extra\` 额外信息
     *  2、常见的索引失效问题
     *   *  \`WHERE\` 子句出现 \`OR\`
         *  索引字段使用函数或进行表达式计算
     *  3、\`like\` 通配符模糊查询
     *  4、索引字段隐式转换问题
     *  5、联合索引不匹配问题

## 一、MySQL索引是什么 ##

### 1、概述 ###

MySQL的索引是一种帮助 MySQL **高效地查询和检索数据** 的数据结构，可以看作是 **数据的目录**。（就像书籍的目录）

索引是一种**用空间换时间**的设计思想。

### 2、好处 ###

*  **快速数据检索**：索引允许数据库引擎**快速定位和检索**数据，而不必扫描整个表。这是通过使用**树状数据结构**（通常是B树或B+树）实现的，使数据库能够迅速定位所需的数据行。
 *  **减少查询时间**：索引大大减少了查询的**时间复杂度**，使数据库能够在**大型数据集**上执行快速的查找操作，因为它们不需要遍历整个表。
 *  **提高排序性能**：当对带有索引的列进行排序时，数据库引擎可以**快速获取有序数据**，而不必进行全表扫描，这提高了排序的性能。
 *  **提高连接性能**：在连接多个表时，索引可以**加快连接操作的速度**，尤其是在 JOIN 操作中。它们允许数据库引擎更有效地查找匹配的行。
 *  **唯一性约束**：通过在索引上添加唯一性约束，可以确保**某一列的值在表中是唯一的**。这对于维护数据的完整性非常有用。
 *  **加速数据修改**：尽管索引可以加快数据检索，但它们在数据插入、更新和删除时也有一定的好处。这是因为**数据库引擎可以更快地维护索引**，而不需要完全重建它们。

### 3、缺点 ###

*  **额外存储空间开销**：每个索引都需要**额外的存储空间**，通常以B树或B+树结构**存储索引数据**。这会增加数据库的**磁盘空间占用**。
 *  **降低插入、更新和删除性能**：在插入、更新或删除记录时，**索引需要维护**，这会导致额外的I/O操作和处理时间。如果表有多个索引，这个开销会更加显著。
 *  **复杂的查询优化**：当查询涉及多个表和索引时，数据库查询优化器需要选择合适的索引，这可能会导致查询优化的复杂性增加。选择不当的索引可能导致性能下降。
 *  **不适合所有列**：**并非所有列**都适合创建索引。对于某些列，如包含**大量重复值的列或枚举值的列**，创建索引可能不会提供显著的性能提升。
 *  **索引失效问题**：在某些情况下，索引**可能不会被有效利用**，导致查询性能不佳。这可能是由于数据**分布不均匀**、使用了不恰当的查询条件或连接操作等原因。
 *  **索引维护成本**：当数据库中的数据发生变化时，**索引需要不断维护**，这可能会**引入锁和竞争条件**，影响并发性能。
 *  **选择不当的索引**：选择**不合适的列**创建索引，或者创建**过多的索引**，可能导致索引失效，浪费存储空间，并且使查询变得更加复杂。
 *  **内存占用**：索引数据结构需要**占用内存**，如果内存不足，可能会导致性能下降，因为部分索引数据需要从磁盘读取。

### 4、索引的使用场景 ###

1.  **加速数据检索**：**最常见的使用场景是加速数据检索。通过在查询涉及的列**上创建索引，数据库引擎能够快速定位和检索满足查询条件的数据，而不必扫描整个表。
2.  **唯一性约束**：索引可以用来**确保某一列或列组的值在表中是唯一的**，从而维护数据的**完整性**。这是通过创建唯一性索引实现的。
3.  **加速排序操作**：当对包含索引的列进行排序时，数据库可以**快速获取有序数据**，而不必进行全表扫描，从而提高排序操作的性能。
4.  **加速连接操作**：在执行连接操作时，索引可以大幅提高性能，特别是在**多表连接**的情况下。它们使数据库引擎更有效地查找匹配的行。
5.  **加速聚合操作**：当执行聚合函数（如SUM、COUNT、AVG等）时，索引可以用于**快速定位和处理相关的数据**，从而提高聚合操作的性能。
6.  **加速检查外键约束**：**外键列通常会被索引**，以确保**引用完整性**。这有助于在执行插入或更新操作时**快速验证外键引用**。
7.  **加速子查询**：索引可以**加速**子查询的执行，使其更高效。
8.  **加速全文搜索**：对于包含文本数据的列，全文搜索索引可以用于更快速的文本搜索操作。

### 5、常用命令 ###

#### 查看数据表上建的索引 ####

SHOW INDEX FROM your_table_name;

## 二、索引的分类 ##

### 1、三大类 ###

MySQL的索引按照不同的**类型和特点**可以从三个角度进行分类：

*  按 **数据结构** 分类可分为：B+树索引、Hash索引、全文索引等
 *  按 **物理存储** 分类可分为：聚簇索引（主键索引）、非聚簇索引（二级索引）等
 *  按 **逻辑特性** 分类可分为：**主键索引、普通索引、唯一索引、前缀索引、联合索引**（多个字段）等

### 2、为什么使用B+树作为索引 ###

MySQL索引从**数据结构**的角度可以分为B+树索引、Hash索引、全文索引等。

在这里只详细说一下B+树索引，因为InnoDB、MyISAM等常见的MySQL存储引擎都支持B+树索引，并且MySQL5.5后将InnoDB设为默认存储引擎，**B+树索引也成为了MySQL存储引擎使用最多的所有类型。**

#### 在疑问为什么使用B+树这种数据结构之前，先想想什么样的数据结构才最适合MySQL？ ####

MySQL的数据是 **持久化** 的，也就是说数据是存储在**磁盘**中的（即索引和记录是保存在磁盘中的）。

那么在需要使用索引进行高效检索数据时，就必须访问磁盘取得索引读入内存，再根据索引再次访问磁盘检索数据后读入内存，所以每次检索数据都需要进行多次的 I/O操作，I/O操作耗费大量时间。

> **读索引到内存 => 根据索引读数据到内存！**

因此应该选择一种数据结构 **使得 I/O操作 次数尽可能的少** 。

此外还需要注意的是MySQL是支持范围查询的，也就意味着挑选的数据结构必须能够 **支持高效地范围查询**。

**从磁盘读取索引到内存 => 根据索引再次从磁盘读取到目标数据到内存。** （先看目录，再找目标数据）

#### 了解了MySQL对数据结构的需求后，来看看为什么B+树符合？ ####

MySQL中的B+树结构图：  
![image-20231023090054997][]

#### B+树的优点 ####

B+树是一种基于磁盘的 **平衡多路查找树**，它的高度通常很低（3~4层），这意味着访问效率很高，从千万或上亿数据里查询一条数据，**只用 3到4 次 I/O操作**。

B+树的 **非叶子节点不存储实际的数据，只存储索引**，相比于非叶子节点又存储索引又存储数据的B树而言，B+树的非叶子节点可以存放更多的索引，所以查询底层节点的磁盘I/O次数会更少。

B+树的**所有叶子节点都存储了数据，并且叶子节点之间用双向链表连接**，所以数据是有序的，范围查询通过链表可以快速实现，这样可以方便地进行范围查询和排序操作。

B+树每个节点都有大量的数据或索引（有大量冗余的节点），这些冗余的数据或索引可以保证B+树在插入和删除时效率更高（因为不会因为某个数据导致需要进行复杂的树变化，即B+树的树层结构很稳定不会经常变化）。

#### 相比于其他数据结构 ####

*  相对于 **数组** 来说，虽然使用数组＋二分查找的方式实现线性排序更为简单，但是相比于B+树，B+树具备更高效的插入或删除元素的能力。
 *  相对于 **二叉查找树或平衡二叉树** 来说，虽然平衡二叉树具备平衡树高和高效地维护元素地能力，但是相比于B+树，由于二叉树始终只能二分，随着元素地增加，树高会越来越大，B+树具备更矮的树高，即对I/O操作的次数更少，所以具备更高效地查找效率。
 *  相对于 **B树** 来说，因为B树需要使用中序遍历的方式进行范围查询，增加了I/O次数。而由于B+树的叶子节点使用双向链表所以可以更高效地进行范围查询，具备给更高的范围查询效率。

**所以B+树更适合作为MySQL索引的数据结构。**

### 3、聚簇索引和非聚簇索引的区别 ###

#### 聚簇索引和非聚簇索引的区别 ####

*  **聚簇索引**是指将 **数据存储与索引放到了一块**，叶子节点存储索引和索引对应的数据；聚簇索引的顺序就是数据的物理存储顺序，聚簇索引可以把相关数据保存在一起，提高查询效率；但也会增加插入和更新的开销，以及占用更多的空间。
 *  **非聚簇索引**是指将 **数据与索引分开存储**，索引结构的叶子节点指向了数据对应的位置；非聚簇索引的顺序与数据物理排列顺序无关

MySQL的 InnoDB 存储引擎中**每张表只能有一个聚簇索引，通常是主键索引**；而**非聚簇索引都是二级索引，可以有多个**。

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

主键索引是建立在**主键字段**上的索引，主键索引的B+树结构中的叶子节点存放的是实际数据（所有完整的记录）。

关于InnoDB引擎确定 **聚簇索引（主键索引）** 的方式按照如下次序：

1.  如果表中有主键，默认使用 **主键** 作为聚簇索引（主键索引）的索引键；
2.  如果表中没有主键，则选择 **第一个不包含NULL值的唯一列** 作为聚簇索引（主键索引）的索引键；
3.  如果2的情况也不存在，则存储引擎会 **自动生成一个隐式自增id** 列作为聚簇索引（主键索引）的索引键。

#### ★ MySQL主键索引的作用 ####

1.  **唯一性约束**：主键索引确保了表中的**每一行数据都具有唯一的主键值**。这意味着**不允许**在主键列中存在**重复的数值或空值**。这有助于维护数据的**一致性和完整性**，防止数据冗余和错误。
2.  数据**检索效率**：主键索引通常是高效的数据访问路径。当您根据主键进行数据检索时，数据库可以快速定位到目标行，而不必扫描整个表。这大大提高了查询性能，尤其是对于大型数据集。
3.  **表关联**：主键索引用于在表之间建立关系。在关系型数据库中，**外键通常引用其他表的主键**。这种关联关系便于查询多个表的数据并进行连接操作。
4.  **数据完整性**：主键索引有助于确保数据的完整性。它可以防止不良数据插入或删除，并强制执行数据的一致性规则。
5.  **索引优化**：数据库管理系统（DBMS）可以使用主键索引来进行优化，例如在执行连接操作时，它可以更有效地选择连接策略。这有助于改善查询性能。

#### ★ MySQL创建主键索引的常见方式 ####

> **一张表，只能创建一个主键索引，这个主键索引可以包含一个或多个列。**

**001 在创建表时定义主键**

使用CREATE TABLE语句时，可以在列的定义后面添加PRIMARY KEY来指定主键。例如：

CREATE TABLE students (
        student_id INT PRIMARY KEY,
        name VARCHAR(50),
        age INT
    );

**002 使用ALTER TABLE语句添加主键**

如果表已经存在，可以使用ALTER TABLE语句来添加主键。例如：

ALTER TABLE orders
    ADD PRIMARY KEY (order_id);

**003 创建表的同时定义自动递增主键**

通常，可以使用AUTO\_INCREMENT属性创建自动递增的主键。例如：

CREATE TABLE products (
        product_id INT AUTO_INCREMENT,
        product_name VARCHAR(50),
        price DECIMAL(10, 2),
        PRIMARY KEY (product_id)
    );

**004 使用CREATE TABLE时定义复合主键**

如果需要复合主键，可以在CREATE TABLE语句中同时指定多个列作为主键。例如：

CREATE TABLE orders (
        order_id INT,
        customer_id INT,
        order_date DATE,
        PRIMARY KEY (order_id, customer_id)
    );

#### 二级索引 ####

二级索引又称为**辅助索引**，二级索引的B+树结构中的叶子节点 **存放的数据是主键**，索引分类中的**唯一索引、普通索引、前缀索引**等都属于二级索引。

*  唯一索引（UNIQUE）：唯一索引的属性列**不能出现重复的数据**，但是**允许数据为NULL**，一张表可以**允许创建多个唯一索引**。
 *  普通索引（INDEX）：建立在**普通字段上的索引**，一张表可以**允许多个普通索引**，**并允许数据重复和数据为NULL**。
 *  前缀索引：前缀索引只能建立在字符串类型（`char`、`varchar`、`binary`、`varbinary`）等类型上。创建方式可以是`CREATE INDEX 索引名 ON 表名(列名(指定前缀长度));`，前缀索引的目的是为了减少索引占用的存储空间，提高查询效率。

#### ★ MySQL创建唯一索引的常见方式 ####

**001 在CREATE TABLE语句中创建唯一索引**

在创建表的同时定义唯一索引，使用`UNIQUE`关键字确保列中的值是唯一的。

CREATE TABLE users (
        id INT PRIMARY KEY,
        email VARCHAR(255) UNIQUE,
        username VARCHAR(50) UNIQUE
    );

在上面的示例中，我们在`users`表中创建了两个唯一索引，分别用于`email`和`username`列，以确保这两列中的值不重复。

**建表语句末写法：**

CREATE TABLE `sys_dict` (
      `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '唯一ID',
      `dict_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '字典名称',
      `dict_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '字典编码',
      `status` int DEFAULT '0' COMMENT '状态(0正常 1停用)',
      `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
      `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建用户',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新用户',
      `update_time` datetime DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `dict_code_index` (`dict_code`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='字典表';
    
    -- 方法一：等效于方法二
    UNIQUE INDEX dict_code_index (dict_code) USING BTREE
    -- 方法二：等效于方法一
    UNIQUE KEY `dict_code_index` (`dict_code`) USING BTREE

**002 使用ALTER TABLE语句添加唯一索引**

如果表已经存在，可以使用`ALTER TABLE`语句添加唯一索引。例如，如果要为已存在的表`mytable`添加唯一索引：

ALTER TABLE mytable
    ADD UNIQUE (username);

**003 使用CREATE INDEX语句创建唯一索引**

使用`CREATE INDEX`语句，您可以为表的一个或多个列创建唯一索引。例如：

CREATE UNIQUE INDEX idx_username ON mytable (username);

**004 通过PRIMARY KEY创建唯一索引（主键索引）**

在定义表结构时，通常使用`PRIMARY KEY`来创建主键索引，它也是唯一的。例如：

CREATE TABLE mytable (
        id INT PRIMARY KEY,
        username VARCHAR(50)
    );

这将为 `id` 列创建一个唯一索引，并将其定义为主键。

**005 多列组合建立唯一索引（联合索引）**

这个唯一索引要求三个列（`column1`、`column2` 和 `column3`）的**组合是唯一的**，而不是每个列都唯一。

UNIQUE INDEX unique_index_name (column1, column2, column3) USING BTREE

#### ★ MySQL创建普通索引的常见方式 ####

**001 CREATE INDEX语句**

使用`CREATE INDEX`语句可以为表中的一个或多个列创建索引。例如，要为名为`example_table`的表的`column_name`列创建索引，可以使用以下语句：

CREATE INDEX index_name ON example_table (column_name);

这将创建一个名为`index_name`的索引，用于提高`column_name`列的检索性能。

**002 ALTER TABLE语句**

使用`ALTER TABLE`语句也可以添加索引。例如，要为已经存在的表`example_table`的`column_name`列添加索引，可以使用以下语句：

ALTER TABLE example_table ADD INDEX index_name (column_name);

这将在`example_table`表上添加一个名为`index_name`的索引。

**003 建表语句中写法**

CREATE TABLE `sys_dict_item` (
      `id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '唯一ID',
      `dict_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '字典ID',
      `dict_code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '字典编码',
      `dict_label` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '字典标签',
      `dict_value` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '字典键值',
      `value_type` tinyint(1) DEFAULT '0' COMMENT '值类型(0字符 1数字 2布尔)',
      `dict_sort` int DEFAULT '0' COMMENT '字典排序',
      `color` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '配色(用于前端显示)',
      `status` int DEFAULT '0' COMMENT '状态(0正常 1停用)',
      `remark` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
      `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建用户',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新用户',
      `update_time` datetime DEFAULT NULL COMMENT '更新时间',
      PRIMARY KEY (`id`) USING BTREE,
      KEY `dict_code_index` (`dict_code`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='字典项';
    
    -- 方法一：等效于方法二
    INDEX dict_code_index (dict_code) USING BTREE
    -- 方法二：等效于方法一
    KEY `dict_code_index` (`dict_code`) USING BTREE

**004 多列组合建立普通索引（联合索引）**

INDEX index_name (column1, column2, column3) USING BTREE

#### ★ MySQL创建前缀索引的常见方式 ####

**001 使用CREATE INDEX语句**

你可以使用CREATE INDEX语句来创建前缀索引。例如，如果你有一个名为`text_column`的文本列，你可以创建一个前缀索引，仅索引文本列的前几个字符，如下所示：

CREATE INDEX prefix_index ON your_table (text_column(10)); -- 这将创建一个索引，仅索引前10个字符

**002 使用ALTER TABLE语句**

你也可以使用ALTER TABLE语句来添加前缀索引到已存在的表中。例如，如果你有一个名为`your_table`的表，你可以执行以下操作来添加前缀索引：

ALTER TABLE your_table ADD INDEX prefix_index (text_column(10)); -- 这将添加一个索引，仅索引前10个字符

**003 建表语句中写法**

CREATE TABLE your_table (
        id INT AUTO_INCREMENT PRIMARY KEY,
        text_column VARCHAR(255), -- 假设你有一个文本列
        -- 创建一个前缀索引，仅索引text_column的前10个字符
        INDEX prefix_index (text_column(10)) USING BTREE
    );
    
    
    -- 方法一：等效于方法二
    INDEX prefix_index (text_column(10)) USING BTREE
    -- 方法二：等效于方法一
    KEY `prefix_index` (`text_column(10)`) USING BTREE

### 4、回表/覆盖索引/索引下推 ###

#### 什么是回表？ ####

正如上面所说的二级索引的 B+树 结构中的叶子节点存放的数据是主键，那么使用二级索引查找记录的过程是怎么样的呢？

二级索引查找记录的过程为：先获得二级索引中的B+树的索引值，检索 **二级索引的B+树** 找到对应的叶子节点后获取到对应的主键值，再通过主键值检索 **主键索引的B+树** 找到对应的叶子节点即可获得对应的数据（记录）。**第一次检索获取主键值，再通过主键值再次检索获得记录的这个过程叫做 回表。**

举个例子：

select * from products where name = 'iPhone 14';

![image-20231023092036800][]

如上面的代码，id为**主键索引列**，name为**唯一索引列**（二级索引）。执行这条语句时，会先拿`'iPhone 14'`作为索引值检索**二级索引**的B+树找到对应的叶子节点，这里的叶子节点存储的是要找的这条记录的**主键值**`1`，然后再拿`1`作为索引值检索**主键索引**的B+树找到对应的叶子节点，此时这里的叶子节点存储的就是**整条记录**了。

#### 二级索引查找记录一定要进行回表查询吗？ ####

答案是并不一定。

修改上面的例子：

select id from products where name = 'iPhone 14';

当 **查询的数据能在二级索引的B+树的叶子节点上直接查询到** 时，就不再需要进行回表查询，直接返回数据即可。正如这里例子中，因为需要查询的是 `id`，二级索引的B+树的叶子节点上可以直接获取，这时就不需要回表操作了。

这种在二级索引的B+树的叶子节点能直接查询到数据的过程就叫做 **覆盖索引**。

> 扩展：如何确保或者如何实现：二级索引的叶子节点包含了 id 列的值？
> 
> 要确保或实现二级索引的叶子节点包含了 `id` 列的值，你可以按照以下步骤操作：
> 
> 1、**创建合适的二级索引**：首先，你需要创建一个二级索引，确保该索引包含了你希望包含的列。在这种情况下，你希望 `id` 列的值包含在二级索引中。创建索引的SQL语句如下：
> 
>     CREATE INDEX idx_name_id ON products (name, id);
> 
> 这将创建一个名为 `idx_name_id` 的二级索引，其中包含 `name` 列和 `id` 列的值。
> 
> 2、**查询优化**：确保查询语句中包含了需要的列。在你的查询中，你需要选择 `id` 列，以便数据库引擎知道你想要从索引中检索这一列的值。示例查询如下：
> 
>     SELECT id FROM products WHERE name = 'iPhone 14';
> 
> 这个查询将从包含 `name` 列和 `id` 列的二级索引 `idx_name_id` 中检索 `id` 列的值。
> 
> 3、**维护索引**：确保在插入、更新和删除操作后，二级索引保持一致。MySQL通常会自动处理索引的维护，但在大批量插入或更新数据时，可能需要手动重建索引以确保其正确性。

#### 什么是索引下推 ####

索引下推是在MySQL5.6之后引进的索引优化功能，**可以让存储引擎在非聚簇索引检索数据时，对索引中包含的字段先做判断是否符合条件，过滤掉不符合条件的记录，在返回给MySQL数据库（Server层）**，减少回表次数，减少不必要的数据传输，提高查询效率和性能。

索引下推我的理解就是把 **原本应该在MySQL数据库（Server层）进行判断是否符合条件的工作下推到了存储引擎层中进行**，这样的好处就是存储引擎返回给Server层的数据少了，减少了不必要的数据传输同时也就减少了回表的次数。

这里需要注意的是 **存储引擎层** 只是判断 **索引中包含的字段** 是否符合条件，对于那些 **没有索引的字段但又需要判断的** 会返回到 **Server层** 之后再进行判断。

### 5、联合索引 ###

#### 概述 ####

联合索引：由 **多个字段** 组合成一个索引。

语法：`CREATE INDEX 索引名 ON 表名(列名1, 列名2, ...);`

当前数据有：

![image-20231102154239344][]

通过 `CREATE INDEX index_stockNo_stock ON products(stockNo, stock);` 创建 `stockNo` 和 `stock` 的**联合索引**。

此时联合索引的B+树是这样的：

![image-20231102154314441][]

可以看到 B+树 是先按照 `stockNo` 字段进行排序，再 `stockNo` 字段相同的情况况下再按照 `stock` 字段进行排序。

所以 `stockNo` 字段是 **全局有序** 的，而 `stock` 字段是 **全局无序，但局部有序** 的。也就是说在创建联合索引时，创建顺序为`(a, b, c)`，那么对应的就是 `a` 全局有序，`b`、`c` 局部相对有序。

#### 联合索引是否存在失效情况呢？ ####

![image-20231102154425559][]

#### 联合索引的最左前缀原则 ####

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

通俗一点讲：好比现在创建了一个联合索引 `(a, b, c)`，相当于建立了三个索引 `(a)`、`(a, b)`、`(a, b, c)`， 也就是说当需要检索的是 `where a = XXX;` 或者是 `where a = XXX and b = XXX;` 或者是 `where a = XXX and b = XXX and c = XXX`都是可以命中索引，也就是利用索引进行检索数据的。

但是如果需要检索的是 `where b = XXX and c = XXX` 那就无法命中索引了也就是无法利用索引检索数据。也就是说必须从**最左边**开始匹配，只要匹配不到了就无法使用索引（注意这里 `where a = XXX and c = XXX`是可以匹配到的，具体在上面索引失效中介绍了）。

## 三、索引创建的时机和优化 ##

### 1、创建索引的时机 ###

#### 适合创建索引的场景 ####

1.  **不为NULL的字段**：对于数据为NULL的字段，数据库较难优化，字段被频繁查询时，避免不了为NULL。
2.  **频繁用于`where`子句查询的字段**：经常用于条件查询的字段可以建立索引，这样能提高查询的速度。
3.  **经常排序的字段**：经常被`oreder by` 或者 `group by` 使用的字段可以建立索引，这样在查询时就无需再做一次排序。
4.  **被经常用于连接的字段**：经常用于连接的字段涉及到表与表的关系，对于频繁被连接查询的字段，可以建立索引，提高多表连接查询的效率。
5.  **尽可能地创建联合索引**：相对于创建的单一索引来说，如果适合创建联合索引应当选择建立联合索引，因为联合索引多个字段在一个索引上，可以很大程度地减少磁盘占用的空间，提高维护索引效率。
6.  **尽可能地创建前缀索引**：对于字符串类型的字段，创建前缀索引相对于创建普通索引来说，能节约磁盘占用空间。

#### 不适合创建索引的场景 ####

*  **频繁需要更新的字段**：对于需要经常更新的字段不适合建立索引，因为字段频繁被修改，而B+树需要维护索引键的有序性，所以频繁更新的字段会增加维护索引的成本，很影响数据库的性能。
 *  **表中数据量过少**：表数据太少时无需创建索引，因为全表扫描也不会很慢，无需以空间换时间。
 *  **冗余索引**：比如已经有联合索引`(a, b, c)`，则自然无需为字段 `a` 单独建立索引，此时单独建立字段 `a` 索引就是冗余索引。
 *  **较低区分度（重复数据过多）的字段**：较低区分度的字段很容易导致表中出现过多的重复数据，此时建立索引已经无法起到优化作用了，因为**查询优化器在对比成本后会直接跳过索引选择全表扫描**（索引失效）。
 *  **需要参与计算的字段**：索引列不能参与计算，因为B+树中存储的都是原始的字段值，如果需要参与计算的话，可能会导致索引失效（具体后面介绍）。虽然可以把 `a + 1 = 5` 这样的表达式变为 `a = 5 - 1`使得索引不失效，但针对一些较为复杂的表达式，无疑会增加查询成本，所以不建议在需要计算的字段上建立索引。

### 2、索引优化的方式 ###

#### 使用前缀索引替换普通索引 ####

对于**字符串类型**的字段建立索引时，可以使用前缀索引替换普通索引，这样**可以减少索引字段的大小，从而增大数据也存储索引的数量**，可以提高索引的查询效率。

#### 使用覆盖索引优化回表次数 ####

对于查询记录不要求全部的，可以考虑**建立联合索引**，这样使得**二级索引的B+树中的叶子节点能找到所有需要查询的数据**，这样就**不再需要通过主键索引查询整条记录，也就是避免了回表操作**，减少I/O次数，从而提高查询效率。

#### 主键索引中主键设置为自增 ####

对于主键索引的主键最好可以设置为自增。

对于**使用自增的主键值**，在索引的B+树插入新数据时，都是**顺序的追加操作**，无需移动节点调整树结构，这样的**插入效率会变得更高**。

而对于**使用非自增的主键值**，在B+树插入新数据时，**可能需要移动其他节点来满足新节点的插入，也可能出现页分裂**（当前数据页由于新数据的插入需要将数据页的数据隔开，所以需要新建一张数据页并且把当前数据页的一部分数据复制进新数据页），页分裂可能造成大量的内存碎片从而导致索引结构不稳定，**影响查询效率**。

## 四、索引失效问题 ##

> **建立了索引，未必每次查询都能用到索引，也可能全表扫描！**
> 
> 建立了索引，是否意味着 **所有查询都可以使用索引进行检索数据** 呢？
> 
> 当然不是，如果**无法命中索引（索引失效）时，将会进行全表扫描**，此时建立的索引就派不上用场了。
> 
> 出现索引失效这种情况将会导致性能大大降低，所以需要了解 **如何查看索引是否命中** 以及一些 **常见的索引失效问题**，避免出现索引失效这样才能有效地利用索引提高查询效率。

### 1、如何查看是否命中索引 ###

MySQL中通过 `explain` 查看SQL的执行计划，通过执行计划可以查看是否命中索引。

> 语法：EXPLAIN + sql 语句

如下图这样：

![image-20231102171856382][]

执行计划中只需要重点关注 `type` 、`key` 、`rows` 、`filtered` 、`Extra` 即可。

#### `type` 连接类型 ####

> 可以看成是扫描方式

`type`如下表，扫描方式的 **速度（性能）从慢到快**。

![image-20231102171953644][]

#### `key` 实际使用的索引 ####

表示实际使用到的索引（显示的是索引名，也就是在创建索引时给索引命名的名字）。

#### `rows` 扫描的行数 ####

表示MySQL估算找到所需的记录需要扫描的行数，注意这个值只是**估算值**，并不是一定准确。

#### `filtered` 返回的行数 ####

表示符合条件的记录数的百分比（注意这里是百分比值），百分比为 **经过过滤后满足条件的记录数 / 存储引擎返回的记录数** 。

#### `Extra` 额外信息 ####

![image-20231102172128079][]

### 2、常见的索引失效问题 ###

#### `WHERE` 子句出现 `OR` ####

如果在查询语句的 `WHERE` 子句中出现 `OR` 并且 `OR` 的左右两边的字段中有一个字段不是索引列时，会出现索引失效，进行全表扫描。

如 `select * from products where name = 'Apple Watch' or price = 999.99;`语句中 `name`字段为唯一索引列，而 `price` 只是普通字段时，执行计划如下：

![image-20231102172340985][]

可以看到执行计划中的 `type` 为 ALL，说明进行了全表扫描，也就说明了对于 `name` 字段的唯一索引并没有使用，即 **索引失效**。

如何避免这种情况下的索引失效呢？

避免这种情况的方法是 **`OR` 的左右两边的字段都需要是索引列**。

如 `select * from products where name = 'Apple Watch' or id = 1;` 就使用了主键索引和唯一索引，这里的执行计划 `type` 为 index\_merge，表示利用两个索引分别检索后合并得到结果。

![image-20231102172438784][]

#### 索引字段使用函数或进行表达式计算 ####

如果在查询语句中 **对索引列使用函数** 或者 **对索引列进行表达式计算**，都会导致索引失效。

如 `select * from products where LENGTH(name) = 5;` 语句中看似使用了 `name` 的唯一索引，但是实际上并没有命中索引，而是进行了全表扫描。

![image-20231102172529570][]

**为什么会出现这种情况呢？**

因为 **索引的B+树结构中索引键是原始的索引值**（没有经过计算或函数的），如果经过函数或者表达式计算之后自然就无法在B+树结构找到对应的索引键了，那么就自然无法通过索引来检索到记录了。

**如何避免这种情况呢？**

其实只需要对代码做一些转换，像 `id + 2 = 5` 转换为 `id = 5 - 2`，`from_unixtime(create_time) = '2023-3-22'`转换为 `create_time = unix_timestamp('2023-3-22')` 这样即可实现索引命中，也就是让**索引列比较时变得“干净些”**。

如 `select * from products where id = 5 - 2;` 即可避免索引失效问题。

![image-20231102172611844][]

但是还是比较建议如果 **需要进行计算的字段就不必再建立索引** 了，因为当面对一些较为复杂的函数时无法做到的两边转换。

### 3、`like` 通配符模糊查询 ###

如果在查询语句中使用 `like` 模糊匹配时，对于 `like %XXX` 和 `like %XXX%` 这样的形式模糊匹配时是无法命中索引（索引失效）的。而对于 `like%` 这样的形式是可以命中索引进行索引检索的。

如 `select * from products where name like '%Pro';`语句中执行计划的 `type` 为 ALL，表明这次执行是全表扫描。

![image-20231102172738137][]

如 `select * from products where name like 'Apple%';`语句中执行计划的 `type` 为 range，表明这次执行是索引范围查询。

![image-20231102172750785][]

**为什么会出现这样的情况呢？**

由于索引的B+树的索引键是有序的，根据指定的值和索引键比较，最后找到所需的记录，在模糊查询时会根据指定的值 **按照前缀的方式** 比较索引键来找到所需的记录。

对于像 `Apple%` 这样的值因为前面有明确的 `A`，所以完全可以使用索引快速缩小范围，进行索引范围查询。

而对于像 `%Pro` 或者 `%Pro%` 这类的值因为前面不具有明确的值，可以是任意值，这样索引就不知道从哪里开始检索，无法快速缩小范围，只能选择全表扫描，也就会导致索引失效。

### 4、索引字段隐式转换问题 ###

如果在查询语句时，对**字符串类型**的索引列的输入参数类型为**整型**时，会导致索引失效。

如 `select * from products where stockNo = 1001;` 中将为 `varchar` 类型的 `stockNo`比较时输入参数为整型 `1001` 时，虽然可以找出记录，但是使用的却是全表扫描，说明索引失效。

![image-20231102172956269][]

**出现这种情况的原因**

MySQL的隐式转换：**当遇到字符串和数字进行比较时，会自动将字符串转为数字进行比较**。

知道这个原则后，就比较好解释了，上面的例子中在 字符串类型的 `stockNo` 和数字类型的 `1001`比较时，会自动把 `stockNo`转换成数字，但是因为 **隐式转换仍然是需要使用函数进行转换的**，也就是说需要对 `stockNo` 索引列使用函数，所以自然无法使用索引检索了，这就导致索引失效。

也就是说如果例子为 `select * from products where id = '2';` 时，因为 `id` 是主键索引并且是整型，此时输入参数是字符串类型，但是通过隐式转换后会变为 `id = 2`，所以并不影响主键索引的使用，也就不会导致索引失效。

![image-20231102173019603][]

### 5、联合索引不匹配问题 ###

如果在查询数据时使用联合索引进行检索数据时，需要注意按照最左优先的方式进行索引的匹配（遵循最左前缀原则）。

现在创建一个联合索引 `(a, b, c)` ，此时的索引结构是以 `a` 为全局有序， `b`、`c` 为局部相对有序。

如果此时的查询为：`where b = 2 and c = 3` 或者 `where b = 2` 或者 `where c = 3` 会出现索引失效的问题

![image-20231102173047644][]

之所以出现这样的情况是因为查询中并没有遵守最左前缀原则，无法匹配到 `a` 字段，所以无法命中联合索引，导致索引失效问题。

**对于 `where a = 1 and c = 3` 这种情况会出现索引失效吗？**

答案是不会。

在没有出现 **索引下推** 前，这条语句的执行过程是 先去匹配 `a` 字段的索引键找到所有符合条件的主键后，通过回表操作将这些主键值经过主键索引找到对应的数据行，并且把这些数据都返回给Server层，然后再通过Server层的 `where` 比较 `c` 的值，过滤掉不符合的条件。

在出现 **索引下推** 后，这条语句的执行过程是 先去匹配 `a` 字段的索引键找到所有符合条件的主键时，因为 `c`字段也在索引中，所以可以在存储引擎层就对 `c` 字段进行比较，过滤掉不符合 `c`字段的值，然后再将剩余符合条件的主键进行回表操作，通过主键索引找到对应的数据返回给Server层。

通过这个例子也可以巩固之前文章中 **索引下推** 的定义，索引下推减少了回表的数量，提高了查询效率。

![image-20231102173157937][]

**为什么不遵守最左前缀原则就无法命中索引呢？**

因为在联合索引中，数据是按照索引第一列排序，在第一列相同的情况下才按照第二列进行排序，也就是**最左边的列是全局有序的，而其余的列都是局部相对有序的**，查询时如果连最左的列都无法匹配到，那么自然无法使用索引。

想要更多的列的使用到 **联合索引**，必须保证从最左边开始能连续匹配到对应列，像 `where a = XXX and c = XXX`这种使用索引只能缩小范围到 `a` 字段，无法快速检索 `c` 字段，只能通过遍历进行 `c` 字段的比较。

[image-20231023090054997]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/f8950205f79643d0831b95ec93cdaebf.png
[image-20231023092036800]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/066ac70124a54f0da84436faab6cef4f.png
[image-20231102154239344]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/17ec9493848a417b875f98371bafffff.png
[image-20231102154314441]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/125a50efa47b45fbbd51325ae22dd000.png
[image-20231102154425559]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/10ff7151f68540b59e8bac53cff8c37a.png
[image-20231102171856382]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/b96e53f893ab42db992b7ddbd8338f55.png
[image-20231102171953644]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/a6e3812ce277468fb82900670de116f0.png
[image-20231102172128079]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/234e109e4f3f4606a8f028bc93ea4c34.png
[image-20231102172340985]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/46966d8f6bf745f3b6c0272c5c199f17.png
[image-20231102172438784]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/f5efec6cb2cb4cdf89fb540f028f7abe.png
[image-20231102172529570]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/9248948a064b40ac90ea3e043a6ee416.png
[image-20231102172611844]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/5cd11eba90914fffbe50ac60986a028f.png
[image-20231102172738137]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/aa153101137a4cc399f1bb625dbac652.png
[image-20231102172750785]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/a82ba07ab35848f1b7ed13d40d942237.png
[image-20231102172956269]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/d428b6e01e8646248ee164580276494d.png
[image-20231102173019603]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/36ba15f6612d4842aa4c619522efaf25.png
[image-20231102173047644]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/9808c87912f6412792a9ba38b5f718f7.png
[image-20231102173157937]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/e6ba411613854b75922a30c5210c8315.png