记mysql的union all的简单优化

网站后台最近需要把两张表数据进行统一分页查询,一张表150多万,一张表50多万

首先就想到了union all ,但感觉会很慢, 果不其然, 第一页就花了十几秒, 开始的SQL是这样的:

原方案：

-- 总数
select count(*) from
 (
select id,order_no,state,create_time from order1
 union all 
select id,order_no,state,create_time from order2
 ) ua where state=1 
 
-- 分页
select * from
 (
select id,order_no,state,create_time from order1
 union all 
select id,order_no,state,create_time from order2
 ) ua where state=1 
order by create_time desc 
limit 0,10;

细思极恐之后, 决定还是要优化一下, 效果不错, 最重要的两个优化如下:

1,  如果有筛选条件, 务必先各自筛选, 分而治之然后再进行union

优化后的方案：

-- 总数
select count(*) from
 (
select id from order1 where state=1 
 union all 
select id from order2 where state=1 
 ) ua ;
 
-- 分页
select * from
 (
select id,order_no,state,create_time from order1 where state=1
 union all 
select id,order_no,state,create_time from order2 where state=1
 ) ua 
order by create_time desc 
limit 0,10;

2, 如果没有任何筛选条件, 务必充分利用“覆盖索引”- Covering Index

覆盖索引,简单理解就是 select 字段(身上有索引) from table , 这种情况不会去遍历表, 使用explain解释覆盖索引语句时,会显示type为index , extra为Using index

当无筛选条件进行分页时, 首先利用覆盖索引快速查出一页的id, 然后再用in去查询出该页的行数据

-- 覆盖索引, 假如id是主键, create_time身上有索引
select * from
 (
select id,create_time from order1 
 union all 
select id,create_time from order2 
 ) ua order by create_time desc
limit 0,10;
 
-- 根据上面查询出的id,再去in查询指定的行数据
select * from
 (
select id,order_no,state,create_time from order1 where id in (...)
 union all 
select id,order_no,state,create_time from order2 where id in (...)
 ) ua