网站首页 收藏本站
广告位联系

APP正在开发中...
返回顶部
您的位置： 主页 > 数据库 > 数据库其他 >

收藏
分享到

mysql覆盖索引的项目实践

数据库其他 来源：互联网 作者：佚名 发布时间：2026-01-11 17:08:32 人浏览
摘要

在 MySQL 中，覆盖索引（Covering Index）是一种非常重要的查询优化技术。它的核心思想是：一个索引包含了查询所需的所有字段，因此 MySQL 可以直接从索引中获取数据，而无需回表（即无需访问

在 MySQL 中，覆盖索引（Covering Index） 是一种非常重要的查询优化技术。它的核心思想是：一个索引包含了查询所需的所有字段，因此 MySQL 可以直接从索引中获取数据，而无需回表（即无需访问主键索引或数据行） 。

???? 一、为什么需要覆盖索引？

1.普通索引查询的流程（非覆盖）

假设有一张用户表：

1

2

3

4

5

6

7

CREATE TABLE users (

    id INT PRIMARY KEY,

    name VARCHAR(100),

    email VARCHAR(100),

    age INT,

    INDEX idx_name (name)

);

执行查询：

1

SELECT email FROM users WHERE name = 'Alice';

???? 执行过程：

  1. 在 idx_name 索引中查找 name = 'Alice' 的记录；
  2. 找到对应的 主键值（id） ；
  3. 回表（回主键索引） ：用主键 id 去聚簇索引（InnoDB 的主键索引）中查找完整的行数据；
  4. 从行数据中提取 email 字段返回。

?? 问题：多了一次“回表”操作，增加了 I/O 和 CPU 开销。

2.覆盖索引的查询流程

如果我们将索引改为：

1

2

-- 创建包含 name 和 email 的联合索引

CREATE INDEX idx_name_email ON users (name, email);

再执行相同查询：

1

SELECT email FROM users WHERE name = 'Alice';

? 执行过程：

  1. 在 idx_name_email 索引中查找 name = 'Alice'；
  2. 直接从索引叶子节点中读取 email 值；
  3. 无需回表！

???? 这就是覆盖索引：查询所需的所有列都包含在索引中。

? 二、覆盖索引的核心优势

优势 说明
减少 I/O 操作 避免回表，少读一次聚簇索引（磁盘或缓冲池）
提升查询速度 尤其对大表、高并发场景效果显著
降低 CPU 消耗 减少数据解析和内存拷贝
利用索引顺序性 覆盖索引常配合 ORDER BY 实现“索引扫描排序”

????? 三、如何判断是否使用了覆盖索引？

使用 EXPLAIN 查看执行计划：

1

EXPLAIN SELECT email FROM users WHERE name = 'Alice';

关键看 Extra 列：

  • 如果显示 Using index → ? 使用了覆盖索引
  • 如果显示 Using where 或 空 → ? 未覆盖，需要回表

? 示例输出：

1

2

id | select_type | table | type | possible_keys     | key             | key_len | ref   | rows | Extra

1  | SIMPLE      | users | ref  | idx_name_email    | idx_name_email  | 303     | const | 1    | Using index

???? 四、覆盖索引的使用条件

  1. SELECT 的所有字段 必须包含在同一个索引中；
  2. WHERE 条件字段 也应尽可能在该索引中（用于快速定位）；
  3. 不能包含未索引的列（如 SELECT * 通常无法覆盖，除非是主键表）；
  4. 适用于 InnoDB 和 MyISAM（但 InnoDB 的聚簇索引结构使其更依赖覆盖索引来避免回表）。

?? 五、注意事项与陷阱

1.不要盲目创建宽索引

  • 索引越大，写入（INSERT/UPDATE）越慢；
  • 占用更多磁盘和内存（Buffer Pool）；
  • 只包含真正需要的字段。

2.主键自动包含在 InnoDB 二级索引中

InnoDB 的二级索引叶子节点存储的是 (索引列, 主键值) 。
所以以下查询也能覆盖：

1

2

3

4

-- 表：users(id PK, name, email)

-- 索引：idx_name(name)

 

SELECT id FROM users WHERE name = 'Alice';  -- ? 覆盖！因为 id 在二级索引中

但：

1

SELECT email FROM users WHERE name = 'Alice';  -- ? 不覆盖！email 不在 idx_name 中

3.函数或表达式会破坏覆盖

1

2

3

-- 即使有 idx_email(email)，以下查询也无法覆盖：

SELECT UPPER(email) FROM users WHERE email = 'a@example.com';

-- 因为需要对 email 计算 UPPER()

???? 六、实战优化示例

场景：高频查询“用户邮箱”

1

2

3

4

5

6

7

-- 优化前（无覆盖）

SELECT email FROM users WHERE status = 1 AND created_at > '2023-01-01';

 

