在设计用户表的索引时，需要结合实际查询场景（即 “不同维度的查询条件”）来决定，核心原则是：让高频查询、过滤性强的字段优先被索引覆盖，同时避免冗余索引（索引会占用空间，且影响插入 / 更新效率）。

下面以一个典型的用户表为例，说明如何根据查询维度设计索引。

示例场景

假设用户表 user 的结构如下（常见字段）：

sql

CREATE TABLE user (
  id BIGINT PRIMARY KEY,  -- 主键（自增ID）
  username VARCHAR(50) NOT NULL,  -- 用户名（唯一）
  phone VARCHAR(20) NOT NULL,  -- 手机号（唯一）
  email VARCHAR(100),  -- 邮箱
  gender TINYINT,  -- 性别（1-男，2-女）
  age INT,  -- 年龄
  register_time DATETIME,  -- 注册时间
  status TINYINT  -- 账号状态（0-禁用，1-正常）
);

常见查询维度（假设这些是高频场景）：

1. 按 username 查询（如登录时根据用户名查用户）；
2. 按 phone 查询（如根据手机号找回密码）；
3. 按 register_time 范围查询（如查 “近 7 天注册的用户”）；
4. 按 status + age 组合查询（如查 “状态正常且年龄> 18 的用户”）。

索引设计方案

1. 针对 “单一字段查询”：建单列索引

• username 和 phone：这两个字段一般是唯一的（业务上不允许重复），且查询频率极高（如登录、找回密码），适合建 唯一索引（唯一索引比普通索引更高效，且能保证数据唯一性）。

CREATE UNIQUE INDEX idx_user_username ON user(username); 
CREATE UNIQUE INDEX idx_user_phone ON user(phone);

• 效果：查询 WHERE username = '张三' 或 WHERE phone = '13800138000' 时，能直接通过索引快速定位，避免全表扫描。
• email：如果有根据邮箱查询的场景（如 “通过邮箱登录”），但频率低于手机号 / 用户名，可建普通单列索引：

CREATE INDEX idx_user_email ON user(email);

2. 针对 “范围查询”：建单列索引（范围字段放索引末尾）

• register_time：范围查询（如 BETWEEN … AND …、>, <）是高频场景，单列索引即可高效支持：

CREATE INDEX idx_user_register_time ON user(register_time);

• 效果：查询 WHERE register_time > '2023-01-01' 时，利用 B+ 树的叶子节点链表，可快速定位范围起始点并遍历，效率远高于全表扫描。

3. 针对 “多字段组合查询”：建联合索引（遵循 “最左前缀原则”）

• status + age 组合查询：如 WHERE status = 1 AND age > 18，需要建联合索引。
• 联合索引的字段顺序很重大：过滤性强（区分度高）的字段放前面。
• 假设 status 只有 2 个值（0/1），过滤性弱；age 有多个可能值，过滤性强。但此处 status 是 “等值查询”，age 是 “范围查询”，根据规则：等值字段放前面，范围字段放后面。
• 因此建联合索引：

CREATE INDEX idx_user_status_age ON user(status, age);

• 效果：查询时先通过 status = 1 过滤出部分数据，再在结果中按 age > 18 筛选，充分利用索引，避免全表扫描。

4. 避免冗余索引

• 主键 id 已默认建立聚簇索引，无需重复建索引。
• 若有查询 WHERE status = 1（单独查状态），上面的联合索引 (status, age) 可覆盖此场景（由于最左前缀 status 有效），无需再为 status 建单列索引。
• 若某字段查询频率极低（如 gender，可能仅用于统计），无需建索引（建索引的维护成本高于查询收益）。

最终索引总结

-- 唯一索引（高频唯一查询）
CREATE UNIQUE INDEX idx_user_username ON user(username);
CREATE UNIQUE INDEX idx_user_phone ON user(phone);

-- 普通单列索引（高频单一/范围查询）
CREATE INDEX idx_user_email ON user(email);
CREATE INDEX idx_user_register_time ON user(register_time);

-- 联合索引（高频多字段组合查询）
CREATE INDEX idx_user_status_age ON user(status, age);

核心原则回顾

1. 高频优先：优先为查询频率高的字段建索引；
2. 区分度优先：区分度高（值分布分散）的字段放联合索引前面；
3. 最左前缀：联合索引能匹配 “最左前缀” 的查询（如 (a,b) 可匹配 a 或 a AND b，但不能匹配 b）；
4. 避免冗余：若某索引已被另一索引覆盖（如 (a,b) 覆盖 a），则无需重复建索引；
5. 控制数量：索引不是越多越好，过多会降低插入 / 更新效率（每次写操作需同步更新索引）。

根据实际业务的查询场景调整索引，才能达到最优性能。