По какому принципу работает набор индексов в одной таблице?

Question

[MishaXXL]

Если я правильно понял, когда мы создаем индекс, у нас таблица изменяет порядок записей внутри себя сортируя их по указанным столбцам

CREATE INDEX prod_idx ON products (name, brand, price)

Т.е. в данном случае сразу она отсортирует все записи name в алфавитном порядке, после уже эти записи отсортирует по brand и так же по price от убывания к возрастания
Как если бы мы сделали выборку по умолчанию SELECT * FROM products ORDER BY name, brand, price

И один индекс

CREATE INDEX prod_idx ON products (name, brand, price)

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

CREATE INDEX name_idx ON products (name)
CREATE INDEX brand_idx ON products (brand)
CREATE INDEX price_idx ON products (price)

Которые бы по очереди друг за другом добавляли правило сортировки в таблице, верно?

И в нашем случае при редактировании записи нашим INSERT, он сработает медленно только в том случае, если мы в обновлении затронем один из перечисленных индексированных полей?
Причем при изменении name обновление будет происходить дольше, чем при изменении price, потому что у нас priceпоследняя в нашей очереди индексов по сортировке?

Answer 1

[Everything_is_bad]

Если я правильно понял, когда мы создаем индекс, у нас таблица изменяет порядок записей внутри себя сортируя их по указанным столбцам

нет, никакой порядок не меняется, индекс это отдельные от таблицы данные

И один индекс

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

нет, перестань уже придумывать и начни читать учебники и документацию

Которые бы по очереди друг за другом добавляли правило сортировки в таблице, верно?

опять ты что себе напридумал, нет никаких правил сортировки в таблице, индексы просто могут использоваться для ускорения сортировки

Причем при изменении name обновление будет происходить дольше, чем при изменении price, потому что у нас price последняя в нашей очереди индексов по сортировке?

когда фантазию уже не остановить

Comments

[MishaXXL]

В книге как раз и приводился пример с order by, что представьте его )

[Everything_is_bad]

MishaXXL, и в этой же книге, рядом такая ересь про "порядок записей внутри" и индексы написана или всё таки ты сам про это придумал?

[MishaXXL]

Everything_is_bad, там было 2 страницы про индексы )
При создании индекса, что они создают для таблицы, что помогает после в ней делать ускоренную выборку?
И при выборке БД сама подберет нужную индексацию из тех, что мы создали под нее учитывая по каким полям мы поставили условие?

[Everything_is_bad]

MishaXXL, решения применять индекс принимает СУБД. у каждой СУБД свои критерии, более того, в разных ситуация она может по разному принимать решения по его использованию, поэтому у каждой СУБД есть инструменты для получения информации, какие индексы использовались для конкретного запроса, у postgresql это explain analyze перед запросом.

Answer 2

[Melkij]

Нет, ни одно из утверждений не является верным.

btree индекс - это структура, располагающаяся отдельно, сбоку, от таблицы. Порядок данных в таблице никак не меняется от создания индекса.
Индекс btree(a,b,c) и три индекса btree(a), btree(b), btree(c) - разные вещи.

Comments

[MishaXXL]

Индекс создает свою мини таблицу или что-то подобное, что у себя отсортировало все записи на ссылки к ним?
Т.е. при создании очередного индекса, при выборке будет проанализировано условие WHEREи если там только поле name="user", то он будет производит поиск с учетом созданного индекса, где мы реализовали
CREATE INDEX name_idx ON products (name), а если у нас в условиях поиск по (name, brand, price), то уже будет использовать

CREATE INDEX prod_idx ON products (name, brand, price)

, верно?

[Melkij]

Индекс может быть вообще не отсортирован никак. Например, hash. Если говорить именно о btree - тогда да, это отсортированная структура данных.
Планировщик анализирует не только where, а много чего. И много чего ещё, напротив, не анализирует, чтобы не тратить слишком много времени на создание возможных и выбор единственного плана выполнения.

btree(name, brand, price) может использоваться для поиска по условию name="user" так же как и btree(name). А вот btree(prand, name, price) уже эффективно использоваться не может.

[MishaXXL]

Melkij, а в плане создание 2 и более индексов, в таком подходе есть смысл или обычно создается один усредненно универсальный индекс?
Например, создать 3 отдельных индекса на name, brand, price и 4-ый, где будут все три сразу name, brand, price

[Melkij]

Индекс btree(a,b,c) и три индекса btree(a), btree(b), btree(c) - разные вещи

универсального индекса нет. Любой индекс - частная потребность конкретной задачи поиска данных.

[MishaXXL]

Melkij, я про то, что есть ли смысл создания 4 таких индексов, чтобы БД делала поиск по подходящему или они так не работают?

[Melkij]

Сначала потребность (конкретные критерии, какие данные нужно искать достаточно часто и/или нужно искать быстро чтобы оправдать существование индекса) - потом индексы для решения этой потребности. Не наоборот.

Иметь одновременно btree(a), btree(b), btree(c) и btree(a,b,c) смысла нет. Среди них btree(a) будет избыточен. Одновременное существование btree(b), btree(c) и btree(a,b,c) может быть целесообразным.