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

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

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

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

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

Таблица	Кортежей	Размер
genres	19	48 kB
links	40 110	2 944 kB
movie_genres	74 229	4 856 kB
movie_tags	668 953	49 152 kB
movies	40 110	3 400 kB
rating	24 404 096	1 971 200 kB
genres	49 657	5 344 kB

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

Запрос для примера: вывести все комедии про зомби по рейтингу.

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

Начальный вариант (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 films;

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

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

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

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

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

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

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 Aanalytical Processing): Технология обработки данных, заключающаяся в подготовке суммарной (агрегированной) информации на основе больших массивов данных, структурированных по многомерному принципу.

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

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

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

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

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

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

Проблемы:

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

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

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

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

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

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

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

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

CREATE TABLE test_1 PARTITION OF test FOR VALUES FROM (100) TO (199);
CREATE TABLE test_2 PARTITION OF test FOR VALUES FROM (200) TO (299);

CREATE INDEX ON test_1 (id);
CREATE INDEX ON test_2 (id);

Секционирование: 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;

Модификация схемы

До:

BEGIN;
  INSERT INTO spent_daily
  SELECT :day, level, SUM(spent_daily) FROM players
  GROUP BY level FOR UPDATE;

  UPDATE players SET spent_daily = 0;
END;

UPDATE players SET
  spent_day = spent_day + :delta,
  money = money - :delta
WHERE id = :id;

Модификация схемы

После:

BEGIN;
  INSERT INTO spent_daily
  SELECT :day, level, SUM(CASE
    WHEN spent_day = :day THEN spent_prev
    WHEN spent_day = :day - 1 THEN spent_last
  END) FROM players;
END;

UPDATE players SET
  spent_prev = CASE
    WHEN spent_day = :day THEN spent_prev
    WHEN spent_day = :day - 1 THEN spent_last
    ELSE 0
  END,
  spent_last = CASE
    WHEN spent_day = :day THEN spent_last ELSE 0
  END + :delta,
  spent_day = :day,
  money = money - :delta
WHERE id = :id;

Группировка UPDATE

Массовая замена сгенерированных ресурсов на один с мета-данными.

Кол-во ресурсов: ~30K записей.
Размер таблицы: ~10М записей.

Группировка UPDATE

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

UPDATE items SET res_id = 73534, level = 1, meta = 1001
WHERE res_id = 40477;
UPDATE items SET res_id = 73534, level = 1, meta = 1201
WHERE res_id = 40478;
UPDATE items SET res_id = 73534, level = 2, meta = 1031
WHERE res_id = 40479;
…
UPDATE items SET res_id = 73534, level = 80, meta = 7641
WHERE res_id = 70477;

Группировка UPDATE

Более красивое решение:

CREATE TEMPORARY TABLE tmp_items (
  res_id INT,
  level  SMALLINT,
  meta   BYTEA
);
INSERT INTO items VALUES
  (1, 1001, 40477),
  (1, 1201, 40478),
  …
  (80, 7641, 70477);
UPDATE items i
SET
  res_id = 73534,
  level = t.level,
  meta = t.meta
FROM tmp_items t
WHERE i.res_id = t.res_id;

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

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

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'
);

Блокировки

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

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';