Индексируем базу как делать хорошо и не делать плохо Новиков Андрей Winter Saint P 2021 meetup

No content

No content

Марсианский Open Source Yabeda: Ruby application instrum entation fram ework Lefthook: git hooks m anager AnyCable: Polyglot replacem ent for ActionCable server PostCS S : A tool for transform ing CS S with JavaS cript Im gproxy: Fast and secure standalone server for resizing and converting rem ote im ages Logux: Client-server com m unication fram ework based on Optim istic UI, CRDT, and log Overm ind: Process m anager for Procfile-based applications and tm ux И многие другие на evilmartians.com/oss

Зачем мне знать про индексы? Данные — главная ценность большинства приложений Традиционные СУБД плохо масштабируются горизонтально. Так сложилось, что разработчики на Ruby on Rails разрабатывают фичи целиком (включая модель данных) И не всегда есть DBA под рукой Полезно понимать, как оно работает и что может, чтобы не положить продакшен раньше времени. А главное — нафига оно всё Ruby-разработчику?

Disclaimer Доклад будет про PostgreSQL, но многие вещи справедливы и для других SQL (и даже порой NoSQL) систем управления базами данных.

Что ещё за индексы? Может, не надо, а?

Зачем нужны индексы? SQL — декларативный язык. Пользователь говорит, что хочет получить, СУБД определяет, как выполнять запрос. И мы можем ей помочь, дав вспомогательные «словари»!

И что же такое индекс? Вторичная структура данных, которая не влияет на результат выполнения запросов*, но может ускорить его выполнение. Ещё индексы поддерживают ограничения целостности (первичные и уникальные ключи). * Наличие индекса может поменять порядок возвращаемых данных, если в запросе не указан ORDER BY, а планировщик решил использовать индекс.

Почему не заиндексировать всё? Индексы занимают место Индексы нужно обновлять замедление вставок и обновлений write amplification полезны не всегда планировщик может не захотеть их использовать новый индекс может оказаться медленнее старого

Всегда ли стоит использовать индекс? Ну, нет

А когда не стоит? Если размер таблицы мал* (сотни/тысячи записей) и она не будет расти Или если запрос выполняется редко и скорость его выполнения не критична Если большинство строк отбрасываются другими условиями (мультитенантные таблицы) * Локально приходится использовать SET enable_seqscan = off; , чтобы заставить СУБД использовать индекс. ` `

А когда стоит? Когда ускорение от индекса перевешивает накладные расходы от похода в индекс 1. Когда надо выбрать небольшое количество данных от размера таблицы 2. Результат нужно отсортировать Когда индекс ещё помещается в оперативную память, а таблица — уже нет Когда ожидается, что количество данных в таблице будет расти Или это внешний ключ, по которому будут выполняться JOINы

Падажжи когда надо выбрать небольшое количество данных относительно размера таблицы А как СУБД это понимает? Статистика! СУБД знает о каждой колонке: количество данных их упорядоченность (корреляция) количество разных значений их количественное распределение (гистограмма) И использует это при планировании запросов. Статистика безумно важна!

Как может выполнять запрос PostgreSQL seq scan - обойти всю табличку на диске, страница за страницей index only scan - достать все данные только из индекса index scan - сходить в индекс, сходить в страницы на диске bitmap index scan + heap scan — сходить в индекс, построить битовую карту интересных страниц, загрузить их все, отфильтровать… Как выбирает? Планировщик перебирает варианты выполнения считает примерную стоимость каждого (с учётом статистики) выбирает самый дешёвый (как ему кажется)

Seq scan Последовательно читаем всю таблицу, сразу отдаём данные клиенту пачками (используйте курсоры). Просто, хорошо работает на HDD, линейное время. Если данные нужно отсортировать — становится хуже.

Index scan Ищем требуемые данные в индексе, смотрим, где они лежат на диске, идём на диск, повторяем. Логарифмическое время, чтобы найти данные в индексе, линейное — чтобы считать. В случае стандартного BTree-индекса результат будет уже отсортирован.

Заглянем в индекс (BTree) Источник: Postgres Professional: курс «Оптимизация запросов», тема №4 «Индексный доступ». Подробнее: Индексы в PostgreSQL — 4

Подстава! В PostgreSQL даже в самой последней 14-й версии настройки планировщика по умолчанию заточены под работу на HDD: seq_page_cost (floating point) Sets the planner’s estimate of the cost of a disk page fetch that is part of a series of sequential fetches. The default is 1.0. random_page_cost (floating point) Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0. Это значит, что планировщик будет чаще выбирать seq scan при живом-то индексе! Используйте PgTune! ` ` ` ` pgtune.leopar d.in.ua

