响应正常的语句:
select count(1) from A
select * from A order by id limit 100
大概在0.1秒就返回了
响应超慢的语句:
select count(1) from A
left join B on A.aa = B.bb
left join C on A.aa = C.cc
select * from A
left join B on A.aa = B.bb
left join C on A.aa = C.cc
order by C.id limit 100
A,B,C表数据在200万已经慢得不行了,少则20多秒,多则数分钟导致超时。
解决方法:
对于count中有join,原因未知,解决方法是加上一项字段较小的且一定会成立的条件
select count(1) from A
left join B on A.aa = B.bb
left join C on A.aa = C.cc
where C.m is not null
对于order by 中有join,慢的原因是order by 后没有使用上索引,解决方法是将order by 的语句放到join的子查询里:
select * from A
left join B on A.aa = B.bb
left join (select cc from C order by C.id ) on A.aa = C.cc
limit 100
参考:
https://blog.csdn.net/chengfei112233/article/details/53412233