mysql - 优化短文本比较-6ren

mysql - 优化短文本比较

转载作者：可可西里更新时间：2023-11-01 07:36:18

我有 2 个表 qs 和 local。qs 有 2 列(实际上是从其他几列构建的)是我需要做的比较的一部分:

f1     | t1   
abcdaa | abcdbb

local 只有一列是比较的一部分:

rangeA
abcd

我正在尝试在 qs 中查找在 local 中没有匹配子字符串的条目

我已经尝试了十几种不同的方法，但我一定遗漏了一些东西，因为它花费了不寻常的时间。

这是迄今为止我发现的最快的方法:

CREATE TEMPORARY TABLE `tempB` SELECT f1, t1,
LEFT(f1,2) AS l2,LEFT(f1,3) AS l3,LEFT(f1,4) AS l4,LEFT(f1,5) AS l5,LEFT(f1,6) AS l6,LEFT(f1,7) AS l7,LEFT(f1,8) AS l8,
LEFT(f1,9) AS l9,LEFT(f1,10) AS l10,LEFT(f1,11) AS l11,LEFT(f1,12) AS l12,LEFT(f1,13) AS l13,
LEFT(t1,2) AS lt2,LEFT(t1,3) AS lt3,LEFT(t1,4) AS lt4,LEFT(t1,5) AS lt5,LEFT(t1,6) AS lt6,LEFT(t1,7) AS lt7,LEFT(t1,8) AS lt8,
LEFT(t1,9) AS lt9,LEFT(t1,10) AS lt10,LEFT(t1,11) AS lt11,LEFT(t1,12) AS lt12,LEFT(t1,13) AS lt13 FROM 
 (SELECT  CONCAT(c1,n1,s1) AS f1, CONCAT(c1,n1,s2) AS t1 FROM qs WHERE c1 ='a')tab0 ORDER BY f1 ASC;
CREATE TEMPORARY TABLE `tempB2` SELECT rangeA FROM local WHERE rangeA LIKE 'a%' ORDER BY rangeA ASC;
CREATE TEMPORARY TABLE `tempB3` SELECT rangeA AS rangeAA FROM local WHERE rangeA LIKE 'a%' ORDER BY rangeA ASC;

SELECT f1,t1, rangeA, rangeAA FROM tempB
LEFT JOIN tempB2 ON rangeA IN(l2,l3,l4,l5,l6,l7,l8,l9,l10,l11,l12,l13) 
LEFT JOIN tempB3 ON rangeAA IN(lt2,lt3,lt4,lt5,lt6,lt7,lt8,lt9,lt10,lt11,lt12,lt13) 
WHERE rangeA IS NULL OR rangeAA IS NULL

创建临时表的速度很快，一次从一个字符开始(在本例中为“a”)显着减小了数据集的大小，但即使每个只有几十万行，这仍然非常非常慢临时表。我试过将 f1 和 t1 与

一起使用

 ON f1 LIKE CONCAT (rangeA,'%')

但这似乎更慢。

还有其他想法吗？

请注意，rangeA 的长度至少为 2 个字符，最多为 13 个字符。因此是 LEFT。

示例数据:

qs :

 c1  |  n1  | s1  | s2
 ab  | cd   | aa  | bb
 bb  | bbb  | bb  | bc
 cbc | cc   | cdd | ddd
 ddd | e    | ddf | def

本地:

rangeA
abcd
bdddd
cbcccdd
dddedd

预期结果:

  f1       | t1       | f1match | t1match
  bbbbbbb  | bbbbbbc  | NULL    | NULL
  cbccccdd | cbcccddd | NULL    | cbcccdd
  dddeddf  | dddedef  | dddedd  | NULL

最佳答案

谢谢Paul Spiegel for making this work .

让我们设置一些测试数据。

mysql> select * from qs;
+----+---------------+-------------------+
| id | f1            | t1                |
+----+---------------+-------------------+
|  6 | match1        | no match          |
|  7 | match1        | match2            |
|  8 | foo match1    | match1 bar        |
|  9 | no match      | abc match2 123    |
| 10 | no match      | no match          |
| 11 | also no match | again not a match |
+----+---------------+-------------------+

