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

MySQL深分頁,limit 100000,10優(yōu)化方式

 更新時(shí)間:2024年10月11日 09:10:11   作者:尋求出路的程序媛  
MySQL中深分頁查詢因需掃描大量數(shù)據(jù)行導(dǎo)致效率低下,優(yōu)化方法包括子查詢優(yōu)化、延遲關(guān)聯(lián)、標(biāo)簽記錄法和使用between...and...等,通過減少回表次數(shù)和范圍掃描提升查詢性能,覆蓋索引幫助減少搜索次數(shù),提升性能

我們?nèi)粘W龇猪撔枨髸r(shí),一般會用limit實(shí)現(xiàn),但是當(dāng)偏移量特別大的時(shí)候,查詢效率就變得低下。

本文將分4個(gè)方案,討論如何優(yōu)化MySQL百萬數(shù)據(jù)的深分頁問題.

一、limit深分頁為什么會變慢

表結(jié)構(gòu)

CREATE TABLE account (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵Id',
  name varchar(255) DEFAULT NULL COMMENT '賬戶名',
  balance int(11) DEFAULT NULL COMMENT '余額',
  create_time datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
  update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  PRIMARY KEY (id),
  KEY idx_name (name),
  KEY idx_update_time (update_time) //索引
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='賬戶表';

執(zhí)行的深分頁SQL為

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

這個(gè)SQL的執(zhí)行時(shí)間如下:

執(zhí)行完需要0.742秒,深分頁為什么會變慢呢?如果換成 limit 0,10,只需要0.006秒哦

我們先來看下這個(gè)SQL的執(zhí)行流程

  • 通過普通二級索引樹idx_update_time,過濾update_time條件,找到滿足條件的記錄ID。
  • 通過ID,回到主鍵索引樹,找到滿足記錄的行,然后取出展示的列(回表
  • 掃描滿足條件的100010行,然后扔掉前100000行,返回。

(每一條select語句都會從1遍歷至當(dāng)前位置,若跳轉(zhuǎn)到第10000頁,則會遍歷100000條記錄)

執(zhí)行計(jì)劃如下:

SQL變慢原因有兩個(gè)

  1. limit語句會先掃描offset+n行,然后再丟棄掉前offset行,返回后n行數(shù)據(jù)。也就是說limit 100000,10,就會掃描100010行,而limit 0,10,只掃描10行。
  2. limit 100000,10 掃描更多的行數(shù),也意味著回表更多的次數(shù)。

二、優(yōu)化方案

2.1 通過子查詢優(yōu)化(覆蓋索引)

因?yàn)橐陨系腟QL,回表了100010次,實(shí)際上,我們只需要10條數(shù)據(jù),也就是我們只需要10次回表其實(shí)就夠了。因此,我們可以通過減少回表次數(shù)來優(yōu)化。

回顧B+樹結(jié)構(gòu)

如何減少回表次數(shù)呢?我們先來復(fù)習(xí)下B+樹索引結(jié)構(gòu)

InnoDB中,索引分主鍵索引(聚簇索引)和二級索引

  • 主鍵索引,葉子節(jié)點(diǎn)存放的是整行數(shù)據(jù)
  • 二級索引,葉子節(jié)點(diǎn)存放的是主鍵的值。

覆蓋索引

覆蓋索引(covering index ,或稱為索引覆蓋)即從非主鍵索引中就能查到的記錄,而不需要查詢主鍵索引中的記錄,避免了回表的產(chǎn)生減少了樹的搜索次數(shù),顯著提升性能。

如何確定數(shù)據(jù)庫成功使用了覆蓋索引呢? —— 當(dāng)發(fā)起一個(gè)索引覆蓋查詢時(shí),在explain的extra列可以看到using index的信息

可以看到Extra中的Using index,表明我們成功使用了覆蓋索引

把條件轉(zhuǎn)移到主鍵索引樹

如果我們把查詢條件,轉(zhuǎn)移回到主鍵索引樹,那就不就可以減少回表次數(shù)啦。轉(zhuǎn)移到主鍵索引樹查詢的話,查詢條件得改為主鍵id了,之前SQL的update_time這些條件咋辦呢?抽到子查詢那里嘛~

子查詢那里怎么抽的呢?因?yàn)槎壦饕~子節(jié)點(diǎn)是有主鍵ID的,所以我們直接根據(jù)update_time來查主鍵ID即可,同時(shí)我們把 limit 100000的條件,也轉(zhuǎn)移到子查詢,完整SQL如下:

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10; -- (可以加下時(shí)間條件到外面的主查詢)

