Оптимальное использование mysql


Содержание

Советы по оптимальному использованию MySQL

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

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

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

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

Код: Выделить всё Развернуть // Кэш запроса НЕ РАБОТАЕТ
$r = mysql_query(«SELECT username FROM user WHERE signup_date >= CURDATE()»);

// Кэш запроса РАБОТАЕТ!
$today = date(«Y-m-d»);
$r = mysql_query(«SELECT username FROM user WHERE signup_date >= ‘$today'»);

Причина того, что кэш запросов не работает в первом случае, заключается в использовании функции CURDATE(). Такой подход используется для всех недетерминированных функций, например, NOW(), RAND() и т.д. Так как возвращаемый результат функции может измениться, то MySQL решает не размещать данный запрос в кэше. Все что, нужно, чтобы исправить ситуацию — это добавить дополнительную строчку кода PHP перед запросом.

Используйте EXPLAIN для ваших запросов SELECT

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

Результат запроса EXPLAIN показывает, какие индексы используются, как таблица сканируется и сортируется, и так далее.

Возьмем запрос SELECT (предпочтительно, чтобы он был сложным, с JOIN), добавим перед ним ключевое слово EXPLAIN. Вы можете использовать PhpMyAdmin для этого. Такой запрос выведет результат в прекрасную таблицу. Допустим, мы забыли добавить индекс для столбца, который используется для JOIN:

Используйте EXPLAIN для ваших запросов SELECT

После добавления индекса для поля group_id:

После добавления индекса для поля group_id

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

Используйте LIMIT 1, если нужно получить уникальную строку

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

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

Код: Выделить всё Развернуть // Есть ли какой нибудь пользователь из Алабамы?

// Так не нужно делать:
$r = mysql_query(«SELECT * FROM user WHERE state = ‘Alabama'»);
if (mysql_num_rows($r) > 0) <
// .
>

// Вот так будет значительно лучше:
$r = mysql_query(«SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1»);
if (mysql_num_rows($r) > 0) <
// .
>

Индексируйте поля поиска

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

Индексируйте не только основные и уникальные ключи

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова ( “WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать полнотекстовый поиск mysql или построение собственных решений на основе индексирования.

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

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

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

Код: Выделить всё Развернуть // Поиск компании из определенного штата
$r = mysql_query(«SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users. >
// оба столбца для названия штата должны быть индексированы
// и оба должны иметь одинаковый тип и кодировку символов
// или MySQL проведет полное сканирование таблицы

Не используйте ORDER BY RAND()

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

Если вам действительно нужно случайным образом располагать строки в результате вашего запроса, то существует множество лучших способов решить такую задачу. Конечно, это будет реализовано дополнительным кодом, но вы будете спасены от проблемы, которая растет по экспоненциальному закону вместе с ростом объема данных. Дело в том, что MySQL выполняет операцию RAND() (которая занимает время процессора) для каждой отдельной строки в таблице перед тем, как отсортировать ее и выдать вам только одну строку.

Код: Выделить всё Развернуть // Так делать НЕ НУЖНО:
$r = mysql_query(«SELECT username FROM user ORDER BY RAND() LIMIT 1»);

// Вот так будет лучше работать:

$r = mysql_query(«SELECT count(*) FROM user»);
$d = mysql_fetch_row($r);
$rand = mt_rand(0,$d[0] — 1);

$r = mysql_query(«SELECT username FROM user LIMIT $rand, 1»);

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

Старайтесь не использовать SELECT *

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

Хорошей привычкой является указание столбца при выполнении SELECT.

Код: Выделить всё Развернуть // Плохо:
$r = mysql_query(«SELECT * FROM user WHERE user_ >$d = mysql_fetch_assoc($r);
echo «Welcome <$d['username']>«;

// Так лучше:
$r = mysql_query(«SELECT username FROM user WHERE user_ >$d = mysql_fetch_assoc($r);
echo «Welcome <$d['username']>«;

// Разница становится существенной на больших объемах данных

Старайтесь использовать поле id везде

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно — UNSIGNED, так как в этом случае значение не может быть отрицательным.

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

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

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

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

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

Если у вас есть поля, которые содержат только несколько различных видов значений, используйте для них ENUM вместо VARCHAR. Например, может быть столбец с именем “status”, который будет содержать только такие значения как “active”, “inactive”, “pending”, “expired” и так далее.

MySQL может “предложить” способ изменения структуры вашей таблицы. Когда вы создаете поле VARCHAR, то наверняка «предложение» будет содержать рекомендацию сменить тип столбца на ENUM. «Предложения» получаются в ходе выполнения вызова PROCEDURE ANALYSE().

Изучите предложения PROCEDURE ANALYSE()

PROCEDURE ANALYSE() позволяет MySQL анализировать структуру столбцов и действительных данных в вашей таблице и на основании анализа выдавать «предложения». Это действует только если в вашей таблице есть реальные данные, так как их наличие играет существенную роль при принятии решений.

Например, если вы создали поле типа INT для основного ключа, но в таблице не так много записей, то «предложение» может содержать рекомендацию сменить тип поля на MEDIUMINT. Или если вы используете поле типа VARCHAR, то можете получить «предложение» конвертировать его в ENUM, если в нем содержится только несколько значений.

Вы также можете получить рекомендации, если нажмете ссылку “Propose table structure” (Анализ структуры таблицы) в PhpMyAdmin на закладке структуры таблицы.

Анализ структуры таблицы

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

Используйте NOT NULL, если это возможно

Если нет особых причин использовать значение NULL, нужно всегда использовать для столбца свойство NOT NULL.

Спросите себя, есть ли разница между пустой строкой и значением NULL (для полей типа INT: 0 и NULL). Если нет причин использовать оба значения, то нет необходимости иметь поле NULL. (Вы знаете, что Oracle рассматривает NULL и пустую строку как одинаковые величины?)

Столбец NULL требует дополнительного пространства и может быть источником затруднений для выражений сравнений. Просто избегайте использовать его по мере возможности. Хотя, конечно, есть объективные причины для использования значений NULL в некоторых случаях.

Из документации MySQL:

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

Подготовленные выражения

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

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

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

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

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

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

Код: Выделить всё Развернуть // Создаем подготовленное выражение
if ($stmt = $mysqli->prepare(«SELECT username FROM user WHERE state=?»)) <

// Привязываем параметры
$stmt->bind_param(«s», $state);

// Привязываем переменные результата
$stmt->bind_result($username);

// Получаем значения
$stmt->fetch();

printf(«%s is from %s\n», $username, $state);

Небуферированные запросы

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

Отличное объяснение функции mysql_unbuffered_query() из документации PHP:

mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.

Однако существует несколько ограничений. Вы должны либо прочитать все строки либо вызвать mysql_free_result() перед тем, как выполнить следующий запрос. Также нельзя использовать mysql_num_rows() или mysql_data_seek() для набора результата.

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

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

В запросах можно использовать функцию INET_ATON() для конвертации IP адреса в целое, и INET_NTOA() для обратного процесса. Также есть схожие функции PHP: ip2long() и long2ip().

Код: Выделить всё Развернуть $r = «UPDATE users SET ip = INET_ATON(‘<$_SERVER['REMOTE_ADDR']>‘) WHERE user_ >

Таблицы с фиксированной длиной записи (Static) работают быстрее

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как “static” или “с фиксированной длиной записи”. Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как «static» и будет по-другому обрабатываться механизмом MySQL.

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

Такие таблицы также проще кэшировать и проще восстанавливать при сбоях. Но они могут занимать больше места. Например, если конвертировать поле VARCHAR(20) в поле CHAR(20), то всегда будут заняты 20 байт вне зависимости от того, используются они или нет.

Использование техники «Вертикальное разделение» дает возможность отделить столбцы с переменной длиной в отдельную таблицу.

Вертикальное разделение

Вертикальное разделение — это действие по разделению структуры таблицы по вертикали с целью оптимизации.

Пример 1: У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.

Пример 2: У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.

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

Разделяйте большие запросы DELETE или INSERT

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

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

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

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

Код: Выделить всё Развернуть while (1) <
mysql_query(«DELETE FROM logs WHERE log_date

Маленькие столбцы обрабатываются быстрее

Для механизма базы данных диск является наиболее важным узким местом. Стремление сделать все более компактным и маленьким обычно хорошо сказывается в сфере производительности за счет сокращения объема перемещаемых данных.

Документация MySQL содержит список норм хранения данных для всех типов.

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

Нужно только помнить о возможностях роста.

Выбирайте правильный механизм хранения данных

Есть два основных механизма хранения данных для MySQL: MyISAM и InnoDB. Каждый имеет свои достоинства и недостатки.

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

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

Используйте объектно-реляционное отображение

Использование объектно-реляционного отображения (ORM — Object Relational Mapper) дает ряд преимуществ. Все, что можно сделать в ORM , можно сделать вручную, но с большими усилиями и более высокими требованиями к уровню разработчика.

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

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

Для PHP можно использовать ORM Doctrine.

Будьте осторожны с постоянными соединениями

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

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

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

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

Приемы и техники куннилингуса — секс-советы для мужчин
Затащить девушку в постель недостаточно, нужно, чтобы она попросила тебя повторить.

Хелсньюс Восьмерка Восьмерка — классическая техника куннилингуса, способная довести девушку до исступления и не надоесть. Не знаешь, что делать — делай “восьмерку”. Смысл этого приема понятен — движения языком , описывающие восьмерку. Вертикальную или горизонтальную, .

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

Оптимизация MySQL – основы правильной реализации

Дата публикации: 2020-06-13

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

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

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

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

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

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

Как создать сайт самому?

Какие технологии и знания необходимы сегодня, чтобы создавать сайты самостоятельно? Узнайте на интенсиве!

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

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

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

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

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

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

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

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

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

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

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

Оптимизация производительности MySQL

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

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

Скорость работы MySQL

Оптимизация без аналитики бессмысленна. Перед тем как переходить к оптимизации давайте посмотрим как работает база данных сейчас, есть ли запросы, которые выполняются очень медленно. Все настройки вашего сервиса mysql находятся в файле /etc/my.cnf. Чтобы включить отображение медленных запросов добавьте такие строки в my.cnf, в секцию [mysqld]:

Здесь первая строка включает запись лога медленных запросов, вторая указывает, что минимальное время запроса для внесения его в этот лог — две секунды. Еще можно включить в лог запросы, которые не используют индексы:

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

systemctl restart mariadb

tail -f /var/log/mariadb/slow-queries.log

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

SELECT option_name, option_value FROM wp_options WHERE autoload = ‘yes’;

Можно его выполнить отдельно, в консоли mysql:

Здесь тоже измеряется время, и мы видим результат — три секунды. Это очень много. И еще ничего, если такие запросы приходят редко, если ваш сайт постоянно под нагрузкой, то тремя секундами вы не отделаетесь, количество необработанных запросов будет расти, а скорость ответа увеличиваться до нескольких минут. Можно пойти двумя путями — оптимизировать код, убрать сложные запросы, или же нужна оптимизация mysql на сервере.

Оптимизация MySQL

Конфигурация MySQL достаточно сложная, но, к счастью, вам не нужно в нее сильно углубляться. Есть специальный скрипт под названием MySQLTunner, который анализирует работу MySQL и дает советы какие параметры нужно изменить и какие значения для них установить. Скрипт поддерживает большинство версий MariaDB, MySQL и Percona XtraDB. Нам понадобится загрузить три файла с помощью wget:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Первый из них — это сам скрипт, написанный на Perl, второй и третий — база данных простых паролей и уязвимостей. Они позволяют обнаружить проблемы с безопасностью. Дальше можно переходить к тестированию. Я использую сервер с настройками mysql по умолчанию, установленными панелью управления VestaCP.

Буквально за несколько минут скрипт выдаст полную статистику по работе MySQL. Количеству запросов, занимаемому объему памяти и эффективности работы буферов. Вы можете ознакомиться со всем этим, чтобы лучше понять в чем причина проблем. Проблемные места обозначены красными восклицательными знаками. Например, здесь мы видим, что размер буфера движка таблиц InnoDB (InnoDB buffer pool) намного меньше, чем должен быть для оптимальной работы:

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

Все параметры нужно добавлять в /etc/my.cnf. Еще раз замечу, что вы не копируете статью, а смотрите что вам выдала утилита. Начнем с query-cache.

query_cache_size=0
query_cache_type=0
query_cache_limit=1M

Скрипт рекомендует отключить кэш запросов. Query Cache — это кэш вызовов SELECT. Когда базе данных отправляется запрос, она выполняет его и сохраняет сам запрос и результат в этом кэше. И все бы ничего, но при использовании его вместе с InnoDB при любом изменении совпадающих данных кэш будет перестраиваться, что влечет за собой потерю производительности. И чем больше объем кэша, тем больше потери. Кроме того при обновлении кэша могут возникать блокировки запросов. Таким образом, если данные часто пишутся в базу данных — его надежнее отключить.

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

Этот параметр отвечает за количество потоков, которые будут закэшированны. После того, как работа с подключением будет завершена, база данных не разорвет его, а закэширует, если количество кэшированных потоков не превышает ограничение. Утилита рекомендует больше четырех, например, 16.

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

Этот параметр определяет размер буфера InnoDB в оперативной памяти, от этого размера очень сильно зависит скорость выполнения запросов. Значение зависит от размера ваших таблиц и количества данных в них. Если памяти недостаточно, запросы будут обрабатываться дольше. У меня используется стандартный объем 128, а нужно больше 652.

Размер файла лога innodb должен составлять 25% от размера буфера. В случае 800 мегабайт это будет 200М. Но тут есть одна проблема. Чтобы изменить размер лога нужно выполнить несколько действий. Поскольку мы изменили все нужные параметры перейдем к перезагрузке сервера. Для нашего лога нужно остановить сервис:

systemctl stop mariadb

Затем переместите файлы лога в /tmp:

mv /var/lib/mysql/ib_logfile[01] /tmp

И запустите сервис:

systemctl start mariadb

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

systemctl status mariadb

Тестирование результата

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

> USE база_данных;
> SELECT option_name, option_value FROM wpfc_options WHERE autoload = ‘yes’;

Первый раз он выполняется долго, может даже дольше чем обычно, но все последующие разы буквально мгновенно. Результат с более 3 секунд до 0,15. А если брать статистику из slow-log, то от более 12. Если в выводе утилиты для вас были предложены и другие оптимизации, то их тоже стоит применить.

Выводы

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

На завершение лекция про производительность MySQL от Percona:

Советы по использования MySQL

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

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

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

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

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

Причина того, что кэш запросов не работает в первом случае, заключается в использовании функции CURDATE(). Такой подход используется для всех недетерминированных функций, например, NOW(), RAND() и т.д. Так как возвращаемый результат функции может измениться, то MySQL решает не размещать данный запрос в кэше. Все что, нужно, чтобы исправить ситуацию — это добавить дополнительную строчку кода PHP перед запросом.

2. Используйте EXPLAIN для ваших запросов SELECT

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

Результат запроса EXPLAIN показывает, какие индексы используются, как таблица сканируется и сортируется, и так далее.

Возьмем запрос SELECT (предпочтительно, чтобы он был сложным, с JOIN), добавим перед ним ключевое слово EXPLAIN. Вы можете использовать PhpMyAdmin для этого. Такой запрос выведет результат в прекрасную таблицу. Допустим, мы забыли добавить индекс для столбца, который используется для JOIN:

После добавления индекса для поля group_id:

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

3. Используйте LIMIT 1, если нужно получить уникальную строку

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

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

4. Индексируйте поля поиска

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

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова ( “WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать полнотекстовый поиск mysql или построение собственных решений на основе индексирования.

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

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

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

6. Не используйте ORDER BY RAND()

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

Если вам действительно нужно случайным образом располагать строки в результате вашего запроса, то существует множество лучших способов решить такую задачу. Конечно, это будет реализовано дополнительным кодом, но вы будете спасены от проблемы, которая растет по экспоненциальному закону вместе с ростом объема данных. Дело в том, что MySQL выполняет операцию RAND() (которая занимает время процессора) для каждой отдельной строки в таблице перед тем, как отсортировать ее и выдать вам только одну строку.

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

7. Старайтесь не использовать SELECT *

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

Хорошей привычкой является указание столбца при выполнении SELECT.

8. Старайтесь использовать поле id везде

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно — UNSIGNED, так как в этом случае значение не может быть отрицательным.

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

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

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

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

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

Если у вас есть поля, которые содержат только несколько различных видов значений, используйте для них ENUM вместо VARCHAR. Например, может быть столбец с именем “status”, который будет содержать только такие значения как “active”, “inactive”, “pending”, “expired” и так далее.

MySQL может “предложить” способ изменения структуры вашей таблицы. Когда вы создаете поле VARCHAR, то наверняка «предложение» будет содержать рекомендацию сменить тип столбца на ENUM. «Предложения» получаются в ходе выполнения вызова PROCEDURE ANALYSE().

10. Изучите предложения PROCEDURE ANALYSE()

PROCEDURE ANALYSE() позволяет MySQL анализировать структуру столбцов и действительных данных в вашей таблице и на основании анализа выдавать «предложения». Это действует только если в вашей таблице есть реальные данные, так как их наличие играет существенную роль при принятии решений.

Например, если вы создали поле типа INT для основного ключа, но в таблице не так много записей, то «предложение» может содержать рекомендацию сменить тип поля на MEDIUMINT. Или если вы используете поле типа VARCHAR, то можете получить «предложение» конвертировать его в ENUM, если в нем содержится только несколько значений.

Вы также можете получить рекомендации, если нажмете ссылку “Propose table structure” (Анализ структуры таблицы) в PhpMyAdmin на закладке структуры таблицы.

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

11. Используйте NOT NULL, если это возможно

Если нет особых причин использовать значение NULL, нужно всегда использовать для столбца свойство NOT NULL.

Спросите себя, есть ли разница между пустой строкой и значением NULL (для полей типа INT: 0 и NULL). Если нет причин использовать оба значения, то нет необходимости иметь поле NULL. (Вы знаете, что Oracle рассматривает NULL и пустую строку как одинаковые величины?)

Столбец NULL требует дополнительного пространства и может быть источником затруднений для выражений сравнений. Просто избегайте использовать его по мере возможности. Хотя, конечно, есть объективные причины для использования значений NULL в некоторых случаях.

Из документации MySQL:

“Столбец NULL требует дополнительного пространства в строке для записи о возможном значении NULL. Для таблиц MyISAM каждый столбец NULL использует дополнительный бит, округление проводится до ближайшего байта.”

12. Подготовленные выражения

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

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

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

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

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

Для использования подготовленных выражений в PHP можно использовать расширение mysqli или PDO.

13. Небуферированные запросы

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

Отличное объяснение функции mysql_unbuffered_query() из документации PHP:

“mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.”

Однако существует несколько ограничений. Вы должны либо прочитать все строки либо вызвать mysql_free_result() перед тем, как выполнить следующий запрос. Также нельзя использовать mysql_num_rows() или mysql_data_seek() для набора результата.

14. Храните IP адрес как UNSIGNED INT

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

В запросах можно использовать функцию INET_ATON() для конвертации IP адреса в целое, и INET_NTOA() для обратного процесса. Также есть схожие функции PHP: ip2long() и long2ip().

15. Таблицы с фиксированной длиной записи (Static) работают быстрее

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как “static” или “с фиксированной длиной записи”. Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как «static» и будет по-другому обрабатываться механизмом MySQL.

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

Такие таблицы также проще кэшировать и проще восстанавливать при сбоях. Но они могут занимать больше места. Например, если конвертировать поле VARCHAR(20) в поле CHAR(20), то всегда будут заняты 20 байт вне зависимости от того, используются они или нет.

Использование техники «Вертикальное разделение» дает возможность отделить столбцы с переменной длиной в отдельную таблицу.

16. Вертикальное разделение

Вертикальное разделение — это действие по разделению структуры таблицы по вертикали с целью оптимизации.

Пример 1: У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.

Пример 2: У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.


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

17. Разделяйте большие запросы DELETE или INSERT

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

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

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

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

18. Маленькие столбцы обрабатываются быстрее

Для механизма базы данных диск является наиболее важным узким местом. Стремление сделать все более компактным и маленьким обычно хорошо сказывается в сфере производительности за счет сокращения объема перемещаемых данных.

Документация MySQL содержит список норм хранения данных для всех типов.

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

Нужно только помнить о возможностях роста.

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

Есть два основных механизма хранения данных для MySQL: MyISAM и InnoDB. Каждый имеет свои достоинства и недостатки.

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

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

20. Используйте объектно-реляционное отображение

Использование объектно-реляционного отображения (ORM — Object Relational Mapper) дает ряд преимуществ. Все, что можно сделать в ORM , можно сделать вручную, но с большими усилиями и более высокими требованиями к уровню разработчика.

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

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

Для PHP можно использовать ORM Doctrine.

21. Будьте осторожны с постоянными соединениями

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

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

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

22. Помните про нюансы

Поиск с учетом регистра символов:

Сравнения чисел и строк работает по-разному, например следующие 2 запроса абсолютно разные:

SELECT page_ — правильный способ, если имя не соответствует строке с знаком ноль — не выводит строки

Неправильное нахождение данных в связи с неправильным запросом (см. на кавычки)

SELECT file_id FROM pages_files WHERE file_id IN (‘2997,12345’) — возвращает одну строку — 2997

Использование mysql. Оптимальное использование MySQL. Выбор базы данных

Введение

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

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

Такой подход, конечно, дает желаемый результат — интерфейсы веб-сайта пользователя в итоге оказываются интегрированными с базой данных. Однако не всегда пользователи задумываются о том, насколько оптимально работает их база, как можно оптимизировать происходящие при работе с MySQL процессы и каково будет функционирование виртуального сервера при увеличившейся нагрузке, «наплывах» пользователей в результате, например, «раскрутки» сайта.

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

Какие данные нужно хранить в MySQL

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

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

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

В ситуациях, когда реально требуется получить только определенную порцию данных из MySQL, можно использовать ключ LIMIT для функции SELECT . Это полезно, когда, например, нужно показать результаты поиска чего-либо в базе данных. Допустим, в базе есть список товаров, которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в нужной категории несколько негуманно по отношению к пользователю — каналы связи с интернет не у всех быстрые и выдача лишних ста килобайт информации зачастую заставляет пользователяй провести не одну минуту в ожидании результатов загрузки страницы. В таких ситуациях информацию выдают порциями по, допустим, 10 позиций. Неправильно делать выборку из базы всей информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать запрос вида

select good, price from books limit 20,10

В результате, MySQL «отдаст» Вам 10 записей из базы начиная с 20-й позиции. Выдав результат пользователю, сделайте ссылки «Следующие 10 товаров», в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к MySQL.

Также следует помнить, что при составлении запросов к базе данных (SQL queries) следует запрашивать только ту информацию, которая Вам реально нужна. Например, если в базе 10 полей, а в данный момент реально требуется получить только два из них, вместо запроса

select * from table_name

используйте конструкцию вида

select field1, field2 from table_name

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

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

select title from books where author=» Иванов»

Также есть ключ LIKE, который позволяет искать поля, значения которых «похожи» на заданный шаблон:

select title from books where author like » Иванов%»

В данном случае MySQL выдаст названия книг, значения поля author у которых начинаются с » Иванов»

Ресурсоемкие операции

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

Индексы

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

В каких ситуациях создание индекса целесообразно:

  1. Быстрый поиск строк при использовании конструкции WHERE
  2. Поиск строк из других таблиц при выполнении объединения
  3. Поиск значения MIN() или MAX() для проиндексированного поля
  4. Сортировка или группировка таблицы в случае, если используется проиндексированное поле
  5. В некоторых случаях полностью теряется необходимость обращаться к файлу данных. Если все используемые поля для некоторой таблицы цифровые и формируют левосторонний индекс для некоторого ключа, то значения могут быть возвращены полностью из индексного дерева с намного большей скоростью.
  6. Если выполняются запросы вида
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
    и существует смешанный индекс для полей col1 и col2, то данные будут возвращены напрямую. Если же созданы отдельные индексы для col1 и для col2, то оптимизатор попробует найти наиболее ограниченный индекс путем определения того, какой из индексов может найти меньше строк, и будет использовать этот индекс для получения данных.
    Если у таблицы есть смешанный индекс, то будет использоваться любое левостороннее совпадение с существующим индексом. Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1, col2, col3).

Подробнее об индексировании

Поддержка соединения

Как Вы наверняка знаете, для работы с MySQL-сервером необходимо предварительно установить с ним соединение, предъявив логин и пароль. Процесс установки соединения может продолжаться гораздо большее время, нежели непосредственная обработка запроса к базе после установки соединения. Следуя логике, надо избегать лишних соединений к базе, не отсоединяясь от нее там, где это можно сделать, если в дальнейшем планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт установил соединение к базе, сделал выборку данных для анализа, не нужно закрывать соединение к базе, если в процессе работы этого же скрипта Вы планируете результаты анализа поместить в базу.

Также можно поддерживать так называемое persistent (постоянное) соединение к базе, но это возможно в полном объеме при использовании более сложных сред программирования, чем php или perl в обычном CGI-режиме, когда интерпретатор соответствующего языка разово запускается веб-сервером для выполнения пришедшего запроса.

Что такое MySQL

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

MySQL поддерживает SQL (структурированный язык запросов) и может применяться в качестве SQL-сервера. Это означает, что общаться с сервером можно на языке SQL: клиент посылает серверу запрос, тот его обрабатывает и отдает клиенту только те данные, которые были получены в результате этого запроса. Тем самым клиенту не требуется выкачивать данные и производить вычисления, как, например, в Microsoft Access.

Кроме того, MySQL — это ПО с открытым кодом, т.е. его можно свободно изучать и изменять. Пакет распространяется на условиях GPL (General Public License), его можно бесплатно загрузить из Интернета (http://www.mysql.com) для некоммерческого применения.

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

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

Недостатки

Вот краткий перечень основных функций, которых не хватает в MySQL.

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

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

Заметим, что при помощи команды LOCK TABLES в MySQL можно эмулировать транзакцию. Эта команда блокирует таблицу на время выполнения запросов, и тем самым обеспечивается целостность данных, но откат все равно нельзя сделать.

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

Хранимые процедуры — это несколько SQL-команд, которые хранятся в базе данных под неким именем и в совокупности выполняют некую функцию. При помощи хранимых процедур можно расширить синтаксис SQL так, что он будет похож на обычный язык программирования (например, Oracle PL/SQL). В нашем примере с переводом денег два SQL-запроса можно было бы сохранить под одним именем, а потом вызвать эту процедуру, передав ей в качестве параметров два номера счета и сумму денег. Тогда оба запроса выполнялись бы в одной транзакции.

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

SELECT auto FROM autopark WHERE massa > !Больше чего? Я понятия не имею, каково среднее значение!

Для этого среднее значение в поле massa нужно вычислить:

SELECT AVG(massa) FROM autopark

Если поддерживаются вложенные запросы, то эти два запроса можно вложить друг в друга:

SELECT auto FROM autopark WHERE massa >(SELECT AVG(massa) FROM autopark)

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

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

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

Преимущества

А теперь перечислим преимущества MySQL.

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

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

Лицензия. Раньше лицензирование MySQL было немного запутанным; сейчас эта программа для некоммерческих целей распространяется бесплатно.

Открытость кода. Благодаря этому вы сможете сами добавлять в пакет нужные функции, расширяя его функциональность так, как вам требуется. Кстати, за отдельную плату для вас это могут сделать и сами авторы MySQL. Чтобы заказать расширение MySQL у создателей пакета, просто зайдите на сайт http://www.mysql.com и заполните соответствующую форму.

Надежность. Создатели MySQL потрудились на славу: насколько мне известно, этот пакет довольно стабилен и его трудно вывести из строя. Я не отслеживаю специально сводки результатов хакерских атак на MySQL, но мне ни разу не попадалось на глаза (в отличие от тех же Web-серверов) сообщение о том, что MySQL был поврежден в результате чьего-то злого умысла.

Ресурсы. Это может зависеть от разных факторов, но в любом случае суперкомпьютер вам не потребуется.

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

Переносимость. В настоящее время существуют версии программы для большинства распространенных компьютерных платформ. Это говорит о том, что вам не навязывают определенную операционную систему. Вы сами можете выбрать, с чем работать, например с Linux или Windows, но даже в случае замены ОС вы не потеряете свои данные и вам даже не понадобятся дополнительные инструменты для их переноса.

Не знаю, к недостаткам или преимуществам отнести тот факт, что у MySQL нет графического интерфейса пользователя (GUI). Мне, например, удобнее написать SQL-запрос вручную (кстати, результаты его выполнения можно перенаправить в файл), чем пользоваться мастером запросов, как в Microsoft SQL Server.

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

  • Winmysqladmin — входит в Windows-дистрибутив MySQL, имеет стандартный графический интерфейс и позволяет администрировать MySQL;
  • MySqlManager — входит в Windows-дистрибутив MySQL, несет в себе клиентские функции (но ничего серьезного с ее помощью автору сделать так и не удалось);
  • MySQL Administrator for Windows — более «продвинутая» утилита сторонних разработчиков. Позволяет зарегистрировать и подключаться одновременно к нескольким MySQL-серверам, создавать, удалять и изменять структуру баз данных и таблиц, создавать в таблицах ключи, писать SQL-запросы и сохранять их в файле:
  • XMySQL — клиент MySQL для X Window-подобных систем. Предоставляет полный доступ к таблицам, допускает групповые вставки и удаления, имеет конструктор запросов и функции администрирования пакета. Программу можно найти по адресу http://web.wt.net/

Более полный список всевозможных утилит для MySQL (а он очень велик) есть по адресу http://www.mysql.com/downloads/ . Там вы найдете массу интересных и полезных вещей: экспорт данных из MySQL в Microsoft Access и обратно, драйверы ODBC и т.д.

Несмотря на отсутствие графического интерфейса, в пакет MySQL входят довольно мощные средства администрирования с интерфейсом командной строки. Ниже приведен их список с краткими описаниями утилит.

  • MySQLAdmin — главный инструмент администрирования MySQL. С его помощью вы можете создавать, уничтожать, изменять базы данных и полностью контролировать свой сервер.
  • MySQLDump — утилита резервирования данных.
  • MySQLAccess — позволяет изменять таблицы прав доступа и выводить их содержание в удобном для чтения виде.
  • MySQLBug — в случае ошибки в MySQL эта утилита создает для разработчиков программы отчет об ошибках, отсылая его также в почтовый список рассылки MySQL, чтобы специалисты могли помочь решить вашу проблему.
  • MySQLImport — импортирует данные из файла с разделителями в базу данных.
  • MySQLShow — показывает структуру баз данных и таблиц, из которых они состоят.

Хочу заострить внимание читателя вот на чем: сейчас появились программы, работающие через CGI-интерфейс, которые предоставляют практически полный пакет услуг администрирования баз данных. Эти программы лежат на Web-серверах и представляют собой обычные CGI-скрипты. Очень часто эти скрипты размещаются в доступных для общего пользования каталогах. Опасность заключается в том, что с помощью поисковых машин любой может найти такие программы по имени файла, а потом сделать с вашей базой данных все, что его душе угодно. Эту проблему легко обойти, если размещать эти скрипты в закрытых паролем каталогах сервера. Но лучшее решение — вообще отказаться от использования таких программ на сервере.

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

SELECT title, LENGTH(title) FROM table

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

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

SELECT event FROM table WHERE time>(Unix_TIMESTAMP()-(60*60*24))

Здесь функция Unix_TIMESTAMP() вычисляет текущее время, от которого мы отнимаем одни сутки.

Применение

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

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

Возможность работы нескольких пользователей. Это очевидное требование следует дополнить тем, что интенсивность использования базы данных в данном случае будет значительно выше, чем на Web-сервере. В самом деле, для сайта 20 посетителей одновременно считается большим успехом, а в случае корпоративной базы таким показателем может похвастаться даже небольшая фирма. Особое внимание надо обратить на то обстоятельство, что корпоративная база данных использует более сложные пользовательские интерфейсы, чем странички на сайте; иными словами, более интенсивно посылает запросы на сервер. В техническом плане это означает, что нужна блокировка на уровне изменяемой записи. Здесь MySQL показывает себя не лучшим образом: блокировка в нем осуществляется на уровне таблиц. Это означает, в частности, что если кто-то вводит заказ, то всем запросам (анализирующим статистику, выбирающим записи для отчета и т.п.) придется ждать, пока ввод заказа закончится. В случае корпоративной базы данных это сводит на нет даже такое преимущество MySQL, как быстродействие.

Контроль целостности данных на уровне SQL-сервера. Корпоративная база данных отличается сложной схемой данных, и поддерживать целостность данных средствами клиентской программы очень трудно: одна реляция может соединять пять-семь таблиц, а число таблиц может достигать 30—40. И в этом случае существенной становится отсутствующая у MySQL возможность каскадного обновления и удаления записей в связанных таблицах.

Кроме того, мы уже упоминали, что корпоративная база данных использует более сложный интерфейс, а это обстоятельство порождает еще два требования: поддержку всех стандартных инструкций SQL (и полезных расширений), а также использование сохраненных процедур (stored procedure) и триггеров. Увы, и здесь MySQL нас не радует.

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

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

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

Ещё один момент: что такое реляционная база данных? Реляционная — значит основанная на таблицах. Знаменитый редактор электронных таблиц Excel от Microsoft фактически является редактором реляционных баз данных.

Подключение к серверу MySQL

Для подключения к серверу MySQL в PHP используется функция mysqli_connect() . Данная функция получает три аргумента: имя сервера, имя пользователя и пароль.

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

Код подключения к серверу MySQL:

$link = mysqli_connect(«localhost», «root», «»);

В данном случае я работаю на локальном компьютере на Denwere, поэтому имя хоста localhost, имя пользователя root, а пароля нет.

Соединение также нужно закрыть, после завершения работы с MySQL. Для закрытия соединения используется функция mysqli_close() . Расширяем пример:

$link = mysqli_connect(«localhost», «root», «»); if (!$link) die(«Error»); mysqli_close($link);

Тут мы проверели идентификатор подключения на истинность, если с нашим подключением что-то не так, то и программа выполняться не будет, функция die() остановит её выполнение и выведет в браузер сообщение об ошибке.

Ошибки подключения

Для проверки подключения используются следующие функции:

  • mysqli_connect_errno() — возвращает код ошибки последней попытки соединения. При отсутствие ошибок возвращает ноль.
  • mysqli_connect_error() — возвращает описание последней ошибки подключения к серверу MySQL.

define («HOST», «localhost»); define («DB_USER», «root»); define («DB_PASSWORD», «»); define («DB», «tester»); $link = mysqli_connect(HOST, DB_USER, DB_PASSWORD, DB); /* проверка соединения */ if (mysqli_connect_errno()) < printf("Не удалось подключиться: %s\n", mysqli_connect_error()); exit(); >else

Функция mysqli_get_host_info() возвращает строку, содержащую тип используемого соединения.

Также обратите внимание, при помощи команды define я все параметры подключения сохранил в константах. Когда вы будете писать большие проекты, и подключатся к серверу MySQL будут много файлов, то удобно хранить параметры соединения в отдельном файле и вставлять его при помощи функции include или require .

Выбор базы данных

На сервере MySQL может быть несколько баз данных. Первым делом нам нужно выбрать для работы нужную нам базу. В PHP для этого в функции mysqli_connect() есть ещё один параметр — имя базы данных.

Я создал у себя на компьютере через phpMyAdmin с именем tester. Подключаемся к ней:

$link = mysqli_connect(«localhost», «root», «», «tester»); if (!$link) die(«Error»); mysql_close($link);

Итак, мы выбрали для работы базу данных. Но как нам известно, реляционная база данных состоит из таблиц, а в нашей базе данных таблиц пока что нет. База данных создаётся пустая, без таблиц. Таблицы в неё нужно добавить отдельно. Вот давайте добавим в неё таблицу средствами PHP.

Создаём таблицу

В названии баз данных MySQL часть SQL обозначает Structured Query Language, что переводится как структурированный язык запросов. На языке SQL мы будем писать запросы и из программы PHP посылать их серверу MySQL.

Чтобы создать таблицу нам просто нужно указать команду CREATE TABLE . Давайте создадим таблицу с именем users в столбцах которой будут храниться логины (столбец login) и пароли (столбец password) пользователей.

$query = «CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))»;

В этом коде мы присвоили переменной $query строку текста, которая представляет собой запрос SQL. Мы создаём таблицу с именем users, которая содержит два столбца login и password, у обоих тип данных VARCHAR(20). О типах данных мы поговорим позже, сейчас только отмечу, что VARCHAR(20) — это строка максимальной длины 20 символов.

Чтобы отправить наш запрос на сервер MySQL мы используем PHP функцию mysqli_query() . Эта функция возвращает положительное число, если операция прошла успешно и false, если произошла ошибка (синтаксис запроса ошибочный или у программы нет прав на выполнение запроса).

$link = mysqli_connect(«localhost», «root», «», «tester»); if (!$link) die(«Error»); $query = «CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))»; mysqli_query($query); mysqli_close($link);

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

У этого скрипта есть один недостаток — он ничего не выводит в браузер. Давайте добавим сообщение:

$link = mysqli_connect(«localhost», «root», «», «tester»); if (!$link) die(«Error»); $query = «CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))»; if (mysqli_query($query)) echo «Таблица создана.»; else echo «Таблица не создана.»; mysqli_close($link);

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

Обработка ошибок

При отладке программы нам может понадобиться точная информация о ошибке. Когда в MySQL происходит ошибка, то сервер базы данных устанавливает номер ошибки и строку с её описанием. Для доступа к этим данным в PHP есть специальные функции.

  • mysqli_errno() — возвращает номер ошибки.
  • mysqli_error() — возвращает строку с описанием ошибки.

Теперь давайте добавим функцию mysql_error() в наш скрипт:

$link = mysql_connect(«localhost», «root», «», «tester»); if (!$link) die(«Error»); $query = «CREATE TABLE users(login VARCHAR(20), password VARCHAR(20))»; if (mysqli_query($query)) echo «Таблица создана.»; else echo «Таблица не создана: «.mysqli_error(); mysqli_close($link);

Теперь наш скрипт вернёт в браузер строку: «Таблица не создана: Table «users» already exists».

Удаление таблицы

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

Для удаления таблицы используется команда DROP TABLE , за которой следует имя таблицы.

$link = mysqli_connect(«localhost», «root», «», «tester»); if (!$link) die(«Error»); $query = «DROP TABLE users»; if (!mysqli_query($query)) echo «Ошибка при удалении таблицы: «.mysqli_error(); else echo «Таблица удалена.»; mysqli_close($link);

Итоги

Итак, мы освоили основы MySQL. Что мы научились делать:

  • Подключаться к базе данных MySQL при помощи функции mysqli_connect() .
  • Закрывать соединение с сервером MySQL при помощи функции mysqli_close() .
  • Отправлять SQL запросы серверу MySQL при помощи функции mysqli_query() .
  • Мы узнали SQL запрос создания таблицы: create table.
  • Мы узнали SQL запрос удаления таблицы: drop table.
  • Мы узнали как обрабатывать ошибки при помощи функций mysqli_errno() и mysqli_error() .

Потом мы подробно рассмотрим типы данных MySQL.

Читаем следующий урок:

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

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

Основными понятиями, с которыми следует ознакомиться на данном этапе, являются:

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

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

Доступ к MySQL из командной строки

Работать с MySQL можно тремя основными способами: используя командную строку, используя веб-интерфейс наподобие phpMyAdmin и используя такой язык программирования, как PHP. Третий из перечисленных способов будет рассмотрен в последующих статьях, а сейчас давайте рассмотрим первые два способа.

Если у вас в соответствии с инструкциями, изложенными в статье , установлен WAMP-сервер OpenServer, то доступ к исполняемой программе MySQL можно получить из следующего каталога:

курса MySQL с нуля .

Вместо MySQL-5.7-x64 нужно подставить версию, указанную в настройках OpenServer во вкладке «Модули».

Нужно открыть программу «Командная строка» и перейти в этот каталог. Делается это при помощи команды cd ПУТЬ_К_НУЖНОЙ_ПАПКЕ:

Код доступен только после покупки курса MySQL с нуля .

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

Код доступен только после покупки курса MySQL с нуля .

В результате запустится MySQL-клиент. Он подключён к MySQL-серверу, который был запущен при старте OpenServer-a.

Если это не приведет к желаемому результату и будет выдано сообщение об ошибке подключения к серверу MySQL «Can»t connect to MySQL server on «localhost»», убедитесь, что OpenServer запущен и в модулях указан MySQL.

Параметр -u расшифровывается как user. То есть это флажок для указания пользователя, под которым нужно подключиться к серверу. root — это самый главный пользователь в MySQL. Он создаётся при установке сервера и по умолчанию у него нет пароля.

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

Код доступен только после покупки курса MySQL с нуля .

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

Работа с MySQL через phpMyAdmin

Для управления базами данных и таблицами будет намного проще и быстрее использовать программу phpMyAdmin. Перед тем как вводить в адресную строку браузера следующую строку, нужно убедиться в том, что программа OpenServer уже запущена и, значит, база данных MySQL готова к работе:

Тут нас встретит вот такое красивое окошечко для входа в систему.

Также как и в случае с консольным приложением указываем пользователя root и оставляем пустым пароль. Жмём «войти».

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

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

В открывшемся окне введите всё тот же запрос:

Код доступен только после покупки курса MySQL с нуля .

Нажимаем кнопку «вперёд» и видим тот же результат, что и в случае с консольным приложением.

Основные понятия языка SQL

По словам Эндрю Тейлора (Andrew Taylor), разработавшего язык SQL, название этого языка не является сокращением от Structured Query Language (или от чего-то подобного), хотя многие считают, что так оно и есть. Язык SQL лежит в основе более строгого и более общего метода хранения данных по сравнению с предыдущим стандартом организации баз данных в стиле DBM, который основан на использовании плоских файлов.

Язык SQL определен стандартами ANSI (American National Standards Institute) и ECMA (European Computer Manufacturer»s Association); обе эти организации по стандартизации являются международно признанными. Но следует учитывать, что в общих рекомендациях стандартов SQL наблюдаются заметные различия, касающиеся программных продуктов коммерческих компаний, с одной стороны, и организаций, занимающихся разработкой баз данных с открытым исходным кодом, с другой. Например, за последние несколько лет наблюдалось стремительное развитие так называемых объектно-реляционных баз данных, а также программных продуктов SQL, специально предназначенных для рынка веб. Перечень баз данных, которые могут применяться в сочетании с системой PHP, чрезвычайно велик, поэтому при выборе наиболее подходящей базы данных необходимо руководствоваться определенными принципами.

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

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

По существу, база данных SQL имеет очень простую логическую структуру. Каждая конкретная инсталляция программного обеспечения SQL обычно может состоять из нескольких баз данных. Например, одна БД может применяться для хранения данных о заказчиках, а другая — содержать данные о товарах. (Возникает определенная сложность, связанная с тем, что сам сервер SQL и коллекции поддерживаемых этим сервером таблиц обычно принято обозначать общим термином база данных.) Каждая база данных содержит несколько таблиц, каждая таблица состоит из тщательно определенных столбцов, а каждая позиция в таблице может рассматриваться как внесенная в таблицу запись, или строка.

Любой сервер SQL поддерживает четыре так называемых оператора манипулирования данными, и в целом эти операторы лежат в основе подавляющего большинства операций, выполняемых с реляционной базой данных. Этими четырьмя основными операторами базы данных являются SELECT, INSERT, UPDATE и DELETE. Операторы SQL, называемые также командами — очень удобны и позволяют выполнять практически все необходимые действия в базе данных.

Важной особенностью указанных четырех операторов SQL является то, что они позволяют манипулировать только значениями, хранящимися в базе данных, но не воздействуют на структуру самой базы данных. Иными словами, команды на основе этих операторов могут использоваться, например, для ввода данных, а не для создания базы данных; с помощью таких команд можно удалить из базы данных любой фрагмент данных, но сама «оболочка» останется нетронутой, поэтому, в частности, нельзя присвоить другой базе данных, работающей под управлением того же сервера, имя существующей базы данных. Для того чтобы добавить или удалить столбцы, уничтожить целую базу данных, не оставив и следа, или создать новую базу данных, необходимо применить другие команды, такие как DROP, ALTER и CREATE.

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

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

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

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

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

// Кэш запроса НЕ РАБОТАЕТ $r = mysql_query(«SELECT username FROM user WHERE signup_date >= CURDATE()»); // Кэш запроса РАБОТАЕТ! $today = date(«Y-m-d»); $r = mysql_query(«SELECT username FROM user WHERE signup_date >= «$today»»);

Причина того, что кэш запросов не работает в первом случае, заключается в использовании функции CURDATE() . Такой подход используется для всех недетерминированных функций, например, NOW(), RAND() и т.д. Так как возвращаемый результат функции может измениться, то MySQL решает не размещать данный запрос в кэше. Все что, нужно, чтобы исправить ситуацию — это добавить дополнительную строчку кода PHP перед запросом.

2. Используйте EXPLAIN для ваших запросов SELECT

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

Результат запроса EXPLAIN показывает, какие индексы используются, как таблица сканируется и сортируется, и так далее.

Возьмем запрос SELECT (предпочтительно, чтобы он был сложным, с JOIN), добавим перед ним ключевое слово EXPLAIN. Вы можете использовать PhpMyAdmin для этого. Такой запрос выведет результат в прекрасную таблицу. Допустим, мы забыли добавить индекс для столбца, который используется для JOIN:

После добавления индекса для поля group_id:

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

3. Используйте LIMIT 1, если нужно получить уникальную строку

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

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

// Есть ли какой нибудь пользователь из Алабамы? // Так не нужно делать: $r = mysql_query(«SELECT * FROM user WHERE state = «Alabama»»); if (mysql_num_rows($r) > 0) < // . >// Вот так будет значительно лучше: $r = mysql_query(«SELECT 1 FROM user WHERE state = «Alabama» LIMIT 1″); if (mysql_num_rows($r) > 0) < // . >

4. Индексируйте поля поиска

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

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова (“WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать полнотекстовый поиск mysql или построение собственных решений на основе индексирования.

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

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

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

// Поиск компании из определенного штата $r = mysql_query(«SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users. >

6. Не используйте ORDER BY RAND()

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

Если вам действительно нужно случайным образом располагать строки в результате вашего запроса, то существует множество лучших способов решить такую задачу. Конечно, это будет реализовано дополнительным кодом, но вы будете спасены от проблемы, которая растет по экспоненциальному закону вместе с ростом объема данных. Дело в том, что MySQL выполняет операцию RAND() (которая занимает время процессора) для каждой отдельной строки в таблице перед тем, как отсортировать ее и выдать вам только одну строку.

// Так делать НЕ НУЖНО: $r = mysql_query(«SELECT username FROM user ORDER BY RAND() LIMIT 1»); // Вот так будет лучше работать: $r = mysql_query(«SELECT count(*) FROM user»); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d — 1); $r = mysql_query(«SELECT username FROM user LIMIT $rand, 1»);

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

7. Старайтесь не использовать SELECT *

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

Хорошей привычкой является указание столбца при выполнении SELECT.

// Плохо: $r = mysql_query(«SELECT * FROM user WHERE user_ ; // Разница становится существенной на больших объемах данных

8. Старайтесь использовать поле id везде

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно — UNSIGNED, так как в этом случае значение не может быть отрицательным.

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

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

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

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

13. Небуферированные запросы

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

Отличное объяснение функции mysql_unbuffered_query() из документации PHP:

“mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.”

Однако существует несколько ограничений. Вы должны либо прочитать все строки либо вызвать mysql_free_result() перед тем, как выполнить следующий запрос. Также нельзя использовать mysql_num_rows() или mysql_data_seek() для набора результата.

14. Храните IP адрес как UNSIGNED INT

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

$r = «UPDATE users SET ip = INET_ATON(«<$_SERVER["REMOTE_ADDR"]>«) WHERE user_ >

15. Таблицы с фиксированной длиной записи (Static) работают быстрее

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как “static” или “с фиксированной длиной записи” . Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как «static» и будет по-другому обрабатываться механизмом MySQL.

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

Такие таблицы также проще кэшировать и проще восстанавливать при сбоях. Но они могут занимать больше места. Например, если конвертировать поле VARCHAR(20) в поле CHAR(20), то всегда будут заняты 20 байт вне зависимости от того, используются они или нет.

Использование техники «Вертикальное разделение» дает возможность отделить столбцы с переменной длиной в отдельную таблицу.

16. Вертикальное разделение

Вертикальное разделение — это действие по разделению структуры таблицы по вертикали с целью оптимизации.

Пример 1 : У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.


Пример 2 : У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.

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

17. Разделяйте большие запросы DELETE или INSERT

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

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

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

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

Для механизма базы данных диск является наиболее важным узким местом. Стремление сделать все более компактным и маленьким обычно хорошо сказывается в сфере производительности за счет сокращения объема перемещаемых данных.

Документация MySQL содержит список норм хранения данных для всех типов.

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

Нужно только помнить о возможностях роста.

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

Есть два основных механизма хранения данных для MySQL: MyISAM и InnoDB. Каждый имеет свои достоинства и недостатки.

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

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

20. Используйте объектно-реляционное отображение

Использование объектно-реляционного отображения (ORM — Object Relational Mapper) дает ряд преимуществ. Все, что можно сделать в ORM , можно сделать вручную, но с большими усилиями и более высокими требованиями к уровню разработчика.

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

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

Для PHP можно использовать ORM Doctrine .

21. Будьте осторожны с постоянными соединениями

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

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

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

Оптимальная настройка MySQL

Привет! Данная статья покажет как проводится оптимальная настройка MySQL.

Дефолтные конфигурационные параметры в MySQL рассчитаны на микроскопические базы данных, работающие под малыми нагрузками на скромном железе.

Настройка некоторых параметров может повысить производительность базы данных в сотни раз!

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

Разберемся с параметрами и рекомендациями по установке их значений. Настройки нужно вносить в my.cnf.

innodb_buffer_pool_size

Если Вы используете только InnoDB таблицы, устанавливайте это значение максимально возможным для Вашей системы. Буфер InnoDB кэширует и данные и индексы. Поэтому значение этого ключа стоит устанавливать в 70%…80% всей доступной памяти.

innodb_buffer_pool_size = 24G
# При том, что на нашем сервере 32Гб оперативной памяти

innodb_log_file_size

Эта опция влияет на скорость записи. Она устанавливает размер лога операций (так операции сначала записываются в лог, а потом применяются к данным на диске). Чем больше этот лог, тем быстрее будут работать записи (т.к. их поместится больше в файл лога). Файлов всегда два, а их размер одинаковый. Значением параметра задается размер одного файла:

innodb_log_file_size = 512M
# Так два файла дадут размер лога в 2x512M = 1G

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

innodb_log_buffer_size

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

innodb_log_buffer_size = 2M
# Значения по умолчанию в 1М должно быть достаточно для большинства случаев

innodb_file_per_table

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

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

innodb_flush_method

Этот параметр определяет логику сброса данных на диск. В современных системах при использовании RAID и резервных узлов, вы будете выбирать между O_DSYNC и O_DIRECT:

innodb_flush_method = O_DSYNC
# Помните об обязательном использовании резервных узлов (например, реплик)

innodb_flush_log_at_trx_commit

Изменение этого параметра может повысить пропускную способность записи данных в базу в сотни раз. Он определяет, будет ли MySQL сбрасывать каждую операцию на диск (в файл лога).

Тут следует руководствоваться такой логикой:

innodb_flush_log_at_trx_commit = 1 для случаев, когда сохранность данных — это приоритет номер один.
innodb_flush_log_at_trx_commit = 2 для случаев, когда небольшая потеря данных не критична (например, вы используете дублирование и сможете восстановить небольшую потерю). В этом случае транзакции будут сбрасываться в лог на диск только раз в секунду.
Устанавливайте значение на свое усмотрение, однако в большинстве случаев подойдет второй вариант:

innodb_flush_log_at_trx_commit = 2
# Значительное ускорение записи в базу, однако это потребует механизмов дублирования данных

query_cache_size

Значение этого параметра определяет сколько памяти стоит использовать под кэш запросов. Самый правильный подход — не полагаться на этот механизм. На практике он работает очень неэффективно. Так, весь кэш запросов для определенной таблицы сбрасывается всякий раз, когда в таблицу вносится хотя бы одно изменение. Это может привести к тому, что включение кэширования даже замедлит базу данных:

query_cache_size = 0
# Однако убедитесь, что используете индексы для обеспечения высокой скорости работы запросов

max_connections

Не следует изменять значение этого параметра на старте. Однако, если вы получаете ошибки «Too many connections», эту опцию стоит поднимать. Она определяет максимальное количество одновременных соединений с базой данных:

max_connections = 256
# Поднимайте значение постепенно при появлении ошибок соединений

Заключение

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

Оптимальное использование MySQL. Настоящим «Чайникам» посвящается или MySQL для начинающих

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

Базы данных: основные понятия

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

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

В этой таблице данные – это собственно номера телефонов, адреса и ФИО., т.е. строки «Иванов Иван Иванович», «32-43-12 » и т.п., а названия столбцов этой таблицы, т.е. строки «ФИО», «Номер телефона» и «Адрес» задают смысл этих данных, их семантику.

Таблица 10.1. Пример базы данных: телефонная книга

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

Для точности дадим определение базы данных, предлагаемое Глоссарий.ру

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

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

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

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

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

Примеры объектных СУБД: Cache, GemStone (от Servio Corporation), ONTOS (ONTOS).

В последнее время производители СУБД стремятся соединить два этих подхода и проповедуют объектно-реляционную модель представления данных. Примеры таких СУБД – IBM DB2 for Common Servers, Oracle8.

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

1. Ключи

Первичный ключ (primary key, PK) – минимальный набор полей, уникально идентифицирующий запись в таблице. Значит, первичный ключ – это в первую очередь набор полей таблицы, во-вторых, каждый набор значений этих полей должен определять единственную запись (строку) в таблице и, в-третьих, этот набор полей должен быть минимальным из всех обладающих таким же свойством.

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

Например, в нашей таблице (см. выше) ФИО и адрес позволяют однозначно выделить запись о человеке. Если же говорить в общем, без связи с решаемой задачей, то такие знания не позволяют точно указать на единственного человека, поскольку существуют однофамильцы, живущие в разных городах по одному адресу. Все дело в границах, которые мы сами себе задаем. Если считаем, что знания ФИО, телефона и адреса без указания города для наших целей достаточно, то все замечательно, тогда поля ФИО и адрес могут образовывать первичный ключ. В любом случае проблема создания первичного ключа ложится на плечи того, кто проектирует базу данных (разрабатывает структуру хранения данных). Решением этой проблемы может стать либо выделение характеристик, которые естественным образом определяют запись в таблице (задание так называемого логического, или естественного, PK), либо создание дополнительного поля, предназначенного именно для однозначной идентификации записей в таблице (задание так называемого суррогатного, или искусственного, PK).

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

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

Например, у нас есть три таблицы: содержащая информацию об исторических личностях (Persons), содержащая информацию об их изобретениях (Artifacts) и содержащая изображения как личностей, так и артефактов (Images) (рис 10.1).

Первичным ключом во всех этих таблицах является поле >

2. Индексирование

Для этого (для оптимизации производительности запросов) производят индексирование некоторых полей таблицы. Использовать индексы полезно для быстрого поиска строк с указанным значением одного столбца. Без индекса чтение таблицы осуществляется по всей таблице, начиная с первой записи, пока не будут найдены соответствующие строки. Чем больше таблица, тем больше накладные расходы. Если же таблица содержит индекс по рассматриваемым столбцам, то база данных может быстро определить позицию для поиска в середине файла данных без просмотра всех данных. Это происходит потому, что база данных помещает проиндексированные поля поближе в памяти, так, чтобы можно было побыстрее найти их значения. Для таблицы, содержащей 1000 строк, это будет как минимум в 100 раз быстрее по сравнению с последовательным перебором всех записей. Однако в случае, когда необходим доступ почти ко всем 1000 строкам, быстрее будет последовательное чтение, так как при этом не требуется операций поиска по диску. Так что иногда индексы бывают только помехой. Например, если копируется большой объем данных в таблицу, то лучше не иметь никаких индексов. Однако в некоторых случаях требуется задействовать сразу несколько индексов (например, для обработки запросов к часто используемым таблицам).

Если говорить о Mysql, то там существует три вида индексов: PRIMARY, UNIQUE, и INDEX, а слово ключ (KEY) используется как синоним слова индекс (INDEX). Все индексы хранятся в памяти в виде B-деревьев.

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

UNIQUE – ключ (индекс), задающий поля, которые могут иметь только уникальные значения.

INDEX – обычный индекс (как мы описали выше). В Mysql, кроме того, можно индексировать строковые поля по заданному числу символов от начала строки.

3. СУБД Mysql

Работать с Mysql можно не только в текстовом режиме, но и в графическом. Существует очень популярный визуальный интерфейс (кстати, написанный на PHP) для работы с этой СУБД. Называется он PhpMyAdmin. Этот интерфейс позволяет значительно упростить работу с базами данных в Mysql.

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

Перед тем как переходить к детальному изучению языка SQL, несколько слов об установке Mysql и подготовке к работе. Если вы не собираетесь заниматься администрированием сервера, то информация, приведенная ниже, пригодится вам только для общего развития. Итак, устанавливается Mysql очень просто – автоматически, пару раз нажмите OK, и все. После этого вы можете зайти в директорию, где лежат файлы типа Mysql.exe, Mysqld.exe и т.п. (у нас под Windows XP это c:\Mysql\bin) Последний файл запускает Mysql-сервер. В некоторых системах сервер запускается в виде сервиса. После запуска сервера следует запустить Mysql-клиент, запустив программу Mysql.exe. Здесь даже пароля не спросят. Более того, если вы наберете shell> Mysql.exe -u root или shell>Mysql -u root Mysql то получите все права администратора Mysql сервера. Кстати, выполнять эти команды надо, находясь в той директории, где лежат файлы Mysql.exe.
Для начала, не вдаваясь в подробности команд, исправим эти два недочета (отсутствие пароля у администратора и возможность входа анонимным пользователям):

Все данные о пользователях Mysql хранит в таблице user в специальной базе данных Mysql, доступ к которой имеет только администратор сервера. Поэтому, чтобы изменить какой-либо пароль, нужно изменить эту таблицу. Пароль задается с помощью функции PASSWORD, которая кодирует введенные данные. Кроме изменения пароля администратора, нужно еще удалить всех пользователей, не имеющих логина (команда DELETE). Команда Flush Privileges заставляет вступить в действие изменения, произошедшие в системной базе данных (Mysql).

Теперь создадим базу данных, с которой будем работать (мы все еще работаем как администратор сервера):
Mysql>create database book;

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

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

Mysql> GRANT ALL PRIVILEGES ON book.* TO [email protected]
IDENTIFIED BY «123»;

Команда GRANT наделяет пользователя nina, зашедшего на сервер с этой же машины (c localhost) и идентифицируемого паролем «123», определенными правами (в данном случае всеми) на все таблицы базы данных book. Теперь мы можем выйти и зайти как пользователь nina с соответствующим паролем:

shell>Mysql -u nina -p
Enter password : ***
Welcome to the Mysql monitor.
Mysql>

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

7. Построение интерфейса для добавления информации

Разобьем эту задачу на следующие подзадачи:

  • установка соединения с БД;
  • выбор рабочей БД;
  • получение списка полей таблицы;
  • отображение полей в html-форму.

После этого данные, введенные в форму, нужно записать в базу данных.

Рассмотрим все эти задачи по порядку.

8. Установка соединения

Воспользуемся функцией Mysql_connect.

ресурс Mysql_connect ([строка server
[, строка username [, строка password
[, логическое new_link
[, целое client_flags]]]]])

Данная функция устанавливает соединение с сервером Mysql и возвращает указатель на это соединение или FALSE в случае неудачи.

Для отсутствующих параметров устанавливаются следующие значения по умолчанию:
server = «localhost:3306»
username = имя пользователя владельца процесса сервера
password = пустой пароль

Если функция вызывается дважды с одними и теми же параметрами, то новое соединение не устанавливается, а возвращается ссылка на старое соединение.

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

Параметр client_flags — это комбинация следующих констант:

MYSQL_CLIENT_COMPRESS (использовать протокол сжатия),
MYSQL_CLIENT_IGNORE_SPACE (позволяет вставлять пробелы после имен функций), MYSQL_CLIENT_INTERACTIVE (ждать interactive_timeout секунд — вместо wait_timeout — до закрытия соединения).

Параметр new_link появился в PHP 4.2.0, а параметр client_flags — в PHP 4.3.0.

Соединение с сервером закрывается при завершении исполнения скрипта, если оно до этого не было закрыто с помощью функции Mysql_close().

Итак, устанавливаем соединение с базой данных на локальном сервере для пользователя nina с паролем “123”:

«Невозможно установить
соединение: » . Mysql_error ());
echo «Соединение установлено» ;
Mysql_close ($conn );
?>

Действие Mysql_connect равносильно команде

shell>Mysql -u nina -p123

9. Выбор базы данных

В PHP для этого существует функция Mysql_select_db.

Mysql_select_db (строка database_name [, ресурс link_identifier])

Эта функция возвращает TRUE в случае успешного выбора базы данных и FALSE

  • в противном случае.

Сделаем базу данных book рабочей:

«Невозможно установить
соединение: » . Mysql_error ());
echo «Соединение установлено» ;
Mysql_select_db («book» );
?>

10. Получение списка полей таблицы

10.1. Синтаксис Mysql_list_fields

ресурс Mysql_list_fields (
строка database_name,
строка table_name
[, ресурс link_identifier])

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

Функция Mysql_field_name возвращает имя поля, полученного в результате выполнения запроса. Функция Mysql_field_len возвращает длину поля. Функция Mysql_field_type возвращает тип поля, а функция Mysql_field_flags возвращает список флагов поля, записанных через пробел. Типы поля могут быть int, real, string, blob и т.д. Флаги могут быть not_null, primary_key, unique_key, blob,
auto_increment и т.д.

Синтаксис у всех этих команд одинаков:

строка Mysql_field_name (ресурс result, целое field_offset)
строка Mysql_field_type (ресурс result, целое field_offset)
строка Mysql_field_flags (ресурс result, целое field_offset)
строка Mysql_field_len (ресурс result, целое field_offset)

Здесь result — это идентификатор результата запроса (например, запроса, отправленного функциями Mysql_list_fields или Mysql_query (о ней будет рассказано позднее)), а field_offset — порядковый номер поля в результате.

Вообще говоря, то, что возвращают функции типа Mysql_list_fields или Mysql_query, представляет собой таблицу, а точнее, указатель на нее. Чтобы получить из этой таблицы конкретные значения, нужно задействовать специальные функции, которые построчно читают эту таблицу. К таким функциям и относятся Mysql_field_name и т.п. Чтобы перебрать все строки в таблице результата выполнения запроса, нужно знать число строк в этой таблице. Команда Mysql_num_rows(ресурс result) возвращает число строк во множестве результатов
result.

А теперь попробуем получить список полей таблицы Artifacts (коллекция экспонатов).

«Невозможно установить
соединение: » . Mysql_error ());
echo «Соединение установлено» ;
Mysql_select_db («book» );
$list_f = Mysql_list_fields (
«book» , «Artifacts» , $conn );
$n = Mysql_num_fields ($list_f );
for($i = 0 ; $i «
Строка флагов поля: » .
$flags_str . » » ;
>
?>

В результате должно получиться примерно вот что (если в таблице всего два поля, конечно):

Имя поля: id
Тип поля: int
Длина поля: 11
Строка флагов поля:
not_null primary_key auto_increment
Имя поля: title
Тип поля: string
Длина поля: 255
Строка флагов поля:
Отображение списка полей в html-форму

Теперь немножко подкорректируем предыдущий пример. Будем не просто выводить информацию о поле, а отображать его в подходящий элемент html- формы. Так, элементы типа BLOB переведем в textarea (заметим, что поле description, которое мы создавали с типом TEXT, отображается как имеющее тип BLOB), числа и строки отобразим в текстовые строки ввода , а элемент, имеющий метку автоинкремента, вообще не будем отображать, поскольку его значение устанавливается автоматически.

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

массив explode(строка separator, строка string [, int limit ])

Эта функция разбивает строку string на части с помощью разделителя separator и возвращает массив полученных строк.

В нашем случае в качестве разделителя нужно взять пробел “ «, а в качестве исходной строки для разбиения — строку флагов поля.

Итак, создадим форму для ввода данных в таблицу Artifacts:

// устанавливаем соединение
$database = «book» ;
$table_name = «Artifacts» ;
Mysql_select_db ($database ); // выбираем базу данных для
// работы

// число строк в результате
// предыдущего запроса (т.е. сколько всего
// полей в таблице Artifacts)
echo «» ;
?>

Если открытые соединения отсутствуют, функция пытается соединиться с СУБД, аналогично функции Mysql_connect() без параметров. Результат запроса буферизируется.

Замечание: строка запроса НЕ должна заканчиваться точкой с запятой.

Только для запросов SELECT, SHOW, EXPLAIN, DESCRIBE, Mysql_query() возвращает указатель на результат запроса, или FALSE, если запрос не был выполнен. В остальных случаях Mysql_query() возвращает TRUE, если запрос выполнен успешно, и FALSE — в случае ошибки. Значение, не равное FALSE, говорит о том, что запрос был выполнен успешно. Оно не говорит о количестве затронутых или возвращенных рядов. Вполне возможна ситуация, когда успешный запрос не затронет ни одного ряда. Mysql_query() также считается ошибочным и вернет FALSE, если у пользователя недостаточно прав для работы с указанной в запросе таблицей.

Итак, теперь мы знаем, как отправить запрос на вставку строк в базу данных.
$list_f = Mysql_list_fields ($database , $table_name );
// получаем список полей в базе
$n = Mysql_num_fields ($list_f ); // число строк в результате
// предыдущего запроса
// составим один запрос сразу для всех полей таблицы
$sql = «INSERT INTO $table_name SET » ; // начинаем создавать
// запрос, перебираем все поля таблицы
for($i = 0 ; $i // вычисляем имя поля
$value = $_POST [ $name_f ]; // вычисляем значение поля
$j = $i + 1 ;
$sql = $sql . $name_f . » = «$value»» ; // дописываем в
// строку $sql пару имя=значение
if ($j <> $n ) $sql = $sql . «, » ; // если поле не
// последнее в списке, то ставим запятую
>
// перед тем как записывать что-то в базу,
// можно посмотреть, какой запрос получился
//echo $sql;
$result = Mysql_query ($sql , $conn ); // отправляем запрос
// выводим сообщение успешно ли выполнен запрос
if (! $result ) echo » Can»t add ($table_name) » ;
else echo «Success!
» ;
?>

Листинг 11.0.2. insert.php

Итак, задачу добавления данных с помощью web-интерфейса мы решили. Однако тут есть одна тонкость. При решении мы не учитывали тот факт, что значения некоторых полей (author, photo) должны браться из других таблиц (Persons, Images). Поскольку Mysql с внешними ключами не работает, этот момент остается на совести разработчиков системы, т.е. на нашей совести. Нужно дописать программу таким образом, чтобы была возможность вводить в такие поля правильные значения. Но мы делать этого не будем, поскольку задача лекции состоит в том, чтобы познакомить читателя с элементами технологии, а не в том, чтобы создать работающую систему. Кроме того, имеющихся у читателя знаний вполне достаточно, чтобы решить эту проблему самостоятельно. Мы же обратимся к другой задаче — отображение данных, хранящихся в базе данных СУБД Mysql.

15. Литература

Оптимальное использование MySQL

Введение

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

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

Такой подход, конечно, дает желаемый результат — интерфейсы веб-сайта пользователя в итоге оказываются интегрированными с базой данных. Однако не всегда пользователи задумываются о том, насколько оптимально работает их база, как можно оптимизировать происходящие при работе с MySQL процессы и каково будет функционирование виртуального сервера при увеличившейся нагрузке, «наплывах» пользователей в результате, например, «раскрутки» сайта.

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

Какие данные нужно хранить в MySQL

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

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

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

В ситуациях, когда реально требуется получить только определенную порцию данных из MySQL, можно использовать ключ LIMIT для функции SELECT . Это полезно, когда, например, нужно показать результаты поиска чего-либо в базе данных. Допустим, в базе есть список товаров, которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в нужной категории несколько негуманно по отношению к пользователю — каналы связи с интернет не у всех быстрые и выдача лишних ста килобайт информации зачастую заставляет пользователяй провести не одну минуту в ожидании результатов загрузки страницы. В таких ситуациях информацию выдают порциями по, допустим, 10 позиций. Неправильно делать выборку из базы всей информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать запрос вида

select good, price from books limit 20,10

В результате, MySQL «отдаст» Вам 10 записей из базы начиная с 20-й позиции. Выдав результат пользователю, сделайте ссылки «Следующие 10 товаров», в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к MySQL.

Также следует помнить, что при составлении запросов к базе данных (SQL queries) следует запрашивать только ту информацию, которая Вам реально нужна. Например, если в базе 10 полей, а в данный момент реально требуется получить только два из них, вместо запроса

select * from table_name

используйте конструкцию вида

select field1, field2 from table_name

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

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

select title from books where author=» Иванов»

Также есть ключ LIKE, который позволяет искать поля, значения которых «похожи» на заданный шаблон:

select title from books where author like » Иванов%»

В данном случае MySQL выдаст названия книг, значения поля author у которых начинаются с » Иванов»

Ресурсоемкие операции

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

Индексы

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

В каких ситуациях создание индекса целесообразно:

  1. Быстрый поиск строк при использовании конструкции WHERE
  2. Поиск строк из других таблиц при выполнении объединения
  3. Поиск значения MIN() или MAX() для проиндексированного поля
  4. Сортировка или группировка таблицы в случае, если используется проиндексированное поле
  5. В некоторых случаях полностью теряется необходимость обращаться к файлу данных. Если все используемые поля для некоторой таблицы цифровые и формируют левосторонний индекс для некоторого ключа, то значения могут быть возвращены полностью из индексного дерева с намного большей скоростью.
  6. Если выполняются запросы вида
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
    и существует смешанный индекс для полей col1 и col2, то данные будут возвращены напрямую. Если же созданы отдельные индексы для col1 и для col2, то оптимизатор попробует найти наиболее ограниченный индекс путем определения того, какой из индексов может найти меньше строк, и будет использовать этот индекс для получения данных.
    Если у таблицы есть смешанный индекс, то будет использоваться любое левостороннее совпадение с существующим индексом. Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1, col2, col3).

Подробнее об индексировании

Поддержка соединения

Как Вы наверняка знаете, для работы с MySQL-сервером необходимо предварительно установить с ним соединение, предъявив логин и пароль. Процесс установки соединения может продолжаться гораздо большее время, нежели непосредственная обработка запроса к базе после установки соединения. Следуя логике, надо избегать лишних соединений к базе, не отсоединяясь от нее там, где это можно сделать, если в дальнейшем планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт установил соединение к базе, сделал выборку данных для анализа, не нужно закрывать соединение к базе, если в процессе работы этого же скрипта Вы планируете результаты анализа поместить в базу.

Также можно поддерживать так называемое persistent (постоянное) соединение к базе, но это возможно в полном объеме при использовании более сложных сред программирования, чем php или perl в обычном CGI-режиме, когда интерпретатор соответствующего языка разово запускается веб-сервером для выполнения пришедшего запроса.

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

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

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

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

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

// Кэш запроса НЕ РАБОТАЕТ $r = mysql_query(«SELECT username FROM user WHERE signup_date >= CURDATE()»); // Кэш запроса РАБОТАЕТ! $today = date(«Y-m-d»); $r = mysql_query(«SELECT username FROM user WHERE signup_date >= «$today»»);

Причина того, что кэш запросов не работает в первом случае, заключается в использовании функции CURDATE() . Такой подход используется для всех недетерминированных функций, например, NOW(), RAND() и т.д. Так как возвращаемый результат функции может измениться, то MySQL решает не размещать данный запрос в кэше. Все что, нужно, чтобы исправить ситуацию — это добавить дополнительную строчку кода PHP перед запросом.

2. Используйте EXPLAIN для ваших запросов SELECT

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

Результат запроса EXPLAIN показывает, какие индексы используются, как таблица сканируется и сортируется, и так далее.

Возьмем запрос SELECT (предпочтительно, чтобы он был сложным, с JOIN), добавим перед ним ключевое слово EXPLAIN. Вы можете использовать PhpMyAdmin для этого. Такой запрос выведет результат в прекрасную таблицу. Допустим, мы забыли добавить индекс для столбца, который используется для JOIN:

После добавления индекса для поля group_id:

Теперь вместо сканирования 7883 строк, будут сканироваться только 9 и 16 строк из двух таблиц. Хорошим методом оценки производительности является умножение всех чисел в столбце “rows”. Результат примерно пропорционален прорабатываемому объему данных.

3. Используйте LIMIT 1, если нужно получить уникальную строку

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

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

// Есть ли какой нибудь пользователь из Алабамы? // Так не нужно делать: $r = mysql_query(«SELECT * FROM user WHERE state = «Alabama»»); if (mysql_num_rows($r) > 0) < // . >// Вот так будет значительно лучше: $r = mysql_query(«SELECT 1 FROM user WHERE state = «Alabama» LIMIT 1″); if (mysql_num_rows($r) > 0) < // . >

4. Индексируйте поля поиска

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

Как вы можете видеть, данное правило применимо и к поиску по части строки, например, “last_name LIKE ‘a%’”. Когда для поиска используется начало строки, MySQL может использовать индекс столбца, по которому проводится поиск.

Вам также следует разобраться, для каких видов поиска нельзя использовать обычное индексирование. Например, при поиске слова (“WHERE post_content LIKE ‘%apple%’”) преимущества индексирования будут не доступны. В таких случая лучше использовать или построение собственных решений на основе индексирования.

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

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

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

// Поиск компании из определенного штата $r = mysql_query(«SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users. >

6. Не используйте ORDER BY RAND()

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

Если вам действительно нужно случайным образом располагать строки в результате вашего запроса, то существует множество лучших способов решить такую задачу. Конечно, это будет реализовано дополнительным кодом, но вы будете спасены от проблемы, которая растет по экспоненциальному закону вместе с ростом объема данных. Дело в том, что MySQL выполняет операцию RAND() (которая занимает время процессора) для каждой отдельной строки в таблице перед тем, как отсортировать ее и выдать вам только одну строку.

// Так делать НЕ НУЖНО: $r = mysql_query(«SELECT username FROM user ORDER BY RAND() LIMIT 1»); // Вот так будет лучше работать: $r = mysql_query(«SELECT count(*) FROM user»); $d = mysql_fetch_row($r); $rand = mt_rand(0,$d — 1); $r = mysql_query(«SELECT username FROM user LIMIT $rand, 1»);

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

7. Старайтесь не использовать SELECT *

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

Хорошей привычкой является указание столбца при выполнении SELECT.

// Плохо: $r = mysql_query(«SELECT * FROM user WHERE user_ ; // Разница становится существенной на больших объемах данных

8. Старайтесь использовать поле id везде

Хорошей практикой является использование в каждой таблице поля id, для которого установлены свойства PRIMARY KEY, AUTO_INCREMENT, и оно имеет тип из семейства INT. Предпочтительно — UNSIGNED, так как в этом случае значение не может быть отрицательным.

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

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

Одним возможным исключением из данного правила являются “ассоциативные таблицы”, которые используются для отношений многие-ко-многим между двумя другими таблицами. Например, таблица “posts_tags” содержит 2 столбца: post_id, tag_id. Они используются для описания отношений между двумя таблицами “post” и “tags”. Описанная таблица может иметь основной ключ, который содержит оба поля id.

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

13. Небуферированные запросы

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

Отличное объяснение функции из документации PHP:

“mysql_unbuffered_query() отправляет SQL запрос на сервер MySQL без автоматического получения и буферирования строк результата, как это делает функция mysql_query(). Таким образом, сохраняется определенный объем памяти запросами SQL, которые выдают большой набор результата, и можно начинать работать с набором результата сразу же после получения первой строки, не дожидаясь пока запрос SQL будет полностью выполнен.”

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

14. Храните IP адрес как UNSIGNED INT

Многие программисты создают поле VARCHAR(15) для хранения IP адреса, даже не задумываясь о том, что будут хранить в этом поле целочисленное значение. Если использовать INT, то размер поля сократится до 4 байт, и оно будет иметь фиксированную длину.

Нужно использовать тип UNSIGNED INT, так как IP адрес задействует все 32 бита беззнакового целого.

$r = «UPDATE users SET ip = INET_ATON(«<$_SERVER["REMOTE_ADDR"]>«) WHERE user_ >

15. Таблицы с фиксированной длиной записи (Static) работают быстрее

Когда каждый отдельный столбец в таблице имеет фиксированную длину, то вся таблица в целом рассматривается как . Примеры типов столбцов, которые не имеют фиксированной длины: VARCHAR, TEXT, BLOB. Если вы включите хотя бы один столбец с таким типом, то таблица перестает рассматриваться как «static» и будет по-другому обрабатываться механизмом MySQL.

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

Такие таблицы также проще кэшировать и проще восстанавливать при сбоях. Но они могут занимать больше места. Например, если конвертировать поле VARCHAR(20) в поле CHAR(20), то всегда будут заняты 20 байт вне зависимости от того, используются они или нет.

Использование техники «Вертикальное разделение» дает возможность отделить столбцы с переменной длиной в отдельную таблицу.

16. Вертикальное разделение

Вертикальное разделение — это действие по разделению структуры таблицы по вертикали с целью оптимизации.

Пример 1 : У вас есть таблица, которая содержит домашние адреса, редко используемые в приложении. Вы можете разделить вашу таблицу и хранить адреса в отдельной таблице. Таким образом основная таблица пользователей сократится в размере. А как известно, меньшая таблица обрабатывается быстрее.

Пример 2 : У вас в таблице есть поле “last_login”. Оно обновляется каждый раз, когда пользователь регистрируется на сайте. Но каждое обновление таблицы вызывает кэширование запроса, что может создать перегрузку системы. Вы можете выделить данное поле в другую таблицу, чтобы сделать обновления таблицы пользователей не такими частыми.

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

17. Разделяйте большие запросы DELETE или INSERT

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

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

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

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

Переменные $dblocation, $dbuser и $dbpasswd хранят имя сервера, имя пользователя и пароль.

1.2 Разрыв соединения с сервером. Функция mysql_close

Соединение с MySQL – сервером будет автоматически закрыто по завершении работы сценария, либо же при вызове функции mysql_close

Эта функция разрывает соединение с сервером MySQL, и возвращает true при успешном выполнении операции и false в противном случае. Функция принимает в качестве аргумента дескриптор соединения с базой данных, возвращаемый функцией mysql_connect.

$dblocation = «localhost»; //Имя сервера

$dbuser = «root»; //Имя пользователя

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

$dbcnx = @ mysql_connect($dblocation, $dbuser, $dbpasswd);


if (!$dbcnx) //Если дескриптор равен 0, соединение не установлено

B настоящий момент сервер базы данных не доступен, поэтому корректное отображение страницы невозможно. «);

