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

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

• Построение/удаление индексов;
• Модификация запросов;
• Нормализация/денормализация данных;
• Уменьшение времени блокировок (более короткие транзакции);
• Группировка UPDATE-запросов;
• Специальные средства для вставки данных.

Построение/удаление индексов

База для примера

• База с сайта: https://movielens.org/
• Объем в .csv-файлах: 613.3Mb:
  • links.csv: 0.7Mb
  • movies.csv: 1.6Mb
  • ratings.csv: 591Mb
  • tags.csv: 20Mb
• Размер базы данных: 858.2Mb:
  • genres: 19 rows, 0.016Mb
  • links: 34 560 rows, 1.5Mb
  • movie_genres: 65 736 rows, 2.5Mb
  • movie_tags: 585 984 rows, 24.5Mb
  • movies: 34 333 rows, 2.5Mb
  • rating: 20 150 654 rows, 822.0Mb
  • tags: 36 229 rows, 3.5Mb
• Запрос для примера: вывести все комедии про зомби по рейтингу.

Построение/удаление индексов

Начальный вариант:

# Query_time: 186.225664  Lock_time: 0.000000
# Rows_sent: 15  Rows_examined: 23 536 988
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 g.name = 'Comedy'
  and t.name = 'Zombie'
group by m.id, m.title
order by avg(r.rating) desc

Построение/удаление индексов

После создания индексов для Foreign Key:

# Query_time: 194.333533  Lock_time: 0.000000
# Rows_sent: 15  Rows_examined: 633 731
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 g.name = 'Comedy'
  and t.name = 'Zombie'
group by m.id, m.title
order by avg(r.rating) desc

Построение/удаление индексов

После создания покрывающего индекса по ratings:

# Query_time: 1.264987  Lock_time: 0.000000
# Rows_sent: 15  Rows_examined: 634 202
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 g.name = 'Comedy'
  and t.name = 'Zombie'
group by m.id, m.title
order by avg(r.rating) desc

Построение/удаление индексов

Итого:

• Без индексов: 186.22 sec, 23 536 988 rows
• С индексами FOREIGN KEY: 194.33 sec, 633 731 rows
• С покрывающим индексом: 1.26 sec, 634 202 rows

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

Что-то не похоже :(

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

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

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

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

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

# Query_time: 1.264987  Lock_time: 0.000000
# Rows_sent: 15  Rows_examined: 634 202
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 g.name = 'Comedy'
  and t.name = 'Zombie'
group by m.id, m.title
order by avg(r.rating) desc

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

Переосмысление запроса:

# Query_time: 14.323418  Rows_examined: 23 618 206 (без индексов)
# Query_time: 25.683016  Rows_examined: 81 575 (с индексами)
# Query_time:  0.178307  Rows_examined: 82 046 (с покрывающим индексом)

select m.id, m.title, m.cnt, avg(r.rating)
from (
  select m.id, m.title, count(*) as 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 g.name = 'Comedy‘
    and t.name = 'Zombie‘
  group by m.id, m.title
) m
join ratings r on (r.movie_id = m.id)
group by m.id, m.title, m.cnt
order by avg(r.rating) desc

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

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

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

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

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

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

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

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

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

create trigger after insert ...;
create trigger after update ...;
create trigger after delete ...;

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

После денормализации:

# Query_time: 0.015626  Lock_time: 0.000000
# Rows_sent: 15  Rows_examined: 813
select m.id, m.title, case
  when m.rating_cnt > 0
  then m.rating_sum / m.rating_cnt
  else null
end
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 g.name = 'Comedy'
  and t.name = 'Zombie'
group by m.id, m.title

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

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

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

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

До:

DELETE FROM work_logs
WHERE created_at > ADDDATE(DATE(NOW()), INTERVAL -90 DAY);

BEGIN;
DELETE FROM work_logs
WHERE created_at > ADDDATE(DATE(NOW()), INTERVAL -90 DAY)
LIMIT 10000;
COMMIT;

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

До:

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

  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 VARBINARY(64)
) ENGINE = MEMORY;
INSERT INTO items VALUES
  (1, 1001, 40477),
  (1, 1201, 40478),
  …
  (80, 7641, 70477);
UPDATE items I JOIN tmp_items t ON (i.res_id = t.res_id)
SET
  i.res_id = 73534,
  i.level = t.level,
  i.meta = t.meta;

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

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

load data local infile 'tags.csv'
into table movie_tags
fields
  terminated by ','
  enclosed by '"'
  escaped by ''
lines
  terminated by '\r\n'
ignore 1 lines
(user_id, movie_id, tag, @created)
set created = from_unixtime(@created);

Домашнее задание №3

К следующему рубежному контролю надо будет сделать задание №3.

К рубежному контролю будут допущены только те, кто сделал ДЗ №3.