С MySQL 5.5 является типом по умолчанию. Таблицы такого типа имеют большую производительность, хранение данных в таблицах до 1Тб и Нагрузкой на сервер до 800 обновлений в секунду. Особенности: 1) хранит все таблицы в одном файле, поэтому имена таблиц должны быть уникальны 2) поддерживает компактное хранение данных в одном файле - это позволяет снять ограничение на объём таблиц. 3) таблицы поддерживают автоматическое восстановление после сбоя 4) обеспечивается поддержка транзакций 5) единственный тип таблиц, поддерживающий внешние ключи и каскадное удаление Итог: таблицы типа inno db надёжнее MyISAM, но заметно уступают в скорости
Тип таблиц MySQL: NDB Cluster
Кластеризация – разделение БД по нескольким серверам. Раньше целью кластеризации было создание высоконадежной и отказоустойчивой системы, а теперь вместе с требованиями надежности все чаще преследуются цели распределения нагрузки по серверам.Тип таблиц NDBCluster предназначен для организации кластеров MySQL, в случае когда таблицы распределены между несколькими компьютерами, объединенными в сеть.
Формат CSV
CSV — текстовый формат, предназначенный для представления табличных данных. Строка таблицы соответствует строке текста, которая содержит одно или несколько полей, разделенных запятыми.
Поддержка ограничений целостности в MySQL
Ограничения — это правила, применяемые к столбцам данных таблицы. Они используются, чтобы ограничить типы данных, которые могут храниться в таблице. Это обеспечивает точность и надежность данных в базе данных.По стандарту ASNI/SQL поддерживаются следующие ограничения целостности:
уникальность (значений атрибута или комбинации значений полей):
UNIQUE (имя_поля1 [, имя_поля2,...])
обязательность / необязательность:
NOT NULL / NULL
первичный ключ:
PRIMARY KEY(имя_поля1 [, имя_поля2,...])
внешний ключ:
FOREIGN KEY(имя_поля1 [, имя_поля2,...]) REFERENCESимя_таблицы [(имя_поля1 [, имя_поля2,...])][ON DELETE CASCADE | ON DELETE SET NULL]
При создании полей таблицы в модели можно использовать свойства:
PK – PRIMARY KEY;
NN – NOT NULL;
UQ – уникальное значение ;
BIN – бинарное поле, хранит данные как бинарные строки ;
UN – UNSIGNED – беззнаковое значение ;
ZF – ZERO FIELD – заполнение нулями;
AI – AUTO INCREMENTAL – счётчик ;
G – значение, генерируемое формулами ;
DEFAULT – значение по умолчанию .
Понятие представления(View)
Представление - фактически , тот же запрос , который выполняется всякий раз при участии в какой - либо команде . Результат выполнения этого запроса становится содержанием представления.Представления, или просмотры (VIEW), представляют собой временные, производные ( иначе - виртуальные ) таблицы и являются объектами базы данных, информация в которых не хранится постоянно, а формируется динамически при обращении к нимПредставление не может существовать само по себе, а определяется только в терминах одной или нескольких таблиц
Преимущества использования представлений
Повышение защищенности данных
Права доступа к данным могут быть предоставлены исключительно через ограниченный набор представлений, содержащих только то подмножество данных, которое необходимо пользователю. Подобный подход позволяет ужесточить контроль за доступом отдельных категорий пользователей к информации в БД.
Механизм представлений позволяет предоставить каждому пользователю только ту часть БД, которая ему действительно требуется для работы (внешнюю схему), скрывая от многочисленных пользователей концептуальную схему, доступную только администратору.
Запросы на выборку, которые необходимо выполнять регулярно, нет смысла пересылать по сети и компилировать каждый раз, как только клиенту потребуется соответствующая выборка данных. Разумно постоянно хранить в бд тексты таких запросов, вместе с планами их исполнения
Выборка данных из представленияSELECT * FROM view1
Создание хранимых процедур 18
Для создания хранимой процедуры существуют операторы: create procedure имя_хранимой_процедуры (список параметров) тело_процедуры; create function имя_хранимой_функции (список параметров) [returns тип_возвр_значения] тело функции Формат задания параметров: [ IN | OUT | INOUT ] имя_параметра тип Вызов хранимой процедуры осуществляется встроенной командой call call имя_проедуры (параметры) Для того чтобы использовать переменную используется оператор declare declare x int default 0; Для того чтобы присвоить значение переменной используется оператор set. Переменные названия которых начинаются со знака @ являются глобальными, то есть они являются доступными на протяжении всей сессии. Режимы
Режим IN - Режим по умолчанию. В этом случае процедура работает не с самим значением параметра, а с копией.
Режим OUT - Режим параметра, чьё значение может быть возвращено из хранимой процедуры. Это означает, что переменная переданная в хранимую процедуру из родительского блока после выполнения хранимой процедуры будет содержать измененное значение.
Режим INOUT - Комбинация режимов IN и OUT.
Использование хранимых процедур 19
Хранимые процедуры – объекты бд представляющие набор sql-инструкций, которые компилируются один раз и хранятся на сервере. Преимущества использования хранимых процедур: 1) помощь в увеличении производительности 2) после компиляции mysql кэширует все созданные хранимые процедуры, если приложение за время работы хранимую процедуру несколько раз в одно соединение, то берётся кэшированная версия 3) экономия трафика вместо отправки огромных конструкций sql для использования процедуры понадобится лишь название и список параметров 4) создав процедуру один раз, можно переписать клиентскую и серверную часть 5) повышенная безопасность использованных хранимых процедур 6) администратор базы данных может ограничить использование процедур на отдельные приложения или открыть процедуру, но не давать доступа к отдельным таблицам
Недостатки 1) использование оперативной памяти на каждое соединение возрастает так как mysql создаёт кэш процедуру для каждого соединения 2) хранимые процедуры усложняют бизнес-логику приложения
Понятие триггера
Триггер – хранимая процедура, которая начинает работать при наступлении определённого события. Например, можно создать триггер, который всегда будет срабатывать при удалении записи (будут дублироваться значения истории). Триггер всегда привязывается к конкретной таблице и часто вызывается по событию только к этой таблице.
Применение триггеров 21
С помощью триггеров можно: 1) проверить корректность введенных данных при выполнении сложных ограничений целостности 2) выдать предупреждение о необходимости определенных действий Псевдонимы OLD и NEW С их помощью можно обращаться к данным в разном состоянии. 1) OLD.callname позволяет обращаться к столбцу существующей строки до её модификации или её удаления. 2) NEW.callname позволяет обращаться к столбцу новой строки, которая будет вставлена или к существующей строке после её модификации. SET @sum = 0; # только 1 раз выполнить эту командуDELIMITER ;;CREATE TRIGGER `account_AFTER_INSERT`AFTER INSERTON `account`FOR EACH ROWBEGINset @sum = @sum + NEW.amount;END;
Команды GRANT(пример работы) 22
Grant - привилегии на таблицу Вы можете предоставить пользователям различные привилегии для таблиц. Эти разрешения могут быть любой комбинацией SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION или ALL. Синтаксис Синтаксис предоставления привилегий для таблицы в MySQL: GRANT privileges ON object TO user [WITH GRANT OPTION]; Пример:GRANT SELECT ON Students TO user1;Выдает право на выборку пользователю user1 в таблице Students.
Команда REVOKE((пример работы) 22
Отменить привилегии на таблицу Как только вы предоставили привилегии, вам может потребоваться отменить некоторые или все эти привилегии. Для этого вы можете запустить команду отмены. Вы можете отменить любую комбинацию SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER или ALL. Синтаксис Синтаксис отмены привилегий на таблицу в MySQL: REVOKE privileges ON object FROM user; Пример:REVOKE SELECT ON Students FROM user1;Отменяет право на выборку пользователю user1 в таблице Students.
4 уровня привилегий
Глобальный уровень.
Глобальные привилегии применяются ко всем базам данных на указанном сервере. Эти привилегии хранятся в таблице mysql.user.
Уровень базы данных.
Привилегии базы данных применяются ко всем таблицам указанной базы данных. Эти привилегии хранятся в таблицах mysql .db и mysql .host.
Уровень таблицы.
Привилегии таблицы применяются ко всем столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql. tables_priv.
·Уровень столбца.
Привилегии столбца применяются к отдельным столбцам указанной таблицы. Эти привилегии хранятся в таблице mysql .columns_priv.
Список возможных значений параметра priv_type: CREATE TEMPORARY TABLES, ALL, ALTER, CREATE, DELETE, DROP, EXECUTE , FILE, INDEX , INSERT 24
ALL [PRIVILEGES]
Задает все простые привилегии, кроме WITH GRANT OPTION(отзывать права у пользователей)
ALTER
Разрешает использование ALTER TABLE
CREATE
Разрешает использование CREATE TABLE
CREATE TEMPORARY TABLES
Разрешает использование CREATE TEMPORARY TABLE
DELETE
Разрешает использование DELETE
DROP
Разрешает использование DROP TABLE
EXECUTE
Разрешает пользователю запускать хранимые процедуры (для MySQL 5.0)
FILE
Разрешает использование SELECT ... INTO OUTFILE и LOAD DATA INFILE
INDEX
Разрешает использование CREATE INDEX and DROP INDEX
INSERT
Разрешает использование INSERT
Список возможных значений параметра priv_type: LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN
LOCK TABLES
Разрешает использование LOCK TABLES на таблицах, для которых есть привилегия SELECT
PROCESS
Разрешает использование SHOW FULL PROCESSLIST
REFERENCES
Зарезервировано для использования в будущем
RELOAD
Разрешает использование FLUSH
REPLICATION CLIENT
Предоставляет пользователю право запрашивать местонахождение головного и подчиненных серверов
REPLICATION SLAVE
Необходимо для подчиненных серверов при репликации (для чтения информации из бинарных журналов головного сервера)
