INSERT 一多导致查询变慢的根源是 B+ 树页分裂与数据物理存储碎片化，引发 I/O 跳跃加剧、缓存命中率下降；需通过页利用率、逻辑连续性等指标判断碎片，并优先优化写入方式而非依赖 OPTIMIZE。
为什么 INSERT 一多，查询就变慢？
不是索引失效了，是 B+ 树页分裂 + 数据物理存储碎片化导致的。每次写入都可能触发页分裂，旧页留下空洞，新数据散落在磁盘不同位置，SELECT 扫描时 I/O 跳跃加剧，缓存命中率下降——尤其对范围查询和 ORDER BY 影响明显。
常见错误现象：SHOW INDEX FROM table_name 显示 Cardinality 长期不更新；EXPLAIN 中 key_len 正常但 rows 暴涨；INFORMATION_SCHEMA.INNODB_BUFFER_PAGE 查到大量低利用率的索引页。
- 别等「明显变慢」才处理，当单表日增 > 50 万行且有复合索引时，就该关注碎片率
-
innodb_file_per_table=ON是前提，否则无法单独优化单个表 - 主键设计影响极大：用自增
BIGINT比 UUID 或字符串主键更少分裂
怎么查索引碎片率？别只看 DATA_FREE
DATA_FREE 在 information_schema.TABLES 里只反映表空间未分配字节，对 InnoDB 碎片无意义。真实碎片得看页利用率和逻辑排序连续性。
实操建议：
- 用
SELECT (data_length+index_length)/table_rows AS avg_row_size FROM information_schema.TABLES WHERE table_name='t' AND table_schema='db';辅助判断——若平均行大小远大于字段实际长度（比如VARCHAR(255)实际存 10 字符却算出 180 字节），说明页内空洞多 - 执行
OPTIMIZE TABLE t;前先跑SELECT COUNT(*) FROM t FORCE INDEX (PRIMARY);和SELECT COUNT(*) FROM t;，如果耗时差异 > 3 倍，大概率存在严重逻辑碎片 - 监控
Innodb_buffer_pool_read_requests与Innodb_buffer_pool_reads比值，持续低于 95% 就得怀疑索引页局部性差
OPTIMIZE TABLE 和 ALTER TABLE ... ENGINE=InnoDB 有什么区别？
在 MySQL 5.6+、innodb_file_per_table=ON 下，两者效果几乎一致：重建表 + 索引，重新聚簇数据，合并页，重排 B+ 树。但行为细节不同。
-
OPTIMIZE TABLE是 DDL 语句，会隐式加SIX锁，期间允许读、阻塞写，执行完自动更新统计信息 -
ALTER TABLE t ENGINE=InnoDB更底层，不强制更新统计信息，需手动ANALYZE TABLE t; - 线上大表慎用：500GB 表执行
OPTIMIZE可能卡住binlog位点，造成从库延迟；建议配合pt-online-schema-change分批做 - 别在业务高峰跑，它会触发一次全表拷贝，磁盘 IO 和 buffer pool 冲击很大
写入优化比碎片整理更治本
碎片是结果，高频随机写才是病根。与其反复 OPTIMIZE，不如压住源头。
- 批量插入用
INSERT INTO t VALUES (...), (...), (...);，单条INSERT的索引维护开销是批量的 3–5 倍 - 临时关闭唯一性检查：
SET unique_checks=0;（导入后记得开回来），跳过二级索引的逐条查找验证 - 调整
innodb_log_file_size：写入量大时，太小会导致频繁刷脏页，间接加剧碎片；建议设为 1–2GB（需重启生效） - 对非实时强一致场景，把部分二级索引改成
GENERATED COLUMN + STORED+ 单独索引，减少写时计算负担
碎片整理不是银弹。真正难的是平衡写入吞吐、查询延迟和磁盘成本——比如一个日增千万的订单表，靠定期 OPTIMIZE 不如拆成按月分区，再配合归档冷数据。
