MySQL 原理优化之Group By的优化技巧

目录

今天来看看MySQL 中如何多Group By 语句进行优化的。

先创建tb_user 表如下：

通过show index from tb_user; 命令查看表，没有存在任何的索引。

执行如下代码，查看SQL 执行情况

explain select profession, count(*) from tb_user group by profession ;

发现返回结果中 type 为“ALL” ，Extra 返回“Using temporary” 说明没有使用索引。

于是，创建基于profession，age和status 的索引如下：

create index index_user_pro_age_sta on tb_user(profession ,age, status);

这里创建索引从左到右的顺序是 profession ,age, status。

此时再次执行SQL执行计划如下：

explain select profession, count(*) from tb_user group by profession ;

发现使用了索引“index_user_pro_age_sta”。说明在执行 group by操作的时候，使用联合索引是有效的。

接着在看使用如下代码：

explain select age, count(*) from tb_user group by age;

SQL 语句使用age 进行group by，查看explain的结果如下：

在Extra 字段中发现使用了“Using temporary”，说明没有走索引，是因为没有满足索引的最左前缀法则。

联合索引 index_user_pro_age_sta的顺序从左到右分别是 profession ,age, status。

上面的SQL 语句Group by 后面接着的是age ，因此出现“Using temporary”。

这里对SQL 进行修改。如下：

explain select profession,age, count(*) from tb_user group by profession, age;

由于group by 后面跟着profession, age ，符合联合索引的创建顺序，因此索引生效。

我们再来试试再加入过滤条件的情况，加入profession = 软件工程，此时group by 里面只显示 age，那么此时是否会走索引， 答案是 using index。因为满足了最左前缀法则。

explain select age, count(*) from tb_user where profession = '软件工程' group by age;

总结一下：

SQL在分组操作的时候，可以通过索引来提高效率。做分组操作的时候，索引的使用需要满足最左前缀法则。

