位置: 编程技术 - 正文

MySQL优化GROUP BY(松散索引扫描与紧凑索引扫描)(Mysql优化sql方案)

编辑:rootadmin

推荐整理分享MySQL优化GROUP BY(松散索引扫描与紧凑索引扫描)(Mysql优化sql方案),希望有所帮助,仅作参考,欢迎阅读内容。

文章相关热门搜索词:Mysql优化器,Mysql优化命令,Mysql优化命令,Mysql优化方案,Mysql优化索引,Mysql优化方案,Mysql优化方案,Mysql优化方案,内容如对您有帮助,希望把文章链接给更多的朋友!

满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表,表中每个组的所有行应为连续的,然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中,MySQL能够做得更好,即通过索引访问而不用创建临时表。 为GROUP BY使用索引的最重要的前提条件是所有GROUP BY列引用同一索引的属性,并且索引按顺序保存其关键字。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件,以及选择的累积函数。 由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚合函数的计算。所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引。在MySQL 中,GROUP BY 的实现同样有多种(三种)方式,其中有两种方式会利用现有的索引信息来完成 GROUP BY,另外一种为完全无法使用索引的场景下使用。下面我们分别针对这三种实现方式做一个分析。

1、使用松散索引扫描(Loose index scan)实现 GROUP BY

对“松散索引扫描”的定义,本人看了很多网上的介绍,都不甚明白。在此逻列如下:定义1:松散索引扫描,实际上就是当 MySQL 完全利用索引扫描来实现 GROUP BY 的时候,并不需要扫描所有满足条件的索引键即可完成操作得出结果。定义2:优化Group By最有效的办法是当可以直接使用索引来完全获取需要group的字段。使用这个访问方法时,MySQL使用对关键字排序的索引的类型(比如BTREE索引)。这使得索引中用于group的字段不必完全涵盖WHERE条件中索引对应的key。由于只包含索引中关键字的一部分,因此称为松散的索引扫描。意思是索引中用于group的字段,没必要包含多列索引的全部字段。例如:有一个索引idx(c1,c2,c3),那么group by c1、group by c1,c2这样c1或c1、c2都只是索引idx的一部分。要注意的是,索引中用于group的字段必须符合索引的“最左前缀”原则。group by c1,c3是不会使用松散的索引扫描的例如:explainSELECT group_id,gmt_createFROM group_messageWHERE user_id>1GROUP BY group_id,gmt_create;本人理解“定义2”的例子说明有一个索引idx(c1,c2,c3)SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;索引中用于group的字段为c1,c2不必完全涵盖WHERE条件中索引对应的key(where条件中索引,即为c1;c1对应的key,即为idx)索引中用于group的字段(c1,c2)只包含索引中关键字(c1,c2,c3)的一部分,因此称为松散的索引扫描。要利用到松散索引扫描实现GROUP BY,需要至少满足以下几个条件:◆ 查询针对一个单表◆ GROUP BY 条件字段必须在同一个索引中最前面的连续位置;GROUP BY包括索引的第1个连续部分(如果对于GROUP BY,查询有一个DISTINCT子句,则所有DISTINCT的属性指向索引开头)。◆ 在使用GROUP BY 的同时,如果有聚合函数,只能使用 MAX 和 MIN 这两个聚合函数,并且它们均指向相同的列。◆ 如果引用(where条件中)到了该索引中GROUP BY 条件之外的字段条件的时候,必须以常量形式存在,但MIN()或MAX() 函数的参数例外; 或者说:索引的任何其它部分(除了那些来自查询中引用的GROUP BY)必须为常数(也就是说,必须按常量数量来引用它们),但MIN()或MAX() 函数的参数例外。补充:如果sql中有where语句,且select中引用了该索引中GROUP BY 条件之外的字段条件的时候,where中这些字段要以常量形式存在。◆ 如果查询中有where条件,则条件必须为索引,不能包含非索引的字段

松散索引扫描explainSELECT group_id,user_idFROM group_messageWHERE group_id between 1 and 4GROUP BY group_id,user_id;松散索引扫描explainSELECT group_id,user_idFROM group_messageWHERE user_id>1 and group_id=1GROUP BY group_id,user_id;非松散索引扫描explainSELECT group_id,user_idFROM group_messageWHERE abc=1GROUP BY group_id,user_id;非松散索引扫描explainSELECT group_id,user_idFROM group_messageWHERE user_id>1 and abc=1GROUP BY group_id,user_id;松散索引扫描,此类查询的EXPLAIN输出显示Extra列的Using index for group-by

下面的查询提供该类的几个例子,假定表t1(c1,c2,c3,c4)有一个索引idx(c1,c2,c3):

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;

由于上述原因,不能用该快速选择方法执行下面的查询:

