如何在 MySql 中的 DATETIME 字段的日期部分创建索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/95183/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does one create an index on the date part of DATETIME field in MySql
提问by Charles Faiga
How do I create an index on the date part of DATETIME field?
如何在 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 is used to save the date and time of a transaction as it happens
TranDateTime 用于保存交易发生时的日期和时间
My Table has over 1,000,000 records in it and the statement
我的表中有超过 1,000,000 条记录和语句
SELECT * FROM transactionlist where date(TranDateTime) = '2008-08-17'
takes a long time.
花费很长时间。
EDIT:
编辑：
Have a look at this blog post on "Why MySQL's DATETIME can and should be avoided"
看看这篇关于“为什么 MySQL 的 DATETIME 可以而且应该避免”的博客文章
采纳答案by Michael Johnson
If I remember correctly, that will run a whole table scan because you're passing the column through a function. MySQL will obediently run the function for each and every column, bypassing the index since the query optimizer can't really know the results of the function.
如果我没记错的话，这将运行整个表扫描，因为您正在通过函数传递列。MySQL 会乖乖地为每一列运行该函数，绕过索引，因为查询优化器无法真正知道该函数的结果。
What I would do is something like:
我会做的是：
SELECT * FROM transactionlist
WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-17 23:59:59.999999';
That should give you everything that happened on 2008-08-17.
这应该给你 2008-08-17 发生的一切。
回答by Mike Tunnicliffe
I don't mean to sound cute, but a simple way would be to add a new column that only contained the date part and index on that.
我并不是想听起来很可爱，但一种简单的方法是添加一个仅包含日期部分和索引的新列。
回答by MarkR
You can't create an index on just the date part. Is there a reason you have to?
您不能仅在日期部分创建索引。你有理由这样做吗？
Even if you could create an index on just the date part, the optimiser would probably still not use it for the above query.
即使您可以只在日期部分创建索引，优化器可能仍然不会将它用于上述查询。
I think you'll find that
我想你会发现
SELECT * FROM transactionlist WHERE TranDateTime BETWEEN '2008-08-17' AND '2008-08-18'
Is efficient and does what you want.
效率高，做你想做的事。
回答by Liran Brimer
Another option (relevant for version 5.7.3and above) is to create a generated/virtual column based on the datetime column, then index it.
另一个选项（与5.7.3及更高版本相关）是基于日期时间列创建一个生成/虚拟列，然后对其进行索引。
CREATE TABLE `table` (
`my_datetime` datetime NOT NULL,
`my_date` varchar(12) GENERATED ALWAYS AS (DATE(`my_daetime`)) STORED,
KEY `my_idx` (`my_date`)
) ENGINE=InnoDB;
回答by Clinton Pierce
I don't know about the specifics of mySql, but what's the harm in just indexing the date field in its entirety?
我不知道 mySql 的具体细节，但是仅对整个日期字段进行索引有什么害处？
Then just search:
然后只需搜索：
select * from translist
where TranDateTime > '2008-08-16 23:59:59'
and TranDateTime < '2008-08-18 00:00:00'
If the indexes are b-trees or something else that's reasonable, these should get found quickly.
如果索引是 b-trees 或其他合理的东西，这些应该很快被找到。
回答by Ray Jenkins
Valeriy Kravchuk on a feature request for this very issue on the MySQL site said to use this method.
Valeriy Kravchuk 在 MySQL 站点上针对此问题的功能请求说使用此方法。
"In the meantime you can use character columns for storing DATETIME values as strings, with only first N characters being indexed. With some careful usage of triggers in MySQL 5 you can create a reasonably robust solution based on this idea."
“与此同时，您可以使用字符列将 DATETIME 值存储为字符串，只对前 N 个字符进行索引。在 MySQL 5 中谨慎使用触发器，您可以基于这个想法创建一个相当可靠的解决方案。”
You could write a routine pretty easy to add this column, and then with triggers keep this column synced up. The index on this string column should be pretty quick.
您可以编写一个很容易添加此列的例程，然后使用触发器保持此列同步。此字符串列上的索引应该很快。
回答by Valentin Rusk
The one and good solution that is pretty good working is to use timestamp as time, rather than datetime. It is stored as INT and being indexed good enough. Personally i encountered such problem on transactions table, that has about million records and slowed down hard, finally i pointed out that this caused by bad indexed field (datetime). Now it runs very quick.
一个很好的解决方案是使用时间戳作为时间，而不是日期时间。它存储为 INT 并且索引足够好。我个人在事务表上遇到过这样的问题，它有大约百万条记录并且速度很慢，最后我指出这是由错误的索引字段（日期时间）引起的。现在它运行得非常快。
回答by Dr. Tyrell
datetime LIKE something% will not catch the index either.
datetime LIKE something% 也不会捕获索引。
Use this: WHERE datetime_field >= curdate();
That will catch the index,
and cover today:00:00:00 up to today:23:59:59
Done.
使用这个： WHERE datetime_field >= curdate();
这将捕获索引，
并涵盖今天：00:00:00 到今天：23:59:59
完成。
回答by antonia007
I don't know about the specifics of mySQL, but what's the harm in just indexing the date field in its entirety?
我不知道 mySQL 的具体细节，但是仅对整个日期字段进行索引有什么害处？
If you use functional magic for * trees, hashes, ... is gone, because for obtaining values you must call the function. But, because you do not know the results ahead, you have to do a full scan of the table.
如果您对 * 树、散列、... 使用函数式魔法，则消失了，因为要获取值，您必须调用该函数。但是，因为您不知道前面的结果，所以您必须对表进行全面扫描。
There is nothing to add.
没有什么可添加的。
Maybe you mean something like computed (calculated?) indexes... but to date, I have only seen this in Intersystems Caché. I don't think there's a case in relational databases (AFAIK).
也许您的意思是诸如计算（计算？）索引之类的东西……但迄今为止，我只在 Intersystems Caché 中看到过这种情况。我认为关系数据库 (AFAIK) 中没有这种情况。
A good solution, in my opinion, is the following (updated clintp example):
在我看来，一个好的解决方案如下（更新的 clintp 示例）：
SELECT * FROM translist
WHERE TranDateTime >= '2008-08-17 00:00:00.0000'
AND TranDateTime < '2008-08-18 00:00:00.0000'
Whether you use 00:00:00.0000or 00:00in my opinion makes no difference (I've generally used it in this format).
无论您使用00:00:00.0000还是00:00我认为都没有区别（我通常以这种格式使用它）。
回答by Justsalt
Rather than making an index based on a function (if that is even possible in mysql) make your where clause do a range comparison. Something like:
而不是基于函数创建索引（如果这在 mysql 中甚至可能）让您的 where 子句进行范围比较。就像是：
Where TranDateTime > '2008-08-17 00:00:00' and TranDateTime < '2008-08-17 11:59:59')
其中 TranDateTime > '2008-08-17 00:00:00' 和 TranDateTime < '2008-08-17 11:59:59')
This lets the DB use the index on TranDateTime (there is one, right?) to do the select.
这让 DB 使用 TranDateTime 上的索引（有一个，对吧？）来进行选择。
