Блог

У меня медленный запрос, без group by выполняется быстро (0,1-0,3 секунды), но с (обязательной) group by длительность составляет около 10-15 секунд.

Запрос объединяет две таблицы, events (около 50 миллионов строк) и events_locations (5 миллионов строк).

Запрос:

 SELECT  `e`.`id` AS `event_id`,`e`.`time_stamp` AS `time_stamp`,`el`.`latitude` AS `latitude`,`el`.`longitude` AS `longitude`,
        `el`.`time_span` AS `extra`,`e`.`entity_id` AS `asset_name`, `el`.`other_id` AS `geozone_id`,
        `el`.`group_alias` AS `group_alias`,`e`.`event_type_id` AS `event_type_id`,
        `e`.`entity_type_id`AS `entity_type_id`, el.some_id
FROM events e
INNER JOIN events_locations el ON el.event_id = e.id
WHERE 1=1       
    AND el.other_id = '1'  
    AND time_stamp >= '2018-01-01'  
    AND time_stamp <= '2019-06-02'
GROUP BY `e`.`event_type_id` , `el`.`some_id` , `el`.`group_alias`;
  

События таблицы:

 CREATE TABLE `events` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `event_type_id` int(11) NOT NULL,
  `entity_type_id` int(11) NOT NULL,
  `entity_id` varchar(64) NOT NULL,
  `alias` varchar(64) NOT NULL,
  `time_stamp` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `entity_id` (`entity_id`),
  KEY `event_type_idx` (`event_type_id`),
  KEY `idx_events_time_stamp` (`time_stamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

Таблица events_locations

 CREATE TABLE `events_locations` (
  `event_id` bigint(20) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `some_id` bigint(20) DEFAULT NULL,
  `other_id` bigint(20) DEFAULT NULL,
  `time_span` bigint(20) DEFAULT NULL,
  `group_alias` varchar(64) NOT NULL,
  KEY `some_id_idx` (`some_id`),
  KEY `idx_events_group_alias` (`group_alias`),
  KEY `idx_event_id` (`event_id`),
  CONSTRAINT `fk_event_id` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

Объяснение:

  ---- ------------- ------- -------- --------------------------------- --------- --------- ------------------------------------------- ---------- ------------------------------------------------ 
| id | select_type | table | type   | possible_keys                   | key     | key_len | ref                                       | rows     | Extra                                          |
 ---- ------------- ------- -------- --------------------------------- --------- --------- ------------------------------------------- ---------- ------------------------------------------------ 
| 1  | SIMPLE      | ea    | ALL    | 'idx_event_id'                  | NULL    | NULL    | NULL                                      | 5152834  | 'Using where; Using temporary; Using filesort' |
| 1  | SIMPLE      | e     | eq_ref | 'PRIMARY,idx_events_time_stamp' | PRIMARY | '8'     | 'name.ea.event_id'                        | 1        |                                                |
 ---- ------------- ---------------- --------------------------------- --------- --------- ------------------------------------------- ---------- ------------------------------------------------ 
2 rows in set (0.08 sec)
  

Из документа:

Временные таблицы могут быть созданы в таких условиях, как эти:

Если есть предложение ORDER BY и другое предложение GROUP BY, или если ORDER BY или GROUP BY содержит столбцы из таблиц, отличных от первой таблицы в очереди объединения, создается временная таблица.

DISTINCT в сочетании с ORDER BY может потребовать временной таблицы.

Если вы используете опцию SQL_SMALL_RESULT, MySQL использует временную таблицу в памяти, если запрос также не содержит элементы (описанные позже), которые требуют хранения на диске.

Я уже пробовал:

• Создайте индекс с помощью ‘ el . some_id , el . group_alias ‘
• Уменьшите размер переменной до 20
• Увеличьте размер sort_buffer_size и read_rnd_buffer_size;

Любые предложения по настройке производительности были бы высоко оценены!

В вашем случае events таблица имеет time_span свойство индексирования as. Поэтому перед объединением обеих таблиц сначала выберите необходимые записи из events таблицы для определенного диапазона дат с требуемыми деталями. Затем присоединитесь к event_location , используя свойства связи с таблицей.

Проверьте свое ключевое слово MySQL Explain , чтобы проверить, как вы подходите к записям в таблице. Это сообщит вам, сколько строк сканируется перед выбором необходимых записей.

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

 SELECT  
    `e`.`id` AS `event_id`,
    `e`.`time_stamp` AS `time_stamp`,
    `el`.`latitude` AS `latitude`,
    `el`.`longitude` AS `longitude`,
    `el`.`time_span` AS `extra`,
    `e`.`entity_id` AS `asset_name`, 
    `el`.`other_id` AS `geozone_id`,
    `el`.`group_alias` AS `group_alias`,
    `e`.`event_type_id` AS `event_type_id`,
    `e`.`entity_type_id` AS `entity_type_id`, 
    `el`.`some_id` as `some_id`
FROM 
    (select
        `id` AS `event_id`,
        `time_stamp` AS `time_stamp`,
        `entity_id` AS `asset_name`,
        `event_type_id` AS `event_type_id`,
        `entity_type_id` AS `entity_type_id`
    from
        `events` 
    WHERE
        time_stamp >= '2018-01-01'  
        AND time_stamp <= '2019-06-02'
    ) AS `e`    
    JOIN `events_locations` `el` ON `e`.`event_id` = `el`.`event_id`
WHERE     
    `el`.`other_id` = '1'      
GROUP BY 
    `e`.`event_type_id` , 
    `el`.`some_id` , 
    `el`.`group_alias`;
  

Соотношение между этими таблицами равно 1: 1, поэтому я спросил себя, почему требуется group by, и я нашел несколько дублированных строк, 200 в 50000 строках. Итак, каким-то образом моя система вставляет дубликаты, и кто-то поместил эту группу (несколько лет назад) вместо поиска ошибки.

Итак, я отмечу это как более или менее решаемое…