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

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' 后，它将简单地返回所有行那。

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

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

 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 的输出s 确实表明你没有这个索引，这由你的 create table 的输出证实。 .

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

这是 a demo :

-- 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 | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      -: | :---------- | :------ | :--------- | :--- | :------------ | :--- | :------ | :--- | ---: | -------: | :---------- 1 | SIMPLE      | mytable | null       | ALL  | null          | null | null    | null |    1 |   100.00 | Using where

-- 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';

身份证 |选择类型 |表|分区|类型 |可能的 key |键 | key_len |引用 |行 |过滤|额外的
-: | :---------- | :------ | :--------- | :---- | :--------------------- | :--------------------- | :------ | :--- | ---: | -------: | :----------------------
1 |简单 |我的 table |空|索引 | idx_mytable_created_at | idx_mytable_created_at | 6 |空| 1 | 100.00 |使用哪里；使用索引