MySQL忽略我在时间戳列上的索引 [英] MySQL ignores my index on a timestamp column

问题描述

我的表有800,000多行。我需要提高在某个时间间隔提取行的查询的性能。

My table has more than 800,000 rows. I need to improve the performance of a query which extract rows on a time interval.

我的表：

CREATE TABLE `bets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  ...
  `stamp_end` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bets_stamp_end_index` (`stamp_end`)
) ENGINE=InnoDB AUTO_INCREMENT=875534 DEFAULT CHARSET=utf8;

有时此查询使用索引：

EXPLAIN SELECT * FROM bets WHERE
bets.stamp_end BETWEEN '2016-05-01 00:00:00' AND '2016-06-01 00:00:00';

+----+-------------+-------+-------+----------------------+----------------------+---------+------+--------+-------------+
| id | select_type | table | type  | possible_keys        | key                  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+--------+-------------+
|  1 | SIMPLE      | bets  | **range** | bets_stamp_end_index | bets_stamp_end_index | 5       | NULL | **158210** | Using where |
+----+-------------+-------+-------+----------------------+----------------------+---------+------+--------+-------------+

当我编写此查询时，从未使用过索引：

When I write this query, the index is never used:

EXPLAIN SELECT * FROM bets WHERE
stamp_end >= DATE_SUB(DATE(NOW()), INTERVAL 56 DAY);

+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys        | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | bets  | **ALL**  | bets_stamp_end_index | NULL | NULL    | NULL | **857651** | Using where; Using filesort |
+----+-------------+-------+------+----------------------+------+---------+------+--------+-----------------------------+

注意：
我已经运行了OPTIMIZE TABLE。第一个查询仅在某些日期间隔内使用索引。

Note: I already run OPTIMIZE TABLE. The first query use the index only for some date intervals.

为什么不使用索引？有解决方案吗？

Why the index is not used? Any solution?

推荐答案

使用索引意味着在索引BTree和数据BTree之间来回跳动。

Using an index means bouncing back and forth between the index BTree and the data BTree.

如果需要访问的表少于大约20％，则弹跳是值得的。

If less than about 20% of the table needs to be accessed, the bouncing is worth it.

如果需要的访问量更多要被访问，实际上扫描表的速度更快，可以过滤出 WHERE 子句所说要排除的内容。

If more than that needs to be accessed, it is actually faster to scan the table, filtering out what the WHERE clause says to exclude.

（20％是不精确的，优化器并不总是达到正确的临界点。）

(The 20% is imprecise, and the Optimizer does not always make the 'right' cutoff.)

这篇关于MySQL忽略我在时间戳列上的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！