Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Индексируем базу: как делать хорошо и не делать плохо

Индексируем базу: как делать хорошо и не делать плохо

Andrey Novikov

December 02, 2021
Tweet

More Decks by Andrey Novikov

Other Decks in Programming

Transcript

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

    View full-size slide

  2. Марсианский 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  17. Подстава!
    В 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  21. Многоколоночные индексы
    число столбцов ограничено. 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) -- нужен!

    View full-size slide

  22. Покрывающие индексы
    Можно добавить лишних колонок в индекс, чтобы не пришлось ходить в таблицу, а
    всё сразу достать из индекса*
    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
    ` `

    View full-size slide

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

    View full-size slide

  24. Частичные индексы
    Дано:
    Создадим два индекса: простой и частичный — с условием 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;

    View full-size slide

  25. Частичные индексы
    Помним, что 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)

    View full-size slide

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

    View full-size slide

  27. Использование нескольких индексов
    Если в запросе идёт отбор по нескольким проиндексированным колонкам, то
    планировщик может построить несколько битовых карт и логически их
    объединить.
    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…

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  30. 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…

    View full-size slide

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

    View full-size slide

  32. 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…

    View full-size slide

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

    View full-size slide

  34. 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…

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  37. 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…

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  40. 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…

    View full-size slide

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

    View full-size slide

  42. 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…

    View full-size slide

  43. Экзотика
    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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  49. Как не пропустить плохую миграцию?
    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

    View full-size slide

  50. Как не пропустить плохую миграцию?
    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

    View full-size slide

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

    View full-size slide

  52. Что почитать?
    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

    View full-size slide

  53. 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:
    ☠️ (алло, вы уже должны были обновиться с неё!)
    ` `

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide