В общем такая проблема - в ЗЦ при очень большом количестве файлов (десятки тысяч) заметно снищается генерация страниц. Может возникнут какие - либо идеи оптимизации этого дела.
Часть запроса, вызывающая нагрузку:
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);
Таблицу создавал полтора года назад, поэтому слишком не ругайте по оптимизации)
В общем основная проблема в получении количества файлов в папке всего и за сутки. Для этого объединил downloads_directories и downloads_files при помощи поиска directory_id в path_to_file через LIKE, может есть какие либо другие идеи? Кеширование поставить то можно, но не самый лучший вариант...
08 Янв 2011, 18:22Запрос нужно разбить на несколько, в которых ищется что-то одно, а не информация на все случаи жизни. И результат каждого запроса - закэшировать. Особенно count и им подобные.
08 Янв 2011, 18:27M0rbid
1. Обновление счетчиков будет заторможенным (из-за времени жизни кешированных данных)
2. Тормоза с загрузкой страницы будут при получении данных в кеш.
Кеш то я поставлю на всякий, но хотелось бы решить проблему более эффективно.
OZ, не мог бы привести пример разбивки запроса на основе запроса в первом посте?
08 Янв 2011, 18:32Ant0ha, выдели отдельно каждый COUNT, постарайся избавиться от JOIN и LIKE (хотя бы от одного из них). Или сделай так, чтобы LIKE и JOIN вызывались не в каждом запросе.
Вникать в структуры двух таблиц сейчас не могу - занята голова.
Кэшировать счётчик количества файлов можно на час, например. Это будет означать, что тормозить будет не каждый запрос, а всего 24 запроса за сутки (и многие из них будут отданы не юзерам, а поисковым ботам). А юзерам не особо важно, 205874 у тебя файлов, или 205872, они в эти цифры не вчитываются.
OZ, огромнейшее спасибо за подсказку).
В общем отчет о проделанных работах)
До изменений запрос составлял 12 сек. при этом не зависел от того сколько папок на страницу, запросом получал 20 папок, но если ставить даже 2 папки в лимите, то всё равно получал 12 сек.
1. Убрал JOIN, соответствнно LIKE и COUNT'ы в основном запросе - сам запрос стал выполняться за 0.00022 сек. Дальше пренес получение коунтов в цикл (!) по 1 запросу на каждую папку.
В итоге время генерации страницы сократилось в двое и составило чуть меньше 5 сек.
2. Изменил количество папок на страницу до 10, генерация страниц уменьшилась еще в 2 раза и составила 2.4 сек.
3. Поставил всё это дело на кеш и теперь время генерации из кеша составляет всего 0.05 сек
Из этого делаем вывод - запросы в цикле не всегда зло)