Допустим, у меня есть простая таблица "многие-ко-многим" между таблицами "table1" и "table2", которые состоят из двух int-полей: "table1-id" и "table2-id". Как я должен индексировать эту таблицу ссылок?
Я использовал простой составной первичный индекс (table1-id, table2-id), но я читал, что этот индекс может не работать, если вы измените порядок полей в запросе. Итак, какое оптимальное решение тогда - сделать независимые индексы для каждого поля без первичного индекса?
Спасибо.
Это зависит от того, как вы выполняете поиск.
Если вы выполните поиск следующим образом:
/* Given a value from table1, find all related values from table2 */
SELECT *
FROM table1 t1
JOIN table_table tt ON (tt.table_1 = t1.id)
JOIN table2 t2 ON (t2.id = tt.table_2)
WHERE t1.id = @id
тогда вам нужно:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)
В этом случае table1 будет ведущим в NESTED LOOPS, и ваш индекс будет использоваться только тогда, когда table1 будет проиндексирован первым.
Если вы выполните поиск следующим образом:
/* Given a value from table2, find all related values from table1 */
SELECT *
FROM table2 t2
JOIN table_table tt ON (tt.table_2 = t2.id)
JOIN table1 t1 ON (t1.id = tt.table_1)
WHERE t2.id = @id
тогда вам нужно:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_2, table_1)
по указанным выше причинам.
Здесь вам не нужны независимые индексы. Составной индекс можно использовать везде, где можно использовать простой индекс в первом столбце. Если вы используете независимые индексы, вы не сможете эффективно искать оба значения:
/* Check if relationship exists between two given values */
SELECT 1
FROM table_table
WHERE table_1 = @id1
AND table_2 = @id2
Для такого запроса вам потребуется хотя бы один индекс для обоих столбцов.
Неплохо иметь дополнительный индекс для второго поля:
ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 PRIMARY KEY (table_1, table_2)
CREATE INDEX ix_table2 ON table_table (table_2)
Первичный ключ будет использоваться для поисков on both values и для поисков на основе значения table_1, дополнительный индекс будет использоваться для поиска на основе значения table_2.
Пока вы указываете оба ключа в запросе, не имеет значения, какой порядок он имеет в запросе, и не имеет значения, какой порядок вы указали в индексе.
Однако маловероятно, что у вас иногда будет только один или другой ключ. Если у вас иногда есть только id_1, то это должно быть первое (но вам по-прежнему нужен только один индекс).
Если у вас иногда есть один, иногда другой, иногда и тот, и вам нужен один индекс с двумя ключами, а второй (не уникальный) индекс с одним полем - более избирательный из двух ключей - и основной составной индекс должен начинаться с другого ключа.
@Quassnoi, в вашем первом запросе вы фактически используете только ключ tt.table_1, как мы можем видеть из предложения WHERE: WHERE t1.id = @id. А во втором запросе - только tt.table_2.
Таким образом, индекс нескольких столбцов может быть полезен только в третьем запросе из-за WHERE table_1 = @id1 AND table_2 = @id2. Если запросы такого типа не будут использоваться, вы считаете целесообразным использовать два отдельных индекса с одним столбцом?