Индексы на практике: как ускорить запросы и не наломать дров

Доклад посвящен оптимизации запросов в базах данных и эффективному использованию индексов. Мы разберем, что делать, если запросы начинают тормозить, научимся анализировать планы выполнения запросов с помощью команд EXPLAIN и EXPLAIN ANALYZE, и выясним, как индексы влияют на производительность. На простых и сложных примерах рассмотрим, как улучшить работу запросов, понять селективность индексов и избежать ошибок, ухудшающих производительность. В завершении обсудим, когда действительно стоит использовать индексы и как они могут стать вашим инструментом для оптимизации.

Transcript

1. Индексы на практике: 
 как ускорить запросы и не наломать дров
2. Содержание 1. Что делать, если начали тормозить запросы в базе данных? 2. Что такое индексы? 3. Где посмотреть, что индексы работают? 4. Примеры: + Попробуем что-нибудь оптимизировать + Индексы и несколько таблиц + Возьмем что-нибудь посложнее 5. Выводы: получается индексы — имба?
3. О спикере Алексей Романов + В индустрии более 13 лет + Основная специализация — разработка на Java и DevOps + Работаю в роли Sofware-архитектора в Т1 Иннотех уже более 4-х лет + Мы с вами встречались на других конференциях github.com/Romanow @romanowalex
4. 4 Всё тормозит «Футура́ма» © 20th Century Fox, 1999–н.в.
5. 5 Тормозят запросы в бд memepedia.ru/natash-my-tam-vsyo-uronili/
6. 6 Основы анализа запросов 
 Давай, Морти Оптимизация это легко «Рик и Мо́рти» © Warner Bros. Television, Netflix, Hulu, HBO Max, 2013–н.в.
7. 7 Как устроены индексы Индексы в базах данных — это структуры, 
 которые ускоряют поиск и доступ к данным в таблицах Они работают аналогично оглавлению в книге: вместо того чтобы перебирать все строки таблицы, база данных использует индекс для быстрого нахождения нужных данных
8. 8 Как устроены индексы 
 Индексы чаще всего реализуются с помощью b+ деревьев Это сбалансированные древовидные структуры, которые позволяют быстро находить данные 
 за время O(log n)
9. 9 Как работает индекс Индекс создается 
 для одного или нескольких 
 столбцов таблицы Когда выполняется запрос с условием WHERE, база данных использует индекс для быстрого нахождения строк, соответствующих условию Каждая запись в индексе содержит значение индексируемого столбца (или комбинации столбцов) и указатель на соответствующую строку в таблице
10. 10 Типы индексов 
 Single Column Index Индекс по одному столбцу 1 Multicolumn Index Индекс по нескольким столбцам 2 Unique Index Гарантируют, что все 
 значения в индексируемом столбце уникальны 3 Partial Index Индексируют только часть данных (условие WHERE) 4 GIN, GiST Используется 
 для сложных типов данных и полнотекстового поиска 5
11. 11 EXPLAIN (ANALYSE, VERBOSE, BUFFERS) SELECT * FROM main_table WHERE a = 1; Index Scan using idx_main_table_a on public.main_table (cost=0.29..8.31 rows=1 width=7) (actual time=0.016..0.041 rows=1 loops=1) Output: a, b, c Index Cond: (main_table.a = 1) Buffers: shared hit=3 Planning Time: 0.059 ms Execution Time: 0.058 ms Основы анализа запросов Стоимость чтения первой строки и всех строк Имя индекса Приблизительное количество возвращаемых строк Средний размер одной строки в байтах
12. 12 «Гу́бка Боб Квадра́тные Штаны ́ » © Paramount International Networks, 1999–н.в.
13. 13 «Разочарование» © Netflix, 2018–2023
14. 14 Статистика Оптимизация запросов основана на статистике. Когда вы выполняете запрос, оптимизатор выбирает наилучший план выполнения на основе собранной статистики Например, если в таблице много записей, но столбец имеет малое количество уникальных значений, использование индекса может оказаться неэффективным 
 (т. к. низкая селективность), и оптимизатор выберет полный скан таблицы
