在程序上线运行一段时间后，一旦数据量上去了，或多或少会感觉到系统出现延迟、卡顿等现象，出现这种问题，就需要程序员或架构师进行系统调优工作了。

其中，大量的实践经验表明，调优的手段尽管有很多，但涉及到SQL调优的内容仍然是非常重要的一环，本文将结合实例，总结一些工作中可能涉及到的SQL优化策略。

可以说，对于大多数系统来说，读多写少一定是常态，这就表示涉及到查询的SQL是非常高频的操作。

前置准备：给一张测试表添加10万条数据。

使用下面的存储过程给单表造一批数据，将表换成自己的就好了。

create procedure addMyData()
  begin
    declare num int;    set num =1;    while num <= 100000 do      insert into XXX_table values(        replace(uuid(),'-',''),concat('测试',num),concat('cs',num),'123456'      );
      set num =num +1;    end while;
  end ;

然后调用该存储过程：

call addMyData();

本篇准备了3张表，分别为学生（student）表，班级（class）表，账户(account)表，各自有50万，1万和10万条数据用于测试。

分页查询是开发中经常会遇到的，有一种情况是，当分页的数量非常大的时候，查询的时候往往非常耗时，比如查询student表，使用下面的sql查询，耗时达到0.2秒。

实践经验告诉我们，越往后，分页查询效率越低，这就是分页查询的问题所在， 因为，当在进行分页查询时，如果执行 limit 400000,10 ，此时需要 MySQL 排序前4000 10 记录，仅仅返回400000 - 4 00010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

1.在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

SELECT * FROM student t1,(SELECT id FROM student ORDER BY id LIMIT 400000,10) t2 WHERE t1.id =t2.id;

执行上面的sql，可以看到响应时间有一定的提升。

2.对于主键自增的表，可以把Limit 查询转换成某个位置的查询

select * from student where id > 400000 limit 10;

执行上面的sql，可以看到响应时间有一定的提升。

在实际的业务开发过程中，关联查询可以说随处可见，关联查询的优化核心思路是，最好为关联查询的字段添加索引，这是关键。具体到不同的场景，还需要具体分析，这个跟mysql的引擎在执行优化策略的方案选择时有一定关系。

下面是一个使用left join 的查询，可以预想到这条sql查询的结果集非常大。

select t.* from student t left join class cs on t.classId = cs.id;

为了检查下sql的执行效率，使用explain做一下分析，可以看到，第一张表即left join左边的表student走了全表扫描，而class表走了主键索引，尽管结果集较大，还是走了索引。

针对这种场景的查询，思路如下：

关于左连接（右连接）的explain结果补充说明：

看下面的这条sql，其关联字段非表的主键，而是普通的字段。

explain select u.* from tenant t left join `user` u on u.account = t.tenant_name where t.removed is null and u.removed is null;

通过explain分析可以发现，左边的表走了全表扫描，可以考虑给左边的表的tenant_name和user表的account 各自创建索引。

create index idx_name on tenant(tenant_name);create index idx_account on `user`(account);

再次使用explain分析结果如下：

可以看到第二行type变为ref，rows的数量优化比较明显。这是由左连接特性决定的，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。

我们知道，左连接和右连接查询的数据分别是完全包含左表数据，完全包含右表数据，而内连接（inner join 或join） 则是取交集（共有的部分），在这种情况下，驱动表的选择是由mysql优化器自动选择的。

在上面的基础上，首先移除两张表的索引：

ALTER TABLE `user` DROP INDEX idx_account;ALTER TABLE `tenant` DROP INDEX idx_name;

使用explain语句进行分析：

然后给user表的account字段添加索引，再次执行explain我们发现，user表竟然被当作是被驱动表了。

此时，如果我们给tenant表的tenant_name加索引，并移除user表的account索引，得出的结果竟然都没有走索引，再次说明，使用内连接的情况下，查询优化器将会根据自己的判断进行选择。

子查询在日常编写业务的SQL时也是使用非常频繁的做法，不是说子查询不能用，而是当数据量超出一定的范围之后，子查询的性能下降是很明显的，关于这一点，本人在日常工作中深有体会。

比如下面这条sql，由于student表数据量较大，执行起来耗时非常长，可以看到耗费了将近3秒。

select st.* from student st where st.classId in (    select id from class where id > 100 );

通过执行explain进行分析得知，内层查询 id > 100的子查询尽管用上了主键索引，但是由于结果集太大，带入到外层查询，即作为in的条件时，查询优化器还是走了全表扫描。

针对上面的情况，可以考虑下面的优化方式：

select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;

使用mysql查询时，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询要快 ，如果查询中使用索引的话，性能就会更好，尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

在下面的这段sql中，优化前使用的是子查询，在一次生产问题的性能分析中，发现某个tenant_id下的数据达到了35万多，这样直接导致某个列表页面的接口查询耗时达到了5秒左右。

找到了问题的根源后，尝试使用上面的优化思路进行解决即可，优化后的sql大概如下：

在MySQL，排序主要有两种方式：

对于以上两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

由于age字段未加索引，查询结果按照age排序的时候发现使用了filesort，排序性能较低。

给age字段添加索引，再次使用order by时就走了索引。

通常在实际业务中，参与排序的字段往往不只一个，这时候，就可以对参与排序的多个字段创建联合索引。

如下根据stuno和age排序：

给stuno和age添加联合索引：

create index idx_stuno_age on `student`(stuno,age);

再次分析时结果如下，此时排序走了索引：

1）关于多字段排序时的注意事项

• 排序时，需要满足最左前缀法则，否则也会出现 filesort

在上面我们创建的联合索引顺序是stuno和age，即stuno在前面，而age在后，如果查询的时候调换排序顺序会怎样呢？通过分析结果发现，走了filesort。

• 排序时，排序的类型保持一致

在保持字段排序顺序不变时，默认情况下，如果都按照升序或者降序时，order by可以使用index，如果一个是升序，另一个是降序会如何呢？分析发现，这种情况下也会走filesort。

group by 的优化策略和order by 的优化策略非常像，主要列举如下几个要点：

如果字段未加索引，分析结果如下，这种结果性能显然很低效。

给stuno添加索引之后：

给stuno和age添加联合索引：

如果不遵循最佳左前缀，group by 性能将会比较低效。

 遵循最佳左前缀的情况如下：

count() 是一个聚合函数，对于返回的结果集，一行行判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

用法：count（*）、count（主键）、count（字段）、count（数字）

如下列举了count的几种写法的详细说明：

经验值总结：

按照效率排序来看，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)