印刻数据库

外观

MySQL 索引使用分析 ​

索引使用统计 ​

系统状态变量 ​

  • Handler_read_first: 读取索引第一个条目的次数
  • Handler_read_key: 通过索引读取数据的次数
  • Handler_read_last: 读取索引最后一个条目的次数
  • Handler_read_next: 通过索引顺序读取的次数
  • Handler_read_prev: 通过索引反向读取的次数
  • Handler_read_rnd: 随机读取的次数
  • Handler_read_rnd_next: 全表扫描的次数

查看索引使用统计 ​

sql
-- 查看索引使用统计
SHOW GLOBAL STATUS LIKE 'Handler_read%';

-- 查看会话级索引使用统计
SHOW SESSION STATUS LIKE 'Handler_read%';

分析统计结果 ​

  • Handler_read_key 与 Handler_read_rnd_next 的比值越高越好
  • Handler_read_rnd_next 值高表示全表扫描频繁
  • Handler_read_rnd 值高表示随机读取较多

未使用的索引识别 ​

使用 sys schema ​

MySQL 5.7+ 提供了 sys schema，可以方便地识别未使用的索引：

sql
-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看冗余索引
SELECT * FROM sys.schema_redundant_indexes;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;

使用 Performance Schema ​

sql
-- 启用索引使用统计
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE 'statement/%';
UPDATE performance_schema.setup_instruments SET enabled = 'YES' WHERE name LIKE 'stage/%';
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name LIKE '%events%';

-- 查看索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0;

自定义脚本分析 ​

sql
-- 分析索引使用情况的脚本
SELECT 
    t.table_schema,
    t.table_name,
    i.index_name,
    s.key_reads,
    s.key_read_requests,
    s.key_write_requests,
    ROUND(100 * s.key_reads / s.key_read_requests, 2) AS hit_rate
FROM 
    information_schema.tables t
JOIN 
    information_schema.indexes i ON t.table_schema = i.table_schema AND t.table_name = i.table_name
JOIN 
    (SELECT 
        object_schema,
        object_name,
        index_name,
        SUM(key_reads) AS key_reads,
        SUM(key_read_requests) AS key_read_requests,
        SUM(key_write_requests) AS key_write_requests
    FROM 
        performance_schema.table_io_waits_summary_by_index_usage
    GROUP BY 
        object_schema, object_name, index_name
    ) s ON t.table_schema = s.object_schema AND t.table_name = s.object_name AND i.index_name = s.index_name
WHERE 
    t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY 
    hit_rate ASC;

执行计划分析 ​

EXPLAIN 命令 ​

sql
-- 分析单条 SQL 的执行计划
EXPLAIN SELECT * FROM table WHERE id = 1;

-- 分析 INSERT/UPDATE/DELETE 的执行计划
EXPLAIN UPDATE table SET col = 'value' WHERE id = 1;

-- 分析包含子查询的执行计划
EXPLAIN SELECT * FROM table WHERE id IN (SELECT id FROM other_table WHERE condition);

-- 分析 JOIN 查询的执行计划
EXPLAIN SELECT * FROM table1 JOIN table2 ON table1.id = table2.table1_id WHERE condition;

EXPLAIN 输出解读 ​

id 列 ​

  • 执行顺序标识
  • 数字越大，执行优先级越高
  • 相同数字，从上到下执行

select_type 列 ​

  • SIMPLE: 简单查询
  • PRIMARY: 主查询
  • SUBQUERY: 子查询
  • DERIVED: 派生表
  • UNION: UNION 查询
  • UNION RESULT: UNION 结果

table 列 ​

  • 数据来源表
  • <derivedN>: 派生表
  • <unionN,M>: UNION 结果

type 列（重要） ​

  • system: 表只有一行数据
  • const: 使用主键或唯一索引
  • eq_ref: 关联查询中使用主键或唯一索引
  • ref: 使用非唯一索引
  • range: 范围查询
  • index: 全索引扫描
  • ALL: 全表扫描

possible_keys 列 ​

  • 可能使用的索引

key 列 ​

  • 实际使用的索引

key_len 列 ​

  • 使用索引的长度
  • 长度越短越好

ref 列 ​

  • 索引引用的列或常量

rows 列 ​

  • 估计扫描的行数
  • 越少越好

filtered 列 ​

  • 过滤后的行数百分比
  • 越高越好

Extra 列 ​

  • 额外信息
  • Using index: 使用覆盖索引
  • Using where: 使用 WHERE 条件过滤
  • Using index condition: 使用索引条件推送
  • Using temporary: 使用临时表
  • Using filesort: 使用文件排序

索引选择分析 ​

索引选择问题 ​

  • MySQL 选择了错误的索引
  • 索引统计信息不准确
  • 复杂查询中的索引选择
  • 隐式类型转换导致索引失效

强制使用索引 ​

sql
-- 强制使用指定索引
SELECT * FROM table FORCE INDEX (index_name) WHERE condition;

-- 忽略指定索引
SELECT * FROM table IGNORE INDEX (index_name) WHERE condition;

-- 使用索引提示
SELECT * FROM table USE INDEX (index_name1, index_name2) WHERE condition;

索引统计信息更新 ​

sql
-- 更新表统计信息
ANALYZE TABLE table_name;

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'table_name';

-- 查看索引统计信息
SELECT * FROM information_schema.statistics WHERE table_schema = 'dbname' AND table_name = 'table_name';

索引使用效率分析 ​

索引覆盖 ​

  • 定义：查询的所有列都包含在索引中
  • 优点：不需要回表查询，提高查询速度
  • 识别：执行计划中出现 Using index

索引条件推送 ​

  • 定义：将 WHERE 条件下推到存储引擎层
  • 优点：减少回表次数，提高查询效率
  • 识别：执行计划中出现 Using index condition

