Базы данных/Лабораторная работа 3 — различия между версиями

Версия 04:45, 30 апреля 2016

Методичка в разработке, но можно начинать делать

Цели лабораторной работы:

• научиться анализировать план выполнения запросов и уменьшать сложность выполнения запросов,
• научиться анализировать состояние сервера СУБД, обучиться основам администрирования.

Введение

Ваша задача в этой лабораторной работе:

• С помощью EXPLAIN посчитать сложность выполнения комплексных запросов из прошлой лабораторной работы,
• Разобраться с выводом EXPLAIN и суметь объяснять, какие части подзапроса сколько будут выполняться, сколько данных будет задействовано.
• С помощью индексов ускорить выполнение запросов.
• Проанализировать выполнение запроса после создания индексов и сравнить планы выполнений.

Создание индексов

Индексы нужны для того, чтобы ускорить доступ к данным. Для каждой таблицы доступен только один самый быстрый индекс - первичный ключ. Как правило, это id в каждой таблице. Запросы, в условиях которых фильтрация происходит только по id, работают быстро, таблица при этом не сканируется целиком.

В таблицу также можно добавить еще индексы для ускорения выполнения запросов. Они могут относиться как к одному, так и к группе атрибутов. Индексы выбирают исходя из информационной потребности пользователей: если в запросах часто встречается какая-либо комбинация условий, то индекс строят именно на эти условия.

Добавление индексов требует дополнительного места на диске, но важнее то, что любые манипуляции с данными становятся более затратными, так как индекс в этом случае должен обновиться. Из-за этого неразумно выстраивать все возможные индексы, следует ограничиться только наиболее востребованными.

Чтобы создать индекс, нужно использовать команду CREATE INDEX, указать имя индекса и список атрибутов, по которым он будет строиться, опционально можно указать тип индекса.

Важно: прежде чем создавать индексы, посмотрите, сколько места занимают ваши данные. Это можно сделать средствами

Типы индексов

Наиболее популярный тип индекса - B-дерево. Эта структура хорошо подходит для атрибутов, которые можно сравнивать между собой, выполняя операции: <, <=, =, >=, >. Также индексы этого типа поддерживают быструю проверку IS (NOT) NULL, поэтому они хорошо подходят для атрибутов с большим количеством пропусков. В-деревья ускоряют любые операции, связанные со сравнением значений, в том числе сортировку и группировку.

Синтаксис создания индекса:

 CREATE INDEX index_name ON table_name (column_name);

Второй по популярности тип индекса - hash, который позволяет быстро проводить сравнения на равенство. Этот тип также подходит для условий типа WHERE column_name IN ( ... ).

Синтаксис создания индекса:

 CREATE INDEX index_name ON table_name USING HASH (column_name);

Функции в индексах

Удобно для строк создавать также индексы независимые от регистра:

 CREATE INDEX index_name ON table_name (lower(column_name)); 

В этом случае, выбирая WHERE lower(title) = 'the matrix' приведение значений колонки не нужно, и запрос выполнится быстро.

Индексы на несколько колонок

Удобный для часто встречающихся комбинаций условий.

Синтаксис:

 CREATE INDEX index_name ON table_name (column1_name, column2_name);

Уникальный индекс

Этот индекс также добавляет проверку целостности при изменении данных.

Синтаксис:

 CREATE UNIQUE INDEX index_name on table_name (column_name);

Условные индексы

Можно создать еще более специализированный индекс на конкретные условия.

 CREATE INDEX index_name on title (kind_id, production_year) where (production_year>2000 and kind_id=1);

Этот индекс позволит выбирать фильмы выпущенные после 2000 года, при этом для записей не удовлетворяющих условию все останется по-прежнему (как скорость выборки, так и скорость изменения данных).

План запроса. EXPLAIN

С помощью команды EXPLAIN можно посмотреть, как предположительно будет выполняться запрос и как он в итоге выполнился. Вывод команды содержит для каждой таблицы, используемой в запросе:

• сколько условных единиц времени уйдет на получение/изменение первой строчки
• сколько условных единиц времени уйдет на выполнение операции целиком
• сколько всего строк будет задействовано

