특정 사용자별로 사용자 메시지를 검색할 수 있는 시스템을 원합니다. 다음과 같은 테이블이 있다고 가정해보세요
으아아아"foo"라는 단어가 포함된 사용자 1의 모든 메시지를 검색하려면 여기서 어떤 인덱스를 사용해야 합니까?
특정 사용자 메시지만 필터링한 다음 특정 단어를 완전히 검색합니다.
색인 이는 모든 사용자의 모든 메시지를 찾아 ID별로 필터링하는데, 이는 사용자 수가 많을 때 비효율적입니다.
user_id
및
따라서 전체 텍스트 인덱스 트리는 사용자별로 개별적으로 생성되므로 개별적으로 검색할 수 있습니다. 쿼리 중에 시스템은 ID를 기준으로 메시지를 필터링한 다음 인덱스의 나머지 행에 대해 텍스트 검색을 수행합니다.
내가 아는 한. 마지막 항목은 불가능합니다. 따라서 첫 번째 옵션을 사용해야 한다고 가정합니다. 사용자가 수천 명이면 성능이 더 좋아질까요?
각 메시지가 100개 정도라면 전체 반복에 리소스가 너무 많이 소모되지 않나요?
메시지에 사용자 이름을 포함하고 BOOLEAN 전체 텍스트 검색 모드를 사용할 수도 있지만 색인 user_id를 사용하는 것보다 속도가 느릴 것 같습니다.
P粉4211197782023-11-08 15:18:58
message 上添加全文索引，在 user_id에 일반 색인을 추가하고 다음 쿼리를 사용해야 합니다.
맞습니다. 옵션 3을 할 수 없습니다. 그러나 1과 2 중 하나를 선택하려고 하는 대신 MySQL이 대신 작업을 수행하도록 하십시오. MySQL은 두 인덱스 중 하나만 사용하고 선형 스캔을 수행하여 두 번째 필터링을 완료하지만 각 인덱스의 효율성을 추정하고 가장 좋은 인덱스를 선택합니다.
참고: 두 인덱스의 오버헤드(삽입/업데이트/삭제 속도가 느림)를 감당할 수 있는 경우에만 이 작업을 수행하세요. 또한 사용자당 몇 개의 메시지만 알고 한다면 간단한 색인을 사용하고 애플리케이션 계층에서 정규식 또는 유사한 작업을 수행하는 것이 합리적일 수 있습니다.
P粉0769873862023-11-08 12:16:14
@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" 메시지에 무작위로 발생하더라도 최종 결과는 제한됩니다.