Mysql索引

猫咪-9527

发布于 2025-03-29 10:26:43

2420

文章被收录于专栏：猫咪-9527猫咪-9527

一、如果没有索引会有什么问题

索引能够大幅提高 MySQL 的查询性能，最简单有效。

优点：不用增加内存，不用改代码或 SQL 语句，只需执行正确的 CREATE INDEX，查询效率有可能提升数百上千倍
缺点：写操作性能下降（插入/更新/删除增加 IO负担）

结论：索引是用于提高海量数据查询效率的重要手段，有时不能不用！

二、磁盘矩形形成

MySQL 存储数据在磁盘中，磁盘的基础单位为“扇区”，512B~4KB 不等。因磁盘速度慢，所以减少 IO 次数是性能优化重点。

根本IO单位

操作系统的IO单位：Block 4KB
MySQL (InnoDB) 的IO单位：Page 16KB

硬件IO场景

随机访问：效率低，磁头需大角度移动
连续访问：效率高，磁头小小移动

故，用Page作为IO单位，是为了提高 IO 效率，不是所有访问都被读输。

三、MySQL 的 Page 系统

一个 Page = 16KB
查询时，是以 Page 为单位读取数据
部署了大型内存区 Buffer Pool，用于 Page 的缓存和管理

一次IO不读1条，而是1个Page，遮盖N条记录，尽可能减少IO次数

四、进化：Page -> B+树

Page 里的数据是有序的，相当于纸上的分类目录
多个 Page 合理组合，形成 B+树
目录Page 不存储数据，只存储其子Page的最小 key 和指针
可以快速缓存中存储上层Page，后续通过 key 快速下滑

五、索引类型

在数据库中，索引是提高查询性能的重要工具。以下是几种常见的索引类型的介绍：

1. 主键索引 (Primary Key)

定义：主键索引是数据库中用来唯一标识表中每一行的索引。每个表只能有一个主键。
创建主键索引：

方法一：

----创建表时，直接再字段名后面添加primary key
create table user1(id int primary key,name varchar(20));

方法二：

----在创建表后添加一列或者多列为主键索引
create table user 2(id int ,name varchar, primary key(id));

方案三：

---创建表时没有指定主键索引
create table user2 (id int ,name varchar(20));
---创建后添加主键索引
alter table user2 add primary key(id);

特点：
- 主键列的值必须是唯一的，并且不能为 NULL。
- 主键索引通常是聚集索引，即表中的数据会按照主键的顺序存储。
- 用于提高对唯一记录的查询效率。(主键只有一个不会重复)

去除主键索引：

alter table user1 drop primary key;

2. 唯一索引 (Unique)

定义：唯一索引是确保数据表中某一列或多列的值唯一的索引。不同于主键，唯一索引允许某些 NULL 值。
创建唯一键索引

方法一：

create table d1(id int ,name varchar(20) unique);

方法二：

create table d1 (id int ,name varchar(20) ,unique(id));

方法三：

create table d1(id int ,name varchar(20));
alter table d1 add unique(id);

特点：
- 唯一索引的列值必须唯一，但允许存在多个 NULL 值（具体取决于数据库的实现）。
- 可以有多个唯一索引，但是每个唯一索引保证指定列的数据唯一性。
- 唯一索引通常也是非聚集索引。
用途：用于需要保证某些列的唯一性的场景，除了主键以外的其他唯一性要求。

删除索引：

方法一：

alter table 表名 drop index couln_name;

方法二：

drop index 字段名 on 表名;

注：复合唯一键明确其中主导地位的是哪个键,删除主导键就意味着删除复合键

此时占据主导的就是id键

3. 普通索引 (Index)

定义：普通索引（非唯一索引）是数据库中最常见的索引类型，提供对数据的快速查找能力。
创建普通索引：

方案一：

create table d3(id int ,name varchar(20),index (name));

方案二：

create table d3(id int ,name varchar(20));
alter table d3 add index(name);

方案三：

create table d4 (id int ,name varchar(20));
create index key_name on d4(id);

方案三是对索引重新起名字，这是与方案一二的不同之处 。

特点：
- 普通索引不要求列的值是唯一的，可以包含重复值。
- 对于查询操作（如 SELECT）有显著性能提升，尤其是在大量数据的情况下。
- 普通索引通常是非聚集索引。
用途：用于提高查询效率，特别是在列上进行 WHERE 查询时，数据库会使用索引来加速检索。

删除索引：

方案一：

drop index key_name on d4;

如果你对索引重新起名字，这是的key_name就是你重新起的名字。

方案二：

alter table d4 drop index key_name;

4. 全文索引 (Fulltext)

定义：全文索引是专门用于加速文本数据（如长文本字段）的查询操作的索引类型，通常用于对包含大量文字数据的字段进行搜索。
创建主键：全文索引创建方式与其他方式基本一样这里不再过度赘述，直接开始测试
全文索引需要满足以下条件：
- 存储引擎：InnoDB（MySQL ≥5.6）或 MyISAM（旧版本）。
- 字段类型：必须是 CHAR、VARCHAR 或 TEXT 类型。
- 中文支持：需配置 ngram 分词插件（默认仅支持英文空格分词）

测试：

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 ...');

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 ...'); 这是两个比较重要的地方大家要注意一下

使用主键索引：

特点：
- 通过全文索引，可以对文本字段进行更复杂的检索，如查找包含某些单词的记录。
- 在处理大量文本数据时，能够显著提升查询效率。
- 常用于支持搜索功能的场景，如文章、评论、博客等的搜索。
用途：主要用于支持 MATCH / AGAINST 查询操作，用于全文搜索，特别适合用于搜索引擎的实现。