MySQL 索引类型与优化

-- 索引: (user_id, status, created_at)
SELECT user_id, status FROM orders WHERE user_id = 100;
-- ✅ 覆盖索引：所有字段都在索引中，Extra: Using index

SELECT user_id, status, amount FROM orders WHERE user_id = 100;
-- ❌ 需要回表：amount 不在索引中

INDEX idx_abc (a, b, c)

WHERE a = 1                 -- ✅ 走索引（匹配 a）
WHERE a = 1 AND b = 2       -- ✅ 走索引（匹配 a, b）
WHERE a = 1 AND c = 3       -- ✅ 走索引（匹配 a，c 部分可用但 b 断了）
WHERE b = 2                 -- ❌ 不走索引（跳过 a）
WHERE a = 1 AND b > 2 AND c = 3  -- ✅ 走 a,b（范围后的 c 不走）

INDEX idx (name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age = 20;

-- 无 ICP: 存储引擎返回所有 name LIKE '张%' 的行 → Server 层过滤 age=20
-- 有 ICP: 存储引擎直接过滤 name LIKE '张%' AND age=20 → 减少返回的行数

-- 高频查询：用户查自己订单
SELECT id, status, amount, created_at
FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC;

-- 索引: (user_id, status, created_at)
-- 覆盖索引 + 最左前缀 + 避免 filesort

-- 索引: INDEX idx_cover (user_id, status, created_at)
-- 查询 1: 覆盖索引
SELECT user_id, status, created_at FROM orders WHERE user_id = 100;
-- EXPLAIN Extra: Using index
-- 原理：user_id, status, created_at 都在 idx_cover 的叶子节点中
-- 存储引擎扫描 idx_cover 即可直接返回结果，不需要跳转到聚簇索引

-- 查询 2: 非覆盖索引（需要回表）
SELECT user_id, status, created_at, amount FROM orders WHERE user_id = 100;
-- EXPLAIN Extra: NULL 或 Using where
-- 原理：amount 不在 idx_cover 中，必须回表取

优点：
  ✅ 减少 I/O（不回表）
  ✅ 减少 Buffer Pool 污染（只读索引页，不污染数据页）
  ✅ 对分页优化友好

代价：
  ⚠️ 索引变大（更多列 → 更大的索引文件 → 更多磁盘空间）
  ⚠️ 写入变慢（每次 UPDATE/INSERT 需要维护更多索引列）
  ⚠️ 内存占用（Buffer Pool 中索引占更多空间 → 数据页缓存变少）

-- 表 users: id INT PK, name VARCHAR(50), age INT, city VARCHAR(50)
-- 索引: INDEX idx_name_age_city (name, age, city)
-- 字符集 utf8mb4（VARCHAR 1字符=4字节，长度前缀=2字节，NULL标志=1字节）

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25 AND city = '北京';
-- key_len: 203 + 5 + 203 = 411
-- 解读：name 占 50*4+2+1=203, age 占 4+1=5, city 占 50*4+2+1=203

EXPLAIN SELECT * FROM users WHERE name = '张三' AND age = 25;
-- key_len: 208（203+5）
-- 解读：只有 name 和 age 被使用，city 未使用

EXPLAIN SELECT * FROM users WHERE name = '张三' AND city = '北京';
-- key_len: 203
-- 解读：只有 name 被使用——age 被跳过，city 不能走索引查找（只能走 ICP 过滤）

-- 索引: INDEX idx_a_b (a, b)
-- ✅ 不需要 filesort 的查询：
WHERE a = 1 ORDER BY b;           -- a 等值后，b 在索引中有序
ORDER BY a, b;                    -- 索引有序

-- ❌ 需要 filesort 的查询：
WHERE a = 1 ORDER BY b DESC;      -- a 升序，b 降序——索引都是升序，不同向
WHERE a > 1 ORDER BY b;           -- a 是范围，b 无序
ORDER BY b;                       -- 跳过 a，b 无序

-- GROUP BY 同理：索引有序 = 分组有序，不需要临时表（Using temporary）

同一查询中可能同时出现三种优化：

SELECT user_id, status FROM orders
WHERE user_id > 100 AND status = 'paid';
-- 索引: INDEX idx_uid_status (user_id, status)

执行情况：
  type: range（范围扫描）
  Extra: Using index condition; Using where

  1. range: 因为 user_id > 100 是范围 → type=range
  2. ICP: status='paid' 在存储引擎层（索引内部）过滤 → Using index condition
  3. 覆盖索引未完全生效（status 虽在索引中，但 user_id 是范围查询 → 
     部分需要 where 层过滤） → Using where

-- 对 email 列前 8 个字符建索引
CREATE INDEX idx_email_prefix ON users (email(8));

-- 前缀长短的选择：
SELECT
    COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4,
    COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS sel6,
    COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10
FROM users;
-- 选择性越接近 1 越好（但前缀越长索引越大）
-- 通常取选择性达到 ~0.95 时即可

-- 表 orders: INDEX idx_user_id (user_id), INDEX idx_status (status)
-- 查询使用两个索引
SELECT * FROM orders WHERE user_id = 100 OR status = 'paid';
-- Extra: Using union(idx_user_id, idx_status); Using where

-- 三种合并方式：
-- 1. Union（OR 条件）：分别走两个索引，结果集做并集
-- 2. Intersection（AND 条件）：分别走两个索引，结果集做交集
-- 3. Sort-Union（OR 条件但未排序）：分别走索引 → 排序 → 合并

-- 现象：某个查询平时走索引很快，某天突然变慢
-- 原因：统计数据变化 → 优化器换执行计划

-- 排查：
SHOW INDEX FROM orders WHERE Key_name = 'idx_user_status';
-- 对比 Cardinality 是否合理

-- 修复：
ANALYZE TABLE orders;  -- 刷新统计信息

-- ORDER BY + LIMIT 场景：优化器可能误判
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- 优化器可能认为全表扫描 + 排序 优于 索引扫描（如果 created_at 有索引）
-- 但如果是覆盖索引，优化器一般会正确选择索引

-- 大表 JOIN 时：优化器可能选错驱动表
-- 用 STRAIGHT_JOIN 强制指定驱动表顺序
SELECT STRAIGHT_JOIN * FROM orders o
INNER JOIN order_items i ON o.id = i.order_id
WHERE o.user_id = 100;

-- 后台订单搜索：5 个可选筛选条件
SELECT * FROM orders
WHERE (user_id = ? OR ? IS NULL)
  AND (status = ? OR ? IS NULL)
  AND (created_at >= ? OR ? IS NULL)
  AND (amount >= ? OR ? IS NULL)
ORDER BY created_at DESC
LIMIT ?, 20;

-- 问题：可选条件多，组合索引无法覆盖所有情况
-- 方案：
-- 1. 分析访问日志，找到最常见的条件组合
-- 2. 为 top-3 组合建联合索引
-- 3. 配合 ICP 和条件判断优化（动态 SQL 去掉 IS NULL 判断）

-- 更优的 SQL（应用层动态拼接）：
SELECT * FROM orders
WHERE user_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT ?, 20;
-- 索引: INDEX idx_ust (user_id, status, created_at)

-- 需求：按金额排序 TOP 100
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
  AND status = 1
GROUP BY user_id
ORDER BY total DESC
LIMIT 100;

-- 分析：
-- 1. WHERE 过滤：created_at（范围） + status（等值） → 约 10 万行
-- 2. GROUP BY user_id → 需要临时表 + 排序
-- 3. ORDER BY total DESC → 基于聚合结果排序，无法走索引

-- 索引策略：
-- INDEX idx_time_status (created_at, status) → 加速 WHERE 过滤
-- 但对于 GROUP BY + ORDER BY 聚合，纯 SQL 无法完全优化

-- 架构级优化：
-- 1. Redis ZSet 维护排行榜（异步更新）
-- 2. 定时任务预计算 → 汇总表
-- 3. Elasticsearch 做聚合分析

-- 电商商品搜索：按名称模糊匹配
SELECT * FROM products WHERE name LIKE '%手机%';
-- ❌ 全表扫描

-- 方案 1：MySQL 全文索引（FULLTEXT + ngram）
ALTER TABLE products ADD FULLTEXT INDEX ft_name (name) WITH PARSER ngram;
SELECT * FROM products WHERE MATCH(name) AGAINST('手机' IN BOOLEAN MODE);

-- 方案 2：Elasticsearch（推荐）
-- 支持分词、相关性排序、高亮、聚合
-- 通过 Canal/Binlog 同步数据

-- 方案 3：简单前缀搜索（右模糊）
SELECT * FROM products WHERE name LIKE '华为%';
-- ✅ 走 B+ 树索引

-- 如果用 UUID 做主键
CREATE TABLE orders (
  id VARCHAR(36) PRIMARY KEY,  -- UUID
  user_id BIGINT NOT NULL,
  order_no VARCHAR(32) UNIQUE,
  created_at DATETIME NOT NULL
);

-- 问题：
-- 1. 聚簇索引页分裂严重（UUID 随机插入）
-- 2. 二级索引叶子存 UUID（36B）不仅大而且乱序
-- 3. 所有通过二级索引的查询都经历 UUID 回表

-- 优化：
-- 1. 换自增主键（最优）
-- 2. 如果必须用 UUID：用有序 UUID（UUID v7、ULID）
-- 3. 用自增主键 + UUID 做业务唯一键

-- 如果同时需要全局唯一 + 有序：
-- Snowflake 雪花算法（64 位 Long）
-- 优势：数字比较快、趋势递增（减少页分裂）、全局唯一

-- 1. 查看未使用的索引（MySQL 8.0+）
SELECT * FROM sys.schema_unused_indexes;
-- 删除无用索引（减少写入开销）

-- 2. 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;
-- 例如：有 INDEX(a) 和 INDEX(a,b) → 前者可删除（(a,b) 可替代 (a)）

-- 3. 查看索引使用统计
SELECT 
    object_schema, object_name, index_name,
    count_read, count_write,
    rows_selected, rows_inserted, rows_updated, rows_deleted
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'ecommerce'
ORDER BY count_read DESC;

-- 4. 索引大小
SELECT 
    database_name, table_name, index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'ecommerce' AND stat_name = 'size'
ORDER BY stat_value DESC;

索引过多的具体影响：

写入性能：
  一次 INSERT 操作 = 1 次聚簇索引写入 + N 次二级索引写入
  5 个索引 → INSERT 慢 5 倍
  UPDATE 如果修改了索引列 → 同样慢
  DELETE 需要标记删除所有索引中的记录

内存占用：
  Buffer Pool 通常占内存的 75-80%
  索引页占得越多 → 数据页缓存越少 → 回表命中磁盘更多

优化器开销：
  5 个索引 → 优化器要评估 5 条执行路径
  每个路径都需计算代价（统计信息采样、I/O 估算）
  查询编译时间增加

-- 原始 SQL（可能全表扫描）
SELECT * FROM orders WHERE user_id = 100 OR status = 'paid';

-- 方案 1: UNION ALL（推荐，稳定高效）
SELECT * FROM orders WHERE user_id = 100
UNION ALL
SELECT * FROM orders WHERE status = 'paid' AND user_id <> 100;
-- 优点：各自走各自的索引，无合并开销
-- 注意：UNION ALL 不去重（如不关心重复行），比 UNION 快

-- 方案 2: 依赖 index_merge（不太稳定）
SELECT * FROM orders WHERE user_id = 100 OR status = 'paid';
-- MySQL 5.6+ 可能自动走 index_merge
-- 但不保证（取决于统计信息、数据分布）

-- 方案 3: 联合索引（最高效，但需要预知查询模式）
CREATE INDEX idx_uid_status ON orders (user_id, status);
SELECT * FROM orders WHERE user_id = 100 OR (status = 'paid' AND user_id IN (...));
-- 注意：联合索引对跨列的 OR 效果有限

-- 原始分页
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 100000, 20;
-- 扫描 100020 行，丢弃 100000 行

-- 优化 1: 游标分页（推荐）
-- 第一页
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC, id DESC LIMIT 20;
-- 记住最后一行的 (created_at, id) = ('2026-05-15', 98765)
-- 第二页
SELECT * FROM orders 
WHERE user_id = 100 
  AND (created_at < '2026-05-15' OR (created_at = '2026-05-15' AND id < 98765))
ORDER BY created_at DESC, id DESC LIMIT 20;
-- 需要索引 (user_id, created_at, id)

-- 优化 2: 延迟关联
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders WHERE user_id = 100 
    ORDER BY created_at DESC LIMIT 100000, 20
) t ON o.id = t.id;
-- 子查询在覆盖索引 (user_id, created_at, id) 上执行，不回表
-- 拿到 20 个 id 后再 JOIN 回表取完整行

