是什么让这个查询如此缓慢?150000个受影响的行需要1.5秒以上的时间

Question

我已经被这个查询卡住两天多了。我已经尝试了子查询(在SELECT中(；用LEFT join和CCD_ 1(NULL＝0(连接所有内容"；。

活动表：

CREATE TABLE `campaigns` (
`id` bigint(20) UNSIGNED NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `campaigns`
ADD PRIMARY KEY (`id`);

ALTER TABLE `campaigns`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

操作系统表：

CREATE TABLE `operating_systems` (
`id` bigint(20) UNSIGNED NOT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`version` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `operating_systems`
ADD PRIMARY KEY (`id`),
ADD KEY `operating_systems_name_index` (`name`),
ADD KEY `operating_systems_version_index` (`version`),
ADD KEY `operating_systems_name_version_index` (`name`,`version`);
ALTER TABLE `operating_systems`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

点击表：

CREATE TABLE `clicks` (
`id` bigint(20) UNSIGNED NOT NULL,
`campaign_id` bigint(20) UNSIGNED NOT NULL,
`operating_system_id` bigint(20) UNSIGNED NOT NULL,
`cost_integral` int(11) NOT NULL DEFAULT 0,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `clicks`
ADD PRIMARY KEY (`id`),
ADD KEY `c_c_ca` (`campaign_id`,`created_at`),
ADD KEY `c_c_os_ca` (`campaign_id`,`operating_system_id`,`created_at`);
ALTER TABLE `clicks`
ADD CONSTRAINT `c_c` FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `c_os` FOREIGN KEY (`operating_system_id`) REFERENCES `operating_systems` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `clicks`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

转换表：

CREATE TABLE `conversions` (
`id` bigint(20) UNSIGNED NOT NULL,
`click_id` bigint(20) UNSIGNED NOT NULL,
`payout_integral` int(11) NOT NULL,
`created_at` timestamp NULL DEFAULT NULL,
`updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `conversions`
ADD PRIMARY KEY (`id`),
ADD KEY `conversions_created_at_index` (`created_at`),
ADD KEY `conversions_click_id_created_at_index` (`click_id`,`created_at`);
ALTER TABLE `conversions`
ADD CONSTRAINT `conversions_click_id_foreign` FOREIGN KEY (`click_id`) REFERENCES `clicks` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `conversions`
MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

慢速查询：

select operating_systems.name,
ifnull(sum(metrics.clicks_count), 0) as clicks_count,
ifnull(sum(metrics.conversions_count), 0) as conversions_count,
ifnull(round(((100 / sum(metrics.clicks_count)) * sum(metrics.conversions_count)), 2), 0) as conversion_rate,
cast(ifnull(sum(metrics.cost_integral), 0) as unsigned) as cost_integral,
cast(ifnull((sum(metrics.cost_integral) / sum(metrics.clicks_count)), 0) as unsigned) as cpc_integral,
cast(ifnull(sum(metrics.revenue_integral), 0) as unsigned) as revenue_integral,
cast(ifnull((ifnull(sum(metrics.revenue_integral), 0) / sum(metrics.clicks_count)), 0) as unsigned) as epc_integral,
cast((ifnull(sum(metrics.revenue_integral), 0) - ifnull(sum(metrics.cost_integral), 0)) as signed) as profit_integral,
round((
case 
when (ifnull(sum(metrics.revenue_integral), 0) = 0 and ifnull(sum(metrics.cost_integral), 0) = 0)
then 0
when (ifnull(sum(metrics.revenue_integral), 0) = 0 and ifnull(sum(metrics.cost_integral), 0) > 0)
then - 100
when (ifnull(sum(metrics.revenue_integral), 0) > 0 and ifnull(sum(metrics.cost_integral), 0) = 0)
then 100
when (ifnull(sum(metrics.revenue_integral), 0) > 0 and ifnull(sum(metrics.cost_integral), 0) > 0)
then ((sum(metrics.revenue_integral) / sum(metrics.cost_integral)) * 100)
end
), 2) as roi
from operating_systems
left join (
select c1.operating_system_id as operating_system_id,
count(c1.id) as clicks_count,
sum(c1.cost_integral) as cost_integral,
count(c2.id) as conversions_count,
sum(c2.payout_integral) as revenue_integral
from clicks as c1
left join conversions as c2 on c2.click_id = c1.id
where c1.campaign_id = '2' and c1.created_at >= '2021-07-06 00:00:00' and c1.created_at <= '2021-07-14 23:59:59'
group by c1.operating_system_id
) as metrics on operating_systems.id = metrics.operating_system_id
group by operating_systems.name;

解释：

表类型可能的_keys键key_len参考行筛选额外>>操作系统名称索引，操作系统名称版本索引操作程序名称索引>1022空<12>使用索引tr>NULLNULLc_os、c_ca、c_c_v、c_c_ca、c_c_b_ca、c_os_ca8常量使用索引条件conversations_click_id_created_at_index<1td>><2td>dn_tracker.c1.id1<10.00>NULL

id	select_type	分区
1	PRIMARY	操作系统	NULL	索引	1	PRIMARY		NULL	ref	<auto_key0>	<auto_key0>	8	dn_tracker.operating_systems.id	777	100.00
2	DERIVED	c1	ref	c_os_ca
2	DERIVED	c2	NULL	ref	converstions_click_id_created_at_inndex	8

构建并维护一个汇总表。每晚午夜过后，将当天的数据添加到汇总表中。然后重写这个"；报告"；根据汇总表进行工作。

更多详细信息：http://mysql.rjweb.org/doc.php/summarytables

在某些情况下，汇总表会将"报告"的速度提高10倍。

此外，这可能会有所帮助：

conversions: INDEX(click_id, payout_integral)

MySQL基本上没有并行执行。