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

MySQL 之索引的结构、分类与语法

ds 2025/9/2 7:17:26

一、索引概述

在 MySQL 数据库中，索引就如同书籍的目录，它能够极大地提高数据查询的效率。没有索引时，MySQL 需要对表进行全表扫描来查找目标数据，当表中数据量较大时，这种方式会消耗大量的时间和资源；而有了索引，MySQL 可以通过索引快速定位到数据所在的位置，从而显著减少查询时间。

需要注意的是，索引并非越多越好。虽然索引能加快查询速度，但它也会占用额外的存储空间，并且在进行数据插入、更新和删除操作时，需要同时维护索引，这会降低这些操作的性能。因此，在创建索引时，需要根据实际的业务场景和查询需求进行合理规划。

二、MySQL 索引的结构

（一）B-Tree索引

B-Tree是一种平衡多路查找树，广泛应用于数据库索引结构中。它的每个节点可以存储多个关键字和对应的指针，具有以下显著特点：

  1. 平衡结构特性

    • B-Tree严格保持左右子树高度差不超过1
    • 这种平衡性保证了从根节点到任意叶子节点的路径长度基本相同
    • 示例：在一个包含100万条数据的表中，B-Tree索引通常只需3-4次磁盘I/O就能定位到数据

  2. 多路分支优势

    • 每个节点可以有M个分支（M通常为几百）
    • 相比二叉树，显著减少了树的高度
    • 实际测试表明，对于相同数据量，B-Tree比二叉树减少60%以上的节点访问次数

  3. 关键字有序排列

    • 节点内关键字按升序排列：[K₁, K₂, ..., Kₙ]
    • 每个关键字Kᵢ对应两个指针：
      • Pᵢ指向的子树中所有关键字 < Kᵢ
      • Pᵢ₊₁指向的子树中所有关键字 ≥ Kᵢ
    • 这种结构支持高效的二分查找算法

注意：虽然理论上存在B-Tree索引，但在MySQL的InnoDB和MyISAM存储引擎中实际使用的是其优化版本B+Tree索引。

（二）B+Tree索引

B+Tree是MySQL最核心的索引结构，InnoDB和MyISAM存储引擎都基于它实现索引，相比B-Tree有以下重要优化：

  1. 数据存储优化

    • 非叶子节点：仅存储键值和子节点指针（不存实际数据）
    • 叶子节点：存储完整数据记录（InnoDB）或数据物理地址（MyISAM）
    • 优势：单次查询最多只需访问h个节点（h为树高）

  2. 顺序访问特性

    • 所有叶子节点通过指针相连形成双向链表
    • 范围查询示例：
      SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'

      可快速定位到起始节点后顺序遍历

  3. 索引覆盖查询

    • 实现条件：查询字段都包含在索引中

    • 性能对比测试：

      查询类型平均响应时间(ms)
      索引覆盖2.3
      回表查询8.7

    • 创建复合索引示例：

      CREATE INDEX idx_name_age ON users(name, age);

（三）Hash索引

Hash索引基于哈希表实现，主要特点包括：

  1. 性能特点

    • 等值查询时间复杂度：O(1)
    • 测试数据：100万条记录中精确查找仅需0.02ms

  2. 使用限制（不支持）

    • 部分匹配（LIKE 'abc%'）
    • 排序操作（ORDER BY）
    • 范围查询（>、<、BETWEEN）

  3. 哈希冲突处理

    • 典型解决方案：链地址法
    • 冲突率公式：当负载因子α=n/m > 0.75时性能显著下降 （n=记录数，m=桶数量）

  4. MySQL实现情况

    • Memory引擎：默认使用Hash索引
    • InnoDB自适应哈希：
      • 自动为频繁访问的页创建哈希索引
      • 通过参数innodb_adaptive_hash_index控制

（四）Full-Text索引

全文索引专门用于文本搜索，核心特性包括：

  1. 适用场景

    • 支持的数据类型：CHAR、VARCHAR、TEXT
    • 典型应用：文章搜索、产品描述搜索

  2. 分词机制

    • 英文：按空格和标点分词
    • 中文：需要ngram解析器（MySQL 5.7+）
      CREATE FULLTEXT INDEX ft_idx ON articles(content) 
