如何在mysql查询中高效使用索引

2021-12-21 00:00:00 indexing performance mysql

我的数据库在 mysql v5.x 上运行.我有一个包含 5 列的表 T1，列 C1 是主键.C1 是 varchar(20) 类型.它包含大约 2000 行，其值如下:

My db is running on mysql v5.x. I have a table T1 with 5 columns and column C1 is the primary key. C1 is of type varchar(20). It contains about 2000 rows with values like:

fxg
axt3
tru56
and so on..

现在我的应用程序的工作是读取输入数据并查找输入数据的起始模式是否与表 T1 的 C1 列中的起始模式类似.例如:我的输入可能显示为:

Now my application's job is to read input data and find if the input data has a starting pattern similar to that found in column C1 in table T1. For example: my input may appear as:

    trx879478986
    fxg87698x84
    784xtr783utr
    axt3487ghty
... and so on

因此，对于上述输入，我必须为 'fxg87698x84' 和 'axt3487ghty' 返回 true，对其他人返回 false.我使用的查询是:

So for the above input, I have to return true for 'fxg87698x84' and 'axt3487ghty' and false for others. The query I use is:

select 1 from T1 where (? like concat(C1,'%'));
note: the ? is replaced by the input value got from the application.

问题是我的输入很大(30 分钟内要处理大约 100 万条记录)而且我的查询速度不够快.关于如何重写查询或强制它使用索引的任何想法?即使我必须使用不同的对象结构，如果有帮助，我也可以做到.所以任何帮助将不胜感激.谢谢.

The issue is my input is huge (about 1 million records to be processed in 30 minutes) and my query is not fast enough. Any ideas on how to re-write the query or force it to use indexes? Even if I have to use a different object structure, I can do it, if that helps. So any help will be appreciated. Thx.

推荐答案

您可以尝试使用 Top-N 查询来查找第一个候选，然后将该候选仅应用于实际模式:

you could try a Top-N query to find the first candidate, and then apply that candidate only to the actual pattern:

select 1 
  from (select c1 
          from junk 
         where c1 <= 'fxg87698x84'
         order by c1 desc limit 1) tmp 
 where 'fxg87698x84' like concat(c1, '%');

top-n 查询应该使用 c1 上的常规索引.

the top-n query should use a regular index on c1.

编辑:在我的博客中更详细地解释了这一点:http://blog.fatalmind.com/2010/09/29/finding-the-best-match-with-a-top-n-query/

EDIT: Explained that in more detail in my blog: http://blog.fatalmind.com/2010/09/29/finding-the-best-match-with-a-top-n-query/

相关文章