MySQL 索引:创建、添加和删除教程

⚡ 智能摘要

MySQL 索引教程讲解了索引如何快速排序和定位数据。索引是基于一个或多个列创建的排序查找结构;CREATE INDEX 用于添加索引,SHOW INDEXES 用于检查索引,而 DROP INDEX 用于在写入密集型表的读写优势大于写入优势时删除索引。

  • 📚 将索引视为字典: 它们对列值进行排序,以便引擎无需扫描整个表即可找到行。
  • 🛠️ 在表格中创建或之后创建: 在 CREATE TABLE 语句中直接定义索引,或者稍后在活动表上使用 CREATE INDEX 语句添加索引。
  • 🔍 使用 SHOW INDEXES 命令检查: 使用 SHOW INDEXES FROM table_name 列出每个索引、键部分、基数和唯一性标志。
  • 🧹 写入成本过高时丢弃: 索引会降低 INSERT 和 UPDATE 操作的速度——使用 DROP INDEX 删除未使用的索引以恢复写入吞吐量。
  • 🤖 利用人工智能进行指数设计: AI 助手读取慢查询日志,建议复合索引的列顺序,并逐行解释 EXPLAIN 计划。

MySQL 索引概念

什么是 MySQL 指数?

An 指数 in MySQL 索引是一种数据结构,它以有序的方式存储列值,以便引擎能够快速查找行。索引创建在最常用于筛选数据的列上。可以将索引想象成一个按字母顺序排序的列表:在有序列表中查找名称比在未排序的列表中找到名称要快得多。

索引的使用存在权衡——每次 INSERT 或 UPDATE 操作都需要维护索引,因此在写入频繁的表上添加过多索引会降低整体性能。一般来说,对于读取频率高于写入频率的表,应为 WHERE、JOIN 和 ORDER BY 子句中出现的列创建索引。

为什么要使用索引?

没人喜欢运行缓慢的系统。高性能几乎是所有基于数据库的应用程序的首要关注点。企业在硬件上投入巨资以保证查询速度,但硬件本身的性能提升终究有限。优化索引是一种更经济有效的方法。

MySQL 索引概念

响应速度慢通常是由于磁盘上的数据行按物理顺序存储造成的。如果没有索引, MySQL 必须扫描每一行才能找到与谓词匹配的行——即“全表扫描”。索引允许这样做。 MySQL 直接跳转到匹配的行,这使得 B 树查找的查询计划从 O(n) 变为大致 O(log n)。

语法:创建索引

索引可以在两个地方定义:

  1. 在创建表格时。
  2. 表格已存在。

示例:在 CREATE TABLE 语句中直接创建索引

如报名参加 myflixdb 数据库中,我们预计会对“全名”列进行大量搜索。以下脚本将创建一个新的 members_indexed 带有索引的表 full_names 列。

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

执行脚本 MySQL 工作台对抗 myflixdb 数据库。

members_indexed 表中 MySQL 工作台

刷新 myflixdb 去看看新的 members_indexed 桌子。 这 full_names 列现在显示在下方 指数 节点。

随着会员人数的增长,对以下内容的搜索查询也随之增加: members_indexed 使用 WHERE 和 ORDER BY 条件 full_names 比在原始服务器上执行相同的查询要快得多。 members 没有索引的表。

在表已存在之后添加索引

您经常会发现现有表需要索引——搜索查询速度很慢,并且 EXPLAIN 执行计划显示对 WHERE 子句中出现的列进行了全表扫描。 CREATE INDEX 该语句添加索引,而无需重新创建表。

CREATE INDEX `id_index` ON `table_name` (`column_name`);

具体例子——加快搜索速度 title 的专栏 movies 表:

CREATE INDEX `title_index` ON `movies` (`title`);

每个筛选条件的查询 movies.title 现在已启用新索引。除非查询本身拥有索引,否则基于其他列进行筛选的查询仍会扫描表。

注意: 当你的查询始终基于相同的列组合进行筛选或排序时,你可以创建跨多个列的复合索引。顺序很重要——首列决定了索引是否可用。