-- 优化 3: 限制最大页码（业务层）
-- 不允许翻到 1000 页之后，强制用户缩小筛选条件

Step 1: 开启慢查询监控
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.1;  -- 100ms 以上的查询记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未走索引也记录

Step 2: 分析慢查询日志
-- 使用 pt-query-digest 分析
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

Step 3: 找到 TOP-N 慢查询，逐条 EXPLAIN
EXPLAIN FORMAT=JSON SELECT ...;

Step 4: 检查是否可以使用覆盖索引、联合索引优化
Step 5: 验证索引效果（EXPLAIN 对比）

-- 慢查询：卖家后台订单列表
SELECT * FROM orders
WHERE shop_id = 12345
  AND created_at >= '2026-06-01'
  AND status IN (1, 2)
ORDER BY created_at DESC
LIMIT 0, 20;

-- 当前执行计划：type=ALL（全表扫描 5000 万行）
-- 问题：无联合索引

-- 添加索引：
ALTER TABLE orders ADD INDEX idx_shop_time_status (shop_id, created_at, status);
-- 添加后：type=range，rows≈500，查询从 3s 降至 50ms

-- 场景：查询用户及其订单数
-- users: 10 万行, orders: 500 万行

-- ❌ 差写法：
SELECT u.name, COUNT(o.id) AS order_cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- 执行计划: 优化器选 users 驱动（10 万行）
-- users 每行去 orders 的 user_id 索引命中 50 行
-- 10 万 × 50 次索引查找 + GROUP BY（需要临时表）

