索引优化建议与工具

索引优化建议与工具

索引是 MySQL 性能优化最重要的手段，但索引不是越多越好：每个索引都会增加写操作开销和存储空间。本章系统讲解如何科学地设计、评估和管理索引。

索引选择性

选择性（Selectivity）= 不重复值数量 / 总行数，范围 (0, 1]。选择性越接近 1，索引区分度越高，效果越好。

-- 计算列的选择性
SELECT
    COUNT(DISTINCT status) / COUNT(*)         AS status_selectivity,     -- 可能 0.001 (差)
    COUNT(DISTINCT user_id) / COUNT(*)        AS user_id_selectivity,    -- 可能 0.9   (好)
    COUNT(DISTINCT order_no) / COUNT(*)       AS order_no_selectivity,   -- 接近 1.0   (唯一)
    COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS email_prefix_selectivity -- 前缀索引评估
FROM orders;

-- 一般认为 selectivity > 0.1 才值得单独建索引
-- status 等低选择性列不适合单独建索引，但可以放复合索引末位

前缀索引长度选择

-- 评估不同前缀长度的选择性
SELECT
    COUNT(DISTINCT LEFT(email, 5))  / COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(email, 8))  / COUNT(*) AS sel8,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel10,
    COUNT(DISTINCT email)           / COUNT(*) AS sel_full
FROM users;
-- sel5=0.72, sel8=0.92, sel10=0.98, sel_full=1.0
-- 使用 8 个字符的前缀索引（0.92 接近完整列，空间减少约 60%）

CREATE INDEX idx_email_prefix ON users (email(8));

复合索引设计原则

最左前缀原则

-- 假设索引: INDEX idx_a (a, b, c)
-- ✅ 能使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 5          -- a 精确匹配，b 范围查询
WHERE a = 1 ORDER BY b         -- a 精确，b 排序

-- ❌ 不能使用索引（跳过了前缀）
WHERE b = 2                    -- 没有 a
WHERE b = 2 AND c = 3          -- 没有 a
WHERE a = 1 AND c = 3          -- 跳过了 b（c 无法使用）

列顺序设计原则

实战案例：订单列表查询

-- 查询: 用户订单列表，按状态过滤，时间倒序
SELECT id, order_no, total_amount, status, created_at
FROM orders
WHERE user_id = 12345 AND status IN (20, 30)
ORDER BY created_at DESC
LIMIT 20;

-- 索引设计 1: (user_id, status, created_at)
-- - user_id 等值 → 快速定位
-- - status IN(20,30) → 范围，但只有 2 个值
-- - created_at 排序 → 可能仍需 filesort（因为 status 不是等值）

-- 索引设计 2: (user_id, created_at)
-- - 适合不带 status 过滤的场景

-- 最终方案: 根据实际 status 查询频率，若 status 高频过滤:
CREATE INDEX idx_user_status_time ON orders (user_id, status, created_at);
-- 若不带 status 过滤也常见:
CREATE INDEX idx_user_time ON orders (user_id, created_at);

覆盖索引设计

覆盖索引（Covering Index）让查询只需访问索引，不需要回表读取数据行，是减少 I/O 最有效的手段。

-- 原始查询（需回表）
EXPLAIN SELECT order_no, total_amount, status
FROM orders WHERE user_id = 100 AND status = 20;
-- type=ref, Extra=NULL（需回表读 order_no, total_amount）

-- 添加覆盖索引（把 SELECT 的列也加进索引）
ALTER TABLE orders ADD INDEX idx_cover
    (user_id, status, order_no, total_amount);

-- 再次 EXPLAIN
-- Extra: Using index  ← 覆盖索引，不回表！

-- 注意：覆盖索引的列宽不能太大，否则索引文件过大
-- VARCHAR(500) 等宽列不应放入覆盖索引

不可见索引（MySQL 8.0+）

