分頁查詢接口，從2s優(yōu)化到了0.01s

作者：蘇三 2024-10-28 07:00:00

數(shù)據(jù)庫其他數(shù)據(jù)庫

這篇文章從9個(gè)方面跟大家一起聊聊分頁查詢接口優(yōu)化的一些小技巧，希望對(duì)你會(huì)有所幫助。

前言

最近在我的知識(shí)星球中，有個(gè)小伙伴問了這樣一個(gè)問題：百萬商品分頁查詢接口，如何保證接口的性能？

這就需要對(duì)該分頁查詢接口做優(yōu)化了。

這篇文章從9個(gè)方面跟大家一起聊聊分頁查詢接口優(yōu)化的一些小技巧，希望對(duì)你會(huì)有所幫助。

1.增加默認(rèn)條件

對(duì)于分頁查詢接口，如果沒有特殊要求，我們可以在輸入?yún)?shù)中，給一些默認(rèn)值。

這樣可以縮小數(shù)據(jù)范圍，避免每次都count所有數(shù)據(jù)的情況。

對(duì)于商品查詢，這種業(yè)務(wù)場(chǎng)景，我們可以默認(rèn)查詢當(dāng)天上架狀態(tài)的商品列表。

例如：

select * from product 
where edit_date>='2023-02-20' and edit_date<'2023-02-21' and status=1

如果每天有變更的商品數(shù)量不多，通過這兩個(gè)默認(rèn)條件，就能過濾掉絕大部分?jǐn)?shù)據(jù)，讓分頁查詢接口的性能提升不少。

溫馨提醒一下：記得給時(shí)間和狀態(tài)字段增加一個(gè)聯(lián)合索引。

2.減少每頁大小

分頁查詢接口通常情況下，需要接收兩個(gè)參數(shù)：pageNo（即：頁碼）和pageSize（即：每頁大小）。

如果分頁查詢接口的調(diào)用端，沒有傳pageNo默認(rèn)值是1，如果沒有傳pageSize也可以給一個(gè)默認(rèn)值10或者20。

不太建議pageSize傳入過大的值，會(huì)直接影響接口性能。

在前端有個(gè)下拉控件，可以選擇每頁的大小，選擇范圍是：10、20、50、100。

前端默認(rèn)選擇的每頁大小為10。

不過在實(shí)際業(yè)務(wù)場(chǎng)景中，要根據(jù)產(chǎn)品需求而且，這里只是一個(gè)參考值。

3.減少join表的數(shù)量

有時(shí)候，我們的分頁查詢接口的查詢結(jié)果，需要join多張表才能查出數(shù)據(jù)。

比如在查詢商品信息時(shí)，需要根據(jù)商品名稱、單位、品牌、分類等信息查詢數(shù)據(jù)。

這時(shí)候?qū)懸粭lsql可以查出想要的數(shù)據(jù)，比如下面這樣的：

select 
  p.id,
  p.product_name,
  u.unit_name,
  b.brand_name,
  c.category_name
from product p
inner join unit u on p.unit_id = u.id
inner join brand b on p.brand_id = b.id
inner join category c on p.category_id = c.id
where p.name='測(cè)試商品' 
limit 0,20;

使用product表去join了unit、brand和category這三張表。

其實(shí)product表中有unit_id、brand_id和category_id三個(gè)字段。

我們可以先查出這三個(gè)字段，獲取分頁的數(shù)據(jù)縮小范圍，之后再通過主鍵id集合去查詢額外的數(shù)據(jù)。

我們可以把sql改成這樣：

select 
  p.id,
  p.product_id,
  u.unit_id,
  b.brand_id,
  c.category_id
from product
where name='測(cè)試商品'
limit 0,20;

這個(gè)例子中，分頁查詢之后，我們獲取到的商品列表其實(shí)只要20條數(shù)據(jù)。

再根據(jù)20條數(shù)據(jù)中的id集合，獲取其他的名稱，例如：

select id,name 
from unit
where id in (1,2,3);

然后在程序中填充其他名稱。

偽代碼如下：

List<Product> productList = productMapper.search(searchEntity);
List<Long> unitIdList = productList.stream().map(Product::getUnitId).distinct().collect(Collectors.toList());
List<Unit> unitList = UnitMapper.queryUnitByIdList(unitIdList);
for(Product product: productList) {
   Optional<Unit> optional = unitList.stream().filter(x->x.getId().equals(product.getId())).findAny();
   if(optional.isPersent()) {
      product.setUnitName(optional.get().getName());
   } 
}

這樣就能有效的減少join表的數(shù)量，可以一定的程度上優(yōu)化查詢接口的性能。

4.優(yōu)化索引

分頁查詢接口性能出現(xiàn)了問題，最直接最快速的優(yōu)化辦法是：優(yōu)化索引。

因?yàn)閮?yōu)化索引不需要修改代碼，只需回歸測(cè)試一下就行，改動(dòng)成本是最小的。

我們需要使用explain關(guān)鍵字，查詢一下生產(chǎn)環(huán)境分頁查詢接口的執(zhí)行計(jì)劃。

看看有沒有創(chuàng)建索引，創(chuàng)建的索引是否合理，或者索引失效了沒。