WITH PARSER ngram;

  3. 搜索模式对比

    模式语法示例特点
    自然语言模式(默认)AGAINST('数据库')按相关性排序
    布尔模式AGAINST('+MySQL -Oracle' IN BOOLEAN MODE)支持+/-等操作符

  4. 版本支持情况

    • InnoDB支持：MySQL 5.6+
    • 性能提示：对于大文本字段，建议单独建表并建立全文索引

三、MySQL 索引的分类

（一）按功能划分

主键索引（Primary Key Index）

主键索引是一种特殊的唯一索引，它用于唯一标识表中的每一条记录，一个表只能有一个主键索引。主键索引的关键字不能为空，并且具有唯一性。在创建表时，可以通过PRIMARY KEY关键字来指定主键索引。

示例：
创建一个用户表并指定 id 字段为主键索引：

CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,email VARCHAR(100),PRIMARY KEY (id)
);

特点：

  1. 当插入数据时，如果 id 字段的值重复或为空，MySQL 会报错，从而保证了数据的唯一性和完整性
  2. 主键索引的查询效率非常高，因为 InnoDB 存储引擎会将表中的数据按照主键索引的顺序进行存储（聚集索引）
  3. 主键字段通常设置为自增(AUTO_INCREMENT)以确保唯一性
  4. 主键索引在InnoDB中会作为聚集索引使用

应用场景：

  • 用户表的用户ID
  • 订单表的订单号
  • 任何需要唯一标识记录的场合

唯一索引（Unique Index）

唯一索引用于保证索引字段的值具有唯一性，但允许字段的值为空（一个表中可以有多个字段允许为空）。与主键索引不同，一个表可以有多个唯一索引。通过UNIQUE关键字可以创建唯一索引。

示例：
为用户表的 email 字段创建唯一索引：

CREATE UNIQUE INDEX idx_user_email ON user (email);

特点：

  1. 创建唯一索引后，如果插入或更新数据时，email 字段的值与已有的值重复，MySQL 会报错
  2. 唯一索引允许字段的值为空，但可以有多个NULL值（因为NULL不等于任何值，包括NULL）
  3. 唯一索引可以提高查询效率，同时保证数据唯一性
  4. 相比普通索引，唯一索引在插入或更新时需要额外检查唯一性约束

应用场景：

  • 用户邮箱、手机号等需要唯一但不作为主键的字段
  • 避免业务上不允许重复的数据

普通索引（Normal Index）

普通索引是最基本的索引类型，它没有唯一性约束，也没有非空约束，主要用于提高数据查询的效率。通过INDEX或KEY关键字可以创建普通索引。

示例：
为用户表的 name 字段创建普通索引：

CREATE INDEX idx_user_name ON user (name);

特点：

  1. 可以加快对 name 字段的查询速度
  2. 执行SELECT * FROM user WHERE name = '张三'查询时，MySQL 可以通过 idx_user_name 索引快速定位到相关数据
  3. 不影响数据的插入和更新速度（不像唯一索引需要检查唯一性）
  4. 可以创建多个普通索引，但索引过多会影响写入性能

应用场景：

  • 经常作为查询条件的字段
  • 需要提高查询性能但不需要唯一约束的字段
  • JOIN操作中经常使用的字段

全文索引（Full-Text Index）

全文索引主要用于对文本类型的数据进行全文检索，能够高效地进行关键词搜索和大文本字段的模糊匹配。

示例：
创建文章表的 content 字段全文索引：

CREATE FULLTEXT INDEX idx_article_content ON article (content);

特点：

  1. 适用于MyISAM和InnoDB引擎（MySQL 5.6+）
  2. 只能用于CHAR、VARCHAR和TEXT类型的字段
  3. 使用MATCH() AGAINST()语法进行全文搜索
  4. 支持自然语言搜索和布尔搜索模式
  5. 对于中文全文检索需要配合分词插件

