MySQL使用sysdate()导致无法使用索引以及NLJ驱动表结果集增大

 

MySQL使用sysdate()导致无法使用索引以及NLJ驱动表结果集增大

 

MySQL版本：8.0.36

最近遇到一条MySQL的慢语句优化，发现是sysdate()导致的问题。

现在大致模拟一下环境。

 

创建表以及对应索引，如下：

create table zkm(id int,dtime datetime,c3 int);
create index idx_z_dtime on zkm(dtime);
create index idx_z_id on zkm(id);

 

生成随机数据，如下：

复制代码
--生成随机数据的存储过程
DELIMITER //

CREATE PROCEDURE GenerateData(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE start_time datetime DEFAULT '2000-01-01 00:00:00';
    DECLARE end_time   datetime DEFAULT '2024-11-28 23:59:59';

    WHILE i <= num DO
        INSERT INTO zkm VALUES (i, FROM_UNIXTIME(FLOOR(RAND() * (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time) + 1)) + UNIX_TIMESTAMP(start_time)), round(100*rand(),0));
        SET i = i + 1;
    END WHILE;
END;
//

DELIMITER ;

--调用生成 10000 条数据
CALL GenerateData(10000);

--删除存储过程
--DROP PROCEDURE IF EXISTS GenerateData;
复制代码

 

 

现在对比下边前两条SQL，一旦用上sysdate()会立刻使用全表扫描。

嘿！以前都是遇到对表字段做函数处理的时候索引失效的，现在只是个普通的数值就导致了无法使用索引。

复制代码
(root@localhost 10:19:33) [zkm](1355501)> pager grep -vE "Code 1003"
PAGER set to 'grep -vE "Code 1003"'
(root@localhost 10:19:34) [zkm](1355501)> explain select * from zkm where dtime = '2006-01-05 16:29:01';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | zkm   | NULL       | ref  | idx_z_dtime   | idx_z_dtime | 6       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

(root@localhost 10:19:37) [zkm](1355501)> explain select * from zkm where dtime = date_sub(sysdate(), interval 1 day);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | zkm   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10001 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

--强制使用索引也不行
--explain select /*+ INDEX(zkm idx_z_dtime) */ * from zkm where dtime = date_sub(sysdate(), interval 1 day);
(root@localhost 10:19:48) [zkm](1355501)> explain select * from zkm force index(idx_z_dtime) where dtime = date_sub(sysdate(), interval 1 day);
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | zkm   | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10001 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.18 sec)

--普通的值呦
(root@localhost 10:45:21) [zkm](1355701)> select date_sub(sysdate(), interval 1 day) dateValue;
+---------------------+
| dateValue           |
+---------------------+
| 2024-11-28 10:45:31 |
+---------------------+
1 row in set (0.00 sec)
复制代码

 

而另外一个场景则是导致我实际生产SQL慢的主要原因，那就是NLJ无法有效过滤驱动表数据量导致被驱动表执行次数过多。

还是以上边表为例子，问题如下：

复制代码
 1 (root@localhost 10:39:26) [zkm](1355701)> explain format=tree select count(*) from zkm t1,zkm t2 where t1.id = t2.id and t2.dtime = date_sub(sysdate(), interval 1 day) and t2.c3=4 \G
 2 *************************** 1. row ***************************
 3 EXPLAIN: -> Aggregate: count(0)  (cost=1030 rows=1)
 4     -> Nested loop inner join  (cost=1020 rows=100)
 5         -> Filter: ((t2.c3 = 4) and (t2.id is not null))  (cost=917 rows=100)
 6             -> Table scan on t2  (cost=917 rows=10001)
 7         -> Filter: (t2.dtime = (sysdate() - interval 1 day))  (cost=0.933 rows=1)
 8             -> Covering index lookup on t1 using idx_z_id (id=t2.id)  (cost=0.933 rows=1)
 9 
10 1 row in set (0.06 sec)
复制代码

 

在以上的SQL语句中，t2表有两个过滤条件，分别是：

t2.c3=4

t2.dtime = date_sub(sysdate(), interval 1 day)

但是从执行计划看，这两个过滤条件竟然分到了第5行和第7行的Filter执行路径中。

嘶！！

理想情况是第5行的Filter使用过滤条件t2.c3=4 and t2.dtime = date_sub(sysdate(), interval 1 day)后，一行数据都没有，这样被驱动表一次都可以不需要执行。

这使我一度无法理解，放在Oracle里边也是相当炸裂的。

尝试使用with as，子查询改写SQL，都不行。但如果使用CTAS重新生成一张表替换掉t2进行测试的话就没问题。

也猜测了是否是date_sub(sysdate(), interval 1 day)整体类型的问题，使用cast转换了还是不行。

 

不知道尝试多久想放弃的时候，鬼使神差使用now()替换sysdate()之后，您猜怎么着，可以了。。

能走索引了，NLJ中也不会分开到两个Filter了。

