mysql - 在给定日期范围内优化多个组的MySQL查询求和和求平均值

coder 2023-10-11 原文

我目前正在开发一个自主开发的分析系统，目前在WindowsServer2008上使用MySQL5.6.10（很快就会迁移到Linux，我们在MySQL上也不是一成不变的，仍然在探索不同的选择，包括Hadoop）。
我们刚刚完成了一个巨大的导入，对于一个小客户来说，这是一个闪电般的快速查询，而对于一个大客户来说，现在却慢得让人无法忍受。我可能要添加一个全新的表来预计算这个查询的结果，除非我能找出如何使查询本身变得快速。
查询所做的是以@startdate和@enddate作为参数，并为该范围内的每一天计算日期、该日期的新评论数、正在运行的评论总数（包括@startdate之前的任何评论）和每日平均评分（如果没有关于gi的信息哪天，平均评级将从前一天结转）。
可用的筛选器包括年龄、性别、产品、公司和分级类型。每个评论都有1-n个等级，至少包含一个“总体”等级，但每个客户/产品可能有更多的等级，如“质量”、“音质”、“耐久性”、“价值”等。
调用此函数的api基于用户选择注入这些过滤器。如果没有指定rating类型，它将在下面列出的查询的所有三个部分中使用“and ratingtypeid=1”代替and子句注释。所有评级都是介于1和5之间的整数，尽管这对这个查询并不重要。
以下是我正在处理的表格：

CREATE TABLE `times` (
    `timeId` int(11) NOT NULL AUTO_INCREMENT,
    `date` date NOT NULL,
    `month` char(7) NOT NULL,
    `quarter` char(7) NOT NULL,
    `year` char(4) NOT NULL,
    PRIMARY KEY (`timeId`),
    UNIQUE KEY `date` (`date`)
) ENGINE=MyISAM

CREATE TABLE `reviewCount` (
    `companyId` int(11) NOT NULL,
    `productId` int(11) NOT NULL,
    `createdOnTimeId` int(11) NOT NULL,
    `ageId` int(11) NOT NULL,
    `genderId` int(11) NOT NULL,
    `totalReviews` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`companyId`,`productId`,`createdOnTimeId`,`ageId`,`genderId`),
    KEY `companyId_fk` (`companyId`),
    KEY `productId_fk` (`productId`),
    KEY `createdOnTimeId` (`createdOnTimeId`),
    KEY `ageId_fk` (`ageId`),
    KEY `genderId_fk` (`genderId`)
) ENGINE=MyISAM

CREATE TABLE `ratingCount` (
    `companyId` int(11) NOT NULL,
    `productId` int(11) NOT NULL,
    `createdOnTimeId` int(11) NOT NULL,
    `ageId` int(11) NOT NULL,
    `genderId` int(11) NOT NULL,
    `ratingTypeId` int(11) NOT NULL,
    `negativeRatings` int(10) unsigned NOT NULL DEFAULT '0',
    `positiveRatings` int(10) unsigned NOT NULL DEFAULT '0',
    `neutralRatings` int(10) unsigned NOT NULL DEFAULT '0',
    `totalRatings` int(10) unsigned NOT NULL DEFAULT '0',
    `ratingsSum` double unsigned DEFAULT '0',
    `totalRecommendations` int(10) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`companyId`,`productId`,`createdOnTimeId`,`ageId`,`genderId`,`ratingTypeId`),
    KEY `companyId_fk` (`companyId`),
    KEY `productId_fk` (`productId`),
    KEY `createdOnTimeId` (`createdOnTimeId`),
    KEY `ageId_fk` (`ageId`),
    KEY `genderId_fk` (`genderId`),
    KEY `ratingTypeId_fk` (`ratingTypeId`)
) ENGINE=MyISAM

从1900-01-01到2049-12-31，每天都预先填写“Times”表，这两个计数表由ETL脚本填充，其中包含按公司、产品、年龄、性别、等级类型等分组的汇总查询…
我对查询的期望是这样的：

