MYSQL，非常缓慢的order by

Question

我有两个表，一个是带有userid主键的用户表，另一个表通过外键引用了用户表。

目前用户表只有一条记录，而另一个表有一百万条记录。

下面的联接让我很疯狂:

 SELECT p0_.*, p1_.*
 FROM photo p0_, User p1_
 WHERE p0_.user_id = p1_.user_id
 ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000

在一个非常快的机器上，带有order by的查询需要12秒，而没有order by的查询只需要0.0005秒。

我在user_id上创建了一个索引(IDX_14B78418A76ED395)，以及一个由user_id和uploaddate组成的复合索引（"search2"）。

EXPLAIN显示如下：

+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys                | key                  | key_len | ref                 | rows  | Extra                           |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
|  1 | SIMPLE      | p1_   | ALL  | PRIMARY                      | NULL                 | NULL    | NULL                |     1 | Using temporary; Using filesort |
|  1 | SIMPLE      | p0_   | ref  | IDX_14B78418A76ED395,search2 | IDX_14B78418A76ED395 | 4       | odsfoto.p1_.user_id | 58520 |                                 |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+

表格定义：

CREATE TABLE `photo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`album_id` int(11) DEFAULT NULL,
`exif_id` int(11) DEFAULT NULL,
`title` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`width` int(11) NOT NULL,
`height` int(11) NOT NULL,
`uploaddate` datetime NOT NULL,
`filesize` int(11) DEFAULT NULL,
`path` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`originalFilename` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`mimeType` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`description` longtext COLLATE utf8_unicode_ci,
`gpsData_id` int(11) DEFAULT NULL,
`views` int(11) DEFAULT NULL,
`likes` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_14B78418B0FC9251` (`exif_id`),
UNIQUE KEY `UNIQ_14B7841867E96507` (`gpsData_id`),
KEY `IDX_14B78418A76ED395` (`user_id`),
KEY `IDX_14B784181137ABCF` (`album_id`),
KEY `search_idx` (`uploaddate`),
KEY `search2` (`user_id`,`uploaddate`),
KEY `search3` (`uploaddate`,`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `user` (
`user_id` int(11) NOT NULL,
`photoCount` int(11) NOT NULL,
`photoViews` int(11) NOT NULL,
`photoComments` int(11) NOT NULL,
`photoLikes` int(11) NOT NULL,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我该如何加速这个查询？

- user1985207

---

尝试仅在 uploaddate 上建立索引，看看是否有帮助。 - ceejayoz

你能提供 p0_ 和 p1_ 的表定义以及 sort_buffer_size 和 read_rnd_buffer_size 的值吗？ - Dancrumb

顺便提一下，你应该使用FROM photo p0_ INNER JOIN User p1_ ON p0_.user_id = p1_.user_id而不是FROM photo p0_, User p1_ WHERE p0_.user_id = p1_.user_id，但我不认为这是你性能问题的原因。 - Dancrumb

是的，但在这种情况下使用显式连接对执行时间没有影响。 - user1985207

@user1985207：照片和用户之间应该有一个“FOREIGN KEY”吗？“photo.user_id”是否保证在“user.user.id”中有匹配项？ - Quassnoi

Answer 1

看起来你遇到了MySQL无法进行延迟行查找的问题：

• MySQL ORDER BY / LIMIT性能：延迟行查找
• InnoDB中的延迟行查找

尝试这个：

SELECT  p.*, u.*
FROM    (
        SELECT  id
        FROM    photo
        ORDER BY
                uploaddate DESC, id DESC
        LIMIT   10
        OFFSET  100000
        ) pi
JOIN    photo p
ON      p.id = pi.id
JOIN    user u
ON      u.user_id = p.user_id

- Quassnoi

---

谢谢，这样快多了，我也明白了。但是为什么查询优化器没有进行这个简单的优化呢？ - user1985207

@user1985207：因为它没有被编程来这样做。请查看链接。 - Quassnoi

Answer 2

您需要在uploaddate上单独创建索引。只有当uploaddate是复合索引的第一列时，此排序才能利用复合索引。 您也可以尝试将user_id添加到ORDER BY中：

    ....      
    ORDER BY p0_.user_id, p0_.uploaddate

- Cozzamara

---

uploaddate和user_id上也有一个组合索引，但它没有被使用。 - user1985207

将user_id添加到ORDER BY中没有任何区别。 - user1985207

如果在 ORDER BY 中交换 user_id 和 uploaddate 会怎样？ - Cozzamara

执行时间上没有任何区别。 - user1985207

Answer 3

你有两个问题：

1. 你需要创建一个 INDEX(user_id, uploaddate)，这将极大地提高查询的效率。

2. 你需要找到绕过使用 LIMIT 10 OFFSET 100000 的方法。MySQL 正在创建一个包含 100,000 条记录的记录集，然后从末尾取出最后 10 条记录... 这是非常低效的。

https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/

- Ian

---

如果我只使用limit并删除整个offset或将其设置为零，那么效果是相同的。 - user1985207

“LIMIT 10”和“LIMIT 10, 100000”在性能方面非常不同。然而，查询速度缓慢的主要原因是未使用索引。当您在查询上运行“EXPLAIN”时，它应该使用索引，而不是执行“ALL”。 - Ian

我知道，但缺少哪个索引。我认为使用“ALL”是因为用户中只有一个条目。 - user1985207

链接 URL 已更改为：https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/ - Happy

Answer 4

首先尝试根据主键获取结果，而不使用连接，并使用结果再次查询结果。
例如：

$userIds=mysql::select("select user_id from photo ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000");

$photoData=mysql::select("SELECT p0_., p1_. FROM photo p0_, User p1_ WHERE p0_.user_id = p1_.user_id and p0_.user_id in ($userIds->user_id) order by p0_.uploaddate");

这里我们将语句分成两部分：
1.我们可以轻松地按照主键排序并获取结果，也没有连接。
2.基于ID和按顺序排列的查询结果仅限于少量列，我们可以在较短时间内检索数据。

- user2524079

Answer 5

使用Quassnoi的答案，从30秒降至0.015秒/0.000秒！这就是我所说的MySql专业知识！我从我的个人项目中剪掉了一个自身连接的连接。

Select ser.id_table, ser.id_rec, ser.relevance, cnt, title, description, sell_url, medium_thumb,  
        unique_id_supplier, keywords width, height, media_type 
from (    
        Select ser.id_rec, ser.id_table, ser.relevance, ser.cnt 
        from searchEngineResults ser     
        where thisSearch = 16287     
    order by  ser.relevance desc, cnt desc, id_rec 
    ) ser 
join photo_resell sou on sou.id = ser.id_rec 
#join searchEngineResults ser on ser.id_rec = tmp.id_rec 
limit 0, 9

- Dorin