推荐一些实用的慢SQL优化方案
前言
慢 SQL 的优化，主要从两个方面考虑，SQL 语句本身的优化，以及数据库设计的优化。
避免不必要的列
SQL 查询的时候，应该只查询需要的列，而不是包含额外的列，像select *这种写法应该尽量避免。
分页优化
在数据量比较大，分页比较深的情况下，需要考虑分页的优化。
select * from tabel where type = 2 and level = 9 order by id asc limit 100000,10;- 延迟关联
先通过where条件提取出主键，在将该表与原数据表关联，通过主键 id 提取数据行，而不是通过原来的二级索引提取数据行
select a.* from table a,(select id from table where type = 2 and level = 9 order by id asc limit 100000,10) bwhere a.id = b.id;
- id 偏移量
偏移量就是找到 limit 第一个参数对应的主键值，根据这个主键值再去过滤并 limit
select * from table where id >(select id from table where type = 2 and level = 9 order by id asc limit 190 );
索引优化
合理的设计和使用索引，是优化慢 SQL 的利器。
- 利用覆盖索引
InnoDB 使用二级索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引，还有一个简单的理解查询列都是索引列。
select b from test where a = "wanna";alter table test add index idx_a_b (a,b);
- 避免使用 or 查询
在 MySQL 5.0之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，高版本引入了索引合并，解决了这个问题，不过建议大家在实际使用中还是规范写法，能不用就少用。 - 避免使用 != 或者 <> 操作符
SQL 中，不等于操作符会导致查询引擎放弃查询索引，引起全表扫描，即时比较的字段上有索引
解决方法：通过把不等于操作符改成 or，可以使用索引，避免全表扫描
id <> 'aaa' ===> id > 'aaa' or id < 'aaa'
- 适当使用前缀索引
适当的使用前缀索引，可以降低索引的控件占用，提高索引的查询效率。
比如，邮箱的后缀都是固定的@xxx.com，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引
alter table test add index dix_emaile_prefix (email(6));
需要注意的是，前缀索引也存在缺点，MySQL 无法利用前缀索引做 order by和group by操作，也无法作为覆盖索引。
- 避免列上函数运算
要避免在列字段上进行算术运算符或其他表达式运算，否则可能会导致存储引擎无法正确的使用索引，从而影响了查询的效率。
select * from test where id + 1 = 50;select * from test where month(updateTime) = 7;
- 正确的使用联合索引
使用联合索引的时候，注意最左匹配原则。
JOIN 的优化
- 优化子查询
尽量使用 join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新建创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大。 - 小表驱动大表
关联查询的时候要拿小表去驱动大表，因为关联的时候，MySQL 内部会遍历驱动表，再去连接被驱动表。
select name from 小表 left join 大表;
- 适当增加冗余字段
增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略。 - 避免使用 JOIN 关联太多表
《阿里巴巴 Java 开发手册》规定不要 join 超过三张表，第一 join 太多降低查询的速度，第二 join 的 buffer 会占用更多的内存。
排序优化
- 利用索引扫描做排序
MySQL 有两种方式生成有序结果：一是对结果集进行排序的操作，而是按照索引顺序扫描得出的结果，索引是排好序的数据结果，自然是有序的。
但是如果索引不能覆盖查询所需列（覆盖索引），就会没扫描一条记录回表查询一次（逐个获取），这个读操作是随机 IO，通常会比顺序全表扫描还慢，有时会直接放弃使用索引转为全表扫描。
因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行。
-- 索引(a,b,c)
select b,c from test where a like 'aa%' order by b,c;
只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序。
UNION 优化
- 条件下推
MySQL 处理 union 的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在 union 查询中都会失效，因为它无法利用索引。
所以需要将where、limit等子句下推到 union 的各个子查询中，以便优化器可以充分利用这些条件进行优化。
此外，除非确实需要服务器去推，一定要试用union all，如果不加all关键字，MySQL 会给临时表加上 distinct选项，这会导致对整个临时表做唯一性检查，代价很高。