mysql count函式與分頁功能極限優化

gooogle發表於2021-01-14
MySql函式優化

@TOC

某後臺的功能列表，頁面底部為通用分頁：
總條數: 16209321 頁碼：1 2 3 4 5 .... 9819
頁面預設展示10條資料，預設展示條數可選。
頁面上部分搜尋區域部分有多達20-30的篩選條件，篩選條件分別來自於不下10張資料表。 拿訂單列表查詢舉例，可以使用使用者表裡的某個特殊欄位進行篩選，如性別等，這些欄位肯定不會在訂單表儲存，所以必然會進行聯表。
使用者常常有疑問： 為何頁面只有10條資料，查詢卻如此之慢？
老闆會質疑你，做的是什麼玩意？查詢10條資料都要1分鐘以上的時間？（優化前頁面需要轉1分鐘才可顯示出資料，頁面轉圈圈~）

第一次優化：

排查效能問題，首先想到的是優化資料表索引以及sql語句，一般考慮的點有下
1、 索引是否有問題？是否按照該表的實際使用場景建立了合適的複合索引？
2、 編寫的sql是否符合索引規範？是否使用了索引?
3、 是否使用了合適的索引？這點也比較關鍵。

通過分析可知，依舊拿訂單舉例，後臺查詢條件會預設時間欄位，查詢近一個月的，按照欄位查詢常用的順序，分別建立複合索引。
1、 以我們的業務舉例，最長使用的欄位查詢依次為：按時間查、按銷售地域查、按合同查、按客戶查、按訂單號查，所以建立對應複合索引。
2、 優化查詢sql，將where語句中的查詢條件按順序排列，一定要按照最左匹配原則。
3、 當使用Explain關鍵字進行仔細除錯後，最終此次調優效果成功將介面時間提升一倍以上，查詢某月資料（一個月），索引type為range級別，最終優化優化後，時間來到了：50秒

第二次優化：

在第一次優化後艱難使用一段時間後，依然遭到投訴，想了想，遭到投訴也是必然的，50秒開一個頁面。決定再次優化，本次的考慮點如下：
1、 由於時間慢是因為count語句造成，count語句大約需要40多秒的時間，實際資料查詢經過了第一次優化只需要不到十秒。而count函式又是必不可少的，因為需要查詢總數，以供分頁顯示總條數及最後一頁，所以必須從count優化入手
2、 如果技術上優化遇到了瓶頸，或者說mysql已經優化到極致，那麼能否從業務上解決?

優化分析主要從兩個角度進行。

1、 從技術角度來看，查詢必有篩選條件，由於幾十個篩選條件的取值不確定性，通過快取count的總條數是無法滿足的。繼續觀察mysql索引情況，由於現有索引的key_len過大，可以通過建立較小的索引(使用小欄位)來為排序使用，由於我們的業務查詢必有時間段條件，固為時間段欄位單獨建立索引，由此帶來了幾秒的效能提升(並不明顯)。

2、 從業務角度看，我們觀察了百度、google、微博等網站，分頁都不會顯示結果集的總條數以及也不會有最後一頁的連結，都是通過點選下一頁的方式不斷檢索後面的資料，也就沒有使用count函式的必要了，經過與需求方溝通，發現我們的想法並不複合需求，由於是後臺管理系統，是需要通過篩選條件實時查詢總條數，固此優化想法pass。
3、 通過溝通，得到另一個優化方向，可以將前端分頁元件非同步載入。首先將資料列表展示出來，方便其它操作。固修改前端，分別掉2次介面，獲取資料介面、獲取分頁結果介面。分頁結果介面返回資料前，分頁元件顯示“正在努力載入…”字樣。
此種優化最終實現：列表資料載入<5秒，分頁元件載入>40秒

第三次優化：

通過學習研究發現，mysql innodb引擎在有索引、有where條件的情況下，count速度並不慢，所以問題一樣還出在sql上。

1, 通過分析sql發現，由於查詢條件眾多，只是對where語句後面的sql進行了動態sql處理，而left join語句沒有進行動態sql處理，導致不管有幾個查詢條件，都需要連結10張左右其它資料表。是連結查詢限制了sql的效能！而不是count限制的sql的效能！ 固做以下優化，將left join一併使用動態sql連結：

 SELECT
    count(*)
 FROM
    xxx t
    <choose>
        <when test="查詢條件1存在">
            LEFT JOIN aaa uoc ON (t.user_id = uoc.user_id)
        </when>
        <when test="查詢條件2存在">
            LEFT JOIN ccc uoc ON (t.user_id = uoc.user_id)
        </when>
         <otherwise>
        </otherwise>
    </choose>
    <if test="查詢條件3存在">
        LEFT JOIN ddd pcc ON t.center_id = pcc.id
    </if>
    ....等其它N張連結表
    WHERE
        t.cycle_start_time BETWEEN #{x1} AND #{x2}
    <if test="查詢條件1存在">
        AND uoc.user_id = #{x3}
    </if>
    <if test="查詢條件2存在">
        AND uoc.alias = #{accountInstanceDTO.userName}
    </if>
    <if test="查詢條件3存在">
        AND t.product_line = #{x4}
    </if>
    ... 等其餘N個查詢條件
  1. 與需求方溝通後，查詢列表可去掉排序規則，使用預設排序即可。此點優化也十分重要，order by會導致 file sort，而極大的影響查詢效能，所以我們去掉了order by語句，而使用預設的id排序。
  2. 最終優化： 列表資料查詢130ms，分頁外掛查詢(count查詢)150ms，目前資料單表資料量級在2000萬左右，以次時間效率推斷，多查詢條件的複雜分頁查詢，可以支援單表幾億沒有問題。

關於複雜頁面分頁查詢的優化方法，以及count函式的優化方法，我思考、想了很多。希望有更極致的優化思路、更新奇的業務思路。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章