如何在不进行反错的情况下优化数据库查询？
如何在不进行反错的情况下优化数据库查询？
提问于 2013-10-31 01:11:45
我有一个PerconaMySQL5.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;如果我将成本/合作伙伴字段降为table1，如下所示：
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;然后运行以下查询：
从table1 t1中选择t1合作伙伴，SUM(t1成本)作为成本，其中t1.created_at >= '2005-07-01‘和t1.created_at < '2008-09-20’组为1；
第一次要花6秒，之后每次要花2秒(大概是因为mysql缓存)。
我想我希望找到的可能是一些优化/缓存原始查询的方法，而不对数据进行反错。
我不能仅仅合并这些表(因为示例中没有包含其他字段，但是为了测试/在这里准确起见，我删除了这些字段)。我可以在表中复制数据，但我不太喜欢这一点，似乎应该有一个更好的解决方案。
有什么数据库设置要尝试吗？
也许具有更完全非正规化数据的NoSQL --在这种情况下，聚集的速度会相当快吗？
谢谢:)
附注：有一个注释询问查询计划-- 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 |
+----+-------------+-------+-------+--------------------+------------+---------+------------------------+--------+-----------------------------------------------------------+回答 1
Stack Overflow用户
回答已采纳
发布于 2013-10-31 08:54:21
您丢失了table2和table3的主键。我建议至少为包含所有两列的table3提供一个多列主键。由于InnoDB-表是索引组织的表，这将大大减少对table3的查找。使用这样的主键，MySQL可以直接从索引中检索所有相关数据，而无需进一步查找。字段table1_id必须位于多列主键的第一个位置。
对于table2来说，这并不容易，因为(table1_id, cost)并不是唯一的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19696748
复制相关文章
相似问题
腾讯云开发者
