Оптимизация запросов в MySQL (mysql optimization)
Ключевые слова: mysql, optimization, (найти похожие документы)
From: Соколов Сергей <2sly@ukrpost.net.>
Newsgroups: http://detail.phpclub.net
Date: Mon, 20 Dec 2004 18:21:07 +0000 (UTC)
Subject: Оптимизация запросов в MySQL
Оригинал: http://detail.phpclub.net/article/mysql_optimize
Оптимизация запросов в MySQL
Соколов Сергей
22.03.2004
Оптимизация - это изменение системы с целью повышения ее быстродействия.
Оптимизацию работы с БД можно разделить на 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
Или в более общем виде:
SELECT *
FROM a
[LEFT] JOIN b ON b.id = a.b_id
[LEFT] JOIN с ON с.id = b.c_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
В таких случаях может быть разумным вынести эту часть в отдельную
временную таблицу:
CREATE TEMPORARY TABLE tmp_b TYPE=HEAP
SELECT * FROM b WHERE b.field1 = 123 AND b.field2 = 234
И работать уже с ней ( про временные таблицы читайте в документации
http://www.mysql.com/doc/ru/CREATE_TABLE.html).
Также если мы несколько раз рассчитываем агрегатную функцию для одних
и тех же данных, то для ускорения следует сделать такой расчет
отдельно и положить его результат во временную таблицу.
Также бывают тормоза, когда люди пытаются в одном запросе <<поймать
сразу 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
Комментарии:
>>> SELECT COUNT(*) FROM message WHERE user_id = $user_id
>>> внимательно читаем мануал, прежде чем сравнивать
>>> (http://www.mysql.com/doc/en/GROUP-BY-Functions.html):
>>> COUNT(*) is optimized to return very quickly if the SELECT retrieves
>>> from one table, no other columns are retrieved, and there is no WHERE
>>> clause.
>> Моей задачей было показать почему в общем случае COUNT(*) быстрее. А
>> то что он очень быстро возвращает ко-во строк в таблице - это в мане
>> описано, и я не стал этого повторять.
> ты хоть прочитал отквотированный мной мануал? COUNT(*) быстрее
> _только_ в случае, если не выбираются никакие столбцы и не
> накладывается никаких условий в WHERE. теперь посмотри на свой пример.
Где ты в мануале увидел слово "только"
>>> еще насчет оптимизации INSERT. если предполагается объемная вставка
>>> данных, то в 4 mysql можно перед вставкой сделать ALTER TABLE table
>>> DISABLE KEYS, вставить данные и сделать ALTER .. ENABLE. Такой
>>> механизм специально заточен что бы быстро регенерить индексы после
>>> больших вставок, а не перестраивать индекся после вставки каждой
>>> строки.
>> данных, то в 4 mysql можно перед вставкой сделать ALTER TABLE table
>> DISABLE KEYS, вставить данные и сделать ALTER .. ENABLE. Такой
>> механизм специально заточен что бы быстро регенерить индексы после
>> больших вставок, а не перестраивать индекся после вставки каждой
>> строки.
>> Тут Вы правы, но я имел в виду что данные втавляются разными потоками.
>> Правда в таком случае при вставках можно использовать INSERT DELAED.
>> Возможно стоит этот абзац удалить.
> это надо указывать. INSER DELAYED не поможет проблеме, т.к. рано или
> поздно индексы начнут регенериться после каждого инсерта. что создаст
> нагрузку на сервер. хотя inser delayed решит проблему ожидания клиента
> конца вставки. пользуя insert delayed надо помнить, что мы не получим
> значение автоинкрементного поля, что в некоторых случаях может стать
> проблемой при поиске ошибок в неправильно работающем скрипте. ну и
> надо учитывать, что insert delayed тормознее простого inserta, т.к.
> задание на вставку ставится в очередь, на каждую таблицу выделяется по
> потоку, т.е. не подходит под название статьи, т.к. оптимизация времени
> ответа клиенту не есть оптимизацией запросов.
Здесь с тобой не согласен.
"Еще одно существенное преимущество применения оператора INSERT
DELAYED заключается в том, что данные от многих клиентов собираются
вместе и записываются одним блоком. Это намного быстрее, чем несколько
отдельных операций вставки." (c) Мануал
(http://www.mysql.com/doc/ru/INSERT_DELAYED.html)
