SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 24.04.2010 19:39:04

shrus
Участник
Зарегистрирован: 24.04.2010
Сообщений: 5

Помогите оптимизировать запрос к большой таблице

Есть таблица с в 600т записей (в реале будет 10-ки милиардов)
CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `id_server` int(11) NOT NULL default '0',
  `id_file` int(11) NOT NULL default '0',
  `id_database` int(11) NOT NULL default '0',
  `reads` int(11) NOT NULL default '0',
  `time` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `search` (`id_server`,`time`,`id_file`)
) ENGINE=MyISAM AUTO_INCREMENT=604435 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

И запрос к ней
`test`.`id_database`, `test`.`id_file`,
round(avg(`reads`)) AS `value`
FROM
`test`
WHERE (test.id_server = '4') AND
(`time` BETWEEN '2010.03.24 14:10' and '2010.05.24 14:50')
GROUP BY `test`.`id_file`
ORDER BY `value` DESC LIMIT 10

В результате
10 rows fetched (6,162 sec)

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    test    range    search    search    12        604427    Using where; Using temporary; Using filesort

Можно как-то оптимизировать???
А то уже пробовал разную комбинацию ключей, без толку (

Неактивен

 

#2 24.04.2010 21:43:04

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

Re: Помогите оптимизировать запрос к большой таблице

А почему, если явно указать используемый индекс

select `test`.`id_database`, `test`.`id_file`,
round(avg(`reads`)) AS `value`
FROM
`test` use index (`id_server`,`time`,`id_file`)
WHERE (test.id_server = '4') AND
(`time` BETWEEN '2010.03.24 14:10' and '2010.05.24 14:50')
GROUP BY `test`.`id_file`
ORDER BY `value` DESC LIMIT 10;

возникает ошибка
1176     Key 'id_file' doesn't exist in table 'test'

При чем если использовать индекс с другим порядком, он все равно ругается на последний элемент индекса.

Неактивен

 

#3 24.04.2010 22:13:04

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Помогите оптимизировать запрос к большой таблице

Вась, ты, кажется, перепутал синтаксис — там нужно указывать название индекса.

Что касается самого запроса — уже использование time в комбинации с BETWEEN
делает этот запрос RANGE. После этого группировка и сортировка будут работать
в любом случае во временной таблице — нужно менять формат данных.

Ну и заодно: табличка на 10e9 строк будет безбожно тормозить на вставке, если
в ней есть индексы. Сразу думайте о том, как ее шардировать.

Неактивен

 

#4 24.04.2010 22:43:11

shrus
Участник
Зарегистрирован: 24.04.2010
Сообщений: 5

Re: Помогите оптимизировать запрос к большой таблице

Спасибо за ответы. Думаю над сменою формата данных...
Просто задача такая, что надо показывать динамику за период. Причем период "кастом".
А еще и кнопочка "выслать CSV почту"... А там оригинал надо.
И потому как это по другому организовать - не очень в голову лезет...

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

Отредактированно shrus (24.04.2010 22:43:53)

Неактивен

 

#5 24.04.2010 22:53:20

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

Re: Помогите оптимизировать запрос к большой таблице

paulus написал:

Вась, ты, кажется, перепутал синтаксис — там нужно указывать название индекса.

В том то и дело, что нет. Если писать имя индекса, то он закономерно ругается на синтаксис.

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

Проверь, пожалуйста, у себя. Может быть это особенность конкретной версии.

paulus написал:

Что касается самого запроса — уже использование time в комбинации с BETWEEN
делает этот запрос RANGE. После этого группировка и сортировка будут работать
в любом случае во временной таблице — нужно менять формат данных.

А если сначала группировку, а время потом уже через having?
По идее может сработать (`id_server`,`id_file`, `time`)

Неактивен

 

#6 24.04.2010 22:58:36

shrus
Участник
Зарегистрирован: 24.04.2010
Сообщений: 5

Re: Помогите оптимизировать запрос к большой таблице

vasya написал:

Проверь, пожалуйста, у себя. Может быть это особенность конкретной версии.

Мне пишет Key 'id_server' doesn't exist in table 'test'

vasya написал:

А если сначала группировку, а время потом уже через having?
По идее может сработать (`id_server`,`id_file`, `time`)

Попробую, но группировать оно будет то не часть а всю таблицу перед хевингом, на сколько я знаю

--
Тогда надо по тайму группировать. Потом хевинг, а топом обратно группировать результирующее...

Отредактированно shrus (24.04.2010 23:08:30)

Неактивен

 

#7 24.04.2010 23:03:54

shrus
Участник
Зарегистрирован: 24.04.2010
Сообщений: 5

Re: Помогите оптимизировать запрос к большой таблице

vasya написал:

А почему, если явно указать используемый индекс

select `test`.`id_database`, `test`.`id_file`,
round(avg(`reads`)) AS `value`
FROM
`test` use index (`id_server`,`time`,`id_file`)
WHERE (test.id_server = '4') AND
(`time` BETWEEN '2010.03.24 14:10' and '2010.05.24 14:50')
GROUP BY `test`.`id_file`
ORDER BY `value` DESC LIMIT 10;

возникает ошибка
1176     Key 'id_file' doesn't exist in table 'test'

При чем если использовать индекс с другим порядком, он все равно ругается на последний элемент индекса.

Добавил 3 индекса `id_server`,`time`,`id_file` - пошел запрос. Разницы в скорости никакой

Неактивен

 

#8 24.04.2010 23:18:45

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Помогите оптимизировать запрос к большой таблице

Вася, проверил:

Код:

[celestia] root test > select `test`.`id_database`, `test`.`id_file`,
    -> round(avg(`reads`)) AS `value`
    -> FROM
    -> `test` use index (`id_server`,`time`,`id_file`)
    -> WHERE (test.id_server = '4') AND
    -> (`time` BETWEEN '2010.03.24 14:10' and '2010.05.24 14:50')
    -> GROUP BY `test`.`id_file`
    -> ORDER BY `value` DESC LIMIT 10;
ERROR 1176 (42000): Key 'id_file' doesn't exist in table 'test'

[celestia] root test > select `test`.`id_database`, `test`.`id_file`,
    -> round(avg(`reads`)) AS `value`
    -> FROM
    -> `test` use index (`search`)
    -> WHERE (test.id_server = '4') AND
    -> (`time` BETWEEN '2010.03.24 14:10' and '2010.05.24 14:50')
    -> GROUP BY `test`.`id_file`
    -> ORDER BY `value` DESC LIMIT 10;
Empty set (0.00 sec)

Неактивен

 

#9 24.04.2010 23:20:55

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Помогите оптимизировать запрос к большой таблице

А custom периоды делать — в любом случае надо некоторое шардирование.
Хотя бы partitioning, хотя он и не будет панацеей.

Неактивен

 

#10 24.04.2010 23:24:27

shrus
Участник
Зарегистрирован: 24.04.2010
Сообщений: 5

Re: Помогите оптимизировать запрос к большой таблице

Спасибо большое за направление! Не знал о таком.
Читаю это http://habrahabr.ru/blogs/webdev/66151/
--
Прикольная штука. Только вот не очень она мне поможет... Если за 5 мин 30-80т записей. А на 600т уже реально тормозит селект...

Отредактированно shrus (24.04.2010 23:52:01)

Неактивен

 

#11 25.04.2010 00:16:05

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Помогите оптимизировать запрос к большой таблице

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

Неактивен

 

#12 11.08.2010 19:45:08

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6757

Re: Помогите оптимизировать запрос к большой таблице

/me достает большой красный плюсомёт :E

Неактивен

 

Board footer

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