我正在整理有关https://pennydreadfulmagic.com/seasons/all/cards/上的万智牌的牌组，获胜率和其他数据的页面
主SQL查询在生产中通常花费超过10s（在我的笔记本电脑上较少，但仍然> 5s）。服务器是运行openSUSE的标准Linode框，数据库是MariaDB（MySQL）。
我正在尝试确定在请求页面时在用户时间运行此查询是一个愚蠢的主意，还是我只需要正确的优化以使其在不到一秒钟的时间内运行。
该查询的简单版本是：
SELECT
card,
COUNT(*) AS num_decks,
SUM(CASE WHEN dm.games > IFNULL(odm.games, 0) THEN 1 ELSE 0 END) AS wins,
SUM(CASE WHEN dm.games < odm.games THEN 1 ELSE 0 END) AS losses,
SUM(CASE WHEN dm.games = odm.games THEN 1 ELSE 0 END) AS draws
FROM
deck_card AS dc
INNER JOIN
deck AS d ON dc.deck_id = d.id
INNER JOIN
deck_match AS dm ON d.id = dm.deck_id
INNER JOIN
deck_match AS odm ON dm.match_id = odm.match_id AND dm.deck_id <> odm.deck_id
GROUP BY
dc.card
ORDER BY
num_decks DESC,
card
每个表中大约有这么多行：
deck_card - 470,000 (DISTINCT card = 8,500)
deck - 20,000
match - 35,000
deck_match - 70,000
对于SQL来说，这些似乎不是很大的数字，这就是为什么我想知道使此查询运行得更快的原因是否现实。
说明：
+--------+-----+-------+------------------------+-------------------------------+-----+----------------------+-------+----------------------------------------------+
| id | type | tbl | type | possible_keys | key | len | ref | rows | Extra |
+------+--------+-----+-------+------------------------+------------------------+-----+----------------------+-------+----------------------------------------------+
| 1 | SIMPLE | d | index | PRIMARY | person_id | 4 | NULL | 18888 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | dm | ref | match_id,deck_id | deck_id | 4 | decksite.d.id | 1 | |
| 1 | SIMPLE | odm | ref | match_id | match_id | 4 | decksite.dm.match_id | 1 | Using where |
| 1 | SIMPLE | dc | ref | deck_id_card_sideboard | deck_id_card_sideboard | 4 | decksite.d.id | 10 | Using index |
+------+--------+-----+-------+------------------------+------------------------+-----+----------------------+-------+----------------------------------------------+
4 rows in set (0.00 sec)
我有一个较快的查询版本（仍然太慢），在这里我将match / deck_card内容拉入一个子查询，然后将其加入到主查询中，由于难以理解，这里省略了该主查询。这确实使事情变得越来越快，但没有一个接近理想的速度。
我并不一定要急于在这里进行所需的优化（尽管那也很好！），以至于了解在用户时间使用正确的优化来运行这样的查询是否现实？还是应该花时间寻找正确的缓存策略或对数据库进行非规范化？
创建表如下：
mysql> SHOW CREATE TABLE deck_card;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| deck_card | CREATE TABLE `deck_card` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deck_id` int(11) NOT NULL,
`card` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`n` int(11) NOT NULL,
`sideboard` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `deck_card_deck_id_card_sideboard` (`deck_id`,`card`,`sideboard`),
KEY `idx_card` (`card`),
KEY `idx_card_deck_id_sideboard_n` (`card`,`deck_id`,`sideboard`,`n`),
CONSTRAINT `deck_card_ibfk_1` FOREIGN KEY (`deck_id`) REFERENCES `deck` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=39407094 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE deck;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| deck | CREATE TABLE `deck` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`person_id` int(11) NOT NULL,
`source_id` int(11) NOT NULL,
`identifier` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` text COLLATE utf8mb4_unicode_ci,
`created_date` int(11) NOT NULL,
`updated_date` int(11) NOT NULL,
`competition_id` int(11) DEFAULT NULL,
`url` text COLLATE utf8mb4_unicode_ci,
`archetype_id` int(11) DEFAULT NULL,
`resource_uri` text COLLATE utf8mb4_unicode_ci,
`featured_card` text COLLATE utf8mb4_unicode_ci,
`score` int(11) DEFAULT NULL,
`thumbnail_url` text COLLATE utf8mb4_unicode_ci,
`small_thumbnail_url` text COLLATE utf8mb4_unicode_ci,
`finish` int(11) DEFAULT NULL,
`decklist_hash` char(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`retired` tinyint(1) DEFAULT '0',
`reviewed` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `deck_source_id_identifier` (`source_id`,`identifier`),
KEY `person_id` (`person_id`),
KEY `competition_id` (`competition_id`),
KEY `archetype_id` (`archetype_id`),
KEY `deck_hash` (`decklist_hash`),
CONSTRAINT `deck_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`),
CONSTRAINT `deck_ibfk_2` FOREIGN KEY (`source_id`) REFERENCES `source` (`id`),
CONSTRAINT `deck_ibfk_3` FOREIGN KEY (`competition_id`) REFERENCES `competition` (`id`),
CONSTRAINT `deck_ibfk_4` FOREIGN KEY (`archetype_id`) REFERENCES `archetype` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21460 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE deck_match;
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| deck_match | CREATE TABLE `deck_match` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`match_id` int(11) NOT NULL,
`deck_id` int(11) NOT NULL,
`games` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `match_id` (`match_id`),
KEY `deck_id` (`deck_id`),
CONSTRAINT `deck_match_ibfk_2` FOREIGN KEY (`deck_id`) REFERENCES `deck` (`id`),
CONSTRAINT `deck_match_ibfk_3` FOREIGN KEY (`match_id`) REFERENCES `match` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=73857 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
最佳答案
您得到“正确”的答案了吗？还是数字比他们应该的大？后者发生在执行JOIN，然后是GROUP BY时发生的“爆炸内爆”。
看看如果您改变会怎样
FROM deck_card AS dc
至
FROM ( SELECT DISTINCT card FROM deck_card ) AS dc