更快地查询一个 binary(1) 字段或 8 位字段? [英] Faster to query one binary(1) field or 8 bit fields?

问题描述

如果我在一条记录中有 8 个布尔值，并且在对表进行查询时总是使用这 8 个值，并且表中的数据是静态的(只读)，通过索引和获取匹配记录是否更快?查询像 binary(1) 这样的单字节字段，还是将 8 个单独的位列全部添加到索引中更好?

If I have 8 boolean values in a record and those 8 values are always used when doing a query against the table and the data in the table is static (read-only), is it faster to get matching records by indexing and querying against a one byte field like binary(1) or is it better to have 8 separate bit columns with all 8 added to an index?

推荐答案

单个位域上的索引将基本无用.位具有可怕的选择性，0 或 1，并且可能会被优化器忽略.8 个位字段上的 8 个索引将是优化器忽略的 8 个索引.

Indexes on single bit field will be basically useless. A bit has terrible selectivity, 0 or 1, and will be likely disregarded by the optimizer. 8 indexes on 8 bit fields are going to be 8 indexes disregarded by the optimizer.

字节列上的索引只是稍微更具选择性，有 256 个不同的值.但是，如果您正在寻找单个位模式，例如是第 3 位"，则无法将其表示为要寻找的单个值或范围.

An index on a byte column is only slightly more selective, with 256 distinct values. But if you are seeking individual bit patterns, like 'is bit 3 on', then there are no way to express this as a individual value to seek nor as a range.

结论是，无论您尝试什么，最终都会无论如何进行表扫描.

Conclusion is that no matter what you try, you will end up with a table scan anyway.

所以最好解释一下您的问题是什么，而不是您的解决方案，也许我们可以考虑更有效的方法.

So better explain what is your problem, not your solution, and perhaps we can think at something more efficient.

这篇关于更快地查询一个 binary(1) 字段或 8 位字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！