如何优化这个查询？
查询：
select id,
title
from posts
where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5
2,55)
解释计划：
mysql> explain select id,title from posts where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5
2,55);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | posts | ALL | PRIMARY | NULL | NULL | NULL | 30 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)
id是posts表的主键。
Query:
select id,
title
from posts
where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5
2,55)
Explain plan:
mysql> explain select id,title from posts where id in (23,24,60,19,21,32,43,49,9,11,17,34,37,39,46,5
2,55);
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | posts | ALL | PRIMARY | NULL | NULL | NULL | 30 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)
id is the primary key of posts table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了添加其他索引（例如
之外，似乎没什么可做的......
事实上，即使有这样的索引可用，MySQL 可能决定进行表扫描，就像这里的情况一样（“ALL”类型）。原因可能是表可能具有相对较少的行（与查询将返回的估计行数相比），因此按顺序“读取”表会更有效，丢弃非使用索引间接匹配行，而不是“到处希望”。
Other than adding other indexes, such as
there seem to be little to be done...
In fact, even if there were such indexes available, MySQL may decide to do a table scan, as is the case here ("ALL" type). The reason may be the table may has a relative few rows (compared with the estimated number of rows the query would return), and it is therefore more efficient to "read" the table, sequentially, discarding non matching rows as we go, rather than "hoping all over the place", with an index indirection.
我认为这没有任何问题。如果您需要在列表中进行选择，那么“IN”是正确的方法。您没有选择不必要的信息，并且您选择的内容是一个键，它可能已建立索引。
I don't see any problem with it. If you need to select against a list, then "IN" is the right way to do it. You're not selecting unnecessary information, and the thing you're selecting against is a key, which is presumably indexed.