1、除了MIN()或MAX(),还有其它累积函数,例如: SELECT c1, SUM(c2) FROM t1 GROUP BY c1;2、GROUP BY子句中的域不引用索引开头,如下所示: SELECT c1,c2 FROM t1 GROUP BY c2, c3;3、查询引用了GROUP BY部分后面的关键字的一部分,并且没有等于常量的等式,例如: SELECT c1,c3 FROM t1 GROUP BY c1, c2;这个例子中,引用到了c3(c3必须为组合索引中的一个),因为group by 中没有c3。并且没有等于常量的等式。所以不能使用松散索引扫描可以这样改一下:SELECT c1,c3 FROM t1 where c3='a' GROUP BY c1, c2下面这个例子不能使用松散索引扫描SELECT c1,c3 FROM t1 where c3='a' GROUP BY c1, c2为什么松散索引扫描的效率会很高?答:因为在没有WHERE 子句,也就是必须经过全索引扫描的时候, 松散索引扫描需要读取的键值数量与分组的组数量一样多,也就是说比实际存在的键值数目要少很多。而在WHERE 子句包含范围判断式或者等值表达式的时候, 松散索引扫描查找满足范围条件的每个组的第1 个关键字,并且再次读取尽可能最少数量的关键字。

2、使用紧凑索引扫描(Tight index scan)实现 GROUP BY

MySQL优化GROUP BY(松散索引扫描与紧凑索引扫描)(Mysql优化sql方案)

紧凑索引扫描实现 GROUP BY 和松散索引扫描的区别主要在于:紧凑索引扫描需要在扫描索引的时候,读取所有满足条件的索引键,然后再根据读取出的数据来完成 GROUP BY 操作得到相应结果。这时候的执行计划的 Extra 信息中已经没有“Using index for group-by”了,但并不是说 MySQL 的 GROUP BY 操作并不是通过索引完成的,只不过是需要访问 WHERE 条件所限定的所有索引键信息之后才能得出结果。这就是通过紧凑索引扫描来实现 GROUP BY 的执行计划输出信息。在 MySQL 中,MySQL Query Optimizer 首先会选择尝试通过松散索引扫描来实现 GROUP BY 操作,当发现某些情况无法满足松散索引扫描实现 GROUP BY 的要求之后,才会尝试通过紧凑索引扫描来实现。当 GROUP BY 条件字段并不连续或者不是索引前缀部分的时候,MySQL Query Optimizer 无法使用松散索引扫描。这时检查where 中的条件字段是否有索引的前缀部分,如果有此前缀部分,且该部分是一个常量,且与group by 后的字段组合起来成为一个连续的索引。这时按紧凑索引扫描。

SELECT max(gmt_create)FROM group_messageWHERE group_id = 2GROUP BY user_id

需读取group_id=2的所有数据,然后在读取的数据中完成group by操作得到结果。(这里group by 字段并不是一个连续索引,正好where 中group_id正好弥补缺失的索引键,又恰好是一个常量,因此使用紧凑索引扫描)group_id user_id 这个顺序是可以使用该索引。如果连接的顺序不符合索引的“最左前缀”原则,则不使用紧凑索引扫描。

以下例子使用紧凑索引扫描

GROUP BY中有一个差距,但已经由条件user_id = 1覆盖。explainSELECT group_id,gmt_createFROM group_messageWHERE user_id = 1 GROUP BY group_id,gmt_create

GROUP BY不以关键字的第1个元素开始,但是有一个条件提供该元素的常量explainSELECT group_id,gmt_createFROM group_messageWHERE group_id = 1 GROUP BY user_id,gmt_create

下面的例子都不使用紧凑索引扫描user_id,gmt_create 连接起来并不符合索引“最左前缀”原则explainSELECT group_id,gmt_createFROM group_messageWHERE user_id = 1 GROUP BY gmt_creategroup_id,gmt_create 连接起来并不符合索引“最左前缀”原则explainSELECT gmt_createFROM group_messageWHERE group_id=1 GROUP BY gmt_create;

3、使用临时表实现 GROUP BY

MySQL Query Optimizer 发现仅仅通过索引扫描并不能直接得到 GROUP BY 的结果之后,他就不得不选择通过使用临时表然后再排序的方式来实现 GROUP BY了。在这样示例中即是这样的情况。 group_id 并不是一个常量条件,而是一个范围,而且 GROUP BY 字段为 user_id。所以 MySQL 无法根据索引的顺序来帮助 GROUP BY 的实现,只能先通过索引范围扫描得到需要的数据,然后将数据存入临时表,然后再进行排序和分组操作来完成 GROUP BY。explainSELECT group_idFROM group_messageWHERE group_id between 1 and 4GROUP BY user_id;示例数据库文件

