Просмотр кода страницы Базы данных/Лабораторная работа 3


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

== Введение ==

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


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

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

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

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

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

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

Выполните в psql: 

  show data_directory; 

чтобы узнать, где хранятся файлы вашей базы. После этого выполните:

  SELECT oid from pg_database where datname = 'imdb';

чтобы узнать идентификатор базы.

Директория с файлами вашей базы находится тут: {data_directory}/base/{oid} (у меня это: /var/lib/postgresql/9.3/main/base/16384)

Выполните команду du для этого пути (при необходимости прочитайте мануал к команде du).

Пробуйте создавать различные индексы и проверять, как изменяется занимаемое базой место. 

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

Наиболее популярный тип индекса - 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 года, при этом для записей не удовлетворяющих условию все останется по-прежнему (как скорость выборки, так и скорость изменения данных).

== Внешние ключи ==

Внешние ключи - это атрибуты, которые связывают сущности, поддерживая ссылочную целостность: нельзя установить значение для foreign key, которого нет в таблице на которую атрибут ссылается. Для внешних ключей СУБД использует hash-индексы.

Пример создания внешнего ключа:

  alter table movie_info add foreign key (movie_id) references title(id);

Используя схему БД из второй лабораторной работы, добавьте внешние ключи, нужные для ваших запросов.

Запрос для просмотра существующих внешних ключей:

  SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
  FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
  WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

Пример плана запроса после создания внешнего ключа:

  explain 
  select * from title t
  join movie_info mi on mi.movie_id=t.id
  where t.production_year=1899;

План запроса:

  "Hash Join  (cost=24668.15..680874.12 rows=26044 width=192)"
  "  Hash Cond: (mi.movie_id = t.id)"
  "  ->  Seq Scan on movie_info mi  (cost=0.00..247886.02 rows=10198002 width=98)"
  "  ->  Hash  (cost=24411.20..24411.20 rows=9436 width=94)"
  "        ->  Bitmap Heap Scan on title t  (cost=177.56..24411.20 rows=9436 width=94)"
  "              Recheck Cond: (production_year = 1899)"
  "              ->  Bitmap Index Scan on ok  (cost=0.00..175.20 rows=9436 width=0)"
  "                    Index Cond: (production_year = 1899)"

== План запроса. 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 index 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 показывает использование буфера во время выполнения запроса. Значение shared hit показывает, сколько блоков буфера удалось использовать, то есть избежать повторного чтения из основном памяти, shared read - сколько блоков считалось из таблиц и индексов.

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

* [[Базы_данных/Лабораторная_работа_3/Индексы| Подробнее про работу индексов с примерами (спойлеры)]]
* 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-сервера ==

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

Для мониторинга текущих запросов разработана утилита аналогичная программе top - [https://github.com/julmon/pg_activity pg_activity]. Эта программа показывает текущие запросы и используемые ресурсы.

Просмотри статистики выполнения операций в базе:

  select * from pg_stat_database where datname='imdb';

Некоторые значения в статистике:

* xact_commit - количество закоммиченных транзакций (в том числе и обычных операций insert/update)
* xact_rollback - количество сроллбеченных транзакций (в том числе и обычных операций insert/update)
* blks_read - общее количество блоков, которые были считаны
* blks_hit - количество блоков, считанных из кэша
* tup_returned - количество кортежей, которые были выведены для пользователя
* tup_fetched - количество кортежей, которые СУБД выбирала для последующих операций (сравните со значением выше)
* temp_bytes - размер временных файлов, которые создаются для ускорения выборки (например, при присоединении таблиц)
* deadlocks - количество транзакций с взаимной блокировкой

Попробуйте выполнить запрос, сравнив количество считываемых блоков в его плане выполнения и изменениями статистики.

Есть еще несколько таблиц со статистикой по использованию памяти для таблиц и индексов. Единственный интерес в них представляют количества live/dead tuples. Мертвые кортежи - это те, которые уже удалены из базы логически, но на их место в памяти еще не вставлены новые.

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

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