SQL优化经验

在项目压测阶段(多张业务主表数量达亿级)，很多人都会碰到平时明明秒级响应的请求，现在却经常得到的响应是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语