【Mysql】索引

开源代码
2025-09-01 15:39:02

【Mysql】索引一、索引的简介二、索引结构2.1 Hash2.2 二叉搜索树2.3 B树2.4 B+树三、索引分类3.1 主键索引3.2 普通索引3.3 唯一索引3.4 全文索引3.5 聚集索引3.6 非聚集索引3.7 索引覆盖四、使用索引4.1 自动创建索引4.2 手动创建索引4.2.1 主键索引4.2.2 唯一索引4.2.3 普通索引4.2.4 复合索引 4.3 查看索引4.5 删除索引五、索引的总结

一、索引的简介

索引（Index）是一种数据结构，用于优化数据库查询性能。它类似于书籍的目录或图书馆的卡片目录，通过为数据库表中的列创建索引，可以快速定位到数据的位置，从而加快查询速度。

以二叉搜索树为例，在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为全表扫描，性能很低。显⽽易⻅，使⽤索引的⽬的只有⼀个，就是提升数据检索的效率，在应⽤程序的运⾏过程中，查询操作的频率远远⾼于增删改的频率。

二、索引结构 2.1 Hash

Hash的时间复杂度是O(1),查找速度非常快，但是Hash不支持范围查找。比如：我们想知道薪资在6000-8000之间的员工都有谁的话，用Hash查找的话就不合适了。

2.2 二叉搜索树

缺点：

顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。二叉搜索树的最坏时间复杂度是O(N)

这里大家就会想到红黑树或者AVL树，在数据过多时会想到红黑树或者AVL树，虽然可以让二叉树的数据平衡或者近似平衡，但终归还是二叉结构。

2.3 B树

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。即如果是一个深度为5的B树，那么每个节点最多存储4个key值，5个指针：分裂： 5阶的树，一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。

2.4 B+树

B+树是⼀种经常⽤于数据库和⽂件系统等场合的平衡查找树，MySQL索引采⽤的数据结构，以4阶的B+树为例：绿色框框：里面的部分，只起索引作用，不存储数据。红色框框：是数据存储部分，在其叶子节点中要存储具体的数据。

B+树的特点：

能够保持数据稳定有序，插入与修改有较稳定的时间复杂度非叶子节点仅具有索引作用，不存储数据，所有叶子节点保存真实数据所有叶子节点构成一个有序链表，可以按照key排序的次序依次遍历全部数据

重点： B+树和B树的对比：

叶子节点中的数据是连续的，且相互链接，便于区间查找和搜索。非叶子节点的值都包含在叶子节点中对于B+树而言，在相同树高的情况下，查找任一元素的时间复杂度都一样，性能均衡。三、索引分类 3.1 主键索引当在⼀个表上定义⼀个主键 PRIMARY KEY 时，InnoDB（InnoDB是MySQL数据库的默认存储引擎）使⽤它作为聚集索引。推荐为每个表定义⼀个主键。如果没有逻辑上唯⼀且⾮空的列或列集可以使⽤主键，则添加⼀个自增列。 3.2 普通索引最基本的索引类型，没有唯一性的限制。可能为多列创建组合索引，称为复合索引或组全索引 3.3 唯一索引当在一个表上定义一个唯一键 UNQUE 时，自动创建唯一索引。与普通索引类似，但区别在于唯一索引的列不允许有重复值。 3.4 全文索引基于文本列(CHAR、VARCHAR或TEXT列)上创建，以加快对这些列中包含的数据查询和 DML（数据操作语言）操作用于全文搜索，仅MyISAM（MyISAM 是 MySQL 中的一个存储引擎，曾经是 MySQL 早期版本的默认存储引擎。它以快速读取性能和简单的表结构而闻名，但不支持事务（ACID）和行级锁。）和InnoDB引擎支持。 3.5 聚集索引与主键索引是同义词如果没有为表定义 PRIMARY KEY，InnoDB使用第一个 UNIQUE和 NOT NULL 的列作为聚集索引。如果表中没有 PRIMARY KEY或合适的 UNIQUE 索引，InnoDB会为新插入的行生成一个行号并用6字节的 ROW_ID 字段记录，ROW_ID 单调递增，并使用 ROW_ID 做为索引。 3.6 非聚集索引聚集索引以外的索引称为非聚集索引或二级索引二级索引中的每条记录都包含该行的主键列，以及二级索引指定的列。InnoDB使用这个主键值来搜索聚集索引中的行，这个过程称为回表查询

回表查询： orders表：

CREATE TABLE orders ( order_id INT PRIMARY KEY, user_id INT, product_name VARCHAR(100), price DECIMAL(10, 2) );

