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的耗時情況，然後決定如何建立或最佳化索引。
建議優先建立聯合索引。
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 pinner join warehouse w on p.id=w.product_id;...
我們用它將之前的查詢sql進行最佳化：
select p.id from product pstraight_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從主庫同步過來。
根據不同的使用者量，可以做一主一從，一主兩從，或一主多從。
資料庫讀寫分離之後，能夠提升查詢介面的效能。