第49讲：SQL优化之GROUP BY分组查询的优化

using temperary用到了临时表这个性能是比较低的。不满足最左前缀法则的联合索引，在分组的时候的性能分析。根据专业进行分组，并统计个数。创建索引后再执行sql。

在插入50的时候，50应该在47之后，但是页空间不足，此时innoDB会创建一个空的页，然后把前一页50%之后的数据移动到新创建的页，然后再插入50这条记录，最后调整指针的指向。插入结束后：因此我们在插入数据时顺序插入就不会产生页分裂，性能也就比较高。

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：A. 在分组操作时，可以通过索引来提高效率。B. 分组操作时，索引的使用也是满足最左前缀法则的。

因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。group by 就是分组统计的意思，一般情况都是配合聚合函数如（count(),sum(),avg(),max(),min())一起使用。当然，平时大家使用的时候，group by还是配合聚合函数使用的，除非一些特殊场景，比如你想去重，当然去重用distinct也是可以的。如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。

原因是因为对于分组操作， 在联合索引中，也是符合最左前缀法则的。然后，我们在针对于 profession ， age， status 创建一个联合索引。，原因就是因为profession出现了，满足最左前缀法则，所以出现了。但如果只根据age来分组，不满足最左前缀法则，因此出现了临时表。可以看见，用到了索引，并且type由ALL优化为了index，B. 分组操作时，索引的使用也是满足最左前缀法则的。分组操作，我们主要来看看索引对于分组操作的影响。可以看见，ALL全表扫描，并没有用到任何索引，

本文借助一个案例，来了解 not in 对 NULL 值敏感的处理逻辑，并探讨相应的优化方法。

一、join优化Join查找操作的基本原则：应该将条目少的表/子查询放在Join操作符的左边。原因是在Join操作的Reduce阶段，位于Join操作符左边的表的内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出错误的几率。Join查找操作中如果存在多个join，且所有参与join的表中其参与join的key都相同，则会将所有的join合并到一个mapred程序中。案例：SELEC...

3、输出结果解读，详细解读输出结果，理解每个字段的含义，如估计成本(sqx_estcost)、估计行数(sqx_estrows)、会话ID(sqx_sessionid)和SQL语句本身(sqx_sqlstatement)。1、SQL查询基础，通过特定的SQL查询，可以快速定位到成本较高且可能执行缓慢的SQL语句。2、分析查询输出结果，对查询结果进行分析，识别出成本最高且行数估计较多的SQL语句。3、检查会话信息，跟据上一步输出，检查会话信息及执行的sql。2、查看线程会话，根据上一步输出，检查线程信息。

优化group by 语句 由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ： drop index idx_emp_age_salar

SQL语句： select t.type, count(t.id) as todo_count from mc_job_form t where t.state = '2' and t.customs_code = :a group by t.type 表结构: ID VARCHAR2(24) N   id IN_OUT_FLAG CHAR(1) Y    PROVIDER_ID NUM...

• 索引的任何其它部分（除了那些来自查询中引用的GROUP BY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX() 函数的参数例外。• GROUP BY包括索引的第1个连续部分(如果对于GROUP BY，查询有一个DISTINCT子句，则所有DISTINCT的属性指向索引开头)。同时，临时表的使用是否可以用索引访问代替，还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。• 如果使用聚集函数，只能使用MIN()和MAX()，并且它们均指向相同的列。

本文介绍了SQL优化的一些方法，图文并茂，全面细致，适合数据库开发者和面试者学习和参考。

MySQL高级-SQL优化- group by 优化（通过索引来提高效率）

这是一个常见又头疼的问题，有很庞大的一堆数据，要查看第200万条数据后的10条数据，此时需要MySQL排序前2000010条数据，从中返回第2000000~2000010的记录，其他记录丢弃，查询排序的代价非常大。子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是个常用的优化手段。

为GROUP BY使用索引的最重要的先决条件是，所有GROUP BY列引用来自同一索引的属性，并且索引按顺序存储其键(例如，对于BTREE索引是如此，但对于HASH索引则不是如此)。对于索引中的列，必须索引完整的列值，而不仅仅是一个前缀。例如，对于c1 VARCHAR(20)， INDEX (c1(10))，索引仅使用c1值的前缀，不能用于松散索引扫描。除了查询中引用的GROUP BY之外，索引的任何其他部分都必须是常量(也就是说，它们必须在使用常量的等式中被引用)，除了MIN()或MAX()函数的参数。

首先 group by 用到索引，那就在索引树上索引数据，但是因为加了 where 条件，还是需要在去表里检索几乎所有的数据， 这样子，还不如直接去表里进行全表扫，这样还更快些。就比如上面这个 sql，如果存在这个商品的名称被修改过了，但是它们的 id 确还是一样的，那么这个时候展示的商品名称是修改前的还是修改后的呢?注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。因为上面也说了，临时表也分为内存临时表和磁盘临时表。