if (mysql_close($dbcnx)) //разрываем соединение

echo(«Соединение с базой данных прекращено»);

echo(«He удалось завершить соединение»);

1.3 Создание базы данных. Функция CREATE DATABASE

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

CREATE DATABASE ИмяБазыДанных

Создает новую базу данных с именем имяБазыданных.

Пример работы с этой функцией:

@mysql_query(«CREATE DATABASE $dbname»);

Рекомендуется везде использовать апострофы («SQL – команда») в качестве ограничителей строк, содержащих SQL – команды. Этим можно гарантировать, что никакая $ — переменная случайно не будет интерполирована (т.е. не заменится на свое значение), и увеличится безопасность скриптов.

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

Для экспериментов создадим базу данных testbase, выполнив SQL-запрос из командной строки. Для этого нужно войти в систему MySQL и ввести в командной строке MySQL:

mysql> create database testbase;

После этого следует набрать:

База данных создана:

1.4 Выбор базы данных. Функция mysql_select_db

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

bool mysql_select_db(string $database_name [,resource $link_identifier])

Она уведомляет PHP, что в дальнейших операциях с соединением $link_identifier будет использоваться база данных $database_name.

Использование этой функции эквивалентно вызову команды use в SQL-запросе, т. е. функция mysql_select_db выбирает базу данных для дальнейшей работы, и все последующие SQL-запросы применяются к выбранной базе данных. Функция принимает в качестве аргументов название выбираемой базы данных database_name и дескриптор соединения resource. Функция возвращает true при успешном выполнении операции и false — в противном случае:

//Код соединения с базой данных

if (! @mysql_select_db($dbname, $dbcnx))

B настоящий момент база данных не доступна, поэтому корректное отображение страницы невозможно. «);

1.5 Обработка ошибок

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

Важно аккуратно и своевременно использовать эти функции, потому что иначе отладка сценариев может усложниться.

возвращает номер последней зарегистрированной ошибки. Идентификатор соединения $link_identifier можно не указывать, если за время работы сценария было установлено только одно соединение.

возвращает не номер, а строку, содержащую текст сообщения об ошибке. Ее удобно применять в отладочных целях. Обычно mysql_error используют вместе с конструкцией or die (), например:

@mysql_connect(«localhost», «user», «password»)

or die(«Ошибка при подключении к базе данных: «.mysql_error());

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

В последних версиях РНР предупреждения в MySQL-функциях по умолчанию не регистрируются.

1.6 Автоматизация подключения к MySQL. Файл ( config.php )

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

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

Имеет смысл помещать функции для соединения, выбора и создания базы данных в тот же файл (config.php), где объявлены переменные с именем сервера $dblocation, именем пользователя $dbuser, паролем $dbpasswd и именем базы данных $dbname:

//config.php код файла, содержащего параметры соединения с сервером и выбора базы данных

//выводит сообщения об ошибках соединения в браузер