Условные единицы времени зависят от доступных серверу ресурсов.

Основы EXPLAIN

Пример команды:

 explain 
 select * from title t
 join kind_type kt on kt.id=t.kind_id
 where production_year=2000 and kt.id=1;

Ее вывод:

 "Nested Loop  (cost=0.00..108304.97 rows=15233 width=146)"
 "  ->  Seq Scan on kind_type kt  (cost=0.00..1.09 rows=1 width=52)"
 "        Filter: (id = 1)"
 "  ->  Seq Scan on title t  (cost=0.00..108151.55 rows=15233 width=94)"
 "        Filter: ((kind_id = 1) AND (production_year = 2000))"

Здесь Nested Loop означает, что внутри запроса выполняется цикл. Этот цикл состоит из последовательного просмотра записей через первичный ключ (Seq Scan).

Всего запрос предполагает вернуть примерно 15233 записи.

Если добавить индекс:

 create production_year_index on title (production_year);

То план запроса изменится:

 "Nested Loop  (cost=1153.79..57625.67 rows=15233 width=146)"
 "  ->  Seq Scan on kind_type kt  (cost=0.00..1.09 rows=1 width=52)"
 "        Filter: (id = 1)"
 "  ->  Bitmap Heap Scan on title t  (cost=1153.79..57472.25 rows=15233 width=94)"
 "        Recheck Cond: (production_year = 2000)"
 "        Filter: (kind_id = 1)"
 "        ->  Bitmap Index Scan on ok  (cost=0.00..1149.98 rows=62073 width=0)"
 "              Index Cond: (production_year = 2000)"

Время получения первой строки увеличилось, так как теперь запросу нужно сначала прочитать индексы, а затем выполнять сам запрос. Но и максимальная оценка выполнения запроса уменьшилась вдвое.

Также теперь поиск записей в title происходит не последовательным перебором, а Bitmap Heap Scan - алгоритмом поиска по B-дереву. Именно такого типа индекс был добавлен.

EXPLAIN ANALIZE

Опция ANALIZE позволяет сделать более точный прогноз выполнения запроса. Однако, при этом запрос будет частично выполнен, поэтому ее следует использовать в сочетании с ROLLBACK или иметь ввиду, что случайные данные будут обновлены.

ANALIZE показывает точную оценку времени выполнения запроса.

Пример запроса:

 explain ANALYZE
 select * from title t where production_year=2000 and kind_id in (select id from kind_type where id=1);

Пример вывода:

 "Nested Loop  (cost=1153.79..57625.67 rows=15233 width=94) (actual time=234.085..339.698 rows=8763 loops=1)"
 "  ->  Seq Scan on kind_type  (cost=0.00..1.09 rows=1 width=4) (actual time=0.006..0.009 rows=1 loops=1)"
 "        Filter: (id = 1)"
 "        Rows Removed by Filter: 6"
 "  ->  Bitmap Heap Scan on title t  (cost=1153.79..57472.25 rows=15233 width=94) (actual time=234.076..338.145 rows=8763 loops=1)"
 "        Recheck Cond: (production_year = 2000)"
 "        Rows Removed by Index Recheck: 1795048"
 "        Filter: (kind_id = 1)"
 "        Rows Removed by Filter: 52237"
 "        ->  Bitmap Index Scan on ok  (cost=0.00..1149.98 rows=62073 width=0) (actual time=18.910..18.910 rows=61000 loops=1)"
 "              Index Cond: (production_year = 2000)"
 "Total runtime: 340.103 ms"

EXPLAIN (ANALYZE, BUFFERS)

Опция BUFFERS показывает использование буфера во время выполнения запроса.

Дополнительно:

• http://www.postgresql.org/docs/9.3/static/sql-explain.html
• https://habrahabr.ru/post/203320/
• https://habrahabr.ru/post/203386/
• https://habrahabr.ru/post/203484/

Параметры и состояние PostgreSQL-сервера

(В процессе написания)

В этом разделе собраны инструменты и техники для мониторинга и оценки статуса сервера СУБД и сбора статистики.

Дополнительно:

• http://www.postgresql.org/docs/current/static/monitoring-stats.html
• https://github.com/julmon/pg_activity