Mycat常见SQL语句简析

    本文主要对Mycat中常见的SQL语句进行简单说明,会持续更新。

1. order by + limit n,m 语句
   SQL语句：
   

   #测试Mycat分页
   EXPLAIN SELECT
   	*
   FROM
   	oms_track_trace
   WHERE
   	PUSH_STATUS IN (1101, 1103)
   AND PUSH_NUM < 20
   AND NEXT_PUSH_TIME < NOW()
   ORDER BY
   	CREATE_TIME ASC
   LIMIT 10,20

   执行计划：
   

   dn1	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn10	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn2	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn3	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn4	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn5	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn6	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn7	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn8	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30
   dn9	SELECT * FROM oms_track_trace WHERE PUSH_STATUS IN (1101, 1103)  AND PUSH_NUM < 20  AND NEXT_PUSH_TIME < NOW() ORDER BY CREATE_TIME ASC LIMIT 0, 30

   分析：Mycat支持常规limit查询，其本身对原始sql进行解析，limit n,m会在每个路由的分片上自动解析成limit 0,n+m。然后将每个分片的数据进行汇总，然后根据order by顺序，得出业务需要的limit n,m数据。