9 октября 2008 в 23:37

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

MySQL

В повседневной работе приходится сталкиваться с довольно однотипными ошибками при написании запросов.

В этой статье хотелось бы привести примеры того, как НЕ надо писать запросы.

Выборка всех полей
SELECT * FROM table
При написании запросов не используйте выборку всех полей - "*". Перечислите только те поля, которые вам действительно нужны. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вам на самом деле необходимы все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочки невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, со временем количество столбцов в вашей таблице может изменяться, и если сегодня это пять INT столбцов, то через месяц могут добавиться TEXT и BLOB поля, которые будут замедлять выборку.
Запросы в цикле.
Нужно четко представлять себе, что SQL - язык, оперирующий множествами. Порой программистам, привыкшим думать терминами процедурных языков, трудно перестроить мышление на язык множеств. Это можно сделать довольно просто, взяв на вооружение простое правило - «никогда не выполнять запросы в цикле». Примеры того, как это можно сделать:
1. Выборки
$news_ids = get_list("SELECT news_id FROM today_news ");
while($news_id = get_next($news_ids))
$news = get_row("SELECT title, body FROM news WHERE news_id = ". $news_id);
Правило очень простое - чем меньше запросов, тем лучше (хотя из этого, как и из любого правила, есть исключения). Не забывайте про конструкцию IN(). Приведенный код можно написать одним запросом:
SELECT title, body FROM today_news INNER JOIN news USING(news_id)
2. Вставки
$log = parse_log();
while($record = next($log))
query("INSERT INTO logs SET value = ". $log["value"]);
Гораздо более эффективно склеить и выполнить один запрос:
INSERT INTO logs (value) VALUES (...), (...)
3. Обновления
Иногда бывает нужно обновить несколько строк в одной таблице. Если обновляемое значение одинаковое, то все просто:
UPDATE news SET title="test" WHERE id IN (1, 2, 3).
Если изменяемое значение для каждой записи разное, то это можно сделать таким запросом:
UPDATE news SET
title = CASE
WHEN news_id = 1 THEN "aa"
WHEN news_id = 2 THEN "bb" END
WHERE news_id IN (1, 2)
Наши тесты показывают, что такой запрос выполняется в 2-3 раза быстрее, чем несколько отдельных запросов.
Выполнение операций над проиндексированными полями
SELECT user_id FROM users WHERE blogs_count * 2 = $value
В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
SELECT user_id FROM users WHERE blogs_count = $value / 2;
Аналогичный пример:
SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered) <= 10;
Не будет использовать индекс по полю registered, тогда как
SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
будет.
Выборка строк только для подсчета их количества
$result = mysql_query(«SELECT * FROM table», $link);
$num_rows = mysql_num_rows($result);
Если вам нужно выбрать количество строк, удовлетворяющих определенному условию, используйте запрос SELECT COUNT(*) FROM table, а не выбирайте все строки лишь для того, чтобы подсчитать их количество.
Выборка лишних строк
$result = mysql_query(«SELECT * FROM table1», $link);
while($row = mysql_fetch_assoc($result) && $i < 20) {
…
}
Если вам нужны только n строк выборки, используйте LIMIT, вместо того, чтобы отбрасывать лишние строки в приложении.
Использование ORDER BY RAND()
SELECT * FROM table ORDER BY RAND() LIMIT 1;
Если в таблице больше, чем 4-5 тысяч строк, то ORDER BY RAND() будет работать очень медленно. Гораздо более эффективно будет выполнить два запроса:
Если в таблице auto_increment"ный первичный ключ и нет пропусков:
$rnd = rand(1, query("SELECT MAX(id) FROM table"));
$row = query("SELECT * FROM table WHERE id = ".$rnd);
Либо:
$cnt = query("SELECT COUNT(*) FROM table");
$row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
что, однако, так же может быть медленным при очень большом количестве строк в таблице.
Использование большого количества JOIN"ов
SELECT
v.video_id
a.name,
g.genre
FROM
videos AS v
LEFT JOIN
link_actors_videos AS la ON la.video_id = v.video_id
LEFT JOIN
actors AS a ON a.actor_id = la.actor_id
LEFT JOIN
link_genre_video AS lg ON lg.video_id = v.video_id
LEFT JOIN
genres AS g ON g.genre_id = lg.genre_id
Нужно помнить, что при связи таблиц один-ко многим количество строк в выборке будет расти при каждом очередном JOIN"е. Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.
Использование LIMIT
SELECT… FROM table LIMIT $start, $per_page
Многие думают, что подобный запрос вернет $per_page записей (обычно 10-20) и поэтому сработает быстро. Он и сработает быстро для нескольких первых страниц. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет. Многие просто ограничивают количество доступных страниц разумным числом. Также можно ускорить подобные запросы использованием покрывающих индексов или сторонних решений (например sphinx).
Неиспользование ON DUPLICATE KEY UPDATE
$row = query("SELECT * FROM table WHERE id=1");
If($row)
query("UPDATE table SET column = column + 1 WHERE id=1")
else
query("INSERT INTO table SET column = 1, id=1");
Подобную конструкцию можно заменить одним запросом, при условии наличия первичного или уникального ключа по полю id:
INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Читайте

