MYSQL，很慢的顺序

Question

我有两张桌子。一个是在用户ID上带有主键的User表，另一个是使用外键引用该用户表的表。

“用户”表仅具有一个条目（目前），而另一个表则具有一百万个条目。

以下联接使我发疯：

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

在速度非常快的计算机上，查询耗时12秒，而排序的时间为0.0005秒，而没有排序的时间为0.0005秒。

我在user_id（IDX_14B78418A76ED395）上有一个索引，在user_id和uploaddate上有一个复合索引（“ search2”）。

解释显示以下内容：

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

我该如何加快查询速度？

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