mysql優(yōu)化limit查詢語(yǔ)句的5個(gè)方法
mysql的分頁(yè)比較簡(jiǎn)單，只需要limit offset,length就可以獲取數(shù)據(jù)了，但是當(dāng)offset和length比較大的時(shí)候，mysql明顯性能下降
1.子查詢優(yōu)化法
先找出第一條數(shù)據(jù)，然后大于等于這條數(shù)據(jù)的id就是要獲取的數(shù)據(jù)
缺點(diǎn)：數(shù)據(jù)必須是連續(xù)的，可以說(shuō)不能有where條件，where條件會(huì)篩選數(shù)據(jù)，導(dǎo)致數(shù)據(jù)失去連續(xù)性，具體方法請(qǐng)看下面的查詢實(shí)例：
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from Member;
+----------+
| count(*) |
+----------+
| 169566 |
+----------+
1 row in set (0.00 sec)
mysql> pager grep !~-
PAGER set to 'grep !~-'
mysql> select * from Member limit 10, 100;
100 rows in set (0.00 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 1000, 100;
100 rows in set (0.01 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100;
100 rows in set (0.00 sec)
mysql> select * from Member limit 100000, 100;
100 rows in set (0.10 sec)
mysql> select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100;
100 rows in set (0.02 sec)
mysql> nopager
PAGER set to stdout
mysql> show profiles\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00003300
Query: select count(*) from Member
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00167000
Query: select * from Member limit 10, 100
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00112400
Query: select * from Member where MemberID >= (select MemberID from Member limit 10,1) limit 100
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00263200
Query: select * from Member limit 1000, 100
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00134000
Query: select * from Member where MemberID >= (select MemberID from Member limit 1000,1) limit 100
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.09956700
Query: select * from Member limit 100000, 100
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.02447700
Query: select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100
從結(jié)果中可以得知，當(dāng)偏移1000以上使用子查詢法可以有效的提高性能。
2.倒排表優(yōu)化法
倒排表法類(lèi)似建立索引，用一張表來(lái)維護(hù)頁(yè)數(shù)，然后通過(guò)高效的連接得到數(shù)據(jù)
缺點(diǎn)：只適合數(shù)據(jù)數(shù)固定的情況，數(shù)據(jù)不能刪除，維護(hù)頁(yè)表困難
倒排表介紹：（而倒排索引具稱(chēng)是搜索引擎的算法基石）
倒排表是指存放在內(nèi)存中的能夠追加倒排記錄的倒排索引。倒排表是迷你的倒排索引。
臨時(shí)倒排文件是指存放在磁盤(pán)中，以文件的形式存儲(chǔ)的不能夠追加倒排記錄的倒排索引。臨時(shí)倒排文件是中等規(guī)模的倒排索引。
最終倒排文件是指由存放在磁盤(pán)中，以文件的形式存儲(chǔ)的臨時(shí)倒排文件歸并得到的倒排索引。最終倒排文件是較大規(guī)模的倒排索引。
倒排索引作為抽象概念，而倒排表、臨時(shí)倒排文件、最終倒排文件是倒排索引的三種不同的表現(xiàn)形式。
3.反向查找優(yōu)化法
當(dāng)偏移超過(guò)一半記錄數(shù)的時(shí)候，先用排序，這樣偏移就反轉(zhuǎn)了
缺點(diǎn)：order by優(yōu)化比較麻煩，要增加索引，索引影響數(shù)據(jù)的修改效率，并且要知道總記錄數(shù) ，偏移大于數(shù)據(jù)的一半
limit偏移算法：
正向查找： (當(dāng)前頁(yè) - 1) * 頁(yè)長(zhǎng)度
反向查找： 總記錄 - 當(dāng)前頁(yè) * 頁(yè)長(zhǎng)度
做下實(shí)驗(yàn)，看看性能如何
總記錄數(shù)：1,628,775
每頁(yè)記錄數(shù)： 40
總頁(yè)數(shù)：1,628,775 / 40 = 40720
中間頁(yè)數(shù)：40720 / 2 = 20360
第21000頁(yè)
正向查找SQL:
時(shí)間：1.8696 秒
反向查找sql:
時(shí)間：1.8336 秒
第30000頁(yè)
正向查找SQL:
時(shí)間：2.6493 秒
反向查找sql:
時(shí)間：1.0035 秒
注意，反向查找的結(jié)果是是降序desc的，并且InputDate是記錄的插入時(shí)間，也可以用主鍵聯(lián)合索引，但是不方便。
4.limit限制優(yōu)化法
把limit偏移量限制低于某個(gè)數(shù)。。超過(guò)這個(gè)數(shù)等于沒(méi)數(shù)據(jù)，我記得alibaba的dba說(shuō)過(guò)他們是這樣做的
5.只查索引法
MySQL的limit工作原理就是先讀取n條記錄，然后拋棄前n條，讀m條想要的，所以n越大，性能會(huì)越差。
優(yōu)化前SQL:
優(yōu)化后SQL:
區(qū)別在于，優(yōu)化前的SQL需要更多I/O浪費(fèi)，因?yàn)橄茸x索引，再讀數(shù)據(jù)，然后拋棄無(wú)需的行。而優(yōu)化后的SQL(子查詢那條)只讀索引(Cover index)就可以了，然后通過(guò)member_id讀取需要的列。
總結(jié)：limit的優(yōu)化限制都比較多，所以實(shí)際情況用或者不用只能具體情況具體分析了。頁(yè)數(shù)那么后，基本很少人看的。。。
相關(guān)文章
這篇文章主要介紹了Centos7 下Mysql5.7.19安裝教程詳解,小編認(rèn)為非常不錯(cuò)，特此分享到腳本之家平臺(tái)，需要的朋友參考下吧2017-09-09
MySQL存儲(chǔ)引擎InnoDB與Myisam的區(qū)別分析
INNODB會(huì)支持一些關(guān)系數(shù)據(jù)庫(kù)的高級(jí)功能，如事務(wù)功能和行級(jí)鎖，MYISAM不支持。MYISAM的性能更優(yōu)，占用的存儲(chǔ)空間少。所以，選擇何種存儲(chǔ)引擎，視具體應(yīng)用而定。2022-12-12
win10下mysql 8.0.12 安裝及環(huán)境變量配置教程
這篇文章主要為大家詳細(xì)介紹了MySQL8.0的安裝、配置、啟動(dòng)服務(wù)和登錄及配置環(huán)境變量，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2019-03-03
Jmeter連接數(shù)據(jù)庫(kù)過(guò)程圖解
這篇文章主要介紹了jmeter連接數(shù)據(jù)庫(kù)過(guò)程圖解,文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-10-10
這篇文章主要介紹了MySQL select查詢之LIKE與通配符用法,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-12-12
MySQL用作備份還原的導(dǎo)入和導(dǎo)出命令用法整理
這篇文章主要介紹了MySQL用作備份還原的導(dǎo)入和導(dǎo)出命令用法整理,包括mysqldump的命令的使用以及l(fā)oad data相關(guān)命令,需要的朋友可以參考下2015-12-12
這篇文章主要給大家介紹了關(guān)于MySQL聚合查詢與聯(lián)合查詢操作的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2022-02-02
MySQL InnoDB引擎ibdata文件損壞/刪除后使用frm和ibd文件恢復(fù)數(shù)據(jù)
mysql的ibdata文件被誤刪、被惡意修改,沒(méi)有從庫(kù)和備份數(shù)據(jù)的情況下的數(shù)據(jù)恢復(fù),不能保證數(shù)據(jù)庫(kù)所有表數(shù)據(jù)的100%恢復(fù),目的是盡可能多的恢復(fù),下面是具體的操作方法2025-03-03
Mysql中DATEDIFF函數(shù)的基礎(chǔ)語(yǔ)法及練習(xí)案例
Datediff函數(shù),最大的作用就是計(jì)算日期差,能計(jì)算兩個(gè)格式相同的日期之間的差值,下面這篇文章主要給大家介紹了關(guān)于Mysql中DATEDIFF函數(shù)的基礎(chǔ)語(yǔ)法及練習(xí)案例?的相關(guān)資料,需要的朋友可以參考下2022-09-09
這篇文章主要為大家詳細(xì)介紹了mysql 8.0.12安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-09-09