×Закрыть

Полезные фичи MySQL

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

Повелитель CHAR

Если есть возможность, используем поле CHAR для текстовых полей. И искать будет быстрее, и защита от дурака будет. Так, например, для MD5-хэша пароля это CHAR(32), для тикера валюты (USD, EUR) — CHAR(3). Есть ещё масса примеров: если ваше приложение работает с данными по аэропортам, то кандидатом на тип CHAR будет ICAO-код аэропорта (4 символа) или IATA-код (3 символа), если с банками, то код BIC.

Приручаем TIMESTAMP

Часто требуется хранить дату создания и/или модификации сущности (поля stamp_created и stamp_updated). Не все пользуются фреймворками типа Symfony, где система сама отвечает за их наполнение — и так как порой их актуальность обеспечивается вручную, были случаи, когда эти поля оставались просто пустыми — некогда было возиться. Можно объявить поле так, что этот функционал будет работать сам. Правда, в случае MySQL придётся выбирать: автоматически будет работать либо дата создания, либо дата модификации. Для этого нужно создать поле типа TIMESTAMP; в первом случае (created) указываем инициализацию текущим временем, во втором (updated) — указываем авто-обновление поля при каждой модификации текущей записи. Оба варианта умеет делать PHPMyAdmin.

Каскады FOREIGN KEY

Конечно, это касается не только MySQL. Удаление данных в иерархии сущностей можно автоматизировать с помощью каскадного удаления FOREIGN KEY (да, это банально, но часто на это кладут). Например, у меня в Rival Alert есть пользователи, у пользователей есть графики, у графиков есть данные. Без FOREIGN KEY функция удаления пользователя должна сначала удалить все данные по графикам этого пользователя, потом все его графики, и только потом — самого юзера. При использовании FOREIGN KEY вся соответствующая информация удалится сама, причем логикой на стороне сервера БД, и без дополнительных запросов от сервера приложений.

Кстати, FOREIGN KEY поддерживаются только в InnoDB-движке. Перейдя на него, вы получите возможность использовать транзакции, но потеряете полно-текстовый поиск (он в MyISAM).

Есть ещё идейка, которую держу про запас. В той же «Building Scalable Web Sites» пишут, что для ускорения работы приложения базу данных можно немножко де-нормализовать, например, рейтинги статей считать не налету на каждый запрос, а держать в отдельном поле таблицы статей уже в посчитанном виде и время от времени обновлять, ну или скажем вам нужно дублировать название/ссылку статьи в каждой записи рейтинга. Так вот идейка состоит в том, чтобы использовать CASCADE UPDATE для обновления полей в зависимой таблице — тогда целостность данных при такой денормализации будет выше.

INSERT + UPDATE в одном запросе

Частый кейс: если нет такого записи — вставить (INSERT), если есть — обновить для неё пару полей (UPDATE). Часто это решается через предварительный SELECT, чтобы установить факт наличия такой записи. Можно сделать это одним запросом, лишь бы был PRIMARY KEY или UNIQUE KEY.

Приведу пример. В том же Rival Alert у меня у одного графика за один день может быть только одно значение (такое вот условие). Сколько раз в базу будет класться это значение — не важно. Так вот, если значения «за сегодня» нет — мы его добавляем, если есть — обновляем (в поле `date` хранится текущая дата; пара `id_graph`+`date` — уникальна для каждой записи, что было указано через UNIQUE при создании таблицы).

INSERT INTO `data` (`id_graph`, `date`, `value`)
VALUES (1, NOW(), 4444)
ON DUPLICATE KEY UPDATE `value`=4444;
Кстати, чтобы запрос стал красивее, и вам не нужно было два раза указывать значение вставки/обновления (в моём примере — это 4444), можно в разделе UPDATE указать, что нужно взять значение из раздела INSERT:
INSERT INTO `data` (`id_graph`, `date`, `value`)
VALUES (1, NOW(), 4444)
ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);
Оба запроса делают то же самое, только теперь вам нужно будет лишь в одном месте подставлять фактическое значение, а не в нескольких.

И последнее. Если вам нужно работать по сути с одними и теми же данными, но из разных баз данных, посмотрите в сторону Federated Storage Engine. Полезно иметь такую фичу на примете.

Надеюсь, эта заметка поможет вам кода писать меньше, а успевать больше.

LinkedIn

17 комментариев

Подписаться на комментарииОтписаться от комментариев Комментарии могут оставлять только пользователи с подтвержденными аккаунтами.