查詢效果一樣的,執(zhí)行時(shí)間只需要0.038秒! 0.742秒 ——> 0.038秒

我們來看下執(zhí)行計(jì)劃

由執(zhí)行計(jì)劃得知,子查詢 table a查詢是用到了idx_update_time索引。

首先在索引上拿到了聚集索引的主鍵ID,省去了回表操作,然后第二查詢直接根據(jù)第一個(gè)查詢的ID往后再去查10個(gè)就可以了!

所謂的覆蓋索引就是從普通索引樹中就能查到的想要數(shù)據(jù),而不需要通過回表從主鍵索引中查詢其他列,能夠顯著提升性能。

因此,這個(gè)方案是可以的~

2.2 INNER JOIN 延遲關(guān)聯(lián)

延遲關(guān)聯(lián)的優(yōu)化思路,跟子查詢的優(yōu)化思路其實(shí)是一樣的:都是把條件轉(zhuǎn)移到主鍵索引樹,然后減少回表。不同點(diǎn)是,延遲關(guān)聯(lián)使用了inner join代替子查詢。

優(yōu)化后的SQL如下:

SELECT  acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.update_time >= '2020-09-19' ORDER BY a.update_time LIMIT 100000, 10) AS  acct2 on acct1.id= acct2.id;

查詢效果也是杠桿的,只需要0.034秒

執(zhí)行計(jì)劃如下:

查詢思路就是,先通過idx_update_time二級索引樹查詢到滿足條件的主鍵ID,再與原表通過主鍵ID內(nèi)連接,這樣后面直接走了主鍵索引了,同時(shí)也減少了回表。

2.3 標(biāo)簽記錄法(要求id是有序的)

limit 深分頁問題的本質(zhì)原因就是:偏移量(offset)越大,mysql就會掃描越多的行,然后再拋棄掉。這樣就導(dǎo)致查詢性能的下降。

其實(shí)我們可以采用標(biāo)簽記錄法,就是標(biāo)記一下上次查詢到哪一條了,下次再來查的時(shí)候,從該條開始往下掃描。就好像看書一樣,上次看到哪里了,你就折疊一下或者夾個(gè)書簽,下次來看的時(shí)候,直接就翻到啦

select id,name,balance from account limit 1000000,10;

假設(shè)上一次記錄到100000,則SQL可以優(yōu)化為:

select id,name,balance FROM account where id > 100000 order by id limit 10;

這樣的話,后面無論翻多少頁,性能都會不錯的,因?yàn)槊辛?code>id索引。但是你,這種方式有局限性:要求id連續(xù)的、并且有序。

在有序的條件下,也可以使用比如創(chuàng)建時(shí)間等其他字段來代替主鍵id,但是前提是這個(gè)字段是建立了索引的。

id不是連續(xù),我們可以通過order by讓它連續(xù)

總之,使用條件過濾的方式來優(yōu)化 limit 是有諸多限制的,一般還是推薦使用覆蓋索引的方式來優(yōu)化。

2.4 使用between…and…

很多時(shí)候,可以將limit查詢轉(zhuǎn)換為已知位置的查詢,這樣MySQL通過范圍掃描between...and,就能獲得到對應(yīng)的結(jié)果。

