Індекси SQLExplained

31 of 31 rows

Поняття↕	Пояснення↕	Порада↕
Типи індексів
⤢B-tree	Збалансоване дерево для перевірок рівності, діапазонів і відсортованих проходів. Типове для більшості баз даних.	Використовуйте для =, <, >, BETWEEN, ORDER BY. Не для повнотекстового пошуку.
⤢Clustered	Індекс, чиїм листковим рівнем є сама таблиця. Рядки зберігаються в порядку ключа. Один на таблицю.	InnoDB кластеризує за первинним ключем. Таблиці SQL Server без нього — купи (heap).
⤢Nonclustered	Окрема структура з ключами та вказівником назад на базову таблицю. Багато на таблицю.	На купі зберігає ідентифікатор рядка. На кластеризованій таблиці — ключ кластеризації.
⤢Hash	Хеш-таблиця лише для пошуку за рівністю. Точний збіг за сталий час.	Використовуйте лише для операцій =; діапазони й сортування потребують B-tree. Хеш для memory-optimized таблиць SQL Server вимагає кількості кошиків, близької до кількості рядків.
⤢GIN	Узагальнений інвертований індекс. Відображає значення на рядки. Створений для масивів і повного тексту.	Використовуйте для jsonb, масивів, tsvector, триграмного пошуку. Повільніший у побудові за B-tree.
⤢GiST	Узагальнене дерево пошуку. Розширюваний для нескаллярних типів і запитів на перекриття.	Використовуйте для геометричних діапазонів, kNN, обмежень виключення. pg_trgm прискорює LIKE.
⤢SP-GiST	GiST із розбиттям простору. Ділить дані на неперекривні області через radix- або quad-дерева.	Використовуйте для точок, IP-префіксів і пошуку за префіксом. Не для запитів на перекриття; для них беріть GiST.
⤢BRIN	Індекс діапазонів блоків. Зберігає min/max на діапазон блоків. Крихітний слід.	Використовуйте на великих фізично відсортованих таблицях. Дешевий у побудові, слабка селективність.
⤢Columnstore	Індекс, збережений як сегменти стовпців замість рядків. Висока компресія, пакетне виконання.	Використовуйте для аналітичних проходів багатьма рядками і небагатьма стовпцями. Пошук одного рядка повільний.
⤢Full-text	Окремий інвертований словниковий індекс у MySQL і SQL Server. Будується на текстовий стовпець.	Використовуйте MATCH AGAINST або CONTAINS. Повнотекстовий пошук Postgres натомість використовує GIN на tsvector.
⤢Bitmap	Індекс Oracle, що зберігає один растровий образ на окреме значення. Кожен біт позначає один рядок.	Використовуйте на низькокардинальних стовпцях у сховищах з інтенсивним читанням. Паралельні записи серіалізуються по кожному bitmap.
⤢Descending	Стовпець індексу, оголошений DESC. Ключі цього стовпця зберігаються у зворотному порядку.	Потрібен для змішаних сортувань на кшталт (a ASC, b DESC). Звичайні DESC-сортування можуть читати звичайний індекс і назад.
⤢Invisible	Індекс MySQL 8, який оптимізатор ігнорує. Проте підтримується при кожному записі.	Приховуйте індекс перед видаленням, щоб перевірити вплив. Зробіть його знову видимим без перебудови.
Проєктування
⤢Composite	Багатостовпцевий індекс. Провідні стовпці мають відповідати порядку фільтрів запиту.	Ставте спершу рівність, потім діапазон. Два одиночні індекси не замінюють один складений.
⤢Partial	Індекс за предикатом WHERE. У SQL Server називається filtered index. Покриває лише рядки, що відповідають умові.	Індексуйте лише рядки active='t'. Удвічі менший розмір і вартість запису.
⤢Covering (INCLUDE)	B-tree із доданими неключовими стовпцями. Проходи лише індексом уникають звернень до купи.	Додавайте часто запитувані стовпці через INCLUDE. Індекс лишається сортованим.
⤢Unique	Індекс, що забороняє дублікати значень. Відхиляє вставки, які конфліктують.	Використовуйте для натуральних ключів і зв'язків один-до-одного. UNIQUE NULL допускає багато NULL.
⤢Expression	Індекс, побудований на функції від стовпців, а не на самих стовпцях.	Індексуйте lower(email) або date_trunc('day', created_at). Запит має повторити точний вираз.
⤢Opclass	Клас операторів. Визначає, як індекс порівнює і зберігає тип одного стовпця.	Встановіть varchar_pattern_ops для LIKE 'abc%' без приведення регістру. Типові значення пасують рівності й діапазонам.
⤢Index-only scan	План, що відповідає на запит лише зі сторінок індексу. Купа ніколи не читається.	Кожен запитуваний стовпець мусить бути в індексі або списку INCLUDE. Vacuum підтримує актуальність карти видимості.
⤢Foreign keys	Стовпці зовнішніх ключів не отримують автоматичного індексу в таблиці, що посилається.	Індексуйте кожен стовпець FK, через який ви з'єднуєте або видаляєте. Без нього каскадні видалення сканують дочірню таблицю.
Обслуговування
⤢EXPLAIN	Інспектор планів. Показує послідовні скани, вибір індексів, оцінки вартості.	Використовуйте EXPLAIN (ANALYZE, BUFFERS). Seq Scan на таблиці з 10 млн рядків означає відсутній індекс.
⤢ANALYZE	Вибірково читає стовпці таблиці, щоб побудувати статистику планувальника. Гістограми визначають вибір індексу.	Запускайте після масових завантажень. Застаріла статистика спричиняє seq-скани на індексованих стовпцях.
⤢Over-indexing	Кожен індекс додає накладні витрати на запис. INSERT, UPDATE, DELETE платять за кожен індекс.	Видаляйте невикористані індекси. Стежте за використанням у pg_stat_user_indexes.
⤢pg_stat_user_indexes	Подання Postgres про використання за індексами. Рахує скани і прочитані кортежі з часу скидання статистики.	Фільтруйте за idx_scan = 0, щоб знайти невикористані індекси. Скиньте лічильники через pg_stat_reset().
⤢CREATE INDEX CONCURRENTLY	Будує індекс, не блокуючи читання чи запис. Потребує двох проходів таблицею.	Не може виконуватись у транзакції. Невдала побудова лишає INVALID-індекс, який треба видалити.
⤢DROP INDEX	Видаляє індекс і звільняє його сховище. Типово бере ексклюзивне блокування.	Використовуйте DROP INDEX CONCURRENTLY на завантажених таблицях. Перевірте статистику використання перед видаленням.
⤢REINDEX	Перебудовує роздутий індекс. Повертає простір від мертвих кортежів.	Використовуйте REINDEX CONCURRENTLY у продакшені. Без нього блокування зупиняють запис.
⤢Bloat	Мертві кортежі й прогалини сторінок, що накопичуються в MVCC-таблицях та їх індексах.	Виміряйте через pgstattuple. Vacuumте часто; REINDEX, коли мертвий простір переважає.
⤢Fillfactor	Відсоток заповнення сторінки під час запису. Вільний простір поглинає майбутні оновлення.	Знизьте до 70-90 на таблицях із гарячими оновленнями. Повні сторінки спричиняють розщеплення сторінок.
⤢CLUSTER	Перезаписує таблицю у фізичному порядку за індексом. Одноразова операція.	Працює в парі з BRIN для сортованих блоків. Порядок не підтримується подальшими записами.