此查询在 VPS 上运行 12 秒以上。它连接了 3 个表。只有第一个“topcics”有大约 70k 行，其他大约有 20 行，“post_cc”大约有 1500 行。

SELECT topics.*, employee.username, accounts.ac_name, accounts.ac_mail
FROM topics
INNER JOIN employee ON employee.id_user = topics.id_owner 
INNER JOIN accounts ON accounts.id_account = topics.id_account 
WHERE topics.status  IN  ('1','3') AND ( topics.id_owner IN (12, 5) OR topics.id_post IN 
    (SELECT DISTINCT(id_post) FROM post_cc WHERE id_employee IN (12, 5) ) )
ORDER BY topics.creationdate DESC LIMIT 0,25

我已经尝试(没有任何改进)删除子查询和第一个“员工”加入。如果我删除“帐户”连接，查询将在 0.1 秒内运行，但在分页期间需要所有表数据进行排序。

解释:

+----+--------------------+------------+-----------------+-----------------------+---------+---------+-----------------+-------+----------------------------------------------+
| id | select_type        | table      | type            | possible_keys         | key     | key_len | ref             | rows  | Extra                                        |
+----+--------------------+------------+-----------------+-----------------------+---------+---------+-----------------+-------+----------------------------------------------+
|  1 | PRIMARY            | topics     | ALL             | id_owner,id_account   | NULL    | NULL    | NULL            | 75069 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | accounts   | ALL             | PRIMARY               | NULL    | NULL    | NULL            |     5 | Using where; Using join buffer               |
|  1 | PRIMARY            | employee   | eq_ref          | PRIMARY               | PRIMARY | 3       | topics.st_owner |     1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | post_cc    | unique_subquery | PRIMARY               | PRIMARY | 8       | func,const      |     1 | Using index; Using where                     |
+----+--------------------+------------+-----------------+-----------------------+---------+---------+-----------------+-------+----------------------------------------------+

我添加了建议键作为索引，它缩短了 2 秒的时间，但仍然太慢。

缩短的表格:

topics
+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| id_post            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| id_account         | int(10) unsigned    | YES  | MUL | 0       |                |
| mail               | varchar(256)        | YES  | MUL | NULL    |                |
| from_name          | varchar(512)        | YES  |     | NULL    |                |
| title              | varchar(512)        | YES  |     | NULL    |                |
| content            | text                | YES  |     | NULL    |                |
| id_owner           | int(10) unsigned    | YES  | MUL | NULL    |                |
| creationdate       | datetime            | YES  |     | NULL    |                |
+--------------------+---------------------+------+-----+---------+----------------+

employee
+---------------------+-----------------------+------+-----+---------+----------------+
| Field               | Type                  | Null | Key | Default | Extra          |
+---------------------+-----------------------+------+-----+---------+----------------+
| id_employee         | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| id_user             | mediumint(8) unsigned | NO   |     | NULL    |                |
| id_owner            | tinyint(1)            | YES  |     | 0       |                |
| active              | tinyint(1)            | YES  |     | 1       |                |
| username            | varchar(64)           | YES  |     | NULL    |                |
| email               | varchar(128)          | YES  |     | NULL    |                |
+---------------------+-----------------------+------+-----+---------+----------------+

accounts
+----------------------------+---------------------+------+-----+---------+----------------+
| Field                      | Type                | Null | Key | Default | Extra          |
+----------------------------+---------------------+------+-----+---------+----------------+
| id_account                 | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| ac_mail                    | int(10) unsigned    | YES  | UNI | NULL    |                |
| ac_name                    | varchar(512)        | YES  |     | NULL    |                |
| last_sync_time             | datetime            | YES  |     | NULL    |                |
+----------------------------+---------------------+------+-----+---------+----------------+

post_cc
+------------------------+---------------------+------+-----+---------+-------+
| Field                  | Type                | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| id_post                | int(10) unsigned    | NO   | PRI | NULL    |       |
| id_employee            | int(10) unsigned    | NO   | PRI | NULL    |       |
| notifications          | tinyint(3) unsigned | YES  |     | 1       |       |
+------------------------+---------------------+------+-----+---------+-------+

