时间戳

首页 > 解决方案 > 大表的 MySQL 优化

mysql - 大表的 MySQL 优化

问题描述

如果需要，我可以添加更多详细信息，但基本上会遇到查询大表（1 亿多行）的问题。我的查询需要几分钟才能完成。大部分数据是以前的数据（即去年的销售数据），不会改变。我已经在我制作的其他报告中使用了这些数据，并且能够每晚将数据“汇总”到一个按月、年等分组的新表格中。但是，在我正在构建的报告中，有很多动态的诸如自定义时间/日期选择器之类的元素，这使我很难进行此类汇总。

我想我的问题是，有没有人对大表和动态查询有很多经验？

我也尽我所能完成了我的研究，我还确保我的数据库装备精良。目前 16gb 内存和 12gb InnoDB 缓冲池。（我不是这里的专家，所以让我知道是否还有其他需要寻找的东西）。

感谢任何人的帮助，如果您想了解我的用例的具体信息，请再次告诉我。

SELECT   mainaccounts.account_id AS 'ACCOUNTID', 
     ( 
            SELECT name 
            FROM   activitysettings 
            WHERE  org_id = '5a1da86ed6ea7c6000e45e82' 
            AND    id = '5a1da86ed6ea7c6000e45e8e' ) AS 'ACTIVITYNAME', 
     ( 
              SELECT   Count(DISTINCT a.id) 
              FROM     activity a 
              WHERE    a.org_id = '5a1da86ed6ea7c6000e45e82' 
              AND      ( 
                                a.started_at BETWEEN '2018-01-01' AND      '2018-02-01') 
              AND      a.status = true 
              AND      a.account_id = mainaccounts.account_id 
              GROUP BY a.account_id ) AS 'ACTIVITYTHIS', 
     ( 
              SELECT   Count(DISTINCT b.id) 
              FROM     activity b 
              WHERE    b.org_id = '5a1da86ed6ea7c6000e45e82' 
              AND      ( 
                                b.started_at BETWEEN '2017-01-01' AND      '2017-02-01') 
              AND      b.status = true 
              AND      b.account_id = mainaccounts.account_id 
              AND      b.activity_id = '5a1da86ed6ea7c6000e45e8e' 
              GROUP BY b.account_id ) AS 'ACTIVITYLAST', 
     ifnull( 
     ( 
              SELECT   Sum(s1.volumece) 
              FROM     sales s1 
              WHERE    s1.org_id = '5a1da86ed6ea7c6000e45e82' 
              AND      ( 
                                s1.invoice_date BETWEEN '2018-01-01'AND      '2018-02-01' 
                       AND      s1.status = true 
                       AND      s1.account_id = mainaccounts.account_id group BY s1.account_id ),
                       0) AS 'SALESTHIS', ifnull( 
     ( 
              SELECT   sum(s2.volumece) 
              FROM     sales s2 
              WHERE    s2.org_id = '5a1da86ed6ea7c6000e45e82' 
              AND      ( 
                                s2.invoice_date BETWEEN '2017-01-01' AND      '2017-02-01' 
                       AND      s2.status = TRUE 
                       AND      s2.account_id = mainaccounts.account_id GROUP BY s2.account_id ),
                       0) AS 'SALESLAST', @podthis := ifnull( 
     ( 
              SELECT   sum(s1.units) 
              FROM     sales s1 
              WHERE    s1.org_id = '5a1da86ed6ea7c6000e45e82' 
              AND      ( 
                                s1.invoice_date BETWEEN '2018-01-01'AND      '2018-02-01' 
                       AND      s1.status = TRUE 
                       AND      s1.account_id = mainaccounts.account_id GROUP BY s1.account_id ),
                       0) AS 'UNITSTHIS', @podlast :=ifnull( 
     ( 
              SELECT   sum(s2.units) 
              FROM     sales s2 
              WHERE    s2.org_id = '5a1da86ed6ea7c6000e45e82' 
              AND      ( 
                                s2.invoice_date BETWEEN '2017-01-01' AND      '2017-02-01') 
              AND      s2.status = TRUE 
              AND      s2.account_id = mainaccounts.account_id 
              GROUP BY s2.account_id ),0) AS 'UNITSLAST', 
     CASE 
              WHEN ( 
                                @podthis IS NULL 
                       OR       @podthis <= 0) THEN 0 
              ELSE 1 
     end AS 'ISPODTHIS', 
     CASE 
              WHEN ( 
                                @podlast IS NULL 
                       OR       @podlast <= 0) THEN 0 
              ELSE 1 
     end AS 'ISPODLAST' FROM activity mainaccounts WHERE 
     mainaccounts.org_id = '5a1da86ed6ea7c6000e45e82'
     AND      mainaccounts.started_at BETWEEN '2018-12-01' AND      
     '2018-12-31' 
     AND      mainaccounts.status = TRUE 
     AND      mainaccounts.activity_id = '5a1da86ed6ea7c6000e45e8e' 
    GROUP BY account_id

我有很多索引，所以请询问是否有您认为需要或有帮助的特定索引。

解释

标签： mysqloptimization

解决方案

汇总应该是当天的。这让任何日期范围都可以通过汇总汇总表来工作。

至于其他“动态”的东西，您需要已经构建包含可能的动态列的汇总表，并在汇总表上提供“足够的”索引。然后在 UI 中添加一些智能来选择适当的摘要表。

根据我的经验（您所描述的几个项目），选择摘要表中需要的列总是相当容易，甚至可以定制 UI 页面以引导用户选择可用的选项。偶尔会有一个新的请求进来；然后我编写新代码将原始数据汇总到一个新的摘要表中（或扩充现有的表），生成一个 UI，然后工作就完成了。

更多讨论

附带问题...

  • 涉及什么index2以及涉及哪些数据类型？我担心func在解释。

  • 范围

    在“2017-01-01”和“2017-02-01”之间开始

如果目标是 a DATE，您有 32 天。如果是DATETIME，则您有 31 天加一秒（额外的午夜）。我推荐这种模式；它适用于所有日期类型，并避免闰年（等）的麻烦：

    started_at >= '2017-01-01'
AND started_at  < '2017-01-01' + INTERVAL 1 MONTH
  • 索引

主账户是否有

INDEX(org_id, status, activity_id,  -- in any order
      started_at)   -- after the others

也就是说，先有=s，然后是“范围”。

活动需求

INDEX(org_id, status, account_id, activity_id,  -- in any order, then
      started_at)

推荐阅读