带有连接、限制和顺序的 Mysql 查询非常慢(文件排序)

Question

我有以下查询:

explain select * from users, dls where dls.user_id=users.id and users.status = 'accepted' and users.acc = 0 order by users.user_name desc limit 18416, 16

下面的解释是什么结果；

+----+-------------+-------+------+------------------------+-------------+---------+---------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys          | key         | key_len | ref                             | rows  | Extra                           |
+----+-------------+-------+------+------------------------+-------------+---------+---------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | dls   | ALL  | PRIMARY,user_id         | NULL        | NULL    | NULL                            | 19910 | Using temporary; Using filesort | 
|  1 | SIMPLE      | users  | ref  | PRIMARY,id_user_name | id_user_name | 4       | dls.user_id |     1 | Using where                     | 
+----+-------------+-------+------+------------------------+-------------+---------+---------------------------------+-------+---------------------------------+
2 rows in set (0.00 sec)

这个查询真的非常慢，我不知道如何修复它。我通过阅读有关如何优化 order by/limit 查询的文章尝试了各种索引，但结果仍然相同。有人可以帮忙吗？

编辑:模式:

CREATE TABLE `users` (

  `id` int(10) unsigned NOT NULL auto_increment,
  `user_name` varchar(100) character set utf8 NOT NULL,
`status` enum('accepted','rejected') character set utf8 NOT NULL,
`acc` varchar(6) character set utf8 NOT NULL,
 PRIMARY KEY  (`id`),
  KEY `user_name` (`user_name`),
 KEY `id_user_name` (`id`,`user_name`)
)

CREATE TABLE `dls` (
 `user_id` int(10) unsigned NOT NULL,
 `category_id` bigint(20) NOT NULL,

`download_url` varchar(255) character set utf8 NOT NULL,
  PRIMARY KEY  (`user_id`,`category_id`),
  KEY `user_id` (`user_id`)
)

Scrummeister 查询的输出；

+----+-------------+-------+------+------------------------+--------+---------+------------------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys          | key    | key_len | ref                          | rows  | Extra                       |
+----+-------------+-------+------+------------------------+--------+---------+------------------------------+-------+-----------------------------+
|  1 | SIMPLE      | u  | ALL  | PRIMARY,id_user_name | NULL   | NULL    | NULL                         | 10838 | Using where; Using filesort | 
|  1 | SIMPLE      | dls   | ref  | PRIMARY,user_id         | user_id | 4       | u.id |     2 |                             | 
+----+-------------+-------+------+------------------------+--------+---------+------------------------------+-------+-----------------------------+

Answer 1

已知 MySql 存在使用大偏移量的 LIMIT 问题。

STRAIGHT_JOIN关键字，告诉 MySql 首先扫描用户表，然后为每个用户查找 dls 表中的行。

SELECT STRAIGHT_JOIN *
FROM users u JOIN dls ON dls.user_id = users.id
WHERE u.status = 'accepted' and u.acc = 0
ORDER BY users.user_name desc 
LIMIT 18416, 16

除非有需要，否则不建议使用 STRAIGHT_JOIN，在这种特定情况下，我相信它可能会起作用，因为它可以使用 user_name 索引进行排序。

您还有其他选择:

• 增加 sort_buffer_size 的大小
• 增加 read_rnd_buffer_size 的大小(谨慎!)
• 只对 users 表进行分页，不管他有多少 dls，只应用 JOIN。
• 在您的代码中处理分页。假设用户从一个页面跳到另一个页面并跳到许多页面，您应该存储每个页面的第一个和最后一个用户名。如果用户单击下一页 - 添加 WHERE user_name > "{LastPageLastUsername} LIMIT 0,16" 这将增加

其他优化请阅读ORDER BY Optimization和 Limit Optimization