Хранимые процедуры, триггера, генерация эксепшинов в MySQLhttp://illya-keeplearning.blog...

Часто требуется хранить дату создания и/или модификации сущности

Еще можно использовать триггеры. Триггер на insert, триггер на update — и вся функциональность обеспечивается автоматически самим сервером (требуется минимум MySQL 5.1). Кроме того, в этом случае можно использовать не timestamp, a datetime или любой другой тип данных.Как-то так: delimiter $$create trigger piece_ins before insert on piece for each row begin if isnull (new.date_created) then set new.date_created = now (); end if; end$$delimiter;

Відколи це Оракл став блокіровочніком? Поки дані не закомічені поточною сесією, інші сесії «бачать» старі версії цих даних. MS SQL раніше був блокіровочніком, але з появою в 2005-й версії SNAPSHOT ISOLATION я б назвав його гібридом.

Разве Oracle не версионник?

P.S. І я б не ставив в один ряд MS SQL та FB (IMHO).

А их и нельзя ставить. Есть две группы SQL-серверов: т.н. “блокировочники” (например, M$ SQL, Oracle) и т.н. “версионники” (InterBase, Firebird, PostgreSQL). И философия работы с ними разная в виду поведения сервера. “Версионник” можно отстроить на уровне транзакций так, чтобы он вел себя как “блокировочник” (например, те же параметры транзакций Firebird и PostgreSQL), но смысла в этом особого нет в виду большого количества других вкусностей “версионника”.

когда то довно у себя на сайте выкладывал подборку советов с различных сайтов.http://www.lyabah.com/? cat=103

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

я не уверен, что так можно что-то выиграть. как правило, в OLTP денормализация используется для разбиения одной тяжелой транзакции на несколько мелких — так, чтобы основная транзакция выполнялась быстрее, а остальные — параллельно или с задержкой. в случае с CASCADE UPDATE такого не происходит — у нас все равно остается одна большая транзакция (непрозрачная для пользователя), да еще и приходится обновлять больше данных, чем в нормализованной схеме.

2 Александр: >> В отличие от MySQL в FB возможна блокировка на уровне записей. Кто в этом понимает, думаю, это его впечатлит.Я не фанат MySQL, но в нього також “возможна блокировка на уровне записей” (InnoDB).P.S. І я б не ставив в один ряд MS SQL та FB (IMHO).

Кроме вышеперечисленного активно юзаем еще 1) SQL_CALC_FOUND_ROWS — для построения табличных данных.Есть недостаток конечно — при больших данных зараза до конца всю выборку досчитывает и при больших объемах строк в таблицах уже не применима.2) insert delayed — для логов самое оно

Александр, в MySQL (InnoDB) тоже есть row-level locking, поэтому сей факт не может использоваться как аргумент в пользу других СУБД.

В общем пользуйтесь Firebird и таких проблем вообще не возникнет. По архитектуре FB близко к Oracle и PostgreSQL (это вам не вшивый MS SQL). Для небольших баз по 10−50 Гб и 100−500 одновременных коннектов Firebird просто идеальна.В отличие от MySQL в FB возможна блокировка на уровне записей. Кто в этом понимает, думаю, это его впечатлит.

Про CHAR: "И искать будет быстрее, и защита от дурака будет.«1. О какой защите от дурака идет речь? 2. Быстрее или не быстрее — зависит от реализации. И, теоретически, если в строке есть хотя бы одно поле с нефиксированным размером — преимущество CHAR теряется. А еще спорно, что быстрее: считать CHAR-значение и отбросить лишние пробелы справа, или найти правую границу VARCHAR-а в низкоуровневом хранилище. Так что все зависит от конкретной реализации, а заявления вроде «это быстрее того» незакрепленные пруфлинком — мифы.Про TIMESTAMP: Только первое и единственное (в таблице) поле типа TIMESTAMP может обладать описанной функциональностью, поэтому это никак не поможет реализовать связку created_at/updated_at на уровне БД.Вспомню про что-то еще «в тему» — отпишусь:)

А вот еще одна полезняшка: вместо DATE_ADD (my_date_field, INTERVAL 3 DAY) можно писать my_date_field + INTERVAL 3 DAY.Читайте мануалы, там много полезного можно вычитать.

Ну, в каком-то смысле это альтернатива связке INSERT + UPDATE

Есть еще прикольная функция REPLACE INTO

Правда пользуешься? Это ж DELETE+INSERT... Мне ни разу не пригодилась сама по себе.

Есть еще прикольная функция REPLACE INTO

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

Подписаться на комментарии