人妖在线一区,国产日韩欧美一区二区综合在线,国产啪精品视频网站免费,欧美内射深插日本少妇

新聞動(dòng)態(tài)

詳細(xì)聊聊MySQL中的LIMIT語(yǔ)句

發(fā)布日期:2022-02-05 17:03 | 文章來(lái)源:站長(zhǎng)之家

最近有多個(gè)小伙伴在答疑群里問(wèn)了小孩子關(guān)于LIMIT的一個(gè)問(wèn)題,下邊我來(lái)大致描述一下這個(gè)問(wèn)題。

問(wèn)題

為了故事的順利發(fā)展,我們得先有個(gè)表:

CREATE TABLE t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1)
) Engine=InnoDB CHARSET=utf8;

表t包含3個(gè)列,id列是主鍵,key1列是二級(jí)索引列。表中包含1萬(wàn)條記錄。

當(dāng)我們執(zhí)行下邊這個(gè)語(yǔ)句的時(shí)候,是使用二級(jí)索引idx_key1的:

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_key1 | 303     | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

這個(gè)很好理解,因?yàn)樵诙?jí)索引idx_key1中,key1列是有序的。而查詢是要取按照key1列排序的第1條記錄,那MySQL只需要從idx_key1中獲取到第一條二級(jí)索引記錄,然后直接回表取得完整的記錄即可。

但是如果我們把上邊語(yǔ)句的LIMIT 1換成LIMIT 5000, 1,則卻需要進(jìn)行全表掃描,并進(jìn)行filesort,執(zhí)行計(jì)劃如下:

mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9966 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

有的同學(xué)就很不理解了:LIMIT 5000, 1也可以使用二級(jí)索引idx_key1呀,我們可以先掃描到第5001條二級(jí)索引記錄,對(duì)第5001條二級(jí)索引記錄進(jìn)行回表操作不就好了么,這樣的代價(jià)肯定比全表掃描+filesort強(qiáng)呀。

很遺憾的告訴各位,由于MySQL實(shí)現(xiàn)上的缺陷,不會(huì)出現(xiàn)上述的理想情況,它只會(huì)笨笨的去執(zhí)行全表掃描+filesort,下邊我們嘮叨一下到底是咋回事兒。

server層和存儲(chǔ)引擎層

大家都知道,MySQL內(nèi)部其實(shí)是分為server層和存儲(chǔ)引擎層的:

  • server層負(fù)責(zé)處理一些通用的事情,諸如連接管理、SQL語(yǔ)法解析、分析執(zhí)行計(jì)劃之類的東西
  • 存儲(chǔ)引擎層負(fù)責(zé)具體的數(shù)據(jù)存儲(chǔ),諸如數(shù)據(jù)是存儲(chǔ)到文件上還是內(nèi)存里,具體的存儲(chǔ)格式是什么樣的之類的。我們現(xiàn)在基本都使用InnoDB存儲(chǔ)引擎,其他存儲(chǔ)引擎使用的非常少了,所以我們也就不涉及其他存儲(chǔ)引擎了。

MySQL中一條SQL語(yǔ)句的執(zhí)行是通過(guò)server層和存儲(chǔ)引擎層的多次交互才能得到最終結(jié)果的。比方說(shuō)下邊這個(gè)查詢:

SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';

server層會(huì)分析到上述語(yǔ)句可以使用下邊兩種方案執(zhí)行:

  • 方案一:使用全表掃描
  • 方案二:使用二級(jí)索引idx_key1,此時(shí)需要掃描key1列值在('a', 'b')之間的全部二級(jí)索引記錄,并且每條二級(jí)索引記錄都需要進(jìn)行回表操作。

server層會(huì)分析上述兩個(gè)方案哪個(gè)成本更低,然后選取成本更低的那個(gè)方案作為執(zhí)行計(jì)劃。然后就調(diào)用存儲(chǔ)引擎提供的接口來(lái)真正的執(zhí)行查詢了。

