按日期描述排序时，“使用临时”会减慢查询速度

提问者：小点点

按日期描述排序时，“使用临时”会减慢查询速度

我有一个日志条目表，以及大约 100 个可能的日志代码的描述表：

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 ;

以下查询(匹配20k行中的10k行)在0.0034秒内执行(使用< code>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;

但是当添加< code > ORDER BY log _ entries . log entry _ id desc (这当然是必要的)时，它变慢到0.6秒。大概是因为log_codes表上用了“使用临时”？删除索引实际上会使查询执行得更快，但仍然很慢(0.3秒)。

解释没有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 |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+-------------+

包括订单方：

+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+
| 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                     |
+----+-------------+-------------+------+----------------------------+------------------+---------+--------------------------+------+---------------------------------+

关于如何让此查询执行得更快的任何提示？我不明白为什么需要“使用临时”，因为应该在获取和排序适当的日志条目之前选择日志代码？

更新@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 |                                 |
+----+-------------+-------------+------+-------------------------+------------------+---------+-------------------+------+---------------------------------+

更新@RolandoMySQLDBA：

用我的原始索引，按日期排序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       |
+----+-------------+-------------+-------+-----------------------+-----------------------+---------+------+-------+--------------------------------+

使用您的索引，按日期排序：

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更新：

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                                  |
+----+-------------+-------------+------+--------------------------+-----------------+---------+--------------------------+------+----------------------------------------------+

共3个答案

匿名用户

我认为，这里和类似问题中的大多数问题都来自于对MySQL（和其他数据库）如何使用索引进行排序的误解。答案是：MySQL不使用索引进行排序，它只是可以按照索引的顺序或相反的方向读取数据。如果你碰巧希望数据按照当前使用的索引的顺序排序——你很幸运，否则结果将被排序（因此解释中的文件排序）

这是整个结果的顺序，主要取决于连接中的第一个表。如果你查看你的解释，你会看到连接从“log_codes”表开始（因为它要小得多）。

基本上，你需要的是一个“日志条目”的复合索引(partner_id，date)，一个“日志代码”的覆盖复合索引(log_code，category_overview，log_desc)，将“内部连接”更改为“直接连接”以强制连接顺序，并按“日期”desc排序(幸运的是，该索引也将覆盖)。

UPD1：很抱歉，我错误地输入了第一个表的索引：它应该是（partner_id，log_code，日期）。

但是当我尝试对另一个表中的列进行排序时，我仍然难以理解为什么MySQL选择在log_codes表（和100倍查询时间）上“使用临时”？

MySQL可以直接输出数据，只要你同意它获取数据的顺序，或者把数据放在一个临时表中，然后应用排序和输出。当你从连接中的任何非第一个表中按字段排序时，MySQL必须对数据进行排序（不仅仅是按照索引的顺序输出），为了对数据进行排序，它需要一个临时表。

但是当我进一步进入数据集时，它会变慢（LIMIT 50000,25为6秒）。你知道为什么吗？

要输出第50000行，MySQL无论如何都需要获取前50000行并跳过它们。由于我错过了索引中的一列，MySQL不仅扫描了索引，而且还为每个项目进行了额外的磁盘查找，查找＜code＞log_code＜/code＞值。由于所有数据都可以从索引中提取，因此覆盖索引应该快得多。

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;

匿名用户

你需要两样东西

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);

在创建这些索引之前，运行这些

SELECT COUNT(1) rowcount,partner_id FROM log_entries GROUP BY partner_id;
SELECT COUNT(1) rowcount,category_overview FROM log_codes GROUP BY category_overview;

如果所有可能的partner_id值的计数都不超过log_entries表的5%,则创建该索引

ALTER TABLE log_entries ADD INDEX (partner_id,log_code,date);

如果所有可能的category_overview值的计数都没有超过log_codes表的5%，请创建此索引

ALTER TABLE log_codes ADD INDEX (category_overview,log_code,log_desc);

试试看！！！

请尝试此重构查询，包括限制 0，25

SELECT log_entries.date, log_codes.log_desc FROM 
(
    SELECT A.log_code FROM 
    (SELECT log_code FROM log_entries WHERE partner_id = 1) A INNER JOIN
    (SELECT log_code FROM log_codes WHERE category_overview = 1) B USING (log_code)
    LIMIT 0,25
) log_code_keys
INNER JOIN log_entries USING (log_code)
INNER JOIN log_code USING (log_code);

匿名用户

我将首先反转IX_partner_code和IX_overview_code索引中的列。这应该使它们更适合支持JOIN和WHERE子句。

...
KEY `IX_code_partner` (`log_code`,`partner_id`)
...
KEY `IX_code_overview` (`log_code`,`category_overview`),
...