select id,name,balance from account limit 1000000,10;

如果知道邊界值為100000,100010后,就可以這樣優(yōu)化:

select id,name,balance FROM account where id between 100000 and 100010 order by id desc;

總結(jié)

以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。

相關(guān)文章

  • Mac環(huán)境mysql5.7.21 utf8編碼問題及解決方案

    Mac環(huán)境mysql5.7.21 utf8編碼問題及解決方案

    本篇教程給大家簡單介紹下Mac環(huán)境mysql5.7.21 utf8編碼問題及解決方案,非常不錯,具有參考借鑒價(jià)值,需要的朋友參考下吧
    2018-03-03
  • MySQL分表自增ID問題的解決方法

    MySQL分表自增ID問題的解決方法

    這篇文章主要為大家詳細(xì)介紹了MySQL分表自增ID問題的解決方法,感興趣的小伙伴們可以參考一下
    2016-06-06
  • MySQL壓力測試工具M(jìn)ysqlslap的使用

    MySQL壓力測試工具M(jìn)ysqlslap的使用

    這篇文章主要介紹了MySQL官方壓力測試工具 Mysqlslap的使用方法,幫助大家更好的理解和使用MySQL,感興趣的朋友可以了解下
    2020-12-12
  • Mysql中SUM()函數(shù)使用方法

    Mysql中SUM()函數(shù)使用方法

    這篇文章主要給大家介紹了關(guān)于Mysql中SUM()函數(shù)使用的相關(guān)資料,MySQL 的 SUM 函數(shù)可以用來對某個(gè)列進(jìn)行求和,但是如果你想要按照某個(gè)條件進(jìn)行求和,可以使用帶有WHERE子句的SUM函數(shù),需要的朋友可以參考下
    2023-08-08
  • Mysql如何查詢某條記錄在分頁的第幾頁詳析

    Mysql如何查詢某條記錄在分頁的第幾頁詳析

    查詢是我們?nèi)粘9ぷ髦薪?jīng)常會遇到的一個(gè)功能,下面這篇文章主要給大家介紹了關(guān)于Mysql如何查詢某條記錄在分頁的第幾頁的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下
    2018-11-11
  • MySQL中TIMESTAMP類型返回日期時(shí)間數(shù)據(jù)中帶有T的解決

    MySQL中TIMESTAMP類型返回日期時(shí)間數(shù)據(jù)中帶有T的解決

    這篇文章主要介紹了MySQL中TIMESTAMP類型返回日期時(shí)間數(shù)據(jù)中帶有T的解決方案,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教
    2022-12-12
  • mysql備份表的幾種方法總結(jié)

    mysql備份表的幾種方法總結(jié)

    這篇文章主要介紹了mysql的備份表的幾種方法總結(jié),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2024-03-03
  • mysql 8.0.22 安裝配置方法圖文教程

    mysql 8.0.22 安裝配置方法圖文教程

    這篇文章主要為大家詳細(xì)介紹了mysql 8.0.22 安裝配置方法圖文教程,文中安裝步驟介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2020-10-10
  • MySQL 5.7 版本的安裝及簡單使用(圖文教程)

    MySQL 5.7 版本的安裝及簡單使用(圖文教程)

    這篇文章主要介紹了MySQL 5.7 版本的安裝及簡單使用(圖文教程)的相關(guān)資料,這里對mysql 5.7的安裝及使用和注意事項(xiàng),需要的朋友可以參考下
    2016-12-12
  • MySQL中閃回功能的方案討論及實(shí)現(xiàn)

    MySQL中閃回功能的方案討論及實(shí)現(xiàn)

    Oracle有一個(gè)閃回(flashback)功能,能夠用戶恢復(fù)誤操作的數(shù)據(jù),這篇文章主要來和大家討論一下MySQL中支持閃回功能的方案,有需要的可以了解下
    2025-03-03

最新評論