blocks|key|349259|text|要使MySQL使用索引，必须显式地将int字段与值进行比较(例如，true，1)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|349260|select+*+from+myTable+where+myInt+=+true|code-block|syntax|javascript|349261|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For MySQL to use the index, you have to explicitly compare the int field to a value (e.g. true, 1).

<pre><code>select * from myTable where myInt = true
</code></pre>

blocks|key|693124|text|我不是数据库专家，但如果字段中只有两个可能的值，那么它不就不能实现在字段上建立索引的目的吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|693125|如果索引列中的所有字段都是唯一的，那么数据库引擎可以执行索引扫描来查找相关的行。如果只有两个可能的值，那么我看不出将该字段编入索引的目的。DB引擎必须执行与不存在索引时相同的操作。|693126|也许MySQL没有将其显示为可能的键，因为引擎放弃了在执行计划中使用索引的想法？|693127|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I'm not a database expert, but doesn't it defeat the purpose of having an index on the field if there is only two possible values of the field?

If all of the fields in the indexed column are unique, then the database engine can do an index scan to find the relevant rows. If there are only two possible values - then I don't see the purpose of having that field indexed. The DB engine has to do the same operation that it would if the index did not exist.

Perhaps MySQL is not showing it as a possible key because the engine has discarded the idea of using the index in the execution plan?

blocks|key|349286|text|在我看来，您的问题的SQL格式不对。您是否正在寻找列的非空值？这应该使用索引：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|349287|select+*+from+myTable+where+myInt+is+not+null|code-block|syntax|javascript|349288|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Your question's SQL looks malformed to me. Are you looking for non-null values of the column? This should use the index:

<pre><code>select * from myTable where myInt is not null
</code></pre>

blocks|key|705497|text|有很多因素需要考虑。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|705498|一个不应该进入它的因素是问题中使用的符号。当列是布尔值时，优化器应该将这些条件视为相同的：|705499|SELECT+*+FROM+MyTable+WHERE+MyInt;

SELECT+*+FROM+MyTable+WHERE+MyInt+!=+0;

SELECT+*+FROM+MyTable+WHERE+MyInt+IS+TRUE;

SELECT+*+FROM+MyTable+WHERE+MyInt+=+TRUE;|code-block|syntax|javascript|705500|可能还有其他类似的提法。其中第一个不是标准的SQL+(即使MyInt的类型是布尔的，其他的也是标准的)。但是优化器应该简单地将速记转换成适当的长表单，然后表现得就像长表单是用户编写的一样。(如果优化器没有这样做，那么优化器就会出现问题；在决定如何处理查询之前，应该将查询简化为规范形式。然而，即使是最好的优化器也常常存在盲点。学习如何避免这些是一种艺术形式，而且本质上是特定于DBMS的。)|705501|优化器在认为索引将提高查询性能时使用索引。当索引不能提高性能时，它将被忽略(如果优化器是好的)。有时，这取决于索引的统计数据是否是最新的。|705502|在数据仓库系统中，系统可以被设计和配置为非常快地对表进行顺序扫描；在这样的系统中，如果索引的选择性使使用索引的次数超过行数的25%25，那么进行全表扫描实际上比使用索引要快。|705503|想想看。当通过索引读取时，DBMS必须至少进行两次读取；它从索引页读取有关行的信息，然后从数据页读取行。|705504|有些DBMS只提供索引表.所有数据都在索引中。其他DBMS提供了一种机制，您可以说“索引在A、B、C列上是唯一的；但是，在数据中也包括D和E列”。然后，如果查询需要来自A、B、C、D或E(或任何组合)的数据，并且没有对其他列进行筛选，DBMS只需扫描索引，而不需要扫描表页。|705505|通常，一个页面会有许多索引行。但是，对于某些表，读取索引可能需要读取更多的数据，而不是读取行。考虑包含两个(4字节)整数ID值的原型多到多映射表。这需要数据页中的每行8字节，但索引可能需要4-8字节的开销(因为索引键条目存储两个ID值以及在磁盘上定位相应行所需的信息)。因此，即使索引扫描是“仅索引扫描”，也可能需要磁盘I/O是数据扫描的两倍。|705506|这几乎没有触及使用或不使用索引的可能原因的表面。|705507|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|16|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

There are lots of factors to consider.

One factor that should not enter into it is the notation used in the question. When the column is a boolean, then these conditions should be treated by the optimizer as identical:

<pre><code>SELECT * FROM MyTable WHERE MyInt;

SELECT * FROM MyTable WHERE MyInt != 0;

SELECT * FROM MyTable WHERE MyInt IS TRUE;

SELECT * FROM MyTable WHERE MyInt = TRUE;
</code></pre>

There may be other equivalent formulations. The first of these is not standard SQL (even if the type of MyInt is BOOLEAN; the others are standard. But the optimizer should simply transform the shorthand into the appropriate long form and then behave the same as if the long form was written by the user. (If the optimizer does not do this, then there is arguably a problem with the optimizer; the query should be reduced to a canonical form before deciding how to process the query. However, there are often blind spots in even the best optimizers. Learning how to avoid those is an art form, and inherently DBMS-specific.)

The optimizer uses an index when it believes the index will boost performance of the query. When the index won't boost performance, it is ignored (if the optimizer is any good). Sometimes, that depends on whether the statistics for the index are up to date.

In data warehousing systems, the system can be designed and configured to make sequential scans of the table very fast; in such systems, if the selectivity of an index is such that using it will pull more than as little as 25% of the rows, it can actually be quicker to do the full table scan than to use the index.

Think about it. When reading via an index, the DBMS has to do at least two reads; it reads the information about the row from the index page, and then it has to read the row from the data page.

Some DBMS provide index-only tables. All the data is in the index. Other DBMS provide a mechanism such that you can say "index is unique on columns A, B, C; however, include columns D and E in the data too". Then if the query requires data from A, B, C, D or E (or any combination) and there's no filtering on other columns, the DBMS only has to scan the index, not the table pages too.

Typically, you get many index rows to a page. However, for some tables, reading an index may require reading more data than reading the rows. Consider the archetypal many-to-many mapping table containing two (4-byte) integer ID values. That requires 8 bytes per row in the data pages, but the index probably requires 4-8 bytes of overhead (because the index key entry stores the two ID values plus the information needed to locate the corresponding row on disk). So, an index scan there may involve twice as much disk I/O as the data scan, even if the index scan is done 'index only'.

This is barely touching the surface of the possible reasons for using or not using an index.

<pre><code>select * from myTable where myInt
</code></pre>

will not show any possible_keys when explaining the query even though there is an index on myInt field.

Edit: 
 The index in question is not unique.

Why does MySQL not use an index on a int field that's being used as a boolean?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

select * from myTable where myInt即使在possible_keys字段上有索引，在解释查询时也不会显示任何myInt。编辑：所讨论的索引并不是唯一的。

问为什么MySQL不对作为布尔值的int字段使用索引？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么MySQL不对作为布尔值的int字段使用索引？EN