[SQL]When TxEvent has a large number of records (200 million+), TXLE has a very slow query(10min+)

[asiroliu]

Describe The Bug

When TxEvent has a large number of records (200 million+), TXLE has a very slow query(10min+)

TXLE Version

https://github.com/actiontech/txle/commit/3a325a6b395e0c87976c33b945b0a75da3275ab5

To Reproduce

Steps to reproduce the behavior:

1. the MySQL which used by TXLE open the slow log
2. execute a long-term stress test through jmeter, making TxEvent record more than 200 million
3. execute a global timeout transaction
4. view MySQL slow log as follows:

# Time: 2019-06-17T16:36:30.252103+08:00
# User@Host: root[root] @  [172.20.2.1]  Id:  1550
# Query_time: 1594.290094  Lock_time: 0.000061 Rows_sent: 2  Rows_examined: 168732049
SET timestamp=1560760590;
SELECT t0.SURROGATEID, t0.CATEGORY, t0.COMPENSATIONMETHOD, t0.CREATIONTIME, t0.EXPIRYTIME, t0.GLOBALTXID, t0.INSTANCEID, t0.LOCALTXID, t0.PARENTTXID, t0.PAYLOADS, t0.RETRIES, t0.RETRYMETHOD, t0.SERVICENAME, t0.TYPE FROM TxEvent t0 WHERE ((((t0.GLOBALTXID = '3d6f5e2a-453e-46bc-b72c-91edc4374686') AND (t0.TYPE = 'TxStartedEvent')) AND EXISTS (SELECT 1 FROM TxEvent t1 WHERE ((t1.LOCALTXID = t0.LOCALTXID) AND (t1.TYPE = 'TxEndedEvent'))) ) AND NOT EXISTS (SELECT 1 FROM TxEvent t2 WHERE ((t2.LOCALTXID = t0.LOCALTXID) AND (t2.TYPE = 'TxCompensatedEvent'))) );
# Time: 2019-06-17T16:41:34.120188+08:00
# User@Host: root[root] @  [172.20.2.1]  Id:  1545
# Query_time: 720.991837  Lock_time: 0.000087 Rows_sent: 0  Rows_examined: 253098073
SET timestamp=1560760894;
SELECT * FROM TxEvent t WHERE t.type IN ('TxStartedEvent', 'SagaStartedEvent')   AND t.expiryTime < '2019-06-17 08:29:33.128' AND NOT EXISTS(   SELECT t1.globalTxId FROM TxEvent t1   WHERE t1.globalTxId = t.globalTxId     AND t1.localTxId = t.localTxId     AND t1.type != t.type)  AND NOT EXISTS (SELECT 1 FROM TxEvent t2 WHERE t2.globalTxId = t.globalTxId AND t2.type = 'TxAbortedEvent') /**scanner_sql**/;

Expected Result

the query time should be completed in milliseconds

Real Result

the query time spend hundreds of seconds