mysql - 索引非常慢的范围查询

Question

我的 InnoDB 表有大约 10 亿条日志条目。我正在尝试对表进行简单的选择，从而产生大约 3000 万行。该查询包含一个包含时间戳的 int 字段的范围条件。

查询: SELECT * FROM logs WHERE created_at >= 1446422400 AND created_at <= 1447027199

此查询性能不佳，运行了大约 6-7 小时，每秒只能获得大约 1.000 行。 created_at 上有一个索引，查询正在使用该索引。当我做一个简单的 SELECT * FROM logs WHERE id >= xx AND id <= yy ，设置 xx 和 yy 使得结果集几乎相同(3000 万行)，性能很好，运行 max. 10 分钟。

这真的让我很烦恼。为什么 PK 上的范围如此有效，而在索引上却如此糟糕？如何优化索引？我几天前重新创建了表格，所以索引应该没问题。

更多信息:

表格方案:

| logs | CREATE TABLE `logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `categoryid` varchar(15) NOT NULL,
  `type` varchar(15) NOT NULL,
  `text` varchar(500) NOT NULL,
  `created_at` int(7) NOT NULL,
  `status` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `status_categoryid_type` (`status`,`categoryid`,`type`),
  KEY `created_at` (`created_at`),
) ENGINE=InnoDB AUTO_INCREMENT=1335078012 DEFAULT CHARSET=latin1 |

解释:

| id   | select_type | table   | type  | possible_keys | key        | key_len | ref  | rows     | Extra                 |
+------+-------------+---------+-------+----------------------------+---------+------+----------+-----------------------+
|    1 | SIMPLE      | logs    | range | created_at    | created_at | 4       | NULL | 31707348 | Using index condition |

目标:

我的目标是以每周 block 的形式从表中检索数据。上面的范围指定了一个星期范围。后面想批量查询全表，一直获取特定周的数据。

Answer 1

id 查询速度这么快的原因很可能是因为 mysql 为 id 创建了聚集索引，因为它是主键，而 created_at 索引没有聚集，因为 created_at 不是主键。

我不确定为什么差异如此之大，因为 created_at 日期很可能与 id 一样连续，但显然是这样。

那么，试试这个:

SELECT id FROM logs WHERE created_at >= 1446422400 LIMIT 1 

(将结果赋给id1)

SELECT id FROM logs WHERE created_at <= 1447027199 ORDER BY id DESC LIMIT 1 

(将结果赋给id2)

SELECT * FROM logs WHERE id >= id1 AND id <= id2