一文搞懂 MySQL 索引（转载）

你的名字 2024-03-31 16:26 76阅读 0赞

#### 1、MySQL 索引 简介 ####

1.1、MySQL 索引 是什么？

索引是一个单独的、存储在 磁盘 上的 数据库结构 ，包含着对数据表里 所有记录的 引用指针。

1.2、 MySQL 索引 的存储类型有哪些？

MySQL中索引的存储类型有两种，即 BTree 和 Hash。

1.3、MySQL 索引 在哪里实现的？

索引是在存储引擎中实现的。（MySQL 的存储引擎有：InnoDB、MyISAM、Memory、Heap）

*  InnoDB / MyISAM 只支持 BTree 索引
 *  Memory / Heap 都支持 BTree 和 Hash 索引

1.4、存储引擎 是什么？

存储引擎就是指 表的类型 以及 表在计算机上的存储方式。

1.5、索引 的优缺点有哪些？

优点：

*  提高数据的查询的效率（类似于书的目录）
 *  可以保证数据库表中每一行数据的唯一性（唯一索引）
 *  减少分组和排序的时间（使用分组和排序子句进行数据查询）
    
     *  被索引的列会自动进行分组和排序

缺点：

*  占用磁盘空间
 *  降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件）

#### 2、MYSQL 索引 的分类 ####

1、普通索引 和 唯一索引

*  普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值
 *  唯一索引：要求索引列的值必须 唯一，但允许 有空值
    
     *  如果是组合索引，则列值的组合必须 唯一
     *  主键索引是一种特殊的唯一索引，不允许 有空值

2、单列索引 和 组合索引

*  单列索引：一个索引只包含单个列，一个表可以有多个单列索引
 *  组合索引：在表的 多个字段 组合上 创建的 索引
    
     *  只有在查询条件中使用了这些字段的 左边字段 时，索引才会被使用（最左前缀原则）

3、全文索引

*  全文索引 的类型为 fulltext
 *  在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值
 *  全文索引 可以在 char、varchar 和 text 类型的 列 上创建

4、空间索引

*  空间索引 是对 空间数据类型 的字段 建立的索引
 *  MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon
 *  MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引
 *  创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。

5、前缀索引

*  在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度

#### 3、MySQL 索引 的[数据结构][Link 1] ####

MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。

**Hash**：使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。

Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。

**BTree**：属于多叉树，又名多路平衡查找树。

性质：

*  BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址）
 *  BTree 节点的键值按 非降序 排列
 *  BTree 所有叶子节点都位于同一层（具有相同的深度）

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

查询过程，例如：Select \* from table where id = 6;

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

BTree 的不足：

*  不支持范围查询的快速查找（每次查询都得从根节点重新进行遍历）
 *  节点都存储数据会导致磁盘数据存储比较分散，查询效率有所降低

**B+Tree**：在 BTree 的基本上，对 BTree 进行了优化：只有叶子节点才会存储 键值 - 数据，非叶子节点只存储 键值 和 子节点 的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表。

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

等值查询，例如：Select \* from table where id = 8;

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

范围查询，例如：Select \* from table where id between 8 and 22;

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

B+Tree 的优点：

*  保证了等值查询和范围查询的快速查找
 *  单一节点存储更多的元素，减少了查询的 IO 次数

#### 4、MySQL 索引 的实现 ####

##### 4.1、MyISAM 索引 #####

*  MyISAM 的 数据文件（.myd） 和 索引文件（.myi） 是分开存储的
 *  MyISAM（B+Tree）叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
 *  MyISAM 的 主键索引（Primary key）和 辅助索引（Secondary key）在结构上没有任何区别，只是 主键索引 要求 键值唯一，而 辅助索引 键值 可以重复

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

##### 4.2、InnoDB 索引 #####

*  数据和索引都存储在一个文件中（.ibd）
 *  一般情况下，聚簇索引等同于主键索引；除 聚簇索引 外的所有索引 均称为 辅助索引
 *  InnoDB（B+Tree）叶子节点中存储的键值为索引列的值
    
     *  如果是聚簇索引，数据为整行记录（除了主键值）
     *  如果是辅助索引，数据为该行的主键值
 *  每一张表都有一个聚簇索引
    
     *  如果表中有定义主键，主键索引用作聚簇索引
     *  如果表中没有定义主键，选择第一个不为 NULL 的唯一索引列用作聚簇索引
     *  如果以上都没有，使用一个 6 字节长整形的隐式字段 ROWID （自增）用作聚簇索引
 *  根据在 辅助索引树 中获取的 主键id，再到 主键索引树 查询数据的过程 称为 回表 查询
 *  组合索引
    
     *  遵循 最左匹配（最左前缀）原则：
        
         *  使用 组合索引 查询时，MySQL 会一直向右匹配直至遇到范围查询（>、<、between、like）就停止匹配。
     *  只有第一列是有序的，其它列都是无序的（最左匹配原则的原因）

