精华内容
下载资源
问答
  • MySQL查询优化GROUP BY

    万次阅读 多人点赞 2019-03-05 19:46:16
    group by 优化方法 — 索引 松散索引扫描(Loose Index Scan) 为什么松散索引扫描的效率会很高? 紧凑索引扫描(Tight Index Scan) group by 优化方法 — 直接排序 二、group by 与 distinct 三、排序不一致...

    目录

    一、group by

    group by 优化方法 — 索引

    松散索引扫描(Loose Index Scan)

    为什么松散索引扫描的效率会很高?

    紧凑索引扫描(Tight Index Scan)

    group by 优化方法 — 直接排序

    二、group by 与 distinct

    三、排序不一致问题


    一、group by

    当我们执行 group by 操作在没有合适的索引可用的时候,通常先扫描整个表提取数据并创建一个临时表,然后按照 group by 指定的列进行排序。在这个临时表里面,对于每一个 group 的数据行来说是连续在一起的。完成排序之后,就可以发现所有的 groups,并可以执行聚集函数(aggregate function)。可以看到,在没有使用索引的时候,需要创建临时表和排序。在执行计划中通常可以看到“Using temporary; Using filesort”。

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    create table t1(id int primary key, a int, b int, index(a));

    delimiter ;;

    create procedure idata()

    begin

      declare i int;

     

      set i=1;

      while(i<=1000)do

        insert into t1 values(i, i, i);

        set i=i+1;

      end while;

    end;;

    delimiter ;

    call idata();

    一个常见的使用临时表的例子是 group by,我们来看一下这个语句:

    1

    select id%10 as m, count(*) as c from t1 group by m;

    这个语句的逻辑是把表 t1 里的数据,按照 id%10 进行分组统计,并按照 m 的结果排序后输出。它的 explain 结果如下:

     

    1

    2

    3

    4

    5

    6

    7

    mysql> explain select id%10 as m, count(*) as c from t1 group by m;

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+

    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+

    |  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a     | a    | 5       | NULL | 1000 |   100.00 | Using index; Using temporary; Using filesort |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------+

    1 row in set, 1 warning (0.01 sec)

    在 Extra 字段里面,我们可以看到三个信息:

    • Using index,表示这个语句使用了覆盖索引,选择了索引 a,不需要回表;
    • Using temporary,表示使用了临时表;
    • Using filesort,表示需要排序。

    这个语句的执行流程是这样的:

    1. 创建内存临时表,表里有两个字段 m 和 c,主键是 m;

    2. 扫描表 t1 的索引 a,依次取出叶子节点上的 id 值,计算 id%10 的结果,记为 x;

    3. 如果临时表中没有主键为 x 的行,就插入一个记录 (x,1); 如果表中有主键为 x 的行,就将 x 这一行的 c 值加 1;

    4. 遍历完成后,再根据字段 m 做排序,得到结果集返回给客户端。

    这个流程的流程图如下:

     

    接下来,我们再看一下这条语句的执行结果:

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    mysql> select id%10 as m, count(*) as c from t1 group by m;

    +------+-----+

    | m    | c   |

    +------+-----+

    |    0 | 100 |

    |    1 | 100 |

    |    2 | 100 |

    |    3 | 100 |

    |    4 | 100 |

    |    5 | 100 |

    |    6 | 100 |

    |    7 | 100 |

    |    8 | 100 |

    |    9 | 100 |

    +------+-----+

    10 rows in set (0.01 sec)

    如果你的需求并不需要对结果进行排序,那你可以在 SQL 语句末尾增加 order by null,也就是改成:

     

    1

    select id%10 as m, count(*) as c from t1 group by m order by null;

    这样就跳过了最后排序的阶段,直接从临时表中取数据返回。返回结果如下:

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    mysql> select id%10 as m, count(*) as c from t1 group by m order by null;

    +------+-----+

    | m    | c   |

    +------+-----+

    |    1 | 100 |

    |    2 | 100 |

    |    3 | 100 |

    |    4 | 100 |

    |    5 | 100 |

    |    6 | 100 |

    |    7 | 100 |

    |    8 | 100 |

    |    9 | 100 |

    |    0 | 100 |

    +------+-----+

    10 rows in set (0.00 sec)

    由于表 t1 中的 id 值是从 1 开始的,因此返回的结果集中第一行是 id=1;扫描到 id=10 的时候才插入 m=0 这一行,因此结果集里最后一行才是 m=0。

    这个例子里由于临时表只有 10 行,内存可以放得下,因此全程只使用了内存临时表。但是,内存临时表的大小是有限制的,参数 tmp_table_size 就是控制这个内存大小的,默认是 16M。

    如果我执行下面这个语句序列:

     

    1

    2

    set tmp_table_size=1024;

    select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

    把内存临时表的大小限制为最大 1024 字节,并把语句改成 id % 100,这样返回结果里有 100 行数据。但是,这时的内存临时表大小不够存下这 100 行数据,也就是说,执行过程中会发现内存临时表大小到达了上限(1024 字节)。

    那么,当内存放不下时,这时候就会把内存临时表转成磁盘临时表,磁盘临时表默认使用的引擎是 InnoDB。这时,返回结果如下:

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    mysql> select id%100 as m, count(*) as c from t1 group by m order by null limit 10;

    +------+----+

    | m    | c  |

    +------+----+

    |    0 | 10 |

    |    1 | 10 |

    |    2 | 10 |

    |    3 | 10 |

    |    4 | 10 |

    |    5 | 10 |

    |    6 | 10 |

    |    7 | 10 |

    |    8 | 10 |

    |    9 | 10 |

    +------+----+

    10 rows in set (0.01 sec)

    如果这个表 t1 的数据量很大,很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。

    可以看出,相同的语句,由于调整 tmp_table_size 参数大小,查询结果排序方式却不同。这就是因为第一个查询使用的是内存临时表,上面已经提到了,是按照表 t1 的索引 a 顺序取出数据,模 10 得 0 的 id 是最后一行;第二个查询使用的是硬盘临时表,默认用 InnoDB 的引擎,主键是 id%10,因此存入硬盘后再按主键树顺序取出,0 就排到第一行了,InnoDB 表是顺序存储的。

    group by 优化方法 — 索引

    可以看到,不论是使用内存临时表还是磁盘临时表,group by 逻辑都需要构造一个带唯一索引的表,执行代价都是比较高的。如果表的数据量比较大,上面这个 group by 语句执行起来就会很慢,我们有什么优化的方法呢?

    要解决 group by 语句的优化问题,你可以先想一下这个问题:执行 group by 语句为什么需要临时表? group by 的语义逻辑,是统计不同的值出现的个数。但是,由于每一行的 id%100 的结果是无序的,所以我们就需要有一个临时表,来记录并统计结果。 那么,如果扫描过程中可以保证出现的数据是有序的,是不是就简单了呢?

    确实是这样,如果可以确保输入的数据是有序的,那么计算 group by 的时候,就只需要从左到右,顺序扫描,依次累加。比如数据结构(0, 0, 1, 1, 2, 2),那么这个过程如下:

    • 当碰到第一个 1 的时候,已经知道累积了 X 个 0,结果集里的第一行就是 (0,X);
    • 当碰到第一个 2 的时候,已经知道累积了 Y 个 1,结果集里的第二行就是 (1,Y);

    按照这个逻辑执行的话,扫描到整个输入的数据结束,就可以拿到 group by 的结果,不需要临时表,也不需要再额外排序。也就是说,如果语句执行过程可以一边读数据,一边直接得到结果,是不需要额外内存的,否则就需要额外的内存,来保存中间结果。

    所以,我们自然想到索引。MySQL 建立的 B+Tree 索引原生就是有序的,如果通过读取索引就完成 group by 操作,那么就可避免创建临时表和排序。因此使用索引进行 group by 的最重要的前提条件是所有 group by 的参照列(分组依据的列)来自于同一个索引,且索引按照顺序存储所有的 key(即BTREE index,而HASH index没有顺序的概念)。

    MySQL 有两种索引扫描方式完成 group by 操作,分别是松散索引扫描和紧凑索引扫描以及临时表实现 group by。在松散索引扫描方式下,分组操作和范围预测(如果有的话)一起执行完成的。在紧凑索引扫描方式下,先对索引执行范围扫描(range scan),再对结果元组进行分组。

    松散索引扫描(Loose Index Scan)

    何谓松散索引扫描实现 group by 呢?实际上就是当 MySQL 利用索引扫描来实现 group by 的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。

    松散索引扫描仅考虑索引中的一部分,当查询中没有 where 条件的时候,松散索引扫描读取的索引元组的个数和 groups 的数量相同,如果 where 条件包含范围查询,松散索引扫描查找每个 group 中第一个满足范围条件的键,并再次读取尽可能少的键。松散索引扫描只需要读取很少量的数据就可以完成 group by 操作,因而执行效率非常高。

    使用松散索引扫描需要满足以下条件:

    1. 查询在单一表上。

    2. group by 指定的所有列是索引的一个最左前缀,并且没有其它的列。比如表 t1(c1,c2,c3,c4)上建立了索引 (c1,c2,c3)。如果查询包含 “group by c1,c2”,那么可以使用松散索引扫描。但是 “group by c2,c3” (不是索引最左前缀) 和 “group by c1,c2,c4” (c4字段不在索引中)无法使用。

    3. 如果在选择列表 select list 中存在聚集函数,只能使用 min() 和 max() 两个聚集函数,并且指定的是同一列(如果 min() 和 max() 同时存在),这一列必须在索引中,且紧跟着 group by 指定的列。比如 select t1,t2,min(t3),max(t3) from t1 group by c1,c2。这源于索引的有序排序,优化器意识到 min/max 位于最左/右块,从而避免范围扫描。

    4. 如果查询中存在除了 group by 指定的列之外的索引其他部分,那么必须以常量的形式出现(除了min() 和 max() 两个聚集函数)。比如 select c1,c3 from t1 group by c1,c2 不能使用松散索引扫描。而 select c1,c3 from t1 where c3 = 3 group by c1,c2 可以使用松散索引扫描。

    5. 索引中的列必须索引整个数据列的值,而不是一个前缀索引。比如,c1 varchar(20), INDEX (c1(10)),这个索引没发用作松散索引扫描。

    如果查询能够使用松散索引扫描,那么执行计划中 Extra 中提示 “using index for group-by”。

    假设 t1(c1,c2,c3,c4) 表上有一个索引 idx(c1,c2,c3),松散索引扫描访问方法可用于以下查询:

     

    1

    2

    3

    4

    5

    6

    7

    SELECT c1, c2 FROM t1 GROUP BY c1, c2;

    SELECT DISTINCT c1, c2 FROM t1;

    SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

    SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

    SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;

    SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

    SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

    执行以下查询无法使用松散索引扫描:

    • 除了 MIN() 或 MAX() 之外还有聚合功能,比如 SUM() :

     

     

    1

    SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

     

    • group by 子句中的列不会形成索引的最左前缀:

     

     

    1

    SELECT c1, c2 FROM t1 GROUP BY c2, c3;

     

    • 查询列与 group by 不相等:

     

     

    1

    SELECT c1, c3 FROM t1 GROUP BY c1, c2;

    要查询包含 where c3=const 可以使用松散索引扫描。

    除了已经支持的 MIN() 和 MAX() 引用之外,松散索引扫描访问方法可以应用于选择列表中的其他形式的聚合函数引用,如:AVG(DISTINCT),SUM(DISTINCT)、COUNT(DISTINCT)。AVG(DISTINCT) 和 SUM(DISTINCT) 采取单个列参数,COUNT(DISTINCT) 可以有多个列参数。

    假设 t1(c1,c2,c3,c4) 表上有一个索引 idx(c1,c2,c3),松散索引扫描访问方法可用于以下查询:

     

    1

    2

    SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

    SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

    为什么松散索引扫描的效率会很高?

    因为在没有 where 子句,也就是必须经过全索引扫描的时候,松散索引扫描需要读取的键值数量与分组的组数量一样多,也就是说比实际存在的键值数目要少很多。而在 where 子句包含范围判断式或者等值表达式的时候, 松散索引扫描查找满足范围条件的每个组的第1个关键字,并且再次读取尽可能最少数量的关键字。

    紧凑索引扫描(Tight Index Scan)

    group by 在无法使用松散索引扫描时,还可以选择紧凑索引扫描,若两者都不可选,则只能借助临时表。

    紧密索引扫描可以是完整索引扫描,也可以是范围索引扫描,具体取决于查询条件。

    如果 where 条件有范围扫描,那么紧凑索引扫描仅读取满足这些条件的键(索引元组),否则执行全索引扫描。这种方式读取所有 where 条件定义的范围内的键,或者扫描整个索引,因而称作紧凑索引扫描。对于紧凑索引扫描,只有在所有满足范围条件的键被找到之后才会执行分组操作。

    要使紧凑索引扫描起作用,在查询中存在常量相等的 where 条件字段(索引中的字段),且该字段在 group by 指定的字段的前面或者中间,来自于相等条件的常量能够填充搜索键中的间隙,因而可以构成一个索引的完整前缀,索引前缀能够用于索引查找。而如果需要排序 group by 结果,并且能够形成索引前缀的搜索关键字,还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

    紧凑索引扫描实现 group by 和松散索引扫描的区别主要在于他需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取的数据来完成 group by 操作得到相应结果。

    这时候的执行计划的 Extra 信息中已经没有 “Using index for group-by” 了,但并不是说 MySQL 的 group by 操作并不是通过索引完成的,只不过是需要访问 where 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现 group by 的执行计划输出信息。

    假设 t1(c1,c2,c3,c4) 表上有一个索引 idx(c1,c2,c3),以下查询不适用于之前描述的松散索引扫描访问方法,但仍然可以使用紧凑索引扫描访问方法。

    • 这 group by 有一个间隙,但它被覆盖的条件为 c2 = ‘a’:

     

     

    1

    SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

     

    • 这 group by 不是从列的第一部分开始,但是有一个条件为该部分提供了一个常数:

     

     

    1

    SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

    在 MySQL 中,MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 group by 操作,当发现某些情况无法满足松散索引扫描实现 group by 的要求之后,才会尝试通过紧凑索引扫描来实现。

    当 group by 条件字段并不连续或者不是索引前缀部分的时候,MySQL Query Optimize 无法使用松散索引扫描,设置无法直接通过索引完成 group by 操作,因为缺失的索引键信息无法得到。但是,如果查询语句中存在一个常量值来引用缺失的索引键,则可以使用紧凑索引扫描完成 group by 操作,因为常量填充了搜索关键字中的“间隙”,可以形成完整的索引前缀,这些索引前缀可以用于索引查找。而如果需要排序 group by 结果,并且能够形成索引前缀的搜索关键字,MySQL 还可以避免额外的排序操作,因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

    group by 优化方法 — 直接排序

    如果我们明明知道,一个 group by 语句中需要放到临时表上的数据量特别大,却还是要按照“先放到内存临时表,插入一部分数据后,发现内存临时表不够用了再转成磁盘临时表”,看上去就有点儿傻。

    在 group by 语句中加入 SQL_BIG_RESULT 这个提示(hint),就可以告诉优化器:这个语句涉及的数据量很大,请直接用磁盘临时表。

    MySQL 的优化器一看,磁盘临时表是 B+ 树存储,存储效率不如数组来得高。所以,既然你告诉我数据量很大,那从磁盘空间考虑,还是直接用数组来存吧。

    因此,下面这个语句

     

    1

    select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

    的执行流程就是这样的:

    1. 初始化 sort_buffer,确定放入一个整型字段,记为 m;

    2. 扫描表 t1 的索引 a,依次取出里面的 id 值, 将 id%100 的值存入 sort_buffer 中;

    3. 扫描完成后,对 sort_buffer 的字段 m 做排序(如果 sort_buffer 内存不够用,就会利用磁盘临时文件辅助排序);

    4. 排序完成后,就得到了一个有序数组。

    根据有序数组,得到数组里面的不同值,以及每个值的出现次数。这一步的逻辑,已经从前面了解过了。执行流程如下图。

     

    1

    2

    3

    4

    5

    6

    7

    mysql> desc select SQL_BIG_RESULT id%100 as m, count(*) as c from t1 group by m;

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+

    | id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+

    |  1 | SIMPLE      | t1    | NULL       | index | PRIMARY,a     | a    | 5       | NULL | 1000 |   100.00 | Using index; Using filesort |

    +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------------+

    1 row in set, 1 warning (0.01 sec)

    从执行计划上来看,从 Extra 字段可以看到,这个语句的执行没有再使用临时表,而是直接用了排序算法,认为用 sort_buffer 直接排序性能更好。

    以上就介绍了MySQL查询优化MySQL分组查询Group By实现原理详解,包括了MySQL查询优化方面的内容,希望对MySQL有兴趣的朋友有所帮助。

    二、group by 与 distinct

    上面讲完 group by 的原理之后,还有一种与去重的语句 distinct。如果我们不需要对分组做聚合操作,那么 group by 与 distinct 谁的性能更好呢?

    如果表 t 的字段 a 上没有索引,那么下面这两条语句的性能是不是相同的:

     

    1

    2

    select a from t group by a order by null;

    select distinct a from t;

    首先需要说明的是,这种 group by 的写法,并不是 SQL 标准的写法。标准的 group by 语句,是需要在 select 部分加一个聚合函数,比如:

     

    1

    select a,count(*) from t group by a order by null;

    这条语句的逻辑是:按照字段 a 分组,计算每组的 a 出现的次数。在这个结果里,由于做的是聚合计算,相同的 a 只出现一次。

    没有了 count(*) 以后,也就是不再需要执行“计算总数”的逻辑时,第一条语句的逻辑就变成是:按照字段 a 做分组,相同的 a 的值只返回一行。而这就是 distinct 的语义,所以不需要执行聚合函数时,distinct 和 group by 这两条语句的语义和执行流程是相同的,因此执行性能也相同。

    这两条语句的执行流程是下面这样的。

    1. 创建一个临时表,临时表有一个字段 a,并且在这个字段 a 上创建一个唯一索引;

    2. 遍历表 t,依次取数据插入临时表中:如果发现唯一键冲突,就跳过;否则插入成功;

    3. 遍历完成后,将临时表作为结果集返回给客户端。

    三、排序不一致问题

    源起,阿里云论坛有人反应 MySQL 5.6 分页有重复值(排序字段没有用索引,或则直接是全表扫描),MariaDB 已经是优化后的方案,和 5.6 一致。阿里数据库月报也对此进行了回复:MySQL · 答疑解惑 · MySQL Sort 分页

    测试表和数据:

     

    1

    2

    3

    4

    5

    6

    7

    8

    create table t1(id int primary key, c1 int, c2 varchar(128));

    insert into t1 values(1,1,'a');

    insert into t1 values(2,2,'b');

    insert into t1 values(3,2,'c');

    insert into t1 values(4,2,'d');

    insert into t1 values(5,3,'e');

    insert into t1 values(6,4,'f');

    insert into t1 values(7,5,'g');

    假设每页 3 条记录,第一页 limit 0,3 和第二页 limit 3,3 查询结果如下:

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    # MySQL 5.6;

    mysql> select * from t1 order by c1 limit 0,3;

    +----+------+------+

    | id | c1   | c2   |

    +----+------+------+

    |  1 |    1 | a    |

    |  3 |    2 | c    |

    |  4 |    2 | d    |

    +----+------+------+

    3 rows in set (0.00 sec)

     

    mysql> select * from t1 order by c1 limit 3,3;

    +----+------+------+

    | id | c1   | c2   |

    +----+------+------+

    |  4 |    2 | d    |

    |  5 |    3 | e    |

    |  6 |    4 | f    |

    +----+------+------+

    3 rows in set (0.01 sec)

    我们可以看到 id 为 4 的这条记录居然同时出现在两次查询中,这明显是不符合预期的,而且在 5.5 版本中没有这个问题。

    使用优先队列排序的目的就是在不能使用索引有序性的时候,如果要排序,并且使用了 limit n,那么只需要在排序的过程中,保留 n 条记录即可,这样虽然不能解决所有记录都需要排序的开销,但是只需要 sort buffer 少量的内存就可以完成排序,上面已经说明。之所以 MySQL 5.6 出现了第二页数据重复的问题,是因为使用了优先队列排序,其使用了堆排序的排序方法,而堆排序是一个不稳定的排序方法,也就是相同的值(例子中的值2)可能排序出来的数据和读出来的数据顺序不一致,无法保证排序前后数据位置的一致,所以导致分页重复的现象。

    避免这个问题在阿里月报有说:MySQL · 答疑解惑 · MySQL Sort 分页

    但在 MySQL 5.7 版本中此问题又没有了。

     

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    11

    12

    13

    14

    15

    16

    17

    18

    19

    20

    # MySQL 5.7

    mysql> select * from t1 order by c1 limit 0,3;

    +----+------+------+

    | id | c1   | c2   |

    +----+------+------+

    |  1 |    1 | a    |

    |  2 |    2 | b    |

    |  3 |    2 | c    |

    +----+------+------+

    3 rows in set (0.00 sec)

     

    mysql> select * from t1 order by c1 limit 3,3;

    +----+------+------+

    | id | c1   | c2   |

    +----+------+------+

    |  4 |    2 | d    |

    |  5 |    3 | e    |

    |  6 |    4 | f    |

    +----+------+------+

    3 rows in set (0.00 sec)

    <参考>

    极客时间《MySQL 45讲实战》

    http://www.codes51.com/article/detail_1774816_1.html

    http://mysql.taobao.org/monthly/2015/06/04

    https://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html

    转载自:http://www.ywnds.com/?p=10174

    展开全文
  • EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY deptId LIMIT 10000,40;
  • count 分组 统计 时的子查询 优化

    今天在工作中暴露一个问题,。提出优化方案。

    原sql:

    SELECT
            batchno,
            g.name goodsname,
            '开通产品' opercontent,
            date_format(str_to_date(t1.createtime,'%Y%m%d%H%i%s'),'%Y-%m-%d %H:%i:%s') createtime,
            count(*) allcount,
            (
                SELECT
                count(*)
                FROM
                be_channelbathorder t2
                WHERE
                t2.errcode in ('0000','2003')
                AND t2.batchno = t1.batchno
            )
            succount,
            (
                SELECT
                count(*)
                FROM
                be_channelbathorder t2
                WHERE
                t2.errcode not in ('0000','2003')
                AND t2.batchno = t1.batchno
            )
            errcount,

            status,
            isNow
            FROM

            be_channelbathorder t1,be_goods g

    where 1=1 and t1.buycode=g.ID

    GROUP BY
            batchno,
            t1.buycode,
            t1.createtime
    ORDER BY t1.createtime DESC

    在数据量小的时候无法察觉其查询速度。当主表数据到达7万时,发现此查询速度及其慢至卡死。


    上面经过测试,在红色字体部分是导致查询缓慢的最主要原因。经过查阅资料也未能找到合适方法,后来问了组内高端人士,得知,count() 函数中可以放入分组查询的条件。


    得知后,进行优化:


    count(
                case when t1.errcode='0000' OR t1.errcode='2003' then 1  else null end
            )
            succount,
            count(
                case when t1.errcode &lt;&gt; '0000' AND t1.errcode &lt;&gt; '2003' then 1  else null end
            )
            errcount,


    优化sql以后,减少了不必要的二次自表查询。用explain观察也发现,前后两者的确不同,速度有质的变化。


    以下是两个 explain的比较



    展开全文
  • 标量子查询优化(用group by 代替distinct).pdf
  • 主要介绍了oracle中使用group by优化distinct的相关资料,需要的朋友可以参考下
  • mysql group by 优化

    千次阅读 2016-07-27 18:04:32
    本以为mysql select 查询单单只有 where order by 时会进行 索引触发 没想到group by 也可以利用到对应的索引 首先创建一个试坑表 居然调用到name 这个索引了 后来找到一个group by 原理文章有个哥们...

    发现一个很神奇的事情。本以为mysql select 查询单单只有 where order by 时会进行 索引触发 没想到group by 也可以利用到对应的索引


    首先创建一个试坑表


    居然调用到name 这个索引了


    后来找到一个group by 原理文章有个哥们是这么写的

    满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中,MySQL能够做得更好,即通过索引访问而不用创建临时表。

           为GROUP BY使用索引的最重要的前提条件是所有GROUP BY列引用同一索引的属性,并且索引按顺序保存其关键字。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件,以及选择的累积函数。

           由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引。

    看看是不是所有的引擎都可以使用。我这边单单只是试了InnoDB,MyISAM



    运行一下sql


    MyISAM情况下这个玩意就玩不了


    展开全文
  • 现网出现慢查询,在500万数量级的情况下,单表查询速度在30多秒,需要对sql进行优化,sql如下: 这里测试环境构造了500万条数据,模拟了这个慢查询。 简单来说,就是查询一定条件下,都有哪些用户的。很...

    一、问题背景


     

    现网出现慢查询,在500万数量级的情况下,单表查询速度在30多秒,需要对sql进行优化,sql如下:

     

    640?wx_fmt=png

     

    这里测试环境构造了500万条数据,模拟了这个慢查询。

     

    简单来说,就是查询一定条件下,都有哪些用户的。很简单的sql,可以看到,查询耗时为37秒。

     

    说一下app_account字段的分布情况,随机生成了5000个不同的随机数,然后分布到了这500万条数据里,平均来说,每个app_account都会有1000个是重复的值,种类共有5000个。

     

    二、看执行计划


     

    640?wx_fmt=png

     

    可以看到,group by字段上是加了索引的,也用到了。

     

    三、优化


     

    优化思路:

     

    思路一:

     

    后面应该加上 order by null;避免无用排序,但其实对结果耗时影响不大,还是很慢。

     

    640?wx_fmt=png

     

    思路二:

     

    where条件太复杂,没索引,导致查询慢,但给where条件的所有字段加上了组合索引,没起作用。

     

    640?wx_fmt=png

    640?wx_fmt=png

     

    思路三:

     

    既然group by慢,换distinct试试

     

    640?wx_fmt=png

     

    瞬间就加快了。

     

    虽然知道group by和distinct有很小的性能差距,但是没想到,差距居然这么大。

     

    四、你以为这就结束了吗


     

    这个bug转给测试后,测试一测,居然还是30多秒。再测试电脑上执行sql,依旧是30多秒。

     

    又回本人的电脑上,连接同一个数据库,一执行sql,0.8秒。

     

    同一个库,同一个sql,怎么在两台电脑执行的差距这么大。

     

    后来直接在服务器上执行:

     

    640?wx_fmt=png

     

    还是30多秒。那看来就是本人的电脑问题。

     

    后来又实验多个同事的电脑,最后得出的结论是:是因为之前用的SQLyog。

     

    最后发现,只有用sqlyog执行这个“优化后”的sql会是0.8秒,在navcat和服务器上直接执行,都是30多秒。

     

    那就是sqlyog的问题了,现在也不清楚sqlyog是不是做什么优化了,这个慢查询的问题还在解决中(问题可能是出在mysql自身的参数上)。

     

    这里只是记录下这个问题,sqlyog执行sql速度,和服务器执行sql速度,在有的sql中差异巨大,并不可靠

     

    五、后续(还未解决)


     

    感谢大家在评论里出谋划策,本人来回复下问题进展:

     

    1.所谓的sqlyog查询快,命令行查询慢的现象,已经找到原因了。是因为sqlyog会在查询语句后默认加上limit 1000,所以导致很快。这个问题不再纠结。

     

    2.已经试验过的方法(都没有用):

     

    ①给app_account字段加索引。

    ②给sql语句后面加order by null。

    ③调整where条件里字段的查询顺序,有索引的放前面。

    ④给所有where条件的字段加组合索引。

    ⑤用子查询的方式,先查where条件里的内容,再去重。

     

    测试环境和现网环境数据还是有点不一样的,这里贴一张现网执行sql的图(1分钟):

     

    640?wx_fmt=png

     

    六、最终解决方案


     

    经过网友的提醒,发现explain执行计划里,索引好像并没有用到创建的idx_end_time。

     

    然后果断在现网试了下,强制指定使用idx_end_time索引,结果只要0.19秒。

     

    640?wx_fmt=png

     

    至此问题解决,其实同事昨天也在怀疑,是不是这个表索引建的太多了,导致用的不对,原本用的是idx_org_id和idx_mvno_id。

     

    现在强制指定idx_end_time就ok了!

     

    最后再对比下改前后的执行计划:

     

    改之前(查询要1分钟左右):

    640?wx_fmt=png

     

    改之后(查询只要几百毫秒):

    640?wx_fmt=png

     

    出处:https://www.cnblogs.com/dijia478/p/11550902.html(复制到浏览器中打开)

    展开全文
  • 1.优化GROUP BY 语句 默认情况下,MySQL对所有GROUP BY col1,col2,...的字段进行排序。这与在查询中指定ORDER BY col1,col2,...类似。 因此,如果显示包括一个包含相同列的order by 子句,则对MySQL的实际执行...
  • 在数据库查询中,group by语句经常使用,而这个语句的使用是最耗性能的,按常理, 我们生活中要这样做也很麻烦,有两种情形:  1、有索引的情况  2、无索引的情况 对于第一种情况,如果在生活中要做这样的事情,很...
  • 交代一下背景,这算是一次项目经验吧,属于公司一个已上线平台的功能,这算是离职人员挖下的坑,随着数据越来越多,原本的SQL查询变得越来越慢,用户体验特别差,因此SQL优化任务交到了我手上。 这个SQL查询关联两个...
  • Mysql优化GROUP BY语句优化

    千次阅读 2019-06-27 14:47:19
    一、首先看Group By创建临时表的情况,因为有临时表的存在所以索引失效。 如果GROUP BY 的列没有索引,产生临时表 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表 如果GROUP BY...
  • mysql groupby 优化

    千次阅读 2017-12-21 22:27:49
    执行GROUP BY子句的最一般的方法:先扫描整个表,然后创建一个新的临时表,表中每个组的所有行应为连续的,...此类查询的 EXPLAIN 输出显示 Extra 列的值为 Using index for group-by。   一。松散索引扫描
  • MySQL子查询优化-子语句含有group by时

    千次阅读 2016-01-26 23:25:40
    当我使用Mysql进行下列语句的查询时: select count(1) from jy_info_user  where user_card != ''  and id IN (select id from jy_erp.jy_info_user where ifnull(user_card,'')!='' group by user_card ...
  • ②、对 group by 查询慢进行优化; 简单描述下排查步骤: 排查主要分为了两个步骤: 后台接口的监控,看看哪个方法调用时耗时多 数据库开启慢查询日志,记录执行很慢的SQL 推荐使用阿里开源的Java线上诊断工具 ...
  • group by 优化临时表

    千次阅读 2015-11-11 16:39:30
    GROUP BY 优化之临时表 MySQL 在进行 GROUP BY 操作时要想利用索引,必须满足 GROUP BY 的字段同时存放于同一个索引中,且该索引是一个有序索引(如 Hash 索引就不能满足要求)。不仅如此,是否能够利用索引来实现...
  • mysql sum+group条件优化

    千次阅读 2019-06-03 11:30:23
    记一次sql优化: 表:st_youji_day_store_budget 记录数:3482116 sql语句: SELECT the_date, sum(budget_sale) AS budget_sale FROM st_youji_day_store_budget WHERE num = '90' AND the_mon = '2019-06'...
  • hive中distinct和group by优化

    千次阅读 2020-04-28 09:41:33
    由于必须去重,因此Hive会把map阶段的输出全部分布到一个reduce task中,容易引起性能问题,可以通过先group by ,再count得方式进行优化 优化后:select count(*) from( select user_id from a group ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 221,735
精华内容 88,694
关键字:

group查询优化