1 Особенности работы MySQL 5.0 и перспективы развития СУБД Михаил Серов (1234ru), инструктор авторизованного учебного центра MySQL в Москве Григорий Рубцов. - презентация

1 1 Особенности работы MySQL 5.0 и перспективы развития СУБД Михаил Серов (1234ru), инструктор авторизованного учебного центра MySQL в Москве Григорий Рубцов (rgbeast), руководитель авторизованного учебного центра MySQL в Москве

2 2 Развитие MySQL MySQL 4.1 Подзапросы Геометрические данные Поддержка Unicode Более быстрый протокол Новые хэши паролей Шифрование соединений SSL Кластер MySQL MySQL 5.0 Хранимые процедуры Представления Триггеры Курсоры Точные вычисления Information schema Поддержка расширяемой архитектуры Режим strict MySQL 5.1 Не зависящие от типа таблиц резервные копии Репликация со многими инициаторами Не зависящие от типа таблиц внешние ключи Построчный двоичный архив Глобальная репликация кластера NDB Хранение данных на диске в NDB Разбивка таблиц

3 3 Механизмы хранения (Storage Engines) MyISAM и MERGE –Быстрое выполнение запросов на чтение –FULLTEXT-индексы: быстрый поиск в текстовых данных –TABLE-LEVEL LOCKING MEMORY –Очень высокое быстродействие: хранение данных в ОЗУ InnoDB –Поддержка транзакций, ACID-стандарт –Поддержка внешних ключей (FOREIGN KEYS) –ROW-LEVEL LOCKING FEDERATED –Хранение данных на других MySQL-серверах NDBCluster –Разделение задач между различными частями кластера (нодами) –Высокая надежность и отказоустойчивость Возможно использование механизмов хранения от сторонних производителей или самостоятельная их разработка (механизм хранения EXAMPLE)

4 4 mysql>CREATE TABLE myisam_table ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(255), -> description TEXT CHARACTER SET utf8, -> FULLTEXT (description) -> ) -> ENGINE = MyISAM -> CHARACTER SET latin1 COLLATE latin1_general_ci; Query OK, 0 rows affected (0.00 sec) mysql>CREATE TABLE all_users ( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(255) NOT NULL DEFAULT '', -> VARCHAR(255) NOT NULL DEFAULT '', -> INDEX(id) -> ) -> ENGINE = MERGE -> UNION = (old_users, new_users) -> INSERT_METHOD = LAST; Query OK, 0 rows affected (0.01 sec)

