提高LIKE子句的性能 [英] improve performance for LIKE clause

问题描述

在过去的几天里，这一直困扰着我.我在没有任何有关LIKE性能的知识的情况下创建了数据库.我使用的查询是这样的，

This has been a pain on my head for the past few days. I created the database before without any knowledge about the performance of LIKE. The query I used is like this,

SELECT  ....
FROM    .... JOINS ....
WHERE   tableA.col1 LIKE '%keyword%' OR
        tableB.col2 LIKE '%keyword%' OR
        tableC.col2 LIKE '%keyword%' OR
        .....

当我测试查询时，它的速度非常快，因为它只有大约100-150条记录.我想搜索包含关键字的任何字符串.过去的几个月里，该数据库变得庞大，包含50,000条记录.这次，我已经遇到了查询性能低下的问题.太低了.

When I tested the query, it was very fast because there was only around 100-150 records on it. I wanted to search for any string which contains the keyword. As months have past, the database grew huge containing 50,000 records. And this time, I already experiencing the low performance of the query. It was extremely low.

任何建议如何改善?我无法更改数据库，因为公司已经在使用它.

Any suggestions how can I improve it? I can't alter the database because it has already been used by the corporation already.

顺便说一句，我的桌子都是INNODB.

By the way, my tables were all INNODB.

推荐答案

这种类型的搜索称为全文搜索，您确实需要为其使用专用系统，而不是强制数据库不断进行表扫描.您可以将要搜索的所有文本基本交给搜索引擎，然后将其编入索引以进行快速搜索.

This type of search is call Full Text Search, and you really need to use specialized systems for it instead of forcing the database to constantly do table scans. You essnetially hand off all the text you want searched to a search engine, which then indexes it for quick search.

一种选择是 Apache Lucene .

这篇关于提高LIKE子句的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！