group by走索引吗（group by 执行流程及优化）

本文目录

• group by 执行流程及优化
• mysql中的索引，在什么时候开始起作用.onwheregroup byhaving
• MySQL何时使用索引与不使用索引
• mysql group by 与join 连用注意事项
• MySQL怎么禁止使用缓存以及复杂多表查询的索引优化问题
• 记一次MySQL Group by 的坑
• oracle 中 in ，between，大于小于，走不走索引
• mysql的group by怎么优化
• Mysql索引生效条件是什么

group by 执行流程及优化

在说group by执行流程及优化之前，要先说一下临时表。 有的人可能会认为，临时表就是内存表。但是，这两个概念可是完全不同的。 为了便于理解，我们来看下下面这个操作序列： 可以看到，临时表在使用上有以下几个特点： 由于临时表只能被创建它的 session 访问，所以在这个 session 结束的时候，会自动删除临时表。 为了便于分析，我们创建表t1来举例： 我们来看一下这个语句： 这个语句的逻辑是把表 t1 里的数据，按照 id%10 进行分组统计，并按照 m 的结果排序后输出。它的 explain 结果如下： 在 Extra 字段里面，我们可以看到三个信息： 这个语句的执行流程是这样的： 这个流程的执行图如下：把内存临时表的大小限制为最大 1024 字节，并把语句改成 id % 100，这样返回结果里有 100 行数据。但是，这时的内存临时表大小不够存下这 100 行数据，也就是说，执行过程中会发现内存临时表大小到达了上限（1024 字节）。那么，这时候就会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是 InnoDB。 如果这个表 t1 的数据量很大，很可能这个查询需要的磁盘临时表就会占用大量的磁盘空间。 可以看到，不论是使用内存临时表还是磁盘临时表，group by 逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个 group by 语句执行起来就会很慢，我们有什么优化的方法呢？ group by 的语义逻辑，是统计不同的值出现的个数。但是，由于每一行的 id%100 的结果是无序的，所以我们就需要有一个临时表，来记录并统计结果。那么，如果扫描过程中可以保证出现的数据是有序的，是不是就简单了呢？假设，现在有一个类似图 10 的这么一个数据结构，我们来看看 group by 可以怎么做。 可以看到，如果可以确保输入的数据是有序的，那么计算 group by 的时候，就只需要从左到右，顺序扫描，依次累加。也就是下面这个过程： 按照这个逻辑执行的话，扫描到整个输入的数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序。你一定想到了，InnoDB 的索引，就可以满足这个输入有序的条件。 在 MySQL 5.7 版本支持了 generated column 机制，用来实现列数据的关联更新。你可以用下面的方法创建一个列 z，然后在 z 列上创建一个索引。 如果是 MySQL 5.6 及之前的版本，你也可以创建普通列和索引，来解决这个问题 这样，索引 z 上的数据就是类似图 3 这样有序的了。上面的 group by 语句就可以改成： 从 Extra 字段可以看到，这个语句的执行不再需要临时表，也不需要排序了。 所以，如果可以通过加索引来完成 group by 逻辑就再好不过了。但是，如果碰上不适合创建索引的场景，我们还是要老老实实做排序的。那么，这时候的 group by 要怎么优化呢？ 如果我们明明知道，一个 group by 语句中需要放到临时表上的数据量特别大，却还是要按照“先放到内存临时表，插入一部分数据后，发现内存临时表不够用了再转成磁盘临时表”，看上去就有点儿傻。 在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。 MySQL 的优化器一看，磁盘临时表是 B+ 树存储，存储效率不如数组来得高。所以，既然你告诉我数据量很大，那从磁盘空间考虑，还是直接用数组来存吧。因此，下面这个语句 的执行流程就是这样的： 根据有序数组，得到数组里面的不同值，以及每个值的出现次数。下面分别是执行流程图和执行 explain 命令得到的结果。 从 Extra 字段可以看到，这个语句的执行没有再使用临时表，而是直接用了排序算法。

mysql中的索引，在什么时候开始起作用.onwheregroup byhaving

让groupby使用索引而不创建临时表，使用索引的前提条件是：所有GROUPBY列引用同一索引的属性，并且索引按顺序保存其关键字（B-树索引，不是HASH索引）至于DISTINCT和GROUPBY哪个效率更高？理论上DISTINCT操作只需要找出所有不同的值就可以了。而GROUPBY操作还要为其他聚集函数进行准备工作。从这一点上将，GROUPBY操作做的工作应该比DISTINCT所做的工作要多一些。但是实际上，DISTINCT操作，它会读取了所有记录;GROUPBY需要读取的记录数量与分组的组数量一样多，比实际存在的记录数目要少很多。

MySQL何时使用索引与不使用索引

