О грамотном использовании БД MySQL

Многие мои друзья и знакомые нередко спрашивают меня о том, как устроен
мой веб-сайт, сколько у меня таблиц в базе данных, как я храню данные и по
каким полям веду поиск. Я, естественно, не выдаю все свои муниципальные
потаенны, но всегда понимаю причину таких вопросов и пробую посодействовать людям
выстроить резвую и надежную базу данных – т.е. кропотливо обмыслить
структуру БД таким макаром, чтоб при увеличении нагрузки либо объема
таблиц динамический сайт не перевоплотился в тормозное усмертие. А ведь
многие новенькие (веб-строители) даже не догадываются о том, что большие
динамические веб-сайты тормозят совсем не из-за нагрузки скриптов на микропроцессор,
а в главном из-за неоптимизированного либо дохленького MySQL-сервера. При
этом почти во всем все находится в зависимости от того, как устроена ваша база данных.

Итак, начнем ликбез. Сходу всем вопрос: что делает MySQL во время
записи в таблицы типа INSERT либо UPDATE? Верно – Перекрывает ТАБЛИЦЫ и
пишет в их данные. Скорость записи и поиска может быть довольно низкой,
потому статус таблиц воспрещает другим процессам считывать из их данные
до окончания операции записи либо обновления и снятия блокировки. При всем этом
может получиться так, что во время записи единственного поля в длинноватые
таблицы, ваш MySQL-сервер навечно перекроет доступ к таблице остальным
скриптам.

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

ID – номер, первичный ключ
TEMA – тема анонсы
MESS -
сообщение, сама новость
VIEWS – количество просмотров

При каждом воззвании к новостям, скрипт будет выводить саму новость, а
позже наращивать поле VIEWS запросом UPDATE table ‘NEWS’ set
VIEWS=VIEWS+1 where id=ID. При всем этом количество апдейтов будет достаточно
высочайшим. При высочайшей посещаемости веб-ресурса либо при «нападении» на веб-сайт
поискового бота (эти ребята мучаются многопоточностью и могут просто
повесить ваш веб-сайт своими запросами) несколько одновременных процессов
станут пробовать сделать UPDATE и SELECT. При каждом UPDATE таблица будет
блокироваться (на это уходит время) и все другие процессы будут ожидать
окончания операции. А если таблица довольно большая? К примеру,
несколько тыщ записей. Ежу понятно, что построится очередь из нескольких
10-ов скриптов, ожидающих ответа MySQL-сервера. Каждый будет жрать
память и держать другие процессы. В конечном итоге все у вас зависнет и
переглючит. Выход: делать заместо одной таблицы несколько. Советую
делить поля по типу их использования. Одну таблицу – только для вывода
и редчайших обновлений либо вставок. Другую – для нередких обновлений, но
редчайшего вывода. К примеру, значения счетчика воззваний держать раздельно в
таблицу вида:

ID – номер, первичный ключ
VIEWS – количество просмотров

Сами анонсы лучше держать в другой таблице, где нет поля VIEWS. При
этом таблица с новостями будет тяжеленной (много текста, полей, индексов), а
таблица COUNT (счетчик) будет очень легкой и резвой. Таблица NEWS будет
кешироваться и выводиться очень стремительно при всех объемах, а таблица COUNT
будет стремительно обновляться из-за того, что она очень легкая (всего два
целочисленных поля). Разделение данных по нескольким таблицам значительно
ускоряет работу MySQL-сервера. Еще резвее работают несколько маленьких
запросов по каждой таблице, чем один длиннющий запрос по одной либо
нескольким таблицам. Имейте это в виду, чтоб спать тихо.

Далее – круче. Чтоб не перекрыть еще раз свои таблицы
используйте при вставках директиву DELAYED. Пример: INSERT DELAYED into
STAT (ID,IP,UTIME) values (null,$ip,NOW()). Он позволяет серверу
ответвлять поток в режиме ожидания, а саму вставку создавать тогда,
когда сервер освободится от других запросов либо поступит последующий
аналогичный INSERT DELAYED. Обычно отложенный способ подходит для всех
операций с кумулятивными таблицами (когда в главном идут INSERTы, а
данные копятся, а не модифицируются), при которых не особо принципиально когда
конкретно подействуют конфигурации – одномоментно либо через несколько секунд,
минут. К примеру, если желаете собирать IPадреса собственных гостей, УРЛы, по
которым они прогуливаются либо странички, откуда пришли, время. При добавлении с
задержкой скрипт отработает практически одномоментно, еще до выполнения операции.

Операция UPDATE идет в три шага: поиск того, что будете поменять, потом
запись данных, обновление индексов. При всем этом, чем больше таблица, тем
подольше поиск. Если есть индексы, то операция кешируется и производится
довольно стремительно. Но сам процесс очень вместительный. И только дурачина не догонит,
что большая таблица со обилием индексов и записей, будет тормозить при
UPDATE. INSERT же производится одним залпом, очень стремительно. Потому обычно
употребляют аддитивные записи (вставками INSERT) во временные таблицы,
позже заблокируют главные талицы, суммируют обновления, и плюют их в
основную таблицу. Выходит, что в главном, главные таблицы работают
исключительно в режиме вывода, а обновления идут еще пореже и резвее.
К примеру, можно накапливать данные о загрузках новостей во временной таблице, а
по крону либо другим образом обновлять счетчик каждые 10 минут (либо пореже).
Это ускорит работу сервера.

При запросах SELECT * FROM таблица скрипт получит все поля данной
таблицы. А необходимо ли это? Внедрение * ведет к излишнему расходу ресурсов.
Еще эффективнее использовать четкие наименования полей, которые необходимы
скрипту. К примеру: SELECT id,name FROM таблица. При таком запросе передача
займет меньше времени и пригодится меньше ресурсов. Пытайтесь
ограничивать вывод с помощью директивы LIMIT. Это также ускоряет вывод.

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

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

Используйте соответствующий тип полей для собственных записей. Тип TINYINT
занимает 1 б – самый резвый. Таблицы с MEDIUMINT резвее таблиц с INT.
Если ставить полям свойство NOT NULL, то в целом их работа будет резвее.
VARCHAR медленее CHAR, потому таблицы переменной длины (где есть тип
VARCHAR либо TEXT) занимают меньше дискового места, но работают
медлительнее.

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

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

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

Комментарии закрыты.