Date        NewReviews  CumulativeReviewsCount  DailyRatingAverage
2013-01-24  7020        10586                   4.017514595496247
2013-01-25  5505        16091                   4.058400718778077
2013-01-27  2043        18134                   3.992957746478873
2013-01-28  3280        21414                   3.983625730994152
2013-01-29  4648        26062                   3.921597633136095
...
2013-03-09  1608        60297                   3.9409722222222223
2013-03-10  470         60767                   3.7743682310469313
2013-03-11  1028        61795                   4.036697247706422
2013-03-13  494         62289                   3.857388316151203
2013-03-14  449         62738                   3.8282208588957056

我很确定我可以预先计算出所有按年龄、性别等分组的数据，除了平均值，但我可能错了。如果我在一天内对两个产品进行了三次评估，所有其他组都不一样，一个组的评级是2和5，另一个是4，那么第一个组的日均评级是3.5，第二个是4。平均这些平均值可以得到3.75，而我期望得到3.66667。也许我可以做一些事情，比如把这一组的平均值乘以评论的数量，得到当天的总评分总和，再把它们相加，然后除以最后的总评分计数。似乎有很多额外的工作，但可能比我现在做的要快。说到这里，我现在的问题是：

SET @cumulativeCount :=
    (SELECT coalesce(sum(rc.totalReviews), 0)
        FROM reviewCount rc
        INNER JOIN times dt ON rc.createdOnTimeId = dt.timeId
        WHERE dt.date < @StartDate
        -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C#
    );

SET @dailyAverageWithCarry :=
    (SELECT SUM(rc.ratingsSum) / SUM(rc.totalRatings)
        FROM ratingCount rc
        INNER JOIN times dt ON rc.createdOnTimeId = dt.timeId
        WHERE dt.date < @StartDate
        AND rc.totalRatings > 0
        -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C#

        GROUP BY dt.timeId
        ORDER BY dt.date DESC LIMIT 1
    );

SELECT
    subquery.d AS `Date`,
    subquery.newReviewsCount AS `NewReviews`,
    (@cumulativeCount := @cumulativeCount + subquery.newReviewsCount) AS `CumulativeReviewsCount`,
    (@dailyAverageWithCarry := COALESCE(subquery.dailyRatingAverage, @dailyAverageWithCarry)) AS `DailyRatingAverage`
FROM
    (
        SELECT 
            dt.date AS d,
            COALESCE(SUM(rc.totalReviews), 0) AS newReviewsCount,
            SUM(rac.ratingsSum) / SUM(rac.totalRatings) AS dailyRatingAverage
        FROM times dt
        LEFT JOIN reviewCount rc ON dt.timeId = rc.createdOnTimeId
        LEFT JOIN ratingCount rac ON dt.timeId = rac.createdOnTimeId
        WHERE dt.date BETWEEN @StartDate AND @EndDate
        -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C#

        GROUP BY dt.timeId
        ORDER BY dt.timeId
    ) AS subquery;

查询当前运行大约需要2分钟，行数如下：

times       54787
reviewCount 276389
ratingCount 473683
age         122
gender      3
ratingType  28
product     70070

任何帮助都将不胜感激。我想让这个查询更快，或者如果这样做更快的话，预先计算按日期、年龄、性别、产品、公司和ratingtype分组的值，然后对该表进行快速汇总查询。
更新1：我尝试了Meherzad的建议，将索引添加到Times和RatingCount中：

ALTER TABLE times ADD KEY `timeId_date_key` (`timeId`, `date`);
ALTER TABLE ratingCount ADD KEY `createdOnTimeId_totalRatings_key` (`createdOnTimeId`, `totalRatings`);

然后再次运行我的初始查询，它大约快了1秒（~89秒），但仍然太慢。我试了迈赫扎德建议的问题，过了几分钟就把它干掉了。
根据要求，以下是我的查询的解释结果：

