mysql - 令人困惑的 MySQL 解释结果

Question

我目前正在尝试弄清楚哪个查询实际上更好地利用了索引，哪个会更快(这两件事并不总是一致)。

我已经运行了两次相同的查询，但是索引列被打乱以最大化性能。

INDEX(type, finalized_on, user_id, date_applied)

mysql> explain 
select user_id
     , sum(amount) amount 
  from user_accounts_payable force index (type_date_finalized_user) 
 where type=1 
   and date_applied between '2018-01-01' and '2019-01-01' 
   and finalized_on is null 
 group 
  by user_id;
+----+-------------+-----------------------+------+----------------------------------------------------------------------------+--------------------------+---------+-------------+-------+------------------------------------+
| id | select_type | table                 | type | possible_keys                                                              | key                      | key_len | ref         | rows  | Extra                              |
+----+-------------+-----------------------+------+----------------------------------------------------------------------------+--------------------------+---------+-------------+-------+------------------------------------+
|  1 | SIMPLE      | user_accounts_payable | ref  | type_user_date_finalized,type_user_finalized_date,type_date_finalized_user | type_date_finalized_user | 10      | const,const | 59720 | Using index condition; Using where |
+----+-------------+-----------------------+------+----------------------------------------------------------------------------+--------------------------+---------+-------------+-------+------------------------------------+
1 row in set

现在使用 INDEX(type, finalized_on, date_applied, user_id)

mysql> explain select user_id, sum(amount) amount from user_accounts_payable force index (type_date_finalized_user) where type=1 and date_applied between '2018-01-01' and '2019-01-01' and finalized_on is null group by user_id;
+----+-------------+-----------------------+-------+----------------------------------------------------------------------------+--------------------------+---------+------+------+--------------------------------------------------------+
| id | select_type | table                 | type  | possible_keys                                                              | key                      | key_len | ref  | rows | Extra                                                  |
+----+-------------+-----------------------+-------+----------------------------------------------------------------------------+--------------------------+---------+------+------+--------------------------------------------------------+
|  1 | SIMPLE      | user_accounts_payable | range | type_user_date_finalized,type_user_finalized_date,type_date_finalized_user | type_date_finalized_user | 13      | NULL | 3243 | Using index condition; Using temporary; Using filesort |
+----+-------------+-----------------------+-------+----------------------------------------------------------------------------+--------------------------+---------+------+------+--------------------------------------------------------+
1 row in set

第二个查询显然使用了更多的索引，正如我在 key_len 中看到的那样(13 对 10)，并且它匹配的 rows 数量较少(3243对比 59720)。

让我失望的是 EXPLAIN 的 type、ref 和 extra 列。

在第二个查询中，我看到第一个查询中不存在的“使用临时文件；使用文件排序”。 type 是 range 而不是 ref (ref 应该比 range)，并且 ref 列是 NULL 而不是 const,const。

那么...哪个更好地利用了索引？

Answer 1

第一个能够使用 GROUP BY user_id 的索引，从而避免排序 (tmp+filesort)。但是，它必须跳过日期不在范围内的任何行，因此行数更大。

第二个使用日期范围，因此行数较少，但随后必须进行排序。

如果您有两个索引，并且没有使用FORCE INDEX，优化器可能会根据具体日期范围。 (但我对此表示怀疑。)无论如何，您的查询是优化器根本没有足够的统计信息来始终“做正确的事情”的情况。

请注意，“使用临时；使用文件排序”通常是一种快速、简单的 RAM 中的 qsort -- 没有临时表，没有磁盘命中。 (我这样说是为了提醒读者不要害怕这句话。

如果将 amount 添加到任一索引的 end 上，您将得到“Using index”，这意味着它是一个“覆盖”索引，它将运行 (非常粗略地)快两倍。

“使用索引条件”无关；这意味着引擎对 WHERE 进行了一些评估。