创建索引：

CREATE INDEX idx_user_id_product ON orders(user_id, product_name);

带回表查询：

SELECT order_id, user_id, product_name FROM orders WHERE user_id = 1;

这个查询使用了索引idx_user_id_product，但order_id不在索引中，MySQL需要回表查找order_id。

3.7 索引覆盖当一个select语句使用了普通索引且查询列表中的列刚好是创建普通索引时的所有或部分列，这时就可以直接返回数据，而不用回表查询，这样的现象称为索引覆盖。

以上面的为例：

CREATE INDEX idx_user_id_product_all ON orders(user_id, product_name, order_id);

索引覆盖（不带回表查询）：

SELECT order_id, user_id, product_name FROM orders WHERE user_id = 1;

查询就不再需要回表，因为索引中已包含所有所需字段

四、使用索引 4.1 自动创建索引当我们为一张表加主键约束(Primary key)，外键约束(Foreign Key)，唯一约束(Unique)时，MySQL会为对应的的列自动创建一个索引如果表不指定任何约束时，MySQL会自动为每一列生成一个索引并用ROW_ID 进行标识 4.2 手动创建索引 4.2.1 主键索引 -- 自动创建主键索引 create table t_test_pk( id bigint PRIMARY KEY auto_increment, name varchar(20)); -- 创建表时指定主键列 create table t_test_pk1( id bigint auto_increment, name varchar(20), PRIMARY KEY(id) ); -- 修改表中的列为主键列 create table t_test_pk2( id int, name varchar(20) ); -- 将Id设置为主键 alter table t_test_pk2 add primary key (id) ; -- 将Id的类型改为bigint并设置为自增列 -- 自增列：因为自增列只能是主键，所以设置自增列前需要保证此列是不是则增列 alter table t_test_pk2 modify id bigint auto_increment; 4.2.2 唯一索引 -- 自动创建唯一索引 create table t_test_uk( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE ); -- 创建表时指定唯一索引列 create table t_test_uk1( id bigint PRIMARY KEY auto_increment, name varchar(20), UNIQUE(name) ); -- 单独创建索引并指定索引名 create table t_test_uk2( id bigint PRIMARY KEY auto_increment, name varchar(20) ); -- 将name设置为唯一索引列 alter table t_test_uk2 add UNIQUE (name) ; 4.2.3 普通索引 -- 创建表时指定普通索引 create table t_test_index( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE, sex varchar(20), INDEX(sex) ); -- 修改表中的列为普通索引列 create table t_test_index1( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE, sex varchar(10) ); alter table t_test_index1 add index (sex) ; -- 单独创建索引并指定索引名 create table t_test_index2( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE, sex varchar(10) ); create index index_sex on t_test_index2(sex); 4.2.4 复合索引 -- 创建表时指定索引列 create table t_test_cp_index( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE, sex varchar(20), age BIGINT, INDEX(sex,age) ); -- 修改表中的列为复合索引列 create table t_test_cp_index1( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE, sex varchar(10), age BIGINT ); alter table t_test_cp_index1 add index (sex,age) ; -- 单独创建索引并指定索引名 create table t_test_cp_index2( id bigint PRIMARY KEY auto_increment, name varchar(20) UNIQUE, sex varchar(10), age BIGINT ); create index cp_index_sex_age on t_test_cp_index2(sex,age); 4.3 查看索引 SHOW INDEX FROM table_name ; 4.5 删除索引

当我们删除主键时，我们需要确保主键不是自增列，如果主键是自增列我们就需要先将主键的自增列去掉，然后再删除主键。

alter table 表名 drop PRIMARY KEY;

举例：错误信息：Incorrect table definition; there can be only one auto column and it must be defined as a key。

五、索引的总结

优点：

提高查询性能快速定位数据：索引通过有序结构（如B树、哈希表等）快速定位数据，避免全表扫描，从而显著提高查询速度。优化排序和分组操作：索引可以加速ORDER BY和GROUP BY操作，减少排序和分组的开销。支持唯一性约束

确保数据唯一性：唯一索引（UNIQUE）可以确保列中的数据值是唯一的，防止重复数据的插入。

提高数据检索效率

减少I/O操作：通过索引，数据库可以减少磁盘I/O操作，因为索引通常存储在内存中（如InnoDB的缓冲池），从而提高整体性能。

支持全文搜索

全文索引：对于文本数据，全文索引可以快速定位包含特定单词或短语的行，适用于搜索引擎和文档管理系统。

缺点：