前言

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

這就需要對該分頁查詢接口做優化了。

這篇文章從9個方面跟大家一起聊聊分頁查詢接口優化的一些小技巧，希望對你會有所幫助。

圖片

1 增加默認條件

對于分頁查詢接口，如果沒有特殊要求，我們可以在輸入參數中，給一些默認值。

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

對于商品查詢，這種業務場景，我們可以默認查詢當天上架狀態的商品列表。

例如：

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

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

溫馨提醒一下：記得給時間和狀態字段增加一個聯合索引。

2 減少每頁大小

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

如果分頁查詢接口的調用端，沒有傳pageNo默認值是1，如果沒有傳pageSize也可以給一個默認值10或者20。

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

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

前端默認選擇的每頁大小為10。

不過在實際業務場景中，要根據產品需求而且，這里只是一個參考值。

3 減少join表的數量

有時候，我們的分頁查詢接口的查詢結果，需要join多張表才能查出數據。

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

這時候寫一條sql可以查出想要的數據，比如下面這樣的：

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='測試商品' 
limit 0,20;

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

其實product表中有unit_id、brand_id和category_id三個字段。

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

我們可以把sql改成這樣：

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

這個例子中，分頁查詢之后，我們獲取到的商品列表其實只要20條數據。

再根據20條數據中的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表的數量，可以一定的程度上優化查詢接口的性能。

4 優化索引

分頁查詢接口性能出現了問題，最直接最快速的優化辦法是：優化索引。

因為優化索引不需要修改代碼，只需回歸測試一下就行，改動成本是最小的。

我們需要使用explain關鍵字，查詢一下生產環境分頁查詢接口的執行計劃。

看看有沒有創建索引，創建的索引是否合理，或者索引失效了沒。

索引不是創建越多越好，也不是創建越少越好，我們需要根據實際情況，到生產環境測試一下sql的耗時情況，然后決定如何創建或優化索引。

建議優先創建聯合索引。

如果你對explain關鍵字的用法比較感興趣，可以看看我的這篇文章《explain | 索引優化的這把絕世好劍，你真的會用嗎？》。

如果你對索引失效的問題比較感興趣，可以看看我的這篇文章《聊聊索引失效的10種場景，太坑了》。

5 用straight_join

有時候我們的業務場景很復雜，有很多查詢sql，需要創建多個索引。

在分頁查詢接口中根據不同的輸入參數，最終的查詢sql語句，MySQL根據一定的抽樣算法，卻選擇了不同的索引。

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

這時候如果你此時用explain關鍵字，查看該查詢sql執行計劃，會發現現在走的索引，跟之前不一樣，并且驅動表也不一樣。

之前一直都是用表a驅動表b，走的索引c。

此時用的表b驅動表a，走的索引d。

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

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

這時該怎么辦呢？

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

straight_join會告訴Mysql用左邊的表驅動右邊的表，能改表優化器對于聯表查詢的執行順序。

之前的查詢sql如下：

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

我們用它將之前的查詢sql進行優化：

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

6 數據歸檔

隨著時間的推移，我們的系統用戶越來越多，產生的數據也越來越多。

單表已經到達了幾千萬。

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

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

我們的分頁查詢接口，默認從主表中查詢數據，可以將數據范圍縮小很多。

如果有特殊的需求，再從歷史表中查詢數據，最近三個月的數據，是用戶關注度最高的數據。

7 使用count(*)

在分頁查詢接口中，需要在sql中使用count關鍵字查詢總記錄數。

目前count有下面幾種用法：

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

那么它們有什么區別呢？

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

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

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

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

我們在使用count統計總記錄數時，一定要記得使用count(*)。

8 從ClickHouse查詢

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

答：可以將數據保存到ClickHouse。

ClickHouse是基于列存儲的數據庫，不支持事務，查詢性能非常高，號稱查詢十幾億的數據，能夠秒級返回。

為了避免對業務代碼的嵌入性，可以使用Canal監聽Mysql的binlog日志。當product表有數據新增時，需要同時查詢出單位、品牌和分類的數據，生成一個新的結果集，保存到ClickHouse當中。

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

需要特別提醒一下：使用ClickHouse時，新增數據不要太頻繁，盡量批量插入數據。

其實如果查詢條件非常多，使用ClickHouse也不是特別合適，這時候可以改成ElasticSearch，不過它跟Mysql一樣，存在深分頁問題。

9 數據庫讀寫分離

有時候，分頁查詢接口性能差，是因為用戶并發量上來了。

在系統的初期，還沒有多少用戶量，讀數據請求和寫數據請求，都是訪問的同一個數據庫，該方式實現起來簡單、成本低。

剛開始分頁查詢接口性能沒啥問題。

但隨著用戶量的增長，用戶的讀數據請求和寫數據請求都明顯增多。

我們都知道數據庫連接有限，一般是配置的空閑連接數是100-1000之間。如果多余1000的請求，就只能等待，就可能會出現接口超時的情況。

因此，我們有必要做數據庫的讀寫分離。寫數據請求訪問主庫，讀數據請求訪問從庫，從庫的數據通過binlog從主庫同步過來。

根據不同的用戶量，可以做一主一從，一主兩從，或一主多從。

數據庫讀寫分離之后，能夠提升查詢接口的性能。