我有以下 2 个表(browsers
和 metrics
)。 browsers
是一个“维度表”,用于存储浏览器的名称和版本。 metrics
是一个“事实表”,其中包含 browser_id
和指标以及日期。根据 explain select (...)
,metrics
上没有使用键,而 browsers
上使用主键。
SELECT browsers.name AS browser_name,SUM(visits_count) AS visits_count,SUM(clicks_count) AS clicks_count,IFNULL((100 / SUM(visits_count)) * SUM(clicks_count),0) AS ctr,SUM(cost_integral) AS cost_integral,IFNULL((SUM(cost_integral) / SUM(visits_count)),0) AS cpv_integral,IFNULL((SUM(cost_integral) / SUM(clicks_count)),0) AS cpc_integral,SUM(conversions_count) AS conversions_count,IFNULL((100 / SUM(clicks_count)) * conversions_count,0) AS cvr,SUM(revenue_integral) AS revenue_integral,IFNULL((SUM(revenue_integral) / SUM(clicks_count)),0) AS epc_integral,(SUM(revenue_integral) - SUM(cost_integral)) AS profit_integral,IFNULL((SUM(revenue_integral) - SUM(cost_integral)) / SUM(cost_integral) * 100,0) AS roi
FROM metrics
JOIN browsers ON browsers.id = browser_id
GROUP BY browsers.name
服务器:
- 8 个虚拟 CPU、32 GB 内存、250 GB SSD
- MySQL 8
没有所有的SUM函数,900ms的时间减少了大约250到300ms。没有 GROUP BY
甚至低至 1 到 2 位毫秒。不幸的是,我需要 GROUP BY
以及 SUM 函数的数量。
这样的服务器需要 1 秒到 2 秒才能在只有 80,000 行的表上执行查询的原因是什么?根据 explain analyze
,SUM 函数总共需要 96% 的时间 (actual time=845.038..845.052
)。
-- browsers-Table
CREATE TABLE `browsers` (
`id` bigint(20) UNSIGNED NOT NULL,`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,`version` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `browsers`
ADD PRIMARY KEY (`id`),ADD KEY `b_n` (`name`),ADD KEY `b_v` (`version`),ADD KEY `b_n_v` (`name`,`version`),ADD KEY `b_v_n` (`version`,`name`);
ALTER TABLE `browsers`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
-- metrics-Table
CREATE TABLE `metrics` (
`reference_date` date NOT NULL,`browser_id` bigint(20) UNSIGNED NOT NULL,`visits_count` bigint(20) NOT NULL DEFAULT 0,`cost_integral` bigint(20) NOT NULL DEFAULT 0,`clicks_count` bigint(20) NOT NULL DEFAULT 0,`conversions_count` bigint(20) NOT NULL DEFAULT 0,`revenue_integral` bigint(20) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `metrics`
ADD UNIQUE KEY `mu` (`reference_date`,`browser_id`),ADD KEY `metrics_browser_id_foreign` (`browser_id`);
ALTER TABLE `metrics`
ADD CONSTRAINT `metrics_browser_id_foreign` FOREIGN KEY (`browser_id`) REFERENCES `browsers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
即使在我的本地服务器上,使用相同的数据,我也只需要大约 10 毫秒 - 所以我怀疑服务器设置有问题(根据 mysqltuner 没有显着的建议)。