问题分析
所遇情况:
数据库版本:5.6.38查询时使用时间类型,在status、closed、playback_state字段上都有索引几种查询语句explain (select count(*)
from session
where status = 2
and playback_state = 1
and closed > '/10/17'
and closed < '-10-18');
explain (select count(*)
from session
where status = 2
and playback_state = 1
and closed like '-10-17%');
explain (select count(*)
from session
where status = 2
and playback_state = 1
and closed > 1539705600
and closed < 1539792000);
explain (select count(*)
from session
where status = 2
and playback_state = 1
and closed > unix_timestamp('/10/17')
and closed < unix_timestamp('-10-18'));
查询情况:第一种情况使用closed的索引,查询速度很快. 其他使用playback_state,因为playback_state相同的数据有很多,因此除第一种情况外,其他情况都很糟糕。
为什么其他几种情况未使用closed进行索引:
mysql查询时,不管有多少个单个索引或联合索引,永远只使用一个索引。
具体使用哪个索引进行查询,由mysql进行选择,会选择一个它认为最合适的一个字段。在以上情况中,其选择了playback_state,而非closed。