10 лучших приемов оптимизации MySQL

Есть люди из калифорнии (хотя бы один человек)?

Плохой вариант:

SELECT * FROM USER WHERE state = 'California';

Хороший вариант:

SELECT 1 FROM USER WHERE state = 'California' LIMIT 1;

Кеширование запросов - один из наиболее эффективных способов повышения производительности.

Плохой вариант:

// кеш НЕ работает
$r = mysqli_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

Хороший вариант:

// кеш работает!
$today_date = date("Y-m-d");
$r = mysqli_query("SELECT username FROM user WHERE signup_date >= '$today_date'");

Индексы служат не только для первичных ключей и уникальных значений. Также они ускоряют поиск ¹⁾.

last_name LIKE 'a%'

- используется индекс, быстро.

WHERE post_content LIKE '%tomato%'

- индекс не используется, медленно.

Если в приложении много запросов с JOIN, необходимо проиндексировать столбцы, по которым идет объединение, в обоих таблицах.

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

$r = mysqli_query("SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $user_id");

Оба столбца state должны быть проиндексированы и быть в одинаковой кодировке, в противном случае MySQL будет использовать полный просмотр таблиц.

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

Не рекомендуется:

$r = mysqli_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

Лучше:

$r = mysqli_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

-- CASE 1
SELECT username FROM user WHERE user_id = 1; -- получаем имя, результаты запроса кешируется
SELECT reg_date FROM user WHERE user_id = 1; -- получаем дату, результаты запроса кешируется
 
-- CASE 2
SELECT * FROM user WHERE user_id = 1; -- получаем все, результаты запроса кешируется
SELECT * FROM user WHERE user_id = 1; -- используется кеш первого запроса!

ORDER BY RAND() - «бутылочное горлышко», делающее запросы медленными. Проблема решается с помощью дополнительного кода.

Даже если выбирается всего одна запись, MySQL вначале отсортирует всю таблицу.

Плохо:

$r = mysqli_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

Значительно лучше:

$r = mysqli_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] - 1);
 
$r = mysqli_query("SELECT username FROM user LIMIT $rand, 1");

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

Этот же принцип в нескольких вариантах.

Тип ENUM очень быстрый и компактный. Внутри он хранится как TINYINT, а внешне выглядит как строка (очень наглядно). Если у вас всего несколько вариантов значений, лучше использовать ENUM.

Например, столбец status, принимающий варианты «active», «inactive», «pending», «expired».

Этот вариант хранения может предложить и сам MySQL, если при вызове PROCEDURE ANALYSE() тип столбца - VARCHAR.

Вместо VARCHAR(15) следует использовать UNSIGNED INT - он короче на 4 байта. Для перекодирования служат MySQL функции INET_ATON() IP => целое и INET_NTOA() - обратное преобразование. На PHP аналогичные функции long2ip() и ip2long().

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

Вертикальное партиционирование - прием разделения структуры таблицы по-вертикали для оптимизации.

Пример 1: допустим, из таблицы users домашний адрес пользователя address читается редко. Тогда возможно разделить эту таблицу на две, и хранить адрес отдельно, читая его при необходимости. Такой прием уменьшит размер таблицы users, а маленькие таблицы работают быстрее.

Пример 2: в той же таблице есть поле last_login, обновляемое каждый раз при заходе пользователя. Но при каждом обновлении таблицы перестает работать кеш запросов! Поместив это поле в отдельную таблицу, мы сведем обновление users к минимуму.

Но вначале убедитесь, что эти разделенные таблицы не приходится объединять постоянно, потому как это наоборот, может уменьшить производительность.

Диск - самая медленная часть системы БД. Уменьшение хранимого объема положительно сказывается на производительности. В документации указан требуемый для хранения различных типов данных размер. Например, для целых.

Если ожидается, что количество записей будет мало, в качестве первичного ключа возможно использовать не INT, а MEDIUMINT, SMALLINT, и даже TINYINT.

Если существенна только дата, то следует ее хранить не как DATETIME, а как DATE.

Но также стоит задуматься о дальнейшем росте, предусмотрев для него такие возможности.

Две главные системы MyISAM и InnoDB имеют свои преимущества и недостатки.

MyISAM хороша для чтения, и хуже - для записи. Даже если обновляется только одна строка, блокируется вся таблица, в том числе на чтение. MyISAM очень быстро считает SELECT COUNT(*).

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

Оригинал статьи