I've seen many different schedules for optimizing tables in MySQL: everything from immediately after a delete, to once a day, to never. I have often setup a cron job to run every night to optimize all the tables.
我已经看到许多不同的时间表用于优化MySQL中的表:从删除后立即到每天一次到永不停止。我经常设置一个cron作业,每晚运行以优化所有表格。
What factors do you use to determine how often a table should be optimized?
您使用哪些因素来确定表的优化频率?
2 个解决方案
#1
Also this quote from MySQL themselves is good.
MySQL引用的这句话也很好。
In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.
在大多数设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量更新,也不一定需要每周或每月执行此操作一次以上,并且仅在某些表上执行此操作。
Based on this article on Table Optimization.
基于这篇关于表优化的文章。
This has the effect of defragmenting the table and reducing the size of the table on disk down to 105MB. It also has a very positive affect on query performance, reducing the select query response time from 0.63 to 0.39 seconds. N.B. the mysql query cache was turned off to demonstrate.
这样可以对表进行碎片整理,并将磁盘上的表大小减小到105MB。它还对查询性能产生非常积极的影响,将选择查询响应时间从0.63减少到0.39秒。注: mysql查询缓存已关闭以演示。
Here's another quote from "MySQL administrators guide"
这是“MySQL管理员指南”的另一个引用
OPTIMIZE TABLE does a table repair and a key analysis, and also sorts the index tree so that key lookups are faster.
OPTIMIZE TABLE执行表修复和密钥分析,并对索引树进行排序,以便密钥查找更快。
Just because this quote can never be told to often.
仅仅因为这句话永远不会被告知。
Premature optimization is the root of all evil
过早优化是万恶之源
What you should do is measure before, do an optimization and then measure after. Then do the same thing the day after, if the increase in performance is minimal, then you should do the same after and see if you get a big boost in performance then.
你应该做的是先测量,做一个优化,然后测量。然后在第二天做同样的事情,如果性能的提高是最小的,那么你应该在之后做同样的事情,然后看看你是否在性能上有了很大的提升。
#2
Other factors will most likely contribute more to query times than having run the optimize table.
与运行优化表相比,其他因素最有可能对查询时间贡献更多。
If performance is an issue (and only if), then you should profile the application using the database start by optimizing the actual bottlenecks.
如果性能是一个问题(并且仅当),那么您应该通过优化实际瓶颈来使用数据库启动来分析应用程序。
#1
Also this quote from MySQL themselves is good.
MySQL引用的这句话也很好。
In most setups, you need not run OPTIMIZE TABLE at all. Even if you do a lot of updates to variable-length rows, it is not likely that you need to do this more than once a week or month and only on certain tables.
在大多数设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量更新,也不一定需要每周或每月执行此操作一次以上,并且仅在某些表上执行此操作。
Based on this article on Table Optimization.
基于这篇关于表优化的文章。
This has the effect of defragmenting the table and reducing the size of the table on disk down to 105MB. It also has a very positive affect on query performance, reducing the select query response time from 0.63 to 0.39 seconds. N.B. the mysql query cache was turned off to demonstrate.
这样可以对表进行碎片整理,并将磁盘上的表大小减小到105MB。它还对查询性能产生非常积极的影响,将选择查询响应时间从0.63减少到0.39秒。注: mysql查询缓存已关闭以演示。
Here's another quote from "MySQL administrators guide"
这是“MySQL管理员指南”的另一个引用
OPTIMIZE TABLE does a table repair and a key analysis, and also sorts the index tree so that key lookups are faster.
OPTIMIZE TABLE执行表修复和密钥分析,并对索引树进行排序,以便密钥查找更快。
Just because this quote can never be told to often.
仅仅因为这句话永远不会被告知。
Premature optimization is the root of all evil
过早优化是万恶之源
What you should do is measure before, do an optimization and then measure after. Then do the same thing the day after, if the increase in performance is minimal, then you should do the same after and see if you get a big boost in performance then.
你应该做的是先测量,做一个优化,然后测量。然后在第二天做同样的事情,如果性能的提高是最小的,那么你应该在之后做同样的事情,然后看看你是否在性能上有了很大的提升。
#2
Other factors will most likely contribute more to query times than having run the optimize table.
与运行优化表相比,其他因素最有可能对查询时间贡献更多。
If performance is an issue (and only if), then you should profile the application using the database start by optimizing the actual bottlenecks.
如果性能是一个问题(并且仅当),那么您应该通过优化实际瓶颈来使用数据库启动来分析应用程序。