Mysql 索引问题-日期索引使用

扫地僧的故事 2020-08-02

8120

前天晚上，睡觉前突然想到白天同事优化的一个sql。(头发渐渐消失。。

)SQL语句简化下，就是：select * from t_test where dt like concat(curdate(),"%");表t_test的字段dt是timestamp类型,并且dt上有索引。

执行计划如下：

那其实很容易就能想到，like只能用于字符串比较，那mysql肯定要先把dt转换成字符串再去与'2020-08-01%'进行匹配，那结果肯定就是走不上索引。

所以，最简单的方法就是改写下sql：select * from t_test where dt >= curdate() and dt < date_add(curdate(),interval 1 day);

上面的案例中，查询字段发生了隐式转换，导致无法使用索引。

题外话，不能使用索引的还有几种常见情况是，对查询条件中的索引列做计算，对索引列使用了函数等。

那么在mysql中，如果日期和数字比较，和字符串比较，会发生隐式转换吗？能使用索引吗？（问题少年又来了。。）

mysql中datetime和timestamp类型的定义，如下：

Sample 1:日期与数字比较

结果意不意外，惊不惊喜？

mysql在发现dt字段为timestamp类型时，会先把查询条件值中的数字转成timestamp类型，然后再进行比较，这样就可以使用索引了。

但是如果遇到20210932这种，根本不存在的日期，那么数字转时间肯定转不成功，只能把查询条件dt的timestamp型转成数字型再进行比较，那肯定也就不能使用索引了！（之前还有一个同事把逗号不小心写成了点，结果一个sql跑了一个小时也没跑完，所以一定要严谨哟！！）

Sample 2：日期与字符串比较

从上图中能看到，mysql也是同样的把字符串转成timestamp类型，然后再去做比较，也是可以使用索引的。如果转换失败，那就报错。

总结一下：

like只适用于字符串的比较，不要想当然的使用！

mysql还是很灵活的，当它发现字段为timestamp类型时，会先把条件值转换成timestamp类型（TIMESTAMP'2021-09-01 00:00:00'），然后再进行比较。

今天发现了一个很不错的查看执行计划的方法，explain format=tree select XXXXX；

推荐给大家～

刘老师强烈要求附上此图，那就满足他小小的愿望吧！

大家周末愉快～

文章转载自扫地僧的故事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。