所有原则都建立在最左匹配原则上，如果多个字段同时匹配，则最左匹配1、假如表的主键在id1上（有先后顺序），如果使用id1查询则使用索引，如果使用id2则不使用索引2、order by 不使用索引3、group by 使用索引时，字段在第一个字段时 ，使用索引，字段在第二个字段时，不使用索引4、in查询操作，查询字段在第一个字段时 ，使用索引，查询字段在第二个字段时，不使用索引（not in在表中的查询数量大于30%的时候是全表扫描的，用不到索引。所以没有可以优化的。） 5、like 查询操作，如果第一个字符不是正则符号，则查询字段在第一个字段时 ，使用索引，查询字段在第二个字段时，不使用索引如果第一个字符是正则符号，则都不使用索引6、如果没有搜索条件、没有排序条件、没有group by条件时，就不使用索引7、union 查询操作，哪个字段是第一个索引字段，就在那个查询子句上使用索引

mysql group by 与join 连用注意事项

　　1. 如果GROUP BY 的列没有索引,产生临时表. 　　2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表. 　　3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表. 　　4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表. 　　5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表. 　　6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表. 1、执行FROM语句 2、执行ON过滤 3、添加外部行 4、执行where条件过滤 5、执行group by分组语句 6、执行having 7、select列表 8、执行distinct去重复数据 9、执行order by字句 10、执行limit字现在有这么两章表 学生表和分数表，现在需要查出每个班在11点到14点考试的科目，最高分数的学生，以及班级，姓名，和分数 SELECT NAME,score FROM score_record INNER JOIN user_info ON user_info.id = score_record.user_id WHERE create_time BETWEEN ’2019-01-01 11:00:00’ AND ’2019-01-01 14:00:00’ GROUP BY classes ORDER BY score DESC 

MySQL怎么禁止使用缓存以及复杂多表查询的索引优化问题

1、query_cache_size=0 已经禁用了查询缓存，但表数据可能缓存了，flush tables试试，不过操作系统还有一个硬盘缓存，想跟第一次查询之前的状态一致恐怕只能每次重启2、group by与order by不会用索引的，索引最大的用处就是减小磁盘IO，也就是where时尽量少磁盘IO并读出所有满足条件的记录

记一次MySQL Group by 的坑

表结构如下： 业务需要将表中重复数据删除，所以需要按照 组合唯一索引键筛选出重复的数据进行删除。SQL如下： 表中有符合索引 KEY column1_column2_index ( column1 , column2 ) sql语句 Group by 也是按照最左匹配原则顺序写的 group by 的字段，但是每次执行SQL耗时都是好几十秒 explain 该 sql 发现，并没有走表中存在的复合索引，而是直接走的 File sorted（文件排序）；group by 语句其实是有要先排序再分组的； 问题的关键定位到没有没有命中表中的复合索引，那为何 group by 字段前两个就是复合索引，只是最后两个不是，为何没有走索引呢？不是索引只要满足最左匹配原则就可以命中吗？ 分析后发现，索引可以用在两个地方，1 被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。2 快速地执行ORDER BY和GROUP BY语句的排序和分组操作。 本处就是可以使用索引做排序使用，而避免文件排序；此处要命中索引，走索引排序，必须要表中有一个复合索引包含 group by 的所有字段且顺序一致； 网上有部分博客说 group by 自带的排序和 order by 排序，走不走索引的规则是一样的，这里本人测试了一下，添加 group by 后面所有顺序字段的复合索引对 group by 的查询时间有直接的影响，从 30多秒 优化到 3秒； 但是对如下SQL 的执行时间也有影响，但是远远没有对group by 的影响大，如下sql，添加 order by 的全索引后 只能从30多秒优化到 10 多秒 select column2 from am_cm_relationship order by column1,column2,column3,column4 ;

oracle 中 in ，between，大于小于，走不走索引

①.有大量重复值、且经常有范围查询（between, 》,《 ，》=,《 =）和order by、group by发生的列，可考虑建立群集索引； ②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引； ③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列(1)‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. (2) ‘||’是字符连接函数. 就象其他函数那样, 停用了索引. (3) ‘+’是数学函数. 就象其他数学函数那样, 停用了索引. (4)相同的索引列不能互相比较,这将会启用全表扫描.

mysql的group by怎么优化

在某些情况中，MySQL能够做得更好，通过索引访问而不用创建临时表。GROUP BY使用索引的最重要的前提条件是 所有GROUP BY列引用同一索引的属性，并且索引按顺序保存(例如，这是B-树索引，而不是HASH索引)。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的累积函数。有两种方法可以通过索引优化GROUP BY语句：1，组合操作结合所有范围判断式使用(如果有)。2，首先执行范围扫描，然后组合结果元组。

Mysql索引生效条件是什么

假设index（a,b,c）最左前缀匹配：模糊查询时，使用%匹配时：’a%‘会使用索引，’%a‘不会使用索引条件中有or，索引不会生效a and c，a生效，c不生效b and c，都不生效a and b 》 5 and c,a和b生效，c不生效。