-- ✅ 好写法：先聚合子查询减少 JOIN 数据量
SELECT u.name, IFNULL(t.cnt, 0) AS order_cnt
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY user_id
) t ON u.id = t.user_id;
-- 子查询在 orders.user_id 索引上聚合（500 万行 1 次）
-- JOIN 时只有去重后的 user_id（假设 30 万活跃用户）
-- 而不是原始 500 万行

-- ✅ 更好的写法：如果大多数用户都有订单
SELECT u.name, o.order_cnt
FROM users u
INNER JOIN (
    SELECT user_id, COUNT(*) AS order_cnt
    FROM orders
    GROUP BY user_id
    HAVING order_cnt > 0
) o ON u.id = o.user_id;
-- INNER JOIN 进一步减少结果集

□ 被驱动表的 JOIN 列有索引 (type = ref 或 eq_ref)
□ 用小表驱动大表（能用 INNER JOIN 不用 LEFT JOIN）
□ 子查询替代多表 JOIN（减少中间结果集）
□ WHERE 条件在 JOIN 前过滤（写在子查询中而非外层）
□ 避免在 JOIN 的 ON 条件中使用函数
□ JOIN 不超过 3 表（超过则考虑反范式或应用层合并）
□ 大表 JOIN 先用覆盖索引取主键，再回表

