Оптимизация запросов в реляционных БД - презентация, доклад, проект скачать

Нажмите для полного просмотра!

Оптимизация запросов в реляционных БД, слайд №1

Оптимизация запросов в реляционных БД, слайд №2

Оптимизация запросов в реляционных БД, слайд №3

Оптимизация запросов в реляционных БД, слайд №4

Оптимизация запросов в реляционных БД, слайд №5

Оптимизация запросов в реляционных БД, слайд №6

Оптимизация запросов в реляционных БД, слайд №7

Оптимизация запросов в реляционных БД, слайд №8

Оптимизация запросов в реляционных БД, слайд №9

Оптимизация запросов в реляционных БД, слайд №10

Оптимизация запросов в реляционных БД, слайд №11

Оптимизация запросов в реляционных БД, слайд №12

Оптимизация запросов в реляционных БД, слайд №13

Оптимизация запросов в реляционных БД, слайд №14

Оптимизация запросов в реляционных БД, слайд №15

Оптимизация запросов в реляционных БД, слайд №16

Оптимизация запросов в реляционных БД, слайд №17

Оптимизация запросов в реляционных БД, слайд №18

Оптимизация запросов в реляционных БД, слайд №19

Оптимизация запросов в реляционных БД, слайд №20

Оптимизация запросов в реляционных БД, слайд №21

Оптимизация запросов в реляционных БД, слайд №22

Оптимизация запросов в реляционных БД, слайд №23

Оптимизация запросов в реляционных БД, слайд №24

Оптимизация запросов в реляционных БД, слайд №25

Оптимизация запросов в реляционных БД, слайд №26

Оптимизация запросов в реляционных БД, слайд №27

Оптимизация запросов в реляционных БД, слайд №28

Оптимизация запросов в реляционных БД, слайд №29

Оптимизация запросов в реляционных БД, слайд №30

Оптимизация запросов в реляционных БД, слайд №31

Оптимизация запросов в реляционных БД, слайд №32

Оптимизация запросов в реляционных БД, слайд №33

Оптимизация запросов в реляционных БД, слайд №34

Оптимизация запросов в реляционных БД, слайд №35

Оптимизация запросов в реляционных БД, слайд №36

Оптимизация запросов в реляционных БД, слайд №37

Оптимизация запросов в реляционных БД, слайд №38

Оптимизация запросов в реляционных БД, слайд №39

Оптимизация запросов в реляционных БД, слайд №40

Оптимизация запросов в реляционных БД, слайд №41

Оптимизация запросов в реляционных БД, слайд №42

Оптимизация запросов в реляционных БД, слайд №43

Оптимизация запросов в реляционных БД, слайд №44

Оптимизация запросов в реляционных БД, слайд №45

Оптимизация запросов в реляционных БД, слайд №46

Оптимизация запросов в реляционных БД, слайд №47

Оптимизация запросов в реляционных БД, слайд №48

Оптимизация запросов в реляционных БД, слайд №49

Оптимизация запросов в реляционных БД, слайд №50

Оптимизация запросов в реляционных БД, слайд №51

Оптимизация запросов в реляционных БД, слайд №52

Оптимизация запросов в реляционных БД, слайд №53

Оптимизация запросов в реляционных БД, слайд №54

Оптимизация запросов в реляционных БД, слайд №55

Оптимизация запросов в реляционных БД, слайд №56

Оптимизация запросов в реляционных БД, слайд №57

Оптимизация запросов в реляционных БД, слайд №58

Оптимизация запросов в реляционных БД, слайд №59

Оптимизация запросов в реляционных БД, слайд №60

Оптимизация запросов в реляционных БД, слайд №61

Оптимизация запросов в реляционных БД, слайд №62

Оптимизация запросов в реляционных БД, слайд №63

Оптимизация запросов в реляционных БД, слайд №64

Оптимизация запросов в реляционных БД, слайд №65

Оптимизация запросов в реляционных БД, слайд №66

Оптимизация запросов в реляционных БД, слайд №67

Оптимизация запросов в реляционных БД, слайд №68

Оптимизация запросов в реляционных БД, слайд №69

Оптимизация запросов в реляционных БД, слайд №70

Оптимизация запросов в реляционных БД, слайд №71

Содержание ▲

Вы можете ознакомиться и скачать презентацию на тему Оптимизация запросов в реляционных БД. Доклад-сообщение содержит 71 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Mypresentation Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1

Описание слайда:

Оптимизация запросов в реляционных БД Николай Александрович Шестаков Томский Политехнический Университет/Rubius

Слайд 2

Описание слайда:

Физическая организация данных Файлы данных Можно распределять по разным дискам (в зависимости от сценария работы) Файл журнала транзакций Последовательная (sequential) запись Файлы резервных копий Full backup Incremental backup Log backup (для point-in-time restore)

