mysql - 无法理解如何在基本 MYSQL 解释示例中摆脱表扫描

Question

我正在尝试优化一个非常基本的 MYSQL 示例，但我似乎无法弄清楚如何防止以下查询在引用列 uid 时进行表扫描。使用解释，tt 正确显示可能的 key ，但实际上并未使用该 key 并扫描所有行。

CREATE TABLE `Foo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `barId` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `barId` (`barId`),
  KEY `uid` (`uid`)
)

explain 
select   count(uid) as userCount 
FROM Foo
WHERE barId = 1
GROUP BY barId

id  select_type table type possible_keys  key   rows  Extra 
1   SIMPLE      Foo   ALL  barId          NULL   4    Using where

示例数据

id,barId,uid
1,1,1
2,1,2
3,1,3
4,2,4

Answer 1

看起来 MySQL 很聪明，意识到对这么小的表使用索引会花费更多时间？

• 当我解释它为空时，键是“barId”。
• 有 4 行(您的样本数据)，键为 NULL。
• 有 4096 行(我运行 INSERT SELECT 到本身几次)， key 返回“barID”。

来自手册 bottom .

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.