Home >Database >Mysql Tutorial >mysqltimestamp和long存储时间效率比较_MySQL

mysqltimestamp和long存储时间效率比较_MySQL

WBOYOriginal: 2016-06-02 08:49:481564browse

<code class="hljs sql">show create table 20130107date;

CREATE TABLE `20130107date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `c_date_long` int(20) NOT NULL,
  `idx_date` timestamp NOT NULL DEFAULT &#39;0000-00-00 00:00:00&#39;,
  `idx_date_long` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `20130107date_idx_date` (`idx_date`),
  KEY `20130107date_idx_long` (`idx_date_long`)
) ENGINE=InnoDB
</code>

里面有90w数据,都是随机的时间.<br> 先看没有索引的全表扫描

1 :

<code class="hljs sql"><code class="hljs sql">select COUNT(*) from 20130107date
where c_date BETWEEN DATE(&#39;20110101&#39;) and DATE(&#39;20110102&#39;)</code></code>

<code class="hljs sql">这个需要1.54s

<code class="hljs sql">2:

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql">select COUNT(*) from 20130107date
where c_date_long BETWEEN UNIX_TIMESTAMP(&#39;20110101&#39;) and UNIX_TIMESTAMP(&#39;20110102&#39;)</code></code></code>

<code class="hljs sql"><code class="hljs sql">这个是2.3s

<code class="hljs sql"><code class="hljs sql">但是可以这样搞<br> 3 :

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">select UNIX_TIMESTAMP(&#39;20110101&#39;),UNIX_TIMESTAMP(&#39;20110102&#39;);</code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql">得到结果1293811200和1293897600

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql">然后

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">select COUNT(*) from 20130107date
where c_date_long BETWEEN 1293811200 and 1293897600；</code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">发现变成了0.61s<br> 1和2的差距还可以说是比较int和比较timestamp的差距,那么2和3的差距呢?难道多出来的时间是每一条记录都要evaluate UNIX_TIMESTAMP(‘20110102’)?

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">然后用索引

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">select COUNT(*) from 20130107date
where idx_date_long BETWEEN UNIX_TIMESTAMP(&#39;20110101&#39;) and UNIX_TIMESTAMP(&#39;20110102&#39;);

select COUNT(*) from 20130107date
where idx_date BETWEEN &#39;20110101&#39; and &#39;20110102&#39;</code></code></code></code></code></code>

<code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql"><code class="hljs sql">毫无悬念,两个基本都是瞬时的.

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：获取SQL Server数据库里表占用容量大小_MySQLNext article：MySQL学习笔记9：连接查询_MySQL

See more

mysqltimestamp和long存储时间效率比较_MySQL

Related articles