最佳答案

一个可能的怀疑是依赖子查询。

MySQL 正在为外部查询返回的每一行处理该子查询(尚未被其他谓词过滤掉。

要提高性能，请考虑将其重写为 JOIN 操作或 EXISTS 谓词。

要用 JOIN 操作替换它，由于谓词中的 OR，这将需要是 OUTER JOIN(而不是 INNER JOIN)。

作为一种方法的示例:

SELECT topics.*
     , employee.username
     , accounts.ac_name
     , accounts.ac_mail
  FROM topics
  JOIN employee ON employee.id_user = topics.id_owner 
  JOIN accounts ON accounts.id_account = topics.id_account
  LEFT
  JOIN ( SELECT DISTINCT q.id_post
           FROM post_cc q 
          WHERE q.id_employee IN (12, 5) 
       ) p
    ON p.id_post = topics.id_post   
 WHERE topics.status IN ('1','3') 
   AND ( topics.id_owner IN (12, 5) 
       OR p.id_post IS NOT NULL
       )
 ORDER BY topics.creationdate DESC LIMIT 0,25

我建议您对其运行 EXPLAIN，并查看其性能。

另一种选择是考虑 EXISTS 谓词。有时我们可以让它表现得更好，但通常情况下不会。

SELECT topics.*
     , employee.username
     , accounts.ac_name
     , accounts.ac_mail
  FROM topics
  JOIN employee ON employee.id_user = topics.id_owner 
  JOIN accounts ON accounts.id_account = topics.id_account
 WHERE topics.status IN ('1','3') 
   AND ( topics.id_owner IN (12, 5) 
       OR EXISTS ( SELECT 1 
                     FROM post_cc q
                    WHERE q.id_employee IN (12, 5)
                      AND q.id_post = topics.id_post
                 )
       )
 ORDER BY topics.creationdate DESC LIMIT 0,25

为了性能，这几乎需要为 EXISTS 子句中的子查询提供合适的覆盖索引，例如:

ON post_cc (id_post, id_employee)

您可以尝试运行一个 EXPLAIN，看看它的表现如何。

我们看到 MySQL 没有在 topics 表上使用索引。

如果我们有一个前导列为 creationdate 的索引，我们可能会让 MySQL 避免昂贵的“Using filesort”操作。

部分问题可能是谓词中的OR。我们可能会尝试将该查询重写为两个单独的查询，并将它们与 UNION ALL 集合操作结合起来。但如果我们这样做，我们真的很想看到一个关于 topic 的索引被使用(我们可能不会通过对 70,000 行进行 两次 扫描来提高性能。

SELECT topics.*
     , employee.username
     , accounts.ac_name
     , accounts.ac_mail
  FROM topics
  JOIN employee ON employee.id_user = topics.id_owner 
  JOIN accounts ON accounts.id_account = topics.id_account
 WHERE topics.status IN ('1','3')
   AND topics.id_owner IN (12, 5)

 UNION ALL

SELECT topics.*
     , employee.username
     , accounts.ac_name
     , accounts.ac_mail
  FROM topics
  JOIN employee ON employee.id_user = topics.id_owner 
  JOIN accounts ON accounts.id_account = topics.id_account
  JOIN ( SELECT DISTINCT q.id_post
           FROM post_cc q 
          WHERE q.id_employee IN (12, 5) 
       ) p
    ON p.id_post = topics.id_post  
 WHERE topics.status IN ('1','3')
   AND ( topics.id_owner NOT IN (12, 5) OR topics.id_owner IS NULL )

 ORDER BY 8 DESC LIMIT 0,25

通过这种形式的查询，我们更有可能让 MySQL 在主题表上使用合适的索引，

... ON topics (id_owner, status)

... ON topics (id_post, status, id_owner)

关于php - MySQL 查询太慢，7 万行 12 秒，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/24440918/