- 关于
datetime
类型我之前一直按照字符串去理解,也在很多时候它的确是这么表现的。例如使用=
、>
、<
、between '2019' and '2020'
比较进行range
查询。问题出在我知道字符串左前缀like 'A%'
是使用索引的,而datetime
会怪异一些…- 本文不讨论时间存储使用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个字节的索引。
五、结果分析
varchar
使用between and
和like
都可以使用索引datetime
使用between and
可以使用索引,like
匹配不可以使用索引