我目前正在试图弄清楚哪个查询实际上在更好地利用索引,而哪个查询会更快(这两个问题并不总是一致的)。
我已经运行过两次相同的查询,但是索引列被重新排列以最大化性能。
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 vs 10)中看到的那样,第二个查询显然使用了更多的索引,并且rows
它匹配的数目更少了(3243 vs 59720)。
什么是扔我送行的是type
,ref
和extra
的列EXPLAIN
。
在第二个查询中,我看到“使用临时;使用文件排序”,这在第一个查询中不存在。的type
是range
代替ref
(ref
被认为是快于range
),并且ref
列是NULL
代替const,const
。
那么...哪个更好地利用了索引?
第一个能够将索引用于GROUP BY user_id
,从而避免了排序(tmp + filesort)。但是,它必须跳过日期不在范围内的任何行,因此行数更大。
第二个使用日期范围,因此行数较少,但随后必须进行排序。
如果你有两个指标,而没有使用FORCE INDEX
,优化可能是动态选择了更好的索引,基于特定的日期范围。(但我对此表示怀疑。)无论如何,您的查询都是优化器根本没有足够的统计信息来始终“做正确的事情”的情况。
请注意,“使用临时;使用文件排序”在RAM中通常是一种快速,简单的qsort-没有临时表,没有磁盘命中。(我这样说是为了提醒读者不要害怕这个短语。
如果amount
在任何一个索引的末尾添加内容,都会得到“正在使用索引”,这意味着它是一个“覆盖”索引,其运行速度(非常粗略)快一倍。
“使用索引条件”无关。这意味着引擎对引擎进行了一些评价WHERE
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句