到此这篇关于MySQL 原理优化之Group By的优化技巧的文章就介绍到这了,更多相关MySQLGroup By优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MySQL Group by的优化详解

    MySQL Group by的优化详解

    一个标准的 Group by 语句包含排序.分组.聚合函数,比如 select a,count(*) from t group by a ;  这个语句默认使用 a 进行排序.如果 a 列没有索引,那么就会创建临时表来统计 a和 count(*),然后再通过 sort_buffer 按 a 进行排序. 标准的执行流程 结构: create table t1(id int primary key, a int, b int, index(a)); delimiter ;; create proce

  • MySQL优化GROUP BY方案

    执行GROUP BY子句的最一般的方法:先扫描整个表,然后创建一个新的临时表,表中每个组的所有行应为连续的,最后使用该临时表来找到组并应用聚集函数(如果有聚集函数).在某些情况中,MySQL通过访问索引就可以得到结果,而不用创建临时表.此类查询的 EXPLAIN 输出显示 Extra列的值为 Using index for group-by. 一. 松散索引扫描 1.满足条件 查询针对一个表.  GROUP BY 使用索引的最左前缀.  只可以使用MIN()和MAX()聚集函数,并且它们均指向相

  • MySQL group by语句如何优化

    在MySQL中,新建立一张表,该表有三个字段,分别是id,a,b,插入1000条每个字段都相等的记录,如下: mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(1

  • Mysql5.7及以上版本 ONLY_FULL_GROUP_BY报错的解决方法

    近期在开发过程中,因为项目开发环境连接的mysql数据库是阿里云的数据库,而阿里云的数据库版本是5.6的.而测试环境的mysql是自己安装的5.7.因此在开发过程中有小伙伴不注意写了有关group by的sql语句.在开发环境中运行是正常的,而到了测试环境中就发现了异常. 原因分析:MySQL5.7版本默认设置了 mysql sql_mode = only_full_group_by 属性,导致报错. 其中ONLY_FULL_GROUP_BY就是造成这个错误的罪魁祸首了,对于group by聚合

  • mysql之group by和having用法详解

    GROUP BY语法可以根据给定数据列的每个成员对查询结果进行分组统计,最终得到一个分组汇总表. select子句中的列名必须为分组列或列函数,列函数对于group by子句定义的每个组返回一个结果. 某个员工信息表结构和数据如下: id name dept salary edlevel hiredate 1 张三 开发部 2000 3 2009-10-11 2 李四 开发部 2500 3 2009-10-01 3 王五 设计部 2600 5 2010-10-02 4 王六 设计部 2300 4

  • MySQL去重该使用distinct还是group by？

    前言 关于group by 与distinct 性能对比:网上结论如下,不走索引少量数据distinct性能更好,大数据量group by 性能好,走索引group by性能好.走索引时分组种类少distinct快.关于网上的结论做一次验证. 准备阶段屏蔽查询缓存 查看MySQL中是否设置了查询缓存.为了不影响测试结果,需要关闭查询缓存. show variables like '%query_cache%'; 查看是否开启查询缓存决定于query_cache_type和query_cache_

  • 基于mysql实现group by取各分组最新一条数据

    前言: group by函数后取到的是分组中的第一条数据,但是我们有时候需要取出各分组的最新一条,该怎么实现呢? 本文提供两种实现方式. 一.准备数据 http://note.youdao.com/noteshare?id=dba748092a619be0a8f160ccf6e25a5f&sub=FD4C1C7823CA440DB360FEA3B4A905CD 二.三种实现方式 1)先order by之后再分组: SELECT * FROM (SELECT * from tb_dept ORDE

  • MySQL优化GROUP BY（松散索引扫描与紧凑索引扫描）

    满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后使用该临时表来找到组并应用累积函数(如果有).在某些情况中,MySQL能够做得更好,即通过索引访问而不用创建临时表.        为GROUP BY使用索引的最重要的前提条件是所有GROUP BY列引用同一索引的属性,并且索引按顺序保存其关键字.是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引.为该部分指定的条件,以及选择的累积函数.        由于GROUP BY 实

  • MySQL数据库分组查询group by语句详解

    一:分组函数的语句顺序 1 SELECT ... 2 FROM ... 3 WHERE ... 4 GROUP BY ... 5 HAVING ... 6 ORDER BY ... 二:WHERE和HAVING筛选条件的区别 数据源 位置 关键字 WHERE 原始表 ORDER BY语句之前 WHERE HAVING 分组后的结果集 ORDER BY语句之后 HAVING 三:举例说明 #1.查询每个班学生的最大年龄 SELECT MAX(age),class FROM STU_CLASS GR

  • MySQL 原理优化之Group By的优化技巧

    MySQL 原理优化之Group By的优化技巧

    目录 今天来看看MySQL 中如何多Group By 语句进行优化的. 先创建tb_user 表如下: 通过show index from tb_user; 命令查看表,没有存在任何的索引. 执行如下代码,查看SQL 执行情况 explain select profession, count(*) from tb_user group by profession ; 发现返回结果中 type 为“ALL” ,Extra 返回“Using temporary” 说明没有使用索引. 于是,创建基于p

  • MySQL 原理与优化之Update 优化

    MySQL 原理与优化之Update 优化

    前言: 谈到Update 语句大家可能不会陌生,很多情况下我们都会使用它来更新table中的记录.一般而言我们会使用innodb 的存储引擎,innodb引擎是基于行锁的,具体一点说是针对索引来加锁的(保证锁不能失效),并不是针对记录加锁,如果对于没有建立索引的字段进行过滤操作,从而执行update 的话,会导致表锁 . 今天就看看在使用innodb的时候如何使用update 语句. 已经存在course 表,其内容如下: 目前该表没有加任何的索引,默认情况下id 是表的索引. 接着让我们分别开

  • MySQL 原理与优化之Limit 查询优化

    MySQL 原理与优化之Limit 查询优化

    假设有表tb_sku,其表结构如下: 表中大约有200w条记录,执行如下的sql 语句大约 4.36s 返回数据 select count(*) from tb_sku; 接着我们使用 对其进行分页查找: select * from tb_sku limit 0,10; limit 语句 其中0 代表起始位置,10 为每页返回的数据数量. 如上图所示,很快就返回了查询结果. 接着我们再使用SQL 语句 select * from tb_sku limit 10,10; 语句从记录位置10的位置开

  • MySQL 原理与优化之原数据锁的应用

    MySQL 原理与优化之原数据锁的应用

    MySQL 中原数据锁是系统自动控制添加的,对于用户来说无需显示调用,当我们使用一张表的时候就会加上原数据锁. 原数据锁的作用是为了保护表原数据的一致性,如果在表上有活动事务的时候,不可以对元数据进行写入操作.也就是为了避免DML 和DDL 之间的冲突,保证读写的正确性. 说白了就是,在对数据表进行读写操作的时候,不能进行修改表结构的操作. 如上图所示,在执行select 操作的时候,MySQL 会自动加上shared_read 锁,在insert,update, delete 以及 selec

  • MySql子查询IN的执行和优化的实现

    MySql子查询IN的执行和优化的实现

    目录 IN为什么慢? IN和EXISTS哪个快? 如何提高效率? MySQL5.6对子查询的优化? SEMI JOIN策略 Duplicate Weedout优化 Materialization优化 FirstMacth优化 LooseScan优化 SEMI JOIN变量 参考 IN为什么慢? 在应用程序中使用子查询后,SQL语句的查询性能变得非常糟糕.例如: SELECT driver_id FROM driver where driver_id in (SELECT driver_id FR

  • Mysql占用过高CPU时的优化手段(必看)

    Mysql占用过高CPU时的优化手段(必看)

    Mysql占用CPU过高的时候,该从哪些方面下手进行优化? 占用CPU过高,可以做如下考虑: 1)一般来讲,排除高并发的因素,还是要找到导致你CPU过高的哪几条在执行的SQL,show processlist语句,查找负荷最重的SQL语句,优化该SQL,比如适当建立某字段的索引: 2)打开慢查询日志,将那些执行时间过长且占用资源过多的SQL拿来进行explain分析,导致CPU过高,多数是GroupBy.OrderBy排序问题所导致,然后慢慢进行优化改进.比如优化insert语句.优化group

  • 详解MySQL中的数据类型和schema优化

    最近在学习MySQL优化方面的知识.本文就数据类型和schema方面的优化进行介绍. 1. 选择优化的数据类型 MySQL支持的数据类型有很多,而如何选择出正确的数据类型,对于性能是至关重要的.以下几个原则能够帮助确定数据类型: 更小的通常更好 应尽可能使用可以正确存储数据的最小数据类型,够用就好.这样将占用更少的磁盘.内存和缓存,而在处理时也会耗时更少. 简单就好 当两种数据类型都能胜任一个字段的存储工作时,选择简单的那一方,往往是最好的选择.例如整型和字符串,由于整型的操作代价要小于字符,所

  • mysql配置连接参数设置及性能优化

    目录 1 前言 2 数据库连接配置信息 3 配置信息说明 4 数据库sql 5 mysql 性能优化的关注点 6 硬件和操作系统方面的优化 7 架构设计方面的优化 8 mysql 程序配置优化. 9 sql 执行层面优化 10 最终总结 1 前言 在java开发中,最常用的数据存储就是数据库,其中常用的就是mysql,关于数据库的连接配置信息,可能就不是那么的清除了,这里做一个配置信息的说明. 2 数据库连接配置信息 jdbc:mysql://localhost:3306/database_na

  • MySQL提升大量数据查询效率的优化神器

    MySQL提升大量数据查询效率的优化神器

    目录 前言 查看SQL执行频率 定位低效率执行SQL explain分析执行计划 trace分析优化器执行计划 使用索引优化 SQL优化 大量插入数据 优化insert语句 优化order by语句 2.两种排序方式 3.Filesort 的优化 优化group by 子查询优化 limit优化 前言 在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越

  • MySQL千万级数据的大表优化解决方案

    目录 1.数据库设计和表创建时就要考虑性能 设计表时要注意: 索引 简言之就是使用合适的数据类型,选择合适的索引 引擎 2.sql的编写需要注意优化 3.分区 分区的好处是: 分区的限制和缺点: 分区的类型: 4.分表 5.分库 mysql数据库中的表数据量几千万后,查询速度会很慢,日常各种卡慢,严重影响使用体验.在考虑升级数据库或者换用大数据解决方案前,必须优化现有mysql数据库表设计和sql语句. 1.数据库设计和表创建时就要考虑性能 mysql数据库本身高度灵活,造成性能不足,严重依赖开

随机推荐