Share
Embed
Индексируем базу: как делать хорошо и не делать плохо
Andrey Novikov
December 02, 2021
Transcript
58 slides
-
Индексируем базу как делать хорошо и не делать плохо Новиков
Андрей Winter Saint P 2021 meetup -
Марсианский 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… -
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… -
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… -
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