仔细看了看sysdate()和now()的区别，才执行为啥。（点击链接跳转官网说明）

复制代码
(root@localhost 11:05:44) [zkm](1355701)> select now(),sysdate(),sleep(2),now(),sysdate();
+---------------------+---------------------+----------+---------------------+---------------------+
| now()               | sysdate()           | sleep(2) | now()               | sysdate()           |
+---------------------+---------------------+----------+---------------------+---------------------+
| 2024-11-29 11:05:59 | 2024-11-29 11:05:59 |        0 | 2024-11-29 11:05:59 | 2024-11-29 11:06:01 |
+---------------------+---------------------+----------+---------------------+---------------------+
1 row in set (2.02 sec)

--now()用上索引了
(root@localhost 14:18:36) [zkm](1356202)> explain select * from zkm where dtime = date_sub(now(), interval 1 day);
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | zkm   | NULL       | ref  | idx_z_dtime   | idx_z_dtime | 6       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--多表关联中，NLJ执行路径下，now()也能够减少驱动表结果集
(root@localhost 14:18:37) [zkm](1356202)> explain analyze select count(*) from zkm t1,zkm t2 where t1.id = t2.id and t2.dtime = date_sub(now(), interval 1 day) and t2.c3=4 \G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=0.373 rows=1) (actual time=0.0188..0.0189 rows=1 loops=1)
    -> Nested loop inner join  (cost=0.363 rows=0.1) (actual time=0.0173..0.0173 rows=0 loops=1)
        -> Filter: ((t2.c3 = 4) and (t2.id is not null))  (cost=0.26 rows=0.1) (actual time=0.0166..0.0166 rows=0 loops=1)
            -> Index lookup on t2 using idx_z_dtime (dtime=(now() - interval 1 day))  (cost=0.26 rows=1) (actual time=0.0162..0.0162 rows=0 loops=1)
        -> Covering index lookup on t1 using idx_z_id (id=t2.id)  (cost=1.93 rows=1) (never executed)

--多表关联中，now()不走索引的情况
--explain analyze select count(*) from zkm t1,zkm t2 ignore index (idx_z_dtime) where t1.id = t2.id and t2.dtime = date_sub(now(), interval 1 day) and t2.c3=4\G
(root@localhost 14:20:55) [zkm](1356202)> explain analyze select /*+ no_index(t2 idx_z_dtime) */ count(*) from zkm t1,zkm t2 where t1.id = t2.id and t2.dtime = date_sub(now(), interval 1 day) and t2.c3=4\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)  (cost=1008 rows=1) (actual time=12.1..12.1 rows=1 loops=1)
    -> Nested loop inner join  (cost=1008 rows=1) (actual time=12.1..12.1 rows=0 loops=1)
        -> Filter: ((t2.c3 = 4) and (t2.dtime = <cache>((now() - interval 1 day))) and (t2.id is not null))  (cost=1007 rows=1) (actual time=12.1..12.1 rows=0 loops=1)
            -> Table scan on t2  (cost=1007 rows=10001) (actual time=0.0295..11 rows=10001 loops=1)
        -> Covering index lookup on t1 using idx_z_id (id=t2.id)  (cost=1.03 rows=1) (never executed)
复制代码

 

now()返回开始执行时候的时间，是一个常量。

sysdate()则返回那个时候sysdate()被调用时候的时间，因此在同一语句中也可以返回不同的值。

简而言之，在SQL语句开始执行后，sysdate()可以理解为一个不确定的值，是一个变量，因此无法使用索引(包括强制)，这在官网也提到了。

同样也无法在上边的NLJ执行计划的第5行的Filter中和t2.c3=4一块成为过滤条件，转而在第7行Filter进行了过滤，虽然我觉得这取决于优化器。

也可以通过设置启动时候--sysdate-is-now参数控制让sysdate()等同于now()，但不建议这么做。

重启生效。

vi /etc/my.cnf
sysdate-is-now = on

 

另外，now()是受到 set timestamp 影响的，而sysdate()则不会。

复制代码
(root@localhost 15:44:57) [mysql](1356557)> SET session TIMESTAMP=UNIX_TIMESTAMP('2024-01-01');
Query OK, 0 rows affected (0.00 sec)

(root@localhost 15:44:58) [mysql](1356557)> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 2024-01-01 00:00:00 | 2024-11-29 15:45:03 |
+---------------------+---------------------+
1 row in set (0.00 sec)
复制代码

 

后续经过沟通确认，实际业务上使用now()更加准确，因此将sysdate()换成now()来优化SQL语句。

至此。

posted @   PiscesCanon  阅读(64)  评论(0)  编辑  收藏  举报
相关博文：
· Oracle SQL优化过程一则以及group by少见用法报错点
· 对比Oracle和MySQL索引对于like的支持，MySQL一言难尽啊...
· MySQL优化
· 老司机总结的 18 条 SQL 优化方案（非常实用）
· MySql索引优化实战
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