参考文献1、MySQL如何优化GROUP BY 2、详解MySQL分组查询Group By实现原理 3、松散的索引扫描(Loose index scan) 4、MySQL学习笔记

MySQL创建数据库的两种方法 本文为大家分享了两种MySQL创建数据库的方法,供大家参考,具体内容如下第一种方法:使用mysqladmin创建数据库使用普通用户,你可能需要特定的权限

MySQL 句柄数占用过多的解决方法 在Windows下安装MySQL,用了官方的配置向导生成了my.ini,本以为很安稳了,谁知十多个小时过去之后,系统响应非常慢,看资源管理器的性能卡,发现句

Hibernate4在MySQL5.1以上版本创建表出错 type=InnDB 在搭建springmvc框架时,底层使用hibernate4.1.8,数据库使用mysql5.1,使用hibernate自动生成数据库表时,hibernate方言使用org.hibernate.dialect.MySQLInnoDBDialect,自动

标签: Mysql优化sql方案

本文链接地址:https://www.jiuchutong.com/biancheng/347551.html 转载请保留说明!

上一篇:MySQL学习笔记小结(mysql学习教程)

下一篇:MySQL创建数据库的两种方法(MySQL创建数据库字符集和排序规则)

  • 购买需要安装的设备会计分录
  • 转租仓库交增值税吗
  • 所得税申报表营业成本填什么数据
  • 开民工工资专户
  • 不能收回的应收票据
  • 个税按照计提申报怎么调整
  • 异地开发房地产会一直待在那个城市吗
  • 接受捐赠计入什么费用
  • 子公司的财报和母公司的财报并表吗
  • 公司注销税务需要交多少钱
  • 工程交税必须在工程地点交吗
  • 对外支付佣金代扣代缴
  • 污水处理厂税收风险
  • 已认证未抵扣的发票,又做了红字,如何处理
  • 保险公司联合举办活动
  • 增值税纳税义务人
  • 简述房地产开发企业设立的特殊规定
  • 分公司发生修理费能否在总公司列支?
  • 补缴员工社保银行怎么查
  • 股权转让时未分配利润的税务处理
  • 生产车间消耗品管理表
  • photoshop人像磨皮方法
  • 公司车辆报废收入怎么入账
  • 民间非盈利组织会计信息的使用者
  • bios设置内存频率后黑屏
  • php 什么是类
  • 留存收益是否计入成本
  • php运用的技术php开发有哪些实用的技术
  • 结转销售原材料会计分录
  • 停车费过路费和汽油费可以一起报销吗
  • php7.3
  • 员工报销垫付的钱怎么入账
  • 多线程并发python
  • 培训机构先收费后付费
  • 企业所得税申报表在哪里打印
  • 审核出错问题在谁
  • 你用python做什么
  • 出口货物申报表如何填写
  • 建筑行业现在还能斤不
  • 研发费用明细科目怎么填
  • 仓库转租合同
  • 制单会计岗位风险点
  • 给慈善机构捐款英语
  • 冲以前年度成本分路
  • 公司注册资金抽走违法吗
  • 销售退回如何做账务处理
  • 物流费属于什么会计科目
  • 低于注册资本转股怎么办
  • 主营业务成本计入成本类账户吗
  • 融资租赁会计处理实例承租人
  • 代理记账服务业务内容
  • 税收优惠退回的税金怎么入账
  • 销售退回的账务处理红字
  • 支付设备款怎么做账
  • 账上的进项税额比申报多了怎么调账
  • 业务招待费税务扣除标准
  • 有销项税额转出吗
  • 建筑企业工程项目物资管理驼中华
  • mysql8 存储引擎
  • sql多条件组合查询
  • Win10系统怎么删除管理员权限
  • linux pb
  • linux那些事儿
  • 电脑太慢了怎么解决
  • mac的100个必备小技巧
  • linux 日历
  • 上传图片 js
  • replace函数怎么替换
  • JavaScript splice()方法详解
  • jquery的实现原理
  • jquery控制display属性
  • javascript教程 csdn
  • rsa 密钥格式
  • javascript操作网页
  • 税务局投诉怎么投诉
  • 企业所得税年报资产总额平均值怎么填写
  • 个人意外所得税税率
  • 销售哪些产品需要基金从业资格证?
  • 河北社保网上申报时间
  • 2020年南通居民医保缴费标准
  • 免责声明:网站部分图片文字素材来源于网络,如有侵权,请及时告知,我们会第一时间删除,谢谢! 邮箱:opceo@qq.com

    鄂ICP备2023003026号

    网站地图: 企业信息 工商信息 财税知识 网络常识 编程技术

    友情链接: 武汉网站建设