От автора: один мой знакомый решил оптимизировать свой автомобиль. Сначала одно колесо снял, потому крышу спилил, затем мотор… В общем, сейчас он пешком ходит. Это все последствия неправильного подхода! Поэтому, чтобы ваша СУБД продолжала «ездить», оптимизация MySQL должна проходить правильно.

Когда оптимизировать и зачем?

Лишний раз лезть в настройки сервера и изменять значения параметров (особенно, если не знаете, чем это может закончиться) не стоит. Если рассматривать данную тему с «колокольни» улучшения производительности веб-ресурсов, то она настолько обширная, что ей нужно посвящать целое научное издание в 7 томах.

Но такого писательского терпения у меня явно нет, да и у вас читательского тоже. Мы поступим проще, и постараемся лишь слегка углубиться в чащи оптимизации MySQL сервера и его составляющих. С помощью оптимальной установки всех параметров СУБД можно достигнуть нескольких целей:

Увеличить скорость выполнения запросов.

Повысить общую производительность сервера.

Уменьшить время ожидания загрузки страниц ресурса.

Снизить потребление серверных мощностей хостинга.

Снизить объем занимаемого дискового пространства.

Постараемся всю тематику оптимизации разбить на несколько пунктов, чтоб было более-менее понятно, от чего «котелок» закипает .

Зачем настраивать сервер

В MySQL оптимизацию производительности следует начинать с сервера. Прежде всего, следует ускорить его работу и уменьшить время обработки запросов. Универсальным средством для достижения всех перечисленных целей является включения кэширования. Не знаете, «what is it»? Сейчас все поясню.

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

В MySQL оптимизация запросов применима к тем движкам и CMS, которые работают на основе данной СУБД и PHP. При этом код, написанный на языке программирования, для генерации динамической веб-страницы запрашивает некоторые ее структурные части и содержимое (записи, архивы и другие таксономии) из БД.

Благодаря включенному кэшированию в MySQL выполнение запросов к серверу СУБД происходит намного быстрее. За счет чего и повышается скорость загрузки всего ресурса в целом. А это положительно отражается и на пользовательском опыте, и на позиции сайта в выдаче.

Включаем и настраиваем кэширование

Но давайте вернемся от «скучной» теории к интересной практике. Дальнейшую оптимизацию базы MySQL продолжим с проверки состояния кэширования на вашем сервере БД. Для этого с помощью специального запроса мы выведем значения всех системных переменных:

Совсем другое дело.

Сделаем маленький обзор полученных значений, которые пригодятся нам для оптимизации баз данных MySQL:

have_query_cache – значение показывает «ВКЛ» кэширование запросов или нет.

