在mysql的查询优化过程中，索引是提升查询速度的重要手段，但不合理的索引反而会增加存储成本和写入开销，因此需要准确评估SQL语句的索引开销，判断索引是否真正发挥了作用。
通过EXPLAIN分析索引开销
mysql提供的EXPLAIN命令是评估索引开销最基础的工具，它可以展示SQL语句的执行计划，包含索引使用情况、扫描行数、查询成本等核心信息。我们可以在待评估的SQL语句前加上EXPLAIN关键字来执行分析。
以下是一个简单的查询示例，我们先创建一张测试表并插入数据：
-- 创建测试表
CREATE TABLE user_info (
id INT PRIMARY KEY AUTO_INCREMENT,
user_name VARCHAR(50) NOT NULL,
age INT NOT NULL,
register_time DATETIME NOT NULL,
INDEX idx_user_name (user_name),
INDEX idx_age (age)
);
-- 插入测试数据
INSERT INTO user_info (user_name, age, register_time) VALUES
('张三', 25, '2023-01-01 10:00:00'),
('李四', 30, '2023-01-02 11:00:00'),
('王五', 25, '2023-01-03 12:00:00');
接下来我们使用EXPLAIN分析一条查询语句的索引开销：
EXPLAIN SELECT * FROM user_info WHERE user_name = '张三' AND age = 25;
执行后会返回如下关键字段，我们需要重点关注这些字段来判断索引开销：
| 字段名 | 含义 | 评估意义 |
|---|---|---|
| type | 访问类型 | 取值从优到差依次为system、const、eq_ref、ref、range、index、ALL，ALL表示全表扫描，索引开销最高 |
| key | 实际使用的索引 | 如果为NULL说明没有使用索引，需要评估是否需要添加合适索引 |
| rows | 预估扫描行数 | 数值越小说明索引过滤效果越好，索引开销越低 |
| Extra | 额外信息 | 出现Using index表示覆盖索引，无需回表，开销极低；出现Using filesort或Using temporary说明索引无法满足排序或分组需求，需要额外开销 |
通过查询成本指标评估索引开销
除了EXPLAIN的基础信息，mysql还提供了查询成本相关的系统变量，可以更精准地量化索引开销。我们可以通过开启查询成本统计来查看SQL语句的具体成本数值。
首先开启会话级的查询成本统计：
SET SESSION optimizer_trace = "enabled=on";
然后执行需要评估的SQL语句：
SELECT * FROM user_info WHERE user_name = '张三' AND age = 25;
最后查询优化器的追踪信息，获取具体的成本数据：
SELECT * FROM information_schema.OPTIMIZER_TRACE;
在返回的TRACE字段中，我们可以找到query_cost字段，这个数值就是该SQL语句的总查询成本，成本越低说明索引带来的开销越小。同时还可以看到不同索引方案的对比成本，帮助我们判断当前选择的索引是否是最优方案。
索引的额外开销评估
评估索引开销不仅要看查询时的收益，还要考虑索引带来的额外维护成本：
- 存储空间开销：每个索引都会占用额外的磁盘空间，我们可以通过
information_schema.TABLES表查询索引占用的空间大小 - 写入开销：每次对表执行INSERT、UPDATE、DELETE操作时，都需要同步更新所有相关索引，索引越多写入速度越慢
- 优化器选择开销：过多的索引会让优化器在选择执行计划时花费更多时间，极端情况下可能导致优化器选择错误的索引
我们可以通过以下语句查询用户表的索引空间占用情况：
SELECT
TABLE_NAME,
INDEX_NAME,
STAT_NAME,
STAT_VALUE
FROM information_schema.INNODB_INDEX_STATS
WHERE TABLE_SCHEMA = '你的数据库名' AND TABLE_NAME = 'user_info';
性能追踪与长期分析
对于线上运行的SQL语句，我们可以通过mysql的性能追踪工具进行长期分析：
- 慢查询日志：开启慢查询日志，记录执行时间超过阈值的SQL语句，定期分析这些语句的索引使用情况，评估是否存在索引开销过高的问题
- Performance Schema：mysql内置的性能监控工具，可以统计每个SQL语句的执行次数、平均执行时间、索引使用情况等指标，帮助我们定位高频低效的SQL语句
- 定期索引分析：使用
ANALYZE TABLE命令定期更新索引的统计信息，确保优化器能够基于最新的数据分布选择最优的索引方案，避免因为统计信息过期导致索引开销评估不准确
在评估索引开销时，我们需要结合查询频率、数据量变化、业务场景综合判断，不能只追求单条SQL的查询速度，而忽略索引带来的整体维护成本。对于低频查询的字段、区分度极低的字段（如性别字段），通常不建议添加索引，避免产生不必要的开销。