Mysql

MySql - 如何改進此查詢?

  • January 12, 2018

我有下表: http: //pastebin.com/XH6FvhZ8

和以下查詢:

SELECT *
FROM (SELECT *
     FROM `job_logs`
     ORDER BY id DESC) AS job_logs_tmp
WHERE (job_logs_tmp.user_id = ? AND job_logs_tmp.workflow_state = ?)
GROUP BY chain, subchain, job_type

這是這個查詢的解釋:http: //pastebin.com/AjXgzRBA

這個查詢通常需要超過 4K ms 才能執行,有什麼想法可以加快查詢執行速度嗎?如果需要,我可以在表中添加索引沒有問題

謝謝!

你想在這里達到什麼目的?我不是 MySQL 程序員;但是,根據我對 SQL 的一般理解,未在 GROUP BY 子句中列出的每一列都必須是聚合的一部分,例如

SELECT
   MAX(id) AS max_id,
   chain, subchain, job_type
FROM
   `job_logs`
WHERE
   user_id = ? AND
   workflow_state = ?
GROUP BY
   chain, subchain, job_type 

SELECT *在大多數(如果不是全部)SQL 實現中,在分組查詢中將不起作用。


更新

為了獲得每個組的 id 最高的行,您必須將上面的查詢嵌入到“外部”查詢中。

SELECT *
FROM `job_logs`
WHERE id IN (
   SELECT
       MAX(id) AS max_id
   FROM
       `job_logs`
   WHERE
       user_id = ? AND
       workflow_state = ?
   GROUP BY
       chain, subchain, job_type 
)

此查詢是確定性的,應該適用於大多數 SQL 方言。


一些查詢引擎使用連接比使用“IN 子查詢”執行得更好。你可以試試這個

SELECT A.*
FROM
   `job_logs` A
   INNER JOIN (
       SELECT
           MAX(id) AS max_id
       FROM
           `job_logs`
       WHERE
           user_id = ? AND
           workflow_state = ?
       GROUP BY
           chain, subchain, job_type) B
   ON A.id = B.max_id;

嘗試這個:

SELECT 
 *
FROM (
 SELECT *
 FROM `job_logs`
 WHERE user_id = ? AND workflow_state = ?
 ORDER BY id DESC
 ) tmp
GROUP BY chain, subchain, job_type

引用自:https://dba.stackexchange.com/questions/17198