使用Like运算符提高查询性能

I'm using a search query to lookup a skus in my DB and I'm having a bad performance return from TrueSight report.

我正在使用搜索查询在我的数据库中查找skus,而我从TrueSight报告中返回的性能不佳。

Not sure if there a way I could do for better performance.

不确定我是否有办法提高性能。

my query is:

我的查询是:

public SkuQuery(final DataSource ds) {
    super(ds, "SELECT sku, description1, buyer, analyst FROM descrip WHERE item_status != 3 AND sku like ? ");
    declareParameter(new SqlParameter(Types.VARCHAR));
    compile();
}

Because of the sku like statement, it does a full tables can of 420,000 rows each time it runs. If you rewrite this to do an exact search on sku, which is the primary key, it runs in .016 seconds.

由于sku like语句,它每次运行时都会生成420,000行的完整表。如果你重写这个来在sku上做一个精确的搜索,这是主键,它在.016秒内运行。

The pattern I'm using is "sku like '%5052024%';"

我正在使用的模式是“sku like'%5052024%';”

2 个解决方案

#1

It's not clear from your question if you could use = instead of LIKE. Are users not required to enter the whole SKU number?

你的问题不清楚你是否可以使用=而不是LIKE。用户是否不需要输入整个SKU号码?

There is no way to improve the performance of LIKE when you have a wildcard at the start of your pattern.

如果在模式开头有通配符,则无法提高LIKE的性能。

For example, if I ask you to read the telephone book and find all people whose name contains the characters "land" anywhere in their name — beginning, middle, or end — you can't take advantage of the fact that the telephone book is sorted by name. You will have to read the whole book carefully to find the names matching the pattern.

例如,如果我要求您阅读电话簿并找到名字中包含“土地”字符的所有人,无论他们的名字是开头,中间还是结尾,您都无法利用电话簿的优势。按名称排序。您必须仔细阅读整本书,以找到与模式匹配的名称。

That's how wildcards work in LIKE conditions. Unless your pattern has no wildcard at the start, it's forced to do a table-scan. It can't use an index.

这就是通配符在LIKE条件下的工作方式。除非您的模式在开始时没有通配符,否则它将被强制执行表扫描。它不能使用索引。

There are FULLTEXT indexes in MySQL that allows you to match words within one or more string columns. But it only matches whole words, it can't match patterns that are parts of words.

MySQL中有FULLTEXT索引,允许您匹配一个或多个字符串列中的单词。但它只匹配整个单词,它不能匹配作为单词部分的模式。

You might like my presentation Full Text Search Throwdown in which I compared several different fulltext indexing solutions.

您可能会喜欢我的演示文稿全文搜索功能,我在其中比较了几种不同的全文索引解决方案。

#2

Use the query like

使用像这样的查询

super(ds, "SELECT sku, description1, buyer, analyst FROM descrip WHERE item_status != 3");

In the java code, after getting the data in the sku, loop over it checking like

在java代码中,在sku中获取数据后,循环检查就好了

while([RESULT_SET-CONDITION]){
    if(sku.contains(5052024)){
        //Your Code
    }
}

Although this way will increase the data in the main memory as you are loading more data there.

虽然这种方式会增加主内存中的数据,因为您在那里加载更多数据。

#1