概述
最近发现一个有趣的SQL优化场景，分享一下
测试数据
表结构
CREATE TABLE `dateindex_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`t1` varchar(100) DEFAULT NULL,
`t2` text,
`t2_sub` text,
`t3` varchar(50) DEFAULT NULL,
`url` varchar(100) DEFAULT NULL,
`t4` varchar(255) DEFAULT NULL,
`t5` longblob,
`website_name` varchar(50) DEFAULT NULL,
`t6` varchar(50) DEFAULT NULL,
`t7` varchar(50) DEFAULT NULL,
`publish_time` datetime DEFAULT NULL,
`webpage_uuid` varchar(50) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `webpage_uuid` (`webpage_uuid`) USING BTREE,
KEY `website_name` (`website_name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8317875 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC利用阿里云RDS中的DMS生成随机数据10万行如下
mysql>SELECT count(*) FROM dateindex_test;
+--------------------+
| count(*) |
+--------------------+
| 100000 |
+--------------------+
返回行数：[1]，耗时：18 ms.索引问题
问题SQL：
SELECT
a.t1 AS t1,
a.t2 AS t2,
a.url AS url,
a.t3 AS t3,
a.publish_time AS publish_time
FROM
dateindex_test a
WHERE
date( a.publish_time ) >= date_sub(
curdate(),
INTERVAL 1 DAY )没有索引之前，当然是全表扫。现在加上一个索引
ALTER TABLE dateindex_test ADD INDEX idx_publish_time(publish_time)我的期望当然是直接走索引过滤是最好的，但是测试下却发现不行，虽然有索引了，但是没有走
mysql>explain SELECT
a.t1 AS t1,
a.t2 AS t2,
a.url AS url,
a.t3 AS t3,
a.publish_time AS publish_time
FROM
dateindex_test a
WHERE
date( a.publish_time ) >= date_sub(
curdate(),
INTERVAL 1 DAY )
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1 | SIMPLE | a | | ALL | | | | | 99572 | 100 | Using where |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+我第一时间怀疑的就是date() 函数导致的，将具体时间转换为日期，然后再试图使用具体时间的索引。看上去就感觉是有问题的。
所以就马上去掉 date函数再做测试，发现就使用了索引
mysql>explain SELECT
a.t1 AS t1,
a.t2 AS t2,
a.url AS url,
a.t3 AS t3,
a.publish_time AS publish_time
FROM
dateindex_test a
WHERE
a.publish_time >= date_sub(
curdate(),
INTERVAL 1 DAY )
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+---------------+----------------+--------------------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+---------------+----------------+--------------------+-----------------------+
| 1 | SIMPLE | a | | range | idx_publish_time | idx_publish_time | 6 | | 17 | 100 | Using index condition |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+------------------+-------------------+---------------+----------------+--------------------+-----------------------+到了这里，我所关心的就是：去掉date()函数以后，SQL语句的逻辑和原来的还是一样的吗。
我就再来测试一下：原SQL取出的是，昨天一整天+今天到目前为止的数据
我如果是使用原来的包含具体时间的数据来比较，能有一样的过滤效果吗?
就利用刚建的表小测试一下
第一次测试，发现数据并不一样
mysql>select count(*) from dateindex_test where date(publish_time) > '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 64 |
+--------------------+
返回行数：[1]，耗时：34 ms.
mysql>select count(*) from dateindex_test where publish_time > '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 79 |
+--------------------+再比较下原SQL，才发现上面不一样的原因所在，当 publish_time 为 2019-08-15 12:31:12，经过date（）函数处理后的时间为：2019-08-15。此时与上述SQL的代入发现，第一条语句是不符合的，第二条，却是符合的（这里我猜测是因为在比较时发生隐性类型转换，将2019-08-15 转换为2019-08-19 00:00:00）
此时只要将原SQL中的> 改为'>='就能符合原SQL逻辑
如下：
mysql>select count(*) from dateindex_test where publish_time >= '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 79 |
+--------------------+
mysql>select count(*) from dateindex_test where date(publish_time) >= '2019-08-15'
+--------------------+
| count(*) |
+--------------------+
| 79 |
+--------------------+
mysql>select count(*) from dateindex_test where date(publish_time) >= '2019-08-10'
+--------------------+
| count(*) |
+--------------------+
| 154 |
+--------------------+
mysql>select count(*) from dateindex_test where publish_time >= '2019-08-10'
+--------------------+
| count(*) |
+--------------------+
| 154 |
+--------------------+date与datetime
由上引深再学习一下datetime与date字段类型的区别
1、显示格式的区别
Date显示格式：YYYY-MM-DD；DateTime显示格式：YYYY-MM-DD HH:mm:ss。
2、显示范围的区别
Date显示范围是1601-01-01 到 9999-01-01；DateTime显示范围是1601-01-01 00:00:00 到 9999-12-31 23:59:59。
3、应用场景的区别
当业务需求中只需要精确到天时，可以用Date这个时间格式，当业务需求中需要精确到秒时，可以用DateTime这个时间格式。
4、后台取值的区别
Date后台取值：@JSONField(format=”yyyy-MM-dd”)；DateTime后台取值：@JSONField(format=”yyyy-MM-dd HH:mm:ss:SSS”)（这里只会精确到秒）。
字段类型转换导致的索引失效
其实这里最让我想要深入研究一下的主因就是为什么date函数会使该字段的索引无效
- 函数使索引失效
在上面的例子中，我们发现这就是引起索引失效的主要原因了，字段类型发生了转换 - 字符串不加单引号（隐式类型转换；生产重罪）导致索引失效
（下面测试，在表的t1列添加了索引）
mysql>explain select * from dateindex_test where t1 = 8
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1 | SIMPLE | dateindex_test | | ALL | idx_title | | | | 99572 | 10 | Using where |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
mysql>explain select * from dateindex_test where t1 = '8'
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+
| 1 | SIMPLE | dateindex_test | | ref | idx_title | idx_title | 303 | const | 123 | 100 | |
+--------------+-----------------------+-----------------+----------------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+--------------------+-----------------+总结
不要在索引列上做任何操作（计算、函数、（自动或手动）类型转换），会导致索引失效进而转向全表扫描！
