2017-12-05 DBA日记,mysql的datetime字段索引不能识别sysdate
来源:互联网 发布:pro软件下载 编辑:程序博客网 时间:2024/06/05 01:08
一、案例描述
今日在进行MYSQL日常检查时,发现有一条SQL语句,在字段类型为datetime并创建索引的情况下,进行between (sysdate()+interval(-1) hour) and sysdate()时并不会使用索引,但是用now()代替sysdate就可以使用索引,这是为什么呢?
二、问题
为什么datetime字段(已有且只有一个关键字的索引)与sysdate()进行比较运算时不使用索引,而用now()则会?
三、思路
不使用索引的情况一般是类型不对,那么sysdate()返回的类型不是datetime? 判断sysdate()返回的类型
四、收集数据与分析
- 确认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类型
- 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执行计划评估时就已经能确定返回的值,所以可以评估出索引还是全表扫描更合适。
阅读全文
0 0
- 2017-12-05 DBA日记,mysql的datetime字段索引不能识别sysdate
- [DBA日记]字段为null值时,索引及统计的研究及理解
- mysql的date,datetime字段
- 2017-08-05 DBA日记,mysql读书笔记第四天
- MySql截取DateTime字段的日期值
- 2017-05-11 DBA日记,DTCC DAY-1的收获
- 数据库的索引,mysql中的索引|||如何在mysql中对text字段加索引?|||mysql数据库中的字段在什么情况下加索引?|||高手请进:text字段不能做索引,有无其他方法?|||该建那个字段
- MySQL之索引:索引字段的选取
- 2017-08-05 DBA日记,MYSQL半同步复制实验之三
- 2017-07-28 DBA日记,MYSQL读书笔记之一
- 2017-07-30 DBA日记,MYSQL读书笔记第二天
- 导致coldfusion出错的MySQL DATETIME字段问题。
- 再:导致coldfusion出错的MySQL DATETIME字段问题。
- MySql用DATE_FORMAT截取DateTime字段的日期
- MySql用DATE_FORMAT截取DateTime字段的日期值
- 编码中关于mysql DateTime类型字段的注意事项
- mysql新建表datetime类型字段不能设置当前默认时间解决方法
- 在select sqlite数据中的datetime字段的时候出现 该字符串未被识别为有效的 DateTime
- 欢迎使用CSDN-markdown编辑器
- 【学习笔记】Python中的正则表达式re
- Okhttp去除请求头user-agent
- Mysql之我见四(索引优化)
- 布局xml转bitmap
- 2017-12-05 DBA日记,mysql的datetime字段索引不能识别sysdate
- CentOS 7以RPM方式安装MySQL5.7
- Delphi中读写注册表
- Ticwatch被福布斯评为“黑五”最值得买的可穿戴产品
- 关于51单片机的仿真栈(模拟栈/可重入栈)
- 关于vue.js使用.scss模块安装问题
- scala学习-11-package object
- mysql/mariadb centos7 修改root用户密码及配置参数
- 常用linux命令符1