MySQL 8.0 索引使用监控深度解析：对标Oracle的替代方案
简介：MySQL通过Performance Schema 监控索引，查询索引使用统计
一、背景与需求分析
当Oracle用户迁移到MySQL时，常提出的问题是：如何实现类似ALTER INDEX ... MONITORING USAGE的索引监控能力？本文将通过MySQL 8.0的性能监控体系，完整解析索引使用追踪的解决方案。
二、核心监控方案对比
Oracle索引监控机制
- 监控指令：ALTER INDEX index_name MONITORING USAGE
- 查看方式：V$OBJECT_USAGE视图（普通用户）/DBA_OBJECT_USAGE视图（SYSDBA）
- 特点：精准记录索引使用状态，数据持久化存储
MySQL替代方案
- 核心原理：基于性能模式（Performance Schema）的实时I/O统计
- 数据特征：非持久化统计，重启后清零
- 监控维度：索引的读/写/检索次数、联合索引使用分析
三、MySQL实施全流程
3.1 环境准备
-- 验证性能模式状态
SHOW VARIABLES LIKE 'performance_schema';
-- 若未启用需在my.cnf配置
[mysqld]
performance_schema=ON3.2 核心监控表解析
performance_schema.table_io_waits_summary_by_index_usage 关键字段：
| 字段名 | 数据类型 | 描述 |
| OBJECT_SCHEMA | varchar | 数据库名 |
| OBJECT_NAME | varchar | 表名 |
| INDEX_NAME | varchar | 索引名（NULL表示全表扫描） |
| COUNT_READ | bigint | 索引读取次数 |
| COUNT_WRITE | bigint | 索引写入次数 |
| COUNT_FETCH | bigint | 索引数据检索次数 |
3.3 实战监控脚本
-- 基础监控查询
SELECT
OBJECT_SCHEMA AS `数据库`,
OBJECT_NAME AS `表名`,
INDEX_NAME AS `索引`,
COUNT_READ + COUNT_WRITE AS `总操作量`,
COUNT_READ AS `读取次数`,
COUNT_WRITE AS `写入次数`,
COUNT_STAR AS `总触发次数`
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
INDEX_NAME IS NOT NULL
AND OBJECT_SCHEMA = 'your_database'
ORDER BY
COUNT_READ + COUNT_WRITE DESC
LIMIT 10;
-- 高级联合分析（关联INFORMATION_SCHEMA）
SELECT
s.TABLE_NAME,
s.INDEX_NAME,
GROUP_CONCAT(s.COLUMN_NAME ORDER BY s.SEQ_IN_INDEX) AS `索引列顺序`,
p.COUNT_READ,
p.COUNT_WRITE,
STATS.STAT_VALUE AS `索引基数`
FROM
INFORMATION_SCHEMA.STATISTICS s
JOIN
performance_schema.table_io_waits_summary_by_index_usage p
ON s.TABLE_NAME = p.OBJECT_NAME
AND s.INDEX_NAME = p.INDEX_NAME
JOIN
mysql.innodb_index_stats STATS
ON s.TABLE_NAME = STATS.table_name
AND s.INDEX_NAME = STATS.index_name
WHERE
s.TABLE_SCHEMA = 'your_database'
GROUP BY
s.TABLE_NAME, s.INDEX_NAME;3.4 可视化监控建议
推荐配置Prometheus + Grafana监控体系，抓取指标示例：
- name: mysql_index_usage
metrics_path: /sql
static_configs:
- targets: ['localhost:3306']
params:
query:
- >
SELECT
NOW() as timestamp,
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME,
COUNT_READ,
COUNT_WRITE
FROM
performance_schema.table_io_waits_summary_by_index_usage四、辅助监控手段
4.1 Sys Schema快捷查询
-- 索引使用统计视图
SELECT *
FROM sys.schema_index_statistics
WHERE table_schema = 'your_database';
-- 索引使用效率分析
SELECT
table_name,
index_name,
rows_selected / (rows_read + 1) AS select_efficiency
FROM sys.schema_index_statistics
WHERE table_schema = 'your_database';4.2 慢查询日志联动
-- 启用配置
SET GLOBAL slow_query_log = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL long_query_time = 1;
-- 日志分析示例
pt-index-usage /var/lib/mysql/slow.log五、生产环境注意事项
1. 覆盖索引陷阱
- 当查询使用覆盖索引时，COUNT_FETCH可能不会更新
- 解决方法：结合EXPLAIN验证执行计划
2. 统计周期管理
-- 定期重置统计（建议每日凌晨执行）
CREATE EVENT reset_index_stats
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
TRUNCATE performance_schema.table_io_waits_summary_by_index_usage;3. 隐式主键影响
-- 查看隐藏的GEN_CLUST_INDEX
SHOW VARIABLES LIKE 'show_gipk%';
-- 8.0.30+版本可通过以下设置隐藏
SET PERSIST show_gipk_in_create_table_and_information_schema = OFF;4. 性能影响评估
- 性能模式开启时CPU开销增加约2-5%
- 建议在高并发时段禁用非必要consumer
UPDATE performance_schema.setup_consumers
SET ENABLED = 'NO'
WHERE NAME LIKE '%history%';六、索引优化决策树
graph TD
A[开始监控] --> B{索引使用率>阈值?}
B -->|是| C[保留索引]
B -->|否| D{索引是否唯一?
D -->|是| E[必须保留]
D -->|否| F{数据更新频率
F -->|低频| G[考虑删除]
F -->|高频| H[评估查询模式]
H --> I[是否用于排序/分组?]
I -->|是| C
I -->|否| G
七、典型应用场景
7.1 索引失效检测
-- 长期未使用索引清单
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
s.INDEX_NAME,
s.CARDINALITY,
DATEDIFF(NOW(), t.UPDATE_TIME) AS days_unused
FROM
INFORMATION_SCHEMA.TABLES t
JOIN
INFORMATION_SCHEMA.STATISTICS s
ON t.TABLE_NAME = s.TABLE_NAME
LEFT JOIN
performance_schema.table_io_waits_summary_by_index_usage u
ON s.INDEX_NAME = u.INDEX_NAME
WHERE
u.COUNT_READ IS NULL
AND u.COUNT_WRITE IS NULL
AND t.TABLE_SCHEMA = 'your_db';
有遗漏或者不对的可以在我的公众号留言哦