-- 优化：创建覆盖索引

CREATE INDEX idx_status_created_email ON users (status, created_at, email);

 

-- 现在查询完全走覆盖索引！

场景：分页 + 排序

1

2

3

4

5

6

7

8

9

-- 查询最近活跃用户的 ID 和昵称

SELECT id, nickname FROM users

WHERE active = 1

ORDER BY last_login DESC

LIMIT 20;

 

-- 覆盖索引

CREATE INDEX idx_active_login_nickname ON users (active, last_login, nickname);

-- 注意：id 是主键，InnoDB 二级索引自动包含，所以 SELECT id 也能覆盖

? 七、总结

关键点 说明
定义 索引包含查询所需全部字段，无需回表
标志 EXPLAIN 中 Extra = "Using index"
优势 减少 I/O、提升性能、降低负载
适用 高频查询、报表、API 接口等读多场景
禁忌 避免过度索引、注意写性能影响

???? 最佳实践：
对高频查询的 SELECT 字段 + WHERE 字段 + ORDER BY 字段，设计联合覆盖索引，是 MySQL 性能优化的“黄金法则”之一。

如果你有具体的 SQL 查询需要优化，欢迎贴出来，我可以帮你设计覆盖索引！
版权声明 : 本文内容来源于互联网或用户自行发布贡献，该文观点仅代表原作者本人。本站仅提供信息存储空间服务和不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权， 违法违规的内容， 请发送邮件至2530232025#qq.cn(#换@)举报，一经查实，本站将立刻删除。

您可能感兴趣的文章 :

原文链接 :
相关文章
  • 六大主流数据同步工具对比：DataX、Airbyte、Cana
    当数据量变大、数据源复杂、实时需求提高，很多团队在选数据同步工具时犯难。本文对 DataX、Airbyte、Canal、Debezium、Fivetran 与 Apache SeaTun
  • 解读SQL生成工具
    SQL 生成工具可用于测试Parser与其他数据库产品的兼容性，通过解析YACC语法文件中的产生式，生成对应的SQL语句，再使用数据库执行该SQL，根

  • SQLite3在嵌入式C环境中存储音频/视频文件的最优

    SQLite3在嵌入式C环境中存储音频/视频文件的最优
    SQLite3 在嵌入式C环境中存储音频/视频文件的专业方案 在嵌入式系统中存储大型媒体文件需要平衡存储效率、访问速度和资源限制。以下是针

  • 嵌入式数据库SQLite 3配置使用

    嵌入式数据库SQLite 3配置使用
    0、惨痛教训 随着管理开发的项目体积越来越庞大，产品系统涉及的数据量也越来越多，并且伴随着项目不久就要交付给甲方了。如果项目的

  • Sqlite3基本语句及安装过程

    Sqlite3基本语句及安装过程
    SQLite3简介 SQLite3是一款轻量级的、基于文件的开源关系型数据库引擎，由 D. Richard Hipp 于 2000 年首次发布。它遵循 SQL 标准，但与传统的数据
  • 在SQLite中进行批量操作的有效实现方法
    SQLite 是一个轻量级的关系型数据库管理系统，因其高效性和易用性而广受欢迎。在许多应用场景中，批量操作的需求是不可避免的，例如在
  • 一文介绍在Hive中NULL的理解
    在 Hive 中，NULL 是一个特殊的值，表示未知或缺失。任何与NULL的比较操作（如=,,,=,=,）都会返回NULL，而不是TRUE或FALSE。 1.NULL 的比较规则 在

  • Navicat Premium 12数据库管理解决方案

    Navicat Premium 12数据库管理解决方案
    Navicat Premium 12是一款全面的数据库管理工具，支持多种数据库系统如MySQL、MariaDB、Oracle、SQL Server、PostgreSQL等。它提供了多数据库连接、数据
  • sqlite3命令行工具使用介绍
    一、启动与退出 启动数据库连接 1 2 3 sqlite3 [database_file] # 打开/创建数据库文件（如 test.db） sqlite3 # 启动临时内存数据库 (:memory:) sqlite3 :m

  • StarRocks简介与搭建使用介绍

    StarRocks简介与搭建使用介绍
    StarRocks简介 StarRocks 是一款高速、实时、全场景的MPP（大规模并行处理）分析型数据库系统，专为现代数据分析场景设计，强调亚秒级查询性
推荐阅读
最新更新
友情链接
  • 本站所有内容来源于互联网或用户自行发布，本站仅提供信息存储空间服务，不拥有版权，不承担法律责任。如有侵犯您的权益，请您联系站长处理！
  • Copyright © 2017-2022 F11.CN All Rights Reserved. F11站长开发者网 版权所有 | 苏ICP备2022031554号-1 | 51LA统计