MySQLSQL優化：SQL爬蟲翻頁優化

趕著這幾天有些時間，把前段時間優化的幾條SQL經驗分享並總結下，以飨來者。第一個要分享的是對MyISAM優化limit分頁。背景來自公司某個業務系統提供給爬蟲抓取數據。基礎信息：MySQL版本是5.1，引擎為MyISAM，原始SQL內容大致如下：注：為避免敏感信息，將很多字段變為col，但不影響閱讀 :-)
SELECT Aa.* , B.col, B.col, C.col, C.col FROM (SELECT A.col, A.col, A.col, A.col , A.col, A.col, A.col, A.col, A.col, A.col, A.col , A.col FROM A WHERE A.class1id = 1000000 AND 1 ORDER BY A.oktime DESC LIMIT 286660,20) Aa LEFT JOIN B ON Aa.class2id=B.id LEFT JOIN C ON Aa.username=C.username

SELECT Aa.* , B.col, B.col, C.col, C.col FROM (SELECT A.col, A.col, A.col, A.col , A.col, A.col, A.col, A.col, A.col, A.col, A.col , A.col FROM A INNER JOIN (SELECT askid FROM solve_answerinfo use INDEX (idx_1) WHERE class1id = 1000000 ORDER BY oktime DESC LIMIT 389300,20) aaa USING (askid)) Aa LEFT B ON Aa.class2id=B.id LEFT JOIN C ON Aa.username=C.username;

create index idx_1 on solve_answerinfo (oktime,askid,class1id);