Xwab
Форумыnavigate_nextБазы данных

Оптимизация SQL запроса / таблицы
Сообщения
Ant0ha

В общем такая проблема - в ЗЦ при очень большом количестве файлов (десятки тысяч) заметно снищается генерация страниц. Может возникнут какие - либо идеи оптимизации этого дела.

Часть запроса, вызывающая нагрузку:

SELECT SQL_CALC_FOUND_ROWS

                  dd.directory_id AS file_id,

                  dd.name,

                  'directory' AS type,

                  'directory' AS file_ext,

                  COUNT(CASE WHEN status = 'active' THEN 1 END) AS count_files,

                  COUNT(CASE WHEN time > UNIX_TIMESTAMP() - 24 * 3600 THEN 1 END) AS new_day,

                  0 AS real_name,

                  0 AS filesize,

                  0 AS time,

                  0 AS path_to_file,

                  0 AS downloads,

                  0 AS screen1,

                  0 AS about,

                  0 AS previews,

                  dd.position

                  FROM #__downloads_directories AS dd

                     LEFT JOIN #__downloads_files AS df ON df.path_to_file LIKE CONCAT('%/', dd.directory_id, '/%') AND df.status = 'active' AND real_name != ''

                  WHERE dd.parent_id = '$directory_id'

                  GROUP BY dd.directory_id


Дамп таблицы с парой файлов:

--
-- Структура таблицы `a_downloads_files`
--

CREATE TABLE IF NOT EXISTS `a_downloads_files` (
  `file_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `directory_id` int(11) DEFAULT '0',
  `time` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `real_name` varchar(255) NOT NULL,
  `path_to_file` varchar(100) NOT NULL,
  `filesize` float NOT NULL,
  `file_ext` varchar(10) NOT NULL,
  `screen1` varchar(255) NOT NULL,
  `screen2` varchar(255) NOT NULL,
  `screen3` varchar(255) NOT NULL,
  `about` text NOT NULL,
  `downloads` int(11) DEFAULT '0',
  `add_file_real_name_1` varchar(255) NOT NULL,
  `add_file_real_name_2` varchar(255) NOT NULL,
  `add_file_real_name_3` varchar(255) NOT NULL,
  `add_file_real_name_4` varchar(255) NOT NULL,
  `add_file_real_name_5` varchar(255) NOT NULL,
  `status` enum('active','moderate') NOT NULL DEFAULT 'active',
  `previews` enum('yes','no') NOT NULL DEFAULT 'no',
  `file_info` varchar(1000) NOT NULL,
  `rating` float NOT NULL,
  `rating_voices` smallint(6) NOT NULL,
  PRIMARY KEY (`file_id`),
  KEY `dirrectory_id` (`directory_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Дамп данных таблицы `a_downloads_files`
--

INSERT INTO `a_downloads_files` (`file_id`, `user_id`, `directory_id`, `time`, `name`, `real_name`, `path_to_file`, `filesize`, `file_ext`, `screen1`, `screen2`, `screen3`, `about`, `downloads`, `add_file_real_name_1`, `add_file_real_name_2`, `add_file_real_name_3`, `add_file_real_name_4`, `add_file_real_name_5`, `status`, `previews`, `file_info`, `rating`, `rating_voices`) VALUES
(4, 1, 3, 1294501106, '18246', '18246.jar', 'files/downloads/3/4', 197720, 'jar', '', '', '', '', 0, '', '', '', '', '', 'active', '', '', 0, 0),
(3, 1, 1, 1294211834, 'accept', 'accept.png', 'files/downloads/1/3', 781, 'png', '', '', '', '', 5, '', '', '', '', '', 'active', 'yes', '', 0, 0);


Таблицу создавал полтора года назад, поэтому слишком не ругайте по оптимизации)

08 Янв 2011, 18:08
Ant0ha

В общем основная проблема в получении количества файлов в папке всего и за сутки. Для этого объединил downloads_directories и downloads_files при помощи поиска directory_id в path_to_file через LIKE, может есть какие либо другие идеи? Кеширование поставить то можно, но не самый лучший вариант...

08 Янв 2011, 18:22
M0rbid

Ant0ha, кеширование - порой самый. почему нет?

08 Янв 2011, 18:24
OZ_

Запрос нужно разбить на несколько, в которых ищется что-то одно, а не информация на все случаи жизни. И результат каждого запроса - закэшировать. Особенно count и им подобные.

08 Янв 2011, 18:27
Ant0ha

M0rbid

1. Обновление счетчиков будет заторможенным (из-за времени жизни кешированных данных)
2. Тормоза с загрузкой страницы будут при получении данных в кеш.

Кеш то я поставлю на всякий, но хотелось бы решить проблему более эффективно.

08 Янв 2011, 18:28
Ant0ha

OZ, не мог бы привести пример разбивки запроса на основе запроса в первом посте?

08 Янв 2011, 18:32
OZ_

Ant0ha, выдели отдельно каждый COUNT, постарайся избавиться от JOIN и LIKE (хотя бы от одного из них). Или сделай так, чтобы LIKE и JOIN вызывались не в каждом запросе.
Вникать в структуры двух таблиц сейчас не могу - занята голова.
Кэшировать счётчик количества файлов можно на час, например. Это будет означать, что тормозить будет не каждый запрос, а всего 24 запроса за сутки (и многие из них будут отданы не юзерам, а поисковым ботам). А юзерам не особо важно, 205874 у тебя файлов, или 205872, они в эти цифры не вчитываются.

08 Янв 2011, 18:44
Ant0ha

OZ, огромнейшее спасибо за подсказку).

В общем отчет о проделанных работах)
До изменений запрос составлял 12 сек. при этом не зависел от того сколько папок на страницу, запросом получал 20 папок, но если ставить даже 2 папки в лимите, то всё равно получал 12 сек.

1. Убрал JOIN, соответствнно LIKE и COUNT'ы в основном запросе - сам запрос стал выполняться за 0.00022 сек. Дальше пренес получение коунтов в цикл (!) по 1 запросу на каждую папку.
В итоге время генерации страницы сократилось в двое и составило чуть меньше 5 сек.

2. Изменил количество папок на страницу до 10, генерация страниц уменьшилась еще в 2 раза и составила 2.4 сек.

3. Поставил всё это дело на кеш и теперь время генерации из кеша составляет всего 0.05 сек

Из этого делаем вывод - запросы в цикле не всегда зло)

09 Янв 2011, 9:05
OZ_

Рад помочь

09 Янв 2011, 11:01
Ответить на тему