Индекс при использовании ИЛИ в запросе

I know that only one index can be used in a query…

Это неправильно. При правильных обстоятельствах MySQL обычно использует несколько индексов в запросе. (Например, запрос JOINing нескольких таблиц почти всегда будет использовать по крайней мере один индекс для каждой задействованной таблицы.)

В случае вашего первого запроса MySQL будет использовать оптимизацию объединения слияния индексов. Если оба столбца проиндексированы, в выводе EXPLAIN будет указано следующее объяснение:

Using union(index_on_user_1,index_on_user_2); Using where

Запрос, показанный во втором примере, покрыт индексом (user_1, user_2). Создайте этот индекс, если вы планируете регулярно выполнять эти запросы.

MySQL испытывает трудности с OR условиями. Теоретически существует оптимизация слияния индексов, о которой упоминает @duskwuff, но на практике она не срабатывает, когда вы думаете, что должна. Кроме того, он не дает такой же производительности, как одиночный индекс.

Решение, которое большинство людей использует, чтобы обойти это, состоит в том, чтобы разделить запрос:

SELECT ... WHERE user_1 = ?
UNION
SELECT ... WHERE user_2 = ?

Таким образом, каждый запрос сможет использовать собственный выбор индекса, не полагаясь на ненадежную функцию слияния индексов.

Ваш второй запрос оптимизируется проще. Это просто сравнение кортежей. Это можно записать так:

WHERE (user_1, user_2) IN (('$user_id', '$friend_id'), ('$friend_id', '$user_id'))

В старых версиях MySQL при сравнении кортежей индекс не использовался, но начиная с версии 5.7.3 он будет (см. https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html).

P.S.: Не интерполируйте переменные кода приложения непосредственно в выражения SQL. Вместо этого используйте параметры запроса.

Два случая разные.

В первом случае в обоих столбцах нужно искать одно и то же значение. Если у вас есть индекс из двух столбцов (u1, u2), то его можно использовать в столбце u1, поскольку его нельзя использовать в столбце u2. Если у вас есть два отдельных индекса для u1 и u2, возможно, они оба будут использоваться. Выбор исходит из статистики, основанной на ожидаемом количестве возвращенных строк. Если ожидается несколько возвращаемых строк, будет выбран поиск по индексу, если соответствующий индекс доступен. Если число велико, предпочтительнее сканирование таблицы или индекса.

Во втором случае снова нужно снова проверить оба столбца, но в каждом поиске есть два подпоиска, где второй подпоиск будет по результатам первого из-за условия И. Здесь это имеет большее значение, и два индекса u1 и u2 помогут, так как любое поле, выбранное для поиска первым, будет иметь индекс. Выбор использования индекса аналогичен описанному выше.

В любом случае, однако, каждое OR вызовет еще 1 поиск или набор поисков. Таким образом, предлагаемое решение разрыва с использованием объединения больше не мешает, поскольку в таблице будет выполняться поиск x раз, независимо от того, выбран ли 1 с помощью OR (ов) или x выбран с помощью объединения, и независимо от выбора индекса и типа поиска (поиск или сканирование). В результате, поскольку каждый выбор в объединении получает свою собственную часть плана выполнения, более вероятно, что будут использоваться индексы (одного столбца) и, наконец, будут получены все наборы результатов строк из всех частей вокруг ИЛИ. Если вы не хотите копировать большой оператор выбора во многие объединения, вы можете получить значения первичного ключа, а затем выбрать их или использовать представление, чтобы убедиться, что большая часть оператора находится в одном месте.

Наконец, если вы исключите вариант объединения, есть способ обмануть оптимизатор, чтобы он использовал один индекс. Создайте двойной индекс u1, u2 (или u2, u1 — любой столбец с большей кардинальностью будет первым) и измените свой оператор, чтобы все части OR использовали все столбцы:

... WHERE (user_1 = '$user_id' OR user_2 = '$user_id') ...

будет преобразовано в:

... WHERE ((user_1 = '$user_id' and user_2=user_2) OR (user_1=user_1 and user_2 = '$user_id')) ...

Таким образом, всегда будет использоваться двойной индекс (u1,u2). Обратите внимание, что это будет работать, если столбцы могут принимать значения NULL, и обход этого с помощью isnull или объединение может привести к тому, что индекс не будет выбран. Однако он будет работать с выключенными значениями Ansi NULL.