今天加班摸魚，去刷了一下自己以前寫的文章，發現前面出過兩篇關于分頁的優化，有兩個分頁的基本優化手法沒有寫完，今日補上，在數據庫報表查詢中，分頁是最基本的操作，而圍繞分頁有很多特定的優化手法。今天再把我工作中常用的另個兩種優化手法分享一下。
換了工作，換了電腦，這家公司沒有用的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的來解決。會極大的提升分頁報表的性能。
