Оптимизация MySql запросов

Содержание:

  1. Оптимизируйте запросы для кэша запросов
  2. Используйте EXPLAIN для ваших запросов SELECT
  3. LIMIT 1, когда нужна единственная строка
  4. Индексируйте поля, по которым ищите
  5. Индексируйте поля для объединения и используйте для них одинаковые типы столбцов
  6. Не используйте ORDER BY RAND() для больших таблиц
  7. Избегайте SELECT *
  8. Старайтесь всегда создать поле ID
  9. Используйте NOT NULL, если это возможно
  10. Подготовленные выражения
  11. Небуферизованные запросы
  12. Хранение IP в UNSIGNED INT
  13. Таблицы фиксированного размера (статичные) — быстрее
  14. Вертикальное разделение
  15. Разделяйте большие запросы DELETE и INSERT
  16. Выбирайте правильный тип таблицы

Оптимизируйте запросы для кэша запросов

У большинства MySQL серверов включено кэширование запросов. Один из наилучших способов улучшения производительности — просто предоставить кэширование самой базе данных. Когда какой-либо запрос повторяется много раз, его результат берется из кэша, что гораздо быстрее прямого обращения к базе данных. Основная проблема в том, что многие просто используют запросы, которые не могут быть закэшированы:

// запрос не будет кэширован
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// а так будет!
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых недетерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.

Используйте EXPLAIN для ваших запросов SELECT

Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:

После добавления индекса для поля group_id:

Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.

LIMIT 1, когда нужна единственная строка

Иногда, обращаясь к таблице, вы точно знаете, что вам нужна только одна конкретная строка. Например, нужно получить одну уникальную строку или просто проверить существование записей, удовлетворяющих запросу WHERE.
В этом случае, добавление LIMIT 1 в ваш запрос будет оптимальней. Таким образом, база данных остановит выборку записей, после нахождения первой же, вместо того, чтобы выбрать всю таблицу или индекс.

// есть пользователи в Alabama?
// можно так:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
if (mysql_num_rows($r) > 0) {
 // ...
}
// но так лучше:
$r = mysql_query("SELECT * FROM user WHERE state = 'Alabama' LIMIT 1");
if (mysql_num_rows($r) > 0) {
  // ...
}

Индексируйте поля, по которым ищите

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

Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE 'a%'». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE '%apple%'»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.

Индексируйте поля для объединения и используйте для них одинаковые типы столбцов

Если ваше приложение содержит много объединений таблиц, вам необходимо проиндексировать в обеих таблицах поля, используемые для объединения. Это повлияет на то, как MySQL делает внутреннюю оптимизацию объединений.
Так же эти столбцы должны быть одного типа. Например, если вы объединяете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать хотя бы один из индексов. Даже кодировки символов должны быть одного типа для строковых столбцов.

// выборки компаний в штате пользователя
$r = mysql_query("SELECT company_name FROM users
JOIN companies ON (users.state = companies.state)
users.id = $user_id");
// обе колонки state должны быть проиндексированы
// они обе должны иметь один тип данных и кодировку символов
// а иначе MySQL сделает полную выборку из этих таблиц 

Не используйте ORDER BY RAND() для больших таблиц

(Имеется в виду выборка единственной строки. Примечание переводчика)

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

Таким образом вы выберите случайный номер, который меньше количества строк и используете его для смещения в LIMIT.

Примеры

PHP

<?php
$n_rand = mt_rand(1, $mysqli->query("SELECT MAX(id) FROM `table`"));
$result_set = $mysqli->query("SELECT * FROM `table` WHERE `id`='$n_rand'");
?>

SQL

SELECT f.id FROM files f
            JOIN ( SELECT RAND() * (SELECT MAX(id) FROM files) AS max_id ) AS m
            WHERE f.id >= m.max_id
            ORDER BY f.id ASC
            LIMIT 1;

Избегайте SELECT *

Чем больше данных считывается из таблицы, тем медленнее запрос. Это увеличивает время работы с хранилищем данных. Также, когда сервер базы данных установлен отдельно от web-сервера, будет большая задержка при передаче данных по сети.
Прописывайте, какие именно столбцы из запроса вам нужны.

// не очень хорошо:
$r = mysql_query(«SELECT * FROM user WHERE user_id = 1»);
$d = mysql_fetch_assoc($r);
echo «Welcome {$d['username']}»;

// лучше:
$r = mysql_query(«SELECT username FROM user WHERE user_id = 1»);
$d = mysql_fetch_assoc($r);
echo «Welcome {$d['username']}»;

// разница более значительна при большем наборе данных.

Старайтесь всегда создать поле ID

В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.

Используйте NOT NULL, если это возможно

Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».

Подготовленные выражения

Есть несколько фактов преимущества использования подготовленных выражений в области безопасности и производительности.

Подготовленные выражения по умолчанию фильтруют переменные, которые к ним привязаны, что является очень хорошей защитой ваших приложений против атак типа "инъекция SQL". Конечно, вы можете фильтровать переменные вручную тоже, но такие методы более подвержены ошибкам и забывчивости программистов.

Преимущества в области производительности более существенно проявляются в случаях многократного использования одного и того же запроса в приложении. Можно назначать различные значения для одного подготовленного выражения, которое MySQL будет анализировать только один раз.

Кроме того, последние версии MySQL передают подготовленные выражения в бинарной форме, которая более эффективна и помогает сокращать задержки при передаче данных по сети.

Было время, когда программисты избегали использования подготовленных выражений только по причине того, что они не кэшировались  MySQL. Но начиная с версии 5.1 кэширование также поддерживается для запросов подготовленных выражений.

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

// Создаем подготовленное выражение
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) {
    // Привязываем параметры
    $stmt->bind_param("s", $state);

    // Выполняем
    $stmt->execute();

    // Привязываем переменные результата
    $stmt->bind_result($username);

    // Получаем значения
    $stmt->fetch();

    printf("%s is from %s\n", $username, $state);
    $stmt->close();
}

Небуферизованные запросы

Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.

Хранение IP в UNSIGNED INT

Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

Таблицы фиксированного размера (статичные) — быстрее

Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.

Вертикальное разделение

Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.
Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.
Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.
Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.

Разделяйте большие запросы DELETE и INSERT

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

while (1) {
  mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
  if (mysql_affected_rows() == 0) {
    // удалили
    break;
  }
  // небольшая пауза
  usleep(50000);
}

Выбирайте правильный тип таблицы

Два основных типа таблиц — MyISAM и InnoDB, у каждого есть свои плюсы и минусы.
MyISAM хорошо считывает из таблиц большое количество данных, но он плох для записи. Даже если вы изменяете всего одну строку, блокируется вся таблица, и ни один процесс не может ничего из нее прочитать. MyISAM очень быстро выполняет запросы типа SELECT COUNT(*).
У InnoDB более сложный механизм хранения данных, и он может быть медленнее, чем MyISAM, для маленьких приложений. Но он поддерживает блокировку строк, что более эффективно при масштабировании. Так же поддерживаются некоторые дополнительные функции, такие операции как транзакции.

На нашем сайте Вы можете найти надежный платный хостинг.

Принимаем
Все способы