mysql - 如何在不进行非规范化的情况下优化数据库查询？

Question

我有一个 percona mysql 5.6.13 数据库，其中包含如下表:

CREATE TABLE `table1` (
  `table1_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`table1_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB;

CREATE TABLE `table2` (
  `table1_id` int(10) unsigned NOT NULL,
  `cost` decimal(6,2) NOT NULL DEFAULT '0.00',
  KEY `table1_id` (`table1_id`)
) ENGINE=InnoDB;


CREATE TABLE `table3` (
  `table1_id` int(10) unsigned NOT NULL,
  `partner` enum('partner1', 'partner2', 'partner3', 'partner4') NOT NULL DEFAULT 'partner1',
  KEY `table1_id` (`table1_id`)
) ENGINE=InnoDB;

每个表中都有大约 150 万行。

当我运行以下查询时，每次都需要 18 秒。

SELECT t3.partner, SUM(t2.cost) AS cost FROM table1 t1 JOIN table2 t2 ON t1.table1_id = t2.table1_id JOIN table3 t3 ON t1.table1_id = t3.table1_id WHERE t1.created_at >= '2005-07-01' AND t1.created_at < '2008-09-20' GROUP BY 1;

如果我将成本/合作伙伴字段非规范化为表 1，如下所示:

ALTER TABLE table1 ADD `cost` decimal(6,2) NOT NULL DEFAULT '0.00', ADD `partner` enum('partner1', 'partner2', 'partner3', 'partner4') NOT NULL DEFAULT 'partner1', ADD KEY `partner` (`partner`);
UPDATE table1 t1 JOIN table2 t2 ON t1.table1_id = t2.table1_id SET t1.cost = t2.cost;
UPDATE table1 t1 JOIN table3 t3 ON t1.table1_id = t3.table1_id SET t1.partner = t3.partner;

然后运行这个查询:

SELECT t1.partner, SUM(t1.cost) AS cost FROM table1 t1 WHERE t1.created_at >= '2005-07-01' AND t1.created_at < '2008-09-20' GROUP BY 1;

第一次需要 6 秒，之后每次需要 2 秒(可能是因为 mysql 缓存)。

我想我希望找到的可能是某种优化/缓存原始查询而不对数据进行非规范化的方法。
我不能只合并表格(因为示例中未包含其他字段，但我出于测试目的/在此处保持准确而删除了这些字段)。我可以跨表复制数据，但我不太喜欢这样做，而且似乎应该有比这更好的解决方案。
有什么数据库设置可以尝试吗？
也许 NoSQL 具有更完全非规范化的数据——在这种情况下，聚合是否会相当快地工作？
谢谢:)

附注一条评论要求查询计划——where 子句选择的行数就是所有行数。如果我省略 where，结果相同，这里是查询计划:

+----+-------------+-------+-------+--------------------+------------+---------+------------------------+--------+-----------------------------------------------------------+
| id | select_type | table | type  | possible_keys      | key        | key_len | ref                    | rows   | Extra                                                     |
+----+-------------+-------+-------+--------------------+------------+---------+------------------------+--------+-----------------------------------------------------------+
|  1 | SIMPLE      | t1    | range | PRIMARY,created_at | created_at | 5       | NULL                   | 766380 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | t3    | ref   | table1_id,partner  | table1_id  | 4       | lsfs_main.t1.table1_id |      1 | NULL                                                      |
|  1 | SIMPLE      | t2    | ref   | table1_id          | table1_id  | 4       | lsfs_main.t1.table1_id |      1 | NULL                                                      |
+----+-------------+-------+-------+--------------------+------------+---------+------------------------+--------+-----------------------------------------------------------+

Answer 1

您缺少 table2 和 table3 的主键。我建议至少为 table3 包含所有两列的多列主键。由于 InnoDB 表是按索引组织的表，这应该会显着减少 table3 的查找。有了这样的主键，MySQL 就可以直接从索引中检索所有相关数据，而无需进一步查找。字段 table1_id 必须在多列主键的第一个位置。

对于 table2 这并不容易，因为 (table1_id, cost) 不是唯一的。