limit豫union一起使用时的优化
cp_order_exit数据行数：142951
cp_order_exit_led数据行数：20876
查询；这条 查询将会把 cp_order_exit中的142951条 记录和cp_order_exit_led表中的 条记录存放在一个临时表中，然后再从临时表中取出前20条
(SELECT id,order_num FROM cp_order_exit ORDER BY id DESC)
UNION
(SELECT id,order_num FROM cp_order_exit_led ORDER BY id DESC)
LIMIT 20;
+-------+-------------------+
| id | order_num |
+-------+-------------------+
| 3374 | 17081722253475091 |
| 599 | 17081722445667732 |
| 2919 | 17081722470859738 |
| 763 | 17081722482968034 |
| 597 | 17081722485192686 |
| 485 | 17081722515686701 |
| 310 | 17081723044296838 |
| 14030 | 17081723044958251 |
| 1015 | 17081723050166723 |
| 22979 | 17081723052433009 |
| 938 | 17081723121062016 |
| 662 | 17081723164257266 |
| 6071 | 17081723170688044 |
| 931 | 17081723195065006 |
| 21687 | 17081723195869506 |
| 4206 | 17081723200958620 |
| 2361 | 17081723215579738 |
| 794 | 17081723231721394 |
| 2955 | 17081723255650668 |
| 812 | 17081723301563869 |
+-------+-------------------+
20 rows in set (0.41 sec)
查询；可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据
(SELECT id,order_num FROM cp_order_exit ORDER BY id DESC LIMIT 20)
UNION
(SELECT id,order_num FROM cp_order_exit_led ORDER BY id DESC LIMIT 20)
LIMIT 20;
+--------+-------------------+
| id | order_num |
+--------+-------------------+
| 143260 | 17110516090597246 |
| 143259 | 17110513570152100 |
| 143258 | 17102914120810396 |
| 143257 | 17110507585467679 |
| 143256 | 17110513564821578 |
| 143255 | 17110515320939832 |
| 143254 | 17110113430167061 |
| 143253 | 17110514191947002 |
| 143252 | 17110515105274110 |
| 143251 | 17092414521129198 |
| 143250 | 17110514381843286 |
| 143249 | 17110514043443843 |
| 143248 | 17110514095924493 |
| 143247 | 17110419214534187 |
| 143246 | 17110516574761784 |
| 143245 | 17110514595352478 |
| 143244 | 17100817045255527 |
| 143243 | 17110514085146524 |
| 143242 | 17110515463240896 |
| 143241 | 17110514211653455 |
+--------+-------------------+
20 rows in set (0.00 sec)