SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 25.10.2015 14:01:17

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Группировка с последним значением, можно оптимизировать?

Добрый день!

Достаточно частая задача выборка последних данных по однму для каждого из группировки.
Например последние сообщения диалогов в каком-нибудь мессенджере.
У нас не мессенджер, но суть похожа...

Есть таблица:

CREATE TABLE `history` (
  `u` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `gu` int(11) NOT NULL,
  `lat` double NOT NULL,
  `lon` double NOT NULL,
  `speed` float NOT NULL,
  `altitude` int(11) NOT NULL,
  `time` datetime NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8 PACK_KEYS=1;
ALTER TABLE `history`
  ADD PRIMARY KEY (`u`),
  ADD KEY `user_time` (`user`,`time`) USING BTREE,
  ADD KEY `user_gu` (`user`,`time`,`gu`) USING BTREE,
  ADD KEY `gu_time` (`gu`,`time`) USING BTREE;
 

Гуглением был составлен такой запрос:
SELECT t2.* FROM ( SELECT `gu`, max(`time`) AS `time` FROM `history` WHERE `user` = 123 AND `time` > NOW() - INTERVAL 30 MINUTE GROUP BY `gu` ) t1 JOIN `history` t2 USING(gu,time) ORDER BY t1.time DESC

Его EXPLAIN:
+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using filesort |
| 1 | PRIMARY | t2 | ref | gu_time | gu_time | 12 | t1.gu,t1.time | 1 |  |
| 2 | DERIVED | history | range | user_time,user_gu | user_time | 12 | NULL | 1 | Using index condition; Using temporary; Using filesort |
+---+---+---+---+---+---+---+---+---+---+

Вопрос такой - можно ли как-то лучше это сделать? В частности оптимизировать индексы или что-то изменить? Иногда запрос подтормаживает...

Таблица в несколько миллионов записей, растёт как на дрожжах, есть мысль разнести её на две текущую и историческу., так чтобы в той к которой этот запрос оставались только данные за последние сутки (на уровне приложения интервал не может быть больше суток).

PS индекс user_time я предвижу что избыточен, но user_gu был добавлен позднее для тестов. Потому решил пока оставить оба.
PS PS используется MariaDB 10.1
PS PS PS читал http://sqlinfo.ru/articles/info/18.html , но не понял что мне лучше подойдёт

Отредактированно Proger (26.10.2015 14:32:14)

Неактивен

 

#2 26.10.2015 11:37:51

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Группировка с последним значением, можно оптимизировать?

не вижу у вас индексов group_time,group_gu

попробуйте (`gu`, `user`, `time`)

Неактивен

 

#3 26.10.2015 14:33:45

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: Группировка с последним значением, можно оптимизировать?

Да я просто для форума поле group в таблице переименовал в user, в explain забыл.
Просто, чтобы слово зарезервированное с толку не сбивало, а то например IDE часто предупреждает, что так не хорошо...

Ваш вариант вечером попробую, спасибо.

Отредактированно Proger (26.10.2015 14:35:09)

Неактивен

 

#4 27.10.2015 04:43:42

Proger
Активист
Откуда: Санкт-Петербург
Зарегистрирован: 04.09.2008
Сообщений: 172

Re: Группировка с последним значением, можно оптимизировать?

Спасибо, вариант с вашим индексом ускорил запрос в 3 раза! Это очень помогло! В сумме с кешированием будет идеально.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson