非常小的 MySQL 表会忽略索引吗? [英] Do very small MySQL tables ignore indexes?

问题描述

开启log_queries_not_using_indexes后，我注意到一个查询正在快速填满慢查询日志:

After turning on log_queries_not_using_indexes, I noticed that one query was rapidly filling up the slow query log:

选择 abc.* FROM abc在哪里 abc.id不在(从 zyx 中选择不同的 abc_id哪里 id = 12345);

abc 很小，只有 3 行数据.zyx比较大，有10万多行数据.

abc is very small, just 3 rows of data. zyx is relatively large with over 100,000 rows of data.

abc.id 有一个索引，但是当我 EXPLAIN 查询时，该索引未在 key 或 下列出>possible_keys.这解释了为什么查询显示在慢日志中，但我的问题是，为什么不使用索引?

abc.id has an index, but when I EXPLAIN the query, the index isn't listed under either key nor possible_keys. This explains why the query is showing in the slow log, but my question is, why isn't it using the index?

简而言之，我有两个问题:

In short, I have two questions:

1. 非常小的表会忽略索引吗?我明白为什么，对 3 行数据使用索引并没有节省太多时间.
2. 如果是这样，我怎样才能防止这个查询淹没我的慢查询日志?

感谢您的宝贵时间！:)

Thank you for your time! :)

其他信息(如果需要):

Additional information, if needed:

我已经运行了 ANALYZE TABLE abc，因为我读过有时会解决这个问题.添加索引后，我还重新启动了 MariaDB.

I have run ANALYZE TABLE abc as I've read sometimes fixes the issue. I have also restarted MariaDB since adding the index.

更多EXPLAIN:select_type=PRIMARY, table=abc, type=ALL, possible_keys=NULL, key=NULL, key_len=NULL, ref=NULL, rows=3, Extra=Using在哪里

More of the EXPLAIN: select_type=PRIMARY, table=abc, type=ALL, possible_keys=NULL, key=NULL, key_len=NULL, ref=NULL, rows=3, Extra=Using where

推荐答案

非常小的表会忽略索引吗?

Do very small tables ignore indexes?

是的.当整个表可以在单个磁盘访问中读取时，执行单独的磁盘访问来读取索引是没有意义的.

Yes. When the entire table can be read in a single disk access, there's no point in performing a separate disk access to read the index.

如果是这样，我怎样才能防止这个查询淹没我的慢查询日志?

If so, how can I prevent this query from flooding my slow query log?

关闭 log_queries_not_using_indexes.这也是默认不开启的原因之一.

Turn off log_queries_not_using_indexes. This is one of the reasons why it isn't on by default.

这篇关于非常小的 MySQL 表会忽略索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！