MYSQL，非常慢的order by
MYSQL，非常慢的order by
提问于 2013-01-17 05:30:43
我有两张桌子。一个是用户表，主键在userid上，另一个表引用带有外键的用户表。
用户表只有一个条目(目前)，另一个表有一百万个条目。
下面的join让我抓狂：
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;我能做些什么来加速这个查询呢？
回答 5
Stack Overflow用户
回答已采纳
发布于 2013-01-17 05:58:56
看起来您正在遭受MySQL无法执行后期行查找的困扰：
试试这个：
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_idStack Overflow用户
发布于 2013-01-17 05:45:51
您有两个问题：
- 您需要创建一个
INDEX(user_id, uploaddate)，这将大大提高查询效率。
- 您需要找到使用
LIMIT 10 OFFSET 100000的变通方法。MySQL正在创建一个包含100,000条记录的记录集，然后它将最后10条记录从末尾拉出...这是非常低效的。
https://www.percona.com/blog/2006/09/01/mysql-order-by-limit-performance-optimization/
Stack Overflow用户
发布于 2013-01-17 05:34:20
您需要在uploaddate上使用单独的索引。只有当uploaddate是复合索引中的第一列时，这种排序才会利用复合索引。您还可以尝试将user_id添加到ORDER BY：
....
ORDER BY p0_.user_id, p0_.uploaddate页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14368211
复制相关文章
相似问题
腾讯云开发者
