一、背景
在优化一批监控SQL时，发现一批SQL使用sysdate()作为统计数据的查询范围值,执行效率十分低下，查看执行计划发现不能使用到索引，而改为now()函数后则可以正常使用索引，以下是对该现象的分析
二、sysdate()与now()的区别
根据官方文档的示例及说明得知
- now()函数返回一个恒定时间，该时间指示语句开始执行的时间
- sysdate()则是返回语句执行的确切时间，通过下面的示例可以得知2者区别
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 |
+---------------------+----------+---------------------+
三、测试示例
以下通过示例模拟场景
-- 创建测试表及数据
mysql > create table t1(
id int primary key auto_increment,
create_time datetime default current_timestamp,
uname varchar(20),
key idx_create_time(create_time)
);
mysql > insert into t1(id) values(null),(null),(null);
mysql > insert into t1(id) values(null),(null),(null);
mysql > select * from t1;
+----+---------------------+-------+
| id | create_time | uname |
+----+---------------------+-------+
| 1 | 2020-03-23 17:12:44 | NULL |
| 2 | 2020-03-23 17:12:44 | NULL |
| 3 | 2020-03-23 17:12:44 | NULL |
| 4 | 2020-03-23 17:12:44 | NULL |
| 5 | 2020-03-23 17:12:44 | NULL |
| 6 | 2020-03-23 17:12:44 | NULL |
+----+---------------------+-------+
-- sysdate()执行计划
-- 可以看到possible_keys和key均为NULL，也就是不能使用到索引
mysql> explain select * from t1 where create_time<sysdate()G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 6
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
-- now()的执行计划
-- 可以看到possible_keys可以使用到idx_create_time索引
-- key为NULL是因为会查出所有数据所以优化器建议直接全表扫描
mysql > explain select * from t1 where create_time<now()G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: idx_create_time
key: NULL
key_len: NULL
ref: NULL
rows: 6
filtered: 100.00
Extra: Using where
- 进一步通过trace分析可以了解到对于now()函数，优化器在执行前可以将now()的值转换为一个常量，而通过sysdate()时则无法做到该优化
四、结论
通过实际验证执行计划和trace记录并结合官方文档的说明，我们可以做以下理解
- NOW()函数是语开始时就获取时间，优化器进行SQL解析时，已经能确认NOW()返回值并可以将其当做一个已确定的常量做优化。
sysdate()函数则是执行时动态获取时间，所以在优化器对SQL解析是还不能确定其返回值是多少，从而不能做SQL优化和评估，也就导致优化器只能选择对该条件做全表扫描。