如何查看SQLite3的索引使用统计信息？

一、启用索引使用跟踪 #

SQLite默认未开启索引使用计数功能，需通过以下方式启用：

1. 编译时配置（自行编译SQLite场景） #

若你是自行编译SQLite，需添加SQLITE_ENABLE_STAT4和SQLITE_ENABLE_INDEX_SCAN_STATISTICS编译选项，让SQLite具备跟踪索引扫描次数的能力。

2. 运行时启用统计收集 #

针对已编译完成且支持该功能的SQLite版本，执行以下PRAGMA命令开启统计：

PRAGMA enable_coverage_stat = true;

该命令会触发SQLite记录每个索引被查询调用的次数。

二、查看索引使用情况 #

1. 基础统计：查询sqlite_stat1表 #

sqlite_stat1存储了表与索引的核心统计数据，虽非实时使用次数，但能反映索引的选择性与大致使用频率：

SELECT * FROM sqlite_stat1;

结果中的stat列包含索引的关键统计信息，可辅助判断索引的实用性，但无法直接确认是否被使用过。

2. 实时使用详情：查询虚拟表 #

启用enable_coverage_stat后，可通过sqlite_stmt和sqlite_stmt_scanstatus虚拟表获取索引的具体使用记录：

-- 查看所有语句的索引扫描状态
SELECT s.sql, idx.name AS index_name, ss.*
FROM sqlite_stmt s
JOIN sqlite_stmt_scanstatus ss ON s.id = ss.stmtid
LEFT JOIN sqlite_master idx ON ss.idxname = idx.name
WHERE idx.type = 'index';

该查询会展示每个索引被哪些SQL语句调用，以及对应的扫描次数等细节。

三、识别未使用的索引 #

• 若某索引在sqlite_stmt_scanstatus中无任何记录，说明它从未被查询优化器选中使用。
• 结合业务逻辑验证：部分索引可能为特定场景设计，若长期无使用记录且业务已不再需要，即可标记为待删除对象。

四、删除未使用的索引 #

确认索引无用后，使用DROP INDEX命令删除：

DROP INDEX IF EXISTS index_name;

注意：删除前务必备份数据库，或先在测试环境验证删除后不会影响业务查询性能。

补充提示 #

• SQLite的索引统计功能依赖编译选项与运行时配置，若当前版本不支持，需考虑升级或重新编译SQLite。
• 生产环境建议先开启统计并观察一段时间，确保准确识别未使用索引后再执行删除操作。

内容的提问来源于stack exchange，提问作者MandyShaw