MySQL最佳实践：匹配前缀

概述我有一个带代码的表和另一个带前缀的表.我需要匹配每个代码的(最长)前缀.还有一个辅助范围,我必须限制前缀(这涉及引入其他表).我不认为这在大多数情况下都很重要,但这里是一个简化的(规范化的)方案(我必须设置item.prefix_id)：group (id) subgroup (id, group_id) prefix (id, subgroup_id, p

我有一个带代码的表和另一个带前缀的表.我需要匹配每个代码的(最长)前缀.

还有一个辅助范围,我必须限制前缀(这涉及引入其他表).我不认为这在大多数情况下都很重要,但这里是一个简化的(规范化的)方案(我必须设置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条.

编辑：

对不起如果问题定义不够.当使用单词“prefix”时,我实际上是指它,所以代码必须以实际前缀开头.

subgroupID   group_ID-------------1    12    13    14    2prefixID   subgroup_ID  prefix------------------------1    1            a2    2            abc3    2            1234    4            abcdefitemID   group_ID     code    prefix_ID-----------------------------------1    1            abc123  NulL2    1            abcdef  NulL3    1            a123    NulL4    2            abc123  NulL

前缀列的预期结果是(item.ID,item.prefix_ID)：

(1,2)因为：子组1,2,3在组1下,代码abc123以前缀a开头,前缀abc和abc是两者的最大值,所以我们取ab的ID为2并将其放入item.prefix_ID.

(2,2)因为：即使前缀{4}(即abcdef)是最喜欢的匹配前缀,它的子组(也就是4)在组2下,但是项目在组1下,所以我们可以从子组中选择1,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_IDWHERE subgroup.group_ID == item.group_ID AND    item.segment_ID IS NulL

其中code3是KEY code3(segment_ID,code(3)). – 同样的逻辑以1,3和4作为长度重复.它似乎非常有效,但我不喜欢它中存在重复(单个 *** 作的4个查询). – 当然这是前缀的最大长度为4的情况.

感谢大家分享您的想法.

最佳答案

It is allright to cache the group_ID in prefix table.

因此,让我们在表前缀中创建列group_ID,并用适当的值填充列.我假设您知道如何做到这一点,那么让我们进入下一步.

我们将从这个综合指数中获得的最大性能优势：

ALTER table `prefix` ADD INDEX `c_index` (    `group_ID` ASC,`prefix` ASC);

和UPDATE语句：

UPDATE item iSET     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),2),1)            )                        ORDER BY LENGTH(p.prefix) DESC        liMIT 1            )

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

以上是内存溢出为你收集整理的MySQL最佳实践：匹配前缀全部内容，希望文章能够帮你解决MySQL最佳实践：匹配前缀所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。