Bitmap index scan Строится промежуточная битовая карта, чтобы читать страницы по одному разу. Источник: Postgres Professional: курс «Оптимизация запросов», тема №5 «Сканирование по битовой карте». Подробнее: Индексы в PostgreSQL — 4

Когда что используется? Источник: Postgres Pro: курс «Оптимизация запросов», тема №5 «Сканирование по битовой карте».

Index only scan Если все данные, нужные для запроса, лежат в индексе (и MVCC-звёзды сходятся), то можно даже не ходить на диск! Но как этого достичь? Спойлер: избегайте SELECT * ` `

Многоколоночные индексы число столбцов ограничено. 32 BTree, GIN, GiST, SP-GiST Порядок столбцов важен! CREATE INDEX ON users(account_id, age) SELECT * FROM users WHERE account_id = 1 AND age < 30 SELECT * FROM users WHERE account_id = 1 CREATE INDEX ON users(account_id) -- не нужен! CREATE INDEX ON users(age) -- нужен!

Покрывающие индексы Можно добавить лишних колонок в индекс, чтобы не пришлось ходить в таблицу, а всё сразу достать из индекса* PostgreSQL 11+ добавил поддержку INCLUDE , которая позволяет добавлять доп. данные в уникальные индексы: Особенно полезно включать первичный ключ, что позволяет ускорять множественные JOIN’ы. В ActiveRecord фичу не добавили, а вот отдельный гем уже есть: activerecord-covering-index . ` ` CREATE UNIQUE INDEX index_fk_on_listings ON listings (`product_id`, site_id) INCLUDE (`id`); SELECT * FROM products JOIN listings ON products.id = `listings.product_id` JOIN listing_variations ON `listings.id` = listing_variations.listing_id ` `

Размер имеет значение Чем меньше индекс, тем более он «привлекателен» для планировщика.

Частичные индексы Дано: Создадим два индекса: простой и частичный — с условием WHERE item_id IS NOT NULL (в таблице заполнено примерно 10% строк): И… 🥁 class Listing < ActiveRecord::Base scope :published, -> { where.not(item_id: nil) } end ` ` CREATE INDEX index_listings_on_item_id ON listings (item_id); CREATE INDEX index_listings_on_item_id_not_null ON listings (item_id) WHERE item_id IS NOT NULL;

Частичные индексы Помним, что NULL != NULL , да? Size | 290 MB Size | 38 MB example_database=# \di+ index_listings_on_* -[ RECORD 1 ]-+---------------------------------- Name | index_listings_on_item_id Table | listings Access method | btree -[ RECORD 2 ]-+---------------------------------- Name | index_listings_on_item_id_not_null Table | listings Access method | btree ` ` example_database=# EXPLAIN SELECT * FROM listings WHERE item_id = '100500'; Index Scan using index_listings_on_item_id_not_null on listings (cost=0.14..8.16 rows=1 width=373) Index Cond: ((item_id)::text = '100500'::text)

Функциональные индексы позволяют ускорять запросы с точно таким же выражением в SELECT, WHERE или ORDER BY позволяют как бы «закэшировать» производное значение есть своя отдельная статистика «дорогое» обслуживание можно использовать самописные функции (они должны быть IMMUTABLE ) CREATE UNIQUE INDEX username_constraints ON users (`LOWER(username)`); ` `

Использование нескольких индексов Если в запросе идёт отбор по нескольким проиндексированным колонкам, то планировщик может построить несколько битовых карт и логически их объединить. habr.com/ru/company/postgrespro/blog/326096 CREATE INDEX on t(a); CREATE INDEX on t(b); ANALYZE t; EXPLAIN SELECT * FROM t WHERE a <= 100 AND b = 'a'; Recheck Cond: ((a <= 100) AND (b = 'a'::text)) -> BitmapAnd -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) -> Bitmap Index Scan on t_b_idx Index Cond: (b = 'a'::text) habr .com/r u/company/po…

Я слышал, они разные бывают BTree, Hash, GIN, GiST, SP-GiST, BRIN, Bloom, RUM, что, зачем, почему?

BTree cбалансированное дерево сильно ветвистое (много дочерних узлов) двусвязный список из листьев одного уровня habr.com/ru/company/postgrespro/blog/330544