应用场景：

  • 文章内容搜索
  • 产品描述搜索
  • 任何需要文本内容搜索的场景

（二）按索引字段数量划分

单列索引（Single-Column Index）

单列索引是指只基于表中的一个字段创建的索引，前面提到的主键索引、唯一索引、普通索引和全文索引都可以是单列索引。

示例：
为 user 表的 age 字段创建普通单列索引：

CREATE INDEX idx_user_age ON user (age);

特点：

  1. 优点：创建和维护简单，查询时只需要根据单个字段进行匹配
  2. 缺点：当查询条件涉及多个字段时，单列索引的效率可能较低
  3. 适合在单个字段查询频率高的场景使用
  4. 可以为同一字段创建不同类型的单列索引（如唯一索引和普通索引不能同时存在）

应用场景：

  • 单个字段的等值查询
  • 单个字段的范围查询
  • 排序操作(SORT BY)中使用的单个字段

复合索引（Composite Index）

复合索引（也称为联合索引）是指基于表中的多个字段创建的索引，它将多个字段组合在一起作为索引关键字。

示例：
创建name和age的复合索引：

CREATE INDEX idx_user_name_age ON user (name, age);

最左前缀原则详解：

在复合索引中，字段的顺序非常重要，MySQL 会按照索引中字段的顺序进行排序和查找。对于 idx_user_name_age 复合索引：

  1. MySQL 首先会按照 name 字段进行排序
  2. 对于 name 字段值相同的记录，再按照 age 字段进行排序

可以使用该索引的查询示例：

SELECT * FROM user WHERE name = '张三';
SELECT * FROM user WHERE name = '张三' AND age = 25;
SELECT * FROM user WHERE name LIKE '张%'; -- 模糊查询中%在右侧时可以使用索引

无法使用该索引的查询示例：

SELECT * FROM user WHERE age = 25;
SELECT * FROM user WHERE name LIKE '%三'; -- 模糊查询中%在左侧时无法使用索引
SELECT * FROM user WHERE age = 25 AND name = '张三'; -- 字段顺序与索引顺序不一致

优化建议：

  1. 将查询频率高的字段放在复合索引的前面
  2. 选择性高的字段（唯一值多的字段）放在前面
  3. 遵循最左前缀原则编写查询条件
  4. 避免创建过多复合索引，一般3-5个字段为宜

应用场景：

  • 多条件联合查询
  • 多字段排序
  • 覆盖索引场景（索引包含所有查询字段）

（三）按存储结构划分

根据索引的存储结构，MySQL 索引可以分为聚集索引（Clustered Index）和非聚集索引（Non-Clustered Index），这种分类方式主要与 InnoDB 存储引擎相关。

聚集索引（Clustered Index）

特点：

  1. 索引的叶子节点存储的是完整的数据记录
  2. InnoDB 存储引擎的主键索引就是聚集索引
  3. 数据是按照聚集索引的顺序进行存储的
  4. 一个表只能有一个聚集索引
  5. 查询效率非常高，特别是根据主键查询时

工作机制：

  1. 当根据主键查询时，可以直接通过聚集索引获取完整数据
  2. 范围查询效率高，可以快速定位范围的起始和结束位置
  3. 如果没有显式定义主键，InnoDB会选择：
    • 第一个非空的唯一索引作为聚集索引
    • 如果没有，则自动创建一个隐藏的6字节主键作为聚集索引

示例：

-- 聚集索引查询（高效）
SELECT * FROM user WHERE id = 10;-- 范围查询（高效）
SELECT * FROM user WHERE id BETWEEN 10 AND 20;

非聚集索引（Non-Clustered Index）

特点：

  1. 索引的叶子节点存储的是主键值（InnoDB）或物理地址（MyISAM）
  2. 需要"回表"操作获取完整数据
  3. 一个表可以有多个非聚集索引
  4. 查询效率通常低于聚集索引

InnoDB的非聚集索引工作机制：

  1. 首先在非聚集索引中找到主键值
  2. 然后到聚集索引中获取完整数据（回表）