Слайд 3

Описание слайда:

Физическая организация данных в SQL Server БД SQL Server хранится в одном или нескольких файлах (.mdf, .ndf) Кроме файлов данных есть файл журнала транзакций (.ldf) Файл данных разбит на страницы по 8К Страница – минимальная единица чтения/записи данных Страницы сгруппированы в 64К экстенты Постраничная (поблочная) организация данных характерна для большинства реляционных СУБД

Слайд 4

Описание слайда:

Организация данных на странице Страница с данными хранит записи таблицы Одна запись хранится на странице целиком (кроме LOB и var-типов, которые не влезли на страницу) Таким образом, при чтении всего одного атрибута, с диска считывается запись целиком (не считая LOB) Постраничная организация данных характерна для большинства реляционных СУБД

Слайд 5

Описание слайда:

Дисковые операции Количество чтений/записей – это количество дисковых страниц Операции могут быть логические и физические

Слайд 6

Описание слайда:

Организация данных на странице

Слайд 7

Описание слайда:

Дисковые операции

Слайд 8

Описание слайда:

Логарифмический поиск Чтобы найти данные в таблице по условию, необходимо выполнить сканирование таблицы – это O(N) Если данные отсортированы, то это можно сделать поиском за O(log(N))

Слайд 9

Описание слайда:

Сбалансированные деревья

Слайд 10

Описание слайда:

B-Tree Оптимизировано под страничную организацию данных во внешней памяти (один узел – одна страница) Сбалансированное (длина пути от корня до любого листа одинакова для всех листов) Логарифмический поиск (по количеству дисковых чтений) Логарифмическая запись Сильная ветвистость (сотни потомков у узла)

Слайд 11

Описание слайда:

B-Tree

Слайд 12

Описание слайда:

B-Tree

Слайд 13

Описание слайда:

B-Tree

Слайд 14

Описание слайда:

B-Tree

Слайд 15

Описание слайда:

B-Tree

Слайд 16

Описание слайда:

B-Tree

Слайд 17

Описание слайда:

B-Tree

Слайд 18

Описание слайда:

B-Tree

Слайд 19

Описание слайда:

B-Tree

Слайд 20

Описание слайда:

B-Tree

Слайд 21

Описание слайда:

B-Tree

Слайд 22

Описание слайда:

B-Tree

Слайд 23

Описание слайда:

B-Tree Кластерный индекс Листовые узлы – страницы с данными Может быть только один для таблицы Некластерный индекс Листовые узлы – страницы с ключами, плюс ссылка на запись с данными Ссылка: значение ключа кластерного индекса (если он есть) адрес страницы с данными + идентификатор внутри страницы (если кластерный индекс отсутствует)

Слайд 24

Описание слайда:

Когда использовать? СЕЛЕКТИВНОСТЬ! СЕЛЕКТИВНОСТЬ!

Слайд 25

Описание слайда:

B*-Tree Индексы повышают эффективность Операции поиска записей с хорошей селективностью Поддержка уникальности значений атрибутов Операции, требующие упорядочивания по ключу (JOIN, DISTINCT) Проекция по небольшому количеству атрибутов

Слайд 26

Описание слайда:

B-Tree Кластерный индекс лучше выбирать для атрибутов: Короткое значение ключа (т.к. ключи к.и. используются как ссылки в некластерных) Данные часто выбираются диапазонами значений ключа (т.к. по ключу к.и. сгруппированы записи в страницах данных) Чаще используются для поиска (т.к. нет дополнительного обращения к страницам данных, к.и. быстрее) По умолчанию индекс первичного ключа делается кластерным, но это не всегда оптимальный выбор

Слайд 27

Описание слайда:

Пример

Слайд 28

Описание слайда:

B-Tree

Слайд 29

Описание слайда:

B*-Tree Included columns Можно построить индекс по нескольким атрибутам, а можно включить атрибуты посредством INCLUDE (и это не одно и то же) INCLUDED атрибуты не входят в состав ключа, но сохраняются в листьях Если запрос требует только атрибуты, входящие в индекс, либо в INCLUDED атрибуты индекса, то обращаться к странице с данными не придётся – нужные данные уже есть в индексе

Слайд 30

Описание слайда:

B*-Tree FILLFACTOR Задаёт объём занятого пространства на листовых страницах, которое выделяет СУБД при создании/перестройке индекса По умолчанию – 100% Необходим для сокращения времени вставок/обновлений Несколько снижает скорость чтения

Слайд 31

Описание слайда:

B*-Tree Фрагментация данных External fragmentation Internal fragmentation Способы дефрагментации: INDEX REORGANIZE INDEX REBUILD CREATE WITH DROP EXISTING Фрагментация – последнее, о чём стоит беспокоиться

