MySQL之类的查询对于5000条记录表的运行极其缓慢 [英] MySQL like query runs extremly slow for 5000 records table

问题描述

我的生产服务器上出现此问题.应用程序堆栈是

I have this issue on our production server. The application stack is,

• Tomcat 6.0.18上的Java Web App
• iBatis数据访问层
• MySQL 5.0数据库
• CentOS

该系统部署在具有大约256 MB内存的虚拟服务器上.

The system is deployed on virtual server having around 256 MB memory.

实际问题:

查询类似

select * from customer

在大约10秒内执行，但是如果执行以下查询，

executes in around 10 seconds however if the following query is executed,

select * from customer where code like '%a%'

在执行上述查询后，系统立即进入不确定的处理状态，并最终迫使Tomcat重新启动！.

right after executing the above query, the system goes into indefinite processing and ultimately forces Tomcat to restart !.

表格统计信息: -记录数:5000 -主键:代码

Table statistics: - No. of records : 5000 - Primary Key : code

同一查询PHP MyAdmin大约在4秒钟内执行.

The same query PHP MyAdmin executes in around 4 seconds.

您认为这可能是MySQL问题吗?任何想法来调试它.我现在正在启用详细的日志，并将根据我的发现继续更新此问题，但希望您能提供有关数据库的见解.

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.

推荐答案

我最近在一个生产系统中遇到了与MySQL类似的问题.

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.

我们降低了前导％，并将搜索查询的时间减少了几个数量级(从服务器耗时60秒以上到根本没有时间").

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").

替代方法是使用全文索引或像Lucene这样的系统进行搜索.

Alternatives would be to use a Full-Text index or a system like Lucene for searching.

这篇关于MySQL之类的查询对于5000条记录表的运行极其缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！