СУБД. Лекция 6

Оптимизация работы с БД

Нормализация и денормализация данных;
Оптимизация запросов конкретных типов;
Разница между актуальными и историческими данными;
Секционирование.
Оптимизация на уровне приложения;
Примеры эффективного массового изменения данных.

Организационные моменты

Не забывайте отмечаться на лекциях!
Оставляйте обратную связь по завершению лекции!

Нормализация и денормализация данных

Тестовая база

Таблица	Кортежей	Размер
genres	19	8 192 B
links	58 098	2 968 kB
movie_genres	101 841	4 408 kB
movie_tags	1 108 997	55 MB
movies	58 098	3 400 kB
rating	27 753 444	1 594 MB
tags	74 715	3 728 kB

Общий размер: ~2 298 MB

FOREIGN KEY объявлены, но никакие индексы не создавались.

Рейтинг комедий про зомби

Начальный вариант (7.7 secs):

select m.id, m.title, avg(r.rating)
from movies       m
join movie_genres gm on (gm.movie_id = m.id)
join genres       g  on (g.id = gm.genre_id)
join movie_tags   tm on (tm.movie_id = m.id)
join tags         t  on (t.id = tm.tag_id)
join ratings      r  on (r.movie_id = m.id)
where lower(g.name) = lower('Comedy')
  and lower(t.name) like lower('Zombie%')
group by m.id, m.title
order by avg(r.rating) desc;

Рейтинг комедий про зомби

Построили типовые индексы (10.9 secs):

select m.id, m.title, avg(r.rating)
from movies       m
join movie_genres gm on (gm.movie_id = m.id)
join genres       g  on (g.id = gm.genre_id)
join movie_tags   tm on (tm.movie_id = m.id)
join tags         t  on (t.id = tm.tag_id)
join ratings      r  on (r.movie_id = m.id)
where lower(g.name) = lower('Comedy')
  and lower(t.name) like lower('Zombie%')
group by m.id, m.title
order by avg(r.rating) desc;

Рейтинг комедий про зомби

Немного переписали запрос (486 msec):

select m.id, m.title, avg(r.rating)
from (
  select m.id, m.title
  from movies       m
  join movie_genres gm on (gm.movie_id = m.id)
  join genres       g  on (g.id = gm.genre_id)
  join movie_tags   tm on (tm.movie_id = m.id)
  join tags         t  on (t.id = tm.tag_id)
  where lower(g.name) = lower('Comedy')
    and lower(t.name) like lower('Zombie%')
  group by m.id
) m
join ratings r on (r.movie_id = m.id)
group by m.id, m.title
order by avg(r.rating) desc;

Рейтинг комедий про зомби

Покрывающий индекс по рейтингам (82 msec):

select m.id, m.title, avg(r.rating)
from (
  select m.id, m.title
  from movies       m
  join movie_genres gm on (gm.movie_id = m.id)
  join genres       g  on (g.id = gm.genre_id)
  join movie_tags   tm on (tm.movie_id = m.id)
  join tags         t  on (t.id = tm.tag_id)
  where lower(g.name) = lower('Comedy')
    and lower(t.name) like lower('Zombie%')
  group by m.id
) m
join ratings r on (r.movie_id = m.id)
group by m.id, m.title
order by avg(r.rating) desc;

SUBQUERIES vs JOIN

Коррелирующий подзапрос

SELECT E.*
FROM Employee E WHERE EXISTS (
  SELECT *
  FROM Department D WHERE D.DepartmentID = E.DepartmentID
);

Не коррелирующий подзапрос

SELECT E.*
FROM Employee E WHERE E.DepartmentID IN (
  SELECT DepartmentID
  FROM Department D
);

JOIN

SELECT E.*
FROM Employee E
JOIN Department D ON (E.DepartmentID = D.DepartmentID);

Рейтинг комедий про зомби

Итого:

Без индексов: 7.7 sec (hit=17495, read=183234, written=46254)
С базовыми индексами: 10.9 sec (hit=3801568, read=579841, written=1)
Поправленный запрос: 486 msec (hit=118402, read=110620)
С покрывающим индексом: 82 msec (hit=108407)