Слайд 32

Описание слайда:

Что почитать

Слайд 33

Описание слайда:

Оптимизация запросов в реляционных БД Часть II Николай Александрович Шестаков Томский Политехнический Университет Rubius

Слайд 34

Описание слайда:

Оптимизация и выполнение запроса Алгоритмы выполнения соединений Этапы жизненного цикла запроса План выполнения и выполнение запроса Кеширование планов выполнения

Слайд 35

Описание слайда:

Join algorithms Nested loop join Inner join for each row R1 in outer table for each row R2 in inner table //or index lookup! if R1 joins with R2 return join (R1, R2) Outer join for each row R1 in outer table for each row R2 in inner table //or index lookup! if R1 joins with R2 return join (R1, R2) else return join (R1, NULL)

Слайд 36

Описание слайда:

Join algorithms Merge join

Слайд 37

Описание слайда:

Join algorithms Hash join

Слайд 38

Описание слайда:

Join algorithms

Слайд 39

Описание слайда:

Query optimization and execution Query Life Cycle

Слайд 40

Описание слайда:

Query optimization and execution Optimization Goal – to find a good enough execution plan, quickly enough Phases Simplification Trivial plan search Statistics update Cost-based optimization (several stages here) Execution plan generation

Слайд 41

Описание слайда:

Query optimization and execution Посмотреть план выполнения: Можно в Management Studio, включив опцию “Show execution plan” В текстовом/табличном/xml виде, предварительно выполнив запрос SET SHOWPLAN_TEXT ON или SET SHOWPLAN_ALL ON или SET SHOWPLAN_XML ON Использовать системную функцию sys.dm_exec_query_stats. В этом случае не нужно отдельно запускать запрос, план будет показан из кеша

Слайд 42

Описание слайда:

План выполнения Estimated execution plan Actual execution plan

Слайд 43

Описание слайда:

План выполнения Actual execution plan показывает не только оцениваемые показатели, но и реальные

Слайд 44

Описание слайда:

План выполнения

Слайд 45

Описание слайда:

План выполнения Планы можно выводить в виде: Графический XML Табличный (через SET STATISTICS PROFILE ON) Текстовый

Слайд 46

Описание слайда:

Query optimization and execution Query execution

Слайд 47

Описание слайда:

Query optimization and execution Query execution

Слайд 48

Описание слайда:

Статистики /* drop table ForeignKeyTable drop table PrimaryTable */ create table PrimaryTable (Id int identity primary key, N int, S char(150)) go create table ForeignKeyTable (Id int identity primary key, IdPrimary int references PrimaryTable, N int, S char(150)) go insert into PrimaryTable (N, S) values (CAST(RAND() * 10000 as int), CAST(NEWID() as char(150))) go 100000 insert into ForeignKeyTable (IdPrimary, N, S) values (CAST(RAND() * 100000 as int), CAST(RAND() * 1000 as int), CAST(NEWID() as char(150))) go 200000 insert into ForeignKeyTable (IdPrimary, N, S) values (CAST(RAND() * 100000 as int), CAST(RAND() * 1000 + 1000 as int), CAST(NEWID() as char(150))) go 100000 insert into ForeignKeyTable (IdPrimary, N, S) values (CAST(RAND() * 100000 as int), CAST(RAND() * 1000 + 2000 as int), CAST(NEWID() as char(150))) go 150000 create nonclustered index IX_ForeignKeyTable ON dbo.ForeignKeyTable(N) go select * from PrimaryTable p join ForeignKeyTable f on p.Id = f.IdPrimary where f.N = 1234 -- change selectivity here and see how execution plan is changing DBCC SHOW_STATISTICS ("dbo.ForeignKeyTable", "IX_ForeignKeyTable")

Слайд 49

Описание слайда:

Статистики

Слайд 50

Описание слайда:

Статистики

Слайд 51

Описание слайда:

Статистики

Слайд 52

Описание слайда:

Статистики Статистики обновляются автоматически Иногда эвристики автообновления не срабатывают Тогда нужно обновить вручную Если Actual Rows = Estimated Rows – статистики ни при чём

Слайд 53

Описание слайда:

Кеширование планов выполнения Планы кешируются Разные значения параметров –> один кеш Parameter Sniffing OPTIMIZE FOR UNKNOWN hint

Слайд 54

Описание слайда:

Query optimization and execution Просмотр кешированных планов выполнения

Слайд 55

Описание слайда:

Что почитать

Слайд 56

Описание слайда:

Транзакции ACID Atomicity Consistency Isolation Durability

Слайд 57

Описание слайда:

Транзакции Atomicity – всё или ничего

Слайд 58

Описание слайда:

