6.8.5 索引设计原则与常见模式

索引设计原则与常见模式#

设计目标与核心原则#

  • 高选择性优先：优先为高基数、高区分度字段建立索引，避免为低基数字段（如状态位）单独建索引。
  • 覆盖常用查询：围绕高频查询与核心业务路径设计索引，遵循“查询驱动设计”。
  • 最小化索引数量：索引过多会增加写入成本与维护开销，避免冗余与重复索引。
  • 稳定有序：保证索引字段的稳定性，避免对高频更新字段创建多余索引。
  • 避免过长键：控制索引长度，使用前缀索引或合适的字段类型减少页分裂与I/O开销。

原理草图：查询到索引命中路径#

文章图片

单列索引与联合索引选择#

  • 联合索引优先：当查询条件经常组合出现时，优先使用联合索引替代多个单列索引。
  • 左前缀匹配：联合索引遵循最左匹配原则，条件顺序与索引顺序一致可最大化利用。
  • 排序与分组优化：ORDER BY/GROUP BY 字段与联合索引顺序一致，可避免额外排序。
  • 覆盖索引策略：将查询所需字段尽量纳入索引，减少回表，提高性能。

常见索引模式（含可执行示例）#

1）等值查询模式#

-- 表结构
CREATE TABLE app_user (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL,
  name VARCHAR(64),
  KEY idx_user_status (user_id, status)
) ENGINE=InnoDB;

-- 查询：等值匹配
EXPLAIN SELECT * FROM app_user WHERE user_id=1001 AND status=1;

2）范围查询模式#

-- 索引建议：created_at 或 (status, created_at)
ALTER TABLE app_user ADD KEY idx_status_created (status, created_at);

EXPLAIN
SELECT * FROM app_user
WHERE status=1 AND created_at BETWEEN '2024-01-01' AND '2024-01-31';

3）排序分页模式#

-- ORDER BY 与索引顺序一致
EXPLAIN
SELECT id, user_id, created_at
FROM app_user
WHERE status=1
ORDER BY created_at DESC
LIMIT 20;

4）覆盖查询模式#

-- 覆盖索引：避免回表
ALTER TABLE app_user ADD KEY idx_user_name (user_id, name);

EXPLAIN
SELECT user_id, name
FROM app_user
WHERE user_id=1001;

不推荐与误区（对照示例）#

-- 误区1：低选择性单列索引
ALTER TABLE app_user ADD KEY idx_status (status); -- 通常收益低

-- 误区2：函数导致索引失效
EXPLAIN SELECT * FROM app_user WHERE DATE(created_at)='2024-01-01';
-- 推荐写法（范围查询）
EXPLAIN SELECT * FROM app_user
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 误区3：隐式类型转换
EXPLAIN SELECT * FROM app_user WHERE user_id='1001'; -- user_id为BIGINT

关键命令与索引审计示例#

-- 1) 查看表索引
SHOW INDEX FROM app_user;

-- 2) 分析查询计划
EXPLAIN SELECT * FROM app_user WHERE user_id=1001 AND status=1;

-- 3) 更新统计信息（MySQL 8+）
ANALYZE TABLE app_user;

-- 4) 查看索引使用情况（performance_schema）
SELECT
  OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA='test' AND OBJECT_NAME='app_user';

排错场景与处理#

  • 症状：EXPLAIN 显示 type=ALL，未走索引
  • 可能原因：条件未命中左前缀、字段类型不一致、使用函数或表达式。
  • 处理步骤：
-- 步骤1：确认索引是否存在
SHOW INDEX FROM app_user;

-- 步骤2：检查SQL条件顺序和类型
EXPLAIN SELECT * FROM app_user WHERE status=1 AND user_id=1001;

-- 步骤3：调整索引或SQL
ALTER TABLE app_user DROP INDEX idx_status;
ALTER TABLE app_user ADD KEY idx_user_status (user_id, status);

EXPLAIN SELECT * FROM app_user WHERE user_id=1001 AND status=1;
  • 症状：索引存在但回表过多导致慢
  • 处理：使用覆盖索引减少回表
ALTER TABLE app_user ADD KEY idx_user_status_name (user_id, status, name);

EXPLAIN
SELECT user_id, status, name
FROM app_user
WHERE user_id=1001 AND status=1;

维护与优化建议#

  • 周期性分析：使用 EXPLAIN、ANALYZE TABLE 定期评估索引命中率。
  • 删除冗余索引：识别重复/低频索引，减少维护负担。
  • 业务变更同步调整：查询模式变化时同步更新索引设计，避免性能退化。

练习与实操#

  1. 创建 orders 表，字段包含 order_id、user_id、status、created_at、amount，分别设计索引满足：
    - 用户最近订单查询：WHERE user_id=? ORDER BY created_at DESC LIMIT 20
    - 按时间范围统计已支付订单：WHERE status=1 AND created_at BETWEEN ? AND ?
  2. 使用 EXPLAIN 对比以下两条SQL是否走索引并说明原因：
    - SELECT * FROM orders WHERE user_id='1001';
    - SELECT * FROM orders WHERE user_id=1001;
  3. 通过 performance_schema.table_io_waits_summary_by_index_usage 找出 COUNT_READ=0 的索引并给出删除建议。

索引设计检查清单#

  • 查询条件是否固定且高频
  • 索引字段是否高选择性
  • 联合索引顺序是否符合最左匹配
  • 是否存在冗余或重复索引
  • 是否避免了函数、隐式转换与全表扫描