Главная › Интернет › Обращение к mysql через php. Примеры SQL-запросов в MariaDB (MySQL). Используйте значение NOT NULL вместо NULL

Обращение к mysql через php. Примеры SQL-запросов в MariaDB (MySQL). Используйте значение NOT NULL вместо NULL

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

Читайте

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

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

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

}
$mysqli->query("INSERT INTO mytable (name, email) VALUES ("MyName", "[email protected]")");
$mysqli->close();
?>

В данном примере мы подключились к базе данных, проверили успешность подключения, отправили запрос с помощью метода query() , а затем закрыли подключение. Как видите, всё очень просто. Для отправки любых SQL-запросов достаточно всего одного метода - query() , поэтому в данном случае всё безумно просто.

Теперь чуть-чуть усложним задачу. Давайте с Вами разберём запросы, которые возвращают result_set - результат. Самый популярный запрос, возврающающий result_set - это выборка данных из таблицы. В следующем примере мы с Вами сделаем выборку данных, а затем выведем результат:

Для начала немного поясню, что из себя представляет result_set . Result_set - это таблица с результатом. У этой таблицы есть набор записей (строки таблицы). И для вывода всех записей надо перебрать каждую строку таблицы и вывести её. А теперь поясняю пример: после отправки запроса у нас формируется result_set . Затем мы в цикле присваиваем переменной row значение очередной строки, то есть одномерный массив, который возвращает метод fetch_assoc() . Когда все строки закончатся, метод fetch_assoc() вернёт false , и будет произведён выход из цикла. Внутри цикла while мы просто выводим массив с использованием отладочной функции print_r() , хотя, безусловно, можно было бы вывести с использованием foreach , но сейчас это не нужно.

Давайте кратко подведём итог, как нужно работать с result_set :

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

Как Вы заметили, метод fetch_assoc() возвращает всегда следующую запись. То есть сначала 1-ю, потом 2-ю, потом 3-ю и так далее. Если у Вас есть неплохой опыт программирования, то Вы сразу догадаетесь, что это происходит благодаря внутреннему указателю, который Вы, разумеется, можете двигать. Где это используется? Например, это может использоваться, когда Вам необходимо поработать с result_set не 1 , а 2 и более раз. Чтобы заново не формировать тот же запрос, можно просто передвинуть указатель в начало. И тогда можно снова заниматься перебором result_set с помощью метода fetch_assoc().

Для изменения положения указателя существует метод data_seek() , который принимает целое число от 0 до "количества записей - 1 ", соответственно указатель встаёт на соответствующую параметру запись:

$mysqli = @new mysqli("localhost", "Admin", "pass", "mybase");
if (mysqli_connect_errno()) {
echo "Подключение невозможно: ".mysqli_connect_error();
}
$result_set = $mysqli->query("SELECT * FROM mytable");
$result_set->num_rows;
while ($row = $result_set->fetch_assoc()) {
print_r($row);
echo "
";
}
$result_set->data_seek(0);
while ($row = $result_set->fetch_assoc()) {
print_r($row);
echo "
";
}
$result_set->close();
$mysqli->close();
?>

В этом примере мы вывели количество записей в result_set с помощью свойства num_rows . А также познакомились с методом data_seek() . То есть мы перебрали весь результат, затем вернули указатель на 0-ю запись и вновь перебрали результат.

В этой статье мы с Вами повторили подключение к базе данных и закрытие подключения. А также узнали, как отправлять запросы к базе данных через PHP . Узнали, как получать result_set и как работать с ним. Это всё, что Вам необходимо знать для успешной работы с MySQL в PHP .

Как оптимизировать 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 сделает это за вас.

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

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

