string字段与多个int/varchar字段的SQL性能
我正在尝试正确的数据库设计，我不确定我应该选择哪两个选项。将有大约20个bool值用于筛选(为了简单起见，示例中有4个)。
- prop1
- prop2
- prop3
- prop4
选项1)
每个支柱的int(1)或varchar(1)字段。查询的筛选部分可能如下
WHERE prop1=1 AND prop3=1 AND prop4=1选项2)
使用带有表示道具的字符的单个文本字段
- a: prop1
- b: prop2
- c: prop3
- d: prop4
F 224
然后，与选项1类似的查询的筛选部分如下
WHERE props LIKE '%a%' AND props LIKE '%c%' AND props LIKE '%d%'或者如果对字符进行了排序：
WHERE props LIKE '%a%' AND props LIKE '%cd%'我的想法是，选择2使添加新的道具更容易，所以我喜欢这个选项，但是类似的比较器的性能会比相等的比较器差吗？与多个int(1)或varchar(1)相比，使用单个文本有什么不同吗？还有其他我没想过的好处或缺点吗？
回答 2
Stack Overflow用户
发布于 2021-07-29 04:07:04
主要问题是，您是否能够比扫描整个表更快地运行。答案是“否”，除非一小部分布尔人可以用Index(es)单独处理。
您的WHERE bools LIKE '%a%c%d%'是ANDing的一个巧妙的技巧，它可以将任意数量的标志组合在一起。然而，它将需要查看每一行，而LIKE是稍微重量级的。
INT(1)需要4个字节加上开销。TINYINT是您想要的；它需要1字节，加上开销。
另一种技术是有多达64个bools的SET。这种编码有点笨拙，但相当有效率。
INT UNSIGNED (最多为32)或BIGINT UNSIGNED (最多为64)标志的实现类似于SET，并且占用最多8个字节。但编码相当笨拙。让我们用最小有效位对以0开头的位进行编号。
WHERE (bools & ( (1 << 0) | (1 << 2) | (1 << 3) ) ) =
( (1 << 0) | (1 << 2) | (1 << 3) )将检查0、2和3位是否都已设置。(这就像你对a，c，d.的测试)有了这种方法，各种发展战略和其他建议都是可能的。(在本例中，您可以预先计算这些位值- 13。或者使用一些文字：0b1101。)
INT中SET或bits的优点是每一行的“速度”。不过，所有行都必须进行测试。
因此，我建议对bools等进行分类，并决定哪些需要索引，哪些内容可以放到这个组合列中，或者在一个组合的JSON列中，用于非bools。
Stack Overflow用户
发布于 2021-07-28 20:28:43
从性能的角度来看，这两种选择都有缺点：
- 几乎不可能为20列建立适当的索引，不管它们的数据类型如何，因为查询可能因设置条件prop1、prop1 + prop2、prop2 + prop3、prop2 + prop4等而不同。因此，您需要大量的复合索引。此外，布尔值的索引通常表现不佳，因为基数较低(值只能为0或1，请参见here for example).
)。
另一方面，
- ，如带前导%的语句，也是性能问题。关闭%可以工作，但是引导%被确定为慢。
我在这里看到，您希望为记录分配一组属性，每个记录都可以有0.n赋值的propX，并且希望有效地过滤它。就像用户可以分配0,1,2，...n角色一样。在关系数据库中，它被归类为一种经典的多到多关系。如果您使用可能的props设置了一个表，并将这些表与您的记录连接到一个连接表(例如，detailed here )，那么您可以只使用有限数量的索引进行良好的查询。
https://stackoverflow.com/questions/68566615
复制相似问题
腾讯云开发者
