mysql中explain看性能

时间：2022-03-14 04:50

select distinct col_name from table where a=X and b=Y and date(time)=‘xx-xx-xx‘;
执行时间 27.9772 秒

explain select distinct col_name from table where a=X and b=Y and date(time)=‘xx-xx-xx‘;

select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	access	ALL	NULL	NULL	NULL	NULL	38714243	Using where; Using temporary

select distinct col_name from table where a=X and b=Y and time<‘xx-xx-xx‘;
执行时间 0.0038 秒

explain select distinct col_name from table where a=X and b=Y and time<‘xx-xx-xx‘;

select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	access	range	idx_access_dt	idx_access_dt	4	NULL	1	Using where; Using temporary

select distinct col_name from table where a=X and b=Y and time=‘xx-xx-xx xx:xx:xx‘;
执行时间 0.0031 秒

explain select distinct col_name from table where a=X and b=Y and time=‘xx-xx-xx xx:xx:xx‘;

select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	access	ref	idx_access_dt	idx_access_dt	4	const	82	Using where; Using temporary

参考：

电脑软件