MySQL：任何改进最佳匹配存储过程的建议

Question

对于我正在从事的项目，我必须扫描密钥以获得最佳匹配。只有这些扫描才会对我们的测试系统造成大量负载。有人知道我该如何改进吗？我们已经尝试过临时表和游标。

我们正在使用的代码示例：

SET keyLength = (LENGTH(key)-1);
WHILE keyLength >=1 DO
    SELECT COUNT(*) INTO resultCount FROM keytable
    WHERE I ScreeningKey = (SELECT SUBSTRING(key)-,1,keyLength));
    IF (0 < resultCount) THEN
        SELECT ScreeningKey INTO bestMatchScreeningKey FROM keytable 
        WHERE AND ScreeningKey = (SELECT SUBSTRING(key)-,1,keyLength));

        /*go to step4*/
        LEAVE Step4;
    END IF;
    SET keyLength = keyLength-1;
END WHILE;

Answer 1

不使用 SELECT COUNT(*)，只需使用 IF EXISTS。 IF EXISTS 可以在找到匹配项后立即停止扫描，而 COUNT(*) 则需要遍历整个表来统计计数。

所以，你最终会得到类似的结果：（我不太使用 MySQL，所以我不确定语法，我复制粘贴了你的语法，这对我来说看起来不太正确......）

SET keyLength = (LENGTH(key)-1);
WHILE keyLength >=1 DO
    IF EXISTS (SELECT * FROM keytable
               WHERE I ScreeningKey = (SELECT SUBSTRING(key)-,1,keyLength))) THEN
        SELECT SUBSTRING(key)-,1,keyLength INTO bestMatchScreeningKey;

        /*go to step4*/
        LEAVE Step4;
    END IF;
    SET keyLength = keyLength-1;
END WHILE;

Answer 2

像这样的事情怎么样？

对于未找到数据的每次迭代，它都会跳转到异常块（跳过 LEAVE），直到找到记录。

这将节省您为每次迭代进行 2 次选择的时间。

注意我添加了 LIMIT 1 来阻止 select 返回多行的可能性。

SET keyLength = (LENGTH(key)-1);
WHILE keyLength >=1 DO
BEGIN
    SELECT SUBSTRING(key)-,1,keyLength INTO bestMatchScreeningKey LIMIT 1;
    LEAVE Step4;
EXCEPTION
    WHEN no_data_found THEN
        NULL;
    WHEN others THEN
        RAISE;
END;
    SET keyLength = keyLength-1;
END WHILE;