id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1|PRIMARY|<derived2>|ALL|NULL|NULL|NULL|NULL|6808032|NULL
2|DERIVED|dt|range|PRIMARY,timeId_date_key,date|date|3|NULL|88|Using index condition; Using temporary; Using filesort
2|DERIVED|rc|ref|PRIMARY,companyId_fk,createdOnTimeId|createdOnTimeId|4|dt.timeId|126|Using where
2|DERIVED|rac|ref|createdOnTimeId,createdOnTimeId_total_ratings_key|createdOnTimeId|4|dt.timeId|614|NULL

我检查了关于缓冲区大小的文章中提到的缓存读取未命中率，它是

Key_reads 58303
Key_read_requests 147411279
For a miss rate of 3.9551247635535405672723319902814e-4

更新2：已解决！指数确实起到了作用，所以我相信梅赫扎德的回答。实际上，最重要的是认识到，在同一个查询中计算滚动平均数和每日/累计审核计数是将这两个巨大的表连接在一起。我看到变量初始化是在两个单独的查询中完成的，于是决定尝试将两个大查询分离为子查询，然后根据timeid将它们连接起来。现在它在0.358s中运行，并带有以下查询：

SET @StartDate = '2013-01-24';
SET @EndDate = '2013-04-24';

SELECT 
    @StartDateId:=MIN(timeId), @EndDateId:=MAX(timeId)
FROM
    times
WHERE
    date IN (@StartDate , @EndDate);

SELECT 
    @CumulativeCount:=COALESCE(SUM(totalReviews), 0)
FROM
    reviewCount
WHERE
    createdOnTimeId < @StartDateId
    -- Add Filters
;

SELECT 
    @DailyAverage:=COALESCE(SUM(ratingsSum) / SUM(totalRatings), 0)
FROM
    ratingCount
WHERE
    createdOnTimeId < @StartDateId
        AND totalRatings > 0
        -- Add Filters
GROUP BY createdOnTimeId
ORDER BY createdOnTimeId DESC
LIMIT 1;

SELECT 
    t.date AS `Date`,
    COALESCE(q1.newReviewsCount, 0) AS `NewReviews`,
    (@CumulativeCount:=@CumulativeCount + COALESCE(q1.newReviewsCount, 0)) AS `CumulativeReviewsCount`,
    (@DailyAverage:=COALESCE(q2.dailyRatingAverage,
            COALESCE(@DailyAverage, 0))) AS `DailyRatingAverage`
FROM
    times t
        LEFT JOIN
    (SELECT 
        rc.createdOnTimeId AS createdOnTimeId,
            COALESCE(SUM(rc.totalReviews), 0) AS newReviewsCount
    FROM
        reviewCount rc
    WHERE
        rc.createdOnTimeId BETWEEN @StartDateId AND @EndDateId
        -- Add Filters
    GROUP BY rc.createdOnTimeId) AS q1 ON t.timeId = q1.createdOnTimeId
        LEFT JOIN
    (SELECT 
        rc.createdOnTimeId AS createdOnTimeId,
            SUM(rc.ratingsSum) / SUM(rc.totalRatings) AS dailyRatingAverage
    FROM
        ratingCount rc
    WHERE
        rc.createdOnTimeId BETWEEN @StartDateId AND @EndDateId
        -- Add Filters
    GROUP BY rc.createdOnTimeId) AS q2 ON t.timeId = q2.createdOnTimeId
WHERE
    t.timeId BETWEEN @StartDateId AND @EndDateId;

我原以为两个子查询会非常慢，但它们的速度非常快，因为它们没有连接完全不相关的行。它还指出了一个事实，那就是我之前的结果还远远不够。例如，从上面：

Date        NewReviews  CumulativeReviewsCount  DailyRatingAverage
2013-01-24  7020        10586                   4.017514595496247

应该是，现在是：

Date        NewReviews  CumulativeReviewsCount  DailyRatingAverage
2013-01-24  599         407327                  4.017514595496247

