Блог

Я буду первым, кто признает, что я не силен в SQL (и, вероятно, мне не следует относиться к нему как к текущему файлу журнала), но мне было интересно, могу ли я получить несколько советов для улучшения некоторых медленных запросов…

У меня есть большая таблица mysql с 2 строками, в которой я выполняю два полных поиска таблиц на основе подмножества самых последних данных. Когда я загружаю страницу, содержащую эти запросы, я часто обнаруживаю, что их выполнение занимает несколько секунд, но запросы внутри довольно быстрые.

Советник PMA (предположительно ужасный) в значительной степени бросает на меня всю кухонную раковину, временные таблицы, слишком много видов, объединения без индексов (у меня даже нет объединений?), Чтение с фиксированной позиции, чтение следующей позиции, временные таблицы, записанные на диск… последнее особенно заставляет меня задуматься, не проблема ли это с настройкой, но я играл со всеми ручками и даже заплатил за управляемую услугу, которая, похоже, не помогла.

 CREATE TABLE `archive` (  `id` bigint UNSIGNED NOT NULL,  `ip` varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `service` enum('ssh','telnet','ftp','pop3','imap','rdp','vnc','sql','http','smb','smtp','dns','sip','ldap') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,  `hostid` bigint UNSIGNED NOT NULL,  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;  ALTER TABLE `archive`  ADD PRIMARY KEY (`id`),  ADD KEY `service` (`service`),  ADD KEY `date` (`date`),  ADD KEY `ip` (`ip`),  ADD KEY `date-ip` (`date`,`ip`),  ADD KEY `date-service` (`date`,`service`),  ADD KEY `ip-date` (`ip`,`date`),  ADD KEY `ip-service` (`ip`,`service`),  ADD KEY `service-date` (`service`,`date`),  ADD KEY `service-ip` (`service`,`ip`);  

Добавление индексов определенно помогло (хотя они в 4 раза превышают размер фактических данных), но я не знаю, где я могу оптимизировать дальше. Первоначально я думал о кэшировании результатов подзапроса в php и использовании его дважды для основных запросов, но я не думаю, что у меня будет доступ к результату, как только я закрою подзапрос. Я рассматривал возможность объединения, но они выглядят так, как будто предназначены для 2 или более отдельных таблиц, но подзапрос из одной и той же таблицы, поэтому я не уверен, что это тоже сработает. Предполагается, что запросы должны находить наиболее активные ip/службы на основе наличия у меня данных с ip-адреса за последние 24 часа…

 SELECT service, COUNT(service) AS total FROM `archive` WHERE ip IN (SELECT DISTINCT ip FROM `archive` WHERE date gt; DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)) GROUP BY service HAVING total gt; 1 ORDER BY total DESC, service ASC LIMIT 10   ---- -------------- ----------------- ------------ ------- ---------------------------------------------------------------------------- ------------ --------- ------------------------ ------- ---------- ---------------------------------  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |  ---- -------------- ----------------- ------------ ------- ---------------------------------------------------------------------------- ------------ --------- ------------------------ ------- ---------- ---------------------------------  | 1 | SIMPLE | lt;subquery2gt; | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | archive | NULL | ref | service,ip,date-service,ip-date,ip-service,service-date,service-ip | ip-service | 47 | lt;subquery2gt;.ip | 5 | 100.00 | Using index | | 2 | MATERIALIZED | archive | NULL | range | date,ip,date-ip,date-service,ip-date,ip-service | date-ip | 5 | NULL | 44246 | 100.00 | Using where; Using index |  ---- -------------- ----------------- ------------ ------- ---------------------------------------------------------------------------- ------------ --------- ------------------------ ------- ---------- ---------------------------------    

 SELECT ip, COUNT(ip) AS total FROM `archive` WHERE ip IN (SELECT DISTINCT ip FROM `archive` WHERE date gt; DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)) GROUP BY ip HAVING total gt; 1 ORDER BY total DESC, INET_ATON(ip) ASC LIMIT 10   ---- -------------- ----------------- ------------ ------- --------------------------------------------------------------- --------- --------- ------------------------ ------- ---------- ---------------------------------  | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |  ---- -------------- ----------------- ------------ ------- --------------------------------------------------------------- --------- --------- ------------------------ ------- ---------- ---------------------------------  | 1 | SIMPLE | lt;subquery2gt; | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | archive | NULL | ref | ip,date-ip,ip-date,ip-service,service-ip | ip-date | 47 | lt;subquery2gt;.ip | 5 | 100.00 | Using index | | 2 | MATERIALIZED | archive | NULL | range | date,ip,date-ip,date-service,ip-date,ip-service | date-ip | 5 | NULL | 44168 | 100.00 | Using where; Using index |  ---- -------------- ----------------- ------------ ------- --------------------------------------------------------------- --------- --------- ------------------------ ------- ---------- ---------------------------------    

общий подзапрос: 0,0351 с

весь запрос 1: 1.4270 с

весь запрос 2: 1.5601 с

общая загрузка страницы: 3,050 с (всего 7 запросов)

Неужели я просто обречен на ужасную производительность с этим столом?

Надеюсь, здесь достаточно информации, чтобы понять, что происходит, но если кто-нибудь может помочь, я, безусловно, буду признателен. Я не возражаю против того, чтобы использовать больше оборудования для решения этой проблемы, но когда сервер 8c/16t с 16 ГБ не может обрабатывать 150 МБ данных, я не уверен, что это произойдет. Заранее спасибо, что прочитали мой длинный вопрос.

У вас есть правильные индексы (а также многие другие индексы), и ваш запрос соответствует вашим спецификациям и работает почти оптимально. Маловероятно, что вы сможете сделать это намного быстрее: он должен полностью вернуться к началу вашего стола.

