Введение в СУБД Вадим Ковалёв 2011 2012

Введение в СУБД Вадим Ковалёв 2011 2012 © Itransition Group. Proprietary and Confidential 1

О чем будем говорить § § § Об объектах БД О типах данных О нормальных формах Об индексах О транзакциях § Не будем говорить о No. SQL и прочем. 2012 © Itransition Group. Proprietary and Confidential 2

Таблицы § Обычные - это набор данных, организованных с помощью столбцов и строк CREATE TABLE Students ( Id INT PRIMARY KEY, First. Name VARCHAR(50), Last. Name VARCHAR(50) ) 2012 © Itransition Group. Proprietary and Confidential 3

Таблицы § Временные – похожи на обычные, но долго не живут. MS SQL Server: My. SQL: CREATE TABLE #Students ( Id INT PRIMARY KEY, First. Name VARCHAR(50), Last. Name VARCHAR(50) ) CREATE TEMPORARY TABLE Students ( Id INT PRIMARY KEY, First. Name VARCHAR(50), Last. Name VARCHAR(50) ) 2012 © Itransition Group. Proprietary and Confidential 4

А еще бывают § Табличные переменные – очень похожи на временные таблицы, но их жизнь еще короче. Только для MS SQL Server: DECLARE @Temp. Students TABLE ( Id INT PRIMARY KEY, First. Name VARCHAR(50), Last. Name VARCHAR(50) ) 2012 © Itransition Group. Proprietary and Confidential 5

Views § Они же вьюшки, они же представления – виртуальная (логическая) таблица, именованный SELECT запрос. Создание: CREATE VIEW v. Nerds AS SELECT s. Id, First. Name, Last. Name, AVG(e. Mark) AS Mark FROM Students s JOIN Exams. Results e ON e. Student. Id = s. Id GROUP BY s. Id, s. First. Name, s. Last. Name HAVING AVG(e. Mark) > 9 Использование: SELECT COUNT(Id) FROM v. Nerds 2012 © Itransition Group. Proprietary and Confidential 6

Stored Procedures § Это именованный набор SQL команд, хранящийся на сервере. § Компилируются при первом выполнении (генерируется execution plan). За счет этого ускоряются последующие вызовы. 2012 © Itransition Group. Proprietary and Confidential 7

Stored Procedures § Создание: CREATE PROCEDURE Nerd_Close. School. Year ( @School. Year int ) AS BEGIN INSERT INTO Nerds (Student. Id, First. Name, Last. Name, Average. Mark, School. Year) SELECT s. Id, First. Name, Last. Name, AVG(e. Mark), @School. Year FROM Students s JOIN Exams. Results e ON e. Student. Id = s. Id GROUP BY s. Id, s. First. Name, s. Last. Name HAVING AVG(e. Mark) > 9 END 2012 © Itransition Group. Proprietary and Confidential 8

Stored Procedures § Использование: EXECUTE Nerd_Close. School. Year @School. Year = 2012 § Или так: EXEC Nerd_Close. School. Year 2012 © Itransition Group. Proprietary and Confidential 9

Функции § Бывают – Скалярные – возвращают одно значение – Табличные – возвращают набор данных(таблицу) § Функции не могут изменять данные, а также состояние сервера (никаких INSERT, UPDATE, ALTER TABLE и прочего) § Функции легко встраиваются в запрос. 2012 © Itransition Group. Proprietary and Confidential 10

Функции § Пример скалярной функции: CREATE FUNCTION [dbo]. [Get. Nerds. Count] ( @Average. Mark DECIMAL(4, 2) ) RETURNS INT AS BEGIN DECLARE @Result INT SELECT @Result = COUNT(s. Id) FROM Students s JOIN Exams. Results e ON e. Student. Id = s. Id GROUP BY s. Id HAVING AVG(e. Mark) >= @Average. Mark RETURN @Result END § Использование: SELECT dbo. Get. Nerds. Count(8. 5) 2012 © Itransition Group. Proprietary and Confidential 11

