Памятка разработчику MySQL (стандарт)

Автор: Максим Нечаев

Общее

При разработке веб приложений с использованием СУБД MySQL необходимо учитывать следующее: приложение отвечает на запросы пользователя, и чем быстрее будет сформирован ответ, тем лучше. Поэтому необходимо:
  1. По возможности, минимизировать обращения программы к серверу MySQL. С каждым запросом на сервере баз данных происходит синтаксический анализ, построение плана выполнения, исполнение. Чем меньше будет таких обращений, тем быстрее будет работать приложение. Например, если нужно получить какую-либо информацию для ряда объектов, то предпочтительнее выполнить один запрос для получения этой информации сразу для всех объектов, чем несколько запросов для каждого объекта в отдельности.
  2. Дизайн СУБД должен быть оптимальным для решаемой задачи.
    • Общие обязательные моменты:
      1. Данные должны находится, не менее, чем в 4-ой нормальной форме.
      2. У любой таблицы должно присутствовать поле, выполняющее одну единственную роль – первичный ключ. Это должен быть целочисленный беззнаковый счетчик: UNSIGNED AUTO_INCREMENT PRIMARY KEY. Значение первичного ключа для конкретной записи не должно меняться на протяжении всего времени существования этой записи.
      3. Если одно или несколько полей должны содержать неповторяющиеся значения, то по этим полям должен быть создан уникальный индекс.
    • Необязательные, но желательные моменты:
      1. Как правило, значения столбцов внешних ключей должны быть проиндексированны, т.к. скорее всего по этому полю будет происходить объединение таблиц в запросах. Но нельзя создавать неиспользующиеся индексы, т.к. они несут накладные расходы при модификации данных.
      2. Всегда, когда можно использовать первичный ключ для выборки записей из таблицы, его использовать нужно.
  3. Оптимизация запросов. Если не обращать внимания на скорость работы запросов, то разработчик и пользователи не только веб-приложения, но и любой программы скоро увидят существенное замедление её работы. Нужно максимально использовать возможности, предоставляемые MySQL, т.к. всё равно нельзя написать программу, одинаково хорошо работающую с несколькими СУБД, от мнимой совместимости лучше отказаться сразу. Поэтому можно и нужно:
    1. Изучить главу об оптимизации из руководства по MySQL1.
    2. Использовать оператор EXPLAIN2 для изучения плана выполнения запроса и индексацию полей для улучшения этого плана.
    3. По возможности, использовать оператор INSERT DELAYED3 для отложенной вставки данных, особенно при частом добавлении в таблицу. Такая ситуация, например, встречается при ведении какого-либо журнала.
    4. С осторожностью(!) работать с таблицами, содержащими большие объёмы данных – от 1 Гб и выше.
    5. Использовать оператор OPTIMIZE TABLE4 после удаления больших объёмов данных из таблицы.
    6. Включить журналирование медленных запросов5 и периодически изучать этот журнал.
  4. Стараться производить большие вычисления в более редко выполняемых задачах, чем в более частых. Например, отображение сообщения в форуме происходит чаще, чем его удаление или добавление нового сообщения. Соотверственно вычисление количества сообщений можно производить в момент изменения их численности, а при отображении сообщений выводить уже вычисленное значение счётчика. Как видно из примера, ради повышения производительности иногда даже идут на небольшие нарушения нормированности данных, вводя изыточное хранение вычислимых величин, таких как счётчики и т.п.
  5. Вынос долгих вычислений за пределы обработчика запросов. Если действия, описанные в предыдущем пункте применить нельзя или их оказывается недостаточно, то тогда можно разместить трудоёмкие вычисления во внешних скриптах, вызывая их периодически или при наступлении некоторого события.
  6. Учитывать, что работа веб приложения ведётся в многопользовательской среде:
    1. Нельзя надолго блокировать какую-либо таблицу. Это может привести выстраиванию очереди клиентов, ожидающих право доступа к таблице и исчерпанию максимального количества соединений с MySQL.
    2. Между чтением и модификаций данных другой клиент может их прочитать, т.е. у него оказываются устаревшие данные. Более того другой клиент может уже обновить данные, и это значит, что после модификации будут потеряны изменения другого клиента.

Наименование объектов

  1. Название таблицы представляет собой английское существительное в единственном числе, записанное с заглавной буквы. Название таблицы обозначает сущности, которые будут храниться в этой таблице и должно быть максимально информативным. Например: `User`.
  2. У таблиц, логически связанных друг с другом в качестве префикса можно указать название этой связующей сущности. Например, можно дать такие имена таблицам, обслуживающим новостную систему: `NewsCategory`, `NewsAuthor` и т.д.
  3. Название поля состоит из:
    • 2-3 буквенного префикса, обозначающего тип поля (см. ниже);
    • названия аттрибута сущности
  4. Имя первичного ключа таблицы составляется из префикса «id» и названия таблицы (без префикса таблицы). Например для таблицы `User` название первичного ключа `idUser`.
  5. Внешний ключ, размещенный в другой таблице и если в ней он один, должнен называться также, как первичный ключ в таблице, на которую он ссылается.

#Prefixes Рекомендуемые префиксы:
Префикс Тип поля
id ключевое
chr текстовое, фиксированной длины
txt текстовое, переменной длины
dt дата, время
int целочисленное
db l числовое, с плавающей точкой
log логическое
cur денежное
lst список значений

Построение запросов

  1. Все ключевые слова и операторы языка SQL пишутся заглавными буквами.
  2. Названия таблиц и полей таблиц всегда должны заключатся в апострофы. Например `User` или `Right`. Такой синтаксис позволяет:
    • Иметь названия полей и таблиц, совпадающие с зарезервированными словами7 MySQL.
    • Не опасаться, что в будующей версии СУБД какое-либо используемое название станет зарезервированным словом.
  3. Обращение к полям таблицы всегда должно идти с указанием имени таблицы, к которой это поле относится. Например `User`.`logEnabled` вместо просто `logEnabled`. В запросах, использующих несколько таблиц это даёт:
    • Однозначное понимание к какой именно таблице относится поле.
    • Позволяет избежать ошибок неодназначности в существующих запросах, при добавлении одноименного поля в другую таблицу.

-- VitalyDruzhinin - 2012-11-05
Topic revision: r1 - 2013-08-28, VitalyDruzhinin
 

This site is powered by FoswikiCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding Foswiki? Send feedback