MySQL 在mysql中索引日期时间字段是个好主意吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15425230/
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
Is it a good idea to index datetime field in mysql?
提问by Jaylen
I am working on designing a large database. In my application I will have many rows for example I currently have one table with 4 million records. Most of my queries use datetime clause to select data. Is it a good idea to index datetime fields in mysql database?
我正在设计一个大型数据库。在我的应用程序中，我将有很多行，例如我目前有一个包含 400 万条记录的表。我的大多数查询都使用 datetime 子句来选择数据。在 mysql 数据库中索引日期时间字段是个好主意吗？
Select field1, field2,.....,field15
from table where field 20 between now() and now + 30 days
I am trying to keep my database working good and queries being run smoothly
我正在努力使我的数据库正常运行并且查询运行顺利
More, what idea do you think I should have to create a high efficiency database?
更多，你认为我应该有什么想法来创建一个高效的数据库？
回答by Explosion Pills
MySQL recommends using indexes for a variety of reasons including elimination of rows between conditions: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
MySQL 建议使用索引的原因有很多，包括消除条件之间的行：http: //dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
This makes your datetime column an excellent candidate for an index if you are going to be using it in conditions frequently in queries. If your only condition is BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)and you have no other index in the condition, MySQL will have to do a full table scanon every query. I'm not sure how many rows are generated in 30 days, but as long as it's less than about 1/3 of the total rows it will be more efficient to use an index on the column.
如果您要在查询中频繁使用它，这将使您的日期时间列成为索引的绝佳候选者。如果您的唯一条件是BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)并且您在该条件中没有其他索引，则 MySQL 将必须对每个查询进行全表扫描。我不确定 30 天内生成了多少行，但只要它少于总行数的 1/3 左右，在列上使用索引会更有效。
Your question about creating an efficient database is very broad. I'd say to just make sure that it's normalized and all appropriate columns are indexed (i.e. ones used in joins and where clauses).
您关于创建高效数据库的问题非常广泛。我想说的是确保它被规范化并且所有适当的列都被索引（即在连接和 where 子句中使用的列）。
回答by Baurzhan
Hereauthor performed tests showed that integer unix timestamp is better than DateTime. Note, he used MySql. But I feel no matter what DB engine you use comparing integers are slightly faster than comparing dates so int index is better than DateTime index. Take T1 - time of comparing 2 dates, T2 - time of comparing 2 integers. Search on indexed field takes approximately O(log(rows)) time because index based on some balanced tree - it may be different for different DB engines but anyway Log(rows) is common estimation. (if you not use bitmask or r-tree based index). So difference is (T2-T1)*Log(rows) - may play role if you perform your query oftenly.
在这里作者进行的测试表明整数 unix 时间戳比 DateTime 更好。注意，他使用了 MySql。但是我觉得无论您使用什么数据库引擎，比较整数都比比较日期稍快，因此 int 索引优于 DateTime 索引。取 T1 - 比较 2 个日期的时间，T2 - 比较 2 个整数的时间。搜索索引字段大约需要 O(log(rows)) 时间，因为索引基于某种平衡树 - 对于不同的数据库引擎可能会有所不同，但无论如何 Log(rows) 是常见的估计。（如果您不使用位掩码或基于 r 树的索引）。所以区别是 (T2-T1)*Log(rows) - 如果您经常执行查询，可能会起作用。
