使用时间戳（或日期时间）作为主键（或聚集索引的一部分）的一部分 [英] Use timestamp(or datetime) as part of primary key (or part of clustered index)

问题描述

我经常使用以下查询：

SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp

我想优化此查询，我正在考虑把时间戳作为聚集索引的主键的一部分，我认为如果时间戳是主键的一部分，则表中插入的数据按时间戳字段顺序写入磁盘。此外，我认为这样可以改善我的查询，但是不知道这是否有帮助。

I would like to optimize this query, and I am thinking about put timestamp as part of primary key for clustered index, I think if timestamp is part of primary key , data inserted in table has write to disk sequentially by timestamp field.Also I think this improve my query a lot, but am not sure if this would help.

table has 3-4 million+ rows.
timestamp field never changed.
I use mysql 5.6.11

Anothet点是：如果这改善了我的查询，最好使用时间戳（mysql 5.6中的4个字节）或datetime（mysql 5.6中的5个字节）？

Anothet point is : if this is improve my query , it is better to use timestamp(4 byte in mysql 5.6) or datetime(5 byte in mysql 5.6)?

推荐答案

行不是很大。

数据类型datetime和timestamp之间的一个字节的差异是最后一个的事情，你应该考虑在这两种数据类型。 查看其规格。

A one-byte difference between the data types datetime and timestamp is the last thing you should consider in choosing between those two data types. Review their specs.

使您的主键的时间戳部分是一个坏的坏主意。请考虑查看SQL数据库中主键的含义。

Making a timestamp part of your primary key is a bad, bad idea. Think about reviewing what primary key means in a SQL database.

将索引放在时间戳列上。获取执行计划，并将其粘贴到您的问题中。确定您的中位数查询性能，并将 粘贴到您的问题中。

Put an index on your timestamp column. Get an execution plan, and paste that into your question. Determine your median query performance, and paste that into your question, too.

将一天的行从我的台式机上索引的400万行表需要2ms。 （它返回大约8000行。）

Returning a single day's rows from an indexed, 4 million row table on my desktop computer takes 2ms. (It returns around 8000 rows.)

这篇关于使用时间戳（或日期时间）作为主键（或聚集索引的一部分）的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！