Функции § Пример табличной функции: CREATE FUNCTION [dbo]. [Get. Nerds] ( @Average. Mark DECIMAL(4, 2) ) RETURNS @Result TABLE(Student. Id INT , First. Name VARCHAR(50) , Last. Name VARCHAR(50), Average. Mark DECIMAL(4, 2)) AS BEGIN INSERT INTO @Result (Student. Id, First. Name, Last. Name, Average. Mark) SELECT s. Id, First. Name, Last. Name, AVG(e. Mark) FROM Students s JOIN Exams. Results e ON e. Student. Id = s. Id GROUP BY s. Id, s. First. Name, s. Last. Name HAVING AVG(e. Mark) >= @Average. Mark RETURN END § Использование: SELECT Student. Id FROM dbo. Get. Nerds(8. 5) 2012 © Itransition Group. Proprietary and Confidential 12

Триггеры § Это хранимая процедура, которая срабатывает при определенных условиях. § А именно: – При изменении данных (INSERT, UPDATE, DELETE) – При изменении схемы БД (CREATE, ALTER, DROP, etc…) 2012 © Itransition Group. Proprietary and Confidential 13

Триггеры § Создание: CREATE TRIGGER Students_Archive ON Students AFTER DELETE AS BEGIN INSERT INTO Students. Archive SELECT Id, First. Name, Last. Name FROM deleted END § inserted и deleted – системные таблицы. § Используйте триггер только тогда, когда вы на 100% уверены, что он вам нужен. 2012 © Itransition Group. Proprietary and Confidential 14

Типы данных § Exact Numeric Тип Размер Описание (байт) bit 1 0 или 1. Если в таблице от 1 до 8 таких полей, они все хранятся в 1 -ом байте. tinyint 1 0 – 255 smallint 2 -32, 768 – 32, 767 int 4 -2, 147, 483, 648 – 2, 147, 483, 647 bigint 8 -2^63 – 2^63 -1 decimal(p, s) or numeric(p, s) 5 -17 p – общая точность (сколько всего знаков), p ∈ {1 -38}; s – кол-во знаков после запятой. money 8 SQL Server only. То же, что и numeric(18, 4) smallmoney 4 SQL Server only. То же, что и numeric(9, 4) 2012 © Itransition Group. Proprietary and Confidential 15

Типы данных § Approximate Numeric Тип Размер Описание (байт) float(n) 4 или 8 n – кол-во бит для хранения мантиссы. n ∈ {1 -53} real 4 Эквивалент float(24) § Date and time Тип Размер Описание (байт) date 4 0001 -01 -01 – 9999 -12 -31 time 3 -5 00: 00. 0000000 – 23: 59. 9999999 smalldatetime 4 1900 -01 -01 – 2079 -06 -06 datetime 8 1753 -01 -01 – 9999 -12 -31 2012 © Itransition Group. Proprietary and Confidential 16

Типы данных § Character and binary Strings Тип Размер Описание (байт) char(n) n Строки постоянной длины. n ∈ {1 -8000} nchar(n) 2*n Unicode-cтроки постоянной длины. n ∈ {1 -4000} varchar(n) ≤ n Строки переменной длины. n ∈ {1 -8000, max} nvarchar(n) ≤ 2*n Unicode-cтроки переменной длины. n ∈ {1 -4000, max} binary(n) n Набор байт постоянной длины. n ∈ {1 -8000} varbinary(n) ≤ n Набор байт переменной длины. n ∈ {1 -8000, max} 2012 © Itransition Group. Proprietary and Confidential 17

Типы данных - рекомендации § Помните, что 5/2 = 2; 5. 0/2 = 2. 5 § Неявное преобразование идет в сторону увеличения точности/объема; § Никогда не используйте FLOAT или REAL для хранения кол-ва денег. Используйте DECIMAL/NUMERIC или MONEY. § Не злоупотребляйте NVARCHAR(max) § Выбирайте точность с умом. 2012 © Itransition Group. Proprietary and Confidential 18

Внимание, вопрос! Id (int) Student. Id (int) Mark (int) *** 1 12 7 2 14 8 3 17 8 4 24 10 SELECT AVG(Mark) FROM Exams. Results SELECT AVG(CAST(Mark AS DECIMAL)) FROM Exams. Results 2012 © Itransition Group. Proprietary and Confidential ? 19

