对于 5000 条记录的表，类似 MySQL 的查询运行速度极慢
我的生产服务器上有这个问题。应用程序堆栈是
- Tomcat 6.0.18 上的 Java Web App
- iBatis 数据访问层
- MySQL 5.0 数据库
- CentOS
该系统部署在具有大约 256 MB 内存的虚拟服务器上。
真正的问题：
像这样的查询
select * from customer
将在大约 10 秒内执行，但是如果执行以下查询，
select * from customer where code like '%a%'
则在执行上述查询后，系统将进入无限期处理并最终迫使 Tomcat 重新启动！
表格统计： - 记录数量：5000 - 主键：代码
相同的查询 PHP MyAdmin 在大约 4 秒内执行。
你认为这可能是 MySQL 的问题吗？任何调试这个的想法。我现在正在启用详细日志，并将继续用我的发现更新这个问题，但我会感谢您的数据库见解。
I have this issue on our production server. The application stack is,
- Java Web App on Tomcat 6.0.18
- iBatis data access layer
- MySQL 5.0 database
- CentOS
The system is deployed on virtual server having around 256 MB memory.
Real problem:
The query like,
select * from customer
executes in around 10 seconds however if the following query is executed,
select * from customer where code like '%a%'
right after executing the above query, the system goes into indefinite processing and ultimately forces Tomcat to restart !.
Table statistics:
- No. of records : 5000
- Primary Key : code
The same query PHP MyAdmin executes in around 4 seconds.
Do you think it could be MySQL problem ? Any idea to debug this. I am right now enabling the detailed logs and will keep updating this question with my findings but would appreciate your db insights.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我最近在我的一个生产系统中遇到了类似的 MySQL 问题。
正如上面的评论者所指出的，问题在于文本字段上的通配符搜索，特别是搜索中的前导％。
我们降低了领先百分比，并将搜索查询所需的时间缩短了几个数量级（从服务器耗时 60 秒以上到“根本没有时间”）。
替代方案是使用全文索引或 Lucene 等系统进行搜索。
I recently encountered a similar issue with MySQL in one of my production systems.
As a commenter noted above, the issue is the wildcard searching on the text field, and in particular the leading % in the search.
We dropped the leading % and reduced time take for a search query by several orders of magnitude (from a server grinding 60seconds+ to "no time at all").
Alternatives would be to use a Full-Text index or a system like Lucene for searching.
看来你的 MySQL 服务器设置不正确，花费 10 秒处理 5000 条记录是不可接受的。
你如何通过java代码访问你的数据库？在这种情况下，请在此处给出您的高级逻辑，也许您没有有效地重用数据库处理程序。
It seems your MySQL server is not setup correctly, taking 10 seconds for 5000 records should not be acceptable.
How are you accessing your db, via java code? In that case please give your high level logic here, maybe you are not efficiently reusing the db handlers.
搜索开头的 % 导致表从不使用索引。 % 是通配符，因此它必须遍历数据库中的每条记录。再加上 tomcat 和 MySQL 都在运行，情况会变得更糟。内存中没有足够的空间来放置索引以供读取。
您需要增加该机器上的内存量，以便让 MySQL 创建它所需的必要内存缓存以及允许查询快速运行的空间。
The % in the beginning of the search is causing the table to never use an index. The % is a wildcard so it has to go through every single record in the database. Combine this with the fact that tomcat is running as well as a MySQL is running makes it even worse. There is not a lot of room to put an index in memory to read from.
You need to up the amount of memory on that box to let MySQL create the necessary memory caches it needs and room to allow the queries to work fast.
在问题中，发帖者指出，相同的查询在 PHP MyAdmin 中运行需要 4 秒，因此问题不在于 MySql 或由于 % 而无法使用索引，而在于 Tomcat 或数据访问层。
In the question the poster states that the same query runs in 4 seconds in PHP MyAdmin, so the problem is not in MySql or the inability to use an index due to the %, but in Tomcat or the data access layer.
您是否尝试过创建自动增量主字段，并使代码成为单独的唯一索引。
我以前遇到过文本作为主键在某些环境中非常慢的问题。
Have you tried creating an autoincrement primary field, and making code a separate unique index.
I've had issues with text as primary key being very slow in certain environments before.