mysql索引的存储方式 mysql索引的存储结构

转载

langrisser 2023-08-28 18:16:52

文章标签 mysql索引的存储方式 mysql 索引字段创建表 文章分类 MySQL 数据库

什么是索引？

MySQL底层除了在存储数据之外，还维护着特定查找算法的数据结构（B+树）。这些数据结构以特定的方式指向数据，所以我们就可以快速的查找数据。
一般来说，索引本身也很大，不能全部存储到内存中，也会以文件的形式落地到磁盘。

索引的优势和劣势

优势：

提高数据的检索效率，降低数据库的IO成本。
通过索引对数据进行排序，降低CPU的消耗，提高排序效率。

劣势：

虽然大大提高了查询效率，但是也降低了增删改的效率。因为每次更新数据后，数据库不仅要更新数据，还要更新索引中存储的索引字段。
实际上索引也是一张表，它存储的是字段id和索引字段，并指向实体表的数据，所以索引也需要占用空间。

MySQL中的索引结构

MySQL中的索引是B+树结构。

mysql索引的存储方式 mysql索引的存储结构_创建表

如图所示，根节点只存储关键字信息和子节点信息，并不存储真实的数据，只有叶子节点才存储真正的数据。这样就可以在索引文件中尽可能多的存储关键字信息，减少磁盘IO。除此之外，叶子节点还增加了指向相邻叶子节点的指针，保证了数据库的顺序访问，降低排序成本。

MySQL索引基本语法

创建：CREATE [UNIQUE] INDEX [索引名] ON 表名(索引字段)
删除：DROP INDEX [索引名] ON 表名
查看：SHOW INDEX FROM 表名
ALTER增加索引：ALTER TABLE 表名 ADD INDEX 索引名 (索引字段)

索引的分类及创建

单值索引/单列索引：一个索引只包含一个列。一个表中可以有多个单列索引。

-- 创建表同时创建单列索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT,
KEY (sname)
);
-- 创建表后单独添加单值索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT
);
CREATE INDEX idx_student_sname ON student(sname) ;

唯一索引：索引的列必须唯一，但允许有空值。

-- 创建表同时添加索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT,
UNIQUE KEY (sname)
);
-- 创建表后单独添加索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT
);
CREATE UNIQUE INDEX idx_student_sname ON student(sname);

主键索引：设定主键后数据库会自动添加索引。

-- 创建表同时添加索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT
);
-- 创建表后单独添加索引
CREATE TABLE student(
id INT AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT,
KEY(id)
);
ALTER TABLE student ADD PRIMARY KEY student(id);

复合索引：一个索引包含多个列

-- 创建表同时创建索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT,
KEY(sname,class_id)
);

-- 创建表后单独添加索引
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(255),
gender CHAR(1),
class_id INT
);
CREATE INDEX idx_student_name ON student(sname,class_id);