MySQL 索引优化：结构、最左前缀与执行计划分析

CREATE TABLE orders (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    status      TINYINT NOT NULL COMMENT '0待支付 1已支付 2已发货 3已完成',
    amount      DECIMAL(12,2) NOT NULL,
    created_at  DATETIME NOT NULL,
    note        VARCHAR(255) DEFAULT NULL,
    KEY idx_user_status_created (user_id, status, created_at)
) ENGINE=InnoDB;

-- 查询一：走二级索引，拿到 (user_id, status, created_at, id)
SELECT * FROM orders WHERE user_id = 42;
-- 拿到的只有索引列和主键 id，要得到 amount、note，还要拿 id 回聚簇树再查一遍：回表
-- 若命中 1000 行，就是 1000 次回表（页大多在缓冲池里时不算贵，但仍是放大系数）

-- 查询二：只查索引里已有的列
SELECT id, status, created_at FROM orders WHERE user_id = 42;
-- 索引树里全都有，不用回表：覆盖索引，EXPLAIN 的 Extra 会显示 Using index

-- 命中：从 user_id 开始连续
WHERE user_id = 42                                    -- 用 1 列
WHERE user_id = 42 AND status = 1                    -- 用 2 列
WHERE user_id = 42 AND status = 1 AND created_at > '2026-10-01'  -- 3 列（created_at 走范围）

-- 不完整但部分命中
WHERE user_id = 42 AND created_at > '2026-10-01'     -- 只稳定用 user_id 一列

-- 不命中：缺最左列
WHERE status = 1                                     -- 无法使用该索引
WHERE created_at > '2026-10-01'                      -- 无法使用该索引

-- 索引 (user_id, status, created_at)
SELECT * FROM orders
WHERE user_id = 42 AND created_at > '2026-10-01 00:00:00';
-- 无 ICP：server 层先用 user_id=42 定位，逐行回表读整行，再由 server 判断 created_at
-- 有 ICP：在索引树上就用 created_at 过滤，不满足的行直接跳过，回表次数大幅减少

-- 列表页只需要这三列 + 主键 id（索引里天然有）
SELECT id, status, created_at
FROM orders
WHERE user_id = 42 AND status = 1
ORDER BY created_at DESC
LIMIT 20;
-- 完美命中 idx_user_status_created：过滤、排序、取数全在索引树完成，零回表

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 1\G

           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ref
possible_keys: idx_user_status_created
          key: idx_user_status_created
      key_len: 9
          ref: const,const
         rows: 87
     filtered: 100.00
        Extra: NULL

-- 失效一：对索引列用函数
WHERE YEAR(created_at) = 2026            -- 索引失效
-- 改写为可走索引的范围
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- 失效二：隐式类型转换
WHERE user_id = '42'                     -- 数字列给字符串，实际是数字列，等值常量被转换成数字，可以走索引
WHERE note = 42                          -- 字符串列给数字，是把列往数字转，函数作用在列上，索引失效
-- 习惯：常量类型与列类型严格一致，SQL 评审里专查这一条

-- 失效三：前导通配符
WHERE note LIKE '%关键词%'                -- 无法走普通索引
WHERE note LIKE '关键词%'                 -- 可以走索引（前缀确定）

-- 失效四：or 陷阱
WHERE user_id = 42 OR note = 'abc'       -- note 无索引时，整条退化为全表扫描
-- 两个条件都有索引时可用 index merge，但稳定性不如拆成两条 UNION 查询

-- 命中：ORDER BY 与联合索引 (user_id, status, created_at) 顺序一致
SELECT * FROM orders WHERE user_id = 42 AND status = 1 ORDER BY created_at DESC LIMIT 20;

-- 失效：ORDER BY 跳过了 status
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC;
-- filesort：排序列不在过滤条件的连续前缀之后

-- 失效：ORDER BY 方向与索引序不一致（同为升序或同为降序才可以）
SELECT * FROM orders WHERE user_id = 42 AND status = 1 ORDER BY created_at ASC, status DESC;

-- 问题形态：越翻越慢
SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 100000, 20;
-- 要扫描并丢弃前 100000 行（回表或索引跳读都在做无用功），第 1000 页延迟成秒级

-- 游标分页：以唯一序（主键兜底）为游标
SELECT * FROM orders
WHERE user_id = 42
  AND (created_at, id) < ('2026-10-20 12:00:00', 998877)
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- 命中索引直接从游标位置向后读 20 行，延迟与页码无关

-- 延迟关联：子查询在索引内完成 offset 丢弃，回表只发生 20 次
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders
    WHERE user_id = 42
    ORDER BY created_at DESC
    LIMIT 100000, 20
) t ON o.id = t.id;

-- 单个索引的统计
SHOW INDEX FROM orders;

-- 更全的统计（含 data_length / index_length 的库表占用）
SELECT * FROM information_schema.TABLES
WHERE table_schema = 'example_db' AND table_name = 'orders';