索引布尔字段是否有任何性能提升? [英] 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

正如 Michael Durrant 提到的，它也会使写入速度变慢.

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屋！