Триггеры — спасители

Уже много статей в интернете есть про sql триггеры, но добавлю еще одну с адекватными примерами, что бы закрепить материал для тех, кто «в теме» и что бы лучше понять материал тем, кто только начал постигать «дзен sql». Заодно и создам дискуссию по теме.

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

Из работы над ними вынесли один ценный опыт — следить за приоритетами и статистикой. Что это значит? Все просто: если у Вас блог и у него 2-3-4-10012 млн посетителей в сутки, а статьи пишутся всего 1-2-3-3435 раз в сутки (на порядок меньше чем число просмотров), то скорость сохранения статьи (и сложность этого) относительно скорости показа статьи может быть пропорционально меньше. Чем больше показываем, тем критичен именно показ, а не сохранение статьи/страницы/таблицы. Что не означает, что и расслабляться можно. Сохранение статьи за 3-5-10 секунд в блоге — это в рамках адекватности, но генерация страницы за срок более 2 секунды (+ пока скрипты и стили с картинками подгрузятся) — это на грани «какой тормознутый сайт, почитаю что-то иное», а еще хуже «пойду куплю в другом месте».

Если мы возьмем среднестатистический сайт с голосовалкой/кармой, комментариями, счетчиком показа страницы и т.п., то многим разработчикам сразу в голову приходят конструкции вроде SELECT count(*) FROM comment WHERE comment.page=page_id. Ну подумаешь на каждую статью посчитать сумму рейтинга, сумму комментариев. А, у нас на главной по 10 статей из каждого раздела. При посещаемости в 10 человек в секунду, на среднем VPS, можно себе позволить по 60-100 запросов к sql на страницу (привет, битрикс).

Но к черту лирику (достал уже, наверное). Голые данные:

Таблица blog

CREATE TABLE IF NOT EXISTS `blog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(128) NOT NULL,
  `text` text NOT NULL,
  `creation` datetime NOT NULL,
  `modification` datetime NOT NULL,
  `img` varchar(128) NOT NULL DEFAULT 'default.png',
  `status` tinyint(4) NOT NULL DEFAULT '2',
  `user_id` int(11) NOT NULL,
  `rate` int(11) NOT NULL,
  `relax_type` tinyint(4) NOT NULL,
  `timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `contest` tinyint(1) NOT NULL DEFAULT '0',
  `views` int(11) NOT NULL DEFAULT '0',
  `comment` int(11) NOT NULL,
  `url` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`),
  KEY `country_id` (`country_id`),
  KEY `user_id` (`user_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1456435 ;

Таблица comments

CREATE TABLE IF NOT EXISTS `comments` (
  `owner_name` varchar(50) NOT NULL,
  `owner_id` int(12) NOT NULL,
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `parent_id` int(12) DEFAULT NULL,
  `user_id` int(12) DEFAULT NULL,
  `text` text,
  `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `owner_name` (`owner_name`,`owner_id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=243254252 ;

Как видим, в таблице блога у каждой статьи есть счетчик комментариев (поле comment).
Обычная практика:
1. Добавили комментарий — увеличили счетчик для блога
2. Удалили/скрыли комментарий — уменьшили счетчик.
Делать это в коде удобно и привычно, но есть более удобный инструмент — триггеры.

И так, у нас есть 2 события (на самом деле 3): создание комментария и его удаление (третье событие — это изменение его статуса («удаление», бан и т. п.).
Рассмотрим только создание и удаление, а изменение статуса пусть будет домашним заданием.

В примере есть одна особенность: комментарии могут быть к нескольким типам статей.

Создание комментария:

CREATE TRIGGER `add_count_comment` AFTER INSERT ON `comments`
 FOR EACH ROW BEGIN

 // у пользователя в личном кабинете посчитаем сколько он комментариев написал
UPDATE user SET user.countcomment= user.countcomment+1 WHERE user.id = NEW.user_id;

// определяем к чему относится комментарий и сразу увеличиваем счетчик в данных таблицах
CASE NEW.`owner_name`
WHEN 'Blog' THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`+1 WHERE `blog`.id = NEW.`owner_id` ;
WHEN 'Article' THEN UPDATE `article` SET `article`.`comment` = `article`.`comment`+1 WHERE `article`.`id` = NEW.`owner_id` ;
WHEN 'PopulatePlace' THEN UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`+1 WHERE `populate_place`.`id` = NEW.`owner_id` ;
END CASE; 

// тут мы облегчаем себе работу с лентами новостей
// url статьи сразу пишем, что бы ПОТОМ не делать выборок лишних
CASE NEW.`owner_name`
WHEN 'Blog' THEN SET userurl = (SELECT url FROM `blog` WHERE `blog`.id= NEW.`owner_id`);
WHEN 'Article' THEN SET userurl = (SELECT url FROM `article` WHERE article.id=NEW.`owner_id`);
WHEN 'PopulatePlace' THEN SET userurl = ``;
END CASE;   

// название статьи сразу пишем, что бы ПОТОМ не делать выборку
CASE NEW.`owner_name`
WHEN 'Blog' THEN SET usertitle = (select title from `blog` where blog.id=NEW.`owner_id`);
WHEN 'Article' THEN SET usertitle = (select title from `article` where article.id=NEW.`owner_id`);
WHEN 'PopulatePlace' THEN SET usertitle = ` `;
END CASE;   

INSERT INTO user_has_events VALUES (NEW.user_id,NEW.id,"Comments",NOW(),userurl , usertitle );

END

Аналогично и удаление комментария:

CREATE TRIGGER `del_count_comment` AFTER DELETE ON `comments`
 FOR EACH ROW BEGIN
UPDATE user SET user.countcomment= user.countcomment -1 WHERE user.id = OLD.user_id;

CASE OLD.`owner_name`
WHEN 'Blog' THEN UPDATE `blog` SET `blog`.`comment` = `blog`.`comment`-1 WHERE `blog`.`id` = OLD.`owner_id` ;
WHEN 'Article' THEN  UPDATE `article` SET `article`.`comment` = `article`.`comment`-1 WHERE `article`.`id` = OLD.`owner_id` ;
WHEN 'PopulatePlace' THEN  UPDATE `populate_place` SET `populate_place`.`comment` = `populate_place`.`comment`-1 WHERE `populate_place`.`id` = OLD.`owner_id` ;
END CASE; 

END

И так, что получили:
1. При вставке комментария у нас автоматически средствами sql сервера посчиталась сумма комментариев у конкретного объекта комментирования (статья, страница, заметка)
2. Мы сформировали ленту новостей (привет всем соцсетям и т. п.)
3. При удалении комментария у нас происходит вычет всех данных.
4. Мы не использовали средства фреймворка.
5. Выборка всех нужных данных происходит быстро (всего 1 запрос при показе страницы, за исключением прочих «левых» данных на ней.)

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

Добавлен триггер:

CREATE TRIGGER `ins_blog` BEFORE INSERT ON `blog` 
// делаем вставку времени до сохранения информации путем «подмены» данных.
 FOR EACH ROW BEGIN 
SET NEW.modification = NOW();
END

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

Забыл :)

CREATE TRIGGER `ins_blog` BEFORE UPDATE ON `blog` 
// делаем вставку времени до сохранения информации путем «подмены» данных.
 FOR EACH ROW BEGIN 
SET NEW.modification = NOW();
END

При изменении данных — обновим поисковый индекс тоже.

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

Автор: psman