mysql - оператор ИЛИ не использует индекс

У меня есть простая таблица приглашений:

CREATE TABLE `invitation` (
  `invitation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inviter_id` int(10) unsigned NOT NULL,
  `invitee_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`invitation_id`),
  UNIQUE KEY `invitee_inviter_idx` (`invitee_id`,`inviter_id`)
)

Я хочу выбрать приглашение от приглашающего 70 к приглашенному 62 и наоборот:

EXPLAIN SELECT * FROM `invitation` WHERE 
(invitee_id = 70 AND inviter_id = 62) OR (invitee_id = 62 AND inviter_id = 70)

Но этот запрос относится к типу ALL и не использует пригласительный_inviter_idx. Пожалуйста, скажите мне, что здесь не так?

Благодарю вас!

==EDIT== Извините, я ошибся насчет схемы, в ней есть еще одно поле: request_ts. На этот раз план запроса ALL.

    CREATE TABLE `invitation` (
      `invitation_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `inviter_id` int(10) unsigned NOT NULL,
      `invitee_id` int(10) unsigned NOT NULL,
      `request_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
      PRIMARY KEY (`invitation_id`),
      UNIQUE KEY `invitee_inviter_idx` (`invitee_id`,`inviter_id`)
    )

Вот мой результат объяснения:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  invitation  ALL invitee_inviter_idx \N  \N      \N  1   Using where

mysql sql explain
person robinmag    schedule 15.12.2012    source источник
comment
Сколько записей в таблице? Если таблица очень маленькая, оптимизатор запросов может решить выполнить полное сканирование просто потому, что это очень дешево.   -  person Hammerite    schedule 15.12.2012
comment
Я думаю, вы получаете его для таблиц MyISAM только в определенных случаях; а именно, когда вы видите Impossible WHERE, замеченный после чтения таблиц const в дополнительном столбце   -  person a1ex07    schedule 15.12.2012
comment
@Salman А, извините, я ошибся, я обновил схему.   -  person robinmag    schedule 15.12.2012
comment
Я получаю тип ALL с пустой таблицей :( я использую mysql v5.5.23   -  person robinmag    schedule 15.12.2012
comment
Заполните таблицу некоторыми данными и опубликуйте полный результат EXPLAIN.   -  person Salman A    schedule 15.12.2012
comment
@Салман, я обновил свой вопрос   -  person robinmag    schedule 15.12.2012

Ответы (2)


arrow_upward
3
arrow_downward

Вам просто нужно получить достаточно строк в таблице. MySQL будет выполнять полное сканирование небольших таблиц просто потому, что это достаточно дешево.

В моем примере в таблицу помещается 65 тысяч строк, и он будет использовать индекс.

http://sqlfiddle.com/#!2/63079/1

person Andreas Wederbrand    schedule 15.12.2012
comment
Достаточно строк, и он останавливает сканирование таблицы и выполняет частичное сканирование индекса — улучшение, но вырожденные значения для приглашенного_id будут сканировать весь индекс. Объединение all приводит к типу const, а не к диапазону - person Tony Lee; 15.12.2012
comment
Я согласен, что союз был бы быстрее, но это не то, о чем просил ОП. - person Andreas Wederbrand; 16.12.2012

arrow_upward
9
arrow_downward

Есть как минимум 3 причины, по которым ваш выбор не использует индекс

1) Вы использовали select *, который включает элементы, не входящие в индекс (т. е. invitation_id). Это означает, что если бы он использовал индекс, ему пришлось бы искать строку в базе данных, чтобы получить значение invitation_id. Если бы вы добавили invitation_id к индексу, он бы использовал этот индекс. Если бы вы сделали select всего лишь invitee_id, inviter_id, он бы использовал индекс.

2) Оптимизатор запросов решил, что будет лучше просто просмотреть таблицу, а не диапазон индекса. Когда оптимизатор пытается выбрать полное сканирование таблицы или частичное сканирование индекса, он не делает этого для конкретного запроса — ему нужен план, который в целом работает хорошо. Тот, который может быть запущен снова и снова. Сканирование от invitee_id,inviter_id (62,70) до (70,62), скорее всего, даст только 8 элементов индекса, но при случайном выборе из 50 000 элементов среднее расстояние составит около 17 000 элементов. Таким образом, в среднем один запрос будет обращаться к 1/3 индекса (т. е. извлекать его в память), а затем обращаться к странице, на которой находится строка (см. № 1), извлекая ее в память. Ваши строки настолько малы, что доступ только к одному элементу, вероятно, приведет к 680 строкам (страница 8 КБ по 12 байтам для 3 32-битных #), что составляет 1/70 таблицы - выполните 100 запросов и, вероятно, вы вытащили весь индекс в памяти и всей таблицы — имеет смысл потратить немного больше времени на сканирование таблицы и использовать на 40% меньше памяти для хранения битов других таблиц. В какой-то момент (который кажется 65 тыс. строк) это перестает иметь смысл.

3) Что сказал ваш вопрос: вы использовали ИЛИ. Выражение ИЛИ нельзя использовать для поиска чего-либо в индексе, то есть вы не можете искать 62 или 70. Вместо этого оно создает диапазон, ищущий (62,70), затем сканирует, чтобы добраться до (70,62) (см. # 2, почему это может быть плохо).

Вы спросили "что здесь не так" - это то, что вы использовали ИЛИ, которое не масштабируется. Вам нужно избегать не только типа ALL, но и большого типа RANGES.

Я видел ту же проблему с другими механизмами SQL, и решение, которое я использовал, было UNION ALL.

Что-то типа

SELECT * FROM `invitation` WHERE 
    (invitee_id = 70 AND inviter_id = 62)
UNION ALL
SELECT  * FROM `invitation` WHERE
    (invitee_id = 62 AND inviter_id = 70)

Это сделает это двумя запросами и объединит результаты без проверки дубликатов.

Это намного проще в использовании памяти и намного быстрее - требуется всего несколько страниц индекса и две страницы из таблицы и O (log (N)) для каждого поиска. Это потому, что теперь он имеет тип const - ваша цель состояла в том, чтобы исключить ALL, но переключение на RANGE почти так же плохо, как выборка всего двух строк. Сканирование всей таблицы равно O(N), а сканирование RANGE индекса также равно O(N), поскольку O(1/3*N) равно O(N). Другими словами, он не масштабируется.

person Tony Lee    schedule 15.12.2012