平均值是正确的，但是join将新评论和累积评论的数量都搞砸了，我用一个查询验证了这一点。
我还去掉了与times表的连接，而是在一个快速初始化查询中确定开始和结束日期id，然后在最后重新连接到times表。
现在的结果是：

Date        NewReviews  CumulativeReviewsCount  DailyRatingAverage
2013-01-24  599         407327                  4.017514595496247
2013-01-25  551         407878                  4.058400718778077
2013-01-26  455         408333                  3.838926174496644
2013-01-27  433         408766                  3.992957746478873
2013-01-28  425         409191                  3.983625730994152
...
2013-04-13  170         426066                  3.874239350912779
2013-04-14  182         426248                  3.585714285714286
2013-04-15  171         426419                  3.6202531645569622
2013-04-16  0           426419                  3.6202531645569622
2013-04-17  0           426419                  3.6202531645569622
2013-04-18  0           426419                  3.6202531645569622
2013-04-19  0           426419                  3.6202531645569622
2013-04-20  0           426419                  3.6202531645569622
2013-04-21  0           426419                  3.6202531645569622
2013-04-22  0           426419                  3.6202531645569622
2013-04-23  0           426419                  3.6202531645569622
2013-04-24  0           426419                  3.6202531645569622

最后的几个平均值也正确地携带了前面的那些，因为我们在大约10天内还没有从该客户的数据馈送中导入数据。
谢谢你的帮助！

最佳答案

尝试此查询
您没有必要的索引来优化查询
表times在(timeId, dateId)上添加复合索引
表ratingCount在(createdOnTimeId, totalRatings)上添加复合索引
正如您已经提到的，您正在根据用户输入使用各种其他的AND过滤器，因此请为这些列创建一个复合索引，其顺序是为它们各自的表ex tableratingCount复合索引(createdOnTimeId, totalRatings, ratingType, age, gender, product, and company)添加的顺序。NOTE只有在查询中添加这些约束时，此索引才有用。
我还将检查以确保缓冲池足够大，可以容纳索引。您不希望索引在查询期间在缓冲池中进行分页。
检查缓冲池大小
BUFFER_SIZE
如果您在性能上没有发现任何改进，请为您的查询发布explain语句，这将有助于正确理解问题。
我试着理解你的问题，并做了一个新的检查，它是否有效。

 SELECT 
   * 
 FROM
 (SELECT
  dt.timeId 
  dt.date,
  COALESCE(SUM(rc.totalReviews), 0) AS `NewReviews`,
  (@cumulativeCount := @cumulativeCount + subquery.newReviewsCount) AS    `CumulativeReviewsCount`,
  (@dailyAverageWithCarry := COALESCE(SUM(rac.ratingsSum) / SUM(rac.totalRatings), @dailyAverageWithCarry)) AS `DailyRatingAverage`
  FROM
    times dt
  LEFT JOIN 
    reviewCount rc 
  ON 
    dt.timeId = rc.createdOnTimeId
  LEFT JOIN 
    ratingCount rac ON dt.timeId = rac.createdOnTimeId
  JOIN
    (SELECT @cumulativeCount:=0, @dailyAverageWithCarry:=0) tmp
  WHERE 
    dt.date < @EndDate
    -- AND clause for filtering by ratingType (default 1), age, gender, product, and company is injected here in C#

  GROUP BY 
    dt.timeId
  ORDER BY 
    dt.timeId
 ) AS subquery
 WHERE
    subquery.date>@StartDate;

希望这有帮助…

关于mysql - 在给定日期范围内优化多个组的MySQL查询求和和求平均值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/16205988/

求和给定 createdOnTimeId code timeId mysql sql optimization query-optimization

有关mysql - 在给定日期范围内优化多个组的MySQL查询求和和求平均值的更多相关文章

