Блог

У меня есть две таблицы RequestHistoryLog и Request.

Таблица RequestHistoryLog содержит эти столбцы с 1,2 миллионами строк

 id(bigint), status(VARCHAR), byUser(VARCHAR),  delegatedUserFor(text), reqId(bigint) reqId with CONSTRAINTS FOREIGN KEY (`reqId`) REFERENCES `Request` (`id`)  

Таблица запросов содержит много столбцов с 0,4 миллионами строк

 id(bigint), title(VARCHAR), actionDateTime(Datetime), type(VARCHAR) etc.   

В журнале запросов есть несколько записей о состоянии запроса. И 1 запрос имеет много логисторий.

Здесь delegatedUserFor(тип текстового столбца) имеет несколько имен с электронными письмами.

 Example is: 'X(x@xyz.com)A(a@xyz.com)Y(y@xyz.com)'  

С помощью запроса ниже, я пытаюсь получить запросы, по которым(a@xyz.com) сделал состояние «утвержден», «выполнена», «выполнена», «запрашивать», «отклонено» или некоторые другие пользовательские сделал состояние(это означает, кто-то сделал состояния половины), но эта запись времени идет на колонки delegatedUserFor.

 SELECT *  FROM   (SELECT r.*   FROM Request AS r   JOIN RequestHistoryLog AS rh ON r.id = rh.reqId   where rh.status IN ("Approved", "Done", "Completed",  "Queried", "Rejected")  and (rh.byUser='a@xyz.com'  or rh.delegatedUserFor like '%(a@xyz.com)%')  and r.type='custom'  ) AS a   GROUP BY id   ORDER BY actionDateTime desc limit 10;  

Я пишу образец данных для обеих таблиц в виде:

 RequestHistoryLog Table id status byUser delegatedUserFor reqId 2 "Approved" 'A(a@xyz.com)' '' 15 3 "Rejected" 'G(g@xyz.com)' '' 15 4 "Approved" 'X(x@xyz.com)' 'A(a@xyz.com)Y(y@xyz.com)' 15  5 "Approved" 'X(x@xyz.com)' 'G(g@xyz.com)A(a@xyz.com)Y(y@xyz.com)' 16 6 "Rejected" 'B(b@xyz.com)' '' 16 7 "Completed"'Y(y@xyz.com)' '' 16  Request Table id title actionDateTime 15 "Request1" '2021-11-23 01:23:20' .......... 16 "Request2" '2021-11-23 11:23:20' ..........   

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

Приведенный выше запрос занимает много времени.

Итак, мне нужно, как его оптимизировать, чтобы получить быстрый результат ?

План А: (Вероятно, лучше, если не будет много строк типа=пользовательские)

Сделайте «полу-соединение»:

 SELECT r.*   FROM Request AS r   JOIN RequestHistoryLog AS rh ON r.id = rh.reqId  WHERE r.type = 'custom'  AND EXISTS ( SELECT 1 FROM RequestHistoryLog AS rh  WHERE r.id = rh.reqId  AND rh.status IN ("Approved", "Done", "Completed",  "Queried", "Rejected")  AND ( rh.byUser='a@xyz.com'  or rh.delegatedUserFor like '%(a@xyz.com)%' )  ORDER BY r.actionDateTime desc  LIMIT 10;  

Обратите внимание, что GROUP BY и вложенные SELECT избегаются. Имейте эти индексы:

 r: INDEX(type, actionDateTime) rh: INDEX(reqId, status, byUser, delegatedUserFor)  

План Б: (если тип часто =пользовательский и/или » xyz » встречается редко)

 FULLTEXT(byUser, delegatedUserFor)  

и сделать

 WHERE MATCH(byUser, delegatedUserFor) AGAINST (" xyz" IN BOOLEAN MODE)  AND (rh.byUser='a@xyz.com'  r rh.delegatedUserFor like '%(a@xyz.com)%')  

Это должно сначала найти строки с доменом xyz по ПОЛНОМУ ТЕКСТУ (быстро), а затем проверить с помощью других тестов (с меньшим количеством строк). Можно сделать и другие упрощения. Возможно, что-то вроде

 SELECT r.*  FROM ( SELECT DISTINCT rh.reqId  FROM RequestHistoryLog AS rh  WHERE MATCH ... AND ( ... OR ... )  AND rh.status IN (...)  ) AS x  JOIN Request AS r ON r.id = x.reqId   WHERE r.type = 'custom'  ORDER BY r.actionDateTime desc  LIMIT 10;  

(Никаких других индексов не требуется.) GROUP BY Заменяется на DISTINCT , что, вероятно, быстрее в этом случае. И FULLTEXT индекс может быть очень быстрым.

Обратите внимание, что полный текст имеет минимальную длину слова (по умолчанию 3), поэтому вам нужно избегать поиска «a» или любой другой строки короче этой. Также «com» может быть настолько распространенным, что его не стоит искать.

План С

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

Например, при поиске ...@hp.com обратите внимание , что «hp» слишком короткий, что делает невозможным полнотекстовый подход.

Вы, вероятно, знаете, какие значения типа r. встречаются более чем в 20% случаев, что делает план B лучшим выбором.

План D: если только один домен

Если пользователь и делегированный пользователь для одного и того же «xyz.com» или пусты, затем добавьте столбец, чтобы rh заменить грязный тест AND rh.domain = 'xyz.com' . И все равно сделайте что-нибудь, чтобы получить от GROUP BY этого .