Анализ (логирование медленных запросов, изучение системы, анализ запросов и проектирование базы данных)
Требования к исполнению (сколько пользователей)
Ограничения технологий (скорость железа, неправильное использование 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 - одна из множества баз данных, поддерживаемых в PHP. Система MySQL распространяется бесплатно и обладает достаточной мощностью для решения реальных задач.

Краткое введение в MySQL

SQL - это аббревиатура от слов Structured Query Language , что означает структурированный язык запросов. Этот язык является стандартным средством для доступа к различным базам данных.

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

Для работы с базами данных удобно пользоваться средством, входящее в комплект Web-разработчика: Denwer phpMyAdmin . Здесь можно создать новую базу данных, создать новую таблицу в выбранной базе данных, заполнить таблицу данными, а также добавлять, удалять и редактировать данные.

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

Каждый столбец после своего типа данных содержит и другие спецификаторы:

Тип	Описание
NOT NULL	Все строки таблицы должны иметь значение в этом атрибуте. Если не указано, поле может быть пустым (NULL)
AUTO_INCREMENT	Специальная возможность MySQL, которую можно задействовать в числовых столбцах. Если при вставке строк в таблицу оставлять такое поле пустым, MySQL автоматически генерирует уникальное значение идентификатора. Это значение будет на единицу больше максимального значения, уже существующего в столбце. В каждой таблице может быть не больше одного такого поля. Столбцы с AUTO_INCREMENT должны быть проиндексированными
PRIMARY KEY	Столбец является первичным ключом для таблицы. Данные в этом столбце должны быть уникальными. MySQL автоматически индексирует этот столбец
UNSIGNED	После целочисленного типа означает, что его значение может быть либо положительным, либо нулевым
COMMENT	Название столбца таблицы

Создание новой базы данных MySQL CREATE DATABASE .

CREATE DATABASE IF NOT EXISTS `base` DEFAULT CHARACTER SET cp1251 COLLATE cp1251_bin

Создание новой таблицы осуществляется при помощи SQL-команды CREATE TABLE . Например, таблица books для книжного магазина будет содержать пять полей: ISBN, автор, название, цена и количество экземпляров:

CREATE TABLE books (ISBN CHAR(13) NOT NULL, PRIMARY KEY (ISBN), author VARCHAR(30), title VARCHAR(60), price FLOAT(4,2), quantity TINYINT UNSIGNED); Чтобы избежать сообщения об ошибке, если таблица уже есть необходимо изменить первую строчку, добавив фразу "IF NOT EXISTS": CREATE TABLE IF NOT EXISTS books ...

Для создания автообновляемого поля с текущей датой типа TIMESTAMP или DATETIME используйте следующую конструкцию:

CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

Добавление данных в эту таблицу осуществляется при помощи SQL-команды INSERT . Например:

INSERT INTO books (ISBN, author, title, price, quantity) VALUES ("5-8459-0184-7", "Зандстра Мэт", "Освой самостоятельно PHP4 за 24 часа", "129", "5");

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

Символ * означает, что необходимы все поля. Например:

SELECT * FROM books;

Для получения доступа только к некоторому полю следует указать его имя в инструкции SELECT . Например:

SELECT author, title, price FROM books;

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

SELECT * FROM books WHERE price % Соответствует любому количеству символов, даже нулевых
_ Соответствует ровно одному символу

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

SELECT * FROM books ORDER BY price;

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

SELECT * FROM books ORDER BY price DESC;

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

SELECT * FROM books ORDER BY 4, 2, 3;

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

UPDATE books SET price = price * 1.1;

Конструкция WHERE ограничит работу UPDATE определенным строками. Например:

UPDATE books SET price = price * 1.05 WHERE price

Для удаления строк из базы данных используется оператор DELETE . Ненужные строки указываются при помощи конструкции WHERE . Например, какие-то книги проданы:

DELETE FROM books WHERE quantity = 0;

Если нужно удалить все записи

TRUNCATE TABLE table_name

Для полного удаления таблицы используется:

DROP TABLE table_name

Связь PHP с базой данных MySQL

Поработав с phpMyAdmin над созданием базы данных, можно приступить к подключению этой базы данных к внешнему Web-интерфейсу.

Чтобы получить доступ к базе данных из Web, используя PHP, надо сделать следующие основные шаги:

Подключение к серверу MySQL.
Выбор базы данных.
Выполнение запроса к базе данных:
- добавление ;
- удаление ;
- изменение ;
- поиск ;
- сортировка .
Получение результата запроса.
Отсоединение от базы данных.

Для подключения к серверу базы данных в PHP есть функция mysql_connect() . Ее аргументы: имя компьютера, имя пользователя и пароль. Эти аргументы можно опустить. По умолчанию имя компьютера = localhost , тогда имя пользователя и пароль не требуется. Если PHP используется в сочетании с сервером Apache, то можно воспользоваться функцией mysql_pconnect() . В этом случае соединение с сервером не исчезает после завершения работы программы или вызова функции mysql_close() . Функции mysql_connect() и mysql_pconnect() возвращают идентификатор подключения, если все прошло успешно. Например:

$link = mysql_pconnect (); if (!$link) die ("Невозможно подключение к MySQL");

После того, как соединение с сервером MySQL установлено, нужно выбрать базу данных. Для этого используется функция mysql_select_db() . Ее аргумент: имя базы данных. Функция возвращает true , если указанная база данных существует и доступ к ней возможен. Например:

$db = "sample"; mysql_select_db ($db) or die ("Невозможно открыть $db");

Для добавления, удаления, изменения и выбора данных нужно сконструировать и выполнить запрос SQL. Для этого в языке PHP существует функция mysql_query() . Ее аргумент: строка с запросом. Функция возвращает идентификатор запроса.

Пример 1

Добавление записи в таблицу

При каждом выполнении примера 1 в таблицу будет добавляться новая запись, содержащая одни и те же данные. Разумеется имеет смысл добавлять в базу данные, введенные пользователем.

В примере 2.1 приведена HTML-форма для добавления новых книг в базу данных.

Пример 2.1

HTML-форма добавления новых книг

Результаты заполнения этой формы передаются в insert_book.php.

Пример 2.2

Программа добавления новых книг (файл insert_book.php) Пожалуйста, вернитесь назад и закончите ввод"); } $isbn = trim ($_POST["isbn"]); $author = trim ($_POST["author"]); $title = trim ($_POST["title"]) ; $isbn = addslashes ($isbn); $author = addslashes ($author); $title = addslashes ($title) ; $db = "sample"; $link = mysql_connect(); if (!$link) die ("Невозможно подключение к MySQL"); mysql_select_db ($db) or die ("Невозможно открыть $db"); $query = "INSERT INTO books VALUES ("" .$isbn."", "".$author."", "".$title."", "" .floatval($_POST["price"])."", "".intval($_POST["quantity"])."")"; $result = mysql_query ($query); if ($result) echo "Книга добавлена в базу данных."; mysql_close ($link); ?>

В примере 2.2 введенные строковые данные обработаны функцией addslashes() . Эта функция добавляет обратные слеши перед одинарными кавычками ("), двойными кавычками ("), обратным слешем (\) и null-байтом. Дело в том, что по требованиям систаксиса запросов баз данных такие символы дожны заключаться в кавычки.

Для определения количества записей в результате запроса используется функция mysql_num_rows() .

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

В примере 3.1 приведена HTML-форма для поиска определенных книг в базе данных.

Пример 3.1

HTML-форма поиска книг

Результаты заполнения этой формы передаются в search_book.php.

Пример 3.2

Пожалуйста, вернитесь назад и закончите ввод"); $searchterm = addslashes ($searchterm); $link = mysql_pconnect (); if (!$link) die ("Невозможно подключение к MySQL"); $db = "sample"; mysql_select_db ($db) or die ("Невозможно открыть $db"); $query = "SELECT * FROM books WHERE " .$_POST["searchtype"]." like "%".$searchterm."%""; $result = mysql_query ($query); $n = mysql_num_rows ($result); for ($i=0; $i<$n; $i++) { $row = mysql_fetch_array($result); echo "

".($i+1). $row["title"]. "

"; } if ($n == 0) echo "Ничего не можем предложить. Извините"; mysql_close ($link); ?>

Альтернативный вариант

Программа поиска книг (файл search_book.php) Пожалуйста, вернитесь назад и закончите ввод"); $searchterm = addslashes ($searchterm); mysql_connect() or die ("Невозможно подключение к MySQL"); mysql_select_db ("sample") or die ("Невозможно открыть БД"); $result = mysql_query ("SELECT * FROM books WHERE ".$_POST["searchtype"]." like "%".$searchterm."%""); $i=1; while($row = mysql_fetch_array($result)) { echo "

".($i++) . $row["title"]."
"; echo "Автор: ".$row["author"]."
"; echo "ISBN: ".$row["ISBN"]."
"; echo "Цена: ".$row["price"]."
"; echo "Количество: ".$row["quantity"]."

"; } if ($i == 1) echo "Ничего не можем предложить. Извините"; mysql_close(); ?>

Итак, как работает архитектура Web-баз данных:

Web-браузер пользователя выдает HTTP-запрос определенной Web-страницы. Например, пользователь, используя HTML-форму, ищет все книги о PHP. Страница обработки формы называется search_book.php.
Web-сервер принимает запрос на search_book.php, извлекает этот файл и передает на обработку механизму PHP.
PHP выполняет соединение с MySQL-сервером и отправляет запрос.
Сервер принимает запрос к базе данных, обрабатывает его и отправляет результат (список книг) обратно механизму PHP.
Механизм PHP завершает выполнение сценария, форматирует результат запроса в HTML. После этого результат в виде HTML возвращается Web-серверу.
Web-сервер пересылает HTML в браузер, и пользователь имеет возможность просмотреть запрошенный список книг.

Использование механизма транзакций

Использование механизма транзакция на примере как передать деньги от одного человека другому

If(mysql_query ("BEGIN") && mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = "Eve"") && mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = "Ida"") && mysql_query ("COMMIT")){ echo "Успешно"; }else{ mysql_query ("ROLLBACK"); echo "Не успешно"; }