MyISAM的非聚集索引特点：

  1. 所有索引都是非聚集索引
  2. 叶子节点存储数据的物理地址
  3. 直接通过物理地址获取数据，不需要回表

示例：

-- 假设name字段有非聚集索引
SELECT * FROM user WHERE name = '张三';
-- 执行过程：
-- 1. 在name索引中查找"张三"对应的主键id
-- 2. 用找到的id到主键索引中获取完整记录

优化建议：

  1. 尽量使用覆盖索引（索引包含所有查询字段）
  2. 避免不必要的回表操作
  3. 对于频繁查询的非主键字段，考虑创建适当的非聚集索引

应用场景：

  • 非主键字段的查询
  • 需要建立多个索引的场景
  • 覆盖索引优化查询性能

四、MySQL 索引的语法

索引类型

MySQL 支持多种索引类型：

  • 主键索引（PRIMARY KEY）
  • 唯一索引（UNIQUE）
  • 普通索引（INDEX/KEY）
  • 全文索引（FULLTEXT）
  • 复合索引（多列组合索引）

一、创建索引

1. 创建表时创建索引

主键索引

主键索引是特殊的唯一索引，不允许NULL值，且每个表只能有一个主键。

CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,email VARCHAR(100),PRIMARY KEY (id)  -- 主键索引
);

唯一索引

确保索引列的值唯一，允许NULL值（但NULL值可以有多个）。

CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,email VARCHAR(100) UNIQUE,  -- 方式一：列约束形式PRIMARY KEY (id),UNIQUE INDEX idx_user_age (age)  -- 方式二：表约束形式
);

普通索引

最基本的索引类型，没有唯一性限制。

CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,email VARCHAR(100),PRIMARY KEY (id),INDEX idx_user_name (name),  -- 普通索引（方式一）KEY idx_user_email (email)   -- 普通索引（方式二，KEY与INDEX等价）
);

全文索引

主要用于文本内容的全文搜索，仅支持MyISAM和InnoDB（MySQL 5.6+）存储引擎。

CREATE TABLE article (id INT NOT NULL AUTO_INCREMENT,title VARCHAR(100) NOT NULL,content TEXT,PRIMARY KEY (id),FULLTEXT INDEX idx_article_content (content)  -- 全文索引
);

复合索引

对多个列的组合建立索引，遵循最左前缀原则。

CREATE TABLE user (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,email VARCHAR(100),PRIMARY KEY (id),INDEX idx_user_name_age (name, age)  -- 复合索引
);

2. 在已有表上创建索引

使用CREATE INDEX语句
-- 创建普通索引
CREATE INDEX idx_user_name ON user (name);-- 创建唯一索引
CREATE UNIQUE INDEX idx_user_email ON user (email);-- 创建复合索引
CREATE INDEX idx_user_name_age ON user (name, age);-- 创建全文索引
CREATE FULLTEXT INDEX idx_article_content ON article (content);

使用ALTER TABLE语句
-- 创建普通索引
ALTER TABLE user ADD INDEX idx_user_age (age);-- 创建唯一索引
ALTER TABLE user ADD UNIQUE INDEX idx_user_email (email);-- 创建主键索引
ALTER TABLE user ADD PRIMARY KEY (id);-- 创建全文索引
ALTER TABLE article ADD FULLTEXT INDEX idx_article_content (content);-- 创建复合索引
ALTER TABLE user ADD INDEX idx_user_name_age (name, age);

二、查看索引

基本语法

SHOW INDEX FROM 表名 [FROM 数据库名];
-- 或
SHOW KEYS FROM 表名 [FROM 数据库名];

输出字段说明

字段名说明
Table索引所属的表名
Non_unique是否允许重复值(0=唯一,1=非唯一)
Key_name索引名称(主键为PRIMARY)
Seq_in_index字段在索引中的位置
Column_name索引字段名
Collation排序方式(A=升序,NULL=无排序)
Cardinality索引中唯一值的估计数
Sub_part索引使用的字符数(NULL=全部)
Packed是否压缩存储
Null是否允许NULL值
Index_type索引类型(BTREE,HASH,FULLTEXT等)
Comment索引注释信息

