Оптимизация баз данных MySQL

Оптимизацию работы с БД можно разделить на 3 типа:

• оптимизация запросов;
• оптимизация структуры;
• оптимизация сервера.

В случае виртуального хостинга возможна только оптимизация запросов и структуры таблиц.

Нижеописанные в статье команды удобнее всего выполнять через SSH.

Соединиться с сервером баз данных можно следующей командой:

mysql -u имя_пользователя -p -h имя_сервера_БД имя_базы

Запросами, которые чаще всего поддаются оптимизации, являются запросы на выборку SELECT.

SELECT

Для того чтобы посмотреть, как будет выполняться запрос на выборку, используется оператор EXPLAIN. С его помощью вы можете посмотреть, в каком порядке будут связываться таблицы и какие индексы при этом будут использоваться.

Пример:

mysql> EXPLAIN SELECT id FROM table1 WHERE id='6891';

Индексы применяются для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице, начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то MySQL может быстро определить позицию для поиска в середине файла данных без просмотра всех данных.

Основная ошибка начинающих — это отсутствие индексов на нужных полях или создание оных на ненужных полях.

Если вы делаете простую выборку наподобие:

SELECT * FROM table WHERE field1 = 123;

то вам нужно проставить индекс на поле field1.

Если вы используете в выборке условие по двум полям:

SELECT * FROM table WHERE field1 = 123 AND field2 = 234;

то вам нужно создать составной индекс на поля field1, field2:

CREATE INDEX ind1 ON table(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 таблица, которая идет в запросе слева, всегда будет просматриваться первой.

Более подробно про синтаксис создания индексов и про использование индексов можно прочитать в справочном руководстве по MySQL.

Иногда бывает такая ситуация, что вам постоянно приходится делать выборки из одной и той же части некоторой очень большой таблицы, например, во многих запросах происходит соединение с частью таблицы:

[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;

и работать уже с ней.

Про временные таблицы также есть статья в справочном руководстве по MySQL.

Для подсчета количества строк используйте функцию COUNT(*), c указанием «звездочки» в качестве аргумента, в этом случае прирост производительности будет выше в разы.

UPDATE, INSERT

Условия в запросах на обновления оптимизируются так же, как и в случае с выборками.

При частом изменении некоторой большой таблицы с большим количеством индексов имеет смысл производить вставки в другую небольшую вспомогательную таблицу с тем же набором полей (но с отсутствием индексов) и периодически перекидывать данные из нее в основную таблицу, очищая вспомогательную. При этом следует учесть, что данные будут выводиться с запозданием, что не всегда может быть возможным.

Чтобы удалить все строки в таблице, нужно использовать команду:

TRUNCATE TABLE table_name;

В справочном руководстве также есть дополнительная информация по поводу оптимизации.