Оптимизация запросов
Сегодня рассмотрим метод анализа запросов SELECT для увеличения их скорости.
Что-то определенного для оптимизации конкретных таблиц я не дам, будет рассмотрен только метод анализа.
Недавно, работая над каталогом организаций и их товара, понял, что реляционные базы данных (т.е. связанные таблицы между собой) не всегда упрощают жизнь, если это связано с оптимизацией на скорость.
Причина в том, что при связке нескольких таблиц, пусть это будет через LEFT JOIN или FROM table1 t1, table2 t2, Mysql для работы с ними создает временную таблицу, в которую помещает данные из связанных таблиц, а потом к ним применяет условия, сортировку, группировку и выборку по количеству. Этот процесс сами видите, даже по описанию очень долгий (Сколько перечислений).
Пришел к выводу, что для выборки, самый лучший вариант это создание собственных таблиц со сведенными данными для определенных целей.
У меня был вариант — каталог аптек. Организация передавала XML Файл, очень большой, который приходилось парсить. получалось несколько таблиц.
- первая таблица: категории товаров, это древовидная структура разделов, с названием и описанием, а также рядом доп. параметров
- вторая таблица: каталог товаров, просто все товары, которые есть в аптеках, это просто перечень названий и описаний (без цен)
- третья таблица: каталог аптек, просто название и контактные данные
- четвертая таблица: каталог товаров аптек, только цена, связь с каталогом товаров таблицы №2
Связь была такая: Товар из аптеки связан с каталогом товаров из таблицы №2 + связь с таблицей аптек + связь с каталогом общим (где только названия) и соответственно связь с рубрикатором (таблица №1).
Жуткая картина, когда 4 таблицы связаны между собой, а когда еще товаров больше 10000 на аптеку, а если еще задумано определять максимальную и минимальную цену по сети аптек, показывать сколько аптек продает этот товар — запросик выполнятся будет не менее 1−5 секунды. а это ужасно!
Запомните, хороший запрос, время его выполнения 0,05 сек. ну максимум 0,1 сек. Секунда ведь на самом деле очень долгая единица. А если таких запросов на странице не сколько ? 
вытекает в вечность, пользователю надоест такой тормознутый сайт.
Любой запрос можно анализировать путем подставления в начале SELECT слова EXPLAIN
(пример запроса)
EXPLAIN SELECT ci.name, ai.price FROM apteks_items ai LEFT JOIN apteks a ON ai.apteka_id = a.id LEFT JOIN catalog_items ci ON ci.id = ai.tovar_id GROUP BY ai.tovar_id ORDER BY ci.name ASC LIMIT 1500
Результатом будем сводная таблица, в которой будет показано как работает запрос, какие ключи используются, и что происходит при выполнении. Особое внимание уделите колонке «Extra». Самый «опасный» атрибут — «Using temporary» . Это как раз то, о чем я говорил — создание временной таблицы.
Как написано в руководстве: «Чтобы выполнить запрос, MySQL должен будет создать временную таблицу для хранения результата. Это обычно происходит, если предложение ORDER BY выполняется для набора столбцов, отличного от того, который используется в предложении GROUP BY»
Поясню, временная таблица появляется, если при выборке формируются новые данные, например когда используете совместно функции «MAX, MIN, AVG, COUNT» с «GROUP BY», получаются новые данные, которых нет в таблице, по этому Мускул создает таблицу вносит в них данные, и потом с ними работает.
Что бы этого не происходило, пытайтесь привести запрос к такому виду, чтобы либо не было «Using temporary», либо что еще лучше, все основные данные находились в одной таблице.
С этим я столкнулся на днях. При импорте данных из XML файлов (как раз про аптеки речь), в таблицу с каталогом товаров я свел необходимы данные для выборки. До этого была таблица только с названием, описанием, рубрикой, картинкой (id, cat_id, name, description, image). Сделал некоторые преобразования:
1. сделал конвертер из обычного древовидного дерева — дерево формата NASTED SETS (ключевые поля left и right)
2. для каждого товара сразу прописал значение ветки дерева, где он находится, т.е. у товара появились значения c_left, c_right
3. тут же в таблицу (при импорте) зафигачил два поля MAX и MIN, в которые вставил соответственные значения цен по сети аптек для данного товара.
4. добавил поле — кол-во аптек с данным товаром (теперь при выборке не надо подсчитывать, т.к. уже все подсчитано)
Получилось:
id, cat_id, name, description, c_left, c_right, min, max, cnt_places
вот и все, из данной таблицы можно одним запросом вывести ВСЕ товары, находящиеся включительно и ниже выбранной рубрики (методами выборки NASTED SETS), с определенной ценой (при поиске) и сразу с показателями кол-ва аптек.
Не бойтесь увеличить объем таблицы один фиг, или данные состоят из 1 большой или из миллиона маленьких таблиц, суммарно — размер будет такой же по этому, старайтесь свести запрос выборки к одной таблице
Материалы для Вашего изучения:
EXPLAIN: http://www.mysql.ru/docs/man/EXPLAIN.html
NASTED SETS: http://phoinix.ucoz.ru/publ/1−1−0−1
Конвертирование TREE -> NASTED SET: http://neudor.ru/2008/10/09/конвертация-дерева-в-nested-sets/
Последние комментарии