很小的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:
SELECT abc.* FROM abc
WHERE abc.id
NOT IN (
SELECT DISTINCT abc_id FROM zyx
WHERE id = 12345
);
SELECT abc.* FROM abc
WHERE abc.id
NOT IN (
SELECT DISTINCT abc_id FROM zyx
WHERE id = 12345
);
abc很小，只有3行数据. zyx相对较大，有超过100,000行数据.
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，可能_keys = NULL，key = NULL，key_len = NULL，ref = NULL，rows = 3，Extra =在何处使用
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屋！