-- 索引: INDEX idx_a_b_c (a, b, c)

-- 场景 1: 覆盖索引 + ICP 同时生效
SELECT a, b, c FROM t WHERE a > 1 AND c = 3;
-- type: range (走 a)
-- Extra: Using index condition; Using where
-- 没有 Using index! 因为 a > 1 是范围，且 SELECT 列虽然全在索引中
-- 但范围查询后索引的有序性被打破，Server 层仍需二次过滤

-- 场景 2: 纯覆盖索引（最优）
SELECT a, b, c FROM t WHERE a = 1 AND b = 2 AND c = 3;
-- type: ref (走 a, b, c)
-- Extra: Using index
-- 查看 key_len 确认三列都用到了

-- 场景 3: ICP 但不覆盖
SELECT * FROM t WHERE a = 1 AND c = 3;
-- type: ref (走 a)
-- Extra: Using index condition
-- a = 1 走索引，c = 3 在 ICP 中过滤
-- 但 SELECT * 需要回表（Using index condition 不是 Using index）

三个 Extra 的关系：
  Using index       → 发生在索引树上，不需要回表（最优）
  Using index condition → 发生在索引树上，减少了回表次数（好）
  Using where       → 发生在 Server 层，需要回表后才能判断（一般）

优先级理解：
  Using index > Using index condition > Using where
  但三者可以不同组合出现！

Cardinality 的估算方式：

  InnoDB 不是 COUNT DISTINCT（太慢!），而是随机采样：
    随机选 N 个索引页 → 统计每页中不同值的个数 → 乘以总页数

  采样数量由 innodb_stats_persistent_sample_pages 控制（默认 20）
  
  如果采样不准：
    - 太小（10 个页）：估算误差大
    - 太大（1000 个页）：ANALYZE 本身变慢

  建议：
    数据量大且分布不均 → innodb_stats_persistent_sample_pages = 100-200

手动设置统计信息（极端情况）：
  SET PERSIST innodb_stats_persistent_sample_pages = 200;
  ANALYZE TABLE orders;

┌─────────────────────────────────────────────────────────────┐
│                    索引优化核心口诀                           │
│                                                              │
│  建索引: WHERE 最左列, ORDER BY 中间列, SELECT 覆盖列         │
│  看计划: type 从好到差, Extra 避免 filesort 和 temporary      │
│  慢查询: 定位 → EXPLAIN → 索引/SQL改写 → 验证                 │
│  深分页: 游标 > 延迟关联 > 限制深度                           │
│  JOIN:   小表驱大表, 被驱动表必建索引                         │
│                                                              │
└─────────────────────────────────────────────────────────────┘