Внимание, вопрос! CREATE TABLE Students ( Id INT PRIMARY KEY, First. Name VARCHAR(50), Last. Name VARCHAR(50) ) CREATE PROCEDURE Students_Get. By. Last. Name @Last. Name NVARCHAR(35) AS SELECT Last. Name, First. Name FROM Students WHERE Last. Name = @Last. Name; 2012 © Itransition Group. Proprietary and Confidential ? 20

Нормализация § Цель нормализации – устранение избыточности данных. § Нормализация – это не панацея. 2012 © Itransition Group. Proprietary and Confidential 26

Ключи § Уникальный ключ – поле(я), которое имеет уникальное значение для каждой строки (может быть NULL). § Первичный ключ – уникальный ключ, который также однозначно идентифицирует строку в таблице (НЕ может быть NULL). CREATE TABLE Exams. Results ( • Id INT PRIMARY KEY, Student. Id INT, Exam. Name VARCHAR(50), • UNIQUE (Student. Id, Exam. Name), Mark TINYINT ) 2012 © Itransition Group. Proprietary and Confidential 27

Ключи § Внешний ключ – поле, которое ссылается на первичный или уникальный ключ другой (ссылаемой) таблицы. Не может содержать значения, которые отсутствуют в ссылаемой таблице. CREATE TABLE Exams. Results ( Id INT PRIMARY KEY, Student. Id INT, • FOREIGN KEY (Student. Id) REFERENCES Students(Id), Exam. Name VARCHAR(50), Mark TINYINT ) 2012 © Itransition Group. Proprietary and Confidential 28

1 NF § Нет повторяющихся строк, каждая ячейка таблицы содержит одно значение Bad Good Сотрудник Телефон Ковалёв 555 -42 -47, 135 -68 -97 Ковалёв 555 -42 -47 Ковалёв 135 -68 -97 Петров 357 -55 -21 Иванов 568 -56 -56 2012 © Itransition Group. Proprietary and Confidential 29

2 NF § 1 NF + не должно быть не ключевых ячеек, зависящих от части составного ключа. Bad Сотрудник Квалификация Опыт Световой Меч Ковалёв Джедай 100500 Есть Петров Падаван 250 Нет Иванов Падаван 70 Нет Good Сотрудник Квалификация Опыт Ковалёв Джедай 100500 Петров Падаван Иванов Падаван Квалификация Световой Меч 250 Джедай Есть 70 Падаван Нет 2012 © Itransition Group. Proprietary and Confidential 30

3 NF § 2 NF + не должно быть не ключевых ячеек, транзитивно зависящих от ключа. Bad Сотрудник Квалификация Опыт Световой Меч Ковалёв Джедай 100500 Есть Петров Падаван 250 Нет Иванов Падаван 70 Нет Good Сотрудник Квалификация Опыт Ковалёв Джедай 100500 Петров Падаван Иванов Падаван Квалификация Световой Меч 250 Джедай Есть 70 Падаван Нет 2012 © Itransition Group. Proprietary and Confidential 31

Нормализация – это не панацея Вроде бы неплохо Id Сотрудника Телефон 1 Ковалёв 1 555 -42 -47 3 Петров 1 135 -68 -97 4 Иванов 3 357 -55 -21 4 568 -56 -56 Но это тоже вариант: Id Сотрудник Телефон 1 Телефон 2 1 Ковалёв 555 -42 -47 135 -68 -97 3 Петров 357 -55 -21 4 Иванов Телефон 3 568 -56 -56 2012 © Itransition Group. Proprietary and Confidential 32

Нормализация – это не панацея Вроде бы неплохо SELECT c. Id, c. Check. No, c. Check. Date, i. Invoice. No, i. Amount FROM Checks c INNER JOIN Invoices i ON i. Id = c. Invoice. Id 2012 © Itransition Group. Proprietary and Confidential 33

Нормализация – это не панацея Но это тоже вариант SELECT c. Id, c. Check. No, c. Check. Date, с. Invoice. No, с. Amount FROM Checks c 2012 © Itransition Group. Proprietary and Confidential 34

