2017-12-05 DBA日记，mysql的datetime字段索引不能识别sysdate

一、案例描述

今日在进行MYSQL日常检查时，发现有一条SQL语句，在字段类型为datetime并创建索引的情况下，进行between (sysdate()+interval(-1) hour) and sysdate()时并不会使用索引，但是用now()代替sysdate就可以使用索引，这是为什么呢？

二、问题

为什么datetime字段(已有且只有一个关键字的索引）与sysdate()进行比较运算时不使用索引，而用now()则会？

三、思路

不使用索引的情况一般是类型不对，那么sysdate()返回的类型不是datetime？ 判断sysdate()返回的类型

四、收集数据与分析

1. 确认sysdate(),now()的返回类型

create table qqt.t1 as select now();
create table qqt.t2 as select sysdate();
desc qqt.t1;
desc qqt.t2;

发现now()和sysdate()返回的都是datetime类型

2. sysdate()和now()有什么区别呢？

SYSDATE()

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

SYSDATE() returns the time at which it executes. This differs from the behavior for NOW(), which 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.)


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

Because SYSDATE() can return different values even within the same statement, and is not affected by SET TIMESTAMP, it is nondeterministic and therefore unsafe for replication if statement-based binary logging is used. If that is a problem, you can use row-based logging.

Alternatively, you can use the --sysdate-is-now option to cause SYSDATE() to be an alias for NOW(). This works if the option is used on both the master and the slave.

The nondeterministic nature of SYSDATE() also means that indexes cannot be used for evaluating expressions that refer to it.

根据官方说明已经很好理解了，因为sysdate()是该函数执行时动态获取时间，NOW()是语句开始时就获取时间，这时优化器进行SQL解释时，已经能确认NOW()返回值，但sysdate()不能，所以不能评估成本，于是优化器只能对sysdate()选择全表扫描，就now()就可以用索引了。

五、结论

• 对于索引关键字是datetime类型的字段，使用sysdate进行比较运算时，由于优化器进行SQL评估时是无法获知sysdate()值，而令优化器无法评估成本，所以只能全表扫描。
• 对于now()函数，由于优化器进行SQL执行计划评估时就已经能确定返回的值，所以可以评估出索引还是全表扫描更合适。