1. 首頁 > 生活 > >

mysql分頁語法 mysql分頁的幾種方式

小知識mysql分頁語法



文章插圖
mysql分頁語法 mysql分頁的幾種方式

文章插圖
優化項目代碼過程中發現一個千萬級數據深分頁問題，緣由是這樣的
庫里有一張耗材 MCS_PROD 表，通過同步外部數據中臺多維度數據，在系統內部組裝為單一耗材產品，最終同步到 ES 搜索引擎
MySQL 同步 ES 流程如下：
通過定時任務的形式觸發同步，比如間隔半天或一天的時間頻率同步的形式為增量同步，根據更新時間的機制，比如第一次同步查詢 >= 1970-01-01 00:00:00.0記錄最大的更新時間進行存儲，下次更新同步以此為條件以分頁的形式獲取數據，當前頁數量加一，循環到最后一頁
在這里問題也就出現了，MySQL 查詢分頁 OFFSET 越深入，性能越差，初步估計線上 MCS_PROD 表中記錄在 1000w 左右
如果按照每頁 10 條，OFFSET 值會拖垮查詢性能，進而形成一個 “性能深淵”
同步類代碼針對此問題有兩種優化方式：
采用游標、流式方案進行優化優化深分頁性能，文章圍繞這個題目展開
一、軟硬件說明
MySQL VERSION
mysql>selectversion();+-----------+|version()|+-----------+|5.7.30|+-----------+1rowinset(0.01sec)表結構說明
借鑒公司表結構，字段、長度以及名稱均已刪減
mysql>DESCMCS_PROD;+-----------------------+--------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-----------------------+--------------+------+-----+---------+----------------+|MCS_PROD_ID|int(11)|NO|PRI|NULL|auto_increment||MCS_CODE|varchar(100)|YES|||||MCS_NAME|varchar(500)|YES|||||UPDT_TIME|datetime|NO|MUL|NULL||+-----------------------+--------------+------+-----+---------+----------------+4rowsinset(0.01sec)通過測試同學幫忙造了 500w 左右數據量
mysql>SELECTCOUNT(*)FROMMCS_PROD;+----------+|count(*)|+----------+|5100000|+----------+1rowinset(1.43sec)SQL 語句如下
因為功能需要滿足 增量拉取的方式，所以會有數據更新時間的條件查詢，以及相關 查詢排序（此處有坑）
SELECTMCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIMEFROMMCS_PRODWHEREUPDT_TIME>='1970-01-0100:00:00.0'ORDERBYUPDT_TIMELIMITxx,xx【mysql分頁語法 mysql分頁的幾種方式】二、重新認識 MySQL 分頁
LIMIT 子句可以被用于強制 SELECT 語句返回指定的記錄數 。LIMIT 接收一個或兩個數字參數，參數必須是一個整數常量
如果給定兩個參數，第一個參數指定第一個返回記錄行的偏移量，第二個參數指定返回記錄行的最大數
舉個簡單的例子，分析下 SQL 查詢過程，掌握深分頁性能為什么差
mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT100000,1;+-------------+-------------------------+------------------+---------------------+|MCS_PROD_ID|MCS_CODE|MCS_NAME|UPDT_TIME|+-------------+-------------------------+------------------+---------------------+|181789|XA601709733186213015031|尺、橈骨LC-DCP骨板|2020-10-1916:22:19|+-------------+-------------------------+------------------+---------------------+1rowinset(3.66sec)mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT100000,1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|1|SIMPLE|MCS_PROD|NULL|range|MCS_PROD_1|MCS_PROD_1|5|NULL|2296653|100.00|Usingindexcondition|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+1rowinset,1warning(0.01sec)簡單說明下上面 SQL 執行過程：
首先查詢了表 MCS_PROD，進行過濾 UPDT_TIME 條件，查詢出展示列（涉及回表操作）進行排序以及 LIMITLIMIT 100000, 1 的意思是掃描滿足條件的 100001 行，然后扔掉前 100000 行
MySQL 耗費了 大量隨機 I/O 在回表查詢聚簇索引的數據上，而這 100000 次隨機 I/O 查詢數據不會出現在結果集中
如果系統并發量稍微高一點，每次查詢掃描超過 100000 行，性能肯定堪憂，另外 LIMIT 分頁 OFFSET 越深，性能越差（多次強調）
圖1 數據僅供參考
三、深分頁優化
關于 MySQL 深分頁優化常見的大概有以下三種策略：
子查詢優化延遲關聯書簽記錄
上面三點都能大大地提升查詢效率，核心思想就是讓 MySQL 盡可能掃描更少的頁面，獲取需要訪問的記錄后再根據關聯列回原表查詢所需要的列
3.1 子查詢優化
子查詢深分頁優化語句如下：
mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID>=(SELECTm1.MCS_PROD_IDFROMMCS_PRODm1WHEREm1.UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYm1.UPDT_TIMELIMIT3000000,1)LIMIT1;+-------------+-------------------------+------------------------+|MCS_PROD_ID|MCS_CODE|MCS_NAME|+-------------+-------------------------+------------------------+|3021401|XA892010009391491861476|金屬解剖型接骨板T型接骨板A|+-------------+-------------------------+------------------------+1rowinset(0.76sec)mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID>=(SELECTm1.MCS_PROD_IDFROMMCS_PRODm1WHEREm1.UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYm1.UPDT_TIMELIMIT3000000,1)LIMIT1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+|1|PRIMARY|MCS_PROD|NULL|range|PRIMARY|PRIMARY|4|NULL|2296653|100.00|Usingwhere||2|SUBQUERY|m1|NULL|range|MCS_PROD_1|MCS_PROD_1|5|NULL|2296653|100.00|Usingwhere;Usingindex|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+--------------------------+2rowsinset,1warning(0.77sec)根據執行計劃得知，子查詢 table m1 查詢是用到了索引 。首先在 索引上拿到了聚集索引的主鍵 ID 省去了回表操作，然后第二查詢直接根據第一個查詢的 ID 往后再去查 10 個就可以了
圖2 數據僅供參考
3.2 延遲關聯
“延遲關聯” 深分頁優化語句如下：
mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODINNERJOIN(SELECTm1.MCS_PROD_IDFROMMCS_PRODm1WHEREm1.UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYm1.UPDT_TIMELIMIT3000000,1)ASMCS_PROD2USING(MCS_PROD_ID);+-------------+-------------------------+------------------------+|MCS_PROD_ID|MCS_CODE|MCS_NAME|+-------------+-------------------------+------------------------+|3021401|XA892010009391491861476|金屬解剖型接骨板T型接骨板A|+-------------+-------------------------+------------------------+1rowinset(0.75sec)mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODINNERJOIN(SELECTm1.MCS_PROD_IDFROMMCS_PRODm1WHEREm1.UPDT_TIME>='1970-01-0100:00:00.0'ORDERBYm1.UPDT_TIMELIMIT3000000,1)ASMCS_PROD2USING(MCS_PROD_ID);+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+|1|PRIMARY|<derived2>|NULL|ALL|NULL|NULL|NULL|NULL|2296653|100.00|NULL||1|PRIMARY|MCS_PROD|NULL|eq_ref|PRIMARY|PRIMARY|4|MCS_PROD2.MCS_PROD_ID|1|100.00|NULL||2|DERIVED|m1|NULL|range|MCS_PROD_1|MCS_PROD_1|5|NULL|2296653|100.00|Usingwhere;Usingindex|+----+-------------+------------+------------+--------+---------------+------------+---------+-----------------------+---------+----------+--------------------------+3rowsinset,1warning(0.00sec)思路以及性能與子查詢優化一致，只不過采用了 JOIN 的形式執行
3.3 書簽記錄
關于 LIMIT 深分頁問題，核心在于 OFFSET 值，它會 導致 MySQL 掃描大量不需要的記錄行然后拋棄掉
我們可以先使用書簽 記錄獲取上次取數據的位置，下次就可以直接從該位置開始掃描，這樣可以 避免使用 OFFEST
假設需要查詢 3000000 行數據后的第 1 條記錄，查詢可以這么寫
mysql>SELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID<3000000ORDERBYUPDT_TIMELIMIT1;+-------------+-------------------------+---------------------------------+|MCS_PROD_ID|MCS_CODE|MCS_NAME|+-------------+-------------------------+---------------------------------+|127|XA683240878449276581799|股骨近端-1螺紋孔鎖定板（純鈦）YJBL01|+-------------+-------------------------+---------------------------------+1rowinset(0.00sec)mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAMEFROMMCS_PRODWHEREMCS_PROD_ID<3000000ORDERBYUPDT_TIMELIMIT1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+|1|SIMPLE|MCS_PROD|NULL|index|PRIMARY|MCS_PROD_1|5|NULL|2|50.00|Usingwhere|+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+1rowinset,1warning(0.00sec)好處是很明顯的，查詢速度超級快，性能都會穩定在毫秒級，從性能上考慮碾壓其它方式
不過這種方式局限性也比較大，需要一種類似連續自增的字段，以及業務所能包容的連續概念，視情況而定
上圖是阿里云 OSS Bucket 桶內文件列表，大膽猜測是不是可以采用書簽記錄的形式完成
四、ORDER BY 巨坑, 慎踩
以下言論可能會打破你對 order by 所有 美好 YY
先說結論吧，當 LIMIT OFFSET 過深時，會使 ORDER BY 普通索引失效（聯合、唯一這些索引沒有測試）
mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT100000,1;+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+|1|SIMPLE|MCS_PROD|NULL|range|MCS_PROD_1|MCS_PROD_1|5|NULL|2296653|100.00|Usingindexcondition|+----+-------------+----------+------------+-------+---------------+------------+---------+------+---------+----------+-----------------------+1rowinset,1warning(0.00sec)先來說一下這個 ORDER BY 執行過程：
初始化 SORT_BUFFER，放入 MCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIME 四個字段從索引 UPDT_TIME 找到滿足條件的主鍵 ID，回表查詢出四個字段值存入 SORT_BUFFER從索引處繼續查詢滿足 UPDT_TIME 條件記錄，繼續執行步驟 2對 SORT_BUFFER 中的數據按照 UPDT_TIME 排序排序成功后取出符合 LIMIT 條件的記錄返回客戶端
按照 UPDT_TIME 排序可能在內存中完成，也可能需要使用外部排序，取決于排序所需的內存和參數 SORT_BUFFER_SIZE
SORT_BUFFER_SIZE 是 MySQL 為排序開辟的內存 。如果排序數據量小于 SORT_BUFFER_SIZE，排序會在內存中完成 。如果數據量過大，內存放不下，則會利用磁盤臨時文件排序
針對 SORT_BUFFER_SIZE 這個參數在網上查詢到有用資料比較少，大家如果測試過程中存在問題，可以加微信一起溝通
4.1 ORDER BY 索引失效舉例
OFFSET 100000 時，通過 key Extra 得知，沒有使用磁盤臨時文件排序，這個時候把 OFFSET 調整到 500000
涼涼夜色為你思念成河，化作春泥呵護著你… 一首涼涼送給寫這個 SQL 的同學，發現了 Using filesort
mysql>EXPLAINSELECTMCS_PROD_ID,MCS_CODE,MCS_NAME,UPDT_TIMEFROMMCS_PRODWHERE(UPDT_TIME>='1970-01-0100:00:00.0')ORDERBYUPDT_TIMELIMIT500000,1;+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+|1|SIMPLE|MCS_PROD|NULL|ALL|MCS_PROD_1|NULL|NULL|NULL|4593306|50.00|Usingwhere;Usingfilesort|+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+1rowinset,1warning(0.00sec)Using filesort 表示在索引之外，需要額外進行外部的排序動作，性能必將受到嚴重影響
所以我們應該 結合相對應的業務邏輯避免常規 LIMIT OFFSET，采用 # 深分頁優化 章節進行修改對應業務
結言
最后有一點需要聲明下，MySQL 本身并不適合單表大數據量業務
因為 MySQL 應用在企業級項目時，針對庫表查詢并非簡單的條件，可能會有更復雜的聯合查詢，亦或者是大數據量時存在頻繁新增或更新操作，維護索引或者數據 ACID 特性上必然存在性能犧牲
如果設計初期能夠預料到庫表的數據增長，理應構思合理的重構優化方式，比如 ES 配合查詢、分庫分表、TiDB 等解決方式