Оптимальное использование MySQL. Некоторые советы.

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

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

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

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

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

SELECT good, price FROM books LIMIT 10,20

В результате, MySQL "отдаст" Вам 20 записей из базы начиная с 10-й позиции. Выдав результат пользователю, сделайте ссылки "Следующие 20 товаров", в качестве параметра передав скрипту следующую позицию, с которой будет делаться вывод списка товаров, и используйте это число при генерации запроса к 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 столбцам, т.к. в этом случае не происходит затрат времени на позиционирование жесткого диска.

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

• быстрый поиск строк при использовании конструкции WHERE;
• поиск строк из других таблиц при выполнении объединения;
• поиск значения MIN() или MAX() для проиндексированного поля;
• сортировка или группировка таблицы в случае, если используется проиндексированное поле.

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

SELECT * FROM table_name WHERE col1=val1 AND col2=val2

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

Если у таблицы есть смешанный индекс, то будет использоваться любое левостороннее совпадение с существующим индексом. Например, если есть смешанный индекс 3-х полей (col1, col2, col3), то индексный поиск можно осуществлять по полям (col1), (col1, col2) и (col1, col2, col3).

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

Статьи по теме

Обеспечение безопасности ваших веб-приложений с помощью PHP OOP и PDO

Лучшие практики обеспечения безопасности PHP и PDO в современной веб-разработке Оглавление ...

2024-11-26

Перенос сайта на WordPress с одного домена на другой

Данная инструкция поможет вам, если вам нужно перенести сайт: с одного домена на другой; с поддомена на основной домен (наприме�...

2024-11-15

OSPanel 6: быстрый старт

Коротко объясняю как быстро начать работу с Open Server Panel v.6 Содержание: Вступление Концепция новой версии Первая уст...

2024-11-08

Как установить PhpMyAdmin в Open Server Panel

Как установить PhpMyAdmin в Open Server Panel В данной статье мы рассмотрим процесс установки и настройки PhpMyAdmin (PMA) на платформе Open Server...

2024-11-08

Как быстро запустить Laravel на Windows

Все привет, меня зовут Павел Жуковский и сегодня я расскажу о том как быстро развернуть Laravel на Windows. Как развернуть чере...

2024-09-30

Next.js

На мой взгляд, Next.js - это лучший на сегодняшний день инструмент для разработки веб-приложений. Обратите внимание: руководство актуально �...

2024-09-25

OpenAI рассказал, как запретить ChatGPT использовать содержимое сайта для обучения

OpenAI рассказал, как запретить ChatGPT использовать содержимое сайта для обучения Либо ограничить доступ. OpenAI рассказа...

2024-09-05

WebSocket

Протокол WebSocket («веб-сокет»), описанный в спецификации RFC 6455, обеспечивает возможность обмена данными между браузером и сервером че...

2024-08-01

Ошибка 404: что это такое и как ее использовать для бизнеса

Для SEO 404 Error — возможность сохранить клиента и позиции в выдаче. Как это сделать, в статье. Окно «404 Not Found» вид...

2024-07-17

PostgreSQL 16. Изоляция транзакций. Часть 2

Введение Данная статья является продолжением первой части: "PostgreSQL 16. Организация данных. Часть 1". Как и первая часть, эта является объед�...

2024-05-27

Комментирование кода и генерация документации в PHP

Зачем нужны комментарии к программному коду? В каком виде их писать? Где они нужны, а где нет? Как правильно комментировать код? Как прид�...

2024-04-22

Что такое CSS-модули и зачем они нам?

В последнее время меня интригуют CSS-модули. Если вы о них не слышали — эта статья для вас. Мы рассмотрим, что это за проект, какие задачи о�...

2024-04-22

23 сервиса для эффективного экспресс-аудита любого сайта

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

2024-04-17

Яндекс переходит на новую версию Wordstat

Часть старой функциональности уже недоступна. Обновленный Яндекс Вордстат полностью перейдет на основной домен wordstat.ru 20 марта. С...

2024-04-08

Яндекс интегрировал в свой облачный сервис эмпатичную нейросеть

Она умеет распознавать эмоции человека по голосу. Алгоритм-эмпат определяет характер речи пользователя: говорит он с явным негатив...

2024-04-08