Если вы сможете изменить свою спецификацию так, чтобы вам приходилось оглядываться назад только ограниченное время, например, год, вы получите хорошее ускорение.

Некоторые возможные незначительные изменения.

• используйте параметры latin1_bin сортировки для своей ip колонки. Он использует 8-битные символы и сопоставляет их без учета регистра. Этого достаточно для адресов с точечными квадратами IPv4 (и адресов IPv6). Вы избавитесь от некоторых накладных расходов при сопоставлении и группировке. Или, что еще лучше,
• Если вы знаете, что у вас не будет ничего, кроме адресов IPv4, переработайте свой ip столбец, чтобы сохранить их двоичные представления ( то есть INET_ATON() сгенерированное значение каждого IPv4). Вы можете поместить их в UNSIGNED INT 32-разрядный целочисленный тип данных, что сделает поиск, группировку и упорядочение еще быстрее.

Можно переработать способ сбора этих данных. Например, вы можете организовать сбор не более одной строки на службу в день. Это уменьшит разрешение временных рядов ваших данных, но также значительно ускорит выполнение ваших запросов. Определите свою таблицу примерно так:

 CREATE TABLE archive2 (  ip VARCHAR(15) COLLATE latin1_bin NOT NULL,  service ENUM ('ssh','telnet','ftp',  'pop3','imap','rdp',  'vnc','sql','http','smb',  'smtp','dns','sip','ldap') COLLATE NOT NULL,  `date` DATE NOT NULL,  `count` INT NOT NULL,  hostid bigint UNSIGNED NOT NULL,  PRIMARY KEY (`date`, ip, service) ) ENGINE=InnoDB;  

Затем, когда вы вставляете строку, используйте этот запрос:

 INSERT INTO archive2 (`date`, ip, service, `count`, hostid)  VALUES (CURDATE(), ?ip, ?service, 1, ?hostid) ON DUPLICATE KEY UPDATE  SET count = count   1;  

Это автоматически увеличит ваш count столбец, если строка для ip , service , и date уже существует.

Тогда ваш второй запрос будет выглядеть так:

 SELECT ip, SUM(`count`) AS total  FROM archive   WHERE ip IN (  SELECT ip FROM archive   WHERE `date` gt; CURDATE() - INTERVAL 1 DAY  GROUP BY ip  HAVING total gt; 1  ) ORDER BY total DESC, INET_ATON(ip) ASC LIMIT 10;  

Индекс первичного ключа удовлетворит этому запросу.

Первый запрос

(Я не уверен, что это можно сделать намного быстрее.)

(в настоящее время)

 SELECT service, COUNT(service) AS total  FROM `archive`  WHERE ip IN (  SELECT DISTINCT ip  FROM `archive`  WHERE date gt; DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)  )  GROUP BY service  HAVING total gt; 1  ORDER BY total DESC, service ASC  LIMIT 10  

Примечания:

• COUNT(service) —gt; COUNT(*)
• DISTINCT не требуется в IN (SELECT DISTINCT ...)
• IN ( SELECT ... ) часто работает медленно; перепишите с помощью EXISTS ( SELECT 1 ... ) или JOIN (см. Ниже)
• INDEX(date, IP) — для подзапроса
• INDEX(service, IP) — для вашего внешнего запроса
• INDEX(IP, service) — для моего внешнего запроса
• Выбросьте избыточные индексы; они могут помешать. (См. ниже)
• Ему придется собрать все возможные результаты, прежде чем приступить к ORDER BY и LIMIT . (То есть LIMIT очень мало влияет на производительность этого запроса.)
• CHARACTER SET utf8 COLLATE utf8_unicode_ci является грубым перебором для IP-адресов; переключитесь на CHARACTER SET ascii COLLATE ascii_bin .
• Если вы используете MySQL 8.0 (или MariaDB 10.2), a WITH для вычисления подзапроса один раз вместе с a UNION для вычисления двух внешних запросов может обеспечить некоторую дополнительную скорость.
• В MariaDB есть «кэш подзапросов», который может привести к пропуску оценки второго подзапроса.
• Используя DATETIME вместо TIMESTAMP этого , вы столкнетесь с двумя незначительными сбоями в год, когда начнется/закончится летнее время.
• Я сомневаюсь, что hostid это должно быть BIGINT (8 байт).

Чтобы переключиться на a JOIN , подумайте о том, чтобы сначала выбрать строки-кандидаты:

 SELECT service, COUNT(*) AS total  FROM ( SELECT DISTINCT IP  FROM archive  WHERE `date` gt; NOW() - INTERVAL 24 HOUR  ) AS x  JOIN archive USING(IP)  GROUP BY service  HAVING total gt; 1  ORDER BY total DESC, service ASC  LIMIT 10  

 EXPLAIN SELECT ... EXPLAIN FORMAT=JSON SELECT ...  

 ADD KEY `service` (`service`),  ADD KEY `date` (`date`),  ADD KEY `ip` (`ip`),  

 ADD PRIMARY KEY (`id`),  -- as discussed:  ADD KEY `date-ip` (`date`,`ip`),  ADD KEY `ip-service` (`ip`,`service`),  ADD KEY `service-ip` (`service`,`ip`),  -- maybe other queries need these:  ADD KEY `date-service` (`date`,`service`),  ADD KEY `ip-date` (`ip`,`date`),  ADD KEY `service-date` (`service`,`date`),  

 SELECT ip, COUNT(DISTINCT ip) AS total  FROM `archive`  WHERE date gt; DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 24 HOUR)  GROUP BY ip  HAVING total gt; 1  ORDER BY total DESC, INET_ATON(ip) ASC  LIMIT 10