explain SELECT DISTINCT `id`,`fid`,`aid`,`daction`,`dmoney`,`lmoney` FROM pft_member_journal
WHERE `rectime` BETWEEN '2017-03-13 00:00:00' AND '2017-03-13 23:59:59' AND `fid` = 4 AND `aid` = 50618 AND `ptype` = 2发现 extra里使用了using intersect。
通过设置 SET optimizer_switch = 'index_merge_intersection=off'
再次执行exolain sql语句 变成了using index condition
执行sql语句 速度变得飞快
或者强制不使用索引
SELECT DISTINCT `id`,`fid`,`aid`,`daction`,`dmoney`,`lmoney` FROM pft_member_journal IGNORE index(aid,fid)
WHERE `rectime` BETWEEN '2017-03-13 00:00:00' AND '2017-03-13 23:59:59' AND `fid` = 4 AND `aid` = 50618 AND `ptype` = 2