@Steely

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

Задача выбрать топ 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 ;
  • Вопрос задан
  • 171 просмотр
Решения вопроса 1
opium
@opium
Просто люблю качественно работать
Никак не ускорить простыми методами , сразу видно что провайдерский биллинг и дохрена логов юзеров по айпи.
Из вариантов решения ложить данные не сразу в базу а промежуточную точку и делать агрегацию этих данных, в итоге в базе будет меньше записей и выборка будет работать в раз 10-100 быстрее.
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
mgyk
@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 если не хотите подвесить все остальное пока ваш тяжелый запрос отработает
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы