EXPLAIN是判断索引是否被有效使用的唯一可靠方式;SHOW INDEX仅显示索引结构,key为NULL表示未走索引,type为ALL/index说明全表或全索引扫描,联合索引跳过首列则失效。

查 SHOW INDEX 和 EXPLAIN 判断索引是否被有效使用
索引“存在”不等于“被用”,SHOW INDEX FROM your_table 只显示结构,真正要看它在查询中是否生效,得结合实际 SQL 走 EXPLAIN。常见误判是看到有索引就以为没问题,结果 type 是 ALL 或 index,说明走了全表或全索引扫描。
-
key列为空 → 没走任何索引;为NULL且possible_keys非空 → 优化器认为不值得用 -
rows明显大于表总行数 → 索引选择性差,可能因CARDINALITY过低(比如CARDINALITY / TABLE_ROWS ) - 联合索引中只用了前缀列(如索引
(a,b,c),查询只WHERE a = ?)是正常;但WHERE b = ?就失效,不会跳过首列
用 information_schema.TABLES 算碎片率,别只看 Data_free
Data_free 是表空间级空闲字节数,不是索引碎片本身。它对 innodb_file_per_table = ON 的表才有意义,且无法区分数据页和索引页。真正要评估碎片影响,得算比值:
- 碎片率 ≈
Data_free / (Data_length + Index_length),> 0.2(20%)才需警惕 - 绝对值参考:100MB
Data_free对 1GB 表不算严重,对 10MB 表就该干预 - 执行
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'db' AND TABLE_NAME = 't',重点看这三列:Data_free、Data_length、Index_length
查 INNODB_SYS_INDEXES 看索引页利用率
InnoDB 不暴露碎片率,但 INFORMATION_SCHEMA.INNODB_SYS_INDEXES 里的 page_count 和 n_leaf_pages 能间接反映 B+ 树填充情况。叶子节点占比过低(比如 n_leaf_pages / page_count ),说明内部节点多、空洞多,逻辑碎片已影响范围查询性能。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
- 查法:
SELECT NAME, N_FIELDS, PAGE_NO, PAGE_COUNT, N_LEAF_PAGES FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE TABLE_ID IN (SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME = 'your_db/your_table') -
PAGE_NO = 0是聚簇索引,其他是二级索引;N_FIELDS是索引列数,可辅助判断是否冗余 - 注意
NAME格式是database/table,不是反引号包裹的`db`.`t`
OPTIMIZE TABLE 前必须确认三点
它本质是重建表(ALTER TABLE ... FORCE),不是轻量整理。线上执行前不验证这三项,容易锁表、OOM 或白忙一场:
- 确认
innodb_file_per_table = ON:否则Data_free不会释放到磁盘,只整理 ibdata1 内部页 - 确认磁盘剩余空间 ≥ 原表大小:临时表 + 原表同时存在,空间不足会失败并残留
#sql-*.ibd - 确认无长事务或未提交 DML:
OPTIMIZE TABLE需要 MDL 全局锁,会卡住所有写操作,大表可能持续几分钟
真正难的是区分物理碎片和逻辑碎片——前者看 Data_free 和文件大小,后者藏在查询响应时间和 EXPLAIN 的 rows 里,不跑真实负载压测,光看指标容易误判。










