赶着这几天有些时间,把前段时间优化的几条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
这已经是条优化过的SQL,我们观察单次执行的影响:
1. CPU抖动:
2. 执行时间:
单次执行情况貌似也不是严重,但这是爬虫SQL,没有规则可循,我们的长查询告警邮件疯狂抛出
故需要再次对其优化,看下这个优化模型在MySQL里面实际上很经典:ORDER BY desc/asc LIMIT x,y ,x很大
我们认为它无法优化,是因为他是MyISAM,不是InnoDB,InnoDB在二级索引上会自动添加主键,但MyISAM不行。
但我们认为它可以优化,也是因为他是MyISAM,我们可以模拟InnoDB这个行为来优化
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);
优化后的效果:
1 cpu抖动
2 执行时间
具体的优化方法,可以参考我之前的文章: