如何提高这个MySQL查询的速度？

Question

以下是查询语句：

SELECT 
u.uid as UID,
fuo.uid as FUO_UID,
fo.prid as FO_NAME
FROM 
users u
LEFT OUTER JOIN firstpoint_users_organisations fuo ON (u.uid=fuo.uid)
LEFT OUTER JOIN firstpoint_organisations fo ON (fo.nid=fuo.nid)
WHERE 
u.status=1 AND u.uid>1 
ORDER BY u.uid 
LIMIT 3;

还有表格：

users
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | varchar(60)      | NO   | UNI |         |                |
| status           | tinyint(4)       | NO   |     | 0       |                |
+-----------------------------------------------------------------------------+

firstpoint_users_organisations
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| nid   | int(10) unsigned | NO   | PRI | 0       |       |
| uid   | int(10) unsigned | NO   | PRI | 0       |       |
+-------+------------------+------+-----+---------+-------+

firstpoint_organisations
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| nid      | int(10) unsigned | NO   | PRI | 0       |       |
| prid     | varchar(32)      | NO   |     |         |       |
+------------------------------------------------------------+

我希望在users表的每一行中显示users.uid和firstpoint_organisations.prid，即使有些用户没有prid，此时应该显示NULL（因此使用左外连接）。连接应该如下：

users
uid -      firstpoint_users_organisations 
     \---->uid
           nid -          firstpoint_organisations
                \-------->nid
                          prid

每个用户都有一个用户ID（UID），以及他们所关联的组织（firstpoint_users_organisation）的节点ID（NID），并存储此关联。然后，组织的详细信息存储在firstpoint_organisations中。

因此，每个用户都将有一个prid，但如果没有，则显示NULL。

现在，如果我在firstpoint_users_organisations上进行INNER JOIN，然后在firstpoint_organisations上进行JOIN，我会得到很好的查询速度（上面的查询在0.02秒内运行）。但是，当我将两者都切换为LEFT OUTER JOIN时，以便获得所有用户，无论是否有prid，上述查询需要大约90秒才能运行。

有什么方法可以加快此查询速度吗？用户表中大约有70,000行，但即使使用LIMIT 3，将INNER JOIN变为LEFT OUTER JOIN也需要很长时间。有趣的是，查询用时与LIMIT 30相同，因此我认为我的查询存在根本性问题。

如请求的EXPLAIN所示：

+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                   | rows  | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | u     | range  | PRIMARY       | PRIMARY | 4       | NULL                  | 13152 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | fuo   | index  | NULL          | PRIMARY | 8       | NULL                  |  3745 | Using index                                  |
|  1 | SIMPLE      | fo    | eq_ref | PRIMARY       | PRIMARY | 4       | dbdb-dbdb_uat.fuo.nid |     1 |                                              |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+----------------------------------------------+
3 rows in set (0.00 sec)

- njp

---

请发布此语句的 EXPLAIN。 - Kermit

你的firstpoint_users_organisations表中有一个问题，是否可以有2个主键？这对我来说听起来有些奇怪。 - Wikunia

你的表中忘记了一个u.status！ - Wikunia

一张表格不可能有两个主键。但是，像任何其他索引一样，一个主键可以包含多个列。 - geomagas

所以问题出在第一个 EXPLAIN 行，但我不知道为什么 MySQL 在这里使用了 filesort，因为 "order by" 应该使用主键 u.uid，难道不是吗？ - Wikunia

Answer 1

你的查询是毫无意义的（因为uid > 1将包括除一个用户以外的所有用户），所以对于该索引使用IGNORE INDEX提示：

SELECT 
  u.uid as UID,
  fuo.uid as FUO_UID,
  fo.prid as FO_NAME
FROM users u IGNORE INDEX (uid)
LEFT JOIN firstpoint_users_organisations fuo ON u.uid=fuo.uid
LEFT JOIN firstpoint_organisations fo ON fo.nid=fuo.nid
WHERE u.status=1
AND u.uid > 1 
ORDER BY u.uid 
LIMIT 3

你应该在users(status)上放置索引，如果有足够的状态不等于1的行，则可能会给您带来一些好处。

更改LIMIT预计没有影响，因为必须在限制应用于知道要返回哪些行之前对70000行进行排序。除了向客户端返回较少的行（较少的逗号IO）外，limit几乎没有影响。

---

我相信“少代码就是好代码”，所以从严格的风格角度来看，我已经从你的查询中删除了非必要的代码：

• 删除了OUTER，因为没有其他类型的左连接
• 删除了连接条件周围的括号，因为你不需要它们

- Bohemian

Answer 2

我会建议在u.status和u.uid上使用唯一索引，因为MySQL需要进行全表扫描来查找状态=1的条目。我认为这样做后速度会更快；)

- Wikunia