MySQLSQL优化：SQL爬虫翻页优化_MySQL
程序员文章站
2022-04-16 15:51:41
...
赶着这几天有些时间，把前段时间优化的几条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 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);