SELECT … FOR UPDATE

Если Вы запускаете несколько процессов, которые делают select запрос к одной и той же таблице, то они могут выбрать одну и ту же запись одновременно.

Чтобы избежать вышеупомянутой ситуации необходимо выполнить не просто SELECT запрос, а его расширенную версию, о которой многие и не подозревают: SELECT … FOR UPDATE.

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

Однако не всё так просто. Вам нужно выполнить ещё несколько условий. Во-первых, ваша таблица должна быть создана на основе архитектуры InnoDB. В противном случае блокировка просто не будет срабатывать. Во-вторых, перед выполнением выборки необходимо отключить авто-коммит запроса. Т.е. другими словами автоматическое выполнение запроса. После того как вы укажите UPDATE запрос, необходимо будет ещё раз обратиться к базе и закоммитить изменения с помощью команды COMMIT:

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

СУБД MySQL - что это?

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

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

Как должно быть организовано хранение данных

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

Например, если составляется таблица сотрудников определенной компании, то ее самая простая структура будет иметь следующий вид. За каждым сотрудником закреплен уникальный номер, который, как правило, используется в качестве первичного ключа к таблице. Затем в таблицу заносятся персональные данные сотрудника. Это может быть что угодно: Ф. И. О., номер отдела, за которым он закреплен, телефон, адрес и прочее. Согласно требованиям нормализации (6 нормальных форм баз данных), а также для того, чтобы MySQL-запросы выстраивались структурированно, поля таблицы должны быть атомарными, то есть не иметь перечислений или списков. Поэтому, как правило, в таблице существуют отдельные поля для фамилии, имени и т. д.


	Иванович	Администрац.	Директор
	Петрович	Администрац.	Зам. директора
