MySQL 最佳实践 : matching prefixes

Question

我有一个包含代码的表格和另一个包含前缀的表格。我需要匹配每个代码的(最长)前缀。

还有一个次级范围，我必须在其中限制前缀(这涉及引入其他表)。我认为在大多数情况下这并不重要，但这里有一个简化的(规范化的)方案(我必须设置 item.prefix_id):

group (id)
subgroup (id, group_id)
prefix (id, subgroup_id, prefix)
item (id, group_id, code, prefix_id)

把前缀的长度缓存在一个新的字段中索引就可以了。可以将 group_id 缓存在前缀表中(尽管组是相当小的表，但在大多数情况下我认为不会获得任何性能提升)。 item 表包含几十万条记录，前缀最多包含 500。

编辑:

抱歉，如果问题定义不够。当使用“前缀”这个词时，我是认真的，所以代码必须以实际的前缀开头。

subgroup
id   group_id
-------------
1    1
2    1
3    1
4    2

prefix
id   subgroup_id  prefix
------------------------
1    1            a
2    2            abc
3    2            123
4    4            abcdef

item
id   group_id     code    prefix_id
-----------------------------------
1    1            abc123  NULL
2    1            abcdef  NULL
3    1            a123    NULL
4    2            abc123  NULL

前缀列的预期结果是 (item.id, item.prefix_id):

(1, 2) 因为:子组 1、2、3 在组 1 下，代码 abc123 以前缀 a 和前缀 开头abc 和abc 是两者的logest，所以我们取abc 的id 为2 放入item.prefix_id.

(2, 2) 因为:即使前缀 {4}(即 abcdef)是最匹配的前缀，它的子组(即 4)在组 2 下，但项目在第 1 组，因此我们可以从子组 1、2、3 中进行选择，abc 仍然是三个可能前缀中最匹配的。

(3, 1) 因为:a 是最匹配的。

(4, NULL) 因为:第 4 项在组 2 下，组 2 下唯一的前缀是 abcdef，它与 abc123 不匹配(因为 abc123 不以 abcdef 开头。

但正如我所说，整个摸索过程不是问题的本质部分。我主要关心的是将具有可能前缀的表与字符串表匹配，以及如何以最佳方式进行匹配。 (最好的意思是可读性、可维护性和性能之间的最佳权衡 - 因此标题中的“最佳实践”)。

目前我正在做类似的事情:

UPDATE item USE INDEX (code3)
    LEFT JOIN prefix ON prefix.length=3 AND LEFT(item.code,3)=prefix.prefix
    LEFT JOIN subgroup ON subgroup.id=prefix.subgroup_id
WHERE subgroup.group_id == item.group_id AND
    item.segment_id IS NULL

其中 code3 是一个 KEY code3 (segment_id, group_id, code(3))。 - 相同的逻辑以 1、2、3 和 4 作为长度重复。它看起来非常有效，但我不喜欢其中存在重复(单个操作有 4 个查询)。 - 当然，这是在前缀的最大长度为 4 的情况下。

到目前为止，感谢大家分享您的想法。

Answer 1

It is allright to cache the group_id in prefix table.

所以让我们在表 prefix 中创建列 group_id 并用适当的值填充该列。我假设您知道如何执行此操作，所以让我们进入下一步。

我们将从这个复合索引中获得的最大性能优势:

ALTER TABLE `prefix` ADD INDEX `c_index` (
    `group_id` ASC, 
    `prefix` ASC
);

UPDATE 语句:

UPDATE item i
SET 
    prefix_id = (
        SELECT p.id
        FROM prefix p USE INDEX (`c_index`)
        WHERE 
            p.group_id = i.group_id AND 
            p.prefix IN (
                LEFT(i.code, 4), 
                LEFT(i.code, 3), 
                LEFT(i.code, 2), 
                LEFT(i.code, 1)
            )                
        ORDER BY LENGTH(p.prefix) DESC
        LIMIT 1        
    )

在这个例子中，我假设前缀是可变长度的 {1,4}。我决定一起使用 IN 子句而不是 LIKE 来获得 c_index 的全部好处。