MySQL - 并发选择 - 一个客户端等待另一个客户端？
我有以下场景：
我有一个数据库，其中包含大约 400 万行的特定 MyISAM 表。 我使用存储过程（MySQL 版本 5.1），特别是根据各种条件搜索这些行。 该表上有多个索引，并且通过该存储过程的查询通常非常快（<1s）。 基本上，我使用准备好的语句，并在此搜索 sp 中创建并执行一些动态 SQL。 执行准备好的语句后，我执行“DEALLOCATE PREPARED stmt;”
大多数查询运行时间不到一秒（我使用 LIMIT 随时只获取 15 行）。 但是，有一些罕见的查询需要更长的时间才能运行（例如 2-3 秒）。 我已经尽可能优化了搜索表。
我开发了一个 Web 应用程序，我可以在我的开发计算机上运行并在一秒钟之内查看快速查询的结果。
但是，如果我打开两个浏览器实例并同时进行搜索（针对开发计算机），一个使用运行时间较长的查询，另一个使用运行速度更快的查询，则结果会同时返回，即看起来好像快速查询会等待较慢查询完成后再返回结果。 即两个查询都需要 2-3 秒...
这有什么原因吗？ 因为我认为 MyISAM 处理 SELECTS 时彼此无关，而目前这不是我遇到的行为...
提前致谢！ 蒂姆
I have the following scenario:
I have a database with a particular MyISAM table of about 4 million rows. I use stored procedures (MySQL Version 5.1) and one in particular to search through these rows on various criteria. This table has several indexes on it, and the queries through this stored procedure are normally very fast ( <1s). Basically I use a prepared statement and create and execute some dynamic SQL in this search sp. After executing the prepared statement, I perform "DEALLOCATE PREPARED stmt;"
Most of the queries run in under a second (I use LIMIT to get just 15 rows at any time). However, there are some rare queries which take longer to run (say 2-3s). I have optimized the searched table as far as I can.
I have developed a web application and I can run and see the results of the fast queries in under a second on my development machine.
However, if I open two browser instances and do a simultaneous search (against the development machine), one with the longer running query, and the other with the faster query, the results are returned at the same time, i.e. it seems as if the fast query waits for the slower query to finish before returning the results. i.e. both queries will take 2-3 seconds...
Is there a reason for this? Because I thought that MyISAM handles SELECTS irrespective of one another and currently this is not the behaviour I am experiencing...
Thanks in advance!
Tim
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这只是因为您在同一台机器上执行此操作，如果搜索来自两台不同的机器，它们将同时进行。 你真的希望有人仅仅通过打开一堆浏览器窗口并点击刷新就能让你的 MySQL 服务器陷入困境吗？
This is just due to you doing it from the same machine, if the searches were coming from two different machines they would go at the same time. Would you really like one person to be able to bog down your MySQL server just by opening a bunch of browser windows and hitting refresh?
没错。 MyISAM 表上的每个选择查询都会锁定整个表，直到完成。 他们的借口是这实现了“非常高的读取吞吐量”。 切换到 innoDB 将允许并发读取。
That is right. Each select query on a MyISAM table locks the entire table until it is finished. Their excuse is that this achieves "a very high read throughput". Switching to innoDB will allow concurrent reads.