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

gooogle發表於2021-01-14

@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 協議》,轉載必須註明作者和本文連結

相關文章