mysql union all的一种优化方法

业务中遇到一个语句：

类似：

(SELECT * FROM xtable WHERE xx=100500137 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=104546692 ORDER BY xid asc imit 10)

union all (SELECT * FROM xtable WHERE xx=109000327 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=110084551 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=110186680 ORDER BY xid asc limit 10)

union all (SELECT * FROM xtable WHERE xx=110748205 ORDER BY xid asc limit 10);

业务上是根据xx分组，每个组中按前10个xid排序，组内必须有序，组间不必有序，xx可能有几十个；

首先想到是group by  但是group by 不能limit每个分组数目数目

后面想到一个方式对每个xid 取一个序号，然后控制序号的偏移量；

select * from (

select  t1.*,(select count(*)+1 from tmp t where t.xx=t1.xx and t.xid<t1.xid) as group_id from tmp t1 where t1.xx in(100500137,104546692,109000327,110084551,110186680,110748205)

) tbl where tbl.group_id<=10;

效率上如果在xx比较小的情况下union all比较有优势，但是如果xx在相对比较多得情况下后面一种方式比较有优势，xx,xid上有联合索引！