Логика индексов

#sql #база_данных #oracle

Есть такая таблица

CREATE TABLE my_table (
  .......
  f1 NUMBER NOT NULL,
  f2 NUMBER NOT NULL,
  ........
);


поле f1 почти уникально. В таблице может порядка 50 записей с одинаковым f1 при общем
числе записей ~100 000

поле f2 повторяется очень часто. В таблице не более 10 различных значений f2

К этой таблице бывают запросы двух типов

SELECT * FROM my_table WHERE f1 = :val1 AND f2 = :val2


и

SELECT * FROM my_table WHERE f2 <> 0
SELECT * FROM my_table WHERE f2 = 0


(сравнение идет только с нулем и только на равно/не равно)

Вопрос какой лучше индекс создать


один составной: (f2, f1)
два простых: (f1), (f2)
составной и простой: (f1, f2), (f2)

    

Ответы

Ответ 1

Один индекс, только по f1.

Индекс, по которому надо читать существенную часть таблицы - бесполезен. Сильно дешевле
обойти всю таблицу целиком последовательным чтением. Планировщик запросов это знает.
Как следствие вы можете сделать индекс по f2, вы будете тратить ресурсы на его хранение
и поддержание актуальности, но он просто не будет использоваться. Или, что тоже гипотетически
возможно и что ещё хуже: будет использоваться, но вместо ускорения работы превратит
простое последовательное чтение в очень много операций случайного чтения сначала прыгая
по индексу, потом по таблице за данными.

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



Если ваша СУБД умеет частичные (partial) индексы - то может иметь смысл сделать индекс
по f2 с исключением наиболее частых значений. Вероятно, у вас большинство строк подходят
либо под f2 <> 0 либо под f2 = 0.


Ответ 2

Кроме индекса по f1, я бы еще попробовал построить bitmap index по f2 и посмотреть
какие планы строит оптимизатор. Есть неплохой шанс, что запросы вида:

SELECT * FROM my_table WHERE f2 <> 0
SELECT * FROM my_table WHERE f2 = 0


будут работать быстрее с использованием bitmap индекса по f2.