索引下推限制 ​

  • 只适用于 MyISAM 和 InnoDB 存储引擎
  • 只适用于二级索引
  • 某些条件无法下推（如子查询）

索引跳跃扫描 ​

  • MySQL 8.0 引入的特性
  • 适用于复合索引的第一个列有多个重复值的情况
  • 提高复合索引的使用效率

慢查询中的索引分析 ​

分析慢查询日志 ​

bash
-- 使用 mysqldumpslow 分析
mysqldumpslow -s t /var/log/mysql/slow-query.log

-- 使用 pt-query-digest 分析
pt-query-digest /var/log/mysql/slow-query.log

慢查询中的索引问题 ​

  • 全表扫描
  • 索引失效
  • 索引选择不当
  • 复杂查询缺少合适索引

示例分析 ​

sql
-- 慢查询示例
# Time: 2023-01-01T00:00:00.000000Z
# User@Host: user[user] @ localhost []  Id: 1
# Query_time: 10.000000  Lock_time: 0.000000 Rows_sent: 10  Rows_examined: 1000000
SELECT * FROM large_table WHERE created_at > '2023-01-01' AND status = 'active';

-- 分析：缺少复合索引 (created_at, status)
-- 优化：CREATE INDEX idx_created_at_status ON large_table(created_at, status);

索引使用监控 ​

开启索引监控 ​

sql
-- 开启表级索引监控
ALTER TABLE table_name ENABLE KEYS;

-- 开启会话级索引监控
SET SESSION optimizer_trace = 'enabled=on';

-- 查看优化器跟踪
SELECT * FROM information_schema.optimizer_trace;

监控工具 ​

  • MySQL Enterprise Monitor: 提供索引使用监控
  • Percona Monitoring and Management (PMM): 监控索引使用情况
  • Zabbix: 自定义监控项监控索引使用
  • Prometheus + Grafana: 监控索引相关指标

监控指标 ​

  • 索引使用率
  • 未使用索引比例
  • 索引扫描 vs 全表扫描
  • 索引维护成本

索引使用最佳实践 ​

创建合适的索引 ​

  • 为 WHERE 子句中的列创建索引
  • 为 JOIN 条件中的列创建索引
  • 为 ORDER BY 和 GROUP BY 中的列创建索引
  • 考虑创建复合索引

避免索引失效 ​

  • 避免在索引列上使用函数
  • 避免在索引列上进行运算
  • 避免使用 !=、<> 操作符
  • 避免使用 IS NULL、IS NOT NULL
  • 避免使用 LIKE '%...' 前缀模糊查询
  • 注意隐式类型转换

索引维护 ​

  • 定期分析索引使用情况
  • 删除未使用的索引
  • 优化冗余索引
  • 更新统计信息

复杂查询优化 ​

  • 分解复杂查询
  • 使用覆盖索引
  • 考虑使用临时表
  • 优化子查询

不同版本的索引特性 ​

MySQL 5.6 ​

  • 引入索引条件推送
  • 增强执行计划
  • 改进索引统计信息

MySQL 5.7 ​

  • 引入 sys schema
  • 增强性能模式
  • 改进索引选择算法

MySQL 8.0 ​

  • 引入索引跳跃扫描
  • 增强直方图统计
  • 改进索引使用监控
  • 支持降序索引

MariaDB ​

  • 增强索引统计
  • 提供更多索引类型
  • 改进索引使用分析工具

索引使用分析工具 ​

pt-index-usage ​

  • Percona Toolkit 工具
  • 分析慢查询日志中的索引使用情况
  • 识别未使用的索引

使用示例 ​

bash
# 分析慢查询日志中的索引使用
pt-index-usage /var/log/mysql/slow-query.log --database dbname

# 分析特定查询的索引使用
pt-index-usage --query "SELECT * FROM table WHERE condition" --database dbname

MySQL Workbench ​

  • 提供可视化执行计划
  • 索引使用分析
  • 性能建议

其他工具 ​

  • SQLyog: 提供索引分析功能
  • Navicat: 可视化索引管理
  • DBeaver: 执行计划分析

常见问题（FAQ） ​

Q1: 如何判断 MySQL 是否使用了索引？ ​

A1: 可以通过以下方法判断：

  • 使用 EXPLAIN 命令查看执行计划
  • 查看 key 列是否显示了索引名称
  • 查看 type 列是否为 const、eq_ref、ref、range 等
  • 查看 Extra 列是否有 Using index 或 Using index condition

Q2: 为什么 MySQL 选择了错误的索引？ ​

A2: 可能的原因包括：

  • 索引统计信息不准确
  • 查询条件复杂，MySQL 优化器判断失误
  • 数据分布不均匀
  • 隐式类型转换
  • 索引选择性差

Q3: 如何处理未使用的索引？ ​

A3: 处理未使用的索引时应谨慎：

  • 确认索引确实未被使用（观察足够长的时间）
  • 考虑索引的维护成本
  • 评估删除索引的影响
  • 先在测试环境删除，观察性能变化
  • 生产环境中可以先禁用索引，再决定是否删除

Q4: 如何优化复合索引的顺序？ ​

A4: 复合索引的顺序应考虑：

  • 选择性高的列放在前面
  • 经常用于等值查询的列放在前面
  • 考虑查询覆盖
  • 遵循最左前缀原则
  • 分析实际查询模式

Q5: 索引越多越好吗？ ​

A5: 不是，索引过多会带来以下问题：

  • 增加写操作的成本
  • 增加存储空间
  • 增加索引维护成本
  • 可能导致 MySQL 选择错误的索引
  • 降低插入、更新、删除操作的性能