mysql - 如何提高此 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

因此每个用户 (users) 都有一个用户 ID (uid)，与他们关联的组织 (firstpoint_users_organisation) 有一个节点 ID (nid) 并存储此关联。该组织的详细信息随后存储在 firstpoint_organisations 中。

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

现在，如果我在 firstpoint_users_organisations 上执行 INNER JOIN，然后在 firstpoint_organisations 上执行，我将获得很好的查询速度(上述查询在 0.02 秒内运行)。但是，当我将两者切换到 LEFT OUTER JOIN 时，我可以获得所有用户，prid 或没有 prid，上面的查询需要大约 90 秒才能运行。

我能做些什么来加快这个查询的速度吗？有大约。 users 表中有 70,000 行，但即使使用 LIMIT 3，将 INNER JOIN 变为 LEFT OUTER JOIN 也会花费大量时间。有趣的是，在 LIMIT 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 上的索引，因此对该索引使用 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 行进行排序在应用该限制以了解哪些行是第一行return - 限制几乎没有影响，除了更少的行返回给客户端(更少的逗号 IO)

---

我坚信“代码越少越好”，因此从严格的样式角度来看，我已从您的查询中删除了非必要代码:

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