MySQL/SQL Server分页优化（三）

今天加班摸鱼，去刷了一下自己以前写的文章，发现前面出过两篇关于分页的优化，有两个分页的基本优化手法没有写完，今日补上，在数据库报表查询中，分页是最基本的操作，而围绕分页有很多特定的优化手法。今天再把我工作中常用的另个两种优化手法分享一下。
换了工作，换了电脑，这家公司没有用的SQL Server 所以就用MySQL来举例了，但接下来我要讲的两种优化手法只和技巧相关，所有数据库都是通用的。

第一种  延迟关联

延迟关联是常见且最好用的一种分页手法

下面通过实际例子还介绍这种方法

数据情况：

1、单据表：erp_bill_index 表中存有客户所有历史账单  数据行数为 220W行，数据列数为60列左右

建表语句省略如下

CREATE TABLE `erp_bill_index` (  
`profileid` int(20) unsigned NOT NULL COMMENT '账套id',
`billid` bigint(20) NOT NULL COMMENT '单据id',  
`billtype` int(11) NOT NULL COMMENT '单据类型',  
`billcode` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '单据编号',  
`billdate` datetime NOT NULL COMMENT '单据日期',

 ...... 省略N个字段

  PRIMARY KEY (`billid`),

  KEY `idx_billdate` (`profileid`,`billdate`)

  .....

假如有一个看单据历史的报表

select * from erp_bill_index  
where profileid = 200006141
and  billdate > '2021-01-01'
order by profileid,billdate,billid
limit 200000,10;

执行上述语句，需要4.188S

我们换一种写法

select * from erp_bill_index a
inner join (select billid from erp_bill_index b   force index(idx_billdate)
            where profileid = 200006141
			and  billdate > '2021-01-01'
            order by profileid,billdate,billid
            limit 200000,10
            ) t1 on a.billid = t1.billid

执行上述语句只需要 0.047S

利用延迟关联，性能提升89倍

我们看一下延迟关联的执行计划

从子查询可以得知。派生表是完成了索引覆盖，加载更少的列到bufferpool中 也就加载了更少的page到bufferpool中。

另外一个小知识点是 当order by 的列值不能唯一标识行时，返回的行是不固定的。 这个在官方可以找到原话，所以我最后还加上了billid主键列，确保order by的列所在的行是唯一的。那么在数据不发生变化的情况下。我查询的结果就是恒定的

第二种  去掉JOIN 做第二次查询

举例说明一下 假设我们的一个报表数据是这样的

  SELECT A.A1,A.A2,A.A3,B.B1,B.B2，C.C1,C.C2
  from A 
  INNER JOIN B ON A.ID = B.ID
  INNER JOIN C ON A.ID = C.ID
  ORDER BY A.ID
  LIMIT 100,10

如果A,B,C都是千万级的大表。
那么上述查询去掉JOIN就会快很多

改造成这样

SELECT A.A1,A.A2,A.A3,A.ID
from A 
ORDER BY A.ID
LIMIT 100,10

然后再单独查B表的列与C表的列

SELECT B.B1,B.B2,B.ID
FROM B
WHERE B.ID IN (第一个查询的10个ID)

这样B表与C表都会扫描极少数的行

然后在后端代码中把三个结果集进行合并

这种做法有一个唯一的缺陷，就是不能支持对B,C表的列进行排序操作

如果你的应用中有大量这种分页报表，排序的列是固定的某一个表的。那么其它表都可能通过第二次查询只关联当前页的ID的来解决。会极大的提升分页报表的性能。