query_cache_type – отображает активный тип кэша. Нам нужно значение «ON». Это говорит о том, что кэширование включено для всех видов выборки (команда SELECT). Кроме тех, в которых используется параметр SQL_NO_CACHE (запрещает сохранение информации об этом запросе).

У нас все настройки заданы правильно.

Отмеряем кэш под индексы и ключи

Теперь нужно проверить, сколько отведено оперативной памяти под индексы и ключи. Рекомендуется устанавливать этот важный для оптимизации БД MySQL параметр на 20-30% от объема оперативки, доступной для сервера. Например, если под экземпляр СУБД выделено 4 «гектара», то смело ставьте 32 «метра». Но все зависит от особенностей определенной базы и ее структуры (типов) таблиц.

Для установки значения параметра нужно отредактировать содержимое конфигурационного файла my.ini, который в Денвере находится по следующему пути: F:\Webserver\usr\local\mysql-5.5

Файл открываем с помощью Блокнота. Затем находим в нем параметр key_buffer_size и устанавливаем оптимальный для вашей системы ПК (в зависимости от «гектаров» оперативки) размер. После этого нужно перезапустить сервер БД.

В СУБД используется несколько дополнительных подсистем (нижнего уровня), и все основные их настройки также задаются в данном файле конфигурации. Поэтому, если нужно провести в MySQL InnoDB оптимизацию, то добро пожаловать сюда. Более подробно эту тему мы изучим в одном из наших следующих материалов.

Измеряем уровень индексов

Использование индексов в таблицах значительно повышает скорость обработки и формирования ответа СУБД на введенный запрос. MySQL постоянно «измеряет» уровень применения индексов и ключей в каждой БД. Для получения данного значения используйте запрос:

SHOW STATUS LIKE "handler_read%"

В полученном результате нас интересует значение в строке Handler_read_key. Если указанное там число маленькое, то это говорит о том, что индексы почти не используются в данной базе. А это плохо (как у нас ).

MySQL по-прежнему является самой популярной в мире реляционной базой данных, но в то же время и наиболее не оптимизированной. Многие люди остаются с настройками по умолчанию, не "копая" глубже. В этой статье мы рассмотрим некоторые советы по оптимизации MySQL в сочетании с некоторыми новинками, которые вышли относительно недавно.

Оптимизация конфигурации

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

Мы надеемся, что вы используете Linux или что-то вроде Vagrant -box (как наш Homestead Improved), а, соответственно, ваш конфигурационный файл будет находиться в /etc/mysql/my.cnf . Вполне возможно, что ваша установка на самом деле будет подгружать дополнительный файл конфигурации в этот. Так что посмотрите, если файл my.cnf содержит немного, то посмотрите в /etc/mysql/mysql.conf.d/mysqld.cnf .

Ручной тюнинг

Следующие настройки должны быть сделаны "из коробки". Согласно этим советам , добавьте в файл конфига в раздел :

Innodb_buffer_pool_size = 1G # (здесь поменяйте примерно 50%-70% от общего объема оперативы) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # можно поменять на 2 или 0 innodb_flush_method = O_DIRECT