Транзакции Durability – если транзакция выполнена, она выполнена (результат устойчив к системным сбоям)

Слайд 59

Описание слайда:

Транзакции Consistency Данные согласованы в начале транзакции и после окончания транзакции (но не обязательно внутри транзакции) Согласованные данные – удовлетворяющие ограничениям целостности и бизнес-правилам Иногда свойство понимают в том смысле, что результаты завершённых транзакций должны быть видны последующим транзакциям

Слайд 60

Описание слайда:

Transactions Isolation Выполняющиеся параллельно транзакции логически не влияют друг на друга Принцип сериализации: транзакции, выполняющиеся параллельно, должны логически выполняться так, как будто они запущены по очереди На практике сериализация обходится дорого, поэтому поддержка изоляции сводится к поддержке выполнения условий заданного уровня изоляции транзакции

Слайд 61

Описание слайда:

Уровни изоляции транзакций Read Uncommitted Разрешены грязные чтения Read Committed Чтения только зафиксированных данных, но повторное чтение может вернуть изменённые данные Repeatable Read Повторное чтение внутри транзакции всегда возвращает одинаковые данные для прочитанных ранее записей. Но могут появиться новые записи, которых раньше не было Serializable Полная сериализация! Snapshot Используется мультиверсионность записей, каждая транзакция видит состояние БД, которое было на момент её начала

Слайд 62

Описание слайда:

Уровни изоляции транзакций Read Uncommitted Быстр Никого не ждёт Наибольшая вероятность получить несогласованные данные При чтении ничего не блокирует

Слайд 63

Описание слайда:

Уровни изоляции транзакций Read Committed Ждёт освобождения exclusive lock Есть вероятность получить изменённые данные при повторных чтениях При чтении ничего не блокирует

Слайд 64

Описание слайда:

Уровни изоляции транзакций Repeatable Read Ждёт освобождения exclusive lock При чтении ставит Shared lock При повторных чтениях могут появляться фантомные записи

Слайд 65

Описание слайда:

Уровни изоляции транзакций Serializable Обеспечивает 100% изоляцию Ждёт освобождения exclusive lock При чтении ставит Shared lock на диапазон значений атрибутов (диапазон берётся из условия запроса)

Слайд 66

Описание слайда:

Locks Shared Lock Блокирует изменение (попытки Update и Exclusive), позволяет чтение. Несколько транзакций могут установить одновременно на один ресурс. Exclusive Lock Блокирует изменение (попытки Update и Exclusive) и чтение. Только одна транзакция может установить одновременно на один ресурс. Запрашивается для изменения данных. Key-Range Запрещает INSERT по значениям диапазона ключей Update Lock Блокирует изменение (попытки Update и Exclusive), позволяет чтение. Только одна транзакция может установить одновременно на один ресурс. Потом для обновления записи запрашивается Exclusive. Intent Lock, Schema Lock, Bulk Update Lock

Слайд 67

Описание слайда:

Deadlocks Вероятность взаимных блокировок повышается, если: Большое количество параллельных транзакций, которые меняют данные Используются долгие сложные транзакции, состоящие из нескольких операций Используются операции, блокирующие большое количество записей (агрегатные, типа SUM) Используется высокий уровень изоляции Не используется MVCC (Snapshot Isolation)

Слайд 68

Описание слайда:

Snapshot Isolation Multiversion Concurrency Control Используется timestamp для обозначения версии записи Транзакции читают версии записей, соответствующие моменту начала транзакции При изменении данных, старые записи перемещаются в хранилище Version Store (в tempdb) В SQL Server включается для БД целиком: SET READ_COMMITTED_SNAPSHOT ON или SET ALLOW_SNAPSHOT_ISOLATION ON Snapshot Isolation исключает аномалии грязных, повторных и фантомных чтений Отсутствуют блокировки при чтении Snapshot Isolation не обеспечивает Serializable «Serializable» в Oracle – на самом деле Snapshot!

Слайд 69

Описание слайда:

Snapshot Isolation Snapshot Isolation исключает аномалии грязных, повторных и фантомных чтений Отсутствуют блокировки при чтении Snapshot Isolation не обеспечивает Serializable «Serializable» в Oracle – на самом деле Snapshot! (аналогично в PostgreSQL < 9.1) MVCC режим выгоден при большом количестве параллельных транзакций, но требует ресурсов на управление версиями данных

Слайд 70

Описание слайда:

Snapshot Isolation write-skew anomaly

Слайд 71

Описание слайда:

Monitoring locks and deadlocks SSMS Activity Monitor Performance Monitor SQL Server Lock counters Dynamic Management Views sys.dm_exec_requests sys.dm_tran_locks sys.dm_os_waiting_tasks SQL Server Profiler SQL Server Extended Events (since 2012)