* Събития в MySQL
Публикувано на 03 април 2012 в раздел Бази от Данни.
„Събитията“ (events) в MySQL ни позволяват да изпълняваме процедури отложено във времето. Те може да са регулярни, еднократни или ограничени в даден интервал от време. Основната полза от тях идва тогава, когато искаме да извършваме операции, които не са жизненоважни за функционирането на системата „в момента на изпълнение“. Такива може да са заявки за обобщение на данни (например изготвяне на статистически отчет в края на деня) или такава промяна на данни, която е хубаво да бъде правена, но не е съществено необходима за функционирането на системата и ако я правим всеки път, то бихме забавили излишно приложението.
Събитията в MySQL се контролират от специална нишка, която работи паралелно с останалите. Преди да започнете да работите със събития трябва да проверите дали тя е включена (по подразбиране не е). Това се контролира от глобалната променлива „event_scheduler“. Ако нишката не е стартирана, то MySQL ще приема заявките ви за създаване на събития, но те няма да се изпълняват. За да проверите стойността на променливата напишете следната команда:
mysql> SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | OFF | +-------------------+ 1 row in set (0.00 sec)
За да я включите трябва да я промените на „ON“:
mysql> SET GLOBAL event_scheduler = "ON"; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@event_scheduler; +-------------------+ | @@event_scheduler | +-------------------+ | ON | +-------------------+ 1 row in set (0.00 sec)
Ако искате да го включите автоматично при рестартиране на MySQL добавете в my.cnf „event_scheduler = on“. Може да проверите, че нишката е стартирана разглеждайки списъка с процесите:
mysql> SHOW PROCESSLIST\G
*************************** 1. row
Id: 1
User: root
Host: localhost:9737
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
*************************** 2. row
Id: 2
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 74
State: Waiting on empty queue
Info: NULL
2 rows in set (0.00 sec)
Първият процес в списъка е този, който самите ние сме стартирали отваряйки връзка с потребител root към СУБД, а втория е въпросния „event scheduler“.
За да създадете събитие трябва да използвате командата CREATE EVENT. В най-елементарния си вариант тя има следния синтаксис:
CREATE EVENT <име> ON SCHEDULE <график> DO <коматди>
Пример 1: Нека демонстрираме с класически пример. Нека имаме база от данни, в която съхраняваме публикации и записи за влизанията в тях (log). За всяка публикация искаме да пазим статистика за уникалните посещения (visits), която се калкулира на база на записите за влизанията:
CREATE TABLE articles(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
contents TEXT NOT NULL,
visits INT UNSIGNED NOT NULL
)ENGINE=InnoDB CHARACTER SET=utf8;
INSERT INTO articles(title, contents, visits)
VALUES ("Статия 1", "Някакво съдържание...", 0),
("Статия 2", "Друго съдържание...", 0);
CREATE TABLE logs(
article_id SMALLINT UNSIGNED,
FOREIGN KEY (article_id) REFERENCES articles(id),
ip INT UNSIGNED NOT NULL,
hits INT NOT NULL,
PRIMARY KEY(article_id,ip)
) ENGINE=InnoDB CHARACTER SET=ascii;
Когато потребителите започнат да четат дадени статии, то ние ще взимаме IP адреса на всеки един от тях и ще правим запис в таблицата logs за съответното посещение.
Сега остава да разрешим проблема със съответствието на hits в таблицата logs и visits в таблицата articles. Разбира се вече знаем как да създадем тригер, който при добавяне на нов запис в таблицата logs ще обновява стойността на visits в таблицата articles. Ако обаче нашия сайт е достатъчно много натоварен (например имаме по няколко посещения в секунда) това може да се окаже неприятна операция (забележете, че при използване на InnoDB всяка UPDATE заявка сама по себе си е трансакция, т.е. имаме и заключване на данните). Разбира се ние можем напълно да се откажем от колона visits и всеки път когато даваме информацията на потребителите ни да си калкулираме стойността динамично, но това също не ни харесва от гледна точка на производителност.
Именно тук можем да използваме събитията – ще калкулираме статистиката за visits например веднъж на минута. Така потребителя няма да вижда най-актуалната статистика за посещенията към текущия момент, в който чете дадената статия, но в най-лошия случай ще вижда данните най-много от преди минута. За целта можем да добавим следното събитие:
CREATE EVENT visits_update
ON SCHEDULE EVERY 1 MINUTE
DO UPDATE articles
SET visits = ( SELECT COUNT(*)
FROM logs
WHERE logs.article_id = articles.id
);
Сега да вмъкнем примерни данни в logs:
INSERT INTO logs(article_id, ip, hits)
VALUES (1, INET_ATON('1.1.1.1'), 1),
(2, INET_ATON('1.1.1.1'), 1),
(1, INET_ATON('2.2.2.2'), 1),
(1, INET_ATON('3.3.3.3'), 1),
(1, INET_ATON('1.1.1.1'), 1),
(2, INET_ATON('2.2.2.2'), 1),
(2, INET_ATON('3.3.3.3'), 1),
(2, INET_ATON('3.3.3.3'), 1),
(2, INET_ATON('3.3.3.3'), 1),
(1, INET_ATON('4.4.4.4'), 1)
ON DUPLICATE KEY UPDATE hits=hits+1;
Първоначално ще видим, че visits не е обновено:
SELECT * FROM articles; +----+----------+-----------------------+--------+ | id | title | contents | visits | +----+----------+-----------------------+--------+ | 1 | Статия 1 | Някакво съдържание... | 0 | | 2 | Статия 2 | Друго съдържание... | 0 | +----+----------+-----------------------+--------+ 2 rows in set (0.00 sec)
Ако обаче почакаме една минута, то ще видим, че visits ще се обнови:
SELECT * FROM articles; +----+----------+-----------------------+--------+ | id | title | contents | visits | +----+----------+-----------------------+--------+ | 1 | Статия 1 | Някакво съдържание... | 4 | | 2 | Статия 2 | Друго съдържание... | 3 | +----+----------+-----------------------+--------+ 2 rows in set (0.00 sec)
Можем ли да помислим за някаква оптимизация? Разбира се, че може. Защо всеки път обновяваме посещенията пресмятайки броя на всички записи в таблицата, а не просто да добавяме броя само на новите такива? Ще го направим, като от тук нататък ще разглеждаме таблицата logs като „дневни записи“ (т.е. ще записваме TIME). Естествено не е проблем да се направят и „месечни“ или „вечни“:
DROP EVENT visits_update; TRUNCATE TABLE logs; ALTER TABLE articles ADD COLUMN last_visits_update DATETIME NOT NULL; UPDATE articles SET last_visits_update = NOW(), visits = 0; ALTER TABLE logs ADD COLUMN at_time TIME NOT NULL;
Изтрихме записите в logs и добавихме нови колони – последно обновяване в articles и време на първото посещение на даден IP адрес в logs. Сега пренаписваме събитието така, че да удовлетворява новото условие:
CREATE EVENT visits_update
ON SCHEDULE EVERY 1 MINUTE
DO UPDATE articles
SET visits = visits +
( SELECT COUNT(*)
FROM logs
WHERE logs.article_id = articles.id
AND
logs.at_time > TIME(articles.last_visits_update)
),
last_visits_update = NOW();
Добавяме новите записи:
INSERT INTO logs(article_id, ip, hits, at_time)
VALUES (1, INET_ATON('1.1.1.1'), 1, CURTIME()),
(2, INET_ATON('1.1.1.1'), 1, CURTIME()),
(1, INET_ATON('2.2.2.2'), 1, CURTIME()),
(1, INET_ATON('3.3.3.3'), 1, CURTIME()),
(1, INET_ATON('1.1.1.1'), 1, CURTIME()),
(2, INET_ATON('2.2.2.2'), 1, CURTIME()),
(2, INET_ATON('3.3.3.3'), 1, CURTIME()),
(2, INET_ATON('3.3.3.3'), 1, CURTIME()),
(2, INET_ATON('3.3.3.3'), 1, CURTIME()),
(1, INET_ATON('4.4.4.4'), 1, CURTIME())
ON DUPLICATE KEY UPDATE hits=hits+1;
… и проверяваме. Първоначално статистиката няма да е обновена:
SELECT * FROM articles; +----+----------+-----------------------+--------+---------------------+ | id | title | contents | visits | last_visits_update | +----+----------+-----------------------+--------+---------------------+ | 1 | Статия 1 | Някакво съдържание... | 0 | 2012-04-03 15:18:45 | | 2 | Статия 2 | Друго съдържание... | 0 | 2012-04-03 15:18:45 | +----+----------+-----------------------+--------+---------------------+ 2 rows in set (0.00 sec)
… но след една минута вече ще бъде:
SELECT * FROM articles; +----+----------+-----------------------+--------+---------------------+ | id | title | contents | visits | last_visits_update | +----+----------+-----------------------+--------+---------------------+ | 1 | Статия 1 | Някакво съдържание... | 4 | 2012-04-03 15:19:45 | | 2 | Статия 2 | Друго съдържание... | 3 | 2012-04-03 15:19:45 | +----+----------+-----------------------+--------+---------------------+ 2 rows in set (0.00 sec)
Ако впоследствие добавим нови записи със съществуващи стари (не трябва да обновява visits) и нови (трябва да обнови visits) IP адреси:
INSERT INTO logs(article_id, ip, hits, at_time)
VALUES (1, INET_ATON('5.5.5.5'), 1, CURTIME()),
(1, INET_ATON('1.1.1.1'), 1, CURTIME()),
(1, INET_ATON('6.6.6.6'), 1, CURTIME())
ON DUPLICATE KEY UPDATE hits=hits+1;
… ще видим, че обновяването е коректно в обхвата на действие на събитието:
SELECT * FROM articles; +----+----------+-----------------------+--------+---------------------+ | id | title | contents | visits | last_visits_update | +----+----------+-----------------------+--------+---------------------+ | 1 | Статия 1 | Някакво съдържание... | 4 | 2012-04-03 15:23:45 | | 2 | Статия 2 | Друго съдържание... | 3 | 2012-04-03 15:23:45 | +----+----------+-----------------------+--------+---------------------+ 2 rows in set (0.00 sec) -- след известно време... SELECT * FROM articles; +----+----------+-----------------------+--------+---------------------+ | id | title | contents | visits | last_visits_update | +----+----------+-----------------------+--------+---------------------+ | 1 | Статия 1 | Някакво съдържание... | 6 | 2012-04-03 15:28:45 | | 2 | Статия 2 | Друго съдържание... | 3 | 2012-04-03 15:28:45 | +----+----------+-----------------------+--------+---------------------+ 2 rows in set (0.00 sec)
Така пазим повече данни в таблиците (датите и времената), но за сметка на това можем да си позволим изтриване на стари логове, без това да наврежда на „visits“.
Накрая да не забравим да направим събитие, което ще изчиства logs таблицата в края на всеки ден (в нея пазим колона „TIME“, т.е. тя ще е актуална само в рамките на текущия ден):
CREATE EVENT truncate_logs ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP '2012-04-03 00:00:00' DO TRUNCATE TABLE logs;
Както виждате можем да правим събитията регулярни като „интервал от … започвайки от дадено време“. Именно затова се използва ключовата дума STARTS. Ако я нямаше, то събитието щеше да започне от текущата дата. Аналогично можем да задаваме крайна дата на събитие чрез ключова дума ENDS.
Пример 2: Ще създадем база данни със служители (employees), клиенти (customers) и продукти (products):
CREATE TABLE employees(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
)ENGINE = InnoDB;
INSERT INTO employees(name)
VALUES ("Petar"), ("Ivan"), ("Maria");
CREATE TABLE customers(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
)ENGINE = InnoDB;
INSERT INTO customers(name)
VALUES ("Todor"), ("Mihail"), ("Angelina"), ("Mariela");
CREATE TABLE products(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(5,2) NOT NULL
)ENGINE = InnoDB;
INSERT INTO products(name, price)
VALUES ("Отверки", 6.50), ("Чукове", 12.80);
Ще създадем таблица „дневни продажби“ (dailysales), в която ще записваме кой служител на кой клиент какво е продал и в какво количество за даден ден:
CREATE TABLE dailysales(
employee_id INT UNSIGNED NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees(id),
customer_id INT UNSIGNED NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id),
product_id INT UNSIGNED NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(id),
quantity SMALLINT NOT NULL,
at_date DATE NOT NULL,
PRIMARY KEY(employee_id, customer_id, product_id, at_date)
)ENGINE=InnoDB;
INSERT INTO dailysales(employee_id, customer_id, product_id,
quantity, at_date)
VALUES (1, 3, 1, 3, 2012-04-02),
(1, 3, 2, 1, 2012-04-02),
(2, 1, 1, 4, 2012-04-02),
(2, 2, 1, 1, 2012-04-02),
(1, 1, 2, 1, 2012-04-03),
(3, 4, 2, 1, 2012-04-03);
Сега искаме да направим таблица с обобщение за приходите получени от даден служител за даден ден. Тя може да изглежда по следния начин:
CREATE TABLE employees_income( employee_id INT UNSIGNED NOT NULL, FOREIGN KEY (employee_id) REFERENCES employees(id), at_date DATE NOT NULL, total_income DECIMAL(6,2), PRIMARY KEY(employee_id, at_date) )ENGINE=InnoDB;
И да създадем регулярно дневно събитие, което ще обновява информацията в тази таблица:
CREATE EVENT daily_income_stats
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP '2012-04-03 00:01:30'
DO
INSERT IGNORE
INTO employees_income(employee_id, at_date, total_income)
SELECT dailysales.employee_id, dailysales.at_date,
SUM(dailysales.quantity * products.price)
FROM dailysales JOIN products
ON dailysales.product_id = products.id
GROUP BY dailysales.employee_id, dailysales.at_date;
Правим проверка:
SELECT * FROM employees_income; Empty set (0.00 sec) -- на следващия ден (в нашия случай 04-04) -- събитието ще изчисли всичко SELECT * FROM employees_income; +-------------+------------+--------------+ | employee_id | at_date | total_income | +-------------+------------+--------------+ | 1 | 2012-04-02 | 32.30 | | 1 | 2012-04-03 | 12.80 | | 2 | 2012-04-02 | 32.50 | | 3 | 2012-04-03 | 12.80 | +-------------+------------+--------------+ 4 rows in set (0.00 sec)
Можем да се досетим, че събитието не е направено оптимално. С всеки следващ ден ще имаме все повече и повече дублиращи се редове, които ще се пропускат (използваме INSERT IGNORE… заявка). Ние обаче всеки път пресмятаме сумата на парите за всеки един от тях! Затова горното събитие не е добро – с напредване на времето то ще работи все по-бавно и по-бавно. Затова можем да я оптимизираме така, че да пресмята събитията само от предишния ден, а другите (вече изчислени) да не ги изчислява отново:
DROP EVENT daily_income_stats;
DELETE FROM employees_income;
CREATE EVENT daily_income_stats
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP '2012-04-03 00:01:30'
DO
INSERT INTO employees_income(employee_id, at_date, total_income)
SELECT dailysales.employee_id,
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY),
SUM(dailysales.quantity * products.price)
FROM dailysales JOIN products ON dailysales.product_id = products.id
WHERE dailysales.at_date = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY dailysales.employee_id, dailysales.at_date;
Проверка:
SELECT * FROM employees_income; Empty set (0.00 sec) -- на следващия ден (в нашия случай 04-04) -- събитието ще вмъкне общите приходи -- само за 04-03 (предишния ден) SELECT * FROM employees_income; +-------------+------------+--------------+ | employee_id | at_date | total_income | +-------------+------------+--------------+ | 1 | 2012-04-03 | 12.80 | | 3 | 2012-04-03 | 12.80 | +-------------+------------+--------------+ 4 rows in set (0.00 sec)
В случая се възползвахме от функцията DATE_SUB, която връща разликана между две дати. По-конкретно DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) ни дава „вчера“.
* Нарочно стартирахме събитието в 00:01:30, а не в 00:00:00. Не е добра идея да създавате събития, които се изпълняват точно в полунощ, защото стандартно много други приложения може правят други такива и по този начин да създавате излишен „час пик“ за сървъра. Винаги гледайте събитията ви да се разминават във времето.
Задача. В пример 2 ако някой служител не е направил никакви продажби в даден ден, то той ще липсва в отчета за него. Направете така, че id на такъв служител да се добавя, а неговия total_income да бъде или 0 или NULL (по ваш избор).
Добави коментар