innodb_buffer_pool_size . Пул буферизации (buffer pool) является эдаким "складом" для кэширования данных и индексов в памяти. Он используется, чтобы хранить часто используемые данные в памяти. И когда вы используете выделенный или виртуальный сервер, на котором зачастую именно БД является узким местом, то есть смысл отдать ей бОльшую часть оперативы. Следовательно, мы даем ей 50-70% всей RAM. В документации MySQL есть руководство по настройке этого пула .
innodb_log_file_size . Настройка размера лог-файла хорошо описана , но в двух словах это количество хранимых данных в логах, прежде чем его почистят. Обратите внимание, что лог в этом случае - это не записи об ошибках, а некий дельта-слепок изменений, которые ещё не были сброшены на диск в основные файлы innodb. MySQL пишет в фоновом режиме, но это все же влияет на производительность в момент записи. Большой лог-файл означает более высокую производительность из-за малого количества создаваемых новых и небольших контрольных точек, но при этом более длительное время восстановления в случае краша (больше данных должно быть переписано в БД).
innodb_flush_log_at_trx_commit описан и показывает, что происходит с файлом логов. Значение 1 - самое безопасное, т. к. лог сбрасывается на диск после каждой транзакции. При значениях 0 и 2 - меньше гарантируется ACID , но больше производительность. Разница не является достаточно большой, чтобы перевесить преимущества стабильности при 1.
innodb_flush_method . В довершение всего того, что касается сброса данных, эту настройку нужно установить в O_DIRECT - чтобы избежать двойной буферизации. Советую всегда это делать, пока система ввода-вывода остаётся очень медленной. Хотя на большинстве хостингах, типа DigitalOcean, вы будете иметь SSD-диски, поэтому система ввода-вывода будет более производительна.

Есть инструмент от Percona, который поможет нам найти оставшиеся проблемы автоматически. Обратите внимание, что если мы бы запустили его без этой ручной настройки, то только 1 из 4 настроек была бы определена, т. к. другие 3 зависят от предпочтений пользователя и окружающей среды приложения.

Variable Inspector

Установка variable inspector на Ubuntu:

Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo apt-get update sudo apt-get install percona-toolkit

Для других систем, следуйте этим инструкциям .

Затем запустите toolkit:

Pt-variable-advisor h=localhost,u=homestead,p=secret

Вы увидите такой результат:

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary. # NOTE max_binlog_size: The max_binlog_size is smaller than the default of 1GB. # NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it. # NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later. # WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

Прим. переводчика:
На моей локальной машине, кроме этого, выдал ещё вот такой ворнинг:

# NOTE innodb_flush_method: Most production database servers that use InnoDB should set innodb_flush_method to O_DIRECT to avoid double-buffering, unless the I/O system is very low performance.

О том, что параметр innodb_flush_method нужно установить в O_DIRECT и почему говорилось выше. И если вы придерживались последовательности тюнинга как в статье, то вы не увидите это предупреждение.

Ни одно из этих (прим.пер.: указанных автором ) предупреждений не критично, их необязательно исправлять. Единственное, что можно поправить - это настройка бинарного лога для репликации и снапшотов.

Примечание: в новых версиях размер binlog-а по умолчанию 1G и этого ворнинга не будет.

Max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "ROW"

max_binlog_size . Определяет насколько большими будут бинарные логи. В них записываются ваши транзакции и запросы и делаются контрольные точки. Если транзакция превышает максимум, то лог может превышать свой размер при сохранении на диск; в противном случае MySQL будет поддерживать его в рамках этого лимита.
log_bin . Эта опция включает запись бинарных логов в целом. Без неё невозможны снапшоты или репликации. Обратите внимание, что это может очень сказаться на дисковом пространстве. server-id - это необходимая опция при включении бинарного лога, поэтому логи "знают" с какого сервера они пришли (для репликации), а binlog-format - это просто способ, которым они записываются.

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

MySQL Tuner

Вспомогательные инструменты: Percona Toolkit для выявления дублирующих индексов

Ранее установленный нами Percona Toolkit также имеет инструмент для обнаружения дублирующих индексов, который может пригодиться при использовании сторонних CMS или просто проверить себя - вдруг вы случайно добавили больше индексов, чем нужно. Например, установка WordPress по умолчанию имеет дублирующие индексы в таблице wp_posts:

Pt-duplicate-key-checker h=localhost,u=homestead,p=secret # ######################################################################## # homestead.wp_posts # ######################################################################## # Key type_status_date ends with a prefix of the clustered index # Key definitions: # KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`), # PRIMARY KEY (`ID`), # Column types: # `post_type` varchar(20) collate utf8mb4_unicode_520_ci not null default "post" # `post_status` varchar(20) collate utf8mb4_unicode_520_ci not null default "publish" # `post_date` datetime not null default "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `homestead`.`wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

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