$dblocation = «localhost»; //Имя сервера

$dbname = «вставить имя базы» //Имя базы данных: создаваемой или уже существующей

$dbuser = «root»; //Имя пользователя базы данных

//Осуществляем соединение с сервером базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

if (!$dbcnx) //Если дескриптор равен 0, соединение с сервером базы данных не установлено

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

//Создаем базу данных $dbname – это может делать только суперпользователь

//Если база данных уже существует, будет некритическая ошибка

@mysql_query(«CREATE DATABASE if not exists $dbname’);

//Код соединения с базой данной: осуществляем однозначный выбор только что созданной базы или уже существующей базы данных

//Подавляем вывод ошибок символом @ перед вызовом функции

if([email protected]_select_db($dbname, $dbcnx)) //Если дескриптор равен 0, соединение с базой данных не установлено

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

//Небольшая вспомогательная функция, которая выводит сообщение

//об ошибке в случае ошибки запроса к базе данных

2. ВЫПОЛНЕНИЕ ЗАПРОСОВ К БАЗЕ ДАННЫХ

2.1 Создание таблицы. Функция CREATE TABLE:

CREATE TABLE Имя Таблицы (ИмяПоля тип, ИмяПоля тип,)

Этой командой в базе данных создается новая таблица с колонками (полями), определяемыми своими именами (ИмяПоля) и указанными типами. После создания таблицы в нее можно будет добавлять записи, состоящие из перечисленных в данной команде полей.