Индексы § На примере библиотеки 2012 © Itransition Group. Proprietary and Confidential 37

Кластерный индекс § Показывает, каким образом данные упорядочены в таблице. § Нет дополнительных/избыточных структур. Название книги Автор Агитлубки Маяковский Базы данных для ПРО Ковалёв Гагарин, я вас любила Каренина Горе от ума Грибоедов ************* Я – легенда Мэтисон 2012 © Itransition Group. Proprietary and Confidential 38

Кластерный индекс § Что будет при добавлении новых книг? § На помощь придет картотека 2012 © Itransition Group. Proprietary and Confidential 39

Не кластерный индекс § Присвоим каждой книге порядковый номер § Для каждой книги создадим карточку с названием и порядковым номером. § Карточки в ящике должны быть отсортированы по названию. Название No No Название книги Автор Агитлубки 1 1 Агитлубки Маяковский 2 Базы данных для ПРО Ковалёв Базы данных для ПРО 2 3 Гагарин, я вас любила Каренина 4 Горе от ума Грибоедов ************* 100500 Я – легенда Мэтисон 100501 Гарри Поттер 1 -8 Роулинг Гагарин, я вас 3 любила Гарри Поттер 100501 1 -8 Горе от ума 4 Я – легенда 100500 2012 © Itransition Group. Proprietary and Confidential 40

Не кластерный индекс § Отдельная структура, которая содержит в себе ключ(и) индекса в упорядоченом виде + ссылки на ключи кластерного индекса. No Название книги Автор Название No 1 Агитлубки Маяковский Агитлубки 1 2 Базы данных для ПРО Ковалёв 3 Гагарин, я вас любила Каренина Базы данных для ПРО 2 4 Горе от ума Грибоедов Гагарин, я вас 3 любила 100500 Я – легенда Мэтисон 100501 Гарри Поттер 1 -8 Роулинг Гарри Поттер 100501 1 -8 ************* Горе от ума 4 Я – легенда 100500 2012 © Itransition Group. Proprietary and Confidential 41

Вложенные колонки (Included columns) § Хотим получить списко книг с указанием года издания § Решение: включить год издания в индекс: Название No Год Агитлубки 1 1925 Базы данных для ПРО 2 2012 Гагарин, я вас любила 3 2001 Гарри Поттер 1 -8 100501 2010 Горе от ума 4 Я – легенда 100500 1957 1887 2012 © Itransition Group. Proprietary and Confidential 42

Рекомендации по индексам § Делайте индексы на Внешние Ключи (Foreign Keys) § Анализируйте наиболее популярные запросы и создавайте для них индексы § Не забывайте про вложенные колонки (Included Columns) 2012 © Itransition Group. Proprietary and Confidential 43

Рекомендации по индексам § Не создавайте индексы на небольших таблицах (e. g. States, Countries), или на полях с маленькой селективностью (e. g. bit data type, “Gender” column, etc) § Старайтесь не использовать отрицания в WHERE (e. g. !=, <>, NOT IN, LIKE ‘%fdsa’) § Вьюшки тоже можно индексировать. 2012 © Itransition Group. Proprietary and Confidential 44

Внимание, вопрос! SELECT Id, First. Name, Last. Name FROM Students WHERE LEFT(Last. Name, 1) = 'K' SELECT Id, First. Name, Last. Name FROM Students WHERE Last. Name LIKE 'K%' SELECT Id, First. Name, Last. Name, Date. Of. Birth FROM Students WHERE DATEPART(YEAR, Date. Of. Birth) = 1987 SELECT Id, First. Name, Last. Name, Date. Of. Birth FROM Students WHERE Date. Of. Birth BETWEEN '1987 -01 -01' AND '1987 -12 -31' 2012 © Itransition Group. Proprietary and Confidential 45

Транзакции § Единица работы, выполняемая сервером базы данных. § ACID: – – Atomicity Consistency Isolation Durability 2012 © Itransition Group. Proprietary and Confidential 46

Транзакции § Пример (MS SQL Server): BEGIN TRY BEGIN TRANSACTION -- do smth COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS Error. Number, ERROR_MESSAGE() AS Error. Message ROLLBACK TRAN END CATCH 2012 © Itransition Group. Proprietary and Confidential 47