Удалось ускорить в 93.90 раз! Победа?

Заменим 'Zombie%' на 'Comedy%' и снова более 10 секунд :(

Оптимизируйте доступ к данным:

Не извлекает ли приложение больше данных, чем нужно;
Не анализирует ли сервер больше строк, чем это необходимо;

Типичные ошибки:

Выборка ненужных строк;
Выборка всех столбцов из соединения нескольких таблиц;
Выборка всех столбцов.

Модификация запросов

Что с этим запросом не так?

select m.id, m.title, avg(r.rating)
from (
  select m.id, m.title
  from movies       m
  join movie_genres gm on (gm.movie_id = m.id)
  join genres       g  on (g.id = gm.genre_id)
  join movie_tags   tm on (tm.movie_id = m.id)
  join tags         t  on (t.id = tm.tag_id)
  where lower(g.name) = lower('Comedy')
    and lower(t.name) like lower('ZombieComedy%')
  group by m.id
) m
join ratings r on (r.movie_id = m.id)
group by m.id, m.title
order by avg(r.rating) desc;

Нормализация

Нормализованные таблицы обычно обновляются быстрее, чем ненормализованные.
Когда данные хорошо нормализованы, они либо редко дублируются, либо не дублируются совсем. Так что изменять приходится меньше данных.
Нормализованные таблицы обычно меньше по размеру, поэтому лучше помещаются в памяти и их производительность выше.
Из-за отсутствия избыточных данных реже возникает необходимость в запросах с фразами DISTINCT или GROUP BY для извлечения списков значений.

Денормализация

Денормализация: Намеренное приведение структуры базы данных в состояние, не соответствующее критериям нормализации, обычно проводимое с целью ускорения операций чтения из базы за счет добавления избыточных данных.

Обновление данных триггерах.
Обновление данных по расписанию.
Инкрементальное обновление данных.

Нормализация/денормализация

alter table movies
  add column rating_sum float default 0 not null,
  add column rating_cnt int default 0 not null;

update movies m
set
  rating_cnt = r.rating_cnt,
  rating_sum = r.rating_sum
from (
  select movie_id, count(*) as rating_cnt, sum(rating) as rating_sum
  from ratings
  group by movie_id
) r where (m.id = r.movie_id);

create trigger ...;

Нормализация/денормализация

После денормализации (12 msec):

select m.id, m.title, m.rating_sum / m.rating_cnt
from movies       m
join movie_genres gm on (gm.movie_id = m.id)
join genres       g  on (g.id = gm.genre_id)
join movie_tags   tm on (tm.movie_id = m.id)
join tags         t  on (t.id = tm.tag_id)
where lower(g.name) = lower('Comedy')
and lower(t.name) like lower('Zombie%')
and m.rating_cnt > 0
group by m.id
order by 3 desc

Оптимизация запросов конкретных типов

Оптимизация DELETE

Очистка таблицы

DELETE FROM films;

Для удаления всех записей из таблицы/таблиц есть отдельная команда

TRUNCATE TABLE films CASCADE;

Особенности:

TRUNCATE много быстрее, чем DELETE;
TRUNCATE нарушает изоляцию транзакций.

Оптимизация COUNT(*)

Получение кол-ва записей в таблице

SELECT COUNT(*) FROM ratings;

Если нужно ориентировочное количество записей в таблице, то можно выполнить запрос вида:

SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE oid = 'ratings'::regclass;

Оптимизация COUNT(*)

Получение кол-ва записей в запросе с условием

SELECT COUNT(*) FROM (
  SELECT * FROM tags WHERE name LIKE 'Zombie%'
) as t;

Если нужно ориентировочное количество записей в результате выполнения запроса, то можно получить их из плана выполнения:

EXPLAIN SELECT * FROM tags WHERE name LIKE 'Comedy%';

Оптимизация COUNT(*)

Получение кол-ва записей в запросе с условием

CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS
$func$
DECLARE
    rec   record;
    ROWS  INTEGER;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN ROWS IS NOT NULL;
    END LOOP;

    RETURN ROWS;
END
$func$ LANGUAGE plpgsql;

SELECT count_estimate('SELECT * FROM tags WHERE name LIKE ''Comedy%''');

Оптимизация LIMIT со смещением

Вывод N-ой страницы

SELECT * FROM movies
ORDER BY title LIMIT 100 OFFSET 2000;

Вывод следующей страницы

SELECT * FROM movies
WHERE title > 'Angry Video Game Nerd: The Movie'
ORDER BY title LIMIT 100;

Оптимизация LIMIT со смещением

SELECT *
FROM movies
ORDER BY title
LIMIT 10 OFFSET 100;


SELECT m.*
FROM movies m
JOIN (
  SELECT id
  FROM movies m
  ORDER BY title
  LIMIT 10 OFFSET 100
) l ON (m.id = l.id)
ORDER BY title;

Исторические и актуальные данные

Настоящее vs Прошлое

OLTP (Online Transaction Processing): Способ организации БД, при котором система работает с небольшими по размерам транзакциями, но идущими большим потоком, и при этом клиенту требуется от системы минимальное время отклика.
OLAP (Online Analytical Processing): Технология обработки данных, заключающаяся в подготовке суммарной (агрегированной) информации на основе больших массивов данных, структурированных по многомерному принципу.

Актуальные данные

Особенности:

Относительно стабильный размер базы данных;
Сильно нормализованные модели данных;
При возникновении ошибки транзакция должна целиком откатиться и вернуть систему к состоянию, которое было до начала транзакции;
Данные активно изменяются;
Обработка данных в реальном времени.

Исторические данные

Особенности:

Записи только добавляются в базу данных;
Версионирование всех изменяемых данных;
Важно сохранить не только "что изменялось", но и "почему изменялось".

Проблемы:

Постоянно увеличивающийся размер базы данных;
Поддержка эволюции схемы данных;
Согласованность с актуальными данными.

xxx: биг дата - это кладбище данных

xxx: раз в столетие туда приходит спиритист чтобы спросить какого-то мертвеца о чем-то - и снова тишина..

Секционирование

Секционирование (англ. partitioning): Разделение хранимых объектов баз данных (таких как таблиц, индексов, материализованных представлений) на отдельные части с раздельными параметрами физического хранения.

Секционирование: польза

В определённых ситуациях оно кардинально увеличивает быстродействие, особенно когда большой процент часто запрашиваемых строк таблицы относится к одной или лишь нескольким секциям.
Секционирование может сыграть роль ведущих столбцов в индексах, что позволит уменьшить размер индекса и увеличит вероятность нахождения наиболее востребованных частей индексов в памяти.
Когда в выборке или изменении данных задействована большая часть одной секции, последовательное сканирование этой секции может выполняться гораздо быстрее, чем случайный доступ по индексу к данным, разбросанным по всей таблице.

Секционирование: польза

Массовую загрузку и удаление данных можно осуществлять, добавляя и удаляя секции, если это было предусмотрено при проектировании секционированных таблиц.
Операция ALTER TABLE DETACH PARTITION или удаление отдельной секции с помощью команды DROP TABLE выполняются гораздо быстрее, чем массовая обработка. Эти команды также полностью исключают накладные расходы, связанные с выполнением VACUUM после DELETE.
Редко используемые данные можно перенести на более дешёвые и медленные носители.

Секционирование: наследование

CREATE TABLE test (
  id         SERIAL PRIMARY KEY,
  title      TEXT,
  created_on TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_test_created_on ON test (created_on);

CREATE TABLE test_1
  (CHECK ( id >= 100 AND id < 200 ))
  INHERITS (test);


CREATE TABLE test_2 ( LIKE test INCLUDING ALL );
ALTER TABLE test_2
  INHERIT test,
  ADD CONSTRAINT partition_check CHECK (id >= 200 and id < 300);

Секционирование: наследование

Плюсы:

Оно работает из коробки.

Минусы:

Строго говоря, это костыль;
При вставке данных нужно явно добавлять данные в соответствующую партицию;
Нужно аккуратно следить за индексами и ограничениями;
Не работают FOREIGN KEY.

Секционирование: pg_pathman

CREATE EXTENSION pg_pathman;

CREATE TABLE journal (
    id      SERIAL,
    dt      TIMESTAMP NOT NULL,
    level   INTEGER,
    msg     TEXT);

CREATE INDEX ON journal(dt);

INSERT INTO journal (dt, level, msg)
SELECT g, random() * 6, md5(g::text)
FROM generate_series('2015-01-01'::date, '2015-12-31'::date, '1 minute') as g;

SELECT create_range_partitions('journal', 'dt',
       '2015-01-01'::date, '1 day'::interval);

Секционирование: pg_pathman

Плюсы:

Более удобная работа с секциями;
Автоматическое создание секций при INSERT-ах;
HASH и RANGE секционирование.

Минусы:

Это расширение не входит в комплект PostgreSQL;
Не работают FOREIGN KEY.

Декларативное секционирование: PostgreSQL 10+

Секционирование по списку значений (List partitioning)

CREATE TABLE list_customers (
        id INTEGER,
        status TEXT,
        arr NUMERIC
) PARTITION BY LIST(status);

CREATE TABLE customers_active PARTITION OF list_customers FOR VALUES IN ('ACTIVE');

CREATE TABLE customers_archived PARTITION OF list_customers FOR VALUES IN ('EXPIRED');

CREATE TABLE customers_others PARTITION OF list_customers DEFAULT;

INSERT INTO list_customers VALUES
        (1,'ACTIVE',100),
        (2,'RECURRING',20),
        (3,'EXPIRED',38),
        (4,'REACTIVATED',144);

Декларативное cекционирование: PostgreSQL 10+

Секционирование по хэшу (hash partitioning)

CREATE TABLE hash_customers (
        id INTEGER,
        status TEXT,
        arr NUMERIC
) PARTITION BY HASH(id);

CREATE TABLE customers_part1 PARTITION OF hash_customers
        FOR VALUES WITH (modulus 3, remainder 0);

CREATE TABLE customers_part2 PARTITION OF hash_customers
        FOR VALUES WITH (modulus 3, remainder 1);

CREATE TABLE customers_part3 PARTITION OF hash_customers
        FOR VALUES WITH (modulus 3, remainder 2);

INSERT INTO hash_customers VALUES
        (1,'ACTIVE',100),
        (2,'RECURRING',20),
        (3,'EXPIRED',38),
        (4,'REACTIVATED',144);

Декларативное cекционирование: PostgreSQL 10+

Секционирование по диапазону (range partitioning)

CREATE TABLE range_customers (
        id INTEGER,
        status TEXT,
        arr NUMERIC
) PARTITION BY RANGE(arr);

CREATE TABLE customers_arr_small PARTITION OF range_customers
        FOR VALUES FROM (MINVALUE) TO (25);

CREATE TABLE customers_arr_medium PARTITION OF range_customers
        FOR VALUES FROM (25) TO (75);

CREATE TABLE customers_arr_large PARTITION OF range_customers
        FOR VALUES FROM (75) TO (MAXVALUE);

INSERT INTO customers VALUES
        (1,'ACTIVE',100),
        (2,'RECURRING',20),
        (3,'EXPIRED',38),
        (4,'REACTIVATED',144);

Секционирование: PostgreSQL 10+

Плюсы:

Поддержка из коробки.

Минусы:

Не работают FOREIGN KEY;
Не работает на старых версиях PostgreSQL.

Оптимизация на уровне приложения

Уменьшение времени блокировок

Уменьшение времени блокировок за счет избавления от крупных запросов вида «обновить всё».

Разбиение запроса на более мелкие;
Модификация схемы данных для избавления от крупных запросов;
Оптимистические блокировки.

Разбиение запроса на более мелкие

До:

DELETE FROM work_logs
WHERE created_at > NOW() - interval '90 days';

После (выполнять в цикле):

BEGIN;
DELETE FROM work_logs
WHERE id IN (
  SELECT id
  DELETE FROM work_logs
  WHERE created_at > NOW() - interval '90 days'
  LIMIT 10000
);
COMMIT;

Массовая вставка данных

Решение «в лоб»:

String sql = "INSERT INTO foo (name, description) VALUES (?, ?)";
try (PreparedStatement ps = db.prepareStatement(sql)) {

    for (int i = 0; i < 100; ++i) {
        ps.setString(1, "name-" + i);
        ps.setString(2, "description-" + i);
        ps.execute();
    }

}

Массовая вставка данных

Более производительное решение:

String sql = "INSERT INTO foo (name, description) VALUES (?, ?)";
try (PreparedStatement ps = db.prepareStatement(sql)) {

    for (int i = 0; i < 100; ++i) {
        ps.setString(1, "name-" + i);
        ps.setString(2, "description-" + i);
        ps.addBatch();
    }
    ps.executeBatch();
}

Массовая вставка данных

Решение без покемонов:

String sql = "INSERT INTO foo (name, description) VALUES (?, ?)";
try (PreparedStatement ps = db.prepareStatement(sql,
        Statement.NO_GENERATED_KEYS)) {
    for (int i = 0; i < 100; ++i) {
        ps.setString(1, "name-" + i);
        ps.setString(2, "description-" + i);
        ps.addBatch();
    }
    ps.executeBatch();
}

Массовая вставка данных

Загрузка данных через COPY гораздо быстрее:

COPY movie_tags (
  user_id,
  movie_id,
  tag,
  created)
FROM 'ml-latest/tags.csv'
WITH (
  DELIMITER ',',
  FORMAT CSV,
  HEADER true,
  ESCAPE '"',
  ENCODING 'UTF-8'
);

Блокировки

Пессимистическая блокировка: Это стратегия, при которой вы блокируете запись для вашего исключительного использования, пока не закончите с ней. Он имеет гораздо лучшую целостность, чем оптимистическая блокировка, но требует от вас быть осторожным с дизайном приложения, чтобы избежать дедлоков.

Блокировки

Оптимистическая блокировка: Это стратегия, при которой Вы читаете запись, записываете номер версии (другие методы для этого включают даты, метки времени или checksums/hashes) и проверяете, что версия не изменилась, прежде чем записать запись обратно. Когда вы записываете запись обратно, вы фильтруете обновление по версии, чтобы убедиться, что оно атомарное. (т. е. не был обновлен между тем, когда вы проверяете версию и записываете запись на диск) и обновите версию одним ударом. Если запись грязная, то вы прерываете транзакцию, и пользователь может снова запустить ее.

Блокировки

Пессимистическая блокировка

BEGIN;
SELECT * FROM player WHERE id = 42 FOR UPDATE;
...
UPDATE player SET money = 100500 WHERE id = 42;
COMMIT;

Оптимистическая блокировка

BEGIN;
SELECT * FROM player WHERE id = 42;
COMMIT;
...
BEGIN;
UPDATE player SET money = 100500, ver = 13
WHERE id = 42 AND ver = 12;
COMMIT;

Ограничение времени ожидания

Долгий запрос:

UPDATE players SET spent_daily = 0;

Ждущий запрос:

BEGIN;
UPDATE player SET money = 100500 WHERE id = 42;
COMMIT;

Срочный запрос:

BEGIN;
SET LOCAL lock_timeout = '100ms';
UPDATE player SET money = 100500 WHERE id = 42;
COMMIT;

CREATE INDEX

Блокирующее создание индекса:

CREATE INDEX sales_quantity_index
  ON sales_table (quantity);

Неблокирующее создание индекса:

CREATE INDEX CONCURRENTLY sales_quantity_index
  ON sales_table (quantity);

ALTER TABLE

Классический вариант:

ALTER TABLE ratings ADD COLUMN created_new TIMESTAMPTZ NULL;
UPDATE ratings SET
  created_new = 'epoch'::TIMESTEMPTZ
              + created * INTERVAL '1 second';
ALTER TABLE ratings DROP COLUMN created;
ALTER TABLE ratings RENAME COLUMN created_new TO created;
ALTER TABLE ratings ALTER COLUMN created SET NOT NULL;

Сокращенный вариант:

ALTER TABLE ratings
  ALTER COLUMN created SET DATA TYPE TIMESTAMPTZ
  USING 'epoch'::TIMESTEMPTZ + created * INTERVAL '1 second';