ruby-on-rails - Rails 3 中的多个路由文件 - 2
Rails2.3可以选择随时使用RouteSet#add_configuration_file添加更多路由。是否可以在Rails3项目中做同样的事情？最佳答案在config/application.rb中:config.paths.config.routes在Rails3.2(也可能是Rails3.1)中，使用:config.paths["config/routes"] 关于ruby-on-rails-Rails3中的多个路由文件，我们在StackOverflow上找到一个类似的问题
ruby-on-rails - 在 Ruby 中循环遍历多个数组 - 2
我有多个ActiveRecord子类Item的实例数组，我需要根据最早的事件循环打印。在这种情况下，我需要打印付款和维护日期，如下所示:ItemAmaintenancerequiredin5daysItemBpaymentrequiredin6daysItemApaymentrequiredin7daysItemBmaintenancerequiredin8days我目前有两个查询，用于查找maintenance和payment项目(非排他性查询)，并输出如下内容:paymentrequiredin...maintenancerequiredin...有什么方法可以改善上述(丑陋的)代
ruby-on-rails - Rails - 一个 View 中的多个模型 - 2
我需要从一个View访问多个模型。以前，我的links_controller仅用于提供以不同方式排序的链接资源。现在我想包括一个部分(我假设)显示按分数排序的顶级用户(@users=User.all.sort_by(&:score))我知道我可以将此代码插入每个链接操作并从View访问它，但这似乎不是“ruby方式”，我将需要在不久的将来访问更多模型。这可能会变得很脏，是否有针对这种情况的任何技术？注意事项:我认为我的应用程序正朝着单一格式和动态页面内容的方向发展，本质上是一个典型的网络应用程序。我知道before_filter但考虑到我希望应用程序进入的方向，这似乎很麻烦。最终从任何
ruby - 多个属性的 update_column 方法 - 2
我有一个具有一些属性的模型:attr1、attr2和attr3。我需要在不执行回调和验证的情况下更新此属性。我找到了update_column方法，但我想同时更新三个属性。我需要这样的东西:update_columns({attr1:val1,attr2:val2,attr3:val3})代替update_column(attr1,val1)update_column(attr2,val2)update_column(attr3,val3) 最佳答案您可以使用update_columns(attr1:val1,attr2:val2
ruby-on-rails - 在 ruby .gemspec 文件中，如何指定依赖项的多个版本？ - 2
我正在尝试修改当前依赖于定义为activeresource的gem:s.add_dependency"activeresource","~>3.0"为了让gem与Rails4一起工作，我需要扩展依赖关系以与activeresource的版本3或4一起工作。我不想简单地添加以下内容，因为它可能会在以后引起问题:s.add_dependency"activeresource",">=3.0"有没有办法指定可接受版本的列表？~>3.0还是~>4.0？最佳答案根据thedocumentation,如果你想要3到4之间的所有版本，你可以这
ruby-on-rails - date_field_tag，如何设置默认日期？ [ rails 上的 ruby ] - 2
我想设置一个默认日期，例如实际日期，我该如何设置？还有如何在组合框中设置默认值顺便问一下，date_field_tag和date_field之间有什么区别？最佳答案试试这个:将默认日期作为第二个参数传递。youcorrectlysetthedefaultvalueofcomboboxasshowninyourquestion. 关于ruby-on-rails-date_field_tag，如何设置默认日期？[rails上的ruby]，我们在StackOverflow上找到一个类似的问
ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2
我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。最佳答案我不太明白你的问题。我假设您想检查
ruby - 检查日期是否在过去 7 天内 - 2
我的日期格式如下:"%d-%m-%Y"(例如，今天的日期为07-09-2015)，我想看看是不是在过去的七天内。谁能推荐一种方法？最佳答案你可以这样做:require"date"Date.today-7 关于ruby-检查日期是否在过去7天内，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.com/questions/32438063/
ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2
这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build
ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2
我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

mysql - 在给定日期范围内优化多个组的MySQL查询求和和求平均值

有关mysql - 在给定日期范围内优化多个组的MySQL查询求和和求平均值的更多相关文章

随机推荐