Моя Базовая лекция по СУБД - Release.pptx
- Количество слайдов: 50
Введение в СУБД Вадим Ковалёв 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
Вопросы? 2011 v. kovalyov@itransition. com 401 а 2012 © Itransition Group. Proprietary and Confidential 57
Моя Базовая лекция по СУБД - Release.pptx