欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL 千萬級數(shù)據(jù)量如何快速分頁

 更新時間:2020年09月07日 09:27:38   作者:少俠露飛  
這篇文章主要介紹了MySQL 千萬級數(shù)據(jù)量如何快速分頁,幫助大家提高MySQL數(shù)據(jù)庫的性能,感興趣的朋友可以了解下

前言

后端開發(fā)中為了防止一次性加載太多數(shù)據(jù)導致內(nèi)存、磁盤IO都開銷過大,經(jīng)常需要分頁展示,這個時候就需要用到MySQL的LIMIT關鍵字。但你以為LIMIT分頁就萬事大吉了么,Too young,too simple啊,LIMIT在數(shù)據(jù)量大的時候極可能造成的一個問題就是深度分頁。

案例

這里我以顯示電商訂單詳情為背景舉個例子,新建表如下:

CREATE TABLE `cps_user_order_detail` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵',
 `user_id` varchar(32) NOT NULL DEFAULT '' COMMENT '用戶ID',
 `order_id` bigint(20) DEFAULT NULL COMMENT '訂單id',
 `sku_id` bigint(20) unsigned NOT NULL COMMENT '商品ID',
 `order_time` datetime DEFAULT NULL COMMENT '下單時間,格式y(tǒng)yyy-MM-dd HH:mm:ss',
 PRIMARY KEY (`id`),
 KEY `idx_time_user` (`order_time`,`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='用戶訂單詳情';

然后手動向表里插入120W條數(shù)據(jù)。

現(xiàn)在有個需求:分頁展示用戶的訂單詳情,按照下單時間倒序。

表結(jié)構精簡了,需求也簡單。于是嘩嘩嘩的寫完代碼,提測上線了。早期運行一切正常,可隨著訂單量的不斷增大,發(fā)現(xiàn)系統(tǒng)越發(fā)的緩慢,還時不時報出幾個 慢查詢 。

這個時候你就該想到是LIMIT偏移的問題了,沒錯,不是你的SQL不夠優(yōu)美,就是MySQL自身的機制。

這里我就簡單以兩條SQL為例,如下圖,分別是從100和100W的位置偏移分頁,可以看到時間相差很大。這還不算其它數(shù)據(jù)運算和處理的時間,單一條SQL的查詢就耗時一秒以上,在對用戶提供的功能里這是不能容忍的(電商里經(jīng)常要求一個接口的RT不超過200ms)。

這里我們再看下執(zhí)行計劃,如下圖所示:

在此先介紹一下執(zhí)行計劃Extra列可能出現(xiàn)的值及含義:

  1. Using where:表示優(yōu)化器需要通過索引回表查詢數(shù)據(jù)。
  2. Using index:即覆蓋索引,表示直接訪問索引就足夠獲取到所需要的數(shù)據(jù),不需要通過索引回表,通常是通過將待查詢字段建立聯(lián)合索引實現(xiàn)。
  3. Using index condition:在5.6版本后加入的新特性,即大名鼎鼎的索引下推,是MySQL關于減少回表次數(shù)的重大優(yōu)化。
  4. Using filesort:文件排序,這個一般在ORDER BY時候,數(shù)據(jù)量過大,MySQL會將所有數(shù)據(jù)召回內(nèi)存中排序,比較消耗資源。

再看看上圖,同樣的語句,只以為偏移量不同,就造成了執(zhí)行計劃的千差萬別(且容我小小的夸張一下)。第一條語句LIMIT 100,6type列的值是range,表示范圍掃描,性能比ref差一個級別,但是也算走了索引,并且還應用了索引下推:就是說在WHERE之后的下單時間刪選走了索引,并且之后的ORDER BY也是根據(jù)索引下推優(yōu)化,在執(zhí)行WHERE條件篩選時同步進行的(沒有回表)。
而第二條語句LIMIT 1000000,6壓根就沒走索引,type列的值是ALL,顯然是全表掃描。并且Extra列字段里的Using where表示發(fā)生了回表,Using filesort表示ORDER BY時發(fā)生了文件排序。所以這里慢在了兩點:一是文件排序耗時過大,二是根據(jù)條件篩選了相關的數(shù)據(jù)之后,需要根據(jù)偏移量回表獲取全部值。無論是上面的哪一點,都是LIMIT偏移量過大導致的,所以實際開發(fā)環(huán)境經(jīng)常遇到非統(tǒng)計表量級不得超過一百萬的要求。

優(yōu)化

原因分析完了,那么LIMIT深度分頁在實際開發(fā)中怎么優(yōu)化呢?這里少俠給兩點方案。
一是通過主鍵索引優(yōu)化。什么意思呢?就是把上面的語句修改成:

SELECT * FROM cps_user_order_detail d WHERE d.id > #{maxId} AND d.order_time>'2020-8-5 00:00:00' ORDER BY d.order_time LIMIT 6;

如上代碼所示,同樣也是分頁,但是有個maxId的限制條件,這個是什么意思呢,maxId就是上一頁中的最大主鍵Id。所以采用此方式的前提:1)主鍵必須自增不能是UUID并且前端除了傳基本分頁參數(shù)pageNo,pageSize外,還必須把每次上一頁的最大Id帶過來,2)該方式不支持隨機跳頁,也就是說只能上下翻頁。如下圖所示是某知名電商中的實際頁面。

二是通過Elastic Search搜索引擎(基于倒排索引),實際上類似于淘寶這樣的電商基本上都是把所有商品放進ES搜索引擎里的(那么海量的數(shù)據(jù),放進MySQL是不可能的,放進Redis也不現(xiàn)實)。但即使用了ES搜索引擎,也還是有可能發(fā)生深度分頁的問題的,這時怎么辦呢?答案是通過游標scroll。關于此點這里不做深入,感興趣的可以做研究。

小結(jié)

寫這篇博客是因為前段時間在開發(fā)中真實經(jīng)歷到了,并且之前在字節(jié)面試中確實也和面試官探討了一番。知道LIMIT的限制以及優(yōu)化,在面試中能提到是加分項,不能說到MySQL優(yōu)化就是建索引,調(diào)整SQL(實際上在真實開發(fā)中這兩種優(yōu)化方案的成效微乎其微)。畢竟MySQL優(yōu)化那么牛X的話,就不會有那么多中間件產(chǎn)生了。
我是少俠露飛,愛技術,愛分享。

以上就是MySQL 千萬級數(shù)據(jù)量如何快速分頁的詳細內(nèi)容,更多關于MySQL快速分頁的資料請關注腳本之家其它相關文章!

相關文章

  • MySQL登錄時出現(xiàn)ERROR 1045: Access denied for user ‘root‘@‘localhost‘ (using password: YES)無法打開解決方法匯總

    MySQL登錄時出現(xiàn)ERROR 1045: Access denied for&

    本文已解決MySQL登錄時出現(xiàn)Access denied for user ‘root‘@‘localhost‘ (using password: YES)無法打開的相關報錯問題,并總結(jié)提出了幾種可用解決方案,又遇到同樣問題的朋友可以參考閱讀下本文
    2024-09-09
  • Mysql5.7.17 winx64.zip解壓縮版安裝配置圖文教程

    Mysql5.7.17 winx64.zip解壓縮版安裝配置圖文教程

    這篇文章主要介紹了Mysql5.7.17 winx64.zip解壓縮版安裝配置圖文教程,需要的朋友可以參考下
    2018-03-03
  • MySQL使用中遇到的問題記錄

    MySQL使用中遇到的問題記錄

    本文給大家匯總介紹了作者在mysql的使用過程中遇到的問題以及最終的解決方案,非常的實用,有需要的小伙伴可以參考下
    2017-11-11
  • MySQL中USING 和 HAVING 用法實例簡析

    MySQL中USING 和 HAVING 用法實例簡析

    這篇文章主要介紹了MySQL中USING 和 HAVING 用法,結(jié)合實例形式簡單分析了mysql中USING 和 HAVING的功能、使用方法及相關操作注意事項,需要的朋友可以參考下
    2019-08-08
  • SQL和NoSQL之間的區(qū)別總結(jié)

    SQL和NoSQL之間的區(qū)別總結(jié)

    在本篇內(nèi)容里我們給大家精選了關于SQL和NoSQL之間的區(qū)別的總結(jié)內(nèi)容,對此有需要的朋友們跟著學習下。
    2019-02-02
  • mysql隨機查詢10條數(shù)據(jù)的三種方法

    mysql隨機查詢10條數(shù)據(jù)的三種方法

    本文主要介紹了mysql隨機查詢10條數(shù)據(jù)的三種方法,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2023-09-09
  • MySQL5.7主從配置實例解析

    MySQL5.7主從配置實例解析

    這篇文章主要為大家詳細解析了MySQL5.7主從配置的實例,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2017-03-03
  • MySQL 8.0統(tǒng)計信息不準確的原因

    MySQL 8.0統(tǒng)計信息不準確的原因

    這篇文章主要介紹了MySQL 8.0統(tǒng)計信息不準確的原因,幫助大家更好的理解和學習MySQL8.0的相關內(nèi)容,感興趣的朋友可以了解下
    2020-08-08
  • CMD命令操作MySql數(shù)據(jù)庫的方法詳解

    CMD命令操作MySql數(shù)據(jù)庫的方法詳解

    今天小編就為大家分享一篇關于CMD命令操作MySql數(shù)據(jù)庫的方法詳解,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧
    2019-02-02
  • MySQL EXPLAIN輸出列的詳細解釋

    MySQL EXPLAIN輸出列的詳細解釋

    這篇文章主要給大家介紹了關于MySQL EXPLAIN輸出列的詳細解釋,文中通過示例代碼介紹的非常詳細,對大家的學習或者使用mysql具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2021-05-05

最新評論