亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

MySQL分頁(yè)優(yōu)化

 更新時(shí)間：2016年04月20日 14:47:44   作者：iVictor  
這篇文章主要為大家詳細(xì)介紹了MySQL分頁(yè)優(yōu)化，內(nèi)容思路很詳細(xì)，有意對(duì)MySQL分頁(yè)優(yōu)化的朋友可以參考一下

最近，幫同事重寫(xiě)了一個(gè)MySQL SQL語(yǔ)句，該SQL語(yǔ)句涉及兩張表，其中一張表是字典表（需返回一個(gè)字段），另一張表是業(yè)務(wù)表（本身就有150個(gè)字段，需全部返回），當(dāng)然，字段的個(gè)數(shù)是否合理在這里不予評(píng)價(jià)。平時(shí)，返回的數(shù)據(jù)大概5w左右，系統(tǒng)尚能收到數(shù)據(jù)。但12月31日那天，數(shù)據(jù)量大概20w，導(dǎo)致SQL執(zhí)行時(shí)間過(guò)長(zhǎng)，未能在規(guī)定的時(shí)間內(nèi)反饋結(jié)果，于是系統(tǒng)直接報(bào)錯(cuò)。

一般的思路是用MySQL的分頁(yè)功能，即直接在原SQL語(yǔ)句后面增加LIMIT子句。但請(qǐng)注意，雖然你看到的反饋結(jié)果只是LIMIT后面指定的數(shù)量，于是想當(dāng)然的以為MySQL只是檢索了指定數(shù)量的數(shù)據(jù)，然后給予返回。其實(shí)，MySQL內(nèi)部實(shí)現(xiàn)的原理是，檢索所有符合where條件的記錄，然后返回指定數(shù)量的記錄。從這個(gè)角度來(lái)看，直接在原SQL語(yǔ)句后面添加LIMIT子句只能說(shuō)是一種可以實(shí)現(xiàn)功能的方案，但未必最優(yōu)。

具體在本例中，首先我們來(lái)看一下150個(gè)字段的表的統(tǒng)計(jì)信息：

一行大概就占2k，而Innodb默認(rèn)頁(yè)的大小為16k，這意味著，一個(gè)頁(yè)中最多可存儲(chǔ)8行的數(shù)據(jù)。隨機(jī)讀的可能性大大增加。而這無(wú)疑會(huì)對(duì)數(shù)據(jù)庫(kù)系統(tǒng)的IO造成極大的壓力。 

優(yōu)化前

如果采用上述方案，即直接在原SQL語(yǔ)句后面增加LIMIT子句，下面，我們來(lái)看看它的執(zhí)行情況。

首先，直接添加LIMIT子句后的SQL語(yǔ)句如下（已省略a1表的150個(gè)字段和a2中的一個(gè)字段）：

復(fù)制代碼 代碼如下:
FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) WHERE STATUS<>'00' AND settle_date=20151230 limit 50000,10000;

其執(zhí)行時(shí)間如下：

大概執(zhí)行了32s，絕大部分都花費(fèi)到Sending data上了。Sending data指的是服務(wù)器檢索數(shù)據(jù)，讀取數(shù)據(jù)，并將數(shù)據(jù)返回給客戶端的時(shí)間。

關(guān)于上述執(zhí)行結(jié)果，有以下幾點(diǎn)需要說(shuō)明：

1. 這是SQL語(yǔ)句多次執(zhí)行后的結(jié)果，這樣就可以排除結(jié)果緩存的影響，事實(shí)上，每次查詢的時(shí)長(zhǎng)都是32s左右。

2. 為什么選用的是limit 50000,10000，而不是0，10000，這個(gè)主要是考慮到對(duì)于LIMIT子句來(lái)說(shuō)，越到后面，分頁(yè)的成本越高。基于此，選擇了中間值來(lái)作為分頁(yè)的結(jié)果。

該語(yǔ)句的執(zhí)行計(jì)劃如下：

優(yōu)化后：

優(yōu)化的思路：

只對(duì)該表的主鍵進(jìn)行分頁(yè)，然后用返回的主鍵作為子查詢的結(jié)果，來(lái)檢索該表其它字段的值。

改寫(xiě)后的SQL語(yǔ)句如下：

復(fù)制代碼 代碼如下:
FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id from (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000) as t);

其執(zhí)行時(shí)間如下：

大概3s多，比第一種方案快了差不多10倍，效果顯著。

下面來(lái)看看其執(zhí)行計(jì)劃（explain extended）

 總結(jié)：

1. 改寫(xiě)后的語(yǔ)句原本如下：

復(fù)制代碼 代碼如下:
FROM upay_csys_scquery_txn_log_his a1  LEFT JOIN upay_csys_trans_code a2 on(a1.int_trans_code=a2.trans_code) where seq_id in (select seq_id FROM upay_csys_scquery_txn_log_his a1  WHERE STATUS<>'00' AND settle_date=20151230 order by 1 limit 50000,10000);

但MySQL報(bào)以下錯(cuò)誤：

復(fù)制代碼 代碼如下:
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

需再增加一個(gè)嵌套子查詢，

