mysql - MySQL如何决定它是否使用GROUP BY的索引？

Question

我有一个简单的表格

stock_ledger_id   INT(10) (Primary)
piece_to_bin_id   INT(10)
quantity          INT(11)
create_datetime   TIMESTAMP
... and a few VARCHARs

有一些简单的索引

Key_name          Cardinality
PRIMARY               1510443
piece_to_bin_id        100696

这个相当简单的查询大约需要 8 秒:

SELECT piece_to_bin_id,
       SUM(quantity),
       MAX(create_datetime)
FROM stock_ledger
GROUP BY piece_to_bin_id

这是解释:

id select_type table        type possible_keys key  key_len ref  rows    Extra                           
1  SIMPLE      stock_ledger ALL  NULL          NULL NULL    NULL 1512976 Using temporary; Using filesort 

我发现我可以通过强制索引将它降低到大约 0.5 秒:

SELECT piece_to_bin_id,
       SUM(quantity),
       MAX(create_datetime)
FROM stock_ledger
FORCE INDEX (piece_to_bin_id)
GROUP BY piece_to_bin_id

然后 EXPLAIN 看起来像这样:

id select_type table        type  possible_keys key             key_len ref  rows    Extra
1  SIMPLE      stock_ledger index NULL          piece_to_bin_id 4       NULL 1512976

我使用的是 MySQL 5.1.41，表是 MyISAM，我之前运行过 ANALYZE TABLE。

所以我是坚持“MySQL 又搞错了，只是强制索引”还是 MySQL 使用全表扫描的实际原因？也许我可以修好？

Answer 1

查询无论如何都需要全表扫描，可能是mysql试图避免从键值到行的额外转换。查询可能更受益于复合 (piece_to_bin_id, create_datetime) 索引甚至 (piece_to_bin_id, create_datetime, quantity)。后者将成为一个覆盖指数。

UPD

看起来快 16 倍的结果来自您案例中的数据分布(可能许多相邻行具有相同的 piece_to_bin_id，按 create_datetime 排序)。 MyISAM 似乎使用索引来减少结果行数的查询，因为使用它们意味着随机磁盘 I/O 操作。

我从来没有引起任何注意，但我目前对 10K 行的表的测试表明 MyISAM 甚至不使用索引来对查询进行排序，例如:

SELECT indexed_field, another_field
FROM a_table
ORDER BY indexed_field;

即使 indexed_field 是主键。