mysql-表是索引的，但解释说索引iSN＆＃x27;
我的查询非常复杂，非常慢（需要一个多小时），因此我正在努力提高其性能。使用解释，我可以看到其中一个表不使用索引，这很奇怪，因为它确实具有适当的索引。
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table_2 (null) ALL field_1,field2 (null) (null) (null) 8250 0.01 Using where; Using join buffer (Block Nested Loop)
如果我查看table_2，则用相同的“ possbile_keys”索引索引：
UNIQUE KEY `field1` (`field1`,`field2`),
在实际查询中，加入看起来像：
JOIN table_2
ON table_2.field1 = table_1.field1
AND table_2.field2 = table_1.field2
查看table_1 ：
KEY `field1` (`field1`,`field2`),
有人可以帮助我了解到底发生了什么，以及为什么查询不使用我的两个表上的匹配索引？
I have a really complex query that is incredibly slow (takes over an hour), so I'm working to improve its performance. Using explain, I can see that one of the tables isn't using the index, which is odd because it does have proper indexing.
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE table_2 (null) ALL field_1,field2 (null) (null) (null) 8250 0.01 Using where; Using join buffer (Block Nested Loop)
If I look at table_2, it is indexed with the same "possbile_keys" suggested by EXPLAIN:
UNIQUE KEY `field1` (`field1`,`field2`),
In the actual query, the join looks like:
JOIN table_2
ON table_2.field1 = table_1.field1
AND table_2.field2 = table_1.field2
Taking a look at the indexes on table_1:
KEY `field1` (`field1`,`field2`),
Can someone please help me understand what exactly is happening, and why the query isn't using the matching index on my two tables?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这意味着优化器使用了更好的方法。它将表2读为内存并构建了哈希。此通常会导致查询运行非常快。
向我们展示
显示表和。dixpl format = json select ...（如果您想进一步讨论），则显示create table注意： ，的订单，特别是
limitmay 之类的东西，意味着bnl是“错误的”路要走。join_buffer_size的设置部分控制BNL的使用。如果将其设置得太低，则无法使用BNL。 （如果设置得太高，您可能会用尽RAM - 一个更严重的问题。）That means that the Optimizer used an even better way. It read the table2 into memory and built a hash. This usually leads to the query running very fast.
Show us
SHOW CREATE TABLEfor both tables andEXPLAIN FORMAT=JSON SELECT ...if you wish to discuss this further.Note: Things like
GROUP BY,ORDER BY, and especiallyLIMITmay mean that BNL was the 'wrong' way to go.The setting of
join_buffer_sizepartially controls the use of BNL. If it is set too low, BNL cannot be used. (If it is set too high, you could run out of RAM -- a worse problem.)