PHP中分頁肯定會使用到MySQL的limit,大部分對類似"select * from title where uid =** order by id desc limit m,n"很熟悉,也不是全部都能看出裡面有什麼不對,可是當是在大數據量下操作呢,比如百萬類似"select * from title where uid =177 order by id desc limit 1234567,20"就會發現sql執行的時間明顯變得很長,為什麼呢?
先從MySQL的limit原理說起,使用limit m,n是時候,MySQL先掃描(m+n)條記錄,然後從m行開始取n行.比如上面的例子就是先掃描1234587條數據,這樣的話sql能快嗎?這就要求我們盡可能的減少m的值,甚至沒有m直接limit n這樣是sql.
看個例子:
MySQL> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 limit 888888,10; | id | substr(mobile from 1 for 7) | time | cpid | linkid | | 11535090 | 1353554 | 2010-02-24 21:07:48 | 769 | 21064905903309587933 |
| 11535091 | 1353750 | 2010-02-24 21:07:48 | 769 | 21064912943389480033 |
| 11535093 | 1353394 | 2010-02-24 21:07:48 | 769 | 21064912945389480075 |
| 11535098 | 1343073 | 2010-02-24 21:07:50 | 769 | 21064905865309587977 |
| 11535100 | 1369270 | 2010-02-24 21:07:51 | 769 | 21064926770369210194 |
| 11535103 | 1355683 | 2010-02-24 21:07:51 | 769 | 21064912944389480113 |
| 11535104 | 1368959 | 2010-02-24 21:07:51 | 769 | 21064902508384448468 |
| 11535105 | 1365243 | 2010-02-24 21:07:51 | 769 | 21064905907309403124 |
| 11535106 | 1362145 | 2010-02-24 21:07:52 | 769 | 21065002511384448497 |
| 11535107 | 1369228 | 2010-02-24 21:07:52 | 769 | 21064902514384448437 | 10 rows in set (3.84 sec)
MySQL> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 and id>=11535090 limit 10; | id | substr(mobile from 1 for 7) | time | cpid | linkid | | 11535090 | 1353554 | 2010-02-24 21:07:48 | 769 | 21064905903309587933 |
| 11535091 | 1353750 | 2010-02-24 21:07:48 | 769 | 21064912943389480033 |
| 11535093 | 1353394 | 2010-02-24 21:07:48 | 769 | 21064912945389480075 |
| 11535098 | 1343073 | 2010-02-24 21:07:50 | 769 | 21064905865309587977 |
| 11535100 | 1369270 | 2010-02-24 21:07:51 | 769 | 21064926770369210194 |
| 11535103 | 1355683 | 2010-02-24 21:07:51 | 769 | 21064912944389480113 |
| 11535104 | 1368959 | 2010-02-24 21:07:51 | 769 | 21064902508384448468 |
| 11535105 | 1365243 | 2010-02-24 21:07:51 | 769 | 21064905907309403124 |
| 11535106 | 1362145 | 2010-02-24 21:07:52 | 769 | 21065002511384448497 |
| 11535107 | 1369228 | 2010-02-24 21:07:52 | 769 | 21064902514384448437 | 10 rows in set (0.00 sec)
MySQL> select id,substr(mobile from 1 for 7),time,cpid,linkid from cp_mo100227 where cpid=769 and time>='2010-02-24 21:07:48' limit 10; | id | substr(mobile from 1 for 7) | time | cpid | linkid | | 11535090 | 1353554 | 2010-02-24 21:07:48 | 769 | 21064905903309587933 |
| 11535091 | 1353750 | 2010-02-24 21:07:48 | 769 | 21064912943389480033 |
| 11535093 | 1353394 | 2010-02-24 21:07:48 | 769 | 21064912945389480075 |
| 11535098 | 1343073 | 2010-02-24 21:07:50 | 769 | 21064905865309587977 |
| 11535100 | 1369270 | 2010-02-24 21:07:51 | 769 | 21064926770369210194 |
| 11535103 | 1355683 | 2010-02-24 21:07:51 | 769 | 21064912944389480113 |
| 11535104 | 1368959 | 2010-02-24 21:07:51 | 769 | 21064902508384448468 |
| 11535105 | 1365243 | 2010-02-24 21:07:51 | 769 | 21064905907309403124 |
| 11535106 | 1362145 | 2010-02-24 21:07:52 | 769 | 21065002511384448497 |
| 11535107 | 1369228 | 2010-02-24 21:07:52 | 769 | 21064902514384448437 | 10 rows in set (0.01 sec)
例中數據表id是主鍵,time也建了索引,表中總數據約為240w行,其中cpid為769的數據量大約為90w條.這裡面的id和時間可能會是不連續的.故不能直接得獲取id>m這樣操作
所以可以顯示 “1，2，3，4，5,末頁” 或是 “首頁，<<100,101,102,103 >>末頁”這樣,這樣可以極大的減少m值!
MySQL裡面的join順便說一句就是,通常有點講究的是用小表去驅動大表,而由於MySQL join實現的原理就是做循環比如left join就是對左邊的數據進行循環去驅動右邊的表,比如左邊是可能會有m條記錄匹配,右邊有n條記錄那麼就是做m次循環,每次掃描n行數據,總掃面行數是m*n行數據.左邊返回的結果集的大小就決定了循環的次數,故單純的用小表去驅動大表不一定的正確的,小表的結果集可能也大於大表的結果集,所以寫join的時候盡可能的先估計兩張表的可能結果集,用小結果集去驅動大結果集.值得注意的是在使用left/right join的時候,從表的條件應寫在on之後,主表應寫在where之後.否則MySQL會當作普通的連表查詢!
MYSQL的優化是非常重要的。其他最常用也最需要優化的就是limit。MySQL的limit給分頁帶來了極大的方便，但數據量一大的時候，limit的性能就急劇下降。
同樣是取10條數據
select * from posts limit 10000,10 和
select * from posts limit 0,10
就不是一個數量級別的。
網上也很多關於limit的五條優化准則，都是翻譯自MySQL手冊，雖然正確但不實用。今天發現一篇文章寫了些關於limit優化的，很不錯。
文中不是直接使用limit，而是首先獲取到offset的id然後直接使用limit size來獲取數據。根據他的數據，明顯要好於直接使用limit。這裡我具體使用數據分兩種情況進行測試。（測試環境win2033+p4雙核 (3GHZ) +4G內存 MySQL 5.0.19）
1、offset比較小的時候。
select * from posts limit 10,10
多次運行，時間保持在0.0004-0.0005之間
Select * From posts Where id >=(
Select id From posts Order By id limit 10,1
) limit 10
多次運行，時間保持在0.0005-0.0006之間，主要是0.0006
結論：偏移offset較小的時候，直接使用limit較優。這個顯然是子查詢的原因。
2、offset大的時候。
select * from posts limit 10000,10
多次運行，時間保持在0.0187左右
Select * From posts Where id >=(
Select id From posts Order By id limit 10000,1
) limit 10
多次運行，時間保持在0.0061左右，只有前者的1/3。可以預計offset越大，後者越優。
以後要注意改正自己的limit語句，優化一下MySQL了
