查询优化

Question

我有两个问题。

首先：

SELECT * FROM `table` WHERE col='xyz' LIMIT 100
//Time Taken: 0.0047s

第二：

SELECT * FROM `table` WHERE col='xyz' ORDER BY Id DESC LIMIT 100
//Time Taken: 1.8208s

第二种方法需要更长的时间。我知道这是为什么，因为首先我必须选择整个表，然后进行排序，而第一个查询只返回前100行。

有没有办法使用其他方法进行排序，比如选择最后100行，然后进行排序？或者是我做错了查询，可以更快地进行查询？

注意Id是自动递增的，因此选择最后一行仍然会在排序时返回正确的数据。

CREATE TABLE `table`(
    `Id` BIGINT NOT NULL AUTO_INCREMENT,
    `dateReg` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`Id`)
) ENGINE=MyISAM

Answer 1

对于连续的ids：

SELECT t.*
  FROM TABLE t
  JOIN (SELECT MAX(t.id) 'maxid'
          FROM TABLE t) max ON t.id BETWEEN max.maxid-100 AND max.maxid
 WHERE t.col = 'xyz' 

对于非连续ids：

SELECT a.*
  FROM (SELECT t.*,
               @rownum := @rownum+1 AS rownum
          FROM TABLE t, (SELECT @rownum := 0) r
         WHERE t.col = 'xyz') a,
       (SELECT COUNT(t.*) 'max'
          FROM TABLE t
         WHERE t.col = 'xyz') m
WHERE a.rownum BETWEEN m.max-100 AND m.max

Answer 2

如果对表使用MyISAM，则在(col, id)上创建复合索引。

在InnoDB中，PRIMARY KEY作为行指针隐式地包含在表中，因为InnoDB表是按索引组织的。

对于InnoDB，要在(col, id)上创建一个复合索引，只需在col上创建一个索引并确保id是PRIMARY KEY即可。

此索引将用于过滤col和按id排序。

索引是一种B-Tree结构，因此它可以以相同的效率迭代ASC和DESC。

Answer 3

您的id列应该设置为主键(或者，如果您有其他主键，则无论如何都应该在其上放置一个索引)。这应该会使查询速度相当快。