Введение в СУБД Александр Шавлюга 2011 2012 © Itransition Group. Proprietary and Confidential 1
О чем будем говорить § § § Об объектах БД О типах данных О нормальных формах Об индексах О транзакциях 2012 © Itransition Group. Proprietary and Confidential 2
Виды и типы баз данных Можно выделить несколько видов БД: § - иерархические; § - сетевые; § - реляционные; § - объектно-ориентированные; § - гибридные; Типы: § OLTP (online transaction processing) § OLAP (online analytical processing) 2012 © Itransition Group. Proprietary and Confidential 3
Таблицы § Обычные - это набор данных, организованных с помощью столбцов и строк CREATE TABLE Students ( Id INT PRIMARY KEY, First. Name VARCHAR(50), Last. Name VARCHAR(50) ) 2012 © Itransition Group. Proprietary and Confidential 4
Типы данных § 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 5
Типы данных § 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 6
Типы данных § 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 7
Типы данных - рекомендации § Помните, что 5/2 = 2; 5. 0/2 = 2. 5 § Неявное преобразование идет в сторону увеличения точности/объема; § Никогда не используйте FLOAT или REAL для хранения кол-ва денег. Используйте DECIMAL/NUMERIC или MONEY. § Не злоупотребляйте NVARCHAR(max) § Выбирайте точность с умом. 2012 © Itransition Group. Proprietary and Confidential 8
Таблицы § Временные – похожи на обычные, но долго не живут. 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 9
А еще бывают § Табличные переменные – очень похожи на временные таблицы, но их жизнь еще короче. Только для 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 10
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 11
Stored Procedures § Это именованный набор SQL команд, хранящийся на сервере. § Компилируются при первом выполнении (генерируется execution plan). За счет этого ускоряются последующие вызовы. § Можно изменять как данные, так и структуру БД § С MS SQL 2005 появилась возможность написания хранимых процедур на любом языке. NET § Не увлекайтесь!!! Трудно дебажить и искать ошибки 2012 © Itransition Group. Proprietary and Confidential 12
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 13
Stored Procedures § Использование: EXECUTE Nerd_Close. School. Year @School. Year = 2012 § Или так: EXEC Nerd_Close. School. Year 2012 © Itransition Group. Proprietary and Confidential 14
Функции § Бывают – Скалярные – возвращают одно значение – Табличные – возвращают набор данных(таблицу) § Функции не могут изменять данные, а также состояние сервера (никаких INSERT, UPDATE, ALTER TABLE и прочего) § Функции легко встраиваются в запрос. 2012 © Itransition Group. Proprietary and Confidential 15
![Функции § Пример скалярной функции: CREATE FUNCTION [dbo]. [Get. Nerds. Count] ( @Average. Mark](https://present5.com/presentation/49920528_149816976/image-16.jpg "Функции § Пример скалярной функции: CREATE FUNCTION [dbo]. [Get. Nerds. Count] ( @Average. Mark")
Функции § Пример скалярной функции: 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 16
![Функции § Пример табличной функции: CREATE FUNCTION [dbo]. [Get. Nerds] ( @Average. Mark DECIMAL(4,](https://present5.com/presentation/49920528_149816976/image-17.jpg "Функции § Пример табличной функции: CREATE FUNCTION [dbo]. [Get. Nerds] ( @Average. Mark DECIMAL(4,")
Функции § Пример табличной функции: 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 17
Триггеры § Это хранимая процедура, которая срабатывает при определенных условиях. § А именно: – При изменении данных (INSERT, UPDATE, DELETE) – При изменении схемы БД (CREATE, ALTER, DROP, etc…) 2012 © Itransition Group. Proprietary and Confidential 18
Триггеры § Создание: 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 19
Внимание, вопрос! 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 ? 20
Внимание, вопрос! 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 ? 21
Нормализация § Цели нормализации – устранение избыточности данных, устранение некоторых аномалий обновления § Не имеет целью уменьшение или увеличение производительности работы или же уменьшение или увеличение физического объёма базы данных. § Нормализация – это не панацея. 2012 © Itransition Group. Proprietary and Confidential 27
Ключи § Уникальный ключ – поле(я), которое имеет уникальное значение для каждой строки (может быть 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 28
Ключи § Внешний ключ – поле, которое ссылается на первичный или уникальный ключ другой (ссылаемой) таблицы. Не может содержать значения, которые отсутствуют в ссылаемой таблице. 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 29
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 30
2 NF § 1 NF + не должно быть не ключевых ячеек, зависящих от части составного ключа. Bad Сотрудник Квалификация Опыт Световой Меч Сидоров Джедай 100500 Есть Петров Падаван 250 Нет Иванов Падаван 70 Нет Good Сотрудник Квалификация Опыт Сидоров Джедай 100500 Петров Падаван Иванов Падаван Квалификация Световой Меч 250 Джедай Есть 70 Падаван Нет 2012 © Itransition Group. Proprietary and Confidential 31
3 NF § 2 NF + не должно быть не ключевых ячеек, транзитивно зависящих от ключа. Bad Сотрудник Звездолет № звездолета Сидоров Супер-Пупер 0001 МР-7 Петров Ну. Так. Себе 6360 МР-5 Иванов Ну. Так. Себе 6360 МР-5 Good Сотрудник Звездолет № звездолета Сидоров Супер-Пупер 0001 МР-7 Петров Ну. Так. Себе 6360 МР-5 Иванов Ну. Так. Себе 2012 © Itransition Group. Proprietary and Confidential 32
4, 5 и 6 нормальная форма Не стоит увлекаться На практике 3 NF достаточно 2012 © Itransition Group. Proprietary and Confidential 33
Нормализация – это не панацея Вроде бы неплохо 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 34
Нормализация – это не панацея Вроде бы неплохо 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 35
Нормализация – это не панацея Но это тоже вариант SELECT c. Id, c. Check. No, c. Check. Date, с. Invoice. No, с. Amount FROM Checks c 2012 © Itransition Group. Proprietary and Confidential 36
Нормализация – это не панацея Придумайте варианты, когда денормализация нужна. . . 2012 © Itransition Group. Proprietary and Confidential 37
Индексы § На примере библиотеки 2012 © Itransition Group. Proprietary and Confidential 38
Кластерный индекс § Что будет при добавлении новых книг? § На помощь придет картотека 2012 © Itransition Group. Proprietary and Confidential 39
Кластерный индекс § Показывает, каким образом данные физически упорядочены в таблице. Может быть только один на таблицу. § Нет дополнительных/избыточных структур. § Выше скорость поиска, но если большое количество часто обновляемых полей – снижение скорости вставкиобновления § Если часто удаляются записи – фрагментация таблицы 2012 © Itransition Group. Proprietary and Confidential 40
Некластерный индекс § Присвоим каждой книге порядковый номер § Для каждой книги создадим карточку с названием и порядковым номером. § Карточки в ящике должны быть отсортированы по названию. Название No No Название книги Автор Агитлубки 1 1 Агитлубки Маяковский 2 Базы данных для ПРО Ковалёв Базы данных для ПРО 2 3 Гагарин, я вас любила Каренина 4 Горе от ума Грибоедов ************* 100500 Я – легенда Мэтисон 100501 Гарри Поттер 1 -8 Роулинг Гагарин, я вас 3 любила Гарри Поттер 100501 1 -8 Горе от ума 4 Я – легенда 100500 2012 © Itransition Group. Proprietary and Confidential 41
Некластерный индекс § Отдельная структура, которая содержит в себе ключ(и) индекса в упорядоченом виде + ссылки на ключи кластерного индекса. § В таблице может быть любое количество некласстерных индексов § следует ограничиваться 4 -5 индексами § Страдает производительность при вставкеудаленииобновлении данных 2012 © Itransition Group. Proprietary and Confidential 42
Вложенные колонки (Included columns) § Хотим получить список книг с указанием года издания § Решение: включить год издания в индекс: Название No Год Агитлубки 1 1925 Базы данных для ПРО 2 2012 Гагарин, я вас любила 3 2001 Гарри Поттер 1 -8 100501 2010 Горе от ума 4 Я – легенда 100500 1957 1887 2012 © Itransition Group. Proprietary and Confidential 43
Рекомендации по индексам § Делайте индексы на Внешние Ключи (Foreign Keys) § Анализируйте наиболее популярные запросы и создавайте для них индексы § Не забывайте про вложенные колонки (Included Columns) § Можно создавать индексы на VIEW 2012 © Itransition Group. Proprietary and Confidential 44
Рекомендации по индексам § Не создавайте индексы на небольших таблицах (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 45
Внимание, вопрос! 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 46
Транзакции § Единица работы, выполняемая сервером базы данных. § ACID: – – Atomicity Consistency Isolation Durability 2012 © Itransition Group. Proprietary and Confidential 47
Транзакции § Пример (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 48
Уровни изоляции Потерянное обновление Предположим, имеются две транзакции, в которых одновременно выполнены следующие SQLоператоры: Транзакция 1 UPDATE tbl 1 SET f 2=f 2+20 WHERE f 1=1 Транзакция 2 UPDATE tbl 1 SET f 2=f 2+25 WHERE f 1=1 . . . И в результате, значение увеличивается на 45. 2012 © Itransition Group. Proprietary and Confidential 49
Уровни изоляции § READ UNCOMMITTED – Позволяет читать данные других еще активных транзакций, которые не закомичены. Текущая транзакция чтения не блокируют модификации данных. Т. е. другие транзакции могут менять данные, прочитанные этой транзакцией. Это приводит к ошибкам «грязного чтения» , поскольку в начале и в конце транзакции, читая одни и те же строки таблицы, мы можем получать данные других транзакций, которые возможно никогда не попадут в таблицу из-за их последующего отката. 2012 © Itransition Group. Proprietary and Confidential 50
Уровни изоляции § 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 51
Уровни изоляции § READ COMMITTED – этот уровень изоляции избавляет от проблемы грязного чтения, т. к. налагает блокировки чтения на изменяемые данные. Эти блокировки позволяют другим транзакциям читать эти же данные, но запрещают их модифицировать, пока выполняется запрос на чтение (select). Тем не менее, сразу же после выполнения операции select, эти данные могут быть модифицированы другими транзакциями, что приводит к проблеме неповторяемого чтения. Также нет защиты и от фантомных записей. 2012 © Itransition Group. Proprietary and Confidential 52
Уровни изоляции § READ COMMITTED Time Transaction 1 Transaction 2 T 1 BEGIN TRAN T 2 SELECT f 2 FROM tbl 1 WHERE f 1=1; T 3 UPDATE tbl 1 SET f 2=f 2+1 WHERE f 1=1; T 4 SELECT f 2 FROM tbl 1 WHERE COMMIT; Т 5 f 1=1; SELECT f 2 FROM tbl 1 WHERE f 1=1; При повторной попытке выбора значения из поля f 2 в транзакции 2 будет получен другой результат 2012 © Itransition Group. Proprietary and Confidential 53
Уровни изоляции § REPEATABLE READ – этот уровень означает, что транзакция не может читать данные которые были изменены другой транзакцией, но еще не закомичены. Также означает, что ни одна транзакция не может модифицировать данные которые были прочитаны текущей транзакцией до тех пор, пока она не завершится. Однако, другие транзакции могут вставлять новые записи в таблицы, которые могут быть вновь перечитаны текущей транзакцией (фантомные записи). 2012 © Itransition Group. Proprietary and Confidential 54
Уровни изоляции § 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 55
Уровни изоляции § SERIALIZABLE – гарантирует полную изоляцию. Все транзакции выполняются последовательно. 2012 © Itransition Group. Proprietary and Confidential 56
Dead-locks § Взаимная блокировка — ситуация в многозадачной СУБД, при которой несколько процессов находятся в состоянии бесконечного ожидания ресурсов, занятых самими этими процессами. 2012 © Itransition Group. Proprietary and Confidential 57
Dead-locks Шаг Процесс 1 Процесс 2 0 Хочет захватить A и B, начинает с A Хочет захватить A и B, начинает с B 1 Захватывает ресурс A Захватывает ресурс B 2 Ожидает освобождения ресурса B Ожидает освобождения ресурса A Взаимная блокировка 2012 © Itransition Group. Proprietary and Confidential 58
Dead-locks § Вцелом, чем выше уровень изоляции, тем больше шансов попасть на встретить dead-lock, т. к. все большее количество блокировок должны сохраняться до конца транзакции. 2012 © Itransition Group. Proprietary and Confidential 59
Рекомендации по транзакции § Всегда используйте транзакции для логически неделимых операций. § Делайте транзакции как можно короче по времени выполнения. § Всегда помните об откатах во время ошибок. § Устраняйте любое взаимодействие с пользователем во время транзакции. § Старайтесь использовать одинаковый порядок доступа к ресурсам БД в транзакциях. 2012 © Itransition Group. Proprietary and Confidential 60
План выполнения запроса § Нужен для анализа и оптимизации запроса § Иногда СУБД строит не оптимальный план и его можно «подтюнить» § Не стоит увлекаться указаниями выполнения запроса 2012 © Itransition Group. Proprietary and Confidential 61
Что почитать § 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 62
Вопросы? 2011 a. shavlyuga@itransition. com 2012 © Itransition Group. Proprietary and Confidential 63
Скачать презентацию Введение в СУБД Александр Шавлюга 2011 2012
Базовая лекция по СУБД - Release.pptx