Выборка с группировкой из большой myisam таблицы, как ускорить?

Question

[Steely]

Задача выбрать топ 30 ip за определенный период (месяц) с максимальным значением bytes.

SELECT SUM(bytes) as bytes,  ip  
			FROM table
			WHERE stamp_inserted >= '2015-04-01 00:00:00'
			GROUP BY ip
			ORDER BY bytes DESC
			LIMIT 30

В таблице 10 000 000+ записей (будет расти).
Тип таблицы myisam, пробовал добавлять индексы как отдельно для bytes, ip, stamp_inserted. Так и делал составной индекс из трех. Explain пишет:

type: range
key: stamp_inserted (составной из всех трех)
rows: 6988465
extra: Using where; Using index; Using temporary; Using filesort

Запрос выполняется 15+ секунд. Если убрать order то 10 секунд.

Это нормально или я где-то чего-то не понимаю?

Структура таблицы:

CREATE TABLE IF NOT EXISTS `all` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` char(15) NOT NULL,
  `bytes` bigint(20) unsigned NOT NULL,
  `stamp_inserted` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ip` (`ip_dst`),
  KEY `bytes` (`bytes`),
  KEY `stamp_inserted` (`stamp_inserted`),
  KEY `bytes_2` (`bytes`,`ip`,`stamp_inserted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11218827 ;

Answer 1

[Пума Тайланд]

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

Comments

[Пума Тайланд]

ещё можно каждый месяц ложить в отдельную таблицу и избавиться от WHERE stamp_inserted >= '2015-04-01 00:00:00' тоже ускорит существенно запрос.

[Steely]

Понятно, спасибо!

Answer 2

[mgyk]

1) Гораздо правильнее будет реорганизовать таблицу и хранить только день/ip. Данные вставлять с INSERT ON DUPLICATE KEY UPDATE bytes=VALUES(bytes)+bytes
2) Храните IP в int поле. SELECT INET_ATON('10.0.5.9') -> 167773449. Аналогично обратно inet_ntoa
Этим вы уменьшите размер индексов в разы и размер таблицы в сотню раз. Для таблицы достаточно иметь только один уникальный индекс ip/date или date/ip в зависимости от того какие у вас в основном запросы
3) Используйте InnoDB если не хотите подвесить все остальное пока ваш тяжелый запрос отработает

Comments

[Steely]

В данный момент для каждого IP каждый час создается общее кол-во bytes. Т.е. 1 юзер = 24 записи в день.

[Steely]

Я уже разделил таблицы, в данном случае специально сделал myisam для хранения логов, а обновляемая таблица innodb и каждый час из нее берутся записи за последний час и переносятся в хранилище myisam