Оптимизация запроса, который всегда сканирует всю таблицу
в нашей системе есть запрос, который должен фильтровать отчеты по некоторым факторам запрос индексируется, и при использовании объяснения он показывает, что он использует индексы, но номера строк такие же, как и в таблице, например, если сканирование бесполезно
[тег:исходный запрос] :
SELECT COUNT(*) FROM p_rpts r WHERE
( (r.to_player_id='191717' AND r.delete_status!=1) OR (r.from_player_id='191717' AND r.delete_status!=2) ) AND delete_status!=3;
что я пробовал до сих пор:
SELECT COUNT(*)
FROM p_rpts AS r
WHERE r.delete_status != 3 AND (
(r.to_player_id = '191717' AND r.delete_status != 1) OR
(r.from_player_id = '191717' AND r.delete_status != 2)
);
SELECT COUNT(*) FROM (
SELECT 1 FROM p_rpts AS r WHERE r.to_player_id = '191717' AND r.delete_status NOT IN (1, 3)
UNION ALL
SELECT 1 FROM p_rpts AS r WHERE r.from_player_id = '191717' AND r.delete_status NOT IN (2, 3)
) AS combined;
SELECT COUNT(*)
FROM p_rpts AS r FORCE INDEX (idx_player_status)
WHERE
(r.delete_status != 3) AND
(
(r.to_player_id = '191717' AND r.delete_status != 1) OR
(r.from_player_id = '191717' AND r.delete_status != 2)
);
SELECT COUNT(*)
FROM p_rpts r
WHERE
(r.delete_status != 3) AND
(
(r.to_player_id = '191717' AND r.delete_status != 1) OR
(r.from_player_id = '191717' AND r.delete_status != 2)
);
Я попробовал много изменений в запросе и индексах, но ничего не помогло.
обновить, используя объединение:
SELECT COUNT(*)
FROM (
SELECT r.id
FROM p_rpts AS r
WHERE r.to_player_id = '191717' AND r.delete_status != 1 AND r.delete_status != 3
UNION
SELECT r.id
FROM p_rpts AS r
WHERE r.from_player_id = '191717' AND r.delete_status != 2 AND r.delete_status != 3
) AS combined_results;
Поделитесь выводами объяснения предпринятых запросов, индексами, определенными в базовых таблицах, и временем, необходимым для выполнения запросов.
попробовал объединение, все равно просканировал все строки
У вас есть индексы на to_player_id и from_player_id?
Вероятно, это не имеет отношения к проблеме, но r.delete_status NOT IN (1, 3) легче читать и понимать.
да, у меня есть индексы на них
Ответы 2
Сделайте свой UNION DISTINCT (последний из списка) с помощью
INDEX(to_player_id, delete_status, id),
INDEX(from_player_id, delete_status, id)
Запрос выполнит два сканирования диапазона (не сканирования таблицы).
Сколько значений может принимать delete_status? Если это только (1,2,3), то используйте DELETE_STATUS = 2 для первого и =1 для второго. Это сделает его более эффективным.
это может быть 0,1,2,3
@ZeydLprn - Увы, простой оптимизации для этого не существует. Индексы и UNION DISTINCT все равно должны помочь.
Прежде всего: полное сканирование таблицы само по себе неплохо. Зачастую это самый быстрый подход. Вместо этого имеет смысл пройтись по индексу, когда затрагивается только очень небольшая часть таблицы.
Давайте предположим, что это так; критерии справедливы лишь для небольшой части таблицы, возможно, для 0,1%. Тогда применяется следующее:
Как уже упоминалось в комментариях к запросу: MySQL не очень хорошо оптимизирует запросы с условиями OR. Решением этой проблемы является использование UNION:
SELECT COUNT(*)
FROM
(
SELECT * FROM p_rpts WHERE to_player_id = '191717' AND delete_status NOT IN (1, 3)
UNION
SELECT * FROM p_rpts WHERE from_player_id = '191717' AND delete_status NOT IN (2, 3)
) combined;
Если гарантировано, что строка не может иметь одинаковые значения to_player_id и from_player_id, вместо этого вы можете использовать UNION ALL, что является одной из опробованных вами альтернатив. Однако в этом случае вам просто нужно добавить два счетчика, что можно сделать очевидным с помощью следующего запроса:
SELECT
(SELECT COUNT(*) FROM p_rpts WHERE to_player_id = '191717' AND delete_status NOT IN (1, 3))
+
(SELECT COUNT(*) FROM p_rpts WHERE from_player_id = '191717' AND delete_status NOT IN (2, 3)
;
Как теперь получить доступ к данным через индекс? Наиболее подходящими индексами будут:
create index idx1 on p_rpts (to_player_id) where delete_status NOT IN (1, 3);
create index idx2 on p_rpts (from_player_id) where delete_status NOT IN (2, 3);
MySQL, однако, не поддерживает частичные индексы, в которых предложение WHERE уменьшает содержимое индекса. Таким образом, наиболее подходящими индексами в MySQL являются
create index idx1 on p_rpts (to_player_id, delete_status);
create index idx2 on p_rpts (from_player_id, delete_status);
MySQL очень плохо оптимизирует
ORусловия. Используйте отдельные запросы для каждого условия и объедините результаты с помощьюUNION.