MySQL分頁優化。本站提示廣大學習愛好者：（MySQL分頁優化）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL分頁優化正文
比來，幫同事重寫了一個MySQL SQL語句，該SQL語句觸及兩張表，個中一張表是字典表（需前往一個字段），另外一張表是營業表（自己就有150個字段，需全體前往），固然，字段的個數能否公道在這裡不予評價。日常平凡，前往的數據年夜概5w閣下，體系尚能收到數據。但12月31日那天，數據量年夜概20w，招致SQL履行時光太長，未能在劃定的時光內反應成果，因而體系直接報錯。
普通的思緒是用MySQL的分頁功效，即直接在原SQL語句前面增長LIMIT子句。但請留意，固然你看到的反應成果只是LIMIT前面指定的數目，因而想固然的認為MySQL只是檢索了指定命量的數據，然後賜與前往。其實，MySQL外部完成的道理是，檢索一切相符where前提的記載，然後前往指定命量的記載。從這個角度來看，直接在原SQL語句前面添加LIMIT子句只能說是一種可以完成功效的計劃，但未必最優。
詳細在本例中，起首我們來看一下150個字段的表的統計信息：
一行年夜概就占2k，而Innodb默許頁的年夜小為16k，這意味著，一個頁中最多可存儲8行的數據。隨機讀的能夠性年夜年夜增長。而這無疑會對數據庫體系的IO形成極年夜的壓力。
優化前
假如采取上述計劃，即直接在原SQL語句前面增長LIMIT子句，上面，我們來看看它的履行情形。
起首，直接添加LIMIT子句後的SQL語句以下（已省略a1表的150個字段和a2中的一個字段）：
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;
其履行時光以下：
年夜概履行了32s，絕年夜部門都消費到Sending data上了。Sending data指的是辦事器檢索數據，讀取數據，並將數據前往給客戶真個時光。
關於上述履行成果，有以下幾點須要解釋：
1. 這是SQL語句屢次履行後的成果，如許便可以消除成果緩存的影響，現實上，每次查詢的時長都是32s閣下。
2. 為何選用的是limit 50000,10000，而不是0，10000，這個重要是斟酌到關於LIMIT子句來講，越到前面，分頁的本錢越高。基於此，選擇了中央值來作為分頁的成果。
該語句的履行籌劃以下：
優化後：
優化的思緒：
只對該表的主鍵停止分頁，然後用前往的主鍵作為子查詢的成果，來檢索該表其它字段的值。
改寫後的SQL語句以下：
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);
其履行時光以下：
年夜概3s多，比第一種計劃快了差不多10倍，後果明顯。
上面來看看其履行籌劃（explain extended）
總結：
1. 改寫後的語句本來以下：
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報以下毛病：
ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
需再增長一個嵌套子查詢，
好比如許的語句是不克不及准確履行的。
select * from table where id in (select id from table limit 12);
然則，只需你再加一層就行。如：
select * from table where id in (select t.id from (select * from table limit 12)as t)
如許便可以繞開limit子查詢的成績。
成績處理。
2. 假如想檢查MySQL查詢優化器等價改寫後的SQL語句，可起首經由過程explain extended獲得詳細的履行籌劃，然後經由過程show warnings檢查。
詳細在本例中，等價改寫後的SQL語句以下：
與假想中的履行次序分歧~
3. 若何檢查MySQL語句各步調的履行時光。
以上就是本文的全體內容，願望對年夜家MySQL分頁優化有所贊助。
