提问人：Charles Faiga 提问时间：9/19/2008 最后编辑：fancyPantsCharles Faiga 更新时间：12/29/2020 访问量：76119
如何在MySql中DATETIME字段的日期部分创建索引
How does one create an index on the date part of DATETIME field in MySql
问：
如何在 DATETIME 字段的日期部分创建索引？
mysql> SHOW COLUMNS FROM transactionlist;
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO | PRI | NULL | auto_increment |
| WagerId | int(11) | YES | MUL | 0 | |
| TranNum | int(11) | YES | MUL | 0 | |
| TranDateTime | datetime | NO | | NULL | |
| Amount | double | YES | | 0 | |
| Action | smallint(6) | YES | | 0 | |
| Uid | int(11) | YES | | 1 | |
| AuthId | int(11) | YES | | 1 | |
+-------------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)
TranDateTime 用于在交易发生时保存交易的日期和时间
My Table 中有超过 1,000,000 条记录和语句
SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17'
需要很长时间。
编辑：
看看这篇关于“为什么MySQL的DATETIME可以而且应该避免”的博客文章"
答：
0赞
nathan
9/19/2008
#1
“解释”说了什么？（运行 EXPLAIN SELECT * FROM transactionlist where date（TranDateTime） = '2008-08-17'）
如果它因为 date（） 函数而没有使用你的索引，则范围查询应该快速运行：
SELECT * FROM transactionlist 其中 TranDateTime >= '2008-08-17' AND TranDateTime < '2008-08-18'
评论
1赞
JBB
9/19/2008
如果你使用 date（），你不会命中索引。Mysql 不能在这样的函数调用中使用索引。
5赞
Clinton Pierce
9/19/2008
#2
我不知道mySql的具体情况，但是仅对日期字段进行完整索引有什么害处？
然后只需搜索：
select * from translist
where TranDateTime > '2008-08-16 23:59:59'
and TranDateTime < '2008-08-18 00:00:00'
如果索引是 b 树或其他合理的东西，应该很快就会找到它们。
评论
0赞
Chloe
2/20/2019
您可以使用 .假定时间为 。>= '2008-08-16' and ... < '2008-08-18'00:00:00
0赞
A K
2/20/2019
您的意思是：>= '2008-08-17' 和 ...< '2008-08-18'.假定时间为 00：00：00
72赞
Michael Johnson
9/19/2008
#3
如果我没记错的话，这将运行整个表扫描，因为您正在通过函数传递列。MySQL将乖乖地为每一列运行该函数，绕过索引，因为查询优化器无法真正知道该函数的结果。
我会做的是：
SELECT * FROM transactionlist
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';
这应该给你2008-08-17发生的一切。
评论
1赞
kommradHomer
1/24/2013
我曾经认为这种用法只是“YYYY-MM-DD 00：00：00”的快捷方式
3赞
Arth
7/30/2014
我知道这是一个古老的答案，但我不得不指出，由于MySQL使用字符串比较;您的查询将返回正确的结果，并且不包含带有 .DATETIMETranDateTime=2008-08-18 00:00:00
1赞
andreas
3/8/2018
Arth，你有MySQL使用字符串比较的来源吗？旧版本是这种情况吗？对于MySQL 5.7来说，这绝对不是真的。尝试： create table foobar （ mytime timestamp ）;插入 foobar （mytime） 值 （'2008-08-18 00：00：00'）;选择 * 从 foobar where mytime 介于 '2008-08-17 00：00：00' 和 '2008-08-18 23：59：59' 之间;
0赞
Chloe
2/20/2019
比使用更快？BETWEENwhere TranDateTime >= '2008-08-17' and TranDateTime < '2008-08-18'
1赞
ΔO 'delta zero'
12/6/2020
这不是正确答案，问题是关于索引，而不是选择。使用生成的列查看答案。
17赞
Mike Tunnicliffe
9/19/2008
#4
我并不是说听起来很可爱，但一个简单的方法是添加一个新列，该列仅包含日期部分和索引。
评论
0赞
JBB
9/19/2008
是的，添加一个只包含时间部分的列，并完全消除 DATETIME。
0赞
Charles Faiga
9/19/2008
我目前的解决方案是添加另一个字段调用“date”，当我更新 TranDateTime 时，日期也会更新。我现在有一个关于“日期”的索引，并且由于我的表大小增加了 +-5%，查询速度要快得多
0赞
Justsalt
9/19/2008
#5
与其基于函数创建索引（如果在 mysql 中可能的话），不如让 where 子句进行范围比较。像这样：
其中 TranDateTime > '2008-08-17 00：00：00' 和 TranDateTime < '2008-08-17 11:59:59')
这允许数据库使用 TranDateTime 上的索引（有一个，对吧？）来执行选择。
2赞
Ray Jenkins
9/19/2008
#6
瓦列里·克拉夫丘克（Valeriy Kravchuk）在MySQL网站上针对此问题的功能请求中说使用此方法。
“同时，您可以使用字符列将 DATETIME 值存储为字符串，仅对前 N 个字符进行索引。通过在 MySQL 5 中谨慎使用触发器，您可以基于这个想法创建一个相当健壮的解决方案。
您可以编写一个非常容易的例程来添加此列，然后使用触发器使此列保持同步。此字符串列的索引应该非常快。
10赞
MarkR
9/19/2008
#7
不能只在日期部分创建索引。你有什么理由吗？
即使您可以只在日期部分上创建索引，优化器可能仍然不会将其用于上述查询。
我想你会发现
SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'
高效，做你想做的事。
1赞
antonia007
3/11/2010
#8
我不知道mySQL的具体情况，但是仅对日期字段进行完整索引有什么害处？
如果您将功能魔法用于 * 树、哈希、...消失了，因为要获取值，必须调用该函数。但是，由于您不知道前面的结果，因此必须对表格进行全面扫描。
没有什么可补充的。
也许你的意思是计算（计算？）索引......但到目前为止，我只在Intersystems Caché中看到过这一点。我不认为关系数据库（AFAIK）有这种情况。
在我看来，一个好的解决方案如下（更新的 clintp 示例）：
SELECT * FROM translist
WHERE TranDateTime >= '2008-08-17 00:00:00.0000'
AND TranDateTime < '2008-08-18 00:00:00.0000'
你是否使用或在我看来没有区别（我通常以这种格式使用它）。00:00:00.000000:00
-1赞
Mari
8/8/2011
#9
创建一个仅包含日期的新字段，然后为其编制索引。convert(datetime, left(date_field,10))
评论
0赞
Chloe
2/20/2019
为什么不直接使用？date(date_field)
2赞
Valentin Rusk
2/28/2012
#10
一个非常好的解决方案是使用时间戳作为时间，而不是日期时间。 它被存储为 INT 并被索引得足够好。 就我个人而言，我在事务表上遇到了这样的问题，它有大约一百万条记录并且速度变慢了，最后我指出这是由错误的索引字段（日期时间）引起的。 现在它运行得非常快。
1赞
Dr. Tyrell
10/17/2014
#11
datetime LIKE something% 也不会捕获索引。
使用这个： WHERE datetime_field >= curdate（）;
这将捕获索引，
并涵盖今天：00：00：00 直到今天：23：59：59
完成。
17赞
Liran Brimer
6/14/2017
#12
另一个选项（与版本 5.7.3 及更高版本相关）是基于 datetime 列创建生成的/虚拟列，然后对其进行索引。
CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_datetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;
评论
1赞
7/25/2017
为什么是存储的而不是虚拟的？
1赞
Liran Brimer
7/25/2017
如果要编制索引，则必须将其存储。如果没有索引，它可以是虚拟的
1赞
7/25/2017
谢谢，我想，我对这篇文章感到困惑 percona.com/blog/2016/03/04/......
0赞
ΔO 'delta zero'
12/6/2020
这应该是正确答案，我发现日期索引优于日期时间索引，即使使用 BTREE。
1赞
ΔO 'delta zero'
12/6/2020
顺便说一句，现在InnoDB也支持VIRTUAL列上的索引。
0赞
Walf
12/14/2018
#13
如果可以选择修改表，或者您正在编写一个新表，请考虑将日期和时间存储在具有相应类型的单独列中。通过具有更小的键空间和更少的存储空间（与从日期时间派生的仅日期列相比），可以提高性能。这也使得在复合键中使用是可行的，甚至在其他列之前。
在OP的案例中：
+-------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+------------------+------+-----+---------+----------------+
| TransactionNumber | int(10) unsigned | NO | PRI | NULL | auto_increment |
| WagerId | int(11) | YES | MUL | 0 | |
| TranNum | int(11) | YES | MUL | 0 | |
| TranDate | date | NO | | NULL | |
| TranTime | time | NO | | NULL | |
| Amount | double | YES | | 0 | |
| Action | smallint(6) | YES | | 0 | |
| Uid | int(11) | YES | | 1 | |
| AuthId | int(11) | YES | | 1 | |
+-------------------+------------------+------+-----+---------+----------------+
评论