MySQL查询优化组与MAX [已关闭] -- mysql 领域 和 database-design 领域 和 optimization 领域 和 group-by 领域 dba 相关 的问题

MySQL Query Optimization Group By with Max [closed]


1
vote

问题

中文
关闭。这个问题是 off-topic 。它目前不接受答案。

想要改进这个问题?更新问题,所以它是主题用于数据库管理员堆栈交换。

closed 1年前

改善这个问题

我在 stackoverflow.com 但我没有得到任何结构解决方案,所以我试图得到答案在DBA特定的社区。

尝试优化使用max使用组的mysql查询。试图在过去的日期之前获取最新更新的帧。

table audit_frame_master

  CREATE TABLE `audit_frame_master` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `audit_type` enum('INSERT','UPDATE','DELETE') NOT NULL,   `frame_id` bigint(20) NOT NULL,   `name` varchar(100) NOT NULL DEFAULT '',   `sort_order` int(11) DEFAULT '0',   `frame_type` varchar(45) DEFAULT NULL,   `frame_content_type` varchar(45) DEFAULT 'default',   `movable` tinyint(4) DEFAULT '0',   `frame_tags` varchar(512) DEFAULT NULL,   `active` tinyint(4) DEFAULT '1',   `frame_hint` varchar(256) DEFAULT NULL,   `size` double DEFAULT '0',   `lastModified` datetime DEFAULT NULL,   `createdOn` datetime DEFAULT NULL,   `updatedOn` datetime DEFAULT NULL,   `deletedOn` datetime DEFAULT NULL,   `optin_algorithm` tinyint(4) DEFAULT '1',   `optin_menu` tinyint(4) DEFAULT '1',   PRIMARY KEY (`id`),   KEY `framemaster_frame_type_idx` (`frame_type`),   KEY `fk_afm_frame_id_idx` (`frame_id`),   KEY `afm_updatedOn_idx` (`updatedOn`),   KEY `afm_composite_idx` (`active`,`updatedOn`,`deletedOn`),   KEY `afm_covering_idx` (`active`,`updatedOn`,`deletedOn`,`frame_id`,`id`,`sort_order`,`createdOn`),   CONSTRAINT `fk_afm_frame_id` FOREIGN KEY (`frame_id`) REFERENCES `frame_master` (`frame_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1079822 DEFAULT CHARSET=utf8;   

表audit_frame_association

  CREATE TABLE `audit_frame_association` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `audit_type` enum('INSERT','UPDATE') DEFAULT NULL,   `frame_id` bigint(20) NOT NULL,   `entity_id` bigint(20) NOT NULL,   `entity_type` int(11) NOT NULL,   `active` tinyint(4) NOT NULL,   `createdOn` datetime DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `afa_frame_id_idx` (`frame_id`),   KEY `fk_afa_entity_id_idx` (`entity_type`),   KEY `afa_createdOn_idx` (`createdOn`),   KEY `afa_composite_idx` (`createdOn`,`active`,`entity_type`,`entity_id`),   KEY `afa_covering_idx` (`createdOn`,`active`,`entity_type`,`entity_id`,`frame_id`,`id`),   CONSTRAINT `fk_afa_entity_type` FOREIGN KEY (`entity_type`) REFERENCES `entity_type_master` (`entity_type`) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT `fk_afa_frame_id` FOREIGN KEY (`frame_id`) REFERENCES `frame_master` (`frame_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=81166 DEFAULT CHARSET=utf8;   

查询

  SELECT fm.frame_id  FROM                 (SELECT *,MAX(id) as id1                               FROM audit_frame_master          WHERE updatedOn <='2019-03-13 10:49:29'          GROUP BY frame_id) fm,                 (SELECT *,MAX(id) as id1                               FROM audit_frame_association          WHERE createdOn <='2019-03-13 10:49:29'          AND (entity_id,entity_type) IN ((707,1))          GROUP BY frame_id,entity_id,entity_type) fa  WHERE fm.frame_id=fa.frame_id  AND fm.active=1  AND fm.deletedOn IS NULL  AND fa.active=1  GROUP BY fm.frame_id  ORDER BY fm.sort_order = 0, fm.sort_order ASC, fm.createdOn DESC;   

解释查询 在此处输入图像说明

性能:

audit_frame_master有1米行,从而取得超过一分钟来执行单个查询。

任何查询更改或任何DB模式建议,以提高性能将得到理解。

英文原文

I have posted same question on at stackoverflow.com but I did not get any structural solution so I am trying to get answer on dba specific community.

Trying to optimise MySQL query which uses Group by with Max. Trying to get latest updated frames which were active before any past date.

TABLE audit_frame_master

CREATE TABLE `audit_frame_master` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `audit_type` enum('INSERT','UPDATE','DELETE') NOT NULL,   `frame_id` bigint(20) NOT NULL,   `name` varchar(100) NOT NULL DEFAULT '',   `sort_order` int(11) DEFAULT '0',   `frame_type` varchar(45) DEFAULT NULL,   `frame_content_type` varchar(45) DEFAULT 'default',   `movable` tinyint(4) DEFAULT '0',   `frame_tags` varchar(512) DEFAULT NULL,   `active` tinyint(4) DEFAULT '1',   `frame_hint` varchar(256) DEFAULT NULL,   `size` double DEFAULT '0',   `lastModified` datetime DEFAULT NULL,   `createdOn` datetime DEFAULT NULL,   `updatedOn` datetime DEFAULT NULL,   `deletedOn` datetime DEFAULT NULL,   `optin_algorithm` tinyint(4) DEFAULT '1',   `optin_menu` tinyint(4) DEFAULT '1',   PRIMARY KEY (`id`),   KEY `framemaster_frame_type_idx` (`frame_type`),   KEY `fk_afm_frame_id_idx` (`frame_id`),   KEY `afm_updatedOn_idx` (`updatedOn`),   KEY `afm_composite_idx` (`active`,`updatedOn`,`deletedOn`),   KEY `afm_covering_idx` (`active`,`updatedOn`,`deletedOn`,`frame_id`,`id`,`sort_order`,`createdOn`),   CONSTRAINT `fk_afm_frame_id` FOREIGN KEY (`frame_id`) REFERENCES `frame_master` (`frame_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=1079822 DEFAULT CHARSET=utf8; 

TABLE audit_frame_association

CREATE TABLE `audit_frame_association` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `audit_type` enum('INSERT','UPDATE') DEFAULT NULL,   `frame_id` bigint(20) NOT NULL,   `entity_id` bigint(20) NOT NULL,   `entity_type` int(11) NOT NULL,   `active` tinyint(4) NOT NULL,   `createdOn` datetime DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `afa_frame_id_idx` (`frame_id`),   KEY `fk_afa_entity_id_idx` (`entity_type`),   KEY `afa_createdOn_idx` (`createdOn`),   KEY `afa_composite_idx` (`createdOn`,`active`,`entity_type`,`entity_id`),   KEY `afa_covering_idx` (`createdOn`,`active`,`entity_type`,`entity_id`,`frame_id`,`id`),   CONSTRAINT `fk_afa_entity_type` FOREIGN KEY (`entity_type`) REFERENCES `entity_type_master` (`entity_type`) ON DELETE NO ACTION ON UPDATE NO ACTION,   CONSTRAINT `fk_afa_frame_id` FOREIGN KEY (`frame_id`) REFERENCES `frame_master` (`frame_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=81166 DEFAULT CHARSET=utf8; 

Query

SELECT fm.frame_id  FROM                 (SELECT *,MAX(id) as id1                               FROM audit_frame_master          WHERE updatedOn <='2019-03-13 10:49:29'          GROUP BY frame_id) fm,                 (SELECT *,MAX(id) as id1                               FROM audit_frame_association          WHERE createdOn <='2019-03-13 10:49:29'          AND (entity_id,entity_type) IN ((707,1))          GROUP BY frame_id,entity_id,entity_type) fa  WHERE fm.frame_id=fa.frame_id  AND fm.active=1  AND fm.deletedOn IS NULL  AND fa.active=1  GROUP BY fm.frame_id  ORDER BY fm.sort_order = 0, fm.sort_order ASC, fm.createdOn DESC; 

EXPLAIN QUERY enter image description here

Performance:

audit_frame_master has 1M rows and thus taking more than a minute to execute single query.

Any query changes or any db schema suggestion to improve performance would be appreciated.

           
   
   

回答列表

相关问题

4  索引和查询Optimzation需要帮助  ( Help required with indexing and query optimzation ) 
浏览大部分帖子后,我在这里发布的选项后。 我有一个桌子,有76,222,954(76米)的行。它基于国家/地区收集网站流量。 这是我的表结构: CREATE TABLE `publication_month` ( `entity_id` int(11) NOT NULL, `category_id` in...

2  phpmyadmin慢一点小数据库  ( Phpmyadmin slow for small database ) 
我有一个小型数据库(+ 100 kB,当D转储时)将在PHPMyAdmin中挂起,当我尝试排序各种列时。 有时它会在尝试通过直接单击它们的内容编辑列时挂起。 我看到以下警告和想知道是否有人可以提供经验丰富的解释或在改进界面方面的任何帮助。 PHPMYADMIN配置存储未完全配置, 一些扩展功能已被停用。要了解为什...

1  MySQL实时备份的多个实例  ( Multiple instance of mysql realtime backup ) 
我当前的MySQL备份策略是复制。我在主机后面设置一个或多个从站。如果主设备失败,我仍然有一个干净,最新的数据副本从其中一个从站。它有效地没有任何问题。现在我开始拥有更多的主节点来管理,每个主节点都包含不同的数据。 现在,如果我想使用相同的备份策略,我需要为每个主设备设置至少一个从实例。它将要加倍MySQL节点的数量...

1  MySQL Workbench - 如何有效地查看大型数据库表?  ( Mysql workbench how to efficiently review large database tables ) 
我有一个带有900,000多行,20列的数据库表,大多数字符串数据。使用MySQL Workbench,我想通过表格的整个内容来看看究竟已插入表中的内容(我正在评论它,因为我相信我的代码以某种方式仅部分加载了CSV文件的内容,我想要了解为什么和在哪里停止加载整个CSV文件)。 问题是,由于DB表的尺寸有点大,运行"...

4  随附IOPS的AWS表现RDS与EC2  ( Aws performance of rds with provisioned iops vs ec2 ) 
是否有任何人用新的配置IOPS与EC2进行了AWS RDS的性能比较?我发现了大量的非高IOPS RDS VS EC2,但没有任何新的高IOPS功能在RDS中。 ...

3  MySQL非常长时间的查询  ( Mysql very long running queries ) 
我有一个非常大的dB(800 + MB转储文件)我导入到我本地服务器。这是来自女巫的WordPress DB,我需要提取某些帖子。里面有大约160,000个帖子。 目前我用MySQL工作台运行简单查询的MySQL Workbench与Join进行测试,它需要很多时间,因此长时间实际上是工作台停止处理。 这是一个例...

0  SQL查询总和函数加入  ( Sql query on sum function plus joins ) 
抱歉,如果此前已介绍,但我无法找到我的问题的一个例子 情况:说我有一个销售表并想要显示它们 步骤1) SELECT payment_id, amount, payment_date FROM payment WHERE payment_date >= '2018-01-01' AND payment_date ...

0  应使用哪些结构将数据存储有多个类别标识符?  ( What structure should be used to store data with multiple category identifiers ) 
我在mysql数据库中有一个表,其中有各种数据(为了举例说明,让我们提供以下内容: id |放置|手机|电子邮件|类别) 现在,我想重新设计"类别" 部分,以便能够包含多个(可搜索)类别的任何给定行。 例如,让我们说有一个名为"湖畔野餐区" 的地方。目前它被归类为"自然保护区" 。因此,当人们正在寻找自然保护区时,...

7  为什么MySQL在设置事务级别后请求共享锁,以便读取未提交  ( Why would mysql request a shared lock after setting transaction level to read un ) 
在多个并行批处理中运行一个处理作业,基本上读取大块行以更新几个 - 它可以在此处使用较低的交易级别,因为我知道此任务在此之前运行时不会更新相关值调用每个存储的proc我运行: set session transaction isolation level read uncommitted 然后调用存储的pro...

2  重新设计多实例MySQL服务器以进行性能和弹性  ( Redesigning a multi instance mysql server for performance and resiliency ) 
我有一个单独的mysql服务器,该服务器当前在单独的mysql实例上运行n,它正在快速增长超出该框的功能。 是否可以具有主动活动的MySQL群集,其中群集中的每个单个节点托管单个实例,但如果主托管节点发生故障,则具有一个或多个将失败到另一个节点的一个或多个实例? 示例 <强>电流布局 Connecti...

15  max_allowed_pa​​cket足够大,为什么我需要改变它?  ( What max allowed packet is big enough and why do i need to change it ) 
我在主从设置中有mysql(5.5),并创建了另一个从服务器。 我停止了原始从属,转储数据,复制并重新实现,它工作正常。我注意到了原始从站的Master_log POS,并使用这些命令将其设置为新的从站 CHANGE MASTER TO MASTER_HOST='<ipaddress>', MASTER_USE...

2  MySQL在连接失败时行为  ( Mysql behaviour on connection failure ) 
我的mysql数据库托管在来自我的应用程序系统的不同机器上,这些计算机远程向MySQL数据库发出查询。 如果出于某种原因,两台机器之间的网络连接中断,而在处理查询时,会发生什么? 我怀疑mysql将拒绝任何垃圾数据,但我不希望它保存在我的行中的垃圾数据,我不希望mysql陷入困境。 ...

1  当其他细胞也匹配时,MySQL组求解一个单元格  ( Mysql group concat one cell when other cells also match ) 
当来自其他COLUM的两个单元格也匹配时,我正在尝试连接单个小区的内容。例如,说我有一个如下所示的表(包括加入): +------------+--------------+-------------+--------------+ | sale_id | product_no | date_sold ...

0  MySQL:如何关闭日志记录影响性能?  ( Mysql how turning off the logging affect the performance ) 
OS: RHEL5 DB: MySQL 5.1.73 community edition DB size: ~1GB 我已经设置了测试环境并关闭所有日志记录,二进制日志,慢查询日志等。 我正在使用Oracle应用程序测试套件(OATS)来运行负载测试。在注销后,我正在获得较差的负载测试结果(更高的时序和较少...

7  需要检查表是否存在于删除记录之前存在  ( Need to check if a table exists before deleting a record ) 
我在我的Ruby应用程序中有以下SQL查询: sql = "DELETE FROM `#{database}`.`table1` WHERE `same_id` = #{some_id};" 问题是,在极少数情况下<代码> ABCDEFGHIJKLMNABCDEFGHIJKLMN10 可能不存在。我需要构建...




© 2021 it.wenda123.org All Rights Reserved. 问答之家 版权所有


Licensed under cc by-sa 3.0 with attribution required.