5 5 mysql>CREATE TABLE memory_table ( -> id INT UNSIGNED AUTO_INCREMENT, -> value INT NOT NULL DEFAULT 0, -> name VARCHAR(255), -> PRIMARY KEY USING HASH (id), -> INDEX USING BTREE (value) -> ) ENGINE = MEMORY; Query OK, 0 rows affected (0.00 sec) mysql>CREATE TABLE innodb_parent ( -> id INT NOT NULL DEFAULT 0, -> parent_name VARCHAR(255), -> UNIQUE (id) -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql>TABLE innodb_child ( -> id INT NOT NULL DEFAULT 0, -> parent_id INT, -> child_name VARCHAR(255), -> UNIQUE (id), -> INDEX (parent_id), -> FOREIGN KEY (parent_id) -> REFERENCES innodb_parent (id) -> ON UPDATE CASCADE -> ON DELETE CASCADE -> ) ENGINE = InnoDB; Query OK, 0 rows affected (0.05 sec)

6 6 Триггеры Триггер – ассоциированный с таблицей объект, который активируется при совершении над этой таблицей определенных действий Триггер может активироваться при INSERT, UPDATE, DELETE, до или после совершения операции. Для одной и той же таблицы может быть только по одному триггеру одного вида, то есть не более 6 триггеров на одну таблицу. Внутри одного триггера можно совершать несколько действий Хотя триггер относится к определенной таблице, он является объектом базы данных

7 7 mysql>CREATE TABLE trades (summ INT, debt INT NOT NULL DEFAULT 0); Query OK, 0 rows affected (0.01 sec) mysql>DELIMITER $$ mysql>CREATE TRIGGER trades_bi -> BEFORE INSERT -> ON trades -> FOR EACH ROW -> BEGIN -> IF NEW.summ < 0 THEN -> SET NEW.debt = 1; -> END IF; -> END -> $$ Query OK, 0 rows affected (0.00 sec) mysql>DELIMITER ; mysql>INSERT INTO trades VALUES (100), (200), (500), (-10), (-50); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql>INSERT INTO trades (summ) VALUES (100), (200), (500), (-10), (-50); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql>SELECT * FROM trades; | summ | debt | | 100 | 0 | | 200 | 0 | | 500 | 0 | | -10 | 1 | | -50 | 1 | rows in set (0.00 sec) mysql>

8 8 Хранимые процедуры и функции Гибкое использование привилегий (директива SQL SECURITY) Управление действиями при ошибках (EXIT HANDLERы) Разделение логики между приложением-клиентом и MySQL- сервером, хранение однотипного часто используемого кода на сервере Процедуры возвращают результат запроса в виде таблицы Функции возвращают одно значение фиксированного типа Процедуры и функции используют разное пространство имен (т.е. возможно существование процедуры и функции с одним именем)

9 9 Использование процедур $$ PROCEDURE test.sort_values (INOUT var_value INT) -> SQL SECURITY INVOKER -> BEGIN -> DECLARE message VARCHAR(255); -> IF var_value > 0 THEN -> BEGIN -> INSERT INTO positive_values (value) VALUES (var_value); -> SET message = CONCAT(var_value, ' was positive; inserted into table positive_ relults'); -> END; -> ELSE -> BEGIN -> INSERT INTO positive_values (value) VALUES (var_value); -> SET message = CONCAT(var_value, ' was not positive; redirected to table posit ive_relults'); -> END; -> END IF; -> SELECT message AS 'message'; -> END; -> $$ Query OK, 0 rows affected (0.00 sec) EXECUTE ON PROCEDURE test.sort_values TO IDENTIFIED BY 'durak'$$ Query OK, 0 rows affected (0.00 sec)...

10 10 = 1; Query OK, 0 rows affected (0.00 sec) ivan>call | message | | 1 was positive; inserted into table positive_relults | row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) = -1; Query OK, 0 rows affected (0.01 sec) ivan>CALL | message | | -1 was not positive; redirected to table positive_relults | row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec)

11 11 Использование функций = 1; Query OK, 0 rows affected (0.00 sec) $$ FUNCTION daytime (dt DATETIME) -> RETURNS varchar(255) -> NOT DETERMINISTIC -> BEGIN -> DECLARE d INT; -> DECLARE daytime VARCHAR(255); -> SET d = CAST(HOUR(dt) AS UNSIGNED); -> CASE d -> WHEN (d >= 0 AND d < 10) THEN SET daytime = 'night'; -> WHEN (d >= 10 AND d < 14) THEN SET daytime = 'mourning'; -> WHEN (d >= 14 AND d < 20) THEN SET daytime = 'day'; -> WHEN (d >= 20 AND d < 24) THEN SET daytime = 'evening'; -> ELSE SET daytime = 'unknown time'; -> END CASE; -> RETURN daytime; -> END -> $$ Query OK, 0 rows affected (0.00 sec)

12 12 Использование функций test>GRANT SELECT ON test.posts TO Query OK, 0 rows affected (0.02 sec) test>GRANT EXECUTE ON FUNCTION test.daytime TO Query OK, 0 rows affected (0.00 sec)... ivan>SELECT COUNT(id) AS `число действий`, daytime(act_time) AS `время суток` FROM actions -> GROUP BY `время суток` -> ORDER BY `число действий` DESC; | число действий | время суток | | 4820 | ночь | | 340 | день | | 280 | вечер | | 67 | утро | rows in set (0.01 sec)