Листинг test_11.php. Программа, создающая новую таблицу в базе данных:

Все, что вам нужно знать, чтобы начать работать с MySQL

Russian (Pусский) translation by Ilya Nikov (you can also view the original English article)

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

Введение

При постоянном расширении веб-приложений и веб-сайтов, управляемых пользователем, появляется необходимость иметь способ хранения данных в
организованном виде, при этом данные можно получить и манипулировать ими «на лету»; что невозможно при статическом контенте.
Для этого необходимо использовать базу данных!
Базой данных в ее простейшей форме является огранизованный сбор данных, независимо от того, используется ли шкаф для хранения документов или компьютеризированную базу данных.
Интеграция базы данных на ваш веб-сайт или веб-приложение позволяет хранить и извлекать данные с помощью определенных команд.
В этой статье мы будем использовать систему управления реляционной базой данных MySQL.
Это программное обеспечение с открытым исходным кодом, доступное в соответствии с общедоступной лицензией GNU, которое, конечно же, делает его бесплатным!
Веб-сайт MySQL можно найти по адресу http://www.mysql.com/. Все что вы видите в этой статье, указывает на функцию MySQL, вы можете найти полную документацию по этим функциям на веб-сайте MySQL.

СОВЕТ. Чтобы выполнить поиск на веб-сайте MySQL по определенной функции, просто добавьте имя этой функции в URL-адрес MySQL. НАПРИМЕР. http://www.mysql.com/SELECT, этот пример будет искать select.

