оптимизация запросов mysql. Mysql запросов оптимизация
mysql - оптимизация запросов mysql
У меня есть эта база данных:
Tab1 --- 1: n --- > tab2 (parent- > id) (max1: 4) - сложная часть
Tab1 --- 1: n --- > tab3 (parent- > id) - простое соединение
tab1
ID имя версия ..и т.д.
tab2
ID родитель тип цена ..и т.д.
tab3
ID родитель тип данные
Я хотел бы выбрать полный набор информации из этих трех связанных таблиц, но мне нужно использовать множество переменных условий, поэтому мне нужно придумать лучшее решение, насколько это возможно.
Я использую этот запрос:
SELECT tab1.id, CONCAT(tab1.name,' ',tab1.version) AS nv, ndvar.price, tab3.data, ndvar.v1 [more columns] FROM tab1 INNER JOIN ( SELECT parent, type, GROUP_CONCAT(type SEPARATOR '') + '' AS v1, MIN(price) AS price [more columns] FROM tab2 WHERE show = 1 [more condition] GROUP BY parent HAVING type IN (2,3) [1-3 parameters] ) AS ndvar ON tab1.id = ndvar.parent LEFT JOIN content ON tab1.id = tab3.parent AND tab3.type = 0 WHERE name LIKE '%xyz%' [more conditions] ORDER BY nvМои вопросы: 1) Как оптимизировать этот запрос, чтобы как можно быстрее 2) Какие столбцы используются в качестве индексов? На данный момент это только столбец "id".
Столбец 'type' в tab2 содержит значения 0-3, и для каждого идентификатора есть только строка с этим типом, поэтому есть coul по индексу, но я не знаю, будет ли он делать какие-либо улучшения в моем запросе.
Спасибо заранее.
EDIT:
Это запрос, используемый во время поиска в онлайн-каталоге, в течение месяца будет всего несколько вложений или обновлений, но многие поисковые запросы каждый день. Tab1 будет содержать сотни записей, tab2 вокруг tab1 * 4 записей и tab3 вокруг tab1 * 15 записей. Существует много условий поиска, включающих в основном tab1 (1-15 параметров) и tab2 для 3 параметров. Все параметры - это числа (double), кроме имени и версии, которые являются именами - varchar (25) и версией - varchar (20).
Запрос будет выполнен на mysql 5.0.70, движок db MyISAM
По всем статистическим данным, поиск по диапазону tab2.price и tab2.type является наиболее распространенным, и поиск других диапазонов чисел более распространен, чем поиск по tab1.name или tab1.version.
Я с удовольствием заполню любую другую информацию, которую кто-то спросит.
Кстати: извините за мою бедную грамматику, английский не мой родной язык:)
EDIT2:
Я мог бы неверно истолковать всю концепцию "HAVING". В v1 мне нужно сохранить весь набор значений из строк tab2 для каждого родителя из tab1, но мне нужно отфильтровать их с помощью tab2.type.. как это сделать?
EDIT3:
Этот тип агрегации возвращает именно то, что мне нужно, НО - я знаю его страшное решение, кто-то знает, как его улучшить?
GROUP BY parent HAVING v1 LIKE '%0%qaru.site
sql - оптимизация запросов mysql
Мне поручено ускорить этот запрос, и я думаю, что некоторые из индексов на этих таблицах неправильно настроены. Я также считаю, что они не все будут использоваться. Функция b/ca применяется к столбцу, в котором есть индекс. может ли кто-нибудь увидеть, как я могу оптимизировать эти таблицы или запрос? Таблица requests будет самой большой таблицей из 3 и будет содержать записи 200k+. devices настоящее время сидят ~ 500 записей, а clients также будут меньше.
Запрос:
explain extended SELECT MAX(Request.datetime) AS datetime, Device.id, Device.client_id, Device.mac_address, Device.type, Device.manufacturer, Device.model_number, Client.id, Client.email_address, Request.device_id, Request.datetime, Request.ip_address FROM livefi.devices AS Device LEFT JOIN livefi.clients AS Client ON (Client.id = Device.client_id) INNER JOIN livefi.requests AS Request ON (Request.device_id = Device.id) GROUP BY Request.device_id, Request.client_id +----+-------------+---------+--------+---------------------------------------------------------+---------------+---------+-------------------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+--------+---------------------------------------------------------+---------------+---------+-------------------------+------+----------+---------------------------------+ | 1 | SIMPLE | Device | ALL | PRIMARY | NULL | NULL | NULL | 617 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | Client | eq_ref | PRIMARY | PRIMARY | 4 | livefi.Device.client_id | 1 | 100.00 | | | 1 | SIMPLE | Request | ref | idx_device_id,inx_requests_deviceId_datetime_ip_address | idx_device_id | 5 | livefi.Device.id | 144 | 100.00 | Using where | +----+-------------+---------+--------+---------------------------------------------------------+---------------+---------+-------------------------+------+----------+---------------------------------+ 3 rows in set, 1 warning (0.04 sec)mysql - Оптимизация запросов mysql
Откуда вы знаете, что запросы MySQL являются доминирующим фактором в проблемах производительности вашего сайта? Как вы оценили это?
Возможно, вам нужен другой дизайн.
Например, если вы выполняете запрос на каждом обновлении страницы, возможно, вы можете использовать memcache и обнаруживать случаи, когда обновление запускает тот же запрос, который необходимо повторить. Затем вы можете вернуть результаты кэширования (возможно, при асинхронной проверке, чтобы проверить, не требуется ли какое-либо промежуточное обновление баз данных базы данных для отказа от кэширования и/или обновления).
Рассмотрим, как часто изменяются данные и как часто их читают. В некоторых случаях вы можете дополнить свой проект дополнительной таблицей, в которую помещаются временные метки обновления или генерации/серийные номера. Оттуда ваш механизм запросов может заменить сложный запрос, возможно, с объединением и необходимость полного сканирования таблиц, с проверкой согласованности кеша, включающей простой запрос одного столбца в одной таблице. Если предыдущий запрос возвратил все автомобили, которые у вас были в базе данных 10 минут назад, и никакие строки не были обновлены, вставлены или удалены из этих базовых таблиц или представлений, вы можете пропустить запрос и обратиться к кешированным результатам.
Возможно, у вас может быть ваш средний слой выполнить более сложные запросы, а затем вывести данные до более точного подмножества перед представлением.
Например, веб-интерфейс для автомобилей по заданной цене в данном регионе может быть переведен в стандартизованный запрос БД для более широкого диапазона цен в более крупном регионе, при этом сервер приложений кэширует результаты, а затем фильтрует это до более конкретного подмножества, соответствующего этому веб-запросу. Другой аналогичный запрос затем может быть переведен в тот же стандартный абстрактный запрос... и приложение может, таким образом, использовать кешированные результаты, чтобы избежать обращения к внутреннему БД.
Другими словами, когда вы задаете вопрос об оптимизации своих запросов, вы также должны спрашивать, являются ли способы для вас генерировать достоверные, правильные результаты при выполнении меньших запросов. Лучше уменьшить конкуренцию на узком месте, а затем потратить значительные усилия на настройку потока через это узкое место.
На самом деле вы должны даже сомневаться в том, принадлежат ли эти данные в базе данных MySQL или некоторые из них могут быть лучше распределены между некоторыми NoSQL (couchDB или Hadoop HBase и т.д.). Базы SQL лучше всего использовать для транзакционных операций, которые налагают требования ссылочной целостности на работу. Там, где это возможно, вы должны искать способы отделить эти операции от тех, которые более часты и которые связаны с большими объемами данных.
qaru.site
Оптимизация запросов в MySQL
Оптимизация – это изменение системы с целью повышения ее быстродействия. Оптимизацию работы с БД можно разделить на 3 типа:Оптимизация запросов - наиболее простой и приводящий к наиболее высоким результатам тип оптимизации.
SELECT
Запросами, которые чаще всего поддаются оптимизации, являются запросы на выборку.
Для того чтобы посмотреть как будет выполняться запрос на выборку используется оператор EXPLAIN: http://www.mysql.com/doc/ru/EXPLAIN.html С его помощью мы можем посмотреть, в каком порядке будут связываться таблицы и какие индексы при этом будут использоваться.
Основная ошибка начинающих - это отсутствие индексов на нужных полях или создание оных на ненужных полях. Если вы делаете простую выборку наподобие:
SELECT * FROM table WHERE field1 = 123 То вам нужно проставить индекс на поле field1, если вы используете в выборке условие по двум полям: SELECT * FROM table WHERE field1 = 123 AND field2 = 234 То вам нужно создать составной индекс на поля field1, field2.Если вы используете соединение 2 или более таблиц:
SELECT * FROM a, b WHERE a.b_id = b.idТо вам следует создать индексы по полям, по которым будут присоединятся таблицы. В данном случае это поля b.id и c.id. Однако это утверждение верно только в том случае, если выборка будет происходить в том порядке, в котором они перечислены в запросе. Если, к примеру, оптимизатор MySQL будет выбирать записи из таблиц в следующем порядке: c,b,a, то нужно будет проставить индексы по полям: b.c_id и a.b_id. При связывании с помощью LEFT JOIN таблица, которая идет в запросе слева, всегда будет просматриваться первой.
Про синтаксис создания индексов можно прочитать в документации: http://www.mysql.com/doc/ru/CREATE_INDEX.html
Более подробно про использовании индексов можно прочитать здесь: http://www.mysql.com/doc/ru/MySQL_indexes.html
Иногда бывает такая ситуация, что нам постоянно приходится делать выборки из одной и той же части некоторой очень большой таблицы, например, во многих запросах происходит соединение с частью таблицы:
[LEFT] JOIN b ON b.id = a.b_id AND b.field1 = 123 AND b.field2 = 234В таких случаях может быть разумным вынести эту часть в отдельную временную таблицу:
Также если мы несколько раз рассчитываем агрегатную функцию для одних и тех же данных, то для ускорения следует сделать такой расчет отдельно и положить его результат во временную таблицу.
Также бывают тормоза, когда люди пытаются в одном запросе «поймать сразу 2-х зайцев», например, на форуме phpclub’а автор следующего запроса спрашивал, почему он тормозит:
SELECT f_m. *, MAX( f_m_v_w.date ) AS last_visited, COUNT( DISTINCT f_b.id ) AS books_num, IF ( f_m.region != 999, f_r.name, f_m.region_other ) AS region_name FROM fair_members f_m LEFT JOIN fair_members_visits_week f_m_v_w ON f_m_v_w.member_id = f_m.id LEFT JOIN fair_regions AS f_r ON f_m.region = f_r.id LEFT JOIN fair_books AS f_b ON f_b.pub_id = f_m.id GROUP BY f_m.idАвтор запроса пытается в одном запросе посчитать максимальное значение атрибута из одной таблицы и кол-во записей в другой таблице. В результате к запросу приходится присоединять 2 разные таблицы, которые сильно замедляют выборку. Для увеличения быстродействия такой выборки необходимо вынести подсчет MAX’а или COUNT’а в отдельный запрос.
Для подсчета кол-ва строк используйте функцию COUNT(*), c указанием "звездочки" в качестве аргумента.
Почему COUNT(*) обычно быстрее COUNT(id), поясню на примере:
Есть таблица message: id | user_id | text с индексом PRIMARY(id), INDEX(user_id)
Нам надо подсчитать сообщения пользователя с заданым $user_id
Сравним 2 запроса:
SELECT COUNT(*) FROM message WHERE user_id = $user_idи
SELECT COUNT(id) FROM message WHERE user_id = $user_idДля выполнения первого запроса нам достаточно просто пробежаться по индексу user_id и подсчитать кол-во записей, удовлетворяющих условию - такая операция достаточно быстрая, т.к., во-первых, индексы у нас упорядочены и ,во-вторых, часто находятся в буфере.
Для выполнения второго запроса мы сначала проходим по индексу, для отбора записей удовлетворяющих условию, после чего если запись попадает под условие, то вытаскиваем ее (запись скорее всего будет на диске) чтобы получить значение id и только потом инкриментим счетчик.
В итоге получаем, что при большом кол-ве записей скорость первого запроса будет выше в разы.
UPDATE, INSERT
Скорость вставок и обновлений в базе зависит от размера вставляемой (обновляемой) записи и от времени вставки индексов. Время вставки индексов в свою очередь зависит от количества вставляемых индексов и размера таблицы. Эту зависимость можно выразить формулой: [Время вставки индексов] = [кол-во индексов] * LOG2( [Размер таблицы] ) При операциях обновления под [кол-во индексов] понимаются только те индексы, в которых присутствуют обновляемые поля. Условия в запросах на обновления оптимизируются так же, как и в случае с выборками.
При частом изменении некоторой большой таблицы с большим количеством индексов имеет смысл производить вставки в другую небольшую вспомогательную таблицу с тем же набором полей (но с отсутствием индексов) и периодически перекидывать данные из нее в основную таблицу, очищая вспомогательную. При этом следует учесть, что данные будут выводиться с запозданием, что не всегда может быть возможным.
«Чтобы удалить все строки в таблице, нужно использовать команду TRUNCATE TABLE table_name.» © документация MySQL.
Ответы на многие вопросы по оптимизации запросов можно найти в мануале: http://www.mysql.com/doc/ru/Query_Speed.html
yadro.top
mysql - Оптимизация запросов MySQL
Не зная структур или индексов, я бы сначала добавил "STRAIGHT_JOIN", если критические критерии на самом деле из таблицы 0-ранга. Затем убедитесь, что 0_rank имеет индекс "rank". Затем убедитесь, что у 0_right есть указатель на rank_id как минимум, но rank_id, чтобы использовать BOTH ваши критерии. Индекс по 0_member по идентификатору.
Кроме того, вы имеете в виду left-join (т.е. запись только в 0_rank или 0_member) на соответствующие таблицы 0_right и 0_member вместо обычного соединения (где таблицы BOTH должны совпадать с их идентификаторами).
Наконец, обеспечьте индекс в таблице отправления user_id.
SELECT STRAIGHT_JOIN r.user_id AS ID, m.prenom, m.nom FROM 0_rank AS l LEFT JOIN `0_right` AS r ON l.id = r.rank_id AND r.section = 2 LEFT JOIN `0_member` AS m ON r.user_id = m.id WHERE l.rank = 'mod' AND depart IN (SELECT depart FROM 0_depart WHERE user_id = 2 AND user_sec = 2) GROUP BY r.user_id---- пересмотренный пост от обратной связи. Из параметров, которые вы перечисляете, вы всегда включаете идентификатор пользователя... Если это так, я бы полностью реструктурировал его, чтобы получить информацию, доступную для этого пользователя. Каждый пользователь должен, по-видимому, быть связан с несколькими отделами и может или не может соответствовать указанному ранг/отдел/раздел, который вы ищете... Я бы НАЧИНАл запрос с ОДНИМ ПОЛЬЗОВАТЕЛЕМ, потому что это гарантирует единственную запись, вплоть до других элементов...
select STRAIGHT_JOIN u.id, u.prenom, u.nom, u.other_stuffs, rank.rank_name from 0_user u left join 0_right r on u.id = r.user_id AND r.section_id = $section_id join 0_rank rank on r.rank_id = rank.id AND rank.rank_name = '$rank_name' left join 0_dept dept on u.id = dept.user_id where u.id = $user_idКроме того, я беспокоюсь о ваших отношениях в таблице и не вижу легитимного соединения с таблицей отдела...
0_user 0_right by User_ID 0_rank by right.rank_id 0_dept has section which could join to rank or right, but nothing to user_id directlyqaru.site
