在项目压测阶段(多张业务主表数量达亿级),很多人都会碰到平时明明秒级响应的请求,现在却经常得到的响应是504 gateway timeout…这是因为在大数据的前提下,暴露了很多慢SQL,现在我就项目中遇到的一些情况以及对应的优化方案进行简要分享。
首先定位到查询语句,可以通过工具,如pinpoint可以很方便找到慢sql,也可以自己凭业务逻辑定位到查询sql,然后用 EXPLAIN 你的 SELECT 查询。
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。
type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。key_len列,索引长度。
rows列,扫描行数。该值是个预估值。
extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。
举个栗子
如果发现type类型为all的,或者扫描的行数很大的情况,就需要注意一下了。所谓sql优化就是尽可能减少扫描的行数,行数少了,自然就快了。
进入正题之前,先对比熟悉几个常用关键字
exists和in
select * from A
where id in(select id from B)
以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.
它的查询过程类似于以下过程
List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.
如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历100001000000次,效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000100次,遍历次数大大减少,效率大大提升.
select a.* from A a
where exists(select 1 from B b where a.id=b.id)
以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是