BTree Сложность поиска: O(log(n)) Операции: < , <= , = , >= , > , IN , BETWEEN , IS NULL , IS NOT NULL , LIKE 'smth%' , ~ '^smth' Бонус: ускоряет ORDER BY , поддерживает уникальность Для первичных ключей лучше работает в случае последовательных id и хуже — в случае случайных UUID. habr.com/ru/company/postgrespro/blog/330544 ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` habr .com/r u/company/postgr espr o/b…

Hash Сложность поиска: O(1) Операции: только = Компактнее, чем B-Tree, но быстрее только на очень больших таблицах и на выборках единичных значений. Не поддерживает уникальность :-( Осторожно! В бою можно использовать только начиная с PostgreSQL 10 (но это самая старая версия из поддерживаемых) Подробнее: habr.com/ru/company/postgrespro/blog/328280 ` `

GIN Generalized Inverted Index Внутри BTree, содержащее составные значения (пример: лексемы слов) и список/дерево со ссылками на строки Подходит для составных объектов, которые бьются на простые составляющие Может расширяться под различные типы данных Operations hstore: @> , ? , ?& , ?| jsonb: @> , ? , ?& , ?| array: <@ , @> , = , && habr.com/ru/company/postgrespro/blog/340978 ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` habr .com/r u/company/postgr espr o/b…

GIN Источник: habr.com/ru/company/postgrespro/blog/340978

GiST Generalized Search Tree, настраиваемое Инфраструктура под разные типы данных и операторов (B-trees, R-trees) Геометрически данные (box, circle, point, polygon), полнотекстовый поиск (tsquery, tsvector) Не включает в себя данные, но включает в себя предикат, которому данные удовлетворяют Operations: << , >> , ~= , <^ , >^ , <-> , <@ , @> Для поддержки = , < , > нужно включать расширение btree_gist Поддерживает exclusion constraint (как unique, но на интервалах) habr.com/ru/company/postgrespro/blog/333878/ ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` ` habr .com/r u/company/postgr espr o/b…

GiST Источник: habr.com/ru/company/postgrespro/blog/333878/

GiST Источник: habr.com/ru/company/postgrespro/blog/333878/

SP-GiST Space-Partitioned GiST Инфраструктура для несбалансированных многомерных непересекающихся структур Префиксные деревья и прочие естественно непересекающиеся штуки Может быть значительно компактнее BTree для префиксного поиска Внутри себя хранит метки и префиксы, по которым идёт поиск PG 9.2+ Подробнее: habr.com/ru/company/postgrespro/blog/337502 habr .com/r u/company/postgr espr o/b…

SP-GiST Источник: habr.com/ru/company/postgrespro/blog/337502

SP-GiST Источник: habr.com/ru/company/postgrespro/blog/337502

BRIN Block Range Index Очень большие таблицы, естественно сортированные Хранит MIN и MAX значений колонки в каждой странице начиная с PG 14 хранит несколько интервалов Очень компактный и дешёвый в обслуживании PG 9.5+ Подробнее: habr.com/ru/company/postgrespro/blog/346460 habr .com/r u/company/postgr espr o/b…

Bloom Строятся битовые сигнатуры по хэш-функции от значений Вероятностный индекс с нестабильной производительностью Индекс даёт false positives и лишние чтения с диска Поддерживается только = , но можно искать по многим колонкам Больше, чем BRIN, но компактнее чем Hash Компромиссное решение для очень больших таблиц Нужно подбирать параметры PG 9.6+ Подробнее: habr.com/ru/company/postgrespro/blog/349224 ` ` habr .com/r u/company/postgr espr o/b…

RUM GIN 2.0 вместе с номерами строк хранится дополнительная информация, например, расстояние между словами Можно считать релевантность прямо в индексе и делать фразовый поиск (когда порядок слов важен) Нет в стандартной поставке — нужно ставить отдельно. PG 9.6+ Исходники: github.com/postgrespro/rum Подробнее: https://habr.com/ru/company/postgrespro/blog/343488 habr .com/r u/company/postgr espr o/b…

Экзотика ZomboDB — ElasticSearch как индекс в БД CREATE EXTENSION zombodb; CREATE INDEX idxproducts ON products USING zombodb ((products.*)) WITH (url='localhost:9200/'); SELECT * FROM products WHERE products ==> '(keywords:sports keywords:OR keywords:box OR long_description:"wooden away"~5) AN github.com/zombodb/zombodb

Итого BTree — Для большинства типов и запросов GIN — Для JSONB/hstore/arrays/полнотекстового поиска GiST — Для геометрии/географии/exclusion constraints/полнотекстового поиска SP-GiST — Для геометрии/географии/exclusion constraints/префиксного поиска Hash — Для = , но стоит ли? BRIN — для больших естественно-отсортированных (write-only) таблиц Bloom — для поиска по многим колонкам в огромных таблицах RUM — для фразового полнотекстового поиска ` `

Индексы в миграциях или тысяча и всего один способ положить продакшен (зато какой!)