**主键索引（聚簇索引）：**  
![在这里插入图片描述][25a446c4943c4c31babaabd3de246df3.png]

**辅助索引：**

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

**组合索引：**

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

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

**覆盖索引：**

*  覆盖索引不是一种索引结构，而是一种优化手段
 *  我们只需要查询 组合索引 中的字段，而不需要表中的其它字段，在这过程中不会产生回表现象，这种情况称为 覆盖索引

create index idx on user(name, age, gender);

-- 使用覆盖索引
    explain select name, age, gender from user where name ='万叶' and age = 18 and gender = '0';

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

-- 未使用覆盖索引
    explain select * from user where name ='万叶' and age = 18 and gender = '0';

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

#### 5、MySQL 索引 的使用 ####

5.1、MySQL 索引 的基本语法

*  定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引

创建表时：

create table table_name(
    	[col_name data_type] [unique | fulltext | spatial...],
        [unique...] [index | key] [index_name] (col_name [length], ...)
    );

create table user (  
id INT NOT NULL,  
name CHAR(30) NOT NULL,  
unique index uniqueIdx(id)  
);

表已存在时：

-- 第一种 
    alter table table_name 
    	add [unique...] [index | key] [index_name] (col_name [length], ...);

alter table user add unique index uniqueIdx(id);

– 第二种   
create \[unique...\] index index\_name  
on table\_name (col\_name \[length\], ...);

create unique index uniqueIdx on user(id);

– 删除索引  
drop index index\_name on table\_name;

5.2、怎么判断要不要加索引？

加索引：

*  数据本身具有某种的性质，如：唯一性、非空性…
 *  频繁进行 分组或排序 的列；如果待排序的列有多个，可以建立 组合索引

不加索引：

*  经常更新的列
 *  列 的值类型 很少，如 性别
 *  where 条件中用不到的列
 *  参与计算的列
 *  数据量小的表

5.3、只要创建了索引，就一定会生效吗？

不一定。当使用 组合索引 时，如果没有遵循 最左匹配 原则，索引不生效。

例如，创建 id、name、age 组合索引

*  id、（id、name）、（id、name、age）查询，索引生效
 *  age、（age、name）查询，索引不生效

5.4、怎样判断索引是否生效？

使用 explain 关键字。

*  possible\_keys：MySQL 在搜索数据记录时可选用的各个索引
 *  key：MySQL 实际选用的索引

例如：

explain select * from user where id = 1;
    
     
     
      
     
     
      
       1

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

5.5、怎么避免索引失效?

*  使用组合索引时，遵循 最左匹配 原则
 *  不在索引列上进行任何操作，如：计算、函数、类型转换
 *  尽量使用覆盖索引
 *  索引列 尽量不使用 不等于（!= / <>）条件、通配符开头的模糊查询（like %abc）、or 作为连接条件
 *  字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）

[Link 1]: https://so.csdn.net/so/search?q=%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84&spm=1001.2101.3001.7020
[0796add7d453408cb21a17ead434f9dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/566406dbf34f41798d12dd33a99fb757.png
[b2170bdbd1ee4809810615ba612cbc2b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/45c97275c7d24c728b500ab917a3bfc3.png
[c9d0dd40c90d44aa93e47278b6085fbe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/603705331cee41f38824c7c4a29e941c.png
[37cd749b60ed4817a549ac757b64b342.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/eba5c09d989845e490c4fff0e6875b4f.png
[7259d91871034086bc6cc5d856c3d502.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a9ca4cced88443a387fffea5560b6802.png
[fbef149bb9294f3f9db359d43e59b740.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/88c297ff75ba47b78f3b3c298f4ad683.png
[25a446c4943c4c31babaabd3de246df3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e917b9fcc19844d787da5c452a30d2d5.png
[f496152a100a463f9b936078abe7b4fc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e4620c2ec189419c840784f274c13ad5.png
[e1c4a5ba50ee4120a8ee85e321f31fc2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4ebef04468a44dabafaa92cf381d753e.png
[1b5ed4153dea47baa8d1071515563633.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f7ab8b97a3c245a2a708ee095f41f0fd.png
[c4a1de6a7e584acba67b3b7a9588255d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/939c4b529be84feba25e028729cd8984.png
[91b254bd237b43c4b6fce8726cfbea90.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/3cbf5d6b3103465fa144e6f83cf07680.png
[5bbffa134a0946c09be4948ec9443b29.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/08f9e112bda4491e8ae008a9491196a1.png