Как хранятся индексы в postgresql и mysql?

Question

[Sergey Ilichev]

Всем привет!

Про хранение индексов знал и раньше, но в общих чертах, типа там btree что и как в нем ищется и прочее. Но никогда не разбирался с вопросом хранения индексов в разных рсубд. Щас вот сидел читал, но что-то очень мутно эту тему объясняют везде где читал, поэтому задам вопросы тут, если кто в теме, буду благодарен ответам.

1. Прочитал, что в mysql в движке innodb индексы делятся на два больших типа - первичный и вторичный, где первичный индекс является кластеризованным индексом, а все вторичные индексы некластиризованными. Если вам знакомы эти понятия, значит вы уже что-то да читали и может сможете помочь разобраться в вопросе, потому что интересует инфа сотка, когда человек видел, щупал и знает о чем говорит, а не как я почитал и интерпретирует прочитанное) В общем вопрос в том, если некластиризованный индекс хранится в виде отдельной таблицы, то кластеризованный, как написано хранится с данными. Но не понятно как? Он имеет вес? Это все же какое-то изменение в таблице, скрытое поле? Или просто идет сортировка по данному полю (по умолчанию в большую или меньшую сторону?), происходят ли вообще правки в файле таблице на диске?
2. Всегда ли не кластиризованные индексы хранятся в оперативной памяти или это как-то можно регулировать? Я понял, что в зависимости от субд есть разные настройки, с помощью которым можно менять размер буфера для индексов, но получается сервер базы данных сам решает как ему управлять памятью? Если он в какой-то момент решает, что индекс стал слишком большим, он его выгружает и создает на основе него файл индекса?
3.До postgresql версии 13, если я не ошибаюсь, индексы были в полтора, а то и два раза больше. У нас на проекте версия 9, если не ошибаюсь, там индексы добавляют к памяти иногда по 5 гигов. Нормально ли это? Я слышал что индексы должны быть в пределах мегабайт, а не гигабайт.
4. В чем еще отличие индексов в postgresql от mysql, кроме того что в нем нет кластеризованных индексов?
5. Читал, что бывает так, что индекс в таблице индекса хранит сразу данные определенных столбцов, а не ссылки на эти строки в основной таблице. В каких случаях и почему так бывает? Столбцы, которые хранятся в таком индексе это те по которым мы ищем или те которые селектим? Типа индекс это то почему мы ищем, а другие столбца, те которые будем селектить и тогда этот индекс отдаст их быстрее без обращения на диск? Если так, то не будет ли такой индекс менее эффективен из-за размера? Можно ли создать несколько таких индексов с разными наборами полей, чтобы каждый весил не много и почему был быстрый поиск? Какая тогда будет структура индекса? Я читал что стандартная структура индекса в postgresql это структура из языка C типа

Answer 1

[Владимир Коротенко]

1. Плюньте в этот источник. Индекс это индекс, первичный отличается только тем что он указывает на уникальность записи, да и в некоторых БД его можно не указывать.
Храниться все это счастье в файлах, конкретно зависит от движка. Под капотом как правило код очень похожий на BercleyDB или аналог.
2. Любая база стремится сожрать всю память :) Но это настраивается, настройки соответственно разнятся для каждой СУБД
3. Как настроите и не понятно сколько у вас данных
4. снова читаем документацию, в общем то нет различий
5. снова документация хотя структура похожа на правильную.

Comments

[Владимир Коротенко]

Дополнительно. Не заморачивайтесь над этим.
Вам нужно знать тип индекса и сколько он занимает и насколько тормозит вставки.
В принципе для прикладника это все что нужно знать

[Melkij]

Индекс это индекс, первичный отличается только тем что он указывает на уникальность записи

А вот как раз в innodb - не только этим различается.
В innodb вся таблица - это и есть primary key, все прочие колонки лежат в листьях этого дерева. Каждый вторичный индекс хранит значение primary key и именно по нему находит строку данных в таблице.

Если на таблице не объявлен primary key явный, то используется неявный 6-байтовый указатель, но таблица по-прежнему вот такая индекс-ориентированная.
https://dev.mysql.com/doc/refman/8.0/en/innodb-ind...

[Sergey Ilichev]

Melkij, да, это что и читал. Вопрос в том, как это влияет на таблицу в файле. Получается что в файле данные хранятся в отсортированном по индексу порядке? Кстати там чуть не так, как вы написали, там есть несколько если до создания автоматического индекса - если не задали первичный ключ и если еще нет подходящего под условие столбца для создания на нем автоматически кластерного индекса - то есть поля с уникальными значениями и без null.

[Sergey Ilichev]

Владимир Коротенко, не понял ваш ответ)

1. У вас странный подход) Плюнуть на все. Во первых индекс не есть просто индекс, он разный в разных рсубд и в разных движках, даже в рамках одно mysql он ведет себя по разному в myisam и innodb. Во вторых именно знание того как эти индексы себя ведут помогает грамотно их создавать. Одно дело индекс справочника, другое дело индекс на таблицу с полем с кучей разных значений. Каков будет его размер? Влезет он в оперативную память? Как настроить размер буфера для индексов? Все это надо знать и настраивать.

2. Это понятно что можно настроить и что все по-разному.

3. Не понятно что имеете ввиду. Что я настрою? Размер таблицы гигов 120. Что можно настроить чтобы регулировать размер индекса? Он какой есть такой и есть. Суть в том, что в 13 версии, судя по тому что читал, делали какие-то доработки, после чего объем занимаемый индексами уменьшился в полтора или два раза.

4. Как нет, если есть? Читайте ответ на 1 пункт.

5. Я тут для того и задал вопрос и написал четко, что жду ответов от людей, кто вот прямо разбирался в теме, смотрел исходники, файлы открывал индексов, читал, сравнивал и прочее. То есть смысл писать какую-то субъективщину основанную просто на том что вы читали другие статьи как и я? Понятно что кто-то одно читал, кто-то другое, а кто-то вообще не читал, но думает что знает все. Разные мнения, а я спросил в надежде что в сообществе найдётся задрот, который просто перелопатил систему и все знает как будто сам писал)

[Дмитрий]

Одно дело индекс справочника, другое дело индекс на таблицу с полем с кучей разных значений. Каков будет его размер? Влезет он в оперативную память? Как настроить размер буфера для индексов? Все это надо знать и настраивать.

У вас тоже странный подход. Индекс зависит от данных которые вы индексируете, а не от оперативной памяти, или буфера для индексов. Индексы создаются исходя из требований по запросам. Что значит разный в myisam и innodb? B+tree как бы структура известная - и алгоритмическая сложность для операций с ней - будет одинаковой. Отсутствие кластерных индексов в Postgres связано не с индексами а с Postgres'овской MVCC

Разные мнения, а я спросил в надежде что в сообществе найдётся задрот, который просто перелопатил систему и все знает как будто сам писал)

Ну здесь вряд ли - сходите https://t.me/pgsql там сидят и разработчики PostgresPro и всяких экстеншенов для постгреса

[Melkij]

Получается что в файле данные хранятся в отсортированном по индексу порядке?

Не обязательно. Всё-таки, весьма накладно было бы таблицу полностью переписывать чтобы добавить новую строку со значением PK меньше всех предыдущих или в середину диапазона, например. В пределах страницы данных упорядочены, сами страницы данных в датафайле могут быть не всегда упорядочены.

Я по этим вопросам, конечно, могу многое понаписать как DBA и контрибьютор. Но не хочу, очень много писать.

Answer 2

[mayton2019]

До postgresql версии 13, если я не ошибаюсь, индексы были в полтора, а то и два раза больше. У нас на проекте версия 9, если не ошибаюсь, там индексы добавляют к памяти иногда по 5 гигов. Нормально ли это? Я слышал что индексы должны быть в пределах мегабайт, а не гигабайт.

