Mysql：合理预测添加列或索引的持续时间
我知道这个问题不是我能得到准确答案的问题。我要找棒球场的数字。
我基本上需要一种方法来回答这个问题：当我添加此列或索引时，它是否会将表锁定 1 分钟、10 分钟或 1 小时以上。原因是我们需要知道实施此操作是否需要我们通知客户服务中断，或者我们是否可以在非高峰时间通知客户。
我意识到像这样的问题涉及很多因素，但是，我想重点关注那些不明显的因素，例如机器性能、当前系统负载等。
理想情况下是某种形式，例如：
< code>(行数) * (0.01 秒) * (一些未知因素) = 112.secs
另外，在列中添加索引之间是否存在实质性差异？ 添加的不同类型的列怎么样？
换句话说，决定 alter 语句是否缓慢或非常慢的最大因素是什么？
I understand that this question isn't one in that I'm going to get a precise answer. I'm going for ball park figures.
I basically need a way to answer this question: When I add this column or index, is it going to lock the table for 1 minute, 10 minutes or and hour+. The reason being we need to know if this is implementation is going to require us to notify clients of a break in service or if we can do it at off peak hours.
I realize there are a lot of factors involved in a question like this but, I'd like to focus in on the ones that aren't obvious such as machine performance, current system load etc.
Ideally a formal of some sort like:
(Number of Rows) * (0.01 Secs) * (Some unknown factors) = 112.secs
Also is there a substantial difference between adding an index an a column?
What about different types of columns that are added?
To put the question another way, what are the largest factors in determining if an alter statement will be slow or very slow?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最好的方法是在具有相同数据集的类似硬件上拥有数据库的暂存副本，最好是从夜间备份生成。
然后，您可以毫无畏惧地尝试模式修改，并大致了解应该花费多长时间。当然，有负载的数据库比没有负载的数据库需要更长的时间。为了考虑到这一点，您可以针对临时数据库运行一些负载测试脚本，这些脚本在尝试架构更改时运行常见的应用程序操作。
The best approach here is to have a staging copy of your database on similar hardware with the same data set, ideally generated from nightly backups.
Then you can try your schema modifications out without fear, and get a good rough idea of how long it should take. Of course a database under load will take longer than one that is not. To factor this in, you could run some load-testing scripts against the staging database that run common application operations while trying out your schema changes.