Уровни изоляции § READ UNCOMMITTED – Позволяет читать данные других еще активных транзакций, которые не закомичены. Текущая транзакция чтения не блокируют модификации данных. Т. е. другие транзакции могут менять данные, прочитанные этой транзакцией. Это приводит к ошибкам «грязного чтения» , поскольку в начале и в конце транзакции, читая одни и те же строки таблицы, мы можем получать данные других транзакций, которые возможно никогда не попадут в таблицу из-за их последующего отката. 2012 © Itransition Group. Proprietary and Confidential 48

Уровни изоляции § READ UNCOMMITTED Time Transaction 1 Transaction 2 T 1 BEGIN TRAN T 2 UPDATE/INSERT Students BEGIN TRAN SELECT FROM Students. T 3 Получаем измененные в момент T 2 данные. T 4 ROLLBACK 2012 © Itransition Group. Proprietary and Confidential 49

Уровни изоляции § READ COMMITTED – этот уровень изоляции избавляет от проблемы грязного чтения, т. к. налагает блокировки чтения на изменяемые данные. Эти блокировки позволяют другим транзакциям читать эти же данные, но запрещают их модифицировать, пока выполняется запрос на чтение (select). Тем не менее, сразу же после выполнения операции select, эти данные могут быть модифицированы другими транзакциями, что приводит к проблеме неповторяемого чтения. Также нет защиты и от фантомных записей. 2012 © Itransition Group. Proprietary and Confidential 50

Уровни изоляции § READ COMMITTED Time Transaction 1 Transaction 2 T 1 BEGIN TRAN T 2 SELECT sum(mark) FROM Students BEGIN TRAN T 3 (Wait for it…) DELETE FROM Students T 4 (Wait for it…) COMMIT TRAN T 5 SELECT count(Id) FROM Students T 6 Считаем среднюю успеваемость (Делим полученные ранее числа) 2012 © Itransition Group. Proprietary and Confidential 51

Уровни изоляции § REPEATABLE READ – этот уровень означает, что транзакция не может читать данные которые были изменены другой транзакцией, но еще не закомичены. Также означает, что ни одна транзакция не может модифицировать данные которые были прочитаны текущей транзакцией до тех пор, пока она не завершится. Однако, другие транзакции могут вставлять новые записи в таблицы, которые могут быть вновь перечитаны текущей транзакцией (фантомные записи). 2012 © Itransition Group. Proprietary and Confidential 52

Уровни изоляции § REPEATABLE READ Time Transaction 1 Transaction 2 T 1 BEGIN TRAN T 2 SELECT sum(mark) FROM Students BEGIN TRAN T 3 SELECT count(Id) FROM Students T 4 Считаем среднюю успеваемость (Делим полученные ранее числа) Пытаемся DELETE FROM Students (Wait for it. . . ) T 5 COMMIT TRAN (Wait for it. . . ) T 6 DELETE FROM Students 2012 © Itransition Group. Proprietary and Confidential 53

Уровни изоляции § SERIALIZABLE – гарантирует полную изоляцию. Все транзакции выполняются последовательно. 2012 © Itransition Group. Proprietary and Confidential 54

Рекомендации по транзакции § Всегда используйте транзакции для логически неделимых операций. § Делайте транзакции как можно короче по времени выполнения. § Всегда помните об откатах во время ошибок. § Устраняйте любое взаимодействие с пользователем во время транзакции. § Старайтесь использовать одинаковый порядок доступа к ресурсам БД в транзакциях. 2012 © Itransition Group. Proprietary and Confidential 55

Что почитать § http: //www. sqlservercentral. com/stairway/72400/ - Stairway to Database Design (Таблицы, процедуры, констрейнты, нормализация) § http: //www. sqlservercentral. com/stairway/72399/ - Stairway to SQL Server Indexes § http: //www. sqlservercentral. com/stairway/75773/ - Stairway to T-SQL DML (SELECT, INSERT, UPDATE, DELETE, + Матчасть) 2012 © Itransition Group. Proprietary and Confidential 56