列出表上的索引

绝大部分储备使用 SHOW INDEXES 查看表中定义的每个索引。

SHOW INDEXES FROM `table_name`;

示例——列出索引 movies 表:

SHOW INDEXES FROM `movies`;

运行该语句 MySQL 工作台 驳 myflixdb 查看现有索引及其涵盖的列。

注意: 主键和外键会自动建立索引 MySQL每个索引都有一个唯一的名称,并列出它涵盖的列。

语法:删除索引

绝大部分储备使用 DROP INDEX 从表中删除现有索引。当写入密集型表因读取端不再发挥作用的索引而降低速度时,此操作非常有用。

DROP INDEX `index_id` ON `table_name`;

具体例子——放弃 full_names 索引 members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

有哪些 MySQL 指数

MySQL 支持多种索引类型,每种类型都适用于不同的工作负载。

类型 目的
主键 唯一行标识符;与 InnoDB 中的表数据进行聚类。
独特性 确保唯一性,同时充当索引。
索引(B树) 用于范围查询和相等性查找的默认二级索引。
全文 针对自然语言文本搜索进行了优化,支持 MATCH … AGAINST。
空间的 用于 GIS 数据类型(例如 POINT 和 POLYGON)的 R 树索引。
HASH 恒定时间相等性查找;由 MEMORY 存储引擎使用。
复合(多列) 将多列合并为一个索引;遵循最左侧前缀规则。

最佳实践 MySQL 指数

以下习惯有助于保持指数的有效性,防止其沦为无用的负担。

  • 索引的是查询模式,而不是列名: 添加与实际的 WHERE、JOIN 和 ORDER BY 子句相匹配的索引,而不是“每个听起来重要的列”。
  • 观察综合指数排序: 要使用索引,查询中必须包含首列。
  • 避免重复索引: 复合索引的前导前缀已经涵盖了对该前缀的单列查找。
  • 使用 EXPLAIN 进行检查: 确认规划器确实选择了新的索引。
  • 删除未使用的索引: 使用 sys.schema_unused_indexes in MySQL 5.7+ 查找没有任何内容读取的索引。
  • 匹配数据类型: 如果 WHERE 子句将 VARCHAR 列与数字进行比较,则由于隐式转换,索引无法使用。

常见问题

主键唯一标识每一行数据,并且始终会被建立索引。通用索引可以加快查找速度,但允许存在重复值。每个主键都是一个索引,但并非每个索引都是主键。

避免在非常小的表、列值很少(低基数)的表以及写入频率远高于读取频率的表上创建索引。每个额外的索引都会降低每次 INSERT、UPDATE 和 DELETE 操作的速度。

复合(多列)索引在单个索引中涵盖多个列。它遵循最左侧前缀规则,因此可以处理按第一列、前两列等进行筛选的查询,但不能单独处理按第二列进行筛选的查询。

运行 EXPLAIN 在 SELECT 语句前面。 键 该列显示优化器选择的索引,而 类型 和 行 告诉你这条接入路径是否高效。

覆盖索引包含了查询所需的所有列,因此引擎仅使用索引即可回答查询,而无需读取表。当这种情况发生时,EXPLAIN 会报告“正在使用索引”。

常见原因包括包裹ping 函数中的列(WHERE YEAR(col) = …)、隐式类型转换、极低的基数和过时的统计数据。运行 ANALYZE TABLE 刷新统计数据并检查 EXPLAIN 因为真正的原因。

AI 助手能够读取慢查询日志,识别开销最大的模式,建议创建单列或复合索引,并用通俗易懂的语言解释 EXPLAIN 执行计划。它们可以将常规工作负载的调优时间从数小时缩短到数分钟。

是的。人工智能工具可以将“通过电子邮件和注册日期加快客户搜索速度”之类的请求转化为可执行的 CREATE INDEX 语句,推荐列顺序,并解释对读写吞吐量的预期影响。

总结一下这篇文章: