Можете ли вы использовать индекс в mysql, используя col1 ИЛИ col2?

У меня есть запрос mysql, который получает список личных сообщений, где пользователь является либо отправителем, либо получателем.

    SELECT 
    users_user1.user_name AS pm_username_1, 
    users_user1.user_avatar AS pm_username_1_avatar,
    users_user2.user_name AS pm_username_2,
    users_user2.user_avatar AS pm_username
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                    | rows  | Extra                       |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+
|  1 | SIMPLE      | pms         | ALL    | pm_receiver   | NULL    | NULL    | NULL                   | 25354 | Using where; Using filesort |
|  1 | SIMPLE      | users_user1 | eq_ref | PRIMARY       | PRIMARY | 4       | movies.pms.pm_sender   |     1 |                             |
|  1 | SIMPLE      | users_user2 | eq_ref | PRIMARY       | PRIMARY | 4       | movies.pms.pm_receiver |     1 |                             |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+

avatar, 
    pms.*
FROM pm pms
LEFT JOIN users users_user1 
    ON users_user1.user_id = pms.pm_sender
LEFT JOIN users users_user2
    ON users_user2.user_id = pms.pm_receiver
WHERE pm_thread = pm_id 
    AND (pm_receiver = '1' OR pm_sender = '1')
    AND pm_delete != '1'
ORDER by pm_thread_last DESC LIMIT 0, 15

Проблема в том... насколько я могу судить... он не может использовать какой-либо индекс.

Как я могу обойти это?

РЕДАКТИРОВАТЬ

+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+
| id | select_type | table       | type   | possible_keys | key     | key_len | ref                    | rows  | Extra                       |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+
|  1 | SIMPLE      | pms         | ALL    | pm_receiver   | NULL    | NULL    | NULL                   | 25354 | Using where; Using filesort |
|  1 | SIMPLE      | users_user1 | eq_ref | PRIMARY       | PRIMARY | 4       | movies.pms.pm_sender   |     1 |                             |
|  1 | SIMPLE      | users_user2 | eq_ref | PRIMARY       | PRIMARY | 4       | movies.pms.pm_receiver |     1 |                             |
+----+-------------+-------------+--------+---------------+---------+---------+------------------------+-------+-----------------------------+

Изменил схему на эту:

(SELECT 
    users_user1.user_name AS pm_username_1, 
    users_user1.user_avatar AS pm_username_1_avatar,
    users_user2.user_name AS pm_username_2,
    users_user2.user_avatar AS pm_username_2_avatar, 
    pms.*
FROM pm pms
LEFT JOIN users users_user1 
    ON users_user1.user_id = pms.pm_sender
LEFT JOIN users users_user2
    ON users_user2.user_id = pms.pm_receiver
WHERE pm_thread = pm_id 
    AND (pm_receiver = '1')
    AND pm_delete != '1')
UNION
(SELECT 
    users_user1.user_name AS pm_username_1, 
    users_user1.user_avatar AS pm_username_1_avatar,
    users_user2.user_name AS pm_username_2,
    users_user2.user_avatar AS pm_username_2_avatar, 
    pms.*
FROM pm pms
LEFT JOIN users users_user1 
    ON users_user1.user_id = pms.pm_sender
LEFT JOIN users users_user2
    ON users_user2.user_id = pms.pm_receiver
WHERE pm_thread = pm_id 
    AND (pm_sender = '1')
    AND pm_delete != '1')
ORDER by pm_thread_last DESC LIMIT 0, 15

ОБЪЯСНЯТЬ

+----+--------------+-------------+--------+---------------+-------------+---------+------------------------+------+----------------+
| id | select_type  | table       | type   | possible_keys | key         | key_len | ref                    | rows | Extra          |
+----+--------------+-------------+--------+---------------+-------------+---------+------------------------+------+----------------+
|  1 | PRIMARY      | pms         | ref    | pm_receiver   | pm_receiver | 4       | const                  |  336 | Using where    |
|  1 | PRIMARY      | users_user1 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_sender   |    1 |                |
|  1 | PRIMARY      | users_user2 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_receiver |    1 |                |
|  2 | UNION        | pms         | ref    | pm_sender     | pm_sender   | 4       | const                  |  283 | Using where    |
|  2 | UNION        | users_user1 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_sender   |    1 |                |
|  2 | UNION        | users_user2 | eq_ref | PRIMARY       | PRIMARY     | 4       | movies.pms.pm_receiver |    1 |                |
| NULL | UNION RESULT | <union1,2>  | ALL    | NULL          | NULL        | NULL    | NULL                   | NULL | Using filesort |
+----+--------------+-------------+--------+---------------+-------------+---------+------------------------+------+----------------+

