约 1748 字大约 6 分钟
2025-05-27
数据索引
数据结构的角度
- B+ 树索引：通过树形结构存储数据，适用于范围查询（如
BETWEEN）和精确查询（如=），支持有序数据的快速查找、排序、聚合操作。是MySQL默认的索引类型，常用于InnoDB和MyISAM引擎。 - 哈希索引：基于哈希表的结构，适用于等值查询（如
=），查询速度非常快，但不支持范围查询（如>、<）。哈希索引不存储数据的顺序，常用于Memory引擎。 - 倒排索引/全文索引：用于全文搜索，将全文分词，通过存储词与文档的映射，支持模糊匹配和关键字搜索。特别适合用于大文本字段，如
TEXT类型的列，用于查找包含特定词语的记录。 - R- 树索引/多维空间树：专为多维空间数据（如地理坐标）设计，适用于空间查询（例如，计算地理位置的最近距离、区域查询等）。常用于存储和查询地理信息系统（GIS）中的空间数据。
重要
补充：B+ 树把所有的实际数据都存储在深度都一样的叶子节点上，而其他内部节点只存储索引，所有的叶子节点之间通过指针组成一个有序列表。
通常在 B+ 树上有两个指针头，一个指向根节点，另一个指向关键字最小的叶子节点。
基于上述两个指针和链表，就可以支持范围查询和随机查询。
而一颗 B+ 树的节点一般如下：
struct InternalNode {
int keys[N]; // 最多 N 个键/索引, 超出需修改树的结构(分裂操作)
Node* children[N + 1]; // 指向子节点的指针
int key_count; // 关键字/实际内容
};如果对应到数据库中，具体的分裂操作过程可以 看这篇博文，非常详细。
是否聚蔟的角度
- 聚簇索引：
InnoDB中主键索引就是聚簇索引，它基于主键排序存储，之所以叫聚簇索引是因为索引的叶子节点存储完整数据行数据。聚簇索引适合进行大范围查询和排序操作，因为叶子节点存储的是完整的数据行，可以方便地进行范围扫描和按顺序读取。 - 非聚簇索引：
InnoDB中非主键索引的索引，之所以称之为非聚簇是因为这个索引的叶子节点仅保存索引字段和主键值。如果要查询完整的数据行中的数据，需要再从聚簇索引即主键索引中通过主键查询，一个表可以有多个非聚簇索引。
重要
补充：在 InnoDB 里聚簇索引就是主键索引，非聚簇索引指的是非主键索引（辅助索引、二级索引），默认情况下它们都是 B+ 树实现的。例如通过姓名仅查询 id 的话，直接利用姓名索引就能得到 id 数据。如果图方便直接用 select *，那就不得不通过 id 再去主键索引查找不需要的性别和地址，浪费资源，且可能伴随着很多随机 I/O。
而通过二级索引查找得到 id ，再去主键索引查询记录的这个过程叫回表，一般做回表的效率会更加高一些。
索引性质的角度
- 主键索引：表中的每一行数据都有唯一的主键。每个表只能有一个主键索引，且主键值不能为
NULL，InnoDB中主键索引是聚簇索引结构实现的，其实指定主键就会自动生成。 - 唯一索引：保证索引列中的值是唯一的，可以有效防止重复数据的插入。唯一索引允许
NULL值，但一个列中可以有多个NULL，其实指定唯一键就会自动生成。 - 普通索引/二级索引/辅助索引：一般指非主键索引且非唯一索引，不要所有查询都用
select *，因为如果本来只需要查询索引列数据的话，直接利用二级索引就能得到所要的数据。 - 联合索引：由多个列组成的索引，适用于多列的查询条件，能够提高包含多个条件的查询的性能。联合索引中的列是按照指定顺序排列的。
- 全文索引：用于全文搜索，支持对长文本字段（如
TEXT类型）进行关键字查找，支持自然语言处理、模糊匹配等操作，适用于需要对文本内容进行复杂搜索的场景。不过可以考虑使用ES来替代。 - 哈希索引：基于哈希表的结构，适用于等值查询（如
=），查询速度非常快，但不支持范围查询（如>、<）。哈希索引不存储数据的顺序，常用于 Memory 引擎。 - 空间索引：用于空间数据（如地图上的经纬度坐标等）查询，通常使用
R-树结构，适合多维数据的查询，如区域查询和最近距离查询，主要用于MyISAM和InnoDB存储引擎中的地理信息数据。
这里用一张表的创建过程来演示上述索引。
CREATE TABLE `users` (
`id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50),
`email` VARCHAR(50),
`content` VARCHAR(50),
`location` VARCHAR(50),
PRIMARY KEY(id) -- 主键索引
);
-- 唯一索引
CREATE UNIQUE INDEX idx_username ON users(username);
-- 普通索引
CREATE INDEX idx_username ON users(username);
-- 联合索引
CREATE INDEX idx_username_email ON users(username, email);
-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 哈希索引
CREATE INDEX idx_username_hash ON users(username) USING HASH;
-- 空间索引
CREATE SPATIAL INDEX idx_location ON places(location);存储引擎
InnoDB：
支持事务、行级锁、外键。
提供高并发性能，适用于高负载的
OLTP应用。数据以聚集索引的方式存储，提高检索效率。
MyISAM：
- 不支持事务和外键，使用表级锁。
适合读取多、更新少的场景，如数据仓库。
具有较高的读性能和较快的表级锁定。
MEMORY：
数据存储在内存中，速度快，但数据在服务器重启后丢失。
适用于临时数据存储或快速缓存。
NDB：
- 支持高可用性和数据分布，适合大规模分布式应用。
- 提供行级锁和自动分区。
ARCHIVE：
用于存储大量历史数据，支持高效的插入和压缩。
不支持索引，适合日志数据存储。
重要
补充：实际上我们仅需重点了解 InnoDB 和 MyISAM 即可，其它仅需有点印象即可，关于这两者的详细区别可以在这里看一下。
重要
补充：在 MySQL 8.4 版本中，实际上一共提供了 10 种不同的存储引擎，有兴趣可以了解一下官方文档。
