当前设置的数据库性能改进。(mysql - marriaDB)

Question

我目前有一个相当庞大的数据库(至少对我来说)，有超过1500万行的多个表。我试图提高整个表查找的性能，我实现memcached来缓存已经解析的结果，但是初始查找非常慢。20秒+在一个大的查询。

SELECT `something`, ROUND(SUM( ( `amount` / `something_of_value` ) * `column` ) WHERE ... (the where clause is huge based on many conditionals)电流设置：

一个用于大表10.0.36-MariaDB18GBram 8 x2.40 InnoDB CPU的托管vps服务器GHz存储。

我们不使用query_cache，因为25%的查询是写查询，经过一些研究，如果完成了写/更新查询，缓存就会被删除。这方法正确吗？

在不升级硬件的情况下，改进初始查找的最佳方法是什么？我可能遗漏了很多重要的细节，所以如果你需要更多的信息，就问问吧。如果你能引导我在一个好的总体方向，这也是值得赞赏的。我知道查询优化，但我们正在努力提高查找时间和一般mysql的性能。

编辑：

完整查询：

SELECT  `metric`, SUM( `amount` )
    FROM  big_table
    WHERE  1
      AND  (`metric` = '1' )
      AND  (`source` = 'some_unique_source'
              OR  `source` = 'some_other_unique_source'
              OR  `source` = 'yet_another_unique_source'
              OR  `source` = 'some_most_unique_source'
              OR  `source` = 'almost_last_unique_source'
              OR  `source` = 'the_last_unique_source' 
           )
      AND  (`platform` = '2'
              OR  `platform` = '1'
              OR  `platform` = '3' 
           )
      AND  (`account` = '1'
              OR  `account` = '2'
              OR  `account` = '3'
              OR  `account` = '4'
              OR  `account` = '5'
              OR  `account` = '6'
              OR  `account` = '7'
              OR  `account` = '8'
              OR  `account` = '9'
              OR  `account` = '10'
              OR  `account` = '11'
              OR  `account` = '12' 
           )
      AND  (`something_id` = 'some.unique.id' )
      AND  `target_date` >= '2018-08-27'
      AND  `target_date` <= '2018-08-27'
    GROUP BY  `metric`;

创建查询：

CREATE TABLE `big_table` (
  `stat_id` int(8) NOT NULL AUTO_INCREMENT,
  `metric` tinyint(1) NOT NULL DEFAULT '0',
  `source` varchar(16) DEFAULT NULL ,
  `platform` tinyint(1) NOT NULL DEFAULT '0' ,
  `account` int(2) DEFAULT NULL ,
  `something_id` varchar(128) DEFAULT 'EMPTY SOMETHING_ID',
  `target_date` date DEFAULT NULL,
  `country` varchar(2) DEFAULT NULL ,
  `version` varchar(16) DEFAULT NULL ,
  `amount` decimal(16,6) NOT NULL DEFAULT '0.000000' ,
  `tax` decimal(12,6) NOT NULL DEFAULT '0.000000' ,
  `currency` varchar(3) DEFAULT NULL,
  `currency_rate` decimal(12,6) DEFAULT '500.000000',
  `rate_updated` int(11) NOT NULL DEFAULT '0',
  `multiplier` decimal(16,6) NOT NULL DEFAULT '1.000000',
  `unique_key` varchar(180) DEFAULT NULL ,
  `caption` varchar(128) DEFAULT NULL,
  `transaction_timestamp` timestamp NULL DEFAULT NULL ,
  `finalised` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`stat_id`),
  UNIQUE KEY `unique_key` (`unique_key`) USING BTREE,
  KEY `target_date` (`target_date`,`currency`),
  KEY `index_country` (`country`),
  KEY `currency_rate` (`currency_rate`,`multiplier`)
) ENGINE=InnoDB AUTO_INCREMENT=43453089 DEFAULT CHARSET=utf8

编辑：

日期总是1天-脚本运行一个基于用户输入日期范围的预测日期。返回的mysql结果被解析为多维数组，然后在数据交换完成后解析为json文件。现在我考虑了一下，更好的方法可能是进行更智能的查询，其中的结果将按日期分组，尽管我不知道在速度方面有多少改进。有5个主选项卡，每个选项卡都选择不同的main内容--或者分组和选择帐户、度量、源、平台、国家和something_id，然后where子句也是从用户输入构建的，可能是不同的。这是一个自定义的分析仪表板，如果这有助于理解我们使用它的目的。

