SqlSuger千万级数据量分表分页获取列表优化

目前的SqlSuger分页自动生成sql语句：耗时13.7s

SELECT * FROM ( ( SELECT `id`,`packing_cmd_id`,`sku_id`,`sku_code`,`device_mac`,`device_id`,`product_id`,`product_model`,`device_sn`,`device_sn2`,`factory_id`,`factory_user_id`,`create_time`,`change_user_id`,`change_time` FROM `t_print_device_xxx_20231001` WHERE ( `factory_id` = '59' ) ) UNION ALL

( SELECT `id`,`packing_cmd_id`,`sku_id`,`sku_code`,`device_mac`,`device_id`,`product_id`,`product_model`,`device_sn`,`device_sn2`,`factory_id`,`factory_user_id`,`create_time`,`change_user_id`,`change_time` FROM `t_print_device_xxx_20230101` WHERE ( `factory_id` = '59' ) ) UNION ALL

( SELECT `id`,`packing_cmd_id`,`sku_id`,`sku_code`,`device_mac`,`device_id`,`product_id`,`product_model`,`device_sn`,`device_sn2`,`factory_id`,`factory_user_id`,`create_time`,`change_user_id`,`change_time` FROM `t_print_device_xxx_20221001` WHERE ( `factory_id` = '59' ) ) UNION ALL

( SELECT `id`,`packing_cmd_id`,`sku_id`,`sku_code`,`device_mac`,`device_id`,`product_id`,`product_model`,`device_sn`,`device_sn2`,`factory_id`,`factory_user_id`,`create_time`,`change_user_id`,`change_time` FROM `t_print_device_xxx_20220701` WHERE ( `factory_id` = '59' ) ) ) unionTable ORDER BY `id` DESC LIMIT 0,10;

sql逻辑：在每个表获取符合条件的数据，进行UNION ALL ，然后再排序分页，导致几十-百万数据慢查询。

优化逻辑：

1、获取分表名称列表；

2、统计每个分表符合条件的数据条数；

3、根据每个表的数据条数及当前分页数和每页数量，确定当前查找的数据在第几个表（哪几张表，注意第一张表需要计算偏移量值）；

4、按条件查询所在表的数据id主键回表查询。

5、对查询结果进行排序，过滤掉多获取的几条数据。

统计每个表数据

select * from (

(select count(id) as record_count,'t_print_device_shipment_20231001'as table_name from t_print_device_xxx_20231001 where factory_id='59' )

UNION ALL(select count(id) as record_count,'t_print_device_shipment_20230101'as table_name from t_print_device_xxx_20230101 where factory_id='59' )

UNION ALL(select count(id) as record_count,'t_print_device_shipment_20221001'as table_name from t_print_device_xxx_20221001 where factory_id='59')

UNION ALL(select count(id) as record_count,'t_print_device_shipment_20220701'as table_name from t_print_device_xxx_20220701 where factory_id='59' )

)unionTable

查询数据返回

select * from (

(select `id`, `brand_id`, `brand_name`, `sku_code`, `product_id`, `product_model`, `box_count`, `pcs_count`, `shipment_type`, `shipment_ver`, `factory_id`, `factory_user_id`, `bucket_name`, `file_name`, `remark`, `status`, `fail_reason`, `create_time`, `change_time` from t_print_device_xxx_20221001 where id in(select id from(select id from t_print_device_xxx_20221001 where factory_id='59' order by id desc limit 10 offset 4)as table1)

)UNION ALL

(select `id`, `brand_id`, `brand_name`, `sku_code`, `product_id`, `product_model`, `box_count`, `pcs_count`, `shipment_type`, `shipment_ver`, `factory_id`, `factory_user_id`, `bucket_name`, `file_name`, `remark`, `status`, `fail_reason`, `create_time`, `change_time` from t_print_device_xxx_20220701 where id in(select id from(select id from t_print_device_shipment_20220701 where factory_id='59' order by id desc limit 10)as table2)

)

)unionTable order by id desc limit 10

limit 10不能直接用在子查询，需要嵌套一层；

第一张表需要计算出获取分页数据的首行起始点。