索引布尔字段是否有任何性能提升？ [英] Is there any performance gain in indexing a boolean field?

问题描述

我即将编写一个包含 WHERE isok = 1 的查询。顾名思义， isok 是一个布尔字段（实际上是 TINYINT（1）UNSIGNED ，设置为0或1根据需要）。

I'm just about to write a query that includes a WHERE isok=1. As the name implies, isok is a boolean field (actually a TINYINT(1) UNSIGNED that is set to 0 or 1 as needed).

索引此字段是否有任何性能提升？引擎（在这种情况下是InnoDB）是否会更好或更差地查找索引？

Is there any performance gain in indexing this field? Would the engine (InnoDB in this case) perform better or worse looking up the index?

推荐答案

不是真的。你应该把它想象成一本书。如果一本书中只有3种单词并且您将所有单词编入索引，那么您将拥有与普通页面相同数量的索引页面。

Not really. You should think about it like a book. If there were only 3 kinds of words in a book and you index all of them, you would have the same number of index pages as normal pages.

如果一个值的记录相对较少，则会有性能提升。例如，如果您有1000条记录，其中10条为TRUE，那么如果您使用 isok = 1

There would be a performance gain if there are relatively few records of one value. For example, if you have 1000 records and 10 of them are TRUE, then it would be useful if you searching with isok = 1

正如迈克尔·达兰特所提到的，它也使得写入速度变慢。

As Michael Durrant mentioned, it also makes writes slower.

编辑：可能重复：索引布尔字段

这里解释说即使你有一个索引，如果你有太多的记录它反正不使用索引。
MySQL不使用索引当检查= 1时，但使用= 0

Here it explains that even if you have an index, if you have too many records it doesn't use the index anyways. MySQL not using index when checking = 1 , but using it with = 0

这篇关于索引布尔字段是否有任何性能提升？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！