индекс базы данных: почему соединение
У меня есть таблица с несколькими индексами, несколько из которых копируют те же столбцы:
Index 1 columns: X, B, C, D
Index 2 columns: Y, B, C, D
Index 3 columns: Z, B, C, D
Я не очень хорошо осведомлен относительно индексации на практике, таким образом, я задаюсь вопросом, может ли кто-то объяснить, почему X, Y и Z были соединены с этими теми же столбцами. B является датой вступления в силу. C является идентификатором полууникального ключа для этой таблицы для определенной даты вступления в силу B. D является последовательностью, которая определяет приоритет этой записи для идентификатора C.
Почему не только создают 6 индексов, один для каждого X, Y, Z, B, C, D?
Я хочу добавить индекс к другому столбцу T, но в некоторых контекстах я буду только запрашивать на одном только T, в то время как в других я буду также указывать B, столбцы C и D..., таким образом, я должен буду создать всего один индекс как вышеупомянутый, или я должен создать один для T и один для (T, B, C, D)?
У меня не было такой же удачи как ожидалось при поиске с помощью Google для всестороннего покрытия индексации. Какие-либо ресурсы, где я могу добраться посредством объяснения и большого количества примеров индексации B-дерева?
5 ответов
Правило индексирования состоит в том, что индекс может использоваться для фильтрации любого списка столбцов, составляющих префикс столбцов, используемых для этого индекса.
Другими словами, мы можем использовать индекс 1 при фильтрации по X и B, или X, B и C, или только X, или всем четырем.
Однако мы не можем использовать индекс для фильтрации «посередине». Это связано с тем, что индексы работают не совсем так, как объединение значений этих столбцов для каждой строки и сортировка результата. Если мы знаем, с чего начинается то, что мы ищем, мы можем выяснить, где в индексе искать - точно так же, как при бинарном поиске.
Вот почему один индекс не годится: если нам нужно фильтровать по B, C, D и одному из X, Y и Z, нам нужно три индекса; X, Y не подходят в качестве индекса только для фильтрации по Y, потому что префикс значений, которые мы ищем, - X - неизвестен.
Как упоминал Дэниел, индекс покрытия является возможным объяснением повторения B, C и D: даже если D никогда не фильтруется, возможно, нам нужны именно те столбцы, которые вы видите в своих индексах, и Затем мы можем просто прочитать столбцы из индекса, а не просто использовать индекс для поиска строки.
Вы должны создать его в (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 - значения или выражения. Особенно вторые два, и варианты сортировки не будут оптимизированы «вариантом индексов с одним столбцом».
OTOH, он не может оптимизировать запрос
... WHERE B = sthg
, потому что он начинается в середине индекса; здесь будет работать индекс с одним столбцом.
Для ресурса, где вы можете получить подробное объяснение и множество примеров, касающихся индексов в Oracle (и по любой другой проблеме, связанной с Oracle), вам следует посетить askTom и добавить его в закладки.
Одной из причин наличия B, C и D в этих индексах может быть наличие индекса покрытия для часто используемых запросов. У вас будет индекс покрытия, если сам индекс содержит все необходимые поля данных для определенного запроса.
Покрывающий индекс может значительно ускорить извлечение данных, поскольку для извлечения данных будут использоваться только страницы индекса, а не страницы данных.
Ниже приведен пример запроса, в котором индекс 1 будет индексом покрытия:
SELECT B, C, D FROM table WHERE X = '10'