时间戳

首页 > 解决方案 > MySQL - 如何使用 order by 优化查询

mysql - MySQL - 如何使用 order by 优化查询

问题描述

我正在尝试为一组用户任务生成 5 个最新历史项目的列表。如果我通过执行删除订单从〜2秒下降到<20毫秒。

索引开启

h.task_id
h.mod_date
i.task_id
i.user_id

这是查询

SELECT h.*
     , i.task_id
     , i.user_id
     , i.name
     , i.completed
  FROM h
     , i
 WHERE i.task_id = h.task_id 
   AND i.user_id = 42 
 ORDER 
    BY h.mod_date DESC 
 LIMIT 5

这是解释：

id  select_type table type  possible_keys  key    key_len   ref     rows    Extra
 1  SIMPLE      i     ref   PRIMARY,UserID UserID       4   const   3091    Using temporary; Using filesort
 1  SIMPLE      h     ref   TaskID         TaskID       4   myDB.i.task_id  7

这是显示创建表：

CREATE TABLE `h` (
`history_id` int(6) NOT NULL AUTO_INCREMENT,
`history_code` tinyint(4) NOT NULL DEFAULT '0',
`task_id` int(6) NOT NULL,
`mod_date` datetime NOT NULL,
`description` text NOT NULL,
PRIMARY KEY (`history_id`),
KEY `TaskID` (`task_id`),
KEY `historyCode` (`history_code`),
KEY `modDate` (`mod_date`)
) ENGINE=InnoDB AUTO_INCREMENT=185647 DEFAULT CHARSET=latin1

和

CREATE TABLE `i` (
`task_id` int(6) NOT NULL AUTO_INCREMENT,
`user_id` int(6) NOT NULL,
`name` varchar(60) NOT NULL,
`due_date` date DEFAULT NULL,
`create_date` date NOT NULL,
`completed` tinyint(1) NOT NULL DEFAULT '0',
`task_description` blob,
PRIMARY KEY (`task_id`),
KEY `name_2` (`name`),
KEY `UserID` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12085 DEFAULT CHARSET=latin1

标签： mysqlquery-optimization

解决方案

尝试更改索引，h.task_id使其成为此复合索引。

CREATE OR REPLACE INDEX TaskID ON h(task_id, mod_date DESC);

这可能（或可能不会）允许 MySql 缩短您ORDER BY ... LIMIT ...请求中的部分或全部额外工作。顺便说一句，这是一个臭名昭著的性能反模式，但有时是必要的。

编辑索引没有帮助。因此，让我们尝试所谓的延迟连接，这样我们就不必对h表中的所有数据进行 ORDER 和 LIMIT 操作。

从这个子查询开始。它只检索结果中涉及的行的主键值，并且只生成五行。

         SELECT h.history_id, i.task_id
           FROM h
           JOIN i ON h.task_id = i.task_id
          WHERE i.user_id = 42
          ORDER BY h.mod_date
          LIMIT 5

为什么这个子查询？它处理工作密集的 ORDER BY ... LIMIT 操作，同时仅操作主键和日期。它仍然必须对大量行进行排序才能丢弃除五行之外的所有行，但它必须处理的行要短得多。因为这个子查询做了繁重的工作，所以你专注于优化它，而不是整个查询。

保留我上面建议的索引，因为它涵盖了h.

然后，像这样将它加入到您的其余查询中。这样，您只需检索h.description您关心的五行的昂贵列。

SELECT h.* , i.task_id, i.user_id , i.name, i.completed
  FROM h
  JOIN i ON i.task_id = h.task_id 
  JOIN (
             SELECT h.history_id, i.task_id
               FROM h
               JOIN i ON h.task_id = i.task_id
              WHERE i.user_id = 42
              ORDER BY h.mod_date
              LIMIT 5
       ) selected ON h.history_id = selected.history_id
                 AND i.task_id = selected.task_id
 ORDER BY h.mod_date DESC 
 LIMIT 5

推荐阅读