索引MYSQL中的位字段 [英] index a bit field in MYSQL

问题描述

更新的问题：

假设我感兴趣的数据只是那些字段= 1的数据，并且数据的实际比率字段是1对0是非常小的（例如1％）在这种情况下，
将索引字段有益于我的选择where field = 1 query？

updated question:
suppose the data I'm interested in is only those with field=1 and the actual ratio of data wheere field is 1 vs 0 is very small(eg. 1%) in this case, would index the field benefit my select where field =1 query?

原始问题：

我有一个将具有0或1值的int字段，将索引此字段加速选择查询，例如：

original question:
I have a int field that will have either 0 or 1 value, would indexing this field speed up select queries such as:

select * from xxx where field=1;

推荐答案

一般来说，没有。索引时，双状态字段不会加快查询速度，因为您必须平均查看一半的行。您希望索引条目具有选择性 - 索引中的给定条目应仅代表可能值的一小部分（例如，小于10％，最好是百分比的分数）。然后使用索引忽略表中的大多数数据，这将为您带来性能优势。

Generally speaking, no. A bi-state field doesn't speed up queries when indexed because you have to look at half the rows on average. You want your index entries to be selective - a given entry in the index should represent only a small percentage of the possible values (say, less than 10%, preferably into the fractions of a percent). Then using the index ignores most of the data in the table, which is what gives you a performance benefit.

某些DBMS支持位图索引。他们可以提供帮助，但是你仍然会遇到选择性问题。

Some DBMS support bitmap indexes. They can help, but you still run into the problem of selectivity.

更新后的问题表示值的数量值1将很小（小于百分之一）;一个指数现在会给你一个好处吗？

The updated question says that the number of values with value 1 will be small (less than one percent); will an index give you a benefit now?

答案是：

• 对于那些指定值为1的查询，然后是，如果优化程序实际使用了索引，则列上的索引可以提供好处。您可能需要调整DBMS以使其意识到索引倾向于将其用于值为1的查询;这往往是特定于DBMS的，但更新各种伪装的统计数据是游戏的名称，也可能使用SQL查询中的提示。当然，如果优化器从不使用索引，那么它仍然没有提供任何好处 - 优化器可能会决定其他索引以某种方式帮助它。

• For those queries where you specify that the value is 1, then yes, an index on the column could provide a benefit, provided that the optimizer actually makes use of the index. You may need to tweak the DBMS to make it realize that the index is skewed in favour of using it with queries where the value is 1; this tends to be DBMS-specific, but updating statistics in various guises is the name of the game, possibly using hints in the SQL queries too. Of course, if the optimizer never uses the index, then it still provides no benefit - and the optimizer may decide that other indexes help it more in some way.

对于值为0的查询，不应使用索引。但是，有可能DBMS将继续维持0值的索引 - 即使它永远不应该使用它们。这将是一个不寻常的DBMS，可以被命令'只为这个列索引除零以外的值'，即使这将是非常有益的。

For those queries where the value is 0, the index should not be used. The chances are, though, the the DBMS will continue to maintain the index for the 0 values too - even though it should never use them. It would be an unusual DBMS that could be commanded 'only index this column for the values other than zero', even though that would be very beneficial.

所以 - 这取决于。它取决于查询，它取决于优化器。

So - it depends. It depends on the queries, and it depends on the optimizer.

同样注意复合索引 - 在其他一些通常使用的列上，然后是位字段可能会很好地提供一些好处。因此，如果您几乎总是选择日期范围，那么日期和位字段列的复合索引（可能按此顺序）应为您提供良好的索引。

Note too that a composite index - on some other customarily used columns and then the bit-field may well provide some benefit. So, if you almost always select on a date range, then a composite index on the date and bit-field columns (probably in that order) should provide you with a good index.

这篇关于索引MYSQL中的位字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！