如何提高这个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.uid和firstpoint_organisations.prid显示users中的每一行，即使有些用户不会有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和firstpoint_organisations上进行内部连接，那么查询速度就会很好(上面的查询在0.02秒内运行)。但是，当我将两个都切换到左外部联接时，这样我就可以获得所有用户，无论是prid还是没有prid，上面的查询大约需要90秒才能运行。

我能做些什么来加速这个查询吗？大约有几分。users表中的70,000行，但即使有限制3，使内部联接成为左外部联接也要花费大量的时间。有趣的是，这个查询需要同样的时间来运行限制为30，所以我认为我的查询有一些根本的错误。

应要求解释：

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

Answer 1

您的查询是没有意义的(因为uid > 1将包括除一个用户之外的所有用户)，因此在uid上使用索引，因此对该索引使用忽略索引提示：

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，这可能会给您带来一些好处。

更改限制将不会产生任何影响，因为在应用该限制之前必须对70000行进行排序，以确定哪些行是第一个返回的行--该限制几乎没有效果，只是返回给客户端的行较少(较少的逗号IO)。

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

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

Answer 2

我会在u.status上使用唯一的索引，u.uid，因为mysql必须进行全面扫描才能查看，我认为哪些条目的状态=1。

我希望以后更快些;)