Блог

У меня есть следующие 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 vCPU, 32 ГБ памяти, 250 ГБ SSD
• MySQL 8

Без всех функций СУММИРОВАНИЯ время 900 мс сокращается примерно на 250-300 мс. Без GROUP BY четного сокращения до 1-2-значных мс. К сожалению , мне нужны функции GROUP BY , а также количество функций СУММЫ.

В чем может быть причина того, что такому серверу требуется от 1 секунды до 2 секунд для выполнения запроса в таблице, содержащей всего 80 000 строк? В соответствии с explain analyze СУММОЙ функциям требуется 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, замечательных предложений нет).