示例

-- 查看当前数据库中user表的索引
SHOW INDEX FROM user;-- 查看test数据库中user表的索引
SHOW INDEX FROM user FROM test;

三、删除索引

1. 使用DROP INDEX语句

-- 删除普通索引
DROP INDEX idx_user_name ON user;-- 删除唯一索引
DROP INDEX idx_user_email ON user;-- 删除复合索引
DROP INDEX idx_user_name_age ON user;-- 删除全文索引
DROP INDEX idx_article_content ON article;

2. 使用ALTER TABLE语句

-- 删除普通索引
ALTER TABLE user DROP INDEX idx_user_age;-- 删除主键索引
ALTER TABLE user DROP PRIMARY KEY;

注意事项

  1. 删除索引会释放存储空间，但可能影响查询性能
  2. 删除主键索引前需确保没有外键约束
  3. 删除主键后，InnoDB会自动创建隐藏主键(row_id)

四、修改索引

MySQL不支持直接修改索引，需要通过删除重建的方式实现。

修改复合索引字段顺序

-- 1. 删除旧索引
DROP INDEX idx_user_name_age ON user;-- 2. 创建新索引
CREATE INDEX idx_user_age_name ON user (age, name);

修改索引名称

-- 1. 删除旧索引
DROP INDEX idx_user_age ON user;-- 2. 创建新索引
CREATE INDEX idx_user_age_new ON user (age);

索引使用建议

  1. 为经常用于查询条件的列创建索引
  2. 为经常用于表连接的列创建索引
  3. 避免为频繁更新的列创建过多索引
  4. 考虑使用复合索引替代多个单列索引
  5. 定期分析和优化索引使用情况

五、索引使用的注意事项与最佳实践

（一）避免过度索引

问题分析

索引虽然能提高查询速度，但会带来额外的维护成本：

  • 写入性能下降：每次数据变更（INSERT/UPDATE/DELETE）时，数据库需要同步更新所有相关索引。例如，一个包含10个索引的表，每次写入都需要更新10个索引结构。
  • 存储空间增加：索引本身需要占用磁盘空间。一个典型的B+Tree索引通常占用与数据表相当甚至更多的存储空间。

最佳实践建议

  1. 高频查询字段优先索引：

    • WHERE子句过滤字段（如用户表的status字段，用于筛选活跃用户）
    • JOIN关联字段（如订单表的product_id关联商品表）
    • 排序/分组字段（如日志表的create_time用于时间范围查询）

  2. 选择性评估：

    • 对区分度高的字段（如用户名、手机号）创建索引效果显著
    • 对区分度低的字段（如性别、状态标志）创建索引收益有限

  3. 监控工具使用：

    -- 查询未使用的索引
SELECT * FROM sys.schema_unused_indexes;

（二）复合索引的最左前缀原则

深入解析

复合索引(A,B,C)相当于同时创建了三个索引：

  • (A)
  • (A,B)
  • (A,B,C)

实用案例

  1. 有效场景：

    • WHERE A=1 AND B=2
    • WHERE A=1 AND B>2 AND C=3
    • WHERE A IN (1,2,3) AND B=4

  2. 失效场景：

    • WHERE B=2（缺少A字段）
    • WHERE A=1 AND C=3（跳过B字段）
    • WHERE A LIKE '%test'（左模糊查询）

设计建议

  1. 字段顺序策略：

    • 第一顺位：等值查询字段
    • 第二顺位：范围查询字段
    • 第三顺位：排序字段

  2. 实际案例：

    -- 用户表优化示例
CREATE INDEX idx_user_region_age ON users(region, age, gender);

（三）索引失效常见场景

详细场景分析

  1. 函数操作：

    • 错误示例：WHERE DATE(create_time) = '2023-01-01'
    • 优化方案：WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'

  2. 类型不匹配：

    • 错误示例：WHERE id = '100'（id为INT类型）
    • 正确示例：WHERE id = 100

  3. OR条件处理：

    • 方案一：创建联合索引(name, email)
    • 方案二：改写为UNION查询：
      SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'zhangsan@example.com';

  4. 否定查询优化：

    • 改写方案：
      -- 原查询：WHERE status != 'deleted'
