在where子句mysql中使用datetime索引

rqmkfv5c 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(229)

我有一个有2亿行的表，其中索引是在“created\u at”列中创建的，该列是datetime数据类型。
显示创建表[tablename]输出：

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'

创建时间范围为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

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

sql mysql Date where-clause query-optimization

来源：https://stackoverflow.com/questions/62076778/using-datetime-index-in-where-clause-mysql

2条答案

按热度按时间

q43xntqr1#

所有查询都将利用列上的索引 created_at . mysql总是在匹配 where 条款。
您的输出 explain s表示您没有这个索引，这是由您的 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 | 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';

id | select_type | table   | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                   
-: | :---------- | :------ | :--------- | :---- | :--------------------- | :--------------------- | :------ | :--- | ---: | -------: | :-----------------------
 1 | SIMPLE      | mytable | null       | index | idx_mytable_created_at | idx_mytable_created_at | 6       | null |    1 |   100.00 | Using where; Using index

赞(0）回复(0）举报 2021-08-13

eni9jsuy2#

如果值均匀分布，则大约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 是“covering”，这意味着整个查询可以完全在索引的btree中执行，而不必访问数据的btree。 Using index condition 这意味着一些完全不同的东西——它与mysql设计中的两层（“处理程序”和“引擎”）相关的一个小优化有关(更多细节请参见“icp”又名“索引条件下推”。）

赞(0）回复(0）举报 2021-08-13

我来回答

在where子句mysql中使用datetime索引

2条答案

相关问题

热门标签

最新问答