首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL里sysdate和now

MySQL里sysdate和now

作者头像
jeanron100
发布2018-09-29 15:06:14
1.8K0
发布2018-09-29 15:06:14
举报

这是学习笔记的第 1723 篇文章

昨天在做一个SQL优化的时候,注意到一个细节问题,那就是使用sysdate后无法启用索引,感觉是走了全表扫描,但是使用now()就能秒出数据。对于这个问题,我看了下官方文档,这个描述就好像你打开了一个贝壳,里面有一颗珍珠一般,如果你不尝试打开,则仅仅会把它当做一个黑盒子,很容易形成攻略型的经验,这个是不建议的。

问题的现象如下:

有一个表dic_history_20180823_0,数据量大概在1500万左右。modify_time是有一个副主索引的。如果使用如下的语句,可以得到近一个小时的数据情况。

但是结果的差别却很大。

>>select count(fsm_id ) from `dic_history_20180823_0` where modify_time between (sysdate()+interval(-1) hour) and sysdate();

+----------------+

| count(fsm_id ) |

+----------------+

| 0 |

+----------------+

1 row in set (47.87 sec)

>>select count(fsm_id ) from `dic_history_20180823_0` where modify_time between (now()+interval(-1) hour) and now();

+----------------+

| count(fsm_id ) |

+----------------+

| 0 |

+----------------+

1 row in set (0.00 sec)

可以看到使用了sysdate()之后,性能极差,其实就是一个全表扫描。

而使用了now()的方式之后,则数据秒出。

这个是什么原因呢。

首先我们来看下MySQL里面的日期函数,内容还是很丰富的。光要得到当前的日期信息,就有不少于4个函数。

如果要模拟这个问题,可以使用对比的方式来做。 中间可以通过sleep(x)的方式把数据过程放大。

如果是now()的方式,得到的是一个相对静态的值,哪怕在一个SQL里面做多项任务,而对于sysdate()的方式,得到的始终是一个动态的值。

>>SELECT NOW(), SLEEP(2), NOW();

+---------------------+----------+---------------------+

| NOW() | SLEEP(2) | NOW() |

+---------------------+----------+---------------------+

| 2018-08-24 17:13:54 | 0 | 2018-08-24 17:13:54 |

+---------------------+----------+---------------------+

1 row in set (2.00 sec)

>> SELECT SYSDATE(), SLEEP(2), SYSDATE();

+---------------------+----------+---------------------+

| SYSDATE() | SLEEP(2) | SYSDATE() |

+---------------------+----------+---------------------+

| 2018-08-24 17:14:43 | 0 | 2018-08-24 17:14:45 |

+---------------------+----------+---------------------+

1 row in set (2.00 sec)

再进一步,对于now()的数据,可以理解为是一个常量,而sysdate()是一个变量。

再进一步,为什么会出现这种情况。

其实本质就是在优化器层面的处理了,now()得到的是一个静态值,所以在查询中,优化器是能够识别出对应的数据区间。而sysdate()的方式在优化器中是没法直接识别到对应的值的,所以每次调用都会重新获取。

感兴趣的可以看下官方文档的解释:

NOW([fsp])

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

If the fsp argument is given to specify a fractional seconds precision from 0 to 6, the return value includes a fractional seconds part of that many digits.

mysql> SELECT NOW();
        -> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
        -> 20071215235026.000000

NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes.

mysql> SELECT NOW(), SLEEP(2), NOW();+---------------------+----------+---------------------+
| NOW()               | SLEEP(2) | NOW()               |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 |        0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+

mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(2) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 |        0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+

In addition, the SET TIMESTAMP statement affects the value returned by NOW() but not by SYSDATE(). This means that timestamp settings in the binary log have no effect on invocations of SYSDATE(). Setting the timestamp to a nonzero value causes each subsequent invocation of NOW() to return that value. Setting the timestamp to zero cancels this effect so that NOW() once again returns the current date and time.

See the description for SYSDATE() for additional information about the differences between the two functions.

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2018-08-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档