MySQL“锁定”复制 tmp 表时的进程
我有一个查询需要很长时间才能运行，但最终会生成一个新表。实际的连接并不是那么慢，但它几乎将所有时间都花在“复制到临时表”上，在此期间，所有其他查询（应该转到不相关的表）的状态都是“锁定”的。我正在优化长查询，但它需要一段时间是可以的，因为它是一个离线过程，但它不能停止所有其他不应该与它相关的查询。有谁知道为什么所有其他不相关的查询都会以“锁定”状态返回以及如何防止这种行为？
I have a query which takes a very long time to run but produces a new table in the end. The actual joins are not all that slow but it spends almost all of its time in 'copying to tmp table' and during this time the status of all other queries (which should be going to unrelated tables) is 'locked'. I am in the process of optimizing the long query but it is ok for it to take a while since it is an offline process, but it is NOT ok for it to stop all other queries which should not be related to it anyway. Does anyone know why all other unrelated queries would comeback as 'locked' and how to prevent this behavior?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您是对的，“不相关的表”不应受到影响。他们不应该这样做，据我所知，他们也不应该这样做。
MySQL 有很多关于锁的信息、存储引擎以及处理它的方法。
为了限制锁，我建议您编写一个应用程序来读取执行此新表所需的所有数据，然后让您的应用程序将值插入到新表中。这可能需要更长的时间，但它将以较小的块进行，并且具有较少的锁或没有锁。
祝你好运！
You are right in that "unrelated tables" shouldn't be affected. They shouldn't and to my knowledge they aren't.
There is a lot of information over at MySQL regarding locks, storage engines and ways of dealing with it.
To limit locks I would suggest that you write an application that reads all data needed to do this new table and simply have your application insert values to the new table. This might take longer but it will do it in smaller chunks and have less or no locks.
Good luck!
你的 MySQL 版本是什么？
你用MyISAM吗？ MyISAM 在大型 SELECT 命令上有一个很大的 LOCK 问题。
你们有专用服务器吗？内存表的最大大小是多少（查看 my.cnf）？
What is your MySQL Version?
Do you use MyISAM? MyISAM has a big LOCK problems on large SELECT commands.
Do you have a dedicated server? what is your maximum size for in-memory tables (look in my.cnf)?