Работаю с базами данных давно. Начинал с Oracle9i.
Большая часть индексов базируются на B+Tree. Хотя в последнее время в эпоху RocksDb/Cassandra/Tarantool
появились более интересные стурктуры такие как LSM-tree. Они по скорости записи более эффективны.

Про то что индексы должны быть размером в мегабайты - я впервые слышу. Возможно это фраза
вырвана из контекста. Там обсуждалось что-то другое. Поэтому приведите цитату на документацию.
Желательно от производителя БД.

В Оракле есть положительный эффект от периодической перестройки индекса (alter index rebuild).
Этот эффект временный и обычно связан с фактором кластеризации. Его очень любят новички и
часто сам вопрос является троллингом Oracle-профессионалов. Но это было лет 20 назад. Щас
в эпоху облак всем стало пофиг.

Всегда ли не кластиризованные индексы хранятся в оперативной памяти или это как-то можно регулировать?

Не знаю откуда ты такие вот факты черпаешь. Конечно лучше всю базу данных положить в память.
Но база обычно многократно превышает память и мы довольствуемся страничным кешем (page cache)
или buffer pool в других системах. И все они работают по принципу LRU (хранения наиболее горячих
блоков диска). А будет ли это таблица или индекс или еще какойто подвид объекта - это как повезет.
Во всех DBMS есть мониторинг этого страничного кеша. Вот посмотри что у тебя там лежит в час
наибольшей нагрузки. Это и будет самый правильный ответ на твой вопрос. И главное - практически
подтвержденный.

Читал, что бывает так, что индекс в таблице индекса хранит сразу данные определенных столбцов, а не ссылки на эти строки в основной таблице. В каких случаях и почему так бывает?

Приводи ссылки где ты читал потому-что в твоем пересказе получается мистика. Индекс обязан хранить
копии индексируемых столбцов. Иначе-бы поиск вообще не работал.
Если ты строишь композитный индекс по 3 полям то он и будет физически хранить 3 копии этих полей
и ROWID (физический указатель на позицию в таблице для строки). И при определенных условиях
оптимизатор может выдавать данные не из таблицы а прямо из индекса если в SELECT запросе
достаточно данных в индексе. Этим часто пользуются для оптимизации.

Есть альтернативные DBMS наподобие Amazon DynamoDB где индексов нет но есть полная реплика
таблицы которая по другому кластеризована. Динамо считает это индексом хотя с точки зрения
классической DBMS это просто маркетинговый обман.

UPD: R+Tree

Comments

[Станислав Макаров]

Большая часть индексов базируются на R+Tree.

Вы наверное имели в виду B+-дерево, R-tree - это пространственный индекс.

Answer 3

[Akina]

Прочитал, что в mysql в движке innodb индексы делятся на два больших типа - первичный и вторичный

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

В InnoDB - всего лишь есть дополнение, что при наличии первичного индекса он является также и кластерным. Для других движков и других СУБД такой зависимости может и не быть.

кластеризованный, как написано хранится с данными

Не-а. Кластерный индекс - это когда записи в теле таблицы (т.е. сами данные таблицы) хранятся в порядке сортировки по выражению этого кластерного индекса.

Всегда ли не кластиризованные индексы хранятся в оперативной памяти или это как-то можно регулировать?

Индексы всегда хранятся на диске. В оперативной памяти индексы могут всего лишь кэшироваться. Для ускорения доступа.

Я слышал что индексы должны быть в пределах мегабайт, а не гигабайт.

Или не о том слышал, или не так понял. Индексы никому и ничего не должны по части своего размера, который определяется суммарным размером данных индексного выражения, количеством записей и коэффициентом заполнения.

Читал, что бывает так, что индекс в таблице индекса хранит сразу данные определенных столбцов, а не ссылки на эти строки в основной таблице.

Вероятно, речь про INCLUDE-предложение в структуре индекса, имеющееся, например, в SQL Server... т.е. значения полей хранятся в выражении индекса как дополнительные данные, и не используются при сортировке.