А в чём проблема в миграциях? Блокировки! Создание индекса в PG блокирует таблицу на запись целиком. Запросы, которые хотят записать в эту же таблицу встают в очередь за блокировкой… Пока все пулы соединений не исчерпаются. Вуаля! Мы лежим! 🤡 Транзакционный DDL — благо, но блокировки отпускаются только в конце транзакции.

А что делать? Отказаться от транзакционного DDL и создавать индексы конкурентно. disable_ddl_transaction! add_index :big_table, %i[column], algorithm: :concurrently class AddMissingIndexes < ActiveRecord::Migration[6.1] def change end end

Всё равно всё встаёт «колом»? CREATE INDEX CONCURRENTLY всё равно берёт блокировку всей таблицы, чтобы её тут же отпустить, но если в таблицу активно пишут другие долгие транзакции, то следом за ждущим блокировки CREATE INDEX скопятся очередь из других запросов и… 💥 Хак решение Снимайте нагрузку перед накаткой любых миграций на нагруженные таблицы — останавливайте Sidekiq, … И избегайте долгих транзакций! Гем isolator вам в помощь! ` ` ` ` gem isolator

Как не пропустить плохую миграцию? 1. Используйте линтеры: Анализирует ваши миграции и подскажет, когда они могут быть опасны на продакшене. github.com/ankane/strong_migrations gem "strong_migrations" === Dangerous operation detected #strong_migrations === Adding an index non-concurrently blocks writes. Instead, use: class AddMissingIndexes < ActiveRecord::Migration[6.1] disable_ddl_transaction! def change add_index :table, :column, algorithm: :concurrently end end gem str ong_migr ations

Как не пропустить плохую миграцию? 2. Гоняйте миграции на CI: command: bundle exec rails db:create db:migrate db-linters: steps: - attach_workspace: at: . - run: name: Backup db/structure.sql to compare for changes later command: cp -f db/structure.sql{,.bak} - run: name: Check that migrations are not broken - run: name: Check that db/structure.sql is up to date (there should be no changes) command: git diff --exit-code --no-index -- db/structure.sql{.bak,} - run: name: Ensure seeds are valid command: bundle exec rails db:seed

Итого: «правила буравчика» 👍 1. Накладывайте индексы на внешние ключи Хорошая новость: add_reference в миграциях уже их добавляет Для таблиц, используемых в JOIN’ах «посередине», добавляйте первичный ключ в индекс для index only scan. 2. Делайте частичные индексы, если большинство строк в таблице ожидаются пустыми. 3. Не добавляйте индексы для OLAP-запросов (построение отчётов по всем данных и т.п.) 4. Удаляйте дублирующие и неиспользуемые индексы * * wiki.postgresql.org/wiki/Index_Maintenance ` ` PG Index Maintenance

Что почитать? 1. The Art of PostgreSQL: theartofpostgresql.com 2. Use the Index, Luke: use-the-index-luke.com 3. Postgres Professional: курс «Оптимизация запросов» postgrespro.ru/education/courses/QPT 4. Цикл статей «Индексы в PostgreSQL» habr.com/ru/company/postgrespro/blog/326096 1. The Ar t of Postgr eSQL 2. Use the Index, Luke 3. Оптим изация запросов 4. Ин дексы в Postgr eSQL

P.S> Обновляйтесь! PostgreSQL 14: уменьшение распухания BTree, улучшение BRIN PostgreSQL 13: дедупликация значений в BTree (круто для foreign key) PostgreSQL 12: покрывающие GiST-индексы, REINDEX CONCURRENTLY PostgreSQL 11: покрывающие индексы (кроме GiST), партиционированные индексы PostgreSQL 10: параллельное сканирование BTree, полноценные Hash-индексы PostgreSQL 9.6: ☠️ (алло, вы уже должны были обновиться с неё!) ` `

Минутка нативной рекламы Это (и многое-многое другое) мы можем в вас насильно впихнуть под давлением на наших фирменных курсах Brainwashing. В последний раз я рассказывал про базы данных 4 (четыре) часа подряд. Проведём весной, если эпидемиологическая ситуация позволит 🤞 Записывайтесь в добровольцы на brainwashing.pro/rails. br ainwashing.pr o/r ails

Нам нужны твои мозги! Пункт призыва на галактическую службу по контракту: evl.ms/jobs

Внимание! Внимание! Спасибо за внимание! @Envek @Envek @Envek @Envek github.com/Envek @evilmartians @evilmartians_ru @evil.martians Вакансии: evilmartians.com/jobs Блог: evilmartians.com/chronicles Evil Mar tians: links