Вспомогательные инструменты: Percona Toolkit для неиспользуемых индексов

Percona Toolkit может также обнаружить неиспользуемые индексы. Если вы логируете медленные запросы (см. раздел "узкие места" ниже), вы можете запустить утилиту и она будет проверять, используют ли эти запросы индексы в таблицах и как именно.

Pt-index-usage /var/log/mysql/mysql-slow.log

Подробную информацию об использовании этой утилиты см. .

Узкие места

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

Для начала, давайте включим логирование медленных запросов:

Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1

Строки выше должны быть добавлены в конфигурацию mysql. БД будет отслеживать запросы, которые выполнялись больше чем 1 секунду, и те, которые не используют индексы.

Как только в этом логе появятся некоторые данные, вы можете проанализировать их на предмет использования индексов с помощью вышеуказанной утилиты pt-index-usage или с помощью pt-query-digest , которая выведет примерно такие результаты:

Pt-query-digest /var/log/mysql/mysql-slow.log # 360ms user time, 20ms system time, 24.66M rss, 92.02M vsz # Current date: Thu Feb 13 22:39:29 2014 # Hostname: * # Files: mysql-slow.log # Overall: 8 total, 6 unique, 1.14 QPS, 0.00x concurrency ________________ # Time range: 2014-02-13 22:23:52 to 22:23:59 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 3ms 267us 406us 343us 403us 39us 348us # Lock time 827us 88us 125us 103us 119us 12us 98us # Rows sent 36 1 15 4.50 14.52 4.18 3.89 # Rows examine 87 4 30 10.88 28.75 7.37 7.70 # Query size 2.15k 153 296 245.11 284.79 48.90 258.32 # ==== ================== ============= ===== ====== ===== =============== # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============= ===== ====== ===== =============== # 1 0x728E539F7617C14D 0.0011 41.0% 3 0.0004 0.00 SELECT blog_article # 2 0x1290EEE0B201F3FF 0.0003 12.8% 1 0.0003 0.00 SELECT portfolio_item # 3 0x31DE4535BDBFA465 0.0003 12.6% 1 0.0003 0.00 SELECT portfolio_item # 4 0xF14E15D0F47A5742 0.0003 12.1% 1 0.0003 0.00 SELECT portfolio_category # 5 0x8F848005A09C9588 0.0003 11.8% 1 0.0003 0.00 SELECT blog_category # 6 0x55F49C753CA2ED64 0.0003 9.7% 1 0.0003 0.00 SELECT blog_article # ==== ================== ============= ===== ====== ===== =============== # Query 1: 0 QPS, 0x concurrency, ID 0x728E539F7617C14D at byte 736 ______ # Scores: V/M = 0.00 # Time range: all events occurred at 2014-02-13 22:23:52 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 37 3 # Exec time 40 1ms 352us 406us 375us 403us 22us 366us # Lock time 42 351us 103us 125us 117us 119us 9us 119us # Rows sent 25 9 1 4 3 3.89 1.37 3.89 # Rows examine 24 21 5 8 7 7.70 1.29 7.70 # Query size 47 1.02k 261 262 261.25 258.32 0 258.32 # String: # Hosts localhost # Users * # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS LIKE "blog_article"\G # SHOW CREATE TABLE `blog_article`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_.updated AS updated7 FROM blog_article b0_ ORDER BY b0_.created DESC LIMIT 10

Если вы предпочитаете анализировать эти логи вручную, вы можете сделать то же самое, но сначала вам нужно экспортировать лог в более анализируемый формат. Это можно сделать так:

Mysqldumpslow /var/log/mysql/mysql-slow.log

С дополнительными параметрами можно отфильтровать данные, чтобы экспортировать только нужное. Например, топ-10 запросов, отсортированных по среднему времени выполнения:

