无法更新记录，卡住了
我当前的 mysql 数据库有问题。
我有超过 100 个连接正在等待选定的记录。当我执行时：
show processlist;
选择查询是一个大查询，其他查询是较小的查询和插入、更新。
我有一个包含 100 个表的数据库，并且选择使用 5 个连接。
有没有办法暂时停止进程并让其他进程运行，一旦所有进程完成，然后 select 可以继续运行？
i have an issue with my current database with mysql.
i have over 100 connection waiting on a select record. when i execute:
show processlist;
the select query is a big query and the others are smaler queries and inserts, updates.
i have one database with 100 tables and the select is using 5 joins.
is there a way to temporary stop the process and let the other processes run, once all the processes are completed, then the select can continue running?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我建议让查询做它需要做的事情，如果您停止任何或终止进程或查询，您可能会出现数据完整性错误，这可能会导致重大错误。
BookOfZeus 和 tfb785 是对的，首先你可能有索引错误。解释将告诉您问题到底是什么以及要寻找什么。例如，如果您有 5 个联接，并且您得到的行数为 100,000 和 100 和 1 和 1 和 1，那么您将乘以 100,000 * 100，这可能会非常慢。
仔细阅读解释告诉您的内容，并根据它优化您的查询。
如果您经常访问表，innodb 可能是一个不错的选择，因为它的行锁定代替了 myisam 的表锁定。
我想说首先尝试优化您的查询，也许您不需要更改表引擎来解决问题。如果您仍然遇到问题，那么您可以考虑迁移到 innodb。
i will recommend to let the query do what it needs to do, if you stop any or kill processses or queries you might have data integrity errors which can lead to major errors.
BookOfZeus and tfb785 are right, first of all you probably have indexes errors. the explain will tell you what exactly is the problem and what to look for. for example if you have 5 joins and you get row counts like, 100,000 and 100 and 1 and 1 and 1 you will multiple 100,000 * 100 which can be super slow.
read carefully what the explain tells you and optimize your query based on it.
innodb can be a good option if you if the tables are accessed very often because its row locking insted of table locking for myisam.
I would say first try to optimize your query, maybe you wont need to alter your table engine to fix the issue. if you still have issues then you might consider moving to innodb.
我相信您正在使用
MyISAM作为存储引擎。在
MyISAM中，读查询会阻塞并发的写查询。这可以确保写入不会更改正在处理的数据，并且您的记录集不会有一半更新而另一半未更新。无法按照您要求的方式停止 SELECT 查询：写入可能会使返回的记录集不一致。虽然这可能不是您设置中的实际情况，并且理论上写入可能不会影响读取（例如，读取和写入不同的记录），但 MyISAM 不知道这一点，并且它总是阻塞整个表，万一。
如果您需要写入而不阻止读取，请切换到 InnoDB（尽管在某些情况下写入也会阻止读取）。
I believe you are using
MyISAMas a storage engine.In
MyISAM, the read queries block the concurrent write queries. This makes sure that the writes won't change the data in process and you don't get half of your recordset updated and another half not updated.There is no way to stop the
SELECTquery the way you ask: the writes could make the recordset returned inconsisent. While it may be not the actual case in your setup and theoretically writes might not affect the reads (say, different records are read and written),MyISAMis not aware of that and it always blocks whole tables, just in case.If you need writes not to block reads, switch to
InnoDB(though there are cases when writes will block reads too).您无法停止正在运行的查询并为其他查询留出时间。但有三种方法可以解决您的问题：
第一种方法：
更改隔离级别 http://dev.mysql.com/doc /refman/5.6/en/mysql-acid.html
这些也可能意味着您必须更改存储引擎，例如。到 InnoDB。
第二种方式：
尝试使用索引加速长时间运行的选择。这就是所谓的时间记忆权衡。您可以使用更多内存来保存索引树，从而加快查询速度。
第三种方式：
通过重新排列查询（连接和选择）来加速长时间运行的选择。分析所有查询部分的成本。
You can't stop the running query and to give time to the other queries. But there are three ways to solve your problem:
First way:
Change the isolation level http://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html
These can also mean that you have to change your storage engine eg. to InnoDB.
Second way:
Try to speedup your long running select with indices. This is a so called time memory trade off. You speedup your query with more memory for holding the indices trees.
Third way:
Speedup your long running select with rearranging the query (joins and selects). Analyze the cost of all query parts.