這里假設(shè)采用方案二,也就是使用二級(jí)索引idx_key1執(zhí)行上述查詢。那么server層和存儲(chǔ)引擎層的對(duì)話可以如下所示:

server層:“hey,麻煩去查查idx_key1二級(jí)索引的('a', 'b')區(qū)間的第一條記錄,然后把回表后把完整的記錄返給我哈”

InnoDB:“收到,這就去查”,然后InnoDB就通過(guò)idx_key1二級(jí)索引對(duì)應(yīng)的B+樹,快速定位到掃描區(qū)間('a', 'b')的第一條二級(jí)索引記錄,然后進(jìn)行回表,得到完整的聚簇索引記錄返回給server層。

server層收到完整的聚簇索引記錄后,繼續(xù)判斷common_field!='a'條件是否成立,如果不成立則舍棄該記錄,否則將該記錄發(fā)送到客戶端。然后對(duì)存儲(chǔ)引擎說(shuō):“請(qǐng)把下一條記錄給我哈”

小貼士:

此處將記錄發(fā)送給客戶端其實(shí)是發(fā)送到本地的網(wǎng)絡(luò)緩沖區(qū),緩沖區(qū)大小由net_buffer_length控制,默認(rèn)是16KB大小。等緩沖區(qū)滿了才真正發(fā)送網(wǎng)絡(luò)包到客戶端。

InnoDB:“收到,這就去查”。InnoDB根據(jù)記錄的next_record屬性找到idx_key1的('a', 'b')區(qū)間的下一條二級(jí)索引記錄,然后進(jìn)行回表操作,將得到的完整的聚簇索引記錄返回給server層。

小貼士:
不論是聚簇索引記錄還是二級(jí)索引記錄,都包含一個(gè)稱作next_record的屬性,各個(gè)記錄根據(jù)next_record連成了一個(gè)鏈表,并且鏈表中的記錄是按照鍵值排序的(對(duì)于聚簇索引來(lái)說(shuō),鍵值指的是主鍵的值,對(duì)于二級(jí)索引記錄來(lái)說(shuō),鍵值指的是二級(jí)索引列的值)。

server層收到完整的聚簇索引記錄后,繼續(xù)判斷common_field!='a'條件是否成立,如果不成立則舍棄該記錄,否則將該記錄發(fā)送到客戶端。然后對(duì)存儲(chǔ)引擎說(shuō):“請(qǐng)把下一條記錄給我哈”

... 然后就不停的重復(fù)上述過(guò)程。

直到:

也就是直到InnoDB發(fā)現(xiàn)根據(jù)二級(jí)索引記錄的next_record獲取到的下一條二級(jí)索引記錄不在('a', 'b')區(qū)間中,就跟server層說(shuō):“好了,('a', 'b')區(qū)間沒(méi)有下一條記錄了”

server層收到InnoDB說(shuō)的沒(méi)有下一條記錄的消息,就結(jié)束查詢。

現(xiàn)在大家就知道了server層和存儲(chǔ)引擎層的基本交互過(guò)程了。

那LIMIT是什么鬼?

說(shuō)出來(lái)大家可能有點(diǎn)兒驚訝,MySQL是在server層準(zhǔn)備向客戶端發(fā)送記錄的時(shí)候才會(huì)去處理LIMIT子句中的內(nèi)容。拿下邊這個(gè)語(yǔ)句舉例子:

SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