Mysqldumpslow -t 10 -s at /var/log/mysql/localhost-slow.log

Заключение

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

Мы разобрались с оптимизацией конфигурации, мы прокачались по индексам, и мы избавились от некоторых узких мест. Все это было в основном теорией, однако, всё это применимо на реальных приложениях.

В повседневной работе приходится сталкиваться с довольно однотипными ошибками при написании запросов.

В этой статье хотелось бы привести примеры того, как НЕ надо писать запросы.

Выборка всех полей
SELECT * FROM table
При написании запросов не используйте выборку всех полей - "*". Перечислите только те поля, которые вам действительно нужны. Это сократит количество выбираемых и пересылаемых данных. Кроме этого, не забывайте про покрывающие индексы. Даже если вам на самом деле необходимы все поля в таблице, лучше их перечислить. Во-первых, это повышает читабельность кода. При использовании звездочки невозможно узнать какие поля есть в таблице без заглядывания в нее. Во-вторых, со временем количество столбцов в вашей таблице может изменяться, и если сегодня это пять INT столбцов, то через месяц могут добавиться TEXT и BLOB поля, которые будут замедлять выборку.
Запросы в цикле.
Нужно четко представлять себе, что SQL - язык, оперирующий множествами. Порой программистам, привыкшим думать терминами процедурных языков, трудно перестроить мышление на язык множеств. Это можно сделать довольно просто, взяв на вооружение простое правило - «никогда не выполнять запросы в цикле». Примеры того, как это можно сделать:
1. Выборки
$news_ids = get_list("SELECT news_id FROM today_news ");
while($news_id = get_next($news_ids))
$news = get_row("SELECT title, body FROM news WHERE news_id = ". $news_id);
Правило очень простое - чем меньше запросов, тем лучше (хотя из этого, как и из любого правила, есть исключения). Не забывайте про конструкцию IN(). Приведенный код можно написать одним запросом:
SELECT title, body FROM today_news INNER JOIN news USING(news_id)
2. Вставки
$log = parse_log();
while($record = next($log))
query("INSERT INTO logs SET value = ". $log["value"]);
Гораздо более эффективно склеить и выполнить один запрос:
INSERT INTO logs (value) VALUES (...), (...)
3. Обновления
Иногда бывает нужно обновить несколько строк в одной таблице. Если обновляемое значение одинаковое, то все просто:
UPDATE news SET title="test" WHERE id IN (1, 2, 3).
Если изменяемое значение для каждой записи разное, то это можно сделать таким запросом:
UPDATE news SET
title = CASE
WHEN news_id = 1 THEN "aa"
WHEN news_id = 2 THEN "bb" END
WHERE news_id IN (1, 2)
Наши тесты показывают, что такой запрос выполняется в 2-3 раза быстрее, чем несколько отдельных запросов.
Выполнение операций над проиндексированными полями
SELECT user_id FROM users WHERE blogs_count * 2 = $value
В таком запросе индекс использоваться не будет, даже если столбец blogs_count проиндексирован. Для того, чтобы индекс использовался, над проиндексированным полем в запросе не должно выполняться преобразований. Для подобных запросов выносите функции преобразования в другую часть:
SELECT user_id FROM users WHERE blogs_count = $value / 2;
Аналогичный пример:
SELECT user_id FROM users WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(registered) <= 10;
Не будет использовать индекс по полю registered, тогда как
SELECT user_id FROM users WHERE registered >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
будет.
Выборка строк только для подсчета их количества
$result = mysql_query(«SELECT * FROM table», $link);
$num_rows = mysql_num_rows($result);
Если вам нужно выбрать количество строк, удовлетворяющих определенному условию, используйте запрос SELECT COUNT(*) FROM table, а не выбирайте все строки лишь для того, чтобы подсчитать их количество.
Выборка лишних строк
$result = mysql_query(«SELECT * FROM table1», $link);
while($row = mysql_fetch_assoc($result) && $i < 20) {
…
}
Если вам нужны только n строк выборки, используйте LIMIT, вместо того, чтобы отбрасывать лишние строки в приложении.
Использование ORDER BY RAND()
SELECT * FROM table ORDER BY RAND() LIMIT 1;
Если в таблице больше, чем 4-5 тысяч строк, то ORDER BY RAND() будет работать очень медленно. Гораздо более эффективно будет выполнить два запроса:
Если в таблице auto_increment"ный первичный ключ и нет пропусков:
$rnd = rand(1, query("SELECT MAX(id) FROM table"));
$row = query("SELECT * FROM table WHERE id = ".$rnd);
Либо:
$cnt = query("SELECT COUNT(*) FROM table");
$row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
что, однако, так же может быть медленным при очень большом количестве строк в таблице.
Использование большого количества JOIN"ов
SELECT
v.video_id
a.name,
g.genre
FROM
videos AS v
LEFT JOIN
link_actors_videos AS la ON la.video_id = v.video_id
LEFT JOIN
actors AS a ON a.actor_id = la.actor_id
LEFT JOIN
link_genre_video AS lg ON lg.video_id = v.video_id
LEFT JOIN
genres AS g ON g.genre_id = lg.genre_id
Нужно помнить, что при связи таблиц один-ко многим количество строк в выборке будет расти при каждом очередном JOIN"е. Для подобных случаев более быстрым бывает разбить подобный запрос на несколько простых.
Использование LIMIT
SELECT… FROM table LIMIT $start, $per_page
Многие думают, что подобный запрос вернет $per_page записей (обычно 10-20) и поэтому сработает быстро. Он и сработает быстро для нескольких первых страниц. Но если количество записей велико, и нужно выполнить запрос SELECT… FROM table LIMIT 1000000, 1000020, то для выполнения такого запроса MySQL сначала выберет 1000020 записей, отбросит первый миллион и вернет 20. Это может быть совсем не быстро. Тривиальных путей решения проблемы нет. Многие просто ограничивают количество доступных страниц разумным числом. Также можно ускорить подобные запросы использованием покрывающих индексов или сторонних решений (например sphinx).
Неиспользование ON DUPLICATE KEY UPDATE
$row = query("SELECT * FROM table WHERE id=1");
If($row)
query("UPDATE table SET column = column + 1 WHERE id=1")
else
query("INSERT INTO table SET column = 1, id=1");
Подобную конструкцию можно заменить одним запросом, при условии наличия первичного или уникального ключа по полю id:
INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