比如這樣的語(yǔ)句是不能正確執(zhí)行的。

復(fù)制代碼 代碼如下:
select * from table where id in (select id from table limit 12);

但是，只要你再加一層就行。如：

復(fù)制代碼 代碼如下:
select * from table where id in (select t.id from (select * from table limit 12)as t)

這樣就可以繞開(kāi)limit子查詢的問(wèn)題。
問(wèn)題解決。

2. 如果想查看MySQL查詢優(yōu)化器等價(jià)改寫(xiě)后的SQL語(yǔ)句，可首先通過(guò)explain extended得到具體的執(zhí)行計(jì)劃，然后通過(guò)show warnings查看。

具體在本例中，等價(jià)改寫(xiě)后的SQL語(yǔ)句如下：

與設(shè)想中的執(zhí)行順序一致~

3. 如何查看MySQL語(yǔ)句各步驟的執(zhí)行時(shí)間。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家MySQL分頁(yè)優(yōu)化有所幫助。

相關(guān)文章

  • MySQL中文亂碼問(wèn)題的解決

    MySQL中文亂碼問(wèn)題的解決

    MySQL中文亂碼問(wèn)題的解決...
    2006-12-12
  • 通過(guò)實(shí)例判斷mysql update是否會(huì)鎖表

    通過(guò)實(shí)例判斷mysql update是否會(huì)鎖表

    這篇文章主要介紹了通過(guò)實(shí)例判斷mysql update是否會(huì)鎖表,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下
    2020-09-09
  • mysql InnoDB建表時(shí)設(shè)定初始大小的方法

    mysql InnoDB建表時(shí)設(shè)定初始大小的方法

    這篇文章主要介紹了mysql InnoDB建表時(shí)設(shè)定初始大小的方法，需要大家到MYSQL后臺(tái)實(shí)際操作方可以看到效果
    2013-11-11
  • MySQL存儲(chǔ)過(guò)程中變量的定義以及應(yīng)用詳解

    MySQL存儲(chǔ)過(guò)程中變量的定義以及應(yīng)用詳解

    MySQL變量定義和應(yīng)用是我們經(jīng)常會(huì)遇到的問(wèn)題,下面這篇文章主要給大家介紹了關(guān)于MySQL存儲(chǔ)過(guò)程中變量的定義以及應(yīng)用的相關(guān)資料,文章通過(guò)圖文介紹的非常詳細(xì),需要的朋友可以參考下
    2023-06-06
  • mysql表操作-約束刪除、用戶填加、授權(quán)和撤權(quán)方式

    mysql表操作-約束刪除、用戶填加、授權(quán)和撤權(quán)方式

    本文詳細(xì)介紹了數(shù)據(jù)庫(kù)的約束刪除、密碼策略設(shè)置、用戶管理以及權(quán)限控制的具體操作步驟,包括查看與修改表的約束條件、設(shè)置數(shù)據(jù)庫(kù)密碼的策略、增加用戶以及用戶權(quán)限的授權(quán)與撤銷(xiāo)等,這些操作對(duì)于數(shù)據(jù)庫(kù)管理員來(lái)說(shuō)是基本且必須掌握的技能
    2024-09-09
  • Mysql中isnull,ifnull,nullif的用法及語(yǔ)義詳解

    Mysql中isnull,ifnull,nullif的用法及語(yǔ)義詳解

    MySQL中ISNULL判斷表達(dá)式是否為NULL,IFNULL替換NULL值為指定值,NULLIF在表達(dá)式相等時(shí)返回NULL,用于空值處理、條件判斷及避免錯(cuò)誤,本文給大家介紹Mysql中isnull,ifnull,nullif的用法及語(yǔ)義,感興趣的朋友一起看看吧
    2025-06-06
  • MySQL Range Columns分區(qū)的使用

    MySQL Range Columns分區(qū)的使用

    Range Columns分區(qū)是一種靈活的分區(qū)策略,允許基于列值的范圍將數(shù)據(jù)分到不同的分區(qū),本文主要介紹了MySQL Range Columns分區(qū)的使用,具有一定的參考價(jià)值,感興趣的可以了解一下
    2024-07-07
  • MySQL跨服務(wù)器關(guān)聯(lián)查詢的實(shí)現(xiàn)

    MySQL跨服務(wù)器關(guān)聯(lián)查詢的實(shí)現(xiàn)

    本文主要介紹了MySQL跨服務(wù)器關(guān)聯(lián)查詢的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2023-01-01
  • mysql實(shí)現(xiàn)列轉(zhuǎn)行和行轉(zhuǎn)列方式

    mysql實(shí)現(xiàn)列轉(zhuǎn)行和行轉(zhuǎn)列方式

    這篇文章主要介紹了mysql實(shí)現(xiàn)列轉(zhuǎn)行和行轉(zhuǎn)列方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教
    2025-08-08
  • MySQL數(shù)據(jù)庫(kù)高級(jí)查詢和多表查詢

    MySQL數(shù)據(jù)庫(kù)高級(jí)查詢和多表查詢

    這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)高級(jí)查詢和多表查詢，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2020-08-08

最新評(píng)論