Базы данных/Лабораторная работа 3
Методичка в разработке, но можно начинать делать
Цели лабораторной работы:
- научиться анализировать план выполнения запросов и уменьшать сложность выполнения запросов,
- научиться анализировать состояние сервера СУБД, обучиться основам администрирования.
Содержание
Введение
Ваша задача в этой лабораторной работе:
- С помощью 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
(В процессе написания)
Дополнительно:
- 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-сервера
(В процессе написания)
В этом разделе собраны инструменты и техники для мониторинга и оценки статуса сервера СУБД и сбора статистики.
Дополнительно:
