MySQL의 복합 FULLTEXT 인덱스

MySQL의 복합 FULLTEXT 인덱스

특정 사용자에 의해 사용자 메시지를 검색할 수 있는 시스템 스위치를 만들고 싶습니다. 다음 표를 가지고 있다고 가정합니다.

create table messages(
  user_id int,
  message nvarchar(500));

그래서 사용자 1에서 'foo'라는 단어가 포함된 모든 메시지를 검색하려면 여기서 어떤 색인을 사용해야 합니까?

1. 단순하고 고유하지 않은 인덱스 user_id
   특정 사용자 메시지만 필터링한 다음 특정 단어를 전체 검색합니다.
2. 메시지의 FULLTEXT 인덱스
   이것은 모든 사용자의 모든 메시지를 찾은 다음 ID로 필터링 할 것이며, 많은 사용자의 경우 매우 비효율적인 것으로 보입니다.
3. user_id와 메시지 모두에서 combound 인덱스
   따라서 사용자별로 전체 텍스트 색인 트리가 따로 생성되므로 개별적으로 검색할 수 있습니다.쿼리 중에 시스템은 ID로 메시지를 필터링한 다음 인덱스의 나머지 행에 대해 텍스트 검색을 수행합니다.

마지막 A.F.A.I.K.는 불가능합니다.그럼 1st 옵션을 사용해야 할 것 같은데, 몇 만 명의 사용자가 있을 경우 성능이 더 좋아지는 건가요?

각 메시지에 ~100개의 메시지가 있는 경우 전체 반복에 리소스 비용이 많이 들지 않습니까?

아마도 메시지에 사용자 이름을 포함시키고 Boolean full text search mode를 사용할 수 있겠지만 indexed user_id를 사용하는 것보다 느릴 것 같습니다.

@Alden Quimby의 대답은 옳지만, MySQL은 최적의 인덱스만 선택하려고 할 것이고, 전체 텍스트 인덱스가 옵티마이저와 상호 작용하는 방식 때문에 그 결정을 내릴 수 있는 능력이 제한되기 때문에 이야기는 더 많습니다.

실제로 일어나는 일은 다음과 같습니다.

지정된 user_id가 테이블의 0개 또는 1개의 일치하는 행에 존재하는 경우 최적화자는 이를 인식하고 user_id를 해당 쿼리의 인덱스로 선택합니다.빠른 실행.

그렇지 않으면 Optimizer는 전체 텍스트 색인을 선택하여 전체 텍스트 색인과 일치하는 모든 행을 필터링하여 WHERE 절과 일치하는 user_id가 포함되지 않는 행을 제거합니다.그렇게 빠르지는 않습니다.

따라서 이것은 진정한 "최적"인 길이 아닙니다.이는 표에 거의 관심이 없다는 것을 알고 있다는 한 가지 조건 하에서 전체 텍스트 검색을 피할 수 있는 좋은 최적화와 함께 전체 텍스트에 더 가깝습니다.

이것이 고장나는 이유는 전체 텍스트 색인이 옵티마이저에 의미 있는 통계를 반환하지 않기 때문입니다."네, 그 쿼리는 아마 하나의 행만 확인하면 될 것입니다." 라고만 표시됩니다.물론, 이는 옵티마이저를 매우 기쁘게 하기 때문에 정수 값을 가진 인덱스도 비교적 낮거나 낮지 않은 한, 전체 텍스트 인덱스가 가장 낮은 비용에 낙찰됩니다.

그렇다고 제가 먼저 이런 식으로 시도하지는 않을 겁니다.

전체 텍스트 쿼리에 가장 적합한 다른 옵션이 있습니다.IN BOOLEAN MODE즉, CONCAT('user_id_', user_id') 또는 이와 유사한 것으로 채우는 다른 열을 만든 다음 2열 전체 텍스트 인덱스를 선언하는 것입니다.

filter_string VARCHAR(48) # populated with CONCAT('user_id_',user_id);
....
FULLTEXT KEY (message,filter_string)

그런 다음 쿼리의 모든 것을 지정합니다.

SELECT ...
 WHERE user_id = 500 AND
 MATCH (message,filter_string) AGAINST ('+kittens +puppies +user_id_500' IN BOOLEAN MODE);

이제 전체 텍스트 색인은 고양이, 강아지 및 "user_id_500"이 두 열의 결합된 전체 텍스트 색인에 나타나는 행만 일치시킬 책임이 있지만, 메시지에 "user_id_500"이 무작위로 나타나더라도 최종 결과가 제한되도록 정수 필터를 사용해야 합니다.

전체 텍스트 색인을 추가해야 합니다.message에 대한 정기적인 지수.user_id, 쿼리를 사용합니다.

SELECT *
FROM messages
WHERE MATCH(message) AGAINST(@search_query)
AND user_id = @user_id;

3번 옵션을 할 수 없다는 게 맞습니다.그러나 1과 2 중 하나를 선택하기 보다는 MySQL이 대신 작업을 수행하도록 하십시오.MySQL은 두 개의 인덱스 중 하나만 사용하고 선형 스캔을 수행하여 두 번째 필터를 완성하지만 각 인덱스의 효과를 추정하고 최적의 인덱스를 선택합니다.

참고: 두 인덱스(슬로우 인서트/업데이트/삭제)의 오버헤드를 감당할 수 있는 경우에만 이 작업을 수행합니다.또한 각 사용자가 몇 개의 메시지만 가질 수 있다는 것을 알고 있다면, 예, 간단한 색인을 사용하고 응용 프로그램 계층에서 정규 함수를 수행하는 것이 합리적일 수 있습니다.

Optimizer 추적을 켜고 "고려된_실행_계획"을 찾습니다.저는 옵티마이저가 항상 선택할 것이라고 주장합니다.FULLTEXT인덱스, 다른 인덱스가 더 나을 수도 있습니다.이것은 아마도 그 이유가 비용이 많이 들기 때문일 것입니다.MATCHFT 인덱스를 구축할 때와 같이 사전 계산되지 않습니다.

Optimizer Trace에 대한 자세한 내용: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#optimizer_trace (그 문서의 앞 부분은 FULLTEXT에 대한 나의 팁입니다.)

언급URL : https://stackoverflow.com/questions/73938012/slow-query-with-mariadb-and-full-text-search