在mysql中索引日期时间字段是个好主意吗? [英] Is it a good idea to index datetime field in mysql?

问题描述

我正在设计一个大型数据库.在我的应用程序中，我将有很多行，例如我目前有一个包含 400 万条记录的表.我的大多数查询都使用 datetime 子句来选择数据.在mysql数据库中索引日期时间字段是个好主意吗?

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?

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?

推荐答案

MySQL 建议使用索引的原因有很多，包括消除条件之间的行:http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

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

如果您打算在查询中频繁使用它，这将使您的日期时间列成为索引的绝佳候选者.如果您的唯一条件是 BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY) 并且您在条件中没有其他索引，则 MySQL 将不得不执行 全表扫描 在每个查询上.我不确定 30 天内生成了多少行，但只要少于总行数的 1/3 左右，在列上使用索引会更有效.

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 scan on 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.

您关于创建高效数据库的问题非常广泛.我只想确保它已规范化并且所有适当的列都已编入索引(即在连接和 where 子句中使用的列).

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).

这篇关于在mysql中索引日期时间字段是个好主意吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！