不可见索引（Invisible Index）是 MySQL 8.0 的安全删除索引利器：把索引设为不可见，观察业务影响，确认无问题后再删除，零风险。

-- 创建时设为不可见
CREATE INDEX idx_test ON orders (remark) INVISIBLE;

-- 把现有索引设为不可见（测试删除索引的影响）
ALTER TABLE orders ALTER INDEX idx_old_index INVISIBLE;

-- 验证：EXPLAIN 中该索引不会出现在 key 列
-- 如果业务无影响，再真正删除
DROP INDEX idx_old_index ON orders;

-- 紧急恢复：发现问题立刻重新可见
ALTER TABLE orders ALTER INDEX idx_old_index VISIBLE;

-- 查看表上的不可见索引
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

函数索引（MySQL 8.0.13+）

-- 对函数表达式建索引，解决函数调用导致索引失效问题
ALTER TABLE users ADD INDEX idx_lower_email ((LOWER(email)));

-- 查询时使用相同表达式
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- type=ref, Extra=Using index condition  ← 使用了函数索引

-- 常用场景
ALTER TABLE orders ADD INDEX idx_year_month
    ((YEAR(created_at)), (MONTH(created_at)));

-- JSON 字段上建索引
ALTER TABLE products ADD INDEX idx_json_price
    ((CAST(attrs->>'$.price' AS DECIMAL(10,2))));

找出无用索引

无用索引浪费存储空间、拖慢写入，应定期清理。

-- 方法 1: performance_schema 查找未使用的索引（需运行足够长时间）
SELECT
    object_schema    AS db,
    object_name      AS tbl,
    index_name,
    count_read,
    count_fetch
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
  AND index_name IS NOT NULL
  AND count_star = 0  -- 从未被访问过
ORDER BY object_schema, object_name;

-- 方法 2: sys schema（更友好的视图）
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'mydb';

-- 方法 3: pt-duplicate-key-checker（找重复索引）
pt-duplicate-key-checker --host=localhost --user=root --password=pass --database=mydb

-- 常见重复索引场景
INDEX (a)          ← 被 INDEX (a, b) 包含，可以删除
INDEX (a, b)
UNIQUE KEY (id)    ← 主键已经是唯一索引，显式 UNIQUE 是重复的
PRIMARY KEY (id)

sys Schema 索引诊断

-- 找出全表扫描最多的查询（需要加索引）
SELECT * FROM sys.statements_with_full_table_scans
WHERE db = 'mydb'
ORDER BY no_index_used_count DESC
LIMIT 10;

-- 找出没有被使用的索引
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- 找出重复/冗余的索引
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'mydb';

-- 查看索引统计信息（基数）
SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

第三方索引建议工具

# pt-index-usage: 分析哪些索引被实际使用
pt-index-usage --host=localhost --user=root --password=pass \
  --database=mydb /var/log/mysql/mysql-slow.log

# 输出: 每个索引的使用次数，0 次的可以考虑删除

索引决策树

新查询是否很慢？
├── 是 → EXPLAIN 查看 type
│         ├── ALL (全表扫描) → 添加 WHERE 条件列的索引
│         ├── range/ref 但 rows 很大 → 调整复合索引列顺序
│         └── 有索引但 Extra=Using filesort → 把 ORDER BY 列加入索引
├── 否 → 检查是否有重复索引 (pt-duplicate-key-checker)
│         └── 清理未使用的索引 (sys.schema_unused_indexes)

添加索引前检查:
1. 该列的选择性 > 0.1 ?
2. 是否与已有索引重复 ?
3. 写入 TPS 是否很高？（高频写入表慎加索引）
4. 能否改写 SQL 使用已有索引？

索引管理规范：每张表索引数量建议不超过 5-8 个；索引命名规范（如 idx_{列名} 或 uk_{列名}）；添加索引前用 pt-osc 或 gh-ost 在线添加，避免锁表；每季度用 sys.schema_unused_indexes 清理一次无用索引。