MySQL:获取早于xxx的最新条目，性能

问题描述：

我有一个看起来像这样的表:

I have a table that looks like so:

ID | objectID | time | ...
-------------------
 1 |        1 | ...
 2 |        1 | ...
 3 |        1 | ...
 4 |        2 | ...
 5 |        2 | ...
 6 |        3 | ...
 7 |        4 | ...

ID是主键，objectID是唯一的. 我试图提高查询的性能，以获取所有objectID的最新条目，但条目不应比特定值新. 我尝试遵循以下两个查询，这两个查询都应提供相同(和正确的结果):

ID is the primary key, objectID is non-unique. I am trying to increase performance on a query to get the most recent entries for all objectIDs, but the entries should not be newer than a certain value. I tried to following two queries, which should both provide the same (and correct results):

SELECT *
FROM (
    SELECT *
    FROM table
    WHERE time <= XXX
    ORDER BY time DESC
)
GROUP BY objectID

AND

SELECT *
FROM table AS t     
INNER JOIN (
    SELECT ID, MAX(time)
    FROM table
    WHERE time <= 1353143351
    GROUP BY objectID
) s
USING (ID)

第一个查询的解释告诉我

An EXPLAIN for the first query tells me

id | select_type | table      | type | possible_keys | key     | key_len | ref  | rows   | Extra
------------------------------------------------------------------------------------
1  | PRIMARY     | <derived2> | ALL  | NULL          | NULL    | NULL    | NULL | 145827 | Using temporary; Using filesort
2  | DERIVED     | tbl_test   | ALL  | NULL          | NULL    | NULL    | NULL | 238694 | Using filesort

它说的第二个查询

id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows   | Extra
------------------------------------------------------------------------------------
1  | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL | 325 
1  | PRIMARY     | t          | eq_ref | PRIMARY,ID    | PRIMARY | 4       | s.ID | 1   
2  | DERIVED     | tbl_test   | index  | NULL          | ID      | 12      | NULL | 238694 | Using where; Using index; Using temporary; Using filesort

(tbl_test是我要测试的表)

(tbl_test is my table for testing)

第二个查询似乎(快得多)，但是在200k DB条目下运行时间为0.1秒，仍然不是非常快. 有没有办法提高查询性能?缺少索引吗?

The second query seems to be (much) faster, but still not extreme fast with a runtime of 0.1 secs at ~200k DB entries. Is there a way to increase performance of the query? Any missing indexes?

提前谢谢！

解释eggys查询(请参阅他的帖子中的查询):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY <derived2>  ALL NULL    NULL    NULL    NULL    325 
1   PRIMARY lab_case_test   ALL NULL    NULL    NULL    NULL    238694  Using where; Using join buffer
2   DERIVED lab_case_test   index   NULL    ID  12  NULL    238694  Using where; Using index; Using temporary; Using f...

CREATE TABLE `lab_case_test` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `caseID` int(11) NOT NULL,
 `time` int(11) NOT NULL,
 // ....
 PRIMARY KEY (`ID`),
 KEY `ID` (`ID`,`caseID`,`time`),
 KEY `caseID` (`caseID`,`time`)
) ENGINE=MyISAM AUTO_INCREMENT=238695 DEFAULT CHARSET=utf8

答

您要在(objectID, time)上使用复合索引:

You want a composite index over (objectID, time):

ALTER TABLE my_table ADD INDEX (objectID, time)

这样做的原因是MySQL可以直接从索引树中检索每个objectID的最大值.然后，它还可以使用相同的索引再次针对该表进行连接，以找到

The reason for this is that MySQL can then retrieve the maximum time for each objectID directly from the index tree; it can then also use the same index in joining against the table again to find the groupwise maximum records using something like your second query (but one should join on both objectID and time—I like to use a NATURAL JOIN in cases like this):

SELECT *
FROM   my_table NATURAL JOIN (
  SELECT   objectID, MAX(time) time
  FROM     my_table
  WHERE    time <= 1353143351
  GROUP BY objectID
) t

MySQL:获取早于xxx的最新条目，性能

相关推荐