Я создал модифицированную версию поискового буклета Jesse Ruderman для поиска документации MySQL 5.1. Чтобы использовать его, просто выделите подчеркнутое слово, затем нажмите букмарклет, чтобы перейти к результатам поиска. В качестве альтернативы запустите букмарклет ничего не выделяя, чтобы получить окно подсказки с просьбой ввести то, что вы хотите найти.
Закладка:> MySQL SEARCH

Понимание базы данных

Чтобы хранить данные в базе данных, сначала необходимо создать базу данных. Затем эта база данных может хранить множество таблиц (представьте, что в шкафу хранения хранится много файлов); каждая таблица должна иметь определенные столбцы, и эти столбцы создаются для хранения определенных данных (представьте форму ввода данных). Можно ограничить то, что может быть введено в них, например числовые данные или ограничения символов.
После того, как данные будут введены в базу данных, они будут сохранены в соответствующей таблице, тогда таблица будет состоять из строк и столбцов, похожих на таблицу, поскольку данные сохраняются и отображаются в табличной форме. MySQL может управлять несколькими базами данных, которые могут содержать несколько таблиц. Уровни доступа могут предоставляться различным пользователям для предоставления или отзыва определенных привилегий.
MySQL использует команды/функции на основе SQL (язык структурированных запросов). Это позволяет нам использовать ключевые слова, чтобы указать, какие данные мы хотим вернуть. Затем MySQL реализует некоторые дополнительные функции для улучшения доступных функций. Ключевые слова, как правило, легко понять и часто связаны с английским словом или фразой (например SELECT, UPDATE, WHERE).

