电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

MySql深分頁問題解決

瀏覽:264日期:2023-02-18 16:43:11
目錄
  • 1. 問題描述
  • 2. 問題分析
  • 3. 驗證測試
    • 3.1 創建兩個表
    • 3.2 創建兩個函數
    • 3.3 編寫存儲過程
    • 3.4 編寫存儲過程
    • 3.5 創建索引
    • 3.6 驗證測試
  • 4. 解決方案
    • 4.1 使用索引覆蓋+子查詢優化
    • 4.2 起始位置重定義
    • 4.3 降級策略
  • 5. 梳理總結

    1. 問題描述

    日常開發中經常會涉及到數據查詢分頁的問題,一般情況下都是根據前端傳入頁數與頁碼通過mysql的limit方式實現分頁,對于數據量較小的情況下沒有問題,但是如果數據量很大,深分頁可能導致查詢效率低下,接口超時的情況。

    2. 問題分析

    其實對于我們的 MySQL 查詢語句來說,整體效率還是可以的,該有的聯表查詢優化都有,該簡略的查詢內容也有,關鍵條件字段和排序字段該有的索引也都在,問題在于他一頁一頁的分頁去查詢,查到越后面的頁數,掃描到的數據越多,也就越慢。

    我們在查看前幾頁的時候,發現速度非常快,比如 limit 200,25,瞬間就出來了。但是越往后,速度就越慢,特別是百萬條之后,卡到不行,那這個是什么原理呢。先看一下我們翻頁翻到后面時,查詢的 sql 是怎樣的:

    select * from t_name where c_name1="xxx" order by c_name2 limit 2000000,25;
    

    這種查詢的慢,其實是因為 limit 后面的偏移量太大導致的。
    比如像上面的 limit 2000000,25,這個等同于數據庫要掃描出 2000025 條數據,然后再丟棄前面的 20000000 條數據,返回剩下 25 條數據給用戶,這種取法明顯不合理。

    3. 驗證測試

    3.1 創建兩個表

    -- 創建兩個表:員工表和部門表
    -- 部門表,存在則進行刪除
    drop table if EXISTS dep;
    create table dep(
    ? ? id int unsigned primary key auto_increment,
    ? ? depno mediumint unsigned not null default 0,
    ? ? depname varchar(20) not null default "",
    ? ? memo varchar(200) not null default ""
    );
    
    -- 員工表,存在則進行刪除
    drop table if EXISTS emp;
    create table emp(
    ? ? id int unsigned primary key auto_increment,
    ? ? empno mediumint unsigned not null default 0,
    ? ? empname varchar(20) not null default "",
    ? ? job varchar(9) not null default "",
    ? ? mgr mediumint unsigned not null default 0,
    ? ? hiredate datetime not null,
    ? ? sal decimal(7,2) not null,
    ? ? comn decimal(7,2) not null,
    ? ? depno mediumint unsigned not null default 0
    );

    注意說明

    • mediumint是MySQL數據庫中的一種整型,比INT小,比SMALLINT大,
    • 取值范圍為:-8388608到8388607,無符號的范圍是0到16777215。
    • 中等大小的整數,一位大小為3個字節。

    3.2 創建兩個函數

    -- 創建兩個函數:生成隨機字符串和隨機編號
    -- 產生隨機字符串的函數
    delimiter $?
    drop FUNCTION if EXISTS rand_string;
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
    ? ? DECLARE chars_str VARCHAR(100) DEFAULT "abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
    ? ? DECLARE return_str VARCHAR(255) DEFAULT "";
    ? ? DECLARE i INT DEFAULT 0;
    ? ? WHILE i < n DO
    ? ? SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
    ? ? SET i = i+1;
    ? ? END WHILE;
    ? ? RETURN return_str;
    END $
    delimiter;
    
    -- 產生隨機部門編號的函數
    delimiter $?
    drop FUNCTION if EXISTS rand_num;
    CREATE FUNCTION rand_num() RETURNS INT(5)
    BEGIN
    ? ? DECLARE i INT DEFAULT 0;
    ? ? SET i = FLOOR(100+RAND()*10);
    ? ? RETURN i;
    END $
    delimiter;
    
    注意說明
    -- 執行函數問題,This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
    -- 這是我們開啟了bin-log, 我們就必須指定我們的函數是否是,DETERMINISTIC 不確定的, NO SQL 沒有SQl語句,當然也不會修改數據
    -- 在MySQL中創建函數時出現這種錯誤的解決方法:set global log_bin_trust_function_creators=TRUE;
    set global log_bin_trust_function_creators=TRUE;

    3.3 編寫存儲過程

    -- 編寫存儲過程,模擬 100W 的員工數據。
    -- 建立存儲過程:往emp表中插入數據
    ?DELIMITER $
    ?drop PROCEDURE if EXISTS insert_emp;
    ?CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
    ?BEGIN
    ? ? ?DECLARE i INT DEFAULT 0;
    ? ? ?/*set autocommit =0 把autocommit設置成0,把默認提交關閉*/
    ? ? ?SET autocommit = 0;
    ? ? ?REPEAT
    ? ? ?SET i = i + 1;
    ? ? ?INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),"SALEMAN",0001,now(),2000,400,rand_num());
    ? ? ?UNTIL i = max_num
    ? ? ?END REPEAT;
    ? ? ?COMMIT;
    ?END $
    ?DELIMITER;
    ?
    -- 插入500W條數據,時間有點久,耐心等待,1409s
    ?call insert_emp(0,5000000);
    
    -- 查詢部門員工表
    select * from emp LIMIT 1,10;

    3.4 編寫存儲過程

    -- 編寫存儲過程,模擬 120 的部門數據
    -- 建立存儲過程:往dep表中插入數據
    ?DELIMITER $
    ?drop PROCEDURE if EXISTS insert_dept;
    ?CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
    ?BEGIN
    ? ? ?DECLARE i INT DEFAULT 0;
    ? ? ?SET autocommit = 0;
    ? ? ?REPEAT
    ? ? ?SET i = i+1;
    ? ? ?INSERT ?INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
    ? ? ?UNTIL i = max_num
    ? ? ?END REPEAT;
    ? ? ?COMMIT;
    ?END $
    ?DELIMITER;
    ?
    -- 插入120條數據
    ?call insert_dept(1,120);
    
    -- 查詢部門員工表
    select * from dep;

    3.5 創建索引

    -- 建立關鍵字段的索引,這邊是跑完數據之后再建索引,會導致建索引耗時長,但是跑數據就會快一些。
    -- 建立關鍵字段的索引:排序、條件
    CREATE INDEX idx_emp_id ON emp(id);
    CREATE INDEX idx_emp_depno ON emp(depno);
    CREATE INDEX idx_dep_depno ON dep(depno); 
    

    3.6 驗證測試

    -- 驗證測試
    -- 偏移量為100,取25,Time: 0.011s
    SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
    
    -- 偏移量為4800000,取25,Time: 10.242s
    SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;

    4. 解決方案

    4.1 使用索引覆蓋+子查詢優化

    因為我們有主鍵 id,并且在上面建了索引,所以可以先在索引樹中找到開始位置的 id 值,再根據找到的 id 值查詢行數據。

    -- 子查詢獲取偏移100條的位置的id,在這個位置上往后取25,Time: 0.04s
    ?SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    ?from emp a left join dep b on a.depno = b.depno
    ?where a.id >= (select id from emp order by id limit 100,1)
    ?order by a.id limit 25;
    
    -- 子查詢獲取偏移4800000條的位置的id,在這個位置上往后取25,Time: 1.549s
    ?SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    ?from emp a left join dep b on a.depno = b.depno
    ?where a.id >= (select id from emp order by id limit 4800000,1)
    ?order by a.id limit 25;

    4.2 起始位置重定義

    記住上次查找結果的主鍵位置,避免使用偏移量 offset。

    這個效率是最好的,無論怎么分頁,耗時基本都是一致的,因為他執行完條件之后,都只掃描了 25 條數據。

    但是有個問題,只適合一頁一頁的分頁,這樣才能記住前一個分頁的最后 id。如果用戶跳著分頁就有問題了,比如剛剛刷完第 25 頁,馬上跳到 35 頁,數據就會不對。這種的適合場景是類似百度搜索或者騰訊新聞那種滾輪往下拉,不斷拉取不斷加載的情況。這種延遲加載會保證數據不會跳躍著獲取。

    -- 記住了上次的分頁的最后一條數據的id是100,這邊就直接跳過100,從101開始掃描表,Time: 0.006s
    ?SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    ?from emp a left join dep b on a.depno = b.depno
    ?where a.id > 100 order by a.id limit 25;
    
    -- 記住了上次的分頁的最后一條數據的id是4800000,這邊就直接跳過4800000,從4800001開始掃描表,Time: 0.046s
    ?SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
    ?from emp a left join dep b on a.depno = b.depno
    ?where a.id > 4800000
    ?order by a.id limit 25;

    4.3 降級策略

    看了網上一個阿里的 DBA 同學分享的方案:配置 limit 的偏移量和獲取數一個最大值,超過這個最大值,就返回空數據。
    因為他覺得超過這個值你已經不是在分頁了,而是在刷數據了,如果確認要找數據,應該輸入合適條件來縮小范圍,而不是一頁一頁分頁。

    5. 梳理總結

    深分頁問題從理論上來說是存在的場景,但是從實際的業務場景考慮,深分頁很多情況下缺少具體的業務場景做支撐,試想哪個業務會從480W頁面,查詢25條數據,如果需要搜索某條數據,使用最多的應該根據條件類型過濾吧。

    每種方案各有優缺點,具體采用那種解決方案需要結合具體的業務場景,如果根據實際業務場景不需要深分頁,可以采用降級策略,設置分頁參數閾值。如果確實需要深分頁問題可以覆蓋子+子查詢優化或者通過偏移量查詢,如果能獲取到偏移量的前提下優先選擇偏移量的方案,否則采用覆蓋索引+子查詢。

    無論是否深分頁都應該考慮限流降級的問題,而且要考慮短時間內重復調用的問題,可以限制每秒執行次數,避免用戶誤點以及調用頻繁帶來的數據安全問題。

    到此這篇關于MySql深分頁問題解決的文章就介紹到這了,更多相關MySql深分頁內容請搜索以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持!

    標簽: MySQL
    相關文章:
    主站蜘蛛池模板: 不锈钢法兰-碳钢法兰-法兰盘生产加工厂家-[鼎捷峰]-不锈钢法兰-碳钢法兰-法兰盘生产加工厂家-[鼎捷峰] | 济南画室培训-美术高考培训-山东艺霖艺术培训画室 | 神超官网_焊接圆锯片_高速钢锯片_硬质合金锯片_浙江神超锯业制造有限公司 | 光栅尺_Magnescale探规_磁栅尺_笔式位移传感器_苏州德美达 | 法兰连接型电磁流量计-蒸汽孔板节流装置流量计-北京凯安达仪器仪表有限公司 | 安徽净化工程设计_无尘净化车间工程_合肥净化实验室_安徽创世环境科技有限公司 | 钢格板|热镀锌钢格板|钢格栅板|钢格栅|格栅板-安平县昊泽丝网制品有限公司 | 无锡网站建设_小程序制作_网站设计公司_无锡网络公司_网站制作 | 智慧水务|智慧供排水利信息化|水厂软硬件系统-上海敢创 | 五轴加工中心_数控加工中心_铝型材加工中心-罗威斯 | 云南外加剂,云南速凝剂,云南外加剂代加工-普洱澜湄新材料科技有限公司 | ◆大型吹塑加工|吹塑加工|吹塑代加工|吹塑加工厂|吹塑设备|滚塑加工|滚塑代加工-莱力奇塑业有限公司 | 无锡网站建设-做网站-建网站-网页设计制作-阿凡达建站公司 | 特种阀门-调节阀门-高温熔盐阀-镍合金截止阀-钛阀门-高温阀门-高性能蝶阀-蒙乃尔合金阀门-福建捷斯特阀门制造有限公司 | 科箭WMS仓库管理软件-TMS物流管理系统-科箭SaaS云服务 | CTAB,表面活性剂1631溴型(十六烷基三甲基溴化铵)-上海升纬化工原料有限公司 | 黑龙江京科脑康医院-哈尔滨精神病医院哪家好_哈尔滨精神科医院排名_黑龙江精神心理病专科医院 | 珠海网站建设_响应网站建设_珠海建站公司_珠海网站设计与制作_珠海网讯互联 | 干粉砂浆设备_干混砂浆生产线_腻子粉加工设备_石膏抹灰砂浆生产成套设备厂家_干粉混合设备_砂子烘干机--郑州铭将机械设备有限公司 | 昊宇水工|河北昊宇水工机械工程有限公司 | 小型气象站_便携式自动气象站_校园气象站-竞道气象设备网 | 苏商学院官网 - 江苏地区唯一一家企业家自办的前瞻型、实操型商学院 | 超高频感应加热设备_高频感应电源厂家_CCD视觉检测设备_振动盘视觉检测设备_深圳雨滴科技-深圳市雨滴科技有限公司 | 温泉机设备|温泉小镇规划设计|碳酸泉设备 - 大连连邦温泉科技 | 北京宣传片拍摄_产品宣传片拍摄_宣传片制作公司-现像传媒 | 山东风淋室_201/304不锈钢风淋室净化设备厂家-盛之源风淋室厂家 翻斗式矿车|固定式矿车|曲轨侧卸式矿车|梭式矿车|矿车配件-山东卓力矿车生产厂家 | 金刚网,金刚网窗纱,不锈钢网,金刚网厂家- 河北萨邦丝网制品有限公司 | 北京晚会活动策划|北京节目录制后期剪辑|北京演播厅出租租赁-北京龙视星光文化传媒有限公司 | 春腾云财 - 为企业提供专业财税咨询、代理记账服务 | 承插管件_不锈钢承插管件_锻钢高压管件-温州科正阀门管件有限公司 | 云杂志网-学术期刊-首页 | PC构件-PC预制构件-构件设计-建筑预制构件-PC构件厂-锦萧新材料科技(浙江)股份有限公司 | 防腐储罐_塑料储罐_PE储罐厂家_淄博富邦滚塑防腐设备科技有限公司 | 广东之窗网 | 郑州宣传片拍摄-TVC广告片拍摄-微电影短视频制作-河南优柿文化传媒有限公司 | 拉力机-拉力试验机-万能试验机-电子拉力机-拉伸试验机-剥离强度试验机-苏州皖仪实验仪器有限公司 | 广东西屋电气有限公司-广东西屋电气有限公司 | 合肥地磅_合肥数控切割机_安徽地磅厂家_合肥世佳电工设备有限公司 | 匀胶机旋涂仪-声扫显微镜-工业水浸超声-安赛斯(北京)科技有限公司 | 钢化玻璃膜|手机钢化膜|钢化膜厂家|手机保护膜-【东莞市大象电子科技有限公司】 | 铜镍-康铜-锰铜-电阻合金-NC003 - 杭州兴宇合金有限公司 |