关于datetime类型范围查询

1. 关于datetime类型我之前一直按照字符串去理解，也在很多时候它的确是这么表现的。例如使用 =、>、<、between '2019' and '2020'比较进行range查询。问题出在我知道字符串左前缀like 'A%'是使用索引的，而datetime会怪异一些…
2. 本文不讨论时间存储使用datetime、timestamp、int哪个更好的问题

一、创建一张表

创建一张表，有一个辅助字段state，观察字段为 date_start、date_end，分别使用varchar类型和datetime类型，然后分别创建联合索引。
MySQL版本: 5.7

## 
CREATE TABLE test(
	id int auto_increment,
	state varchar(10) not null default 'Planned',
	date_start varchar(20) not null default '2019-05-03 00:00:00',
	date_end datetime not null default '2019-05-03 00:00:00',
	primary key (id),
	key idx_state_dateStart(state, date_start),
	key idx_state_dateEnd(state, date_end)
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;

二、插入数据 （数据本身对explain是有影响的，尽量增加测试数据观察）

insert into test(state, date_start, date_end) values('Planned', '2019-05-03 00:00:00', '2019-05-03 00:00:00');
insert into test(state, date_start, date_end) values('Planned', '2019-04-03 00:00:00', '2019-04-03 00:00:00');

三、varchar-varchar 的联合索引使用

explain测试，观察key_len的使用情况，两个索引分别使用between '2019-05-01' and '2019-05-31 23:59:59' 和 like '2019-05%'作对比。

explain select id from test where state = 'Planned' and date_start between '2019-05-01' and '2019-05-31 23:59:59';

1	SIMPLE	test	NULL	range	idx_state_dateStart,idx_state_dateEnd	idx_state_dateStart	94	NULL	1	100.00	Using where; Using index
## key_len = 94 , 10*3+2 + 20*3+2, 使用了全部联合索引


explain select id from test where state = 'Planned' and date_start like '2019-05%';

1	SIMPLE	test	NULL	range	idx_state_dateStart,idx_state_dateEnd	idx_state_dateStart	94	NULL	1	100.00	Using where; Using index
## key_len = 94 , 10*3+2 + 20*3+2, 使用了全部联合索引 

四、varchar-datetime 的联合索引使用

explain select id from test where state = 'Planned' and date_end between '2019-05-01' and '2019-05-31 23:59:59';

1	SIMPLE	test	NULL	range	idx_state_dateStart,idx_state_dateEnd	idx_state_dateEnd	37	NULL	1	100.00	Using where; Using index
## key_len = 37, 10*3+2 + 5, 使用了全部联合索引


explain select id from test where state = 'Planned' and date_end like '2019-05%';
1	SIMPLE	test	NULL	ref	idx_state_dateStart,idx_state_dateEnd	idx_state_dateEnd	32	const	2	50.00	Using where; Using index
## key_len = 32, 10*3+2, 这里没有使用到datetime的5个字节的索引。

五、结果分析

1. varchar使用between and和 like都可以使用索引
2. datetime使用between and可以使用索引，like匹配不可以使用索引