SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 30.08.2009 22:55:19

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Оптимизация выборки, проект видео-хостинга

Добрый день.

Дана следующая таблица:


CREATE TABLE IF NOT EXISTS `t_video` (
  `N_ID` int(30) NOT NULL auto_increment,
  `N_STATUS` smallint(3) NOT NULL default '2',
  `ID_USER` int(11) NOT NULL,
  `S_TITLE` varchar(20) NOT NULL,
  `DATE_ADD` datetime default '0000-00-00 00:00:00',
  `FILE_NAME` varchar(50) default NULL,
  `N_LENGTH` int(11) NOT NULL,
  `N_SIZE` int(11) NOT NULL,
  `S_DESC` varchar(255) NOT NULL,
  `N_ID_MAIN_SCREEN` int(30) default '0',
  `COUNT_COMM` int(30) default '0',
  `GLOB_RATING` int(30) default '0',
  `TOTAL_VIEWS` int(30) default '0',
  PRIMARY KEY  (`N_ID`),
  KEY `ID_USER` (`ID_USER`),
  KEY `DATE_ADD` (`DATE_ADD`),
  KEY `GLOB_RATING` (`GLOB_RATING`),
  KEY `N_ID_MAIN_SCREEN` (`N_ID_MAIN_SCREEN`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=99728 ;
 


Из название полей, думаю, будет понятны их роли.

Есть запрос вида:

SELECT t1.N_ID, t1.S_DESC, t1.ID_USER, t1.COUNT_COMM, t1.N_ID_MAIN_SCREEN, t1.N_LENGTH, t1.N_SIZE, t1.GLOB_RATING,
                                        UNIX_TIMESTAMP(t1.date_add) TIMEADD, (to_days(now())-to_days(t1.date_add)) DIVDATE
                                    FROM t_video t1
 WHERE N_STATUS = 1
                                ORDER BY N_ID DESC LIMIT 99999, 10
 


Исходная таблица содержит порядка 100-200тыс. записей. Запрос выполняется порядка 2+ секунд.
Как можно оптимизировать?
Из-за ордера+лимит сервер просто загибается.

P.S. Ордер еще происходит по полю GLOB_RATING

Неактивен

 

#2 31.08.2009 01:45:51

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

Re: Оптимизация выборки, проект видео-хостинга

На вскидку. У вас не используются индексы. В этом вы можете убедиться посмотрев explain вашего запроса. Для исправления этой ситуации нужно добавить составной индекс KEY(N_STATUS,N_ID).

Рекомендую прочитать тему http://sqlinfo.ru/forum/viewtopic.php?id=151

Неактивен

 

#3 31.08.2009 10:07:01

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Re: Оптимизация выборки, проект видео-хостинга

vasya написал:

На вскидку. У вас не используются индексы. В этом вы можете убедиться посмотрев explain вашего запроса. Для исправления этой ситуации нужно добавить составной индекс KEY(N_STATUS,N_ID).

Рекомендую прочитать тему http://sqlinfo.ru/forum/viewtopic.php?id=151

Как показывала практика, идекс KEY(N_STATUS,N_ID) не сильно помогал.
Для этого счас разбиение на 2 таблицы идет, t_video (статусы все кроме = 1) и t_video_activ (статус = 1)
Но сейчас из-за этого разбиения возникается ряд проблем, как миниумм с N_ID.

Есть какие то еще идеи на этот счет?

Неактивен

 

#4 31.08.2009 10:52:19

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

Re: Оптимизация выборки, проект видео-хостинга

Не нравится конструкция LIMIT 99999, 10
В этом случае даже при наличии индекса MySQL придется прочитать 100009 строк и потом откинуть ненужные. Единственный способ избежать этого - переписать запрос, изменив условие выборки, чтобы сразу перейти к нужной записи, т.е. WHERE N_STATUS = 1 AND N_ID < 99999 ORDER BY N_ID DESC LIMIT 10

Неактивен

 

#5 31.08.2009 11:42:30

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Re: Оптимизация выборки, проект видео-хостинга

Да! Хорошая идея, спасибо!

Еще есть идея разбить на партиции эту таблицу, как лучше всего разбить (и есть ли смысл?) ? по статусам? Там записей с id_status=1 будет на много больше чем с другими статусами и выборка по ним будет на много чаще.

Неактивен

 

#6 01.09.2009 11:07:58

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Re: Оптимизация выборки, проект видео-хостинга

Идей, как я понимаю нет ни у кого? :-)

Неактивен

 

#7 01.09.2009 12:48:55

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

Re: Оптимизация выборки, проект видео-хостинга

Partitioning имеет смысл делать тогда, когда Вы ожидаете, что будет происходить prunung
на каких-то запросах (ну или для увеличения скорости вставки в таблицу). В Вашем случае
с неимоверным LIMIT, это никак не скажется на производительности, единственное, что
окажется полезным, — научиться создавать партицированные таблички smile

Неактивен

 

#8 01.09.2009 21:41:58

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Re: Оптимизация выборки, проект видео-хостинга

Большое спасибо за ответы, буду эксперементировать

Неактивен

 

#9 06.09.2009 09:16:00

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Re: Оптимизация выборки, проект видео-хостинга

Еще вопрос по оптимизации одной выборки:
Нужно выбрать 10 видео-файлов отсортированных по рейтингу (GLOB_RATING) с заданной категорией (+ у которых статус = 1).

Для этого существует описанная в первом посте таблица t_video и таблица t_video_cat:


CREATE TABLE IF NOT EXISTS `t_video_cat` (
  `ID_VIDEO` int(30) NOT NULL,
  `ID_CAT` int(11) NOT NULL,
  PRIMARY KEY (`ID_VIDEO`,`ID_CAT`),
  KEY `vcat_idx` (`ID_CAT`),
  KEY `ID_VIDEO` (`ID_VIDEO`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
 


Для решения задачи делаю запрос примерно такого вида:


SELECT *
                             FROM t_video_cat t3
                             LEFT JOIN t_video t1 on t3.ID_VIDEO = t1.N_ID
                         WHERE t3.ID_CAT = 2 AND N_STATUS = 1
                   ORDER BY t1.GLOB_RATING DESC LIMIT 10
 

В среднем запрос выполняется порядка 1.4секунды. sad
Как можно оптимизировать запрос? neutral

Отредактированно warl555 (06.09.2009 09:17:29)

Неактивен

 

#10 06.09.2009 16:25:40

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

Re: Оптимизация выборки, проект видео-хостинга

А зачем Вам тут левое объединение? Кажется, обычное тут по логике лучше.

Ну и explain покажите smile

Неактивен

 

#11 06.09.2009 19:24:42

warl555
Участник
Зарегистрирован: 30.08.2009
Сообщений: 8

Re: Оптимизация выборки, проект видео-хостинга

paulus написал:

А зачем Вам тут левое объединение? Кажется, обычное тут по логике лучше.

Ну и explain покажите smile

Да, тут, действительно left join не нужен:


SELECT *
                             FROM t_video_cat t3
                             JOIN t_video t1 ON t3.ID_VIDEO = t1.N_ID
                         WHERE t3.ID_CAT = 2 AND N_STATUS = 1
                   ORDER BY t1.GLOB_RATING DESC LIMIT 10
 


explain:

 id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1    SIMPLE    t3    ref    PRIMARY,vcat_idx,ID_VIDEO    vcat_idx    4    const    25125    Using temporary; Using filesort
1    SIMPLE    t1    eq_ref    PRIMARY,N_STATUS,N_STATUS_2,N_STATUS_3,N_ID    PRIMARY    6    video.t3.ID_VIDEO,const    1    

Неактивен

 

#12 06.09.2009 21:42:10

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

Re: Оптимизация выборки, проект видео-хостинга

По логике все делается правильно, единственное, я бы сортировал не все данные, а только
id, потом ограничивал уже количество строк и вытаскивал данные:

SELECT *
FROM t_video_cat
JOIN (SELECT N_ID FROM ... ) tt USING N_ID

Если Вы уверены, что нужные строки будут вверху t1, то можно попробовать указать MySQL
в явном виде, как искать и как отсеивать данные (но если не уверены — можете сделать
медленнее):

SELECT STRAIGHT_JOIN *
FROM t_video t1 JOIN t_video_cat t3 ON ..

(другой порядок, чтобы искало по t_video и выбирало сразу в нужном порядке, но, возможно,
выбирало лишние данные, которые потом будут выброшены).

Неактивен

 

Board footer

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