-- 优化为：
WHERE status IN ('active', 'pending', 'suspended')

（四）索引类型选择

类型对比指南

索引类型适用场景优势限制
B+Tree默认选择支持范围查询、排序占用空间较大
Hash内存表、精确匹配O(1)查询速度不支持范围查询
Full-Text文本搜索支持自然语言查询仅限文本字段

使用示例

  1. Hash索引：

    CREATE TABLE cache (key VARCHAR(100) PRIMARY KEY,value TEXT,INDEX USING HASH (key)
) ENGINE=MEMORY;

  2. 全文索引：

    ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, content);
-- 使用MATCH...AGAINST查询
SELECT * FROM articles WHERE MATCH(title,content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);

（五）索引维护策略

完整维护流程

  1. 分析阶段：

    EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id=100 AND status='paid';

  2. 优化决策：

    • 关键指标检查：
      • possible_keys：可能使用的索引
      • key：实际使用的索引
      • rows：预估扫描行数

  3. 重建操作：

    • 在线重建：
      ALTER TABLE orders ALGORITHM=INPLACE, LOCK=NONE, REBUILD;

    • 离线重建：
      OPTIMIZE TABLE orders;

维护周期建议

  • 高频率写入表：每周维护
  • 中频率表：每月维护
  • 低频率表：每季度维护

监控脚本示例

-- 查询索引碎片率
SELECT table_name,index_name,ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb,stat_description 
FROM mysql.innodb_index_stats 
WHERE database_name = DATABASE()
AND stat_name = 'size';







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






相关文章：


  • 
四个典型框架对比

    • 

  • 
在 Unity 中调用腾讯云机器翻译

    • 

  • 
一个好的智能体框架应该是什么样子

    • 

  • 
Activiti流程引擎的用户体系与MIS系统的用户体系打通

    • 

  • 
一、Git与Gitee常见问题解答

    • 

  • 
深度学习跨领域应用探索：从技术落地到行业变革

    • 

  • 
pcl案例2 叶片与根茎的分离step2

    • 

  • 
MyBatis 性能优化最佳实践：从 SQL 到连接池的全面调优指南

    • 

  • 
Java网络编程基础 Socket通信入门指南

    • 

  • 
机器视觉软件--VisionPro、Visual Master，Halcon 和 OpenCV 的学习路线

    • 

  • 
从零开始学习n8n-定时器+HTTP+飞书多维表格（上)

    • 

  • 
UFUNCTION C++ 的再次理解

    • 

  • 
产品月报｜睿本云8月产品功能迭代

    • 

  • 
AWS：AssumeRole背后真正的安全哲学，不仅是迂回

    • 

  • 
综合实验：DHCP、VLAN、NAT、BDF、策略路由等

    • 

  • 
K8S 知识框架和命令操作

    • 

  • 
Linux按键输入实验

    • 

  • 
MongoDB 内存管理：WiredTiger 引擎原理与配置优化

    • 

  • 
实战练习：通过HTTP请求节点的POST方法用API创建智能体  JSON序列化节点

    • 

  • 
Java学习笔记-反射（二）

    • 

  • 
使用ansible的playbook完成以下操作

    • 

  • 
Centos安装unoconv文档转换工具并在PHP中使用phpword替换word模板中的变量后，使用unoconv将word转换成pdf

    • 

  • 
高效浏览器标签页管理：Chrome扩展开发完全指南

    • 

  • 
三、数据结构

    • 

  • 
【vue eslint】报错：VSCode自动保存格式化与ESLint规则冲突

    • 

  • 
Linux 正则表达式与grep命令

    • 

  • 
【Excel】将一个单元格内​​的多行文本，​​拆分成多个单元格，每个单元格一行​​

    • 

  • 
ApiFox的使用

    • 

  • 
AP生物课程：全面解析与优质培训机构推荐

    • 

  • 
力扣每日一刷Day 19

    •