背景

这几天客户有Oracle数据库出现了数据文件一天增长200G的情况. 
然后昨天很多同事帮忙定位到了原因
最后再聊解决方案时, 我反馈 truncate和drop 只是释放逻辑空间
不会释放时机物理空间. 
然后同事说drop了就这样吧. 空闲出来的逻辑空间应该可以复用
我想之前已经研究过很多次 Oracle的数据文件和表空间了. 
本来也没想再怎么处理.
结果到了下午, 同事反馈一个 MySQL的数据库出现了删除缓慢的现象.
我这边第一反应是索引失效.
然后重建了一下表.
然后发现,之前50秒超时, 现在3秒就可以了. 
早上起来 突然想到查询一下索引碎片率, 结果发现里面有很多我不清楚和熟悉的地方.
所以总结一下.

学习来源

https://www.cnblogs.com/wy123/p/12535644.html
https://blog.csdn.net/zhezhebie/article/details/120758695

具体优化命令

SELECT TABLE_SCHEMA
      ,TABLE_NAME 
      ,ENGINE
      ,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB
      ,ROUND(DATA_FREE/1024/1024,2) AS FREE_SIZ_MB
FROM information_schema.TABLES 
WHERE DATA_FREE >=5*1024*1024
ORDER BY FREE_SIZ_MB DESC;

OPTIMIZE TABLE pfact_hi_actinst
analyze TABLE pfact_hi_actinst

结果验证

清理效果

执行方式为:
OPTIMIZE TABLE pfact_hi_actinst ;
analyze TABLE pfact_hi_actinst ;

数据文件查看

最开始为 291GB
运行之后的大小为 287GB

使用效果

需要说明, 当时处理的并不是这张表
但是处理方式基本上是一样的. 

现在认为. 出现大量的空洞, 导致效率下降. 
之前删除语句耗时 50S
现在耗时只需要 3S 左右

总结

OPTIMIZE TABLE 
命令用于整理表中的数据，减少碎片，并回收未使用的空间。
这对于频繁插入、更新和删除操作的表特别有用，
因为它可以提高查询性能并减少磁盘空间。

ANALYZE TABLE 命令用于收集表的统计信息，
并将其存储在数据库的内部数据字典中。
数据库优化器使用这些统计信息来选择最佳的执行计划，
从而提高查询性能。

注意事项
执行时间：
OPTIMIZE TABLE 可能会花费较长时间，特别是在大型表上，
因为它需要对表进行全扫描和重组。
锁表：
在某些存储引擎（如 InnoDB）中，
OPTIMIZE TABLE 可能需要对表进行独占锁定，这可能会影响并发访问。
定期执行：
建议定期执行这些命令，尤其是在频繁更新数据的情况下，
以保持数据库的最佳性能。

结论
通过执行 OPTIMIZE TABLE 和 ANALYZE TABLE 操作，
你可以有效地管理和优化数据库表，确保查询性能最佳。