1 / 38

Оптимизация запросов в СУБД PostgreSQL

1. Оптимизация запросов в СУБД PostgreSQL. Оптимизация запросов : : Структура тестовой базы данных 2. Структура тестовой базы данных. Таблица издательств:. Таблица изданных книг:. create table publishers ( id serial, name text not null, address text,

hayfa-brewer
Download Presentation

Оптимизация запросов в СУБД PostgreSQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 1 Оптимизация запросов в СУБД PostgreSQL

  2. Оптимизация запросов :: Структура тестовой базы данных 2 Структура тестовой базы данных Таблица издательств: Таблица изданных книг: create table publishers ( id serial, name text not null, address text, country text, owners text ); create table books ( id serial, title text not null, authors text, pages integer, price numeric (12, 2), description text, publisher_id integer, genre text, publication_date date ); Количество строк: 10 000 Размер 1 строки: ~95 байт Количество строк: 200 000 Размер таблицы: 1.5 МБ Размер 1 строки: ~1500 байт Размер таблицы: 313 МБ

  3. Оптимизация запросов :: Способы выполнения запроса 3 Способы выполнения запроса Исходный запрос: Вывести список стран, издательства которых выпускали книги в заданном жанре, указав количество выпущенных книг. Отсортировать список по убыванию количества выпущенных книг. SQL-запрос: select p.country, count (*) books_count from books b, publishers p where b.publisher_id = p.id and b.genre = 'Жанр 45' group by p.country order by 2 desc; Web-сервер

  4. Оптимизация запросов :: Способы выполнения запроса 4 Выполнение запроса в PgAdmin:

  5. Оптимизация запросов :: Способы выполнения запроса 5 Этапы выполнения запроса, запущенного в PgAdmin:

  6. Оптимизация запросов :: Способы выполнения запроса 6 Выполнение запроса в phpPgAdmin:

  7. Оптимизация запросов :: Способы выполнения запроса 7 Этапы выполнения запроса, запущенного в phpPgAdmin:

  8. Оптимизация запросов :: Способы выполнения запроса 8 Сравнение способов выполнения запроса(в мс): select * from books where price < 300; select * from books where price <= 40;

  9. Оптимизация запросов :: Способы выполнения запроса 9 Замеры времени выполнения запроса разными способами: Explain analyze (308 ms) MyApp.php (без fetch) (760 ms) PgAdmin (3 481 ms) MyApp.php (с fetch) (854 ms) phpPgAdmin (5 721 ms)

  10. Оптимизация запросов :: План выполнения запроса 10 План выполнения запроса План выполнения запроса –последовательность операций, необходимых для получения результата SQL-запроса в реляционной СУБД. Анализ сгенерированного СУБД плана выполнения запроса позволяет: • понять, как СУБД будет исполнять запрос; • найти «узкие» места в запросе, максимально влияющие на время выполнения запроса. Способы построения плана выполнения запроса: • использовать специальную SQL-команду:или • находясь в редакторе запросов клиента PgAdmin, нажать «F7» или «Shift+F7». explainQUERY_TEXT; (F7) (Shift+F7) explainanalyze QUERY_TEXT;

  11. Оптимизация запросов :: План выполнения запроса 11 Исходный запрос: Вывести список стран, издательства которых выпускали книги в заданном жанре, указав количество выпущенных книг. Отсортировать список по убыванию количества выпущенных книг SQL-запросдля получения плана выполнения исходного запроса: explain select p.country, count (*) books_count from books b, publishers p where b.publisher_id = p.id and b.genre = 'Жанр 45' group by p.country order by 2 desc; План исходного запроса:

  12. Оптимизация запросов :: План выполнения запроса 12 SQL-запросдля получения плана выполнения исходного запроса: explainanalyze select p.country, count (*) books_count from books b, publishers p where b.publisher_id = p.id and b.genre = 'Жанр 45' group by p.country order by 2 desc; План исходного запроса: Стоимость плана: 42978 Время выполнения запроса: 625 мс

  13. Оптимизация запросов :: Приёмы оптимизации запросов 13 Приёмы оптимизации запросов Соберём статистику о данных в таблицах: analyze publishers; analyze books; План исходного запроса после сбора статистики: Стоимость плана: 42970 Время выполнения запроса: 629 мс

  14. Оптимизация запросов :: Приёмы оптимизации запросов 14 Создадим первичные ключи для таблиц publishersи books: alter table publishers add primary key (id); alter table books add primary key (id); План исходного запроса после создания первичных ключей: Стоимость плана: 42970 Время выполнения запроса: 621 мс

  15. Оптимизация запросов :: Приёмы оптимизации запросов 15 Вопрос: Зачем мы создавали первичные ключи, если ничего не изменилось? Удалим первичный ключ для таблицы books: alter table books drop constraint books_pkey; Рассмотрим запрос: Получить все данные о книге с идентификатором 100123. select * from books where id = 100123; План запроса без первичного ключа для таблицы books: Стоимость плана: 42500 Время выполнения запроса: 595 мс Снова создадим первичный ключ для таблицы books: alter table books add primary key (id); План запроса c первичным ключом для таблицы books: Стоимость плана: 8 Время выполнения запроса: 0.02 мс

  16. Оптимизация запросов :: Приёмы оптимизации запросов 16 Вернёмся к исходному запросу. Создадим внешний ключ для таблицы books: alter table books add constraint books_publisher_id foreign key (publisher_id) references publishers (id); create index fki_books_publisher_id on books (publisher_id); План исходного запроса после создания внешнего ключа: Стоимость плана: 42970 Время выполнения запроса: 626 мс Эффект от создания внешних ключей – в обеспечении целостностиданных!

  17. Оптимизация запросов :: Приёмы оптимизации запросов 17 Индекс (index)–объект базы данных, создаваемый с целью повышения производительности поиска данных. Индекс состоит из: • значений одного или нескольких столбцов таблицы; • указателей на соответствующие этим значениям строки таблицы. Индекс позволяет искать строки, удовлетворяющие критерию поиска. books_pages_idx books Индекс B-tree: 45 75 20 10 20 55 90 Неиспользуемые индексы: • занимают место; • замедляют обновление.

  18. Оптимизация запросов :: Приёмы оптимизации запросов 18 Создадим индекс для колонки genre таблицы books: create index on books (genre); План исходного запроса после создания индекса: Стоимость плана: 6729 Время выполнения запроса: 13.9 мс

  19. Оптимизация запросов :: Приёмы оптимизации запросов 19 Снова соберём статистику о данных в таблицах: analyze publishers; analyze books; План исходного запроса после создания индекса и сбора статистики: Стоимость плана: 6846 Время выполнения запроса: 13.8 мс

  20. Оптимизация запросов :: Приёмы оптимизации запросов 20 Исходный запрос: select p.country, count (*) books_count from books b, publishers p where b.publisher_id = p.id and b.genre = 'Жанр 45' group by p.country order by 2 desc; Перепишем исходный запрос: select p.country, sum (b.cnt) books_count from publishers p inner join (select publisher_id, count (*) cnt from books where genre = 'Жанр 45' group by publisher_id ) b on b.publisher_id = p.id group by p.country order by 2 desc;

  21. Оптимизация запросов :: Приёмы оптимизации запросов 21 План выполнения переписанного запроса: Стоимость плана: 6715 Время выполнения запроса: 10.9 мс

  22. Оптимизация запросов :: Приёмы оптимизации запросов 22 Рассмотрим запрос: Вывести все данные о книгах, изданных в стране "Страна 5" за заданный период времени, отсортировав их по времени издания. SQL-запрос: select b.* from books b, publishers p where b.publisher_id = p.id and b.publication_date between '2000-01-01' and '2000-12-31' and p.country = 'Страна 5' order by b.publication_date; Рассмотрим 2 периода времени: • с 01.01.2000 по 31.12.2000; • с 01.01.2000 по 31.12.2010.

  23. Оптимизация запросов :: Приёмы оптимизации запросов 23 Стоимость плана: 43353 с 01.01.2000 по 31.12.2000 Период времени: Время выполнения запроса: 578 мс План выполнения запроса: Стоимость плана: 45892 с 01.01.2000 по 31.12.2010 Период времени: Время выполнения запроса: 638 мс

  24. Оптимизация запросов :: Приёмы оптимизации запросов 24 Создадим индексы для колонки publication_date таблицы books и для колонки country таблицы publishers: create index on publishers (country); create index on books (publication_date); Стоимость плана: 9813 с 01.01.2000 по 31.12.2000 Период времени: Время выполнения запроса: 10.4 мс План выполнения запроса:

  25. Оптимизация запросов :: Приёмы оптимизации запросов 25 Стоимость плана: 44038 с 01.01.2000 по 31.12.2010 Период времени: Время выполнения запроса: 548 мс План выполнения запроса:

  26. Оптимизация запросов :: Приёмы оптимизации запросов 26 Выполним горизонтальную фрагментацию таблицы books по странам, в которых издавались книги: create table books_country_5 as select * from books where publisher_id in (select id from publishers where country = 'Страна 5' ); Аналогичные запросы выполним для стран "Страна 1" – "Страна 10" Создаются таблицы books_country_1 – books_country_10 Перепишем запрос так, чтобы он работал с фрагментированной таблицей books: select b.* from books_country_5 b where b.publication_date between '2000-01-01' and '2000-12-31' order by b.publication_date;

  27. Оптимизация запросов :: Приёмы оптимизации запросов 27 Стоимость плана: 4319 с 01.01.2000 по 31.12.2000 Период времени: Время выполнения запроса: 13.1 мс План выполнения запроса: Стоимость плана: 6736 с 01.01.2000 по 31.12.2010 Период времени: Время выполнения запроса: 49.5 мс План выполнения запроса:

  28. Оптимизация запросов :: Приёмы оптимизации запросов 28 Создадим индекс для колонки publication_date таблицы books_country_5и проанализируем данные в ней: create index on books_country_5 (publication_date); analyze books_country_5; Стоимость плана: 957 с 01.01.2000 по 31.12.2000 Период времени: Время выполнения запроса: 1.1 мс План выполнения запроса:

  29. Оптимизация запросов :: Приёмы оптимизации запросов 29 Стоимость плана: 6576 с 01.01.2000 по 31.12.2010 Период времени: Время выполнения запроса: 38.2 мс План выполнения запроса:

  30. Оптимизация запросов :: Приёмы оптимизации запросов 30 Сравнение времени выполнения и стоимости плана для разных вариантов запроса: Вывести все данные о книгах, изданных в стране "Страна 5" за заданный период времени, отсортировав их по времени издания.

  31. Оптимизация запросов :: Приёмы оптимизации запросов 31 Рассмотрим запрос: Вывести названия всех издательств и количество книг, изданных в каждом из них. SQL-запрос: select p.name, b.cnt from publishers p, (select publisher_id, count (*) cnt from books group by publisher_id ) b where b.publisher_id = p.id;

  32. Оптимизация запросов :: Приёмы оптимизации запросов 32 План исходного запроса: Стоимость плана: 43859 Время выполнения запроса: 742 мс

  33. Оптимизация запросов :: Приёмы оптимизации запросов 33 Выполним вертикальную фрагментацию таблицы books: books Таблица books_main_info: Таблица books_description: create table books_description as select id, description from books; create table books_main_info as select id, title, authors, pages, price, publisher_id, genre, publication_date from books; Количество строк: 200 000 Размер 1 строки: ~1353 байт Количество строк: 200 000 Размер таблицы: 306 МБ Размер 1 строки: ~145 байт Размер таблицы: 35 МБ Перепишем запрос так, чтобы он работал с фрагментированной таблицей books: select p.name, b.cnt from publishers p, (select publisher_id, count (*) cnt from books_main_info group by publisher_id ) b where b.publisher_id = p.id;

  34. Оптимизация запросов :: Приёмы оптимизации запросов 34 План запроса, использующего фрагментированную таблицу books: Стоимость плана: 8396 Время выполнения запроса: 216 мс

  35. Оптимизация запросов :: Приёмы оптимизации запросов 35 Результат вертикальной фрагментации таблицы books: Вопрос: Можно ли ещё уменьшить время выполнения данного запроса? Денормализация структуры БД –намеренное приведение структуры базы данных в состояние, несоответствующее критериям нормализации. Цель денормализации – ускорение операций чтения из базы за счёт добавления избыточных данных и повышения риска нарушения целостности данных.

  36. Оптимизация запросов :: Приёмы оптимизации запросов 36 Создадим дополнительную колонку в таблице publishers, в которой будет храниться количество книг, изданных данным издательством: alter table publishers add column books_number integer; Заполним колонку books_number данными из таблицы books: I способ(простой, но медленный): update publishers p set books_number = (select count (*) from books b where b.publisher_id = p.id ); План запроса для I способа: Стоимость плана: 835747 Время выполнения запроса: 4 388 мс

  37. Оптимизация запросов :: Приёмы оптимизации запросов 37 II способ(более сложный, но быстрый): begin transaction; create temporary table books_numbers on commit drop as select publisher_id, count (*) books_count from books group by publisher_id; create index books_numbers_publisher_id_idx on books_numbers (publisher_id); analyze books_numbers; update publishers p set books_number = (select books_count from books_numbers where publisher_id = p.id ); commit; Время выполнения запроса: 1 517 мс

  38. Оптимизация запросов :: Приёмы оптимизации запросов 38 Перепишем оптимизируемый запрос так, чтобы он работал с денормализованной таблицей publishers: select p.name, p.books_number from publishers p; План выполнения запроса: Стоимость плана: 480 Время выполнения запроса: 3.9 мс

More Related