在复制的 mysql 结构中 RENAME TABLE 有哪些风险？
我有一个 cron 脚本，它创建了 mysql 表的更新版本，该表针对站点搜索目的进行了优化。
站点搜索中使用的表称为tblSearch
cronjob 创建一个新表 wrkSearch 并在完成填充表后结束 删除 tblSearch 并重命名 wrkSearch （两个表都有 MySam 引擎）
DROP TABLE IF EXISTS `tblSearch
RENAME TABLE `wrkSearch` TO tblSearch
这工作正常，但我想知道这是否是解决问题的好方法。
我想知道您的考虑因素是否会根据表大小而改变...例如：“该解决方案对于非常大的表可能是危险的，因为...”
我知道每个正在改变某些内容的查询mysql 表内正在文件系统上写入内容...但是执行 RENAME 而不是 UPDATE/INSERT 之间有什么区别吗？我试图了解 RENAME 命令是否对文件系统更具攻击性。
表中的另一个因素是数据库是通过主从结构复制的......所以我还想知道这是否最终会增加 RENAME 查询背后的风险
对我来说重要的另一个方面是使用的系统资源量... RENAME 操作是否比 UPDATE/INSERT 更贪婪？
I have a cron script that creates an updated version of a mysql table that is optimized for site searching purposes.
The table that is used in the site search is called tblSearch
the cronjob creates a new table wrkSearch and when it has finished to populate the table ends
dropping tblSearch and renaming wrkSearch
(both tables have MySam Engine)
DROP TABLE IF EXISTS `tblSearch
RENAME TABLE `wrkSearch` TO tblSearch
This is working fine, but I'd like to know if is a good approach to the problem.
I would like to know if your considerations change based on table size... for example: "the solution could be dangerous for very big tables cause..."
I know that every query that is changing something inside a mysql table is writing things on the filesystem... but are there some difference between doing a RENAME instead of a UPDATE/INSERT? I'm trying to understand if the RENAME command is more aggresive against the filesystem.
Another element on the table is the fact that te database is replicated with a MASTER-SLAVE structure... so I'd also like to know if this could eventually incrase the risks behind a RENAME query
Another aspect that is important to me is the amount of system resources that are used... could the RENAME operation be more greedy than UPDATE/INSERT?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你应该改变顺序；首先重命名，然后删除：
由于 RENAME 是原子的，因此其他进程不可能无法访问 tblSearch；第一次删除时，然后重命名可能会发生这种情况。
除此之外，我没有看到任何与复制相关的问题。
You should change the order; first rename, than drop:
Since RENAME is atomic there is no way some other process will ever fail to access tblSearch; when first dropping, then renaming this might happen.
Apart from that I don't see any replication related problems with this.
重命名本身非常快。如果您要删除表中的所有内容并重新创建它，那么此方法就很好。 （尽管请检查它是否确实正确复制。）
但是，关于复制，这意味着需要传输表的整个数据。
如果可以的话，最好更新表中的数据 - 这样只会更改部分数据，而不是所有数据。
我应该指出，这种类型的操纵行为是令人不悦的——这是有充分理由的！但就像所有规则一样，有时你必须打破它们。
The rename itself is super fast. If you are deleting everything from the table and creating it fresh then this method is fine. (Although please check that it does actually replicate properly.)
However regarding the replication it means the entire data of the table needs to be transmitted.
It's better, if you can, to update the data in the table - that way only some of the data changes, not everything.
I should point out that this type of manipulation is frowned upon - with good reason! But like all rules, sometimes you have to break them.