索引不是創(chuàng)建越多越好，也不是創(chuàng)建越少越好，我們需要根據(jù)實(shí)際情況，到生產(chǎn)環(huán)境測(cè)試一下sql的耗時(shí)情況，然后決定如何創(chuàng)建或優(yōu)化索引。

建議優(yōu)先創(chuàng)建聯(lián)合索引。

5.用straight_join

有時(shí)候我們的業(yè)務(wù)場(chǎng)景很復(fù)雜，有很多查詢sql，需要?jiǎng)?chuàng)建多個(gè)索引。

在分頁查詢接口中根據(jù)不同的輸入?yún)?shù)，最終的查詢sql語句，MySQL根據(jù)一定的抽樣算法，卻選擇了不同的索引。

不知道你有沒有遇到過，某個(gè)查詢接口，原本性能是沒問題的，但一旦輸入某些參數(shù)，接口響應(yīng)時(shí)間就非常長。

這時(shí)候如果你此時(shí)用explain關(guān)鍵字，查看該查詢sql執(zhí)行計(jì)劃，會(huì)發(fā)現(xiàn)現(xiàn)在走的索引，跟之前不一樣，并且驅(qū)動(dòng)表也不一樣。

之前一直都是用表a驅(qū)動(dòng)表b，走的索引c。

此時(shí)用的表b驅(qū)動(dòng)表a，走的索引d。

為了解決Mysql選錯(cuò)索引的問題，最常見的手段是使用force_index關(guān)鍵字，在代碼中指定走的索引名稱。

但如果在代碼中硬編碼了，后面一旦索引名稱修改了，或者索引被刪除了，程序可能會(huì)直接報(bào)錯(cuò)。

這時(shí)該怎么辦呢？

答：我們可以使用straight_join代替inner join。

straight_join會(huì)告訴Mysql用左邊的表驅(qū)動(dòng)右邊的表，能改表優(yōu)化器對(duì)于聯(lián)表查詢的執(zhí)行順序。

之前的查詢sql如下：

select p.id from product p
inner join warehouse w on p.id=w.product_id;
...

我們用它將之前的查詢sql進(jìn)行優(yōu)化：

select p.id from product p
straight_join warehouse w on p.id=w.product_id;
...

6.數(shù)據(jù)歸檔

隨著時(shí)間的推移，我們的系統(tǒng)用戶越來越多，產(chǎn)生的數(shù)據(jù)也越來越多。

單表已經(jīng)到達(dá)了幾千萬。

這時(shí)候分頁查詢接口性能急劇下降，我們不能不做分表處理了。

做簡單的分表策略是將歷史數(shù)據(jù)歸檔，比如：在主表中只保留最近三個(gè)月的數(shù)據(jù)，三個(gè)月前的數(shù)據(jù)，保證到歷史表中。

我們的分頁查詢接口，默認(rèn)從主表中查詢數(shù)據(jù)，可以將數(shù)據(jù)范圍縮小很多。

如果有特殊的需求，再從歷史表中查詢數(shù)據(jù)，最近三個(gè)月的數(shù)據(jù)，是用戶關(guān)注度最高的數(shù)據(jù)。

7.使用count(*)

在分頁查詢接口中，需要在sql中使用count關(guān)鍵字查詢總記錄數(shù)。

目前count有下面幾種用法：

count(1)
count(id)
count(普通索引列)
count(未加索引列)

那么它們有什么區(qū)別呢？

count(*) ：它會(huì)獲取所有行的數(shù)據(jù)，不做任何處理，行數(shù)加1。
count(1)：它會(huì)獲取所有行的數(shù)據(jù)，每行固定值1，也是行數(shù)加1。
count(id)：id代表主鍵，它需要從所有行的數(shù)據(jù)中解析出id字段，其中id肯定都不為NULL，行數(shù)加1。
count(普通索引列)：它需要從所有行的數(shù)據(jù)中解析出普通索引列，然后判斷是否為NULL，如果不是NULL，則行數(shù)+1。
count(未加索引列)：它會(huì)全表掃描獲取所有數(shù)據(jù)，解析中未加索引列，然后判斷是否為NULL，如果不是NULL，則行數(shù)+1。

由此，最后count的性能從高到低是：

count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)

所以，其實(shí)count(*)是最快的。

我們?cè)谑褂胏ount統(tǒng)計(jì)總記錄數(shù)時(shí)，一定要記得使用count(*)。

8.從ClickHouse查詢

有些時(shí)候，join的表實(shí)在太多，沒法去掉多余的join，該怎么辦呢？

答：可以將數(shù)據(jù)保存到ClickHouse。

ClickHouse是基于列存儲(chǔ)的數(shù)據(jù)庫，不支持事務(wù)，查詢性能非常高，號(hào)稱查詢十幾億的數(shù)據(jù)，能夠秒級(jí)返回。

為了避免對(duì)業(yè)務(wù)代碼的嵌入性，可以使用Canal監(jiān)聽Mysql的binlog日志。當(dāng)product表有數(shù)據(jù)新增時(shí)，需要同時(shí)查詢出單位、品牌和分類的數(shù)據(jù)，生成一個(gè)新的結(jié)果集，保存到ClickHouse當(dāng)中。

查詢數(shù)據(jù)時(shí)，從ClickHouse當(dāng)中查詢，這樣使用count(*)的查詢效率能夠提升N倍。