Читайте

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

Зачем оптимизировать запросы к базе данных

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

Вникнуть в суть дела не помешает и тем веб-мастерам, которые используют известные системы администрирования и любят подключать всевозможные плагины сторонних разработчиков, а так же кастомизировать темы под себя, к примеру, на самой популярной бесплатной CMS – WordPress.

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

Результат был виден невооруженным глазом, ведь в случае использования mysql_num_rows, страница подвисала секунд на 5, после чего выводился результат. Во втором же случае мы получали результат в виде количества записей в таблице практически моментально. Нам даже не пришлось замерять время загрузки скрипта при помощи микротаймера, ведь результат был более чем очевиден.

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

Как оптимизировать запросы к базе данных

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

Нам придется обратиться за помощью к интерфейсу популярного и очень удобного phpmyadmin. Для того, чтобы начать, нам нужно выбрать одну из имеющихся баз данных и создать в ней тестовую таблицу. Ее название в нашем случае будет довольно банальным – test.

CREATE TABLE `test` (`ID` INT NOT NULL AUTO_INCREMENT , `TITLE` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `ANNOUNCEMENT` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `TEXT` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , PRIMARY KEY (`ID`)) ENGINE = MYISAM ;

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

Заголовок
Анонс
Полный текст

За абстрактными текстами мы по привычке пойдем на сервис Яндекс.Рефераты , созданный как раз для подобных целей. Нам посчастливилось наткнуться на тему «Торсионный фотон в XXI веке», ее и возьмем.

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

