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

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

1. LIMIT 1 при выборке одной строки

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

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

SELECT * FROM USER WHERE state = 'California';

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

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

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

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

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

// кеш НЕ работает
$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'");

3. Индексируйте поисковые столбцы

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

last_name LIKE 'a%'

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

WHERE post_content LIKE '%tomato%'

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

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

http://ruhighload.com/

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

Если в приложении много запросов с 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 будет использовать полный просмотр таблиц.

5. Избегайте использования SELECT *

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

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

$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']}";

«*» ускоряет разработку, а также используется «один и тот кеш», если WHERE один и тот же, но выбираются разные поля. Сравните:

-- 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; -- используется кеш первого запроса!

6. Пожалуйста, не сортируйте по RAND()

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.

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

7. Используйте ENUM вместо VARCHAR

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

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

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

Следует учесть, что теряется совместимость по SQL-92, так как ENUM есть только в MySQL. Также при добавлении нового типа есть проблема ALTER TABLE на больших таблицах.

И храните IP адреса как UNSIGNED INT

Вместо 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";

8. Вертикальное партиционирование

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

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

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

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

9. Небольшие столбцы быстрее

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

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

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

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

10. Выберите правильную подсистему хранения

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

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

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

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

1) На самом деле вопрос индексов более тонкий, чем описано ниже