MYSQL，按顺序非常慢
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14368211/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL, very slow order by
提问by user1985207
I have got two tables. One is a User table with a primary key on the userid and the other table references the user table with a foreign key.
我有两张桌子。一个是在 userid 上带有主键的 User 表，另一个表使用外键引用 user 表。
The User table has only one entry (for now) and the other table has one million entrys.
User 表只有一个条目（目前），另一个表有 100 万个条目。
The following join drives me mad:
以下加入让我发疯：
SELECT p0_.*, p1_.*
FROM photo p0_, User p1_
WHERE p0_.user_id = p1_.user_id
ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000
The query takes 12sec on a very fast machine with the order by and 0.0005 sec without the order by.
查询在带有 order by 的非常快的机器上需要 12 秒，在没有 order by 的情况下需要 0.0005 秒。
I've got an index on user_id (IDX_14B78418A76ED395) and a composite index ("search2") on user_id and uploaddate.
我在 user_id (IDX_14B78418A76ED395) 上有一个索引，在 user_id 和 uploaddate 上有一个复合索引 ("search2")。
EXPLAIN shows the following:
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 | |
+----+-------------+-------+------+------------------------------+----------------------+---------+---------------------+-------+---------------------------------+
Table definitions:
表定义：
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;
What can I do to speed up this query?
我能做些什么来加速这个查询？
回答by Quassnoi
Seems you're suffering from MySQL's inability to do late row lookups:
似乎您正在遭受 MySQL 无法进行后期行查找的困扰：
Try this:
尝试这个：
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
回答by Cozzamara
You need a separate index on uploaddate. This sort will take advantage of composite index only if uploaddateis first column in it.
You can also try to add user_id to ORDER BY:
你需要一个单独的索引uploaddate。只有当uploaddate第一列在其中时，这种排序才会利用复合索引。您也可以尝试将 user_id 添加到 ORDER BY：
....
ORDER BY p0_.user_id, p0_.uploaddate
回答by Ian
You have two problems:
你有两个问题：
You need to create an
INDEX(user_id, uploaddate)which will greatly increase the efficiency of the query.You need to find a workaround to using
LIMIT 10 OFFSET 100000. MySQL is creating a recordset with 100,000 records in it, then it pulls the last 10 records off the end... that is extremely inefficient.
您需要创建一个
INDEX(user_id, uploaddate)，这将大大提高查询的效率。您需要找到一种解决方法来使用
LIMIT 10 OFFSET 100000. MySQL 正在创建一个包含 100,000 条记录的记录集，然后它将最后 10 条记录从末尾拉出......这是非常低效的。
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/talks about the large limit/offset problem.
http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/谈论大限制/偏移问题。
回答by user2524079
First try to get result based on primary key with out join and use result to query result again.
For ex:
首先尝试在没有连接的情况下根据主键获取结果，然后使用结果再次查询结果。
例如：
$userIds=mysql::select("select user_id from photo ORDER BY p0_.uploaddate DESC Limit 10 OFFSET 100000");
$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");
$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");
Here we had divided the statement into two parts:
1.We can easily order and get based on primary key and also there are no joins.
2.Getting query results based on id and order by is only on limited columns we can retrieve data in less time
这里我们将语句分为两部分：
1.我们可以很容易地基于主键进行排序和获取，并且没有连接。
2.根据id和order by获取查询结果只在有限的列上我们可以在更短的时间内检索数据
回答by Dorin
From 30 seconds to 0.015 sec / 0.000 sec using Quassnoianswer ! This is what I called MySql expertise ! I cut out one Join from my personal project (no join with itself)
从 30 秒到 0.015 秒/0.000 秒使用Quassnoi回答！这就是我所说的MySql 专长！我从我的个人项目中删减了一个 Join（没有加入自身）
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
