我在页面上总结了牌组，赢率和其他有关魔术卡的数据https://pennydreadfulmagic.com/seasons/all/cards/
主sql查询通常在生产上花费10秒以上（在我的笔记本电脑上更少，但仍然>5秒）。服务器是一个运行opensuse的标准linode框，数据库是mariadb（mysql）。
我试图弄清楚，在请求页面时在用户时间运行这个查询是一个愚蠢的想法，还是我只需要适当的优化就可以在不到一秒钟的时间内工作。
阿娜ï查询的ve版本为：
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\u 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)
2条答案
按热度按时间9ceoxa921#
你得到“正确”的答案了吗？或者这些数字比他们应该的要大？后者发生在“爆炸-内爆”发生时
JOIN，那么GROUP BY.看看如果你改变了会发生什么
到
cotxawn72#
我并没有完全解决这个问题，但我确实将应用程序这部分的性能从大约10秒提高到了不到1秒。
我用了一个类似上面的查询，但“扣”了一天(
GROUP BY card, DATE(d.created_date))创建_card_stats我定期重新生成的表。当我需要这些数据时，我现在可以更快地从带方框的表中汇总上周/季节/所有时间的数据。不是我梦想的那样，但是相当可行，代价是更新有点复杂/滞后。