在 where 子句 MySQL 中使用日期时间索引

Question

我有一个包含 2 亿行的表，其中索引是在日期时间数据类型的"created_at"列中创建的。

显示创建表 [表名] 输出：

create table `table`
(`created_at` datetime NOT NULL)
PRIMARY KEY (`id`)
KEY `created_at_index` (`created_at`)
ENGINE=InnoDB AUTO_INCREMENT=208512112 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

created_at范围从 2020-04-01 ~ 2020-05-28。

我只想获取超过 2020-05-15 23：00：00 的行。

当我运行时：

EXPLAIN SELECT created_at
FROM table
where created_at >= '2020-05-15 23:00:00';

它说它输出：

rows       Extra
200mil   Using Where

我的理解是，在RDMS中，如果没有索引行没有排序，但是当您在列上创建索引时，它是按排序顺序排列的，因此在找到"2020-05-15 23：00：00"后，它将简单地返回之后的所有行。

此外，由于它的基数是 7mil，我认为使用索引会比全表扫描更好。

是因为我输入了日期作为字符串吗？ 但是当我尝试时

where created_at >= date('2020-05-15 23:00:00');

还是一样。

和

where created_at >= datetime('2020-05-15 23:00:00');

输出语法错误。

mysql 是否刚刚决定进行全表扫描会更有效？

编辑：

使用等于

EXPLAIN SELECT created_at
FROM table
where created_at = '2020-05-15';

输出：

key_len    ref     rows     Extra
5        const    51 

在 where 子句中，如果我将字符串更改为 date('2020-05-15'(，它会输出：

key_len    ref     rows     Extra
5        const    51      Using index condition

这是否意味着第一个相等查询没有使用索引？

Answer 1

您的所有查询都将利用列created_at上的索引。MySQL在与where子句的谓词匹配时始终使用索引。

您的explain的输出确实表明您没有此索引，这已由create table的输出确认。

只需创建索引，您的数据库就会使用它。

这是一个演示：

-- sample table, without the index
create table mytable(id int, created_at datetime);
--  the query does a full scan, as no index is available
explain select created_at from mytable where created_at >= '2020-05-15 23:00:00';

ID | select_type | 表 | 分区 | 类型 | possible_keys | 键 | key_len | ref | 行 | 筛选 |额外      -: |:---------- |:------ |:--------- |:--- |:------------ |:--- |:------ |:--- |---: |-------: |:----------  1 |简单 |我的表 |空|全部 |空|空|空|空|   1 |  100.00 |使用位置

-- now add the index
create index idx_mytable_created_at on mytable(created_at);
-- the query uses the index
explain select created_at from mytable where created_at >= '2020-05-15 23:00:00';

ID | select_type | 表 | 分区 | 类型 | possible_keys | 键 | key_len | ref | 行 | 筛选 |额外                   -: |:---------- |:------ |:--------- |:---- |:--------------------- |:--------------------- |:------ |:--- |---: |-------: |:-----------------------  1 |简单 |我的表 |空|索引 |idx_mytable_created_at |idx_mytable_created_at |6 |空|   1 |  100.00 |使用地点;使用索引

Answer 2

如果值均匀分布，则大约 25% 的行>= '2020-05-15 23:00:00'是的，当您需要如此大比例的表时，Mysql 更喜欢全表扫描而不是使用索引。

请参阅为什么 MySQL 并不总是使用索引进行选择查询？

在DATE上下文中，date('2020-05-15 23:00:00')与'2020-05-15'相同。

在DATETIME上下文中，datetime('2020-05-15 23:00:00')与'2020-05-15 23:00:00'相同。

Using index意味着INDEX是"覆盖"的，这意味着整个查询可以完全在索引的 BTree 中执行，而无需到达数据的 BTree。

Using index condition的意思完全不同 - 它与MySQL设计中的两层("处理程序"和"引擎"(相关的小优化有关。 (更多详细信息见"ICP"，又名"索引条件下推"。