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

2022-01-15 00:00:00 mariadb mysql

开启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.

相关文章