如果使用idx_key1執(zhí)行上述查詢,那么MySQL會(huì)這樣處理:

  • server層向InnoDB要第1條記錄,InnoDB從idx_key1中獲取到第一條二級(jí)索引記錄,然后進(jìn)行回表操作得到完整的聚簇索引記錄,然后返回給server層。server層準(zhǔn)備將其發(fā)送給客戶端,此時(shí)發(fā)現(xiàn)還有個(gè)LIMIT 5000, 1的要求,意味著符合條件的記錄中的第5001條才可以真正發(fā)送給客戶端,所以在這里先做個(gè)統(tǒng)計(jì),我們假設(shè)server層維護(hù)了一個(gè)稱作limit_count的變量用于統(tǒng)計(jì)已經(jīng)跳過(guò)了多少條記錄,此時(shí)就應(yīng)該將limit_count設(shè)置為1。
  • server層再向InnoDB要下一條記錄,InnoDB再根據(jù)二級(jí)索引記錄的next_record屬性找到下一條二級(jí)索引記錄,再次進(jìn)行回表得到完整的聚簇索引記錄返回給server層。server層在將其發(fā)送給客戶端的時(shí)候發(fā)現(xiàn)limit_count才是1,所以就放棄發(fā)送到客戶端的操作,將limit_count加1,此時(shí)limit_count變?yōu)榱?。
  • ... 重復(fù)上述操作
  • 直到limit_count等于5000的時(shí)候,server層才會(huì)真正的將InnoDB返回的完整聚簇索引記錄發(fā)送給客戶端。

從上述過(guò)程中我們可以看到,由于MySQL中是在實(shí)際向客戶端發(fā)送記錄前才會(huì)去判斷LIMIT子句是否符合要求,所以如果使用二級(jí)索引執(zhí)行上述查詢的話,意味著要進(jìn)行5001次回表操作。server層在進(jìn)行執(zhí)行計(jì)劃分析的時(shí)候會(huì)覺(jué)得執(zhí)行這么多次回表的成本太大了,還不如直接全表掃描+filesort快呢,所以就選擇了后者執(zhí)行查詢。

怎么辦?

由于MySQL實(shí)現(xiàn)LIMIT子句的局限性,在處理諸如LIMIT 5000, 1這樣的語(yǔ)句時(shí)就無(wú)法通過(guò)使用二級(jí)索引來(lái)加快查詢速度了么?其實(shí)也不是,只要把上述語(yǔ)句改寫成:

SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
    WHERE t.id = d.id;

這樣,SELECT id FROM t ORDER BY key1 LIMIT 5000, 1作為一個(gè)子查詢單獨(dú)存在,由于該子查詢的查詢列表只有一個(gè)id列,MySQL可以通過(guò)僅掃描二級(jí)索引idx_key1執(zhí)行該子查詢,然后再根據(jù)子查詢中獲得到的主鍵值去表t中進(jìn)行查找。

這樣就省去了前5000條記錄的回表操作,從而大大提升了查詢效率!

吐個(gè)槽

設(shè)計(jì)MySQL的大叔啥時(shí)候能改改LIMIT子句的這種超笨的實(shí)現(xiàn)呢?還得用戶手動(dòng)想欺騙優(yōu)化器的方案才能提升查詢效率~

到此這篇關(guān)于MySQL中LIMIT語(yǔ)句的文章就介紹到這了,更多相關(guān)MySQL的LIMIT語(yǔ)句內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站!

海外服務(wù)器租用

版權(quán)聲明:本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有,歡迎引用、轉(zhuǎn)載,請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像,否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái),僅供學(xué)習(xí)參考,不代表本站立場(chǎng),如有內(nèi)容涉嫌侵權(quán),請(qǐng)聯(lián)系alex-e#qq.com處理。

相關(guān)文章

實(shí)時(shí)開(kāi)通

自選配置、實(shí)時(shí)開(kāi)通

免備案

全球線路精選!

全天候客戶服務(wù)

7x24全年不間斷在線

專屬顧問(wèn)服務(wù)

1對(duì)1客戶咨詢顧問(wèn)

在線
客服

在線客服:7*24小時(shí)在線

客服
熱線

400-630-3752
7*24小時(shí)客服服務(wù)熱線

關(guān)注
微信

關(guān)注官方微信
頂部