MySQL 索引布尔字段

Indexing boolean fields

提问by nickf

This is probably a really stupid question, but is there going to be much benefit in indexing a boolean field in a database table?

这可能是一个非常愚蠢的问题，但是在数据库表中索引布尔字段会有很多好处吗？

Given a common situation, like "soft-delete" records which are flagged as inactive, and hence most queries include WHERE deleted = 0, would it help to have that field indexed on its own, or should it be combined with the other commonly-searched fields in a different index?

给定一个常见的情况，就像它被标记为非活动状态“软删除”的记录，因此大多数查询包括WHERE deleted = 0，它是否有助于有场索引自身，还是应该与其他组合一般搜索的领域中不同的指数？

采纳答案by Mark Canlas

No.

不。

You index fields that are searched upon and have high selectivity/cardinality. A boolean field's cardinality is obliterated in nearly any table. If anything it will make your writes slower (by an oh so tiny amount).

您索引搜索的字段并具有高选择性/基数。布尔字段的基数几乎在任何表中都会被删除。如果有的话，它会使您的写入速度变慢（非常小）。

Maybe you would make it the first field in the clustered index if every query took into account soft deletes?

如果每个查询都考虑软删除，也许您会将其设为聚集索引中的第一个字段？

回答by jhlllnd

What is about a deleted_at DATETIME column? There are two benefits.

什么是deleted_at DATETIME 列？有两个好处。

1. If you need an unique column like name, you can create and soft-delete a record with the same name multiple times (if you use an unique index on the columns deleted_at AND name)
2. You can search for recently deleted records.

1. 如果您需要像 name 这样的唯一列，您可以多次创建和软删除同名记录（如果您在列被删除的和名称上使用唯一索引）
2. 您可以搜索最近删除的记录。

You query could look like this:

您的查询可能如下所示：

SELECT * FROM xyz WHERE deleted_at IS NULL

回答by Brimstedt

I think it would help, especially in covering indices.

我认为这会有所帮助，尤其是在涵盖指数方面。

How much/little is of course dependent on your data and queries.

多少/多少当然取决于您的数据和查询。

You can have theories of all sorts about indices but final answers are given by the database engine in a database with real data. And often you are surprised by the answer (or maybe my theories are too bad ;)

您可以拥有关于索引的各种理论，但最终答案是由数据库引擎在具有真实数据的数据库中给出的。通常你会对答案感到惊讶（或者我的理论太糟糕了；）

Examine the query plan of your queries and determine if the queries can be improved, or if the indices can be improved. It's quite simple to alter indices and see what difference it makes

检查查询的查询计划并确定查询是否可以改进，或者索引是否可以改进。改变索引很简单，看看它有什么不同

回答by Adriaan Stander

I think it would help if you were using a view (where deleted = 0) and you are regularly querying from this view.

我认为如果您使用视图（其中已删除 = 0）并且您经常从该视图进行查询，这会有所帮助。

回答by umar

i think if your boolean field is such that you would be referring to them in many cases, it would make sense to have a separate table, example DeletedPages, or SpecialPages, which will have many boolean type fields, like is_deleted, is_hidden, is_really_deleted, requires_higher_useretc, and then you would take joins to get them.

我认为，如果你的布尔场就是这样，你会提到他们在许多情况下，它将使意义有一个单独的表，例如DeletedPages，或SpecialPages，这将有许多布尔类型的字段，如is_deleted，is_hidden，is_really_deleted，requires_higher_user等，那么你会采取连接来获得它们。

Typically the size of this table would be smaller and you would get some advantage by taking joins, especially as far as code readability and maintainability is concerned. And for this type of query:

通常这个表的大小会更小，你会通过连接获得一些优势，特别是就代码可读性和可维护性而言。对于这种类型的查询：

select all pages where is_deleted = 1

select all pages where is_deleted = 1

It would be faster to have it implemented like this:

像这样实现它会更快：

select all pages where pages 
inner join DeletedPages on page.id=deleted_pages.page_id 

I think i read it somewhere about mysql databases that you need a field to at least have cardinality of 3 to make indexing work on that field, but please confirm this.

我想我在某处读到它关于 mysql 数据库，您需要一个字段至少具有 3 的基数才能在该字段上进行索引工作，但请确认这一点。

回答by Rondo

If you are using database that supports bitmap indexes (such as Oracle), then such an index on a boolean column will much more useful than without.

如果您正在使用支持位图索引的数据库（例如 Oracle），那么布尔列上的此类索引将比不使用时有用得多。