mysql sql indexing
person Community    schedule 06.09.2009    source источник
comment
Опубликуйте вывод EXPLAIN по вашему запросу. (И дело не в том, что mysql может не использовать индексы, если таблицы маленькие - сканирование может быть таким же быстрым.)   -  person nos    schedule 07.09.2009
comment
Эй, а почему ты везде поменял 1337 на 1? Ворчание: это затрудняет написание ответов на вопрос, когда вы меняете данные вопроса.   -  person Jonathan Leffler    schedule 07.09.2009

Ответы (4)


arrow_upward
1
arrow_downward

Да, MySQL может использовать индекс в выражении ИЛИ. Откуда вы знаете, что он не использует ваш индекс, вы использовали EXPLAIN, чтобы увидеть, как MySQL выполняет ваш запрос? Сколько строк у вас в этой таблице? Если количество строк слишком мало, MySQL не будет использовать индекс, так как быстрее выполнить полное сканирование таблицы. Я думаю, что порог равен 100 - если в таблице меньше 100 строк, она всегда будет выполнять сканирование таблицы, а не использовать индекс.

person Cody Caughlan    schedule 06.09.2009
comment
В таблице гораздо больше записей, чем это. Я использовал объяснение.... и не использовал индекс с выражением ИЛИ. - person ; 07.09.2009

arrow_upward
0
arrow_downward

Вы можете форсировать проблему с помощью подсказок индекса, но это может не привести к повышению производительности запросов.

См. http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

Какие определения индексов вы использовали?

person David Andres    schedule 06.09.2009

arrow_upward
0
arrow_downward

Если вы думаете о том, что оптимизатор хочет сделать, ему довольно сложно эффективно использовать показанный запрос.

Когда оптимизатор читает через индекс, он получает значения столбцов для проиндексированных столбцов, а также информацию о том, сколько строк содержит эти значения и где найти эти строки. Ясно, что для уникального индекса информация о количестве строк равна 1. Также обычно существуют методы поиска записей индекса для определенного набора значений строк (я думаю, все методы индекса). Для некоторых типов индексов существует способ найти первую запись индекса с частичным совпадением для ведущих столбцов индекса (индексы B-дерева и родственники). Я предполагаю, что информация о том, где найти строки, хранится как «rowid»; терминология не полностью унифицирована для всех СУБД, но будет служить. Таким образом, запись индекса в целом идентифицирует ключевые значения и набор строк, в которых столбцы содержат ключевые значения.

Я предлагаю игнорировать критерий pm_thread = pm_id, потому что он выглядит как критерий соединения. Если на самом деле это условие между двумя столбцами единственной таблицы в запросе, то это тоже проблематично - поиск по индексу затруднен.

Два других условия:

  1. (pm_receiver = '1337' OR pm_sender = '1337')
  2. pm_delete != '1337'

Второй из них, как правило, очень неселективный - условие не равно обычно возвращает почти все строки в таблице, и (самостоятельно) лучше всего справляется с сканированием таблицы, отклоняющим несколько строк, которые не совпадают. Из этого могут быть исключения, и именно поэтому оптимизаторы используют статистику. Рассмотрим малый бизнес в Калифорнии; если большинство его клиентов также находятся в Калифорнии, то условие state != 'CA' может быть очень избирательным, если 30 000 клиентов находятся в Калифорнии и 20 за ее пределами (но аналогичное условие state != 'AZ' очень неизбирательно; оно может даже выбрать каждую строку из таблицы, но удаляет не более 20 строк). Но без статистических данных, подтверждающих такой противоположный вывод, оптимизатор предположит, что условие «не равно» не является селективным.

