SQL语句的优化

本文介绍一些sql语句优化的一些知识和经验。IO永远是数据库最容易瓶颈的地方，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑。除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当IO 优化做到一定阶段之后，降低 CPU 计算也就成为了重要目标。

一.in 型子查询引出的陷阱
先举个例子，如果某商城系统，要查询id为6的大类商品下的所有小类商品。思路一般是先查出大类下所有小类，然后查商品表中商品类型是这些小类的所有商品。
直观上会先想到子查询：select goods_id,cat_id,goods_name from  goods where cat_id in (select cat_id from category where parent_id=6);
这条语句给我们的感觉是, 先查到内层的6号栏目的子栏目,如7,8,9,11 ，然后外层, cat_id in (7,8,9,11) 。
可事实是 goods表全扫描, 并逐行与category表对照, 看parent_id=6是否成立。In型子查询,会被查询优化器被改成了exists的执行效果.当goods表越大时, 查询速度越慢。如下图所示。

如果我们在程序中分两次查询，先查出大类下所有小类，select cat_id from category where parent_id=6 。
然后再根据小类查询商品，select goods_id,cat_id,goods_name from  goods where cat_id in (7,8,9,11) 。
这样goods表就会走cat_id列的索引了，不会再全表扫描了。
如果要用一条语句执行，可以改为连接查询，
select g.goods_id,g.cat_id,g.goods_name from  goods as g inner join (select cat_id from ecs_category where parent_id=6) as t  on g.cat_id = t.cat_id;
内层的查询 select cat_id from ecs_category where parent_id=6 ; 用到Parent_id索引, 返回7,8,9,11 。
t和 goods 通过 cat_id 相连, 因为cat_id在 goods表中有索引, 所以相当于用7,8,911,快速匹配上 goods的行。

二.group by 语句
group by 语句语句要注意几点：
1.group by用于统计,而不用于筛选数据。比如: 适合统计平均分、最高分, 但用于筛选重复数据，则不适合。
2.在group by时, 用带有索引的列来group, 速度会稍快一些,另外,分组列用int型比char型，也要快一些。
3.两表连接查询，如果只查询A表的列, 那么 group by 、order by 尽量用A表的列,而且列应该显示声明为A的列。

三.子查询
有时需要使用子查询，在有些业务场景，适合使用子查询，使用子查询也未必慢。
注意：如果需要使用子查询的结果，内层from语句查到的临时表, 是没有索引的。所以from的返回内容要尽量少。

四.union优化
union all不过滤重复数据，效率比union高很多, 因为union去重的代价非常高，如非必须，请用union all。去重可以放在程序里去做。

五.大数据量分页优化
用limit offset进行分页时，当offset非常大时, 效率极低，原因是数据库并不是跳过offset行，然后单取N行，而是取offset+N行，然后放弃前offset行返回N行。效率很低，当offset越大时，效率越低。
当数据量很大时，翻页越靠后，性能越差。对这种业务场景进行SQL优化，应先从业务需求角度考量。从业务上分析，一般情况下，向后翻太多页其实业务意义不大，比如搜索引擎，一般也就翻几十页。因此可以从业务上限制，比如最多只能翻100页。如果必须要查，则可以先只查索引，不查数据，得到id。然后再用id去关联查具体条目，这种技巧就是延迟索引。
例如：select id,name,age from user inner join (select id from user limit 5000000,10)
这种方式，本质就是利用索引覆盖提高效率，但是limit offset得查询方式并未本质改变，只能尽可能提高速度，并不能根治。

此外，如果是在多个表进行关联分页数据查询的时候，我最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。

六.避免产生临时表
应该尽量避免产生临时表，当然也得看具体场景，不能一概而论，这里只是说一般原则。以下情况会产生临时表。
1.group by 的列没有索引，肯定产生临时表。因为分组统计要先按分组字段有序排列，才能进行聚合运算。
2.order by 与group by为不同列时，或者表连接查询时,取A表的内容，order by 、group by 包含B表的列。
3.distinct 和 order by 一起使用时，可能会产生临时表。

以上这些只是一些技巧的总结，其实sql优化的核心和重点还是在索引。