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

MySQL располагает большим набором функций для различных сортировок (ORDER BY ), группировок (GROUP BY ), объединений (LEFT JOIN или RIGHT JOIN ) и так далее. Все они безусловно удобны, но в условиях одноразовых запросов. К примеру, если лично Вам требуется что-то откопать в базе используя кучу таблиц и связок, то кроме вышеперечисленных функций можно и даже нужно применять условный операторы IF . Главная ошибка начинающих программистов это стремление применить такие запросы в рабочем коде сайта. В данном случае сложный запрос безусловно красив, но вреден. Все дело в том, что любые операторы сортировок, группировок, объединений или вложенных запросов, не могут выполняться в оперативной памяти, и используют жесткий диск для создания временных таблиц. А хард, как известно - самое узкое место сервера.

Правила оптимизации mysql запросов

1. Избегайте вложенных запросов

Это самая серьезная ошибка. Родительский процесс всегда будет ждать завершения дочернего и в это время держать коннект к базе, использовать диск и нагружать iowait. Два параллельных запроса в базу и выполнения нужных фильтраций в серверном интерпретаторе (Perl , PHP и т. д.), выполнятся на порядок быстрее чем вложенный.

Примеры на perl , как делать не следует:

My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { my $groupNAME = $dbh->selectrow_array("SELECT groupNAME FROM groups WHERE groupID = $row"); ### Допустим нужно собрать названия групп ### и добавить их в конец массива с данными push @row => $groupNAME; ### Делаем еще что-нибудь... }

или не в коем случае вот так:

My $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(SELECT groupID FROM groups WHERE groupNAME = "Первая" OR groupNAME = "Вторая" OR groupNAME = "Седьмая")");

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

Пример на perl, как делаю обычно я:

My %groups; my $sth = $dbh->prepare("SELECT groupID,groupNAME FROM groups WHERE groupID IN(2,3,7)"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { $groups{$row} = $row; } ### А теперь выполням основную выборку без вложенного запроса my $sth2 = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth2->execute(); while (my @row = $sth2->fetchrow_array()) { push @row => $groups{$row}; ### Делаем еще что-нибудь... }

2. Не сортируйте, не группируйте и не фильтруйте в базе

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

Примеры на perl, как сортировать не следует:

My $sth = $dbh->prepare("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7) ORDER BY elementNAME"); $sth->execute(); while (my @row = $sth->fetchrow_array()) { print qq{$row => $row}; }

Пример на perl, как сортирую обычно я:

My $list = $dbh->selectall_arrayref("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7)"); foreach (sort { $a-> cmp $b-> } @$list){ print qq{$_-> => $_->}; }

Так намного быстрее. Особенно заметна разница если данных много. В случае, если нужно отсортировать в perl по нескольким полям, можно применить сортировку Шварца . Если требуется произвольная сортировка ORDER BY RAND() - используйте сортировку random в perl .

3. Используйте индексы

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

Таким запросом:

ALTER TABLE `any_db`.`any_tbl` ADD INDEX `text_index`(`text_fld`(255));

Где 255 - длина ключа. Для некоторых типов данных он не требуется. Подробности в документации к MySQL.

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

1. Оптимизация таблиц . Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины - text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

   OPTIMIZE TABLE `table1`, `table2`…

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

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

ALTER TABLE `table1` ORDER BY `id`

Тип данных . Лучше не индексировать поля, имеющие строковый тип, особенно поля типа TEXT. Для таблиц, данные которых часто изменяются, желательно избегать использования полей типа VARCHAR и BLOB, так как данный тип создаёт динамическую длину строки, тем самым увеличивая время доступа к данным. При этом советуют использовать поле VARCHAR вместо TEXT, так как с ним работа происходит быстрее.

NOT NULL и поле по умолчанию . Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.

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

Разделение данных. Длинные не ключевые поля советуют выделить в отдельную таблицу в том случае, если по исходной таблице происходит постоянная выборка данных и которая часто изменяется. Данный метод позволит сократить размер изменяемой части таблицы, что приведёт к сокращению поиска информации.
Особенно это актуально в тех случаях, когда часть информации в таблице предназначена только для чтения, а другая часть - не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример - счётчик посещений.
Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе - текстовое, а третье числовое - считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже - отсыл к ключевому полю id из первой таблицы.
Теперь постоянные обновления будут происходить во второй таблице. При этом изменять количество посещений лучше не программно, а через запрос:

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

SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN first ON (first.id = second.first_id)

Стоит помнить, что всё это не актуально для сайтов с малой посещаемостью и малым количеством информации.

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

Требовать меньше данных . При возможности избегать запросов типа:

SELECT * FROM `table1`

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

SELECT id, name FROM table1 ORDER BY id LIMIT 25

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится "легче" и производительнее.
Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.
Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.
Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.
Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

Ограничить использование DISTINCT . Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.
Есть маленькая хитрость. Если необходимо просмотреть две таблицы на тему соответствия, то приведённая команда остановится сразу же, как только будет найдено первое соответствие.

Ограничить использование SELECT для постоянно изменяющихся таблиц .

2. Не забывайте про временные таблицы типа HEAP . Несмотря на то, что таблица имеет ограничения, в ней удобно хранить промежуточные данные, особенно когда требуется сделать ещё одну выборку из таблицы без повторного обращения. Дело в том, что эта таблица хранится в памяти и поэтому доступ к ней очень быстрый.
3. Поиск по шаблону . Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%.

Как оптимизировать MySQL запросы?

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

Почему MySQL?

Сегодня идет много разговоров о Dig Data и других новых технологиях. NoSQL и облачные решения это супер, но много популярного софта (такого как WordPress, phpBB, Drupal) до сих пор работает на MySQL. Миграция на новейшие решения может вылиться не только в изменении конфигурации на серверах. К тому же, эффективность MySQL до сих пор на уровне, особенно версия Percona.

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

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

Для кого эта статья?

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

Сначала бэкап

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

Делать бэкапы MySQL просто, используя утилиту mysqldump:

$ mysqldump myTab > myTab-backup.sql Вы можете узнать больше о mysqldump .

Что делает запрос медленным?

Вот общий список факторов, влияющих на скорость выполнения запросов и нагрузки сервера:

• индексы таблиц;
• условие WHERE(и использования внутренних функций MySQL, например, таких как IF или DATE);
• сортировка по ORDER BY;
• частое повторение одинаковых запросов;
• тип механизма хранения данных (InnoDB, MyISAM, Memory, Blackhole);
• не использование версии Percona;
• конфигурации сервера (my.cnf / my.ini);
• большие выдачи данных (более 1000 строк);
• нестойкое соединение;
• распределенная или кластерная конфигурация;
• слабое проектирование таблиц.

Далее мы обратимся ко всем этим проблемам. Также, установите Percona , если вы еще не используете эту встроенную замену стандартному MySQL - это придаст сильное увеличение мощности базы данных.

Что такое индексы?

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

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

Нестойкое соединение?

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

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

Наиболее быстрый и эффективный способ, который я нашел для этого - это создание хранилища запросов и результатов их выполнения с помощью Memcached или Redis. С Memcache вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

connect("localhost",11211); $cacheResult = $cache->get("key-name"); if($cacheResult){ //не нуждаемся в запросе $result = $cacheResult; } else { //запускаем ваш запрос $mysqli = mysqli("p:localhost","username","password","table"); //добавляйте p: для договременного хранения $sql = "SELECT * FROM posts LEFT JOIN userInfo using (UID) WHERE posts.post_type = "post" || posts.post_type = "article" ORDER BY column LIMIT 50"; $result = $mysqli->query($sql); $memc->set("key-name", $result->fetch_array(), MEMCACHE_COMPRESSED,86400); } //Пароль $cacheResult в шаблон $template->assign("posts", $cacheResult); ?> Теперь тяжелый запрос, использующий LEFT JOIN, будет выполняться только раз за каждые 86 400 секунд (то есть раз в сутки), что значительно уменьшит нагрузку MySQL сервера, оставив ресурсы для других соединений.

Примечание: Допишите p: в начале аргумента хоста MySQLi для создания постоянного соединения.

Распределенная или кластерная конфигурация

Когда данных становится все больше, и скорость вашего сервиса идет под уклон, паника может овладеть вами. Быстрым решением может стать распределения ресурсов (sharding). Однако я не рекомендую делать это, если вы не обладаете хорошим опытом, поскольку распределение по своей сути делает структуры данных сложнейшими.

Слабое проектирование таблиц

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

Обеспечение правильного проектирования для нужного использования является первостепенным в создании вашего приложения. Храните различные данные в различных таблицах (например, категории и статьи) и убедитесь, что отношения к другу (many to one) и один ко многим (one to many) могут быть легко связаны с идентификаторами (ID). Использование FOREIGN KEY в MySQL идеально подходит для хранения каскадных данных в таблицах.

При создании таблицы помните следующее:

• Создавайте эффективные таблицы для решения ваших задач, а не заполняйте таблицы лишними данными и связями.
• Не ожидайте от MySQL выполнения вашей бизнес логики или програмности - данные должны быть готовы к вставке строки вашей скриптовым языком. Например, если вам нужно отсортировать список в случайном порядке, сделайте это в массиве PHP, не используя ORDER BY из арсенала MySQL.
• Используйте индексные типы UNIQUE для уникальных наборов данных и применяйте ON DUPLICATE KEY UPDATE, чтобы хранить дату обновленной, например, для того, чтобы знать, когда строка была в последний раз изменена.
• Используйте тип данных INT для сохранения целых чисел. Если вы не укажете размер типа данных, MySQL сделает это за вас.

Основы оптимизации

Для эффективной оптимизации мы должны применять три подхода к вашему приложению:

1. Анализ (логирование медленных запросов, изучение системы, анализ запросов и проектирование базы данных)
2. Требования к исполнению (сколько пользователей)
3. Ограничения технологий (скорость железа, неправильное использование MySQL)

Анализ может быть сделан несколькими путями. Сначала мы рассмотрим наиболее очевидные способы, чтобы заглянуть под капот вашей MySQL, в котором выполняются запросы. Самый первый инструмент оптимизации в вашем арсенале это EXPLAIN. Если добавить этот оператор перед вашим запросом по SELECT, результат запроса будет таким:

Колонки, вы видите, сохраняют важную информацию о запросе. Колонки, на которые вы должны обратить наибольшее внимание это possible_keys и Extra.

Колонка possible_keys покажет индексы, в которые MySQL имел доступ, чтобы выполнить запрос. Иногда нужно назначить индексы, чтобы запрос выполнялся быстрее. Колонка Extra покажет, были ли использованы дополнительные WHEREили ORDER BY. Наиболее важно обратить внимание, есть ли Using Filesort в выводе.

Что делает Using Filesort, указано в справке MySQL:

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

Лишний проход замедлит ваше приложение, этого нужно избегать, чего бы это ни стоило. Другой критический результат Extra, который мы должны избегать - это Using temporary. Он говорит о том, что MySQL пришлось создать временную таблицу для выполнения запроса. Очевидно, это ужасное использования MySQL. В таком случае результат запроса должен быть сохранен в Redis или Memcache и не выполняться пользователями лишний раз.

Чтобы избежать проблемы с Using Filesort мы должны увериться, что MySQL использует INDEX. Сейчас указано несколько ключей в possible_keys, из которых можно выбирать, но MySQL может выбрать только один индекс для финального запроса. Также индексы могут быть составлены из нескольких колонок, также вы можете ввести подсказки (хинты) для оптимизатора MySQL, указывая на индексы, что вы создали.

Хинтинг индексов

Оптимизатор MySQL будет использовать статистику, основанную на запросах таблиц, чтобы выбрать лучший индекс для выполнения запроса. Он действует достаточно просто, основываясь на встроенной статистической логике, поэтому имея несколько вариантов, не всегда делает правильный выбор без помощи хинтинга. Чтобы убедиться, что был использован правильный (или неправильный) ключ, воспользуйтесь ключевым словам FORCE INDEX, USE INDEX и IGNORE INDEX в вашем запросе. Вы можете прочитать больше о хинтинге индексов в справке MySQL .

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

В дополнение к EXPLAIN существует ключевое слово DESCRIBE. Вместе с DESCRIBE можно просматривать информацию из таблицы следующим образом:

Добавляем индекс

Для добавления индексов в MySQL надо использовать синтаксис CREATE INDEX. Есть несколько видов индексов. FULLTEXT Применяется для полнотекстового поиска, а UNIQUE - для хранения уникальных данных.

Чтобы добавить индекс в вашу таблицу, используйте следующий синтаксис:

Mysql> CREATE INDEX idx_bookname ON `books` (bookname(10)); Это создаст индекс на таблице books, которая будет использовать первые 10 букв из колонки, которая хранит названия книг и имеет тип varchar. В этом случае, любой поиск с запросом WHERE на название книги с совпадением до 10 символов будет давать такой же результат, как и просмотр всей таблицы от начала до конца.

Композитные индексы

Индексы имеют большое влияние на скорость выполнения запросов. Только назначения главного уникального ключа недостаточно - композитные ключи являются реальной областью применения в настройке MySQL, что иногда требует некоторых A/B проверок с использованием EXPLAIN.

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

Mysql> CREATE INDEX idx_composite ON users (username, active); Как только мы создали ключ на основе колонки username, в котором хранится имя пользователя и колонки active типа ENUM, определяющий, активен ли его аккаунт. Теперь все оптимизировано для запроса, который будет использовать WHERE для поиска валидного имени пользователя с активным аккаунтом (active = 1).

Насколько быстра ваша MySQL?

Включим профилирование, чтобы подробнее рассмотреть MySQL запросы. Это можно сделать, выполнив команду set profiling=1, после чего для просмотра результата надо выполнить show profiles.

Если вы используете PDO, выполните следующий код:

$db->query("set profiling=1"); $db->query("select headline, body, tags from posts"); $rs = $db->query("show profiles"); $db->query("set profiling=0"); // отключить профилирование после выполнения запроса $records = $rs->fetchAll(PDO::FETCH_ASSOC); // получить результаты профилирования $errmsg = $rs->errorInfo(); //Отлавливаем некоторые ошибки здесь То же самое можно сделать с помощью mysqli:

$db = new mysqli($host,$username,$password,$dbname); $db->query("set profiling=1"); $db->query("select headline, body, tags from posts"); if ($result = $db->query("SHOW profiles", MYSQLI_USE_RESULT)) { while ($row = $result->fetch_row()) { var_dump($row); } $result->close(); } if ($result = $db->query("show profile for query 1", MYSQLI_USE_RESULT)) { while ($row = $result->fetch_row()) { var_dump($row); } $result->close(); } $db->query("set profiling=0"); Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

Array(3) { => string(1) "1" => string(10) "0.00024300" => string(17) "select headline, body, tags from posts" } Этот запрос выполнялся 0.00024300 секунд. Это довольно быстро, поэтому не будем беспокоиться. Но когда числа становятся большими, мы должны смотреть глубже. Перейдите к вашему приложению, чтобы потренироваться на рабочем примере. Проверьте константу DEBUG в конфигурации вашей базы данных, а затем начните изучать систему, включив вывод результатов профилирования с помощью функций var_dump или print_r. Так вы сможете переходить со страницы на страницу в вашем приложении, получив удобное профилирование системы.

Полный аудит работы базы вашего сайта

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

Чтобы включить логирование в MySQL 5.1.6 используйте глобальную переменную log_slow_queries, также вы можете отметить файл для логирования с помощью переменной slow_query_log_file. Это можно сделать, выполнив следующий запрос:

Set global log_slow_queries = 1; set global slow_query_log_file = /dev/slow_query.log; Также это можно указать в файлах конфигурации /etc/my.cnf или my.ini вашего сервера.

После внесения изменений не забудьте перезагрузить MySQL сервер необходимой командой, например service mysql restart, если вы используете Linux.

В версиях MySQL после 5.6.1 переменная log_slow_queries обозначена как устаревшая и вместо нее используется slow_query_log. Также для более удобного дебаггинга можно включить вывод в таблице, задав переменной log_output значение TABLE, однако эта функция доступна только с MySQL 5.6.1.

Log_output = TABLE; log_queries_not_using_indexes = 1; long_query_time = 1; Переменная long_query_time определяет количество секунд, после которых выполнение запроса считается медленным. Значение это 10, а минимум это 0. Также можно указать миллисекунды, используя дробь; сейчас я указал одну секунду. И теперь каждый запрос, который будет выполняться дольше 1 секунды, записывается в логи в таблице.

Логирование будет вестись в таблицах mysql.slow_log и mysql.general_log вашей MySQL базы данных. Чтобы выключить логирование, измените log_output на NONE.

Логирование на рабочем сервере

На рабочем сервере, который обслуживает клиентов, лучше применять логирование только на короткий период и для мониторинга нагрузки, чтобы не создавать лишней нагрузки. Если ваш сервис перегружен и необходимо безотлагательное вмешательство, попробуйте выделить проблему, выполнив SHOW PROCESSLIST, или обратитесь к таблице information_schema.PROCESSLIST, выполнив SELECT * FROM information_schema.PROCESSLIST;.

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

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

Логирование множества запросов

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

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

Горячий и холодный кэш

Количество запросов и нагрузка сервера имеет сильное влияние на исполнение, также может повлиять на время выполнения запросов. При разработке вы должны взять за правило, что выполнение каждого запроса должно быть не более доли миллисекунды (0.0xx или быстрее) на свободном сервере.

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

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

Исправление медленных запросов

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

Если вы пока не нашли медленного запроса, проверьте настройки long_query_time, если вы пользуетесь этим методом логирования. Иначе, проверив все ваши запросы профилирования (set profiling=1), составьте список запросов, отнимают больше времени, чем доля миллисекунд (0.000x секунд) и начнем из них.

Распространенные проблемы

Вот шесть самых распространенных проблем, которые я находил, оптимизируя MySQL запросы:

ORDER BY и filesort

Предотвращение filesort иногда невозможно из-за выражения ORDER BY. Для оптимизации сохраните результат в Memcache, или выполните сортировку в логике вашего приложения.

Использование ORDER BY вместе с WHERE и LEFT JOIN

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

Применение ORDER BY по временным колонками

Просто не делайте этого. Если вам нужно объединить результаты, сделайте это в логике вашего приложения; не используйте фильтрацию или сортировку во временной таблице запроса MySQL. Это требует много ресурсов.

Игнорирование индекса FULLTEXT

Использование LIKE это самый лучший способ сделать полнотекстовый поиск медленным.

Беспричинный выбор большого количества строк

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

Чрезмерное использование JOIN вместо создания композитных таблиц или представления

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

Итак

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

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

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 летал.

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

От того, насколько хорошо оптимизированы запросы к базе данных 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 алиасы , чтобы потом не путаться при выводе результата.

Заключение

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

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