Приступаем к работе

В этой части мы рассмотрим:

  • Установка MySQL на ваш локальный компьютер (windows)
  • Настройка локальной установки MySQL (windows)
  • Подключение к локальной базе данных
  • Подключение к удаленной базе данных
  • Ввод и форматирование запросов
  • Форматирование и протоколирование результатов
  • Резервное копирование базы данных
  • Восстановление базы данных

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

Сегодня мы будем использовать пакет Essentials, который доступен на веб-сайте MySQL по адресу http://dev.mysql.com/downloads/.
На момент написания этой статьи, текущей стабильной версией была 5.1, и я расскажу, как ее установить в операционной системе Windows. Сначала найдите нужное программное обеспечение, я буду использовать 64-битную версию Windows, но вы можете выбрать другую в зависимости от архитектуры вашей системы. Для этой статьи я решил использовать пакет Essentials, поскольку он включает в себя все необходимые функции. Вы можете сравнить различия между версиями windows по адресу http://dev.mysql.com/doc/refman/5.1/en/windows-choosing-package.html.

Установка MySQL на локальную машину

После того как вы загрузите соответствующий исполняемый файл установки, запустите его, и вам будет предложено вывести это окно. Как вы видите, я устанавливаю версию 5.1.30. Нажмите «Далее», чтобы перейти к следующему экрану.

Я решил сохранить типичную установку, но не стесняйтесь выбирать, какие компоненты вы хотите установить. Вам понадобятся сервер MySQL и файлы данных, если вы работаете на своей локальной машине; однако, если у вас есть доступная вам удаленная база данных, которую вы хотите использовать, вы можете не устанавливать эти функции. Вам понадобится оболочка командной строки MySQL, так как это приложение, которое мы будем использовать. Опять же, если вы не используете локальную базу данных, вы можете пропустить утилиты командной строки и конфигурацию экземпляра сервера. Эта статья не будет охватывать использование C и MySQL, однако, если вы работаете на C, вы можете установить ее. Нажмите «Далее», чтобы перейти к следующему экрану.

Затем вас попросят подтвердить ваш выбор, нажмите «Установить», если вы согласны, иначе нажмите «Назад», чтобы внести какие-либо изменения.

Будет запущена ваша установка, на этом этапе вы можете получить уведомление от антивируса или управления учетными записями пользователей Windows Vista, в нем указывается, что программа просит установить или получить доступ к определенным файлам. Это будет подписан MySQL AB; это компания, которая работает и поддерживает MySQL, о котором вы можете прочитать на http://www.mysql.com/about/.

После установки убедитесь, что сервер MySQL настроен (если установлен), и нажмите «Готово». Поздравляем, вы успешно установили MySQL Server / components. Затем должен загрузиться мастер настройки экземпляра сервера MySQL. Однако, если он не запускается, не волнуйтесь, вы можете получить к нему доступ в папке программ MySQL (если установлена).

Настройка локальной установки MySQL

Теперь мы настроим вашу установку (если вы установили сервер), нажмите «Далее», чтобы перейти к следующему экрану.

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

Я выбрал это как машину для разработки; однако вы можете выбрать, какой вариант вы предпочитаете. Машина для разработки содержит полный функицонал, но это предотвратит использование слишком большого количества ресурсов. Нажмите «Далее», чтобы перейти к следующему экрану.

Поскольку эта установка предназначена для целей разработки, а не для конкретного проекта, я выбрал многофункциональную базу данных, которая позволяет использовать механизм хранения InnoDB и MyISAM. Подробнее о механизмах хранения можно узнать по адресу http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html. Нажмите «Далее», чтобы перейти к следующему экрану.

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

Теперь вы должны настроить количество одновременных подключений, которые вы собираетесь подключать к своей базе данных в любой момент времени. Выбор DSS позволяет до 100 подключений, но предполагает в среднем 20 одновременных подключений. OLTP поддерживает до 500 одновременных подключений. Однако для нашей машины для разработки мы часто используем только одно соединение; поэтому я установил ручную настройку на 5. Нажмите «Далее», чтобы перейти к следующему экрану.

Теперь вам предоставляется возможность настроить параметры TCP/IP. Я убрал галочку, чтобы отключить этот флажок. Отключив это, он предотвращает удаленные подключения к базе данных. Затем вы можете установить режим сервера, я оставил этот флажок (вы можете подробнее узнать о режимах сервера по адресу http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html) , Нажмите «Далее», чтобы перейти к следующему экрану.

Теперь нам нужно выбрать, какой набор символов/ кодировку мы будем использовать. Я выбрал UTF8, поскольку она позволяет вводить символы на разных языках. Нажмите «Далее», чтобы перейти к следующему экрану.

Теперь вам нужно решить, хотите ли вы запускать сервер в качестве службы, вы можете настроить имя службы в раскрывающемся списке. Установив MySQL как службу по умолчанию, сервер будет запущен автоматически и перезапустится даже с ошибкой. Я рекомендую использовать приведенную выше настройку. Вы также можете включить файлы MySQL Bin в путь windows. Это позволяет нам вызывать MySQL непосредственно из командной строки, я также рекомендую это сделать. Нажмите «Далее», чтобы перейти к следующему экрану.

Теперь вы должны указать пароль для учетной записи root. Это учетная запись «master» с полными привилегиями (имя пользователя учетной записи будет «root»); Я не рекомендую оставлять его пустым! Выбор для включения доступа к корневым машинам по умолчанию отключен; Я сохранил его таким образом, так как отключил настройки TCP/IP, но он также открывает более высокий риск для безопасности, если детали попадают в чужие руки. Я также отключил выбор, чтобы включить анонимную учетную запись, поскольку эта база данных предназначена для личного использования. Нажмите «Далее», чтобы перейти к следующему экрану.

Теперь ваша конфигурация установки готова к применению, нажмите «выполнить», чтобы запустить процесс.
Затем вы получите сообщение об успешном конфигурировании. После завершения нажмите кнопку завершения, чтобы выйти из этого мастера. Теперь вы успешно завершили настройку.
Если вы устанавливаете MySQL в другой операционной системе или хотите взглянуть на официальную документацию, то ее можно найти по адресу http://dev.mysql.com/doc/refman/5.1/en/installing.html.

Подключение к локальной базе данных

Теперь, когда у вас установлен сервер, мы можем подключиться к нему!

Я собираюсь использовать Windows CLI (интерфейс командной строки) для инициализации соединения, но вы можете использовать MySQL CLI, найденный в папке программ MySQL.
Сначала откройте командную строку, открыв диалоговое окно запуска, которое находится в меню «Пуск», затем введите CMD и нажмите [ENTER]; под котором вы увидите окно, подобное приведенному ниже.

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

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

  • —host=host или -hhost
  • —port=port или -P
  • —username=username or -uusername
  • —password=password or -ppassword
  • база данных задается просто как строка (например db_name)

Примечание. Если вы печатаете строку пароля так, то она будет видна на экране, если вы предпочитаете, вы можете просто предоставить команду -p или -password без пароля, который будет представлен со скрытой подсказкой ввода пароля. Нам также не нужен командный терминатор, поскольку мы вводим в CLI Windows на данный момент не MySQL.

