https://blog.csdn.net/long690276759/article/details/82586845
1. 直接用limit start, count分頁(yè)語(yǔ)句， 也是我程序中用的方法：
select * from product limit start, count
當(dāng)起始頁(yè)較小時(shí)，查詢沒(méi)有性能問(wèn)題，我們分別看下從10， 100， 1000， 10000開(kāi)始分頁(yè)的執(zhí)行時(shí)間（每頁(yè)取20條）， 如下：
select * from product limit 10, 20 0.016秒
select * from product limit 100, 20 0.016秒
select * from product limit 1000, 20 0.047秒
select * from product limit 10000, 20 0.094秒
我們已經(jīng)看出隨著起始記錄的增加，時(shí)間也隨著增大， 這說(shuō)明分頁(yè)語(yǔ)句limit跟起始頁(yè)碼是有很大關(guān)系的，那么我們把起始記錄改為40w看下（也就是記錄的一般左右） select * from product limit 400000, 20 3.229秒
再看我們?nèi)∽詈笠豁?yè)記錄的時(shí)間
select * from product limit 866613, 20 37.44秒
難怪搜索引擎抓取我們頁(yè)面的時(shí)候經(jīng)常會(huì)報(bào)超時(shí)，像這種分頁(yè)最大的頁(yè)碼頁(yè)顯然這種時(shí)
間是無(wú)法忍受的。
從中我們也能總結(jié)出兩件事情：
1）limit語(yǔ)句的查詢時(shí)間與起始記錄的位置成正比
2）mysql的limit語(yǔ)句是很方便，但是對(duì)記錄很多的表并不適合直接使用。
2. 對(duì)limit分頁(yè)問(wèn)題的性能優(yōu)化方法
利用表的覆蓋索引來(lái)加速分頁(yè)查詢
我們都知道，利用了索引查詢的語(yǔ)句中如果只包含了那個(gè)索引列（覆蓋索引），那么這種情況會(huì)查詢很快。
因?yàn)槔盟饕檎矣袃?yōu)化算法，且數(shù)據(jù)就在查詢索引上面，不用再去找相關(guān)的數(shù)據(jù)地址了，這樣節(jié)省了很多時(shí)間。另外Mysql中也有相關(guān)的索引緩存，在并發(fā)高的時(shí)候利用緩存就效果更好了。
在我們的例子中，我們知道id字段是主鍵，自然就包含了默認(rèn)的主鍵索引?，F(xiàn)在讓我們看看利用覆蓋索引的查詢效果如何：
這次我們之間查詢最后一頁(yè)的數(shù)據(jù)（利用覆蓋索引，只包含id列），如下：
select id from product limit 866613, 20 0.2秒
相對(duì)于查詢了所有列的37.44秒，提升了大概100多倍的速度
那么如果我們也要查詢所有列，有兩種方法，一種是id>=的形式，另一種就是利用join，看下實(shí)際情況：
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查詢時(shí)間為0.2秒，簡(jiǎn)直是一個(gè)質(zhì)的飛躍啊，哈哈
另一種寫法
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id
查詢時(shí)間也很短，贊！
其實(shí)兩者用的都是一個(gè)原理嘛，所以效果也差不多
Mysql的分頁(yè)查詢十分簡(jiǎn)單，但是當(dāng)數(shù)據(jù)量大的時(shí)候一般的分頁(yè)就吃不消了。
傳統(tǒng)分頁(yè)查詢：SELECT c1,c2,cn… FROM table LIMIT n,m
MySQL的limit工作原理就是先讀取前面n條記錄，然后拋棄前n條，讀后面m條想要的，所以n越大，偏移量越大，性能就越差。
推薦分頁(yè)查詢方法：
1、盡量給出查詢的大致范圍
- SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;
2、子查詢法
- SELECT c1,c2,cn... FROM table WHERE id>=
- (
- SELECT id FROM table LIMIT 20000,1
- )
- LIMIT 10;
3、高性能MySQL一書中提到的只讀索引方法
優(yōu)化前SQL:
- SELECT c1,c2,cn... FROM member ORDER BY last_active LIMIT 50,5
優(yōu)化后SQL:
- SELECT c1, c2, cn .. .
- FROM member
- INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5)
- USING (member_id)
分別在于，優(yōu)化前的SQL需要更多I/O浪費(fèi)，因?yàn)橄茸x索引，再讀數(shù)據(jù)，然后拋棄無(wú)需的行。而優(yōu)化后的SQL(子查詢那條)只讀索引(Cover index)就可以了，然后通過(guò)member_id讀取需要的列。
4、第一步用用程序讀取出ID，然后再用IN方法讀取所需記錄
程序讀ID：
- SELECT id FROM table LIMIT 20000, 10;
- SELECT c1, c2, cn .. . FROM table WHERE id IN (id1, id2, idn.. .)
==============
MySQL的limit用法和分頁(yè)查詢的性能分析及優(yōu)化
一、limit用法
在我們使用查詢語(yǔ)句的時(shí)候，經(jīng)常要返回前幾條或者中間某幾行數(shù)據(jù)，這個(gè)時(shí)候怎么辦呢？不用擔(dān)心，mysql已經(jīng)為我們提供了這樣一個(gè)功能。
SELECT * FROM table LIMIT [offset,] rows |
`rows OFFSET offset ` (LIMIT offset, `length`)SELECT*FROM tablewhere condition1 = 0and condition2 = 0
and condition3 = -1and condition4 = -1order by id ascLIMIT 2000 OFFSET 50000
LIMIT 子句可以被用于強(qiáng)制 SELECT 語(yǔ)句返回指定的記錄數(shù)。LIMIT 接受一個(gè)或兩個(gè)數(shù)字參數(shù)。參數(shù)必須是一個(gè)整數(shù)常量。如果給定兩個(gè)參數(shù)，第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目。初始記錄行的偏移量是 0(而不是 1)： 為了與 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
mysql> SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15
//為了檢索從某一個(gè)偏移量到記錄集的結(jié)束所有的記錄行，可以指定第二個(gè)參數(shù)為 -1：
mysql> SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last.
//如果只給定一個(gè)參數(shù)，它表示返回最大的記錄行數(shù)目：
mysql> SELECT * FROM table LIMIT 5; //檢索前 5 個(gè)記錄行
//換句話說(shuō)，LIMIT n 等價(jià)于 LIMIT 0,n。
二、Mysql的分頁(yè)查詢語(yǔ)句的性能分析
MySql分頁(yè)sql語(yǔ)句，如果和MSSQL的TOP語(yǔ)法相比，那么MySQL的LIMIT語(yǔ)法要顯得優(yōu)雅了許多。使用它來(lái)分頁(yè)是再自然不過(guò)的事情了。
最基本的分頁(yè)方式：
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ...
在中小數(shù)據(jù)量的情況下，這樣的SQL足夠用了，唯一需要注意的問(wèn)題就是確保使用了索引：
舉例來(lái)說(shuō)，如果實(shí)際SQL類似下面語(yǔ)句，那么在category_id, id兩列上建立復(fù)合索引比較好：
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10
子查詢的分頁(yè)方式：
隨著數(shù)據(jù)量的增加，頁(yè)數(shù)會(huì)越來(lái)越多，查看后幾頁(yè)的SQL就可能類似：
SELECT * FROM articles WHERE category_id = 123
ORDER BY id LIMIT 10000, 10
一言以蔽之，就是越往后分頁(yè)，LIMIT語(yǔ)句的偏移量就會(huì)越大，速度也會(huì)明顯變慢。
此時(shí)，我們可以通過(guò)子查詢的方式來(lái)提高分頁(yè)效率，大致如下：
SELECT * FROM articles WHERE id >= (SELECT id FROM articles
WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10
JOIN分頁(yè)方式
SELECT * FROM `content` AS t1 JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;
經(jīng)過(guò)我的測(cè)試，join分頁(yè)和子查詢分頁(yè)的效率基本在一個(gè)等級(jí)上，消耗的時(shí)間也基本一致。
explain SQL語(yǔ)句：
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index
為什么會(huì)這樣呢？因?yàn)樽硬樵兪窃谒饕贤瓿傻模胀ǖ牟樵儠r(shí)在數(shù)據(jù)文件上完成的，通常來(lái)說(shuō)，索引文件要比數(shù)據(jù)文件小得多，所以操作起來(lái)也會(huì)更有效率。
實(shí)際可以利用類似策略模式的方式去處理分頁(yè)，比如判斷如果是一百頁(yè)以內(nèi)，就使用最基本的分頁(yè)方式，大于一百頁(yè)，則使用子查詢的分頁(yè)方式。
總結(jié)
三、對(duì)于有大數(shù)據(jù)量的mysql表來(lái)說(shuō)，使用LIMIT分頁(yè)存在很嚴(yán)重的性能問(wèn)題。
查詢從第1000000之后的30條記錄：
SQL代碼1：平均用時(shí)6.6秒 SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30
SQL代碼2：平均用時(shí)0.6秒 SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30
因?yàn)橐?strong>取出所有字段內(nèi)容，第一種需要跨越大量數(shù)據(jù)塊并取出，而第二種基本通過(guò)直接根據(jù)索引字段定位后，才取出相應(yīng)內(nèi)容，效率自然大大提升。對(duì)limit的優(yōu)化，不是直接使用limit，而是首先獲取到offset的id，然后直接使用limit size來(lái)獲取數(shù)據(jù)。
可以看出，越往后分頁(yè)，LIMIT語(yǔ)句的偏移量就會(huì)越大，兩者速度差距也會(huì)越明顯。
實(shí)際應(yīng)用中，可以利用類似策略模式的方式去處理分頁(yè)，比如判斷如果是一百頁(yè)以內(nèi)，就使用最基本的分頁(yè)方式，大于一百頁(yè)，則使用子查詢的分頁(yè)方式。
優(yōu)化思想：避免數(shù)據(jù)量大時(shí)掃描過(guò)多的記錄
