MySQL의 복합 전체 텍스트 인덱스

Question

특정 사용자별로 사용자 메시지를 검색할 수 있는 시스템을 원합니다. 다음과 같은 테이블이 있다고 가정해보세요

으아아아

"foo"라는 단어가 포함된 사용자 1의 모든 메시지를 검색하려면 여기서 어떤 인덱스를 사용해야 합니까?

1. 단순, 고유하지 않은 인덱스 user_id

   
   

   특정 사용자 메시지만 필터링한 다음 특정 단어를 완전히 검색합니다.

2. 전문 message

   
   

   색인 이는 모든 사용자의 모든 메시지를 찾아 ID별로 필터링하는데, 이는 사용자 수가 많을 때 비효율적입니다.

3. Compositeindex of

   user_id
   및

   message

   따라서 전체 텍스트 인덱스 트리는 사용자별로 개별적으로 생성되므로 개별적으로 검색할 수 있습니다. 쿼리 중에 시스템은 ID를 기준으로 메시지를 필터링한 다음 인덱스의 나머지 행에 대해 텍스트 검색을 수행합니다.

내가 아는 한. 마지막 항목은 불가능합니다. 따라서 첫 번째 옵션을 사용해야 한다고 가정합니다. 사용자가 수천 명이면 성능이 더 좋아질까요?

각 메시지가 100개 정도라면 전체 반복에 리소스가 너무 많이 소모되지 않나요?

메시지에 사용자 이름을 포함하고 BOOLEAN 전체 텍스트 검색 모드를 사용할 수도 있지만 색인 user_id를 사용하는 것보다 속도가 느릴 것 같습니다.

Answer 1

message 上添加全文索引，在 user_id에 일반 색인을 추가하고 다음 쿼리를 사용해야 합니다.

으아악

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

참고: 두 인덱스의 오버헤드(삽입/업데이트/삭제 속도가 느림)를 감당할 수 있는 경우에만 이 작업을 수행하세요. 또한 사용자당 몇 개의 메시지만 알고 한다면 간단한 색인을 사용하고 애플리케이션 계층에서 정규식 또는 유사한 작업을 수행하는 것이 합리적일 수 있습니다.

Answer 2

@Alden Quimby의 답변은 그 자체로는 정확하지만 MySQL은 최상의 인덱스를 선택하기 위해 시도만 할 것이며 이 결정을 내리는 능력은 전체 텍스트 인덱스로 인해 제한되기 때문에 더 많은 이야기가 있습니다. 최적화 프로그램.

실제로 일어난 일은 다음과 같습니다:

지정된 user_id가 테이블의 일치하는 행 0개 또는 1개에 존재하는 경우 최적화 프로그램은 이를 인식하고 user_id를 이 쿼리의 인덱스로 선택합니다. 신속하게 실행하세요.

그렇지 않으면 최적화 프로그램은 전체 텍스트 인덱스를 선택하고 전체 텍스트 인덱스와 일치하는 각 행을 필터링하여 WHERE 절과 일치하는 user_id를 포함하지 않는 행을 제거합니다. 그렇게 빠르지는 않습니다.

그래서 이것은 실제로 "최선의" 경로는 아닙니다. 테이블에 관심 있는 내용이 거의 없다는 것을 알 때 전체 텍스트 검색을 수행하지 않는 멋진 최적화 기능을 갖춘 전체 텍스트에 가깝습니다.

이는 전체 텍스트 인덱스가 최적화 프로그램에 의미 있는 통계를 제공하지 않기 때문에 발생합니다. "예, 쿼리에서 행 1개만 확인하면 될 것 같습니다."라고만 나옵니다... 물론 이는 최적화 프로그램을 크게 만족시키므로 인덱스에 정수가 없는 한 전체 텍스트 인덱스가 가장 낮은 비용으로 입찰에서 승리합니다. 값이 너무 낮거나 낮습니다.

그렇다고 먼저 시도하지 않겠다는 뜻은 아닙니다.

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

으아악

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

으아악

이제 전체 텍스트 인덱스는 두 열의 결합된 전체 텍스트 인덱스에 새끼 고양이, 강아지 및 "user_id_500"이 나타나는 행만 일치시키는 역할을 담당하지만, 여전히 정수 필터를 사용하여 이를 확인하는 것이 좋습니다. "user_id_500" 메시지에 무작위로 발생하더라도 최종 결과는 제한됩니다.