Индекс базы данных: почему спаривание
У меня есть таблица с несколькими индексами, некоторые из которых дублируют одни и те же столбцы:
Index 1 columns: X, B, C, D
Index 2 columns: Y, B, C, D
Index 3 columns: Z, B, C, D
Я не очень хорошо разбираюсь в индексации на практике, поэтому мне интересно, может кто-нибудь объяснить, почему X, Y и Z были в паре с этими же столбцами. Б - дата вступления в силу. C - полууникальный идентификатор ключа для этой таблицы на конкретную дату вступления в силу B. D - последовательность, которая идентифицирует приоритет этой записи для идентификатора C.
Почему бы просто не создать 6 индексов, по одному для каждого X, Y, Z, B, C, D?
Я хочу добавить индекс в другой столбец T, но в некоторых контекстах я буду запрашивать только T, а в других я буду также указывать столбцы B, C и D... поэтому я должен создать только один индекс, например выше или я должен создать один для T и один для (T, B, C, D)?
Мне не повезло так много, как ожидалось, когда я искал всесторонний охват индексации. Любые ресурсы, где я могу получить подробное объяснение и множество примеров индексации B-дерева?
5 ответов
Правило индексирования заключается в том, что индекс можно использовать для фильтрации по любому списку столбцов, которые составляют префикс столбцов, используемых для этого индекса.
Другими словами, мы можем использовать Индекс 1, когда фильтруем по X и B, или по X, B и C, или просто по X, или по всем четырем.
Однако мы не можем использовать индекс для фильтрации "в середине". Это связано с тем, что индексы работают не совсем так, как в случае объединения значений этих столбцов для каждой строки и сортировки результата. Если мы знаем, с чего начинается искомая вещь, мы можем выяснить, где в индексе искать - как при бинарном поиске.
Вот почему один индекс не годится: если нам нужно фильтровать по B, C, D и одному из X, Y и Z, нам нужно три индекса; X, Y не годится в качестве индекса для простой фильтрации по Y, потому что префикс значений, которые мы ищем - X - не известен.
Как упоминал Даниэль, покрывающий индекс является возможным объяснением повторения B, C и D: даже если D никогда не фильтруется, возможно, нам понадобятся именно те столбцы, которые вы видите в своих индексах, и тогда мы сможем просто читайте столбцы из индекса вместо того, чтобы просто использовать индекс для поиска строки.
Одной из причин наличия B, C и D в этих индексах может быть наличие покрывающего индекса для часто используемых запросов. У вас будет закрывающий индекс, когда сам индекс содержит все обязательные поля данных для конкретного запроса.
Покрывающий индекс может значительно ускорить поиск данных, поскольку для извлечения данных будут использоваться только страницы индекса, а не страницы данных.
Ниже приведен пример запроса, где index 1 будет индекс покрытия:
SELECT B, C, D FROM table WHERE X = '10'
Вы должны создать его в (T, B, C, D).
Допустим, у вас есть два поля с индексом в таблице: A и B. Когда вы создаете отдельный индекс для каждого из столбцов и имеете запрос, такой как:
SELECT * FROM table WHERE A = 10 AND B = 20
Что происходит, либо:
1) БД создает два промежуточных набора результатов, один со строками, где A = 10, и другой со строками, где B = 20. Затем он должен объединить эти два набора результатов в один (а также проверить наличие дублирующихся строк).
2) БД создает один набор результатов со строками, где A = 10. Затем он должен вручную пройти все строки в этом промежуточном наборе результатов и проверить каждую из них, где B = 10.
Однако, когда вы знаете, что индекс B зависит от индекса A, и ваш запрос использует A до B, вы можете создать один индекс для обоих столбцов: (A, B)
Что это означает, что теперь БД сначала найдет все строки, где A = 10, но, поскольку B является частью того же индекса, она может использовать ту же информацию индекса для фильтрации набора результатов в строках, где B также равно 20. не нужно создавать два промежуточных набора результатов + объединять их, или использовать только один из индексов и выполнять поиск вручную для другого.
Могут быть и другие способы, которыми БД справляется и с этими ситуациями, во многом это зависит от реализации.
Индексы в форме (X, B, C, D) можно использовать для оптимизации запросов, таких как:
... WHERE X rel sthg (possibly ORDER BY B, C, D)
... WHERE X = sthg AND B rel sthg (possibly ORDER BY C, D)
... WHERE X = sthf AND B = sthg AND C rel sthg (possibly ORDER BY D)
и т. д. где rel произвольные операторы отношений (<,>, =, <=,>=), а sthg - значения или выражения. Особенно вторые два, и варианты сортировки не будут оптимизированы "вариантом индекса с одним столбцом".
ОТО, он не может оптимизировать запрос
... WHERE B = sthg
потому что он начинается в середине индекса; здесь индекс одного столбца будет работать.
Для ресурса, где вы можете получить подробное объяснение и множество примеров, касающихся индексов в Oracle (и любых других проблем, связанных с Oracle), вам следует посетить и добавить в закладки askTom.