非常小的 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:
- 非常小的表会忽略索引吗?我明白为什么，对 3 行数据使用索引并没有节省太多时间.
- 如果是这样，我怎样才能防止这个查询淹没我的慢查询日志?
感谢您的宝贵时间！:)
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.
相关文章