15. 15 Статистика Статистика — это информация, 
 которую сама база данных собирает о содержимом таблиц и индексов Основными элементами 
 статистики являются + Количество строк в таблице + Количество уникальных значений (кардинальность) + Распределение значений в столбцах (гистограммы) Она помогает оптимизатору запросов принимать решения о том, какой способ выполнения запроса наиболее эффективен
16. Пример 1: 
 Одна таблица
17. 17 CREATE TABLE users ( id SERIAL PRIMARY KEY, login VARCHAR NOT NULL, password VARCHAR NOT NULL, active BOOLEAN DEFAULT TRUE, created_date TIMESTAMP NOT NULL ); EXPLAIN ANALYSE SELECT * FROM users u WHERE u.created_date > '2024-11-01' AND u.active = TRUE; Простой пример
19. 19 EXPLAIN ANALIZE SELECT * FROM users u WHERE u.created_date > '2024-11-01' AND u.active = TRUE; CREATE INDEX idx_users_created_date ON users (created_date); Простой пример
21. 21 EXPLAIN ANALIZE SELECT * FROM users u WHERE u.created_date > '2024-11-01' AND u.active = TRUE; CREATE INDEX idx_users_active ON users (active); Простой пример
23. 23 EXPLAIN ANALIZE SELECT * FROM users u WHERE u.created_date > '2024-11-01' AND u.active = TRUE; DROP INDEX idx_users_active; DROP INDEX idx_users_created_date; CREATE INDEX idx_users_created_date_and_active ON users (created_date, active) WHERE active = TRUE; Простой пример
25. 25 EXPLAIN ANALIZE SELECT u.login FROM users u WHERE u.created_date > '2024-11-01' AND u.active = TRUE; DROP INDEX idx_users_created_date_and_active; CREATE INDEX idx_users_created_date_and_active ON users (created_date, active) INCLUDE (login); Простой пример
27. 27 Как пользоваться индексами Если вы пишете запрос, отличный от findAll, findById, то, скорее всего, вам нужно 
 создать индекс на поле На PRIMARY KEY индекс 
 строится автоматически Столбец в индексе должен иметь 
 хорошую селективность Нужно стремиться к использованию 
 INDEX ONLY SCAN. Но это не значит, 
 что нужно пихать в индекс всю таблицу
28. 28 Как пользоваться индексами В случае, если нужно строить по двум (и более) полям, то сначала должно идти поле с большой селективностью, а потом второе. Например: 
 createdDate и status В большинстве случаев отдельные индексы на поля лучше объединенных индексов. Исключение — 
 зависимые поля При объединении 
 двух таблиц (JOIN) всегда создавать индекс
29. Пример 2: 
 несколько таблиц
30. 30 CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT CONSTRAINT fk_orders_user_id REFERENCES users (id), amount INT NOT NULL, product VARCHAR NOT NULL, purchase_date TIMESTAMP NOT NULL ); Виды join'ов
31. 31 EXPLAIN ANALYSE SELECT u.login, o.amount, o.product, o.purchase_date FROM orders o JOIN users u ON o.user_id = u.id WHERE o.purchase_date > '2024-01-01'; Hash join
33. 33 Hash join Метод хеш-соединения использует создание 
 хэш-таблицы для одного набора данных 
 и затем сопоставление с другим набором Этот метод эффективен при больших объемах данных
34. 34 SELECT u.login, o.amount, o.product, o.purchase_date FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_date > (NOW() - INTERVAL '1 month'); Merge join
35. 35 sorted by index quick sort
36. 36 Merge join Метод слияния работает путем сортировки обеих таблиц по ключу соединения и последующего прохода по ним одновременно Это эффективно, когда обе таблицы уже отсортированы
37. 37 EXPLAIN ANALYSE VERBOSE SELECT u.login, o.amount, o.product, o.purchase_date FROM orders o JOIN users u ON o.user_id = u.id WHERE o.purchase_date > NOW() - INTERVAL '12 hour'; Nested loop
39. 39 Nested loop Он выполняет вложенный цикл через обе таблицы, сравнивая каждую строку одной таблицы со всеми строками другой Этот метод соединения подходит для небольших наборов данных
40. 40 Виды join'ов Критерий Hash join Merge join Nested loop
41. 41 Виды join'ов Критерий Hash join Merge join Nested loop Условия использования Подходит для больших таблиц, где нет индексов или данные не отсортированы Эффективен, когда обе 
 таблицы отсортированы 
 по ключу соединения Подходит для маленьких таблиц или когда одна таблица 
 значительно меньше другой