Так как мы хотим подключиться к нашему локальному компьютеру, так что мы можем исключить аргумент host (см. соединение с удаленным хостом позже в этой статье). Мы хотим войти в нашу учетную запись root, чтобы получить полные привилегии, поэтому нам понадобятся наши имя пользователя и пароль; поскольку в настоящее время у нас нет каких-либо баз данных, мы также не будем указывать аргумент базы данных.
Я буду подключаться к учетной записи пользователя root на моей локальной машине с паролем «secret», используя следующую команду:

Эта команда соединится с MySQL с использованием пользователя root и паролем secret. Следующие команды достигнут того же результата.

Учетная запись пользователя «root» — это основная учетная запись MySQL, которая была создана во время установки; это тот же пользователь, с которым вы должны подключаться. Однако имейте в виду, что не рекомендуется оставлять учетную запись root, включенной при веб-установке, для получения дополнительной информации см. Учетные записи пользователей и привилегии на веб-сайте MySQL. (http://dev.mysql.com/doc/refman/ 5.1 / о / добавления-users.html)

Подключение к удаленной базе данных

(пропустите это, если вы подключаетесь к локальной базе данных). Чтобы подключиться к вашей удаленной базе данных, часто предоставляемой хостинг-провайдером, вы должны знать IP-адрес или имя хоста вашей базы данных (и, возможно, номер порта, если он был изменен от значения по умолчанию — 3306). Вы будете подключаться к такой базе точно так же,как если бы вы подключались к локальной базе данных, просто заменяя аргументы хоста и порта в зависимости от конфигурации вашего хостинга.

Закрытие соединения

Чтобы отключиться от сеанса MySQL, отправьте команду QUIT (или \ q).

Форматирование запросов и результатов

Теперь, когда мы подключены (после получения сообщения «Welcome to the MySQL monitor.»), мы готовы отправить наши команды, но сначала есть еще несколько замечаний.

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

Примечание. Этот запрос не будет работать, поскольку у нас нет базы данных, установленной для демонстрационных целей!
Вы можете увидеть код, который я использовал ниже, оба введенных запроса идентичны, но нажав [ENTER], CLI позволяет нам продолжать команду на отдельной строке. MySQL реализовал функцию отмены ввода, если ваша команда охватывает несколько строк, просто выпустите команду clear «\ c».

Это, однако, также означает, что необходимо отправить специальную команду, чтобы указать, что это конец команды. Мы можем использовать одно из следующих:

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

Восстановление базы данных

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

Мы собираемся использовать предварительно подготовленную базу данных, доступную по адресу http://dev.mysql.com/doc/#sampledb, прокрутите вниз до тех пор, пока вы не увидите примеры баз данных, и вы хотите продолжить и загрузить базу данных world.

После подключения к MySQL мы собираемся восстановить базу данных world, так как это резервная копия предыдущей базы данных. Этот файл представляет собой просто список команд MySQL, которые будут запускаться для создания новой базы данных. Этот файл SQL содержит только данные таблицы и не сохраняет информацию о базе данных, поэтому мы должны сначала создать базу данных для хранения таблиц (см. Аналогию в начале статьи).
Примечание. Чтобы эта статья не стала слишком длинной, я буду размещать несколько команд в фрагментах кода, вы можете разделить эти команды или собрать их вместе для поиска завершающих команд, показанных ранее в этой статье. Также имейте в виду, что функции MySQL не чувствительны к регистру, поэтому SELECT — это то же самое, что и select и SeLEcT. Я лично использую функции MySQL, чтобы сделать мои запросы максимально простыми, поскольку запросы становятся длиннее и сложнее.

Чтобы создать базу данных, которую мы собираемся вызвать команду CREATE DATABASE, после создания мы будем использовать USE базу данных.

Время импортировать резервную копию, есть много способов сделать это (http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html). Поскольку мы уже подключены к MySQL, мы будем использовать команду SOURCE, за которой следует наше имя файла. Я извлек файл «world.sql» из папки zip и поместил его в свой корневой каталог C: вам нужно знать абсолютный путь к исходному файлу. Если вы укажете только свое имя файла, то MySQL будет искать источник в папке, в которой вы запустили свою командную строку (C:\USERS\USERNAME на vista C:\Documents and Settings\Username
на XP). Вы не можете перейти в другой каталог, используя интерфейс MySQL, поэтому убедитесь, что вы предоставили соответствующий абсолютный путь. После выдачи этой команды окно CLI выдает каждую из команд в файле SQL, после завершения вы будете возвращены в приглашение mysql>.

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

  • SHOW TABLES; — это показывает таблицы в текущей базе данных.
  • DESCRIBE tbl_name; — это показывает, какие поля существуют в указанной таблице.

После выдачи команды мы теперь знаем, что база данных содержит три таблицы: «city», «country» и «countrylanguage». Затем мы узнаем, какие поля хранятся в этих таблицах. Для каждой из таблиц выполните DESCRIBE tbl_name .

После выдачи команды DESCRIBE для таблицы country вы увидите, что она вернула нечитаемый беспорядок, чтобы решить эту проблему, просто используйте терминатор \G вместо;.

Теперь у нас есть вся информация, которая нам нужна для управления таблицами по своему желанию, у нас есть имя пользователя, пароль, имя базы данных, имена таблиц и имена полей, если вы работаете на другом языке, таком как PHP, это информация вам потребуется чтобы получить эти данные в вашей системе (см. http://php.net/mysql).

Логирование результатов

Мы можем выбрать логирование вывода MySQL-интерфейса; мы делаем это, отправив команду \T filename.txt. Чтобы остановить ведение журнала, мы выдаем команду \t. В приведенном ниже примере сохраняется файл с именем log.txt в корневой каталог жесткого диска E:. На моей машине это запасной жесткий диск, обратите внимание, что у вас должно быть разрешение на доступ к этому диску; вы не сможете записать данные в корень на установочном диске Windows в Windows Vista без запуска MySQL из командной строки с повышенными правами.

Резервное копирование базы данных

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

Чтобы запустить это, вы должны быть отключены от сеанса MySQL.

Следующий код логируется в сеанс MySQL с учетной записью пользователя root с паролем secret, затем он выгружает таблицу db_world в файл db_worldbak.sql.

Заключение

Прочитав эту статью, я надеюсь, что вы получили четкое представление о том, как взаимодействовать с mysql на уровне командной строки. То, что может оказаться полезным в качестве команд, может быть отправлено непосредственно в базу данных MySQL, а не через другой язык сценариев. Пожалуйста, не останавливайтесь здесь, http://dev.mysql.com/doc/ действительно отличный ресурс, и вы никогда не будете создавать свою собственную базу данных. Ниже я написал для вас несколько базовых запросов для поиска в базе данных world и некоторые примеры. Посмотрим, сможете ли вы их понять, если вы еще не знаете, где находится документация.

На английском языке этот запрос SELECT и возвращает Name и Population FROM таблицы city WHERE код страны является GBR, результаты затем фильтруются с использованием ORDER BY Population ASC (возрастающая популяция) и затем LIMIT, чтобы вернуть первые 5 результатов. Этот запрос показывает название и население 5 наименее населенных городов Великобритании.

На английском языке этот запрос SELECT данные, он CONCAT Name из таблицы city и Language из таблицы countrylanguage и отображает результаты AS Detail запрос также SELECT Population из таблицы city. Снова этот запрос SELECT свои данные FROM таблицы city и таблицы countrylanguage, он возвращает данные, в которых код страны города совпадает с кодом страны на языке страны. Данные отфильтровываются, чтобы отображать страны, WHERE код страны — это GBR, AND язык — английский, затем он ORDER BY возрастающей численности населения, а результаты LIMIT, чтобы показать результаты от 6 до 10. Этот запрос известен как INNER JOIN, поскольку две таблицы связаны между собой в запросе. Этот запрос отобразит 6-ю по 10-ю наименее населенные города с кодом страны GBR, в которых оворят по-английски.

Как я узнал, какие данные нужно писать для этих запросов? Запустив следующий запрос SELECT, он использует символ wild card, чтобы выбрать все поля и вернуть все данные в указанной таблице. Из результата запроса я понял, что было включено, и я сделал некоторые сценарии, которые я хотел запросить.

Вот несколько функций, которые вы можете посмотреть более подробно, чтобы начать работу:

Следите за последующим руководством по извлечению и обработке данных. Не стесняйтесь задавать любые вопросы в комментариях, и я сделаю все возможное, чтобы ответить на них. Убедитесь, что вы начали свой вопрос с #Q, чтобы я мог их найти!

  • Подпишитесь на RSS-канал NETTUTS для получения других ежедневных статей по веб-разработоке.

Оптимальное использование mysql

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

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

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

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

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

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

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

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

Вы можете узнать больше о 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 вы можете легко положить в кэш результат выполнения вашего запроса, например, следующим образом:

Теперь тяжелый запрос, использующий 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 — для хранения уникальных данных.

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

Это создаст индекс на таблице books, которая будет использовать первые 10 букв из колонки, которая хранит названия книг и имеет тип varchar. В этом случае, любой поиск с запросом WHERE на название книги с совпадением до 10 символов будет давать такой же результат, как и просмотр всей таблицы от начала до конца.

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

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

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

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

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

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

То же самое можно сделать с помощью mysqli:

Это вернет вам профилированные данные, содержащие время выполнения запроса во втором элементе ассоциативного массива.

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

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

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

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

Также это можно указать в файлах конфигурации /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.

Переменная 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 (просмотров: 2536)

Оптимальное использование MySQL

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

Такой подход, конечно, дает желаемый результат — интерфейсы веб-сайта пользователя в итоге оказываются интегрированными с базой данных. Однако не всегда пользователи задумываются о том, насколько оптимально работает их база, как можно оптимизировать происходящие при работе с MySQL процессы и каково будет функционирование виртуального сервера при увеличившейся нагрузке, «наплывах» пользователей в результате, например, «раскрутки» сайта.

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

Какие данные нужно хранить в MySQL

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

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

В ситуациях, когда реально требуется получить только определенную порцию данных из MySQL, можно использовать ключ LIMIT для функции SELECT. Это полезно, когда, например, нужно показать результаты поиска чего-либо в базе данных. Допустим, в базе есть список товаров, которые предлагает Ваш интернет-магазин. Выдавать весь список товаров в нужной категории несколько негуманно по отношению к пользователю — каналы связи с интернет не у всех быстрые и выдача лишних ста килобайт информации зачастую заставляет пользователей провести не одну минуту в ожидании результатов загрузки страницы. В таких ситуациях информацию выдают порциями по, допустим, 10 позиций. Неправильно делать выборку из базы всей информации и фильтрацию вывода скриптом. Гораздо оптимальнее будет сделать запрос вида

select good, price from books limit 20,10

В результате, MySQL «отдаст» Вам 10 записей из базы начиная с 20-й позиции. Выдав результат пользователю, сделайте ссылки «Следующие 10 товаров», в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к MySQL.

Также следует помнить, что при составлении запросов к базе данных (SQL queries) следует запрашивать только ту информацию, которая Вам реально нужна. Например, если в базе 10 полей, а в данный момент реально требуется получить только два из них, вместо запроса

select * from table_name

используйте конструкцию вида

select field1, field2 from table_name

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

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

select title from books where author=’Иванов’

Также есть ключ LIKE, который позволяет искать поля, значения которых «похожи» на заданный шаблон :

select title from books where author like ‘Иванов%’

В данном случае MySQL выдаст названия книг, значения поля author у которых начинаются с ‘Иванов’.

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

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

В каких ситуациях создание индекса целесообразно:

  1. Быстрый поиск строк при использовании конструкции WHERE
  2. Поиск строк из других таблиц при выполнении объединения
  3. Поиск значения MIN() или MAX() для проиндексированного поля
  4. Сортировка или группировка таблицы в случае, если используется проиндексированное поле
  5. В некоторых случаях полностью теряется необходимость обращаться к файлу данных. Если все используемые поля для некоторой таблицы цифровые и формируют левосторонний индекс для некоторого ключа, то значения могут быть возвращены полностью из индексного дерева с намного большей скоростью.
  6. Если выполняются запросы вида
    SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
    и существует смешанный индекс для полей col1 и col2, то данные будут возвращены напрямую. Если же созданы отдельные индексы для col1 и для col2, то оптимизатор попробует найти наиболее ограниченный индекс путем определения того, какой из индексов может найти меньше строк, и будет использовать этот индекс для получения данных.
    Если у таблицы есть смешанный индекс, то будет использоваться любое левостороннее совпадение с существующим индексом. Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1, col2, col3).

Как Вы наверняка знаете, для работы с MySQL-сервером необходимо предварительно установить с ним соединение, предъявив логин и пароль. Процесс установки соединения может продолжаться гораздо большее время, нежели непосредственная обработка запроса к базе после установки соединения. Следуя логике, надо избегать лишних соединений к базе, не отсоединяясь от нее там, где это можно сделать, если в дальнейшем планируется продолжить работу с SQL-сервером. Например, если Ваш скрипт установил соединение к базе, сделал выборку данных для анализа, не нужно закрывать соединение к базе, если в процессе работы этого же скрипта Вы планируете результаты анализа поместить в базу.

Также можно поддерживать так называемое persistent (постоянное) соединение к базе, но это возможно в полном объеме при использовании более сложных сред программирования, чем php или perl в обычном CGI-режиме, когда интерпретатор соответствующего языка разово запускается веб-сервером для выполнения пришедшего запроса.

Автор: Петр Диденко, Вадим Шестерин

Илон Маск рекомендует:  Разработка интернет-портала в студии IntroWeb
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL
Автор материала: Admin [Профиль]
Блоги: 1542
Сообщения: 6097
Откуда: Москва