时间戳

首页 > 解决方案 > 为什么索引不适用于非唯一日期时间列？

mysql - 为什么索引不适用于非唯一日期时间列？

问题描述

表结构：

CREATE TABLE `stat_old` (
  `dt` datetime NOT NULL,
  `offer_id` int(11) DEFAULT NULL,
  `aff_id` int(11) DEFAULT NULL,
  UNIQUE KEY `dt` (`dt`,`offer_id`,`aff_id`),
  KEY `dt_2` (`dt`),
  KEY `offer_id` (`offer_id`),
  KEY `aff_id` (`aff_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

该dt字段存储转换为小时的日期时间值，例如“2019-01-01 01:00:00”、“2019-01-01 02:00:00”，并且它不是唯一的。

询问：

explain select *
FROM stat_old
WHERE
  dt between '2019-02-11 16:00:00' and '2019-02-18 15:59:59'
order by dt;

结果：

+----+-------------+----------+------+---------------+------+---------+------+----------+-----------------------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows     | Extra                       |
+----+-------------+----------+------+---------------+------+---------+------+----------+-----------------------------+
|  1 | SIMPLE      | stat_old | ALL  | dt,dt_2       | NULL | NULL    | NULL | 18914072 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+----------+-----------------------------+

如您所见，它几乎扫描了包含 20,044,835 行的整个表。实际上结果数据只有 2,108,707 行。为什么 index ondt不使用？我怎样才能解决这个问题 ？

标签： mysqlquery-optimizationsql-execution-plan

解决方案

当按顺序检索更大的日期范围时，制作dt主键的第一部分将有所帮助dt：

alter table stat_old
drop key dt,
drop key dt_2,
add primary key (`dt`,`offer_id`,`aff_id`)

推荐阅读