Это оставляет первое условие — предложение ИЛИ для двух разных столбцов. Отдельные критерии, вероятно, будут довольно избирательными; не будет много строк, соответствующих pm_receiver = '1337', и не будет много строк, соответствующих pm_sender = '1337'.

Но как оптимизатор может использовать индекс для поиска строк, удовлетворяющих тому или иному условию? Если есть два доступных индекса, один с pm_receiver в качестве ведущего столбца, а другой с pm_sender в качестве ведущего столбца, то, возможно, оптимизатор сможет прочитать «rowids» для строк из первого индекса, а также «rowids» для строк. из второго индекса, а затем возьмите объединение этих строк. Затем он может приступить к остальной части обработки запроса. Однако автоматически не становится очевидным, что использование двух таких индексов быстрее, чем просмотр таблицы, и многие оптимизаторы этого не делают. Они сканировали таблицу и по очереди оценивали условия для каждой строки. И они часто были бы правы, так как это самый быстрый способ для них обработать запрос.

Если оптимизатор попытается использовать только один из индексов (возможно, из-за того, что присутствует только один из индексов), то у него будет более сложная работа. Если бы индекс существовал на (pm_receiver, pm_sender), то он мог бы ответить на запрос, просканировав весь индекс в поисках строк, где либо pm_receiver равно "1337", либо pm_sender равно "1337". Является ли это выигрышем в производительности, зависит от размера столбцов, размера таблицы и внутреннего устройства механизма выполнения. Большинство СУБД не будут использовать эту стратегию, особенно если им все равно придется обращаться к строке на диске для выполнения запроса. Если все релевантные столбцы содержатся в индексе, сканирование только индекса может быть выигрышной стратегией, но если для этого необходимо также обратиться к диску за данными, то это, вероятно, не выигрышная стратегия.

(Если критерий pm_thread = pm_id является условием между столбцами в одной строке, его также нельзя оценить с помощью индекса, если только индекс не содержит оба столбца, а также требуется полное сканирование индекса, чтобы найти строки, к которым применяется условие. И оптимизатор предпочитаю использовать индекс для условия ИЛИ, если это возможно, потому что это будет иметь лучшую селективность.)

Таким образом, при наличии обычной СУБД с таблицами, хранящимися в виде строк (а не столбцовой базы данных) и нормальными индексами оптимизатор не может эффективно использовать индекс для ответа на запрос, поэтому оптимизатор предпочитает не беспокоиться.

При вводе вышеизложенного вопрос был изменен, чтобы показать многостороннее соединение с двумя критериями LEFT OUTER JOIN (LOJ).

LOJ сам по себе убивает производительность. Его следует избегать, когда это возможно. Их наличие значительно усложняет использование индексов. Нам потребуется знать полную схему каждой из задействованных таблиц, включая индексы таблиц. Даже в этом случае оптимизатор, скорее всего, будет сканировать доминирующую таблицу (ту, к которой остальные присоединены внешним образом) и использовать индексированный поиск для поиска совпадающих строк или отсутствия совпадающих строк во внешних присоединяемых таблицах.

person Jonathan Leffler    schedule 06.09.2009

arrow_upward
0
arrow_downward

Действительно, поскольку это критерий ИЛИ, MySQL не может использовать какой-либо индекс ни в одном из упомянутых столбцов. Это потому, что индекс позволит вам искать по одному или другому столбцу, но не по обоим одновременно.

Я бы предложил разделить запрос на два запроса, чтобы вам не приходилось использовать ИЛИ. А перед этим - проверьте, действительно ли это вызывает у вас проблемы с производительностью. Возможно, вы пытаетесь решить не ту проблему.

Добавлено: Увидев полный запрос, я могу сказать только одно: переосмыслите свою структуру данных. Это может быть очень хорошо для целостности данных или чего-то еще, но вы просто не можете написать такой запрос без полного сканирования таблицы. Если вы не можете реструктурировать его, возможно, добавьте еще одну таблицу с необходимой кэшированной информацией. Однако вам придется поддерживать кеш в актуальном состоянии.

person Vilx-    schedule 06.09.2009