mysql - 优化mysql查询

Question

执行以下查询需要很长时间:

SELECT HISTORY.VERSION_ID
     , HISTORY.ACTION
     , HISTORY.STATUS
     , HISTORY.APP_INSTANCE
     , HISTORY.ACTION_TIMESTAMP

FROM
  HISTORY
WHERE
  HISTORY.HISTORY_ID IN (SELECT max(H.HISTORY_ID) AS expr1 FROM HISTORY H GROUP BY H.VERSION_ID)

GROUP BY
  HISTORY.VERSION_ID
, HISTORY.ACTION
, HISTORY.STATUS
, HISTORY.APP_INSTANCE

, HISTORY.ACTION_TIMESTAMP

与表相关的其他重要事项是:

表:

Field               Type        Null    Key Default     Extra

HISTORY_ID          bigint(20)  NO  PRI (null)      auto_increment
VERSION_ID          bigint(20)  YES MUL (null)  
ACTION              varchar(50) YES     (null)  
STATUS              varchar(100)    YES     (null)  
ACTION_TIMESTAMP        timestamp   NO      CURRENT_TIMESTAMP   
APP_INSTANCE            varchar(50) YES     (null)  
TIME_TO_COMPLETE_PROCESS    bigint(35)  YES     (null)  

索引:

HISTORY 0   PRIMARY         1   HISTORY_ID  A   89  (null)  (null)      BTREE   

HISTORY 1   FK_HISTORY      1   VERSION_ID  A   44  (null)  (null)  YES BTREE

解释查询:

1   PRIMARY         HISTORY     ALL (null)  (null)      (null)  (null)  472468  Using where; Using temporary; Using filesort

2   DEPENDENT SUBQUERY  H       index   (null)  FK_HISTORY  9   (null)  6   Using index 

请帮助我优化查询。

谢谢，萨钦

Answer 1

SELECT  h.VERSION_ID,
        h.ACTION,
        h.STATUS,
        h.APP_INSTANCE,
        h.ACTION_TIMESTAMP
FROM    (
        SELECT  MAX(history_id) AS mid
        FROM    history
        GROUP BY
                version_id
        ) q
JOIN    history h
ON      h.history_id = q.mid

在 (version_id, history_id) 上创建复合索引以使其快速运行。