MySQL SELECT IN() 语句和 InnoDB 索引失败
我有一个执行此操作的查询：
SELECT `threaded_comments`.* FROM `threaded_comments` WHERE `threaded_comments`.`parent_id` IN (4, 5)
我还在parent_id 列上设置了一个索引。
当我对查询执行 EXPLAIN SELECT 检查时，我得到以下信息：
`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`
'SIMPLE', 'threaded_comments', 'ALL', 'COMMENT_PARENT', NULL, NULL, NULL, 3, 'Using where'
看起来索引的 where 子句没有使用任何键。但是，当我执行简单的parent_id = 4 时，它就起作用了。但是当我执行parent_id=4 或parent_id=5 时，它会显示相同的消息。
我发现这与 innoDB 数据库表类型有关。 MySQL 似乎不喜欢在特定数据库引擎上执行 IN 操作。这可能是 EXPLAIN 工具的问题还是 InnoDB 缺少的东西？
I have a query that does this:
SELECT `threaded_comments`.* FROM `threaded_comments` WHERE `threaded_comments`.`parent_id` IN (4, 5)
I've also setup an INDEX on the parent_id column.
When I do a EXPLAIN SELECT check on the query I get the following information:
`select_type`,`table`,`type`,`possible_keys`,`key`,`key_len`,`ref`,`rows`,`Extra`
'SIMPLE', 'threaded_comments', 'ALL', 'COMMENT_PARENT', NULL, NULL, NULL, 3, 'Using where'
Looks like no keys are being used for the where clause of the index. However, when I do a simple parent_id = 4 then it works. But when I do parent_id=4 or parent_id=5 then it brings up the same message.
I figured out that it has todo with the innoDB database table type. MySQL seems to not like it when there are IN operations performed on that specific database engine. Could this be a problem with the EXPLAIN tool or it is something that InnoDB is missing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
不，它与 InnoDB 无关。您的 EXPLAIN 计划显示查询能够使用索引，但 MySQL 优化器决定不使用它。所选择的决定取决于许多因素。但后来当你的表变大时，优化器如果认为有用的话可以做出不同的决定。如果您想在该查询上显式使用索引，那么您可以尝试 力量指数。
No, it has nothing to do with InnoDB. Your EXPLAIN plan shows that the query is able to use the index, but MySQL optimizer decided not to use it. The selected decision depends on many factors. But later when your table grows up, the optimizer can make a different decision if it thinks it's useful. If you want to explicitly use an index on that query then you may try to force index.
看来表中的行数太少，最好进行全面扫描。
It seems that you have too less rows in tables and full scan is preferable.
指数并不总是有好处。
来自文档：
在您的情况下，对于
parent_id = 4来说索引被认为很好，但对于parent_id IN (4, 5)则首选表扫描Index is not always a benefit.
From the documentation:
In your case, index is considered fine for
parent_id = 4but a table scan is preferred forparent_id IN (4, 5)