mysql> select * from local;
+--------+
| rangeA |
+--------+
| match1 |
| match2 |
+--------+

而且我们只期望 f1 和 t1 都不匹配本地任何行的那些行。

+----+---------------+-------------------+
| id | f1            | t1                |
+----+---------------+-------------------+
| 10 | no match      | no match          |
| 11 | also no match | again not a match |
+----+---------------+-------------------+

更新:索引 qs(f1,t1) 和 local(rangeA) 将有助于提高性能。

create index index_qs_fields on qs(f1,t1);
create index index_local_rangeA on local(rangeA);

instr在字符串中找到一个子字符串，这简化了很多事情。

我们可以用 left excluding join 来做到这一点.即只获取左边(qs)右边没有匹配(local)的行。

我们进行正常的左连接来检查匹配项。

select qs.*, rangeA
from qs
left join local on 
    instr(f1,rangeA) or
    instr(t1,rangeA)

+----+---------------+-------------------+--------+
| id | f1            | t1                | rangeA |
+----+---------------+-------------------+--------+
|  1 | match1        | no match          | match1 |
|  2 | match1        | match2            | match1 |
|  3 | foo match1    | match1 bar        | match1 |
|  2 | match1        | match2            | match2 |
|  4 | no match      | abc match2 123    | match2 |
|  5 | no match      | no match          | NULL   |
|  6 | also no match | again not a match | NULL   |
+----+---------------+-------------------+--------+

并通过仅过滤那些根本不匹配的那些将其变成排除连接。

select qs.*, rangeA
from qs
left join local on 
    instr(f1,rangeA) or
    instr(t1,rangeA)
where rangeA is null

+----+---------------+-------------------+
| id | f1            | t1                |
+----+---------------+-------------------+
|  5 | no match      | no match          |
|  6 | also no match | again not a match |
+----+---------------+-------------------+

dbfiddle

更新:local 中的大量条目可能会使速度变慢。我们可以尝试通过将所有匹配项连接到一个正则表达式中来优化它。这可能更快。

我们可以使用 group_concat 构造我们的正则表达式将所有匹配项合并为一个正则表达式。

select group_concat(rangeA separator '|')
into @range_re
from local;

select qs.*
from qs
where not f1 regexp(@range_re) and not t1 regexp(@range_re);

请注意，您需要小心转义匹配中的正则表达式字符。

接下来是太复杂的原始答案。

<罢工>这告诉我们 qs 中的哪些条目不匹配 local 中的条目。

select qs.id, f1, t1, rangeA from qs left join local on 1=1 where instr(f1,rangeA) = 0 and instr(t1,rangeA) = 0; +----+---------------+-------------------+--------+ | id | f1 | t1 | rangeA | +----+---------------+-------------------+--------+ | 6 | match1 | no match | match2 | | 8 | foo match1 | match1 bar | match2 | | 9 | no match | abc match2 123 | match1 | | 10 | no match | no match | match1 | | 10 | no match | no match | match2 | | 11 | also no match | again not a match | match1 | | 11 | also no match | again not a match | match2 | +----+---------------+-------------------+--------+

但我们想要那些不匹配 local 的 all 的。我们可以通过计算一行在我们的不匹配列表中出现的次数来做到这一点。

select qs.id, f1, t1, count(id) from qs left join local on 1=1 where instr(f1,rangeA) = 0 and instr(t1,rangeA) = 0 group by qs.id; +----+---------------+-------------------+-----------+ | id | f1 | t1 | count(id) | +----+---------------+-------------------+-----------+ | 6 | match1 | no match | 1 | | 8 | foo match1 | match1 bar | 1 | | 9 | no match | abc match2 123 | 1 | | 10 | no match | no match | 2 | | 11 | also no match | again not a match | 2 | +----+---------------+-------------------+-----------+

然后只选择计数与匹配数相同的那些。

mysql> select qs.id, f1, t1 from qs left join local on 1=1 where instr(f1,rangeA) = 0 and instr(t1,rangeA) = 0 group by qs.id having count(id) = (select count(*) from local); +----+---------------+-------------------+ | id | f1 | t1 | +----+---------------+-------------------+ | 10 | no match | no match | | 11 | also no match | again not a match | +----+---------------+-------------------+

<罢工> dbfiddle