Получившийся запрос мы сюда копировать не будем, т. к. это будет более 4000 символов не уникального текста, взятого у самого Яндекса, что довольно дерзко, да и вам это тоже не нужно. Лучше мы набросаем простейший цикл на PHP, который быстро добавит в базу данных столько записей, сколько мы захотим. Для начала это будет 100000 статей.

Чем меньше запросов к базе данных, тем лучше

Уже на этом этапе мы покажем вам распространенную ошибку, которую сами же сейчас специально и допустим.

For($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }

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

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

INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст"), (NULL, "Заголовок", "Анонс", "Полный текст"), (NULL, "Заголовок", "Анонс", "Полный текст"), …

Если вернуться к нашему первому способу, то он бы выглядел вот так:

INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст") INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст") INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст") …

Чувствуете разницу? Вариант, в котором используется только одно обращение к базе данных и является оптимальным. Скорость работы этих двух способов, приводящих к одному и тому же результату отличается в разы и видна без всяких измерений невооруженным глазом, поверьте, это действительно так.

Производить выборку только необходимых скрипту полей

Здесь все очень просто – та или иная функция нуждается в определенных данных из целевой таблицы. Очень часто оказывается так, что нужно вытащить вообще все поля, особенно, если таблица довольно большая и этих полей больше 10.

SELECT * FROM `test`

В данном запросе звездочка означает то, что будут извлечены данные из всех полей таблицы test. А что, если этих полей в таблице 20-30 штук или больше? Скрипту скорее всего необходимы лишь некоторые из них, а все остальные, которые не будут никак использоваться, будут выбраны зря. Такая операция будет выполняться медленнее, чем если бы вы указали через запятую только те поля, которые вам действительно нужны в данный момент.

SELECT `ID`, `TITLE` FROM `test`

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

Объединение нескольких запросов в один

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

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

Допустим, вам нужно вывести на экран какую-нибудь случайную статью, а снизу подписать ее именем автора. Связь таблиц между собой в данном случае очевидна и происходит по идентификатору пользователя, т. е. ID пользователя в таблице users должен соответствовать полю USER_ID в таблице posts. Данная связь является стандартной и должна быть понятна всем, без исключения.

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

$rs_post = mysql_query("SELECT `ID`, `USER_ID`, `TITLE`, `TEXT` FROM `posts` ORDER by RAND() LIMIT 1");

Из таблицы posts случайным образом выберется одна статья. После чего наши действия будут иметь примерно такой вид:

$row_post = mysql_fetch_assoc($rs_post); $userID = $row_post["USER_ID"];

Теперь переменная $userID содержит идентификатор пользователя, являющегося автором этой статьи и для того, чтобы получить его данные, например NAME (имя) и SURNAME (фамилию), вы будете обращаться к таблице users и запрос будет выглядеть примерно так:

$rs_user = mysql_query("SELECT `NAME`, `SURNAME` FROM `users` WHERE `ID` = "".$row_post["USER_ID"]."" LIMIT 1");

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

Но эти два запроса можно оптимизировать, превратив в один. Для этого мы воспользуемся конструкцией LEFT JOIN:

SELECT `posts`.`ID`, `posts`.`USER_ID`, `posts`.`TITLE`, `posts`.`TEXT`, `users`.`NAME`, `users`.`SURNAME` FROM `posts` LEFT JOIN `users` ON `posts`.`USER_ID` = `users`.`ID` ORDER by RAND() LIMIT 1

Как видите, ничего сложного в приведенной конструкции нет и все интуитивно понятно. Единственное, на что хочется обратить ваше внимание – это явное указание таблиц в паре с полями, т. к. идет выборка сразу из нескольких таблиц. Если же названия каких-то полей совпадают, то следует использовать так называемые mySQL алиасы , чтобы потом не путаться при выводе результата.

Заключение

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

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