此查询在 VPS 上运行超过 12 秒。它连接了 3 个表。只有第一个“主题”大约有 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 | |
+------------------------+---------------------+------+-----+---------+-------+
最佳答案
一个可能的怀疑是 DEPENDENT SUBQUERY。
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 避免昂贵的“使用文件排序”操作。部分问题可能是谓词中的 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 查询太慢，70k 行 12 秒，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24440918/