当前位置：首页 > news >正文

【MySQL】索引

news 2025/5/25 11:39:08

一、概念

二、如何与磁盘进行交互

三、索引的理解

（一）page 的数据结构

（二）page 如何进行组织

（三）B+树的特征

（四）聚簇索引和非聚簇索引

四、索引的操作

（一）创建索引

1、创建主键索引

2、创建唯一索引

3、创建普通索引

4、创建复合索引

5、创建全文索引

（二）查询索引

（三）删除索引

1、删除主键索引：

2、删除其他索引

3、直接删除索引

（四）总结

一、概念

索引：提高数据库的查找性能。当一个表中存在大量数据且进行查找时是进行全表扫描查询，非常耗时。索引就是用于加速数据库表中的检索速度，但可能会降低插入、更新以及删除的速度。

常见的索引分为：主键索引、唯一索引、普通索引以及全文索引。

二、如何与磁盘进行交互

磁盘相关概念：【Linux】缓冲区/磁盘inode/动静态库_linux 磁盘的一些静态信息-CSDN博客

磁盘的任何一个基本的数据块（扇区）为512字节（一般情况），而MySQL作为一块应用软件，必然存在着大量IO的场景。因此为了提高基本的IO效率，MySQL进行IO的基本单位是16KB（InnoDB引擎）。

mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

也就是磁盘的基本单位为512字节，而MySQL InnoDB引起使用16KB为基本单位大小进行IO交互，该基本数据单位称为 page。

MySQL中的数据文件都是以 page 为单位保存在磁盘中的；
MySQL中的增删查改都是需要通过计算后找到插入位置或对应的数据记录进行操作，因此需要先将数据读入到内存中，也就是在特定的时间内该数据即存在于内存中，也存在在磁盘中。当数据操作结束后会以特定的刷新策略再刷新到磁盘中，此时IO的基本单位就是 page；
为了支持以上操作，MySQL服务器再内存中运行时就会先申请被称为 Buffer Pool 的大内存空间来进行各种缓存操作，提高与磁盘数据交互的效率；
为了更高的效率，一定要减少MySQL与磁盘IO的次数。

有以上理解，无论MySQL读取任意大的数据，都是按照 page 大小的方案进行交互。例如当查找同一表中5条记录，若采用按需查找则需要进行五次IO。但如果按照 page 大小进行数据IO，按照局部性原理很有可能只需要进行一次IO即可满足需求，大大减少了与磁盘IO的次数。

三、索引的理解