42. 42 Виды join'ов Критерий Hash join Merge join Nested loop Условия использования Подходит для больших таблиц, где нет индексов или данные не отсортированы Эффективен, когда обе 
 таблицы отсортированы 
 по ключу соединения Подходит для маленьких таблиц или когда одна таблица 
 значительно меньше другой Скорость Быстрый для больших объемов данных, если хэш-таблица помещается в память Быстрый для больших отсортированных данных Быстрый для маленьких таблиц
43. 43 Виды join'ов Критерий Hash join Merge join Nested loop Условия использования Подходит для больших таблиц, где нет индексов или данные не отсортированы Эффективен, когда обе 
 таблицы отсортированы 
 по ключу соединения Подходит для маленьких таблиц или когда одна таблица 
 значительно меньше другой Скорость Быстрый для больших объемов данных, если хэш-таблица помещается в память Быстрый для больших отсортированных данных Быстрый для маленьких таблиц Память Требует много памяти 
 для построения хэш-таблицы Требует меньше памяти, чем Hash Join, но данные должны быть отсортированы Минимальные требования к памяти
44. 44 Виды join'ов Критерий Hash join Merge join Nested loop Условия использования Подходит для больших таблиц, где нет индексов или данные не отсортированы Эффективен, когда обе 
 таблицы отсортированы 
 по ключу соединения Подходит для маленьких таблиц или когда одна таблица 
 значительно меньше другой Скорость Быстрый для больших объемов данных, если хэш-таблица помещается в память Быстрый для больших отсортированных данных Быстрый для маленьких таблиц Память Требует много памяти 
 для построения хэш-таблицы Требует меньше памяти, чем Hash Join, но данные должны быть отсортированы Минимальные требования к памяти Сложность O(N + M) N и M — размеры таблиц O(N*log(N) + M*log(M)) 
 для сортировки O(N + M) для соединения O(N * M) N и M — размеры таблиц
45. 45 Как оптимизировать join Изменение индексов может кардинально изменить план выполнения, поэтому важно тестировать производительность 
 обоих вариантов на реальных данных
46. Пример 3: 
 cложный запрос
47. 47 SELECT u.login, SUM(o.amount) FROM users u JOIN orders o ON o.user_id = u.id WHERE o.purchase_date BETWEEN NOW() - INTERVAL '1 day' AND NOW() GROUP BY u.login; Совсем сложный пример
49. 49 Совсем сложный пример Индексы не всегда единственный 
 пример оптимизации. Иногда полезнее переписать запрос
50. 50 SELECT (SELECT u.login FROM users u WHERE u.id = o.user_id), SUM(o.amount) FROM orders o WHERE o.purchase_date BETWEEN NOW() - INTERVAL '1 day' AND NOW() GROUP BY o.user_id; SELECT u.login, SUM(o.amount) FROM users u JOIN orders o ON o.user_id = u.id WHERE o.purchase_date BETWEEN NOW() - INTERVAL '1 day' AND NOW() GROUP BY u.login; Совсем сложный пример
52. 52 Индексы — это имба? memepedia.ru/kachok-doge-i-chims/ Запрос с индексами Индексы не использую
53. 53 Индексы — это имба? Если стоимость вычисления с использованием индекса будет больше, чем обычный обход таблицы (низкая селективность) Типы полей в запросе должны в точности совпадать с типами в индексе Если большая выборка, а work_mem маленький, то планировщик выбирает SeqScan по таблице Порядок полей в индексе важен: если индекс построен по A, B, то если в запросе фигурирует только B, то индекс не будет использован
54. Спасибо 
 за внимание Ваши лайки @romanowalex github.com/Romanow