mysql order by + limit 效率低下问题

原SQL语句

SELECT tf.* FROM tran_flow tf WHERE TTF_GRPID = '0073' AND TTF_OPRNO = 'cs5' And TTF_BSNCODE = '0101' AND LEFT('20220120', 8) <= LEFT(TTF_SUBMITTIME, 8) AND LEFT('20230120', 8) >= LEFT(TTF_SUBMITTIME, 8) AND (case when '' = '' then 1 = 1 else TTF_STT in ('1') end) ORDER BY TTF_SUBMITTIME DESC LIMIT 0, 10;

执行时间：5s

不带LIMIT条件

SELECT tf.* tran_flow tf WHERE TTF_GRPID = '0073' AND TTF_OPRNO = 'cs5' And TTF_BSNCODE = '0101' AND LEFT('20220120', 8) <= LEFT(TTF_SUBMITTIME, 8) AND LEFT('20230120', 8) >= LEFT(TTF_SUBMITTIME, 8) AND (case when '' = '' then 1 = 1 else TTF_STT in ('1') end) ORDER BY TTF_SUBMITTIME DESC

执行时间：20ms

猜测效率问题出在LIMIT条件上，通过explain语句分析SQL

explain SELECT tf.* FROM tran_flow WHERE TTF_GRPID = '0073' AND TTF_OPRNO = 'cs5' And TTF_BSNCODE = '0101' AND LEFT('20220120', 8) <= LEFT(TTF_SUBMITTIME, 8) AND LEFT('20230120', 8) >= LEFT(TTF_SUBMITTIME, 8) AND (case when '' = '' then 1 = 1 else TTF_STT in ('1') end) ORDER BY TTF_SUBMITTIME DESC LIMIT 0, 10;

 

发现使用了bs_tran_flow_TTF_SUBMITTIME索引

去掉LIMIT 0, 10，通过explain分析SQL

explain SELECT tf.* FROM tran_flow WHERE TTF_GRPID = '0073' AND TTF_OPRNO = 'cs5' And TTF_BSNCODE = '0101' AND LEFT('20220120', 8) <= LEFT(TTF_SUBMITTIME, 8) AND LEFT('20230120', 8) >= LEFT(TTF_SUBMITTIME, 8) AND (case when '' = '' then 1 = 1 else TTF_STT in ('1') end) ORDER BY TTF_SUBMITTIME DESC

发现使用了idx_TTF_GRPID索引 

加与不加LIMIT条件 决定了走不同的索引， 且不加LIMIT条件的情况下执行效率明显是非常高的。 所以猜测bs_tran_flow_TTF_SUBMITTIME索引效率低。

通过查询相关资料 改造SQL 强制走idx_TTF_GRPID索引， 效率明显提高

force index (idx_TTF_GRPID)

SELECT tf.* FROM tran_flow force index (idx_TTF_GRPID) WHERE TTF_GRPID = '0073' AND TTF_OPRNO = 'cs5' And TTF_BSNCODE = '0101' AND LEFT('20220120', 8) <= LEFT(TTF_SUBMITTIME, 8) AND LEFT('20230120', 8) >= LEFT(TTF_SUBMITTIME, 8) AND (case when '' = '' then 1 = 1 else TTF_STT in ('1') end) ORDER BY TTF_SUBMITTIME DESC LIMIT 0, 10;

执行时间：20ms