mysql> create table if notexists user(-> id int primary key,    //默认生成主键索引-> age int not null,-> name varchar(20) not null-> );
//无序插入
mysql> insert into user(id, age, name) values (3, 18, '杨过');
Query OK, 1 row affected (0.00 sec)mysql> insert into user(id, age, name) values (4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)mysql> insert into user(id, age, name) values (2, 26, '黄蓉');
Query OK, 1 row affected (0.00 sec)mysql> insert into user(id, age, name) values (5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)mysql> insert into user(id, age, name) values (1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)
//按序显示
mysql> select * from user;
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  1 |  56 | 欧阳锋    |
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+
5 rows in set (0.00 sec)

（一）page 的数据结构

对于一个 page 而言，如果内部只存储数据时，那么检索数据时只能扫描整个 page 进行检索，如此会大大降低数据搜索的效率。因此 page 内不单单只存储数据记录，还要存储目录。

因此这也解释了为什么乱序插入数据后会按照有序进行显示，因为只有有序以后才能对应的生成目录结构。

（二）page 如何进行组织

既然 MySQL 是按照 page 大小进行IO操作的，那么必然需要对该单位进行组织管理，那么当存在大量的 page 时，MySQL 如何进行管理呢？

实际上多个 page 之间以链表的形式进行组织，那么问题来了，如果大量的 page 全部以链表的形式进行组织，虽然 page 内部存在目录结构能够加速检索，但是在多个 page 块定位到目标块还是以链表顺序查询的方法进行检索，如此会大大降低检索速度。

因此可以再采用目录的思想对多个 page 进行组织管理：

使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值；
和页内目录不同的是该目录管理的级别为页，而页内目录管理的级别是行；
每个目录项的构成是：键值+指针。图中没有画全。

由此采用目录页对多个页进行管理，目录页中的数据存放的是指向被管理页中最小的数据，通过对目录页中的数据进行比较找到目标页后再通过目标页中的目录结构快速检索到目标数据。

其实目录页的本质也是页，普通页中存储的数据是用户数据，而目录页中村的数据是普通也的地址。

当目录页数量过多时，可以再添加目录页对目录页进行管理：

由上图可以看出，该管理组织结构实际就为B+树。

（三）B+树的特征

1、非叶节点不保存数据，只用来索引，所有数据都保存在叶子节点。

2、数据只在叶子结点保存，并保存指向前后叶子结点的指针，通过链表指针对叶子结点进行级联，且叶子结点本身依关键字的自小而大顺序连接。

为什么采用B+树还不是别的结构呢？

首先线性数据结构一般都按照顺序查询效率太低，而普通二叉树结构可能会存在退化为链表的情况。红黑树和AVL的查询效率也很不错，但B+树由于结构原因在相同数据量下的高度会比红黑树或AVL树更低，也就是查询的效率会更高点。官方的索引实现方式中， MySQL 的索引是支持HASH的，不过 InnoDB 和 MyISAM 并不支持。哈希的查找效率是O(1),但是它不支持范围查找。

B树和B+树的区别在于：

1、B树的非叶节点中除了存放下一层的页目录，也会存放数据，这就导致了每个非叶节点存放的下一层的页目录变少，可能会增加整颗树的高度。

2、B树的叶节点之间不会采用链式结构进行连接。范围查找需要重新遍历整棵树。

（四）聚簇索引和非聚簇索引

聚簇索引：像innodb存储引擎那样把B+树和数据存放在一起称为聚簇索引。

非聚簇索引：像MyISAM存储引擎那样把B+树和数据分离的方式称为非聚簇索引。

上图是 MyISAM 存储引擎中主键索引的存储结构，它与聚簇索引相同的是都采用B+树结构进行数据组织管理，但不同的是 InnoDB 存储引擎是将B+树和数据放在一起，而 MyISAM 存储引擎则是将B+树和数据分离。

同一个表中可以建立多个索引结构，除了主键索引以外，用户可以按照其他列信息建立普通索引。

InnoDB 存储引擎是将其他列信息也建立B+树，不同的是该B+树底层存储的并不是数据本身，而是该数据的主键键值，通过该键值再去对应的主键B+树进行数据查找的方式称为回表查询。

MyISAM 存储引擎是将其他列信息也建立B+树，而对于该B+树底层和主键B+树保持一致，存储数据所在的地址，即可以通过普通索引直接找到目标数据而不同通过回表查询操作。

四、索引的操作

（一）创建索引

1、创建主键索引

（1）方式一：

mysql> create table user1(id int primary key, name varchar(30));
Query OK, 0 rows affected (0.02 sec)

（2）方式二

mysql> create table user2(id int, name varchar(30), primary key(id));
Query OK, 0 rows affected (0.02 sec)

（3）方式三

mysql> create table user3(id int, name varchar(30));
Query OK, 0 rows affected (0.02 sec)mysql> alter table user3 add primary key(id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、创建唯一索引

（1）方式一

mysql> create table user4(id int primary key, name varchar(30) unique);
Query OK, 0 rows affected (0.02 sec)

（2）方式二

mysql> create table user5(id int primary key, name varchar(30), unique(name));
Query OK, 0 rows affected (0.02 sec)

（3）方式三

mysql> create table user6(id int primary key, name varchar(30));
Query OK, 0 rows affected (0.01 sec)mysql> alter table user6 add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、创建普通索引

（1）方式一

mysql> create table user7(-> id int primary key,-> name varchar(30),-> index(name)-> );
Query OK, 0 rows affected (0.05 sec)

（2）方式二

mysql> create table user8(id int primary key, name varchar(30));
Query OK, 0 rows affected (0.02 sec)mysql> alter table user8 add index(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

（3）方式三

mysql> create table user9(id int primary key, name varchar(30));
Query OK, 0 rows affected (0.02 sec)mysql> create index name on user9(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

4、创建复合索引

mysql> create table user10(id int primary key, name varchar(30), email varchar(30));
Query OK, 0 rows affected (0.02 sec)mysql> create index name on user10(name, email);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建的复合索引其实是一颗B+索引，会发现name和email的普通键一样，复合索引的作用在于指定多个字段构建一颗B+树，如果需要高频的通过name找到email的操作，就可以构建复合索引，这样就避免了回表查询，通过索引找另一个索引的方式叫索引覆盖。

以下代码是显示该表存在的索引结构，可以看出列name和email上存在同一个索引name。

*************************** 1. row ***************************Table: user10Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
*************************** 2. row ***************************Table: user10Non_unique: 1Key_name: nameSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: 
*************************** 3. row ***************************Table: user10Non_unique: 1Key_name: nameSeq_in_index: 2Column_name: emailCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: 
3 rows in set (0.00 sec)ERROR: 
No query specified

5、创建全文索引

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文行全文检索，可以使用sphinx的中文版。

--创建全文索引
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)--创建全文索引
)engine=MyISAM;
--插入数据
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

分别使用不同查询和全文索引：

--普通查询
select * from articles where body like '%database%';
--全文索引
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database');

（二）查询索引

//mysql> show key from user1 \G;
mysql> show index from user1 \G;
*************************** 1. row ***************************Table: user1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
1 row in set (0.00 sec)ERROR: 
No query specified

（三）删除索引

1、删除主键索引：

mysql> show index from user1 \G;
*************************** 1. row ***************************Table: user1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: 
1 row in set (0.00 sec)ERROR: 
No query specifiedmysql> alter table user1 drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> show index from user1 \G;
Empty set (0.00 sec)ERROR: 
No query specified

2、删除其他索引

mysql> alter table user4 drop index name;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3、直接删除索引

mysql> drop index name on user7;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

（四）总结

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

查看全文

http://www.xdnf.cn/news/629335.html

【动态规划】P12223 [蓝桥杯 2023 国 Java B] 非对称二叉树|普及+

python打卡day35@浙大疏锦行

【笔记】OpenCV的学习（未完）

CodeBuddy 实现图片转素描手绘工具

springboot中各模块间实现bean之间互相调用（service以及自定义的bean）

符合Python风格的对象(使用 __slots__ 类属性节省空间)

搜索二叉树

开盘啦 APP 抓包逆向分析

从有线到无线：PLC通讯“剪断“最后一根线！

MQTT-排它订阅

STM32F103 HAL多实例通用USART驱动 - 高效DMA+RingBuffer方案，量产级工程模板

python训练营第33天

Lesson 22 A glass envelope

HJ14 字符串排序【牛客网】

Spring AI 源码解析：Tool Calling链路调用流程及示例

从法律视角看债务管理：湖北理元理律师事务所的实践探索

【信息系统项目管理师】一文掌握高项常考题型-成本类计算

巡礼中国西极·跨越昆仑天山 | 北斗卫星徽章护航昆仑科考

神经算子项目实战：数据分析、可视化与实现全过程

归一化超全总结！！

leetcode hot100刷题日记——16.全排列

探秘Transformer系列之（34）--- 量化基础

开源轻量级语音合成和语音克隆模型：OuteTTS-1.0-0.6B

AWTK嵌入式图形框架开发备忘(二)

【GESP真题解析】第 5 集 GESP 二级 2023 年 3 月编程题 2：百鸡问题

【Python】【电网规划】基于经济与可靠性双目标的混合配电系统规划及可靠性评估

ShenNiusModularity项目源码学习（30：ShenNius.Admin.Mvc项目分析-15）

可增添功能的鼠标右键优化工具

【PINN】DeepXDE学习训练营(33)——pinn_forward-fractional_Poisson_1d.py

C++：共享指针unique_ptr的理解与应用

一、概念

二、如何与磁盘进行交互

三、索引的理解

（一）page 的数据结构

（二）page 如何进行组织

（三）B+树的特征

（四）聚簇索引和非聚簇索引

四、索引的操作

（一）创建索引

1、创建主键索引

2、创建唯一索引

3、创建普通索引

4、创建复合索引

5、创建全文索引

（二）查询索引

（三）删除索引

1、删除主键索引：

2、删除其他索引

3、直接删除索引

（四）总结

相关文章：