Григорий	Григорьевич		Начальник

	Сергеевич		Продавец-консульт.

Выше представлен тривиальный пример структуры таблицы базы данных. Однако она ещё не до конца отвечает основным требованиям нормализации. В реальных системах создается дополнительная таблица отделов. Поэтому приведенная таблица вместо слов в колонке «Отдел» должна содержать номера отделов.

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

Для получения данных из таблиц в СУБД используется специальная команда MySQL - запрос Select . Для того чтобы сервер правильно отреагировал на обращение, запрос должен быть корректно сформирован. Структура запроса формируется следующим образом. Любое обращение к серверу БД начинается с ключевого слова select . Именно с негостроятся все вMySQL запросы. Примеры могут иметь различную сложность, но принцип построения очень похож.

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

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

Понятие вложенного запроса

Но главная особенность СУБД, как было указано выше, возможность обрабатывать вложенные запросы MySQL. Как он должен выглядеть? Из названия логически понятно, что , сформированный в определенной иерархии из двух или более запросов. В теории по изучению особенностей СУБД сказано, что MySQL не накладывает ограничений на количество MySQL-запросов, которые могут быть вложены в главный запрос. Однако можно поэкспериментировать на практике и убедиться, что уже после второго десятка вложенных запросов время отклика серьезно увеличится. В любом случае на практике не встречаются задачи, требующие использовать чрезвычайно сложный MySQL-запрос. В запросе может потребоваться максимально до 3-5 вложенных иерархий.

Построение вложенных запросов

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

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

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

Итоги

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

ISBN
Автор
Название
Цена
Количество