MySQL / 系统课时

索引的作用与类型

索引的作用与类型

索引用于帮助 MySQL 更快定位满足条件的数据,但不同索引承担的约束和查询职责并不相同。本课通过一张示例表认识主键索引、唯一索引、普通索引和组合索引,并理解组合索引列顺序为什么会影响查询效果。

学习目标

  • 说明索引为什么能减少查询需要检查的数据量
  • 区分主键索引、唯一索引、普通索引和组合索引
  • 根据常见查询条件判断适合建立哪类索引

索引解决什么问题

没有合适索引时,MySQL 可能需要检查表中的大量记录才能找到目标行。索引会按照索引列维护额外的数据结构,使数据库可以先定位较小的候选范围,再读取对应记录。

索引的主要收益是减少查询需要访问的数据,但它不是免费的:建立索引需要额外磁盘空间,执行 INSERT、UPDATE、DELETE 时也要同步维护索引。因此索引应围绕真实查询条件设计,而不是给每一列都建立一个索引。

下面创建一张用户表,其中同时包含几种常见索引。

sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_username (username),
    KEY idx_users_city_status (city, status)
);

SHOW INDEX FROM users;

预期结果

text
返回 users 表的索引信息,其中可以看到 PRIMARY、uk_users_email、idx_users_username 和 idx_users_city_status。具体 Cardinality 等统计值可能因数据量和统计信息不同而变化。

主键索引与唯一索引

PRIMARY KEY 创建主键索引,用来唯一标识一行数据。一张表只能有一个主键,主键列不能为 NULL。示例中的 id 就承担用户记录的唯一身份标识。

唯一索引使用 UNIQUE KEY 创建,它同样要求索引值不能重复,常用于邮箱、订单号、业务编码等具有唯一性约束的字段。与主键不同,一张表可以存在多个唯一索引。

两者都能加速基于对应列的查询,但设计目的不仅是性能。主键强调行身份,唯一索引则用于保护其他业务字段的唯一性,因此不要为了查询速度随意使用唯一约束。

普通索引用于加速定位

普通索引不要求值唯一,适合经常出现在查询条件、连接条件或排序条件中的列。例如 idx_users_username 可以帮助下面的查询按用户名定位记录。

sql
EXPLAIN SELECT id, email
FROM users
WHERE username = 'alice';

预期结果

text
执行计划会展示该查询的访问方式和候选索引。表中存在有效统计信息且优化器选择 idx_users_username 时,key 列会显示 idx_users_username;实际估算行数可能不同。

普通索引中的重复值是允许的,因此多个用户可以拥有相同的城市、状态等属性。是否值得建立普通索引,要结合字段选择性、查询频率和数据规模判断。

组合索引同时覆盖多列

组合索引把多个列按照确定顺序组成一个索引。例如 idx_users_city_status (city, status) 先按 city 组织,再在相同城市范围内继续按 status 区分。

它通常适合同时使用这些列过滤的查询,例如:

sql
EXPLAIN SELECT id, username
FROM users
WHERE city = 'Seattle'
  AND status = 1;

预期结果

text
如果优化器选择组合索引,执行计划的 key 列会显示 idx_users_city_status。实际访问行数由表中数据分布决定。

组合索引的列顺序非常重要。对 (city, status) 而言,仅按 city 查询通常仍可以利用索引的前导列;如果只按 status 查询,则不能简单认为这个组合索引一定能够高效定位,因为查询跳过了前面的 city。

选择索引类型的判断方式

需要稳定标识每一行时使用主键索引;业务字段必须防止重复时使用唯一索引;只需要提升普通查询定位效率时使用普通索引;多列经常共同参与过滤或排序时,再考虑组合索引。

索引的类型决定了它是否承担唯一性约束,而索引中的列及顺序决定了它能服务哪些查询。建立索引之前先从业务约束和高频 SQL 出发,通常比单纯追求“索引越多越快”更可靠。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

索引的作用与类型

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通