用户可以选择许多不同的选择，并根据用户的输入构造一个自定义查询。我减少了选择大小，排除了国家，因为默认情况下，它将所有>250个国家作为where子句加载，这使得查询长度变得非常荒谬和尴尬。为了澄清，所有国家都在默认情况下被标记为被选中。把它贴在答案上让我意识到它可能会被浪费地改进。删除国家，如果全部被选中，解析的负载时间从21秒~减少到8-10秒(30天的foreach循环，基本上是你在1400万个数据库行上看到的select )。虽然如果用户将至少一个国家排除在外，sql将使用250个where国家构建。我正在考虑一种算法，如果只有少数几个未选定的国家来创建where国家<>，而不是加载所有选定的国家，最终使查询的大小变小。

Answer 1

你没有1天，你有2天。如果你真的只想要一天，而且列是一个DATE，那就简单地说target_date = '2018-08-27'。这将有助于设计最优指标。

从=列开始(按任何顺序)：

INDEX(something_id, metric, target_date,
      xxxx)   -- then add one other column in the WHERE

如果只有3个“平台”，那么就省去它的测试。我假设您正在动态构建查询。因此，在UI中投入更多的精力来优化您所做的事情。

不幸的是，您可能需要多个索引来处理多个用例。超过10个指数是不合理的。因此，设计可能使用的索引，并从=列开始。不要费心使用比4列更长的索引。

其他问题：

• int(2) -- INT总是占用4个字节；为了节省空间，请参见TINYINT (等)。
• DEFAULT NULL -在适当的时候使用NOT NULL。
• 将ORs改为INs，这不会提高性能，但它会更干净。
• 在搬到IN之后，您可以说是country NOT IN (short-list)。
• 有评论提到了JOIN对LEFT JOIN，但我也没看到？

如果数据在插入后从未被修改，那么构建和维护汇总表就是最好的方法。经过精心设计，大约5个这样的表可以处理50个不同的查询。对这些数据的查询速度可能是原来的10倍。有关更多讨论，请提供可能的查询列表。

(我在丹布莱克的回答中添加了一些关于摘要表的评论。)

变体：

• foo LIKE '%something' --由于引导通配符，无法使用索引的foo部分
• foo <> 'blah'或foo NOT IN ('blah') --可能被视为“范围”，因此，如果索引太早，foo可能有害(性能)；如果最后一次使用，可能会有所帮助。
• country NOT IN ('Foostan')和country IN (very-long-list) --非常长的列表需要一些时间来解析，等等；所以NOT IN会稍微好一些。
• 取消“所有选项检查”--去做。优化者不能区分这和“一些检查”。所以，是的，这是一个潜在的优化。

一旦您做了类似的事情，下一个挑战将是选择一组有限的“复合”INDEXes --可能有5个索引，每个索引有3列。跟踪用户访问您的查询。慢速日志就是这样做的一种方法。

Answer 2

由于您将占用30天时间，您最好使用一个GROUP BY target_date并将target_date放在结果字段中，这将节省一些查询解析开销。

因为查询总是在target_dates的范围内迭代，这可能是索引开始时最好的一列。以避免查询返回到表空间以获取每一行以进一步过滤结果，或者作为结果(即amount)。索引应该以date开头，并包括where子句和amount的所有字段。由于这样做的结果是一个较大的索引，因此建议将其分别创建为现有的target_date索引。

要使该查询更快，当用户说选择一个国家，假设这是一个常见的情况，索引country，target_date，{其他字段}将有助于这种形式的查询。同样，如果单个metric、something_id或其他字段作为单个值选择(即x=5，而不是x=5 or x=9)是常见的。

Answer 3

选项2，基于创建后通常是静态的target_date数据，您可以根据日期创建一个汇总表。

CREATE TABLE `summary_of_big_table` (
  `target_date` date NOT NULL,
  `metric` tinyint(1) NOT NULL DEFAULT '0',
  `source` varchar(16) DEFAULT NULL ,
  `platform` tinyint(1) NOT NULL DEFAULT '0' ,
  `account` int(2) DEFAULT NULL ,
  `something_id` varchar(128) DEFAULT 'EMPTY SOMETHING_ID',
  `country` varchar(2) DEFAULT NULL ,
  `amount` decimal(16,6) NOT NULL DEFAULT '0.000000' ,
  PRIMARY KEY (`target_date`),
  KEY `index_country` (`country`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

字段的索引很可能是很少的单个值。

按所有参数和数量填充的will数据组共计：

INSERT INTO summary_of_big_table AS
SELECT target_date, metric, source,
  platform, account, something_id, country,
  SUM(amount) as amount
FROM big_table
GROUP BY target_date, metric, source,
  platform, account, something_id, country