按日期desc排序时，“使用临时”减慢查询速度 [英] When ordering by date desc, "Using temporary" slows down query
问题描述
我有一个日志条目表，以及大约100个可能的日志代码的描述表：
I have a table for log entries, and a description table for the about 100 possible log codes:
CREATE TABLE `log_entries` (
`logentry_id` int(11) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`partner_id` smallint(4) NOT NULL,
`log_code` smallint(4) NOT NULL,
PRIMARY KEY (`logentry_id`),
KEY `IX_code` (`log_code`),
KEY `IX_partner_code` (`partner_id`,`log_code`)
) ENGINE=MyISAM ;
CREATE TABLE IF NOT EXISTS `log_codes` (
`log_code` smallint(4) NOT NULL DEFAULT '0',
`log_desc` varchar(255) DEFAULT NULL,
`category_overview` tinyint(1) NOT NULL DEFAULT '0',
`category_error` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`log_code`),
KEY `IX_overview_code` (`category_overview`,`log_code`),
KEY `IX_error_code` (`category_error`,`log_code`)
) ENGINE=MyISAM ;
以下查询（匹配10k的20k行）在0.0034秒内执行（使用 LIMIT 0,20 ）：
The follwing query (matching 10k of 20k rows) executes in 0.0034 sec (using LIMIT 0,20):
SELECT log_entries.date, log_codes.log_desc FROM log_entries
INNER JOIN log_codes ON log_codes.log_code = log_entries.log_code
WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1;
但添加 ORDER BY log_entries.logentry_id DESC ，这当然是必要的，它减慢到0.6秒。可能是因为在log_codes表中使用了Using temporary？删除索引实际上使查询执行得更快，但仍然很慢（0.3秒）。
But when adding ORDER BY log_entries.logentry_id DESC, which is of course necessary, it slows down to 0.6 sec. Probably because "Using temporary" is used on the log_codes table? Removing the indexes actually makes the query perform faster, but still slow (0.3 sec).
没有ORDER BY的查询的EXPLAIN输出：
EXPLAIN output of the query without ORDER BY:
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+
| 1 | SIMPLE | log_codes | ref | PRIMARY,IX_overview_code | IX_overview_code | 1 | const | 56 | |
| 1 | SIMPLE | log_entries | ref | IX_code,IX_partner_code | IX_partner_code | 7 | const,log_codes.log_code | 25 | Using where |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+
包括ORDER BY：
And including the ORDER BY:
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
| 1 | SIMPLE | log_codes | ref | PRIMARY,IX_overview_code | IX_overview_code | 1 | const | 56 | Using temporary; Using filesort |
| 1 | SIMPLE | log_entries | ref | IX_code,IX_partner_code | IX_partner_code | 7 | const,log_codes.log_code | 25 | Using where |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
有关如何获得此信息的任何提示查询执行得更快？我不明白为什么需要使用临时，因为在获取和分类适当的日志条目之前应该选择日志代码？
Any hints on how to get this query to perform faster? I can't see why "using temporary" should be needed, as the log codes should be chosen before fetching and sorting the appropiate log entries?
UPDATE @ Eugen Rieck ：
SELECT log_entries.date, lc.log_desc FROM log_entries INNER JOIN (SELECT log_desc, log_code FROM log_codes WHERE category_overview = 1) AS lc ON lc.log_code = log_entries.log_code WHERE log_entries.partner_id = 1 ORDER BY log_entries.logentry_id;
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |
| 1 | PRIMARY | log_entries | ref | IX_code,IX_partner_code | IX_partner_code | 7 | const,lc.log_code | 25 | Using where |
| 2 | DERIVED | log_codes | ref | IX_overview_code | IX_overview_code | 1 | | 56 | |
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+
UPDATE @RolandoMySQLDBA ：
使用我的原始索引，ORDER BY date DESC：
SELECT log_entries.date, log_codes.log_desc FROM (SELECT log_code,date FROM log_entries WHERE partner_id = 1) log_entries INNER JOIN (SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1) log_codes USING (log_code) ORDER BY log_entries.date DESC;
+----+-------------+-------------+------+------------------+------------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+------------------+------------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 21937 | Using where; Using join buffer |
| 3 | DERIVED | log_codes | ref | IX_overview_code | IX_overview_code | 1 | | 56 | |
| 2 | DERIVED | log_entries | ALL | IX_partner_code | NULL | NULL | NULL | 22787 | Using where |
+----+-------------+-------------+------+------------------+------------------+---------+------+-------+---------------------------------+
使用您的索引，无需订购：
SELECT log_entries.date, log_codes.log_desc FROM (SELECT log_code,date FROM log_entries WHERE partner_id = 1) log_entries INNER JOIN (SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1) log_codes USING (log_code);
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 57 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 21937 | Using where; Using join buffer |
| 3 | DERIVED | log_codes | index | IX_overview_code_desc | IX_overview_code_desc | 771 | NULL | 80 | Using where; Using index |
| 2 | DERIVED | log_entries | index | IX_partner_code_date | IX_partner_code_date | 15 | NULL | 22787 | Using where; Using index |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+
使用索引，ORDER BY日期DESC：
SELECT log_entries.date, log_codes.log_desc FROM (SELECT log_code,date FROM log_entries WHERE partner_id = 1) log_entries INNER JOIN (SELECT log_code,log_desc FROM log_codes WHERE category_overview = 1) log_codes USING (log_code) ORDER BY log_entries.date DESC;
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 57 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 21937 | Using where; Using join buffer |
| 3 | DERIVED | log_codes | index | IX_overview_code_desc | IX_overview_code_desc | 771 | NULL | 80 | Using where; Using index |
| 2 | DERIVED | log_entries | index | IX_partner_code_date | IX_partner_code_date | 15 | NULL | 22787 | Using where; Using index |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+---------------------------------+
更新@Joe Stefanelli ：
SELECT log_entries.date, log_codes.log_desc FROM log_entries INNER JOIN log_codes ON log_codes.log_code = log_entries.log_code WHERE log_entries.partner_id = 1 AND log_codes.category_overview = 1 ORDER BY date DESC;
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
| 1 | SIMPLE | log_codes | ALL | PRIMARY,IX_code_overview | NULL | NULL | NULL | 80 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | log_entries | ref | IX_code,IX_code_partner | IX_code_partner | 7 | log_codes.log_code,const | 25 | Using where |
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+
推荐答案
我认为，这里和类似问题中的大多数问题都来自误解MySQL（和其他数据库）如何使用索引进行排序。答案是：MySQL不使用索引进行排序，它只能按索引的顺序或相反的方向读取数据。如果您碰巧希望数据按照当前使用的索引的顺序进行 - 您很幸运，否则结果将被排序（因此在EXPLAIN中的文件输出）
I think, most of problems here and in similar questions come from misunderstanding how MySQL (and other databases) uses indexes for sorting. The answer is: MySQL does not use indexes for sorting, it just can read data in the order of an index or in the opposite direction. If you happened to want the data to be oredered in the order of the currently used index - you are lucky, otherwise the result will be sorted (hence filesort in EXPLAIN)
这是整个结果的顺序，主要取决于哪个表是连接中的第一个。如果你看一下你的EXPLAIN，你会看到连接从'log_codes'表开始（因为它要小得多）。
That is order of the whole result mostly depends on which table was the first in the join. And if you look at your EXPLAIN you will see that the join starts from 'log_codes' table (because it is much smaller).
基本上，你需要的是一个复合材料'log_entries'上的index（partner_id，date），'log_codes'的覆盖复合索引（log_code，category_overview，log_desc），将'INNER JOIN'更改为'STRAIGHT_JOIN'以强制连接顺序，并按'date'DESC排序（幸运的是，这个指数也会覆盖。）
Basically, what you need is a composite index (partner_id, date) on 'log_entries', a covering composite index (log_code, category_overview, log_desc) for 'log_codes', change 'INNER JOIN' to 'STRAIGHT_JOIN' to force the join order, and order by 'date' DESC (this index will fortunately be covering too).
UPD1 ：对不起，我错误输入了第一个表的索引：它应该是（partner_id，log_code，date）。
UPD1: I am sorry, I mistyped the index for the first table: it should be (partner_id, log_code, date).
但我仍然很难理解为什么MySQL当我尝试对另一个表中的列进行排序时，选择在log_codes表上使用临时（和100x查询时间）？
But I still struggle to understand why MySQL choose to "use temporary" on the log_codes table (and 100x query time) when I try to sort on a column in another table?
MySQL可以直接输出数据，只要您同意它获取数据的顺序，或者将数据放在临时表中，应用排序然后输出。当您从连接中的任何非第一个表中的字段进行排序时，MySQL必须对数据进行排序（不仅仅是按索引的顺序输出），还要对需要临时表的数据进行排序。
MySQL can either directly output data as long as you agree with the ordering in which it gets it, or put data in a temporary table, apply sorting and output then. When you order by a field from any non-first table in joins, MySQL has to sort data (not just output in the order of an index) and to sort data it needs a temporary table.
但随着我进一步深入数据集，它更慢（LIMIT 50000,25为6秒）。你知道为什么吗？
But as I get further into the dataset it is slower (6 sec for LIMIT 50000,25). Do you know why?
输出行50000,25 MySQL无论如何需要获取前50000并跳过它们。由于我错过了索引中的一个列，因此MySQL不仅仅扫描了索引，而且每个项目都为光盘查找了 log_code 值。覆盖索引应该快得多，因为所有数据都可以从索引中获取。
To output rows 50000,25 MySQL anyway needs to fetch the first 50000 and skip them. Since I missed a column in the index, MySQL not just skanned the index but for each item made an additional on disc lookup for log_code value. With the covering index that should be much faster, since all data can be fetched from the index.
UPD2 ：尝试强制索引：
SELECT log_entries.date, log_codes.log_desc
FROM log_entries FORCE INDEX (IX_partner_code_date)
STRAIGHT_JOIN log_codes
ON log_codes.log_code = log_entries.log_code
WHERE log_entries.partner_id = 1
AND log_codes.category_overview = 1
ORDER BY log_entries.date DESC;
这篇关于按日期desc排序时，“使用临时”减慢查询速度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
