MySQL 데이터베이스 쿼리에는 일정 시간이 걸립니다.

Question

행이 24,000개 있는 대규모 메시지 데이터베이스가 있습니다.

행 0-24를 표시합니다(총 2455455행, 쿼리 소요 시간은 0.0006초). 

메시지가 있으므로 대화를 더 빨리 로드해야 합니다. 대화가 적은 사용자의 경우 로드 방법은 다음과 같습니다(사용자의 대화 수는 3.2k개입니다).

행 0-24를 표시합니다(총 3266행, 쿼리 소요 시간 0.0345초) [id: 5009666... ​​​​- 4375619...]. 

대화가 많은 사용자의 경우 로드 속도가 느려집니다(사용자의 대화 수가 40,000개임):

행 0-24를 표시합니다(총 40296행, 쿼리 소요 시간 5.1763초) [id: 5021561... - 5015545...]. 

저는 다음 열에 색인 키를 사용합니다.

id, to_id, from_id, 시간, 본

데이터베이스 테이블:

CREATE TABLE `메시지`(
  `id` int(255) NULL이 아닙니다.
  `to_id` int(20) NULL이 아님,
  `from_id` int(20) NULL이 아님,
  `메시지` 긴 텍스트는 NULL이 아닙니다.
  `time` double NOT NULL,
  `seen` int(2) NOT NULL,
) 엔진=InnoDB 기본 문자 집합=latin1;



INSERT INTO `messages`(`id`, `to_id`, `from_id`, `message`, `time`, `seen`) 값
(2, 6001, 2, '안녕하세요', 1587581995.5222, 1);


ALTER TABLE `메시지`
  기본 키 추가(`id`),
  키 `time_idx`(`time`)를 추가하세요.
  키 `from_idx`(`from_id`)를 추가하세요.
  키 `to_idx`(`to_id`)를 추가하세요.
  키 추가 `seenx` (`seen`),
  키 `idx`(`id`) 추가;


ALTER TABLE `메시지`
  수정 `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5021570;
COMMIT;

저는 다음 검색어를 사용합니다:

SELECT
  *
에서
  메시지,
  (
    선택하다
      MAX(id) as lastid
    에서
      메시지
    어디
      (
        message.to_id = '1' -- 비교할 ID(로그인한 사용자의 ID)
        OR message.from_id = '1' -- 비교할 ID(로그인한 사용자의 ID)
      )
    그룹 기준
      연결(
        LEAST(messages.to_id, message.from_id),
        '.',
        최고(messages.to_id, message.from_id)
      )
  ) 대화로
어디
  ID = 대화.lastid
주문
  message.id DESC

데이터베이스 구조를 다시 만들어야 하는 경우 대화가 많은 사용자를 위해 어떻게 더 빠르게 만들 수 있는지 모르겠습니다.

Answer 1

흠, 어쩌면 테이블에 인덱스를 추가해 볼 수도 있습니다: https://www.drupal.org/docs/7/guidelines-for-sql/the-benefits-of-indexing-large-mysql-tables#: ~ :text=%20색인 생성&text=%20문%20to%20create%20색인,%20색인%20은%20고유한 %20이어야 합니다. 쿼리하는 행을 기반으로 복합 인덱스를 추가해야 합니다.

이렇게 해도 쿼리 시간이 개선되지 않으면 쿼리를 개선해야 합니다.

Answer 2

참고:

• OR 대신 UNION을 사용하세요(아래 참조)
• 중복 키가 존재합니다. PRIMARY KEY는 키이므로 KEY(id)를 삭제하세요
• 모든 열에 대해 맹목적으로 인덱스를 생성하지 마세요. 대신 쿼리를 사용하여 어떤 인덱스, 특히 복합 인덱스가 실제로 유용한지 확인하세요.
• GROUP BY 및 ORDER BY에서는 CONCAT이 불필요하며 비생산적일 수 있습니다.
• INT 유형의 경우 길이 필드는 무시됩니다. 당신이 가지고 있는 값은 20억 개의 값으로 제한됩니다. (0 또는 1만 있다고 가정할 때 see에 대한 과잉은 무엇입니까?)
• 새로운 구문 사용: JOIN..ON.
• 본 내용이 true/false로만 표시되면 해당 색인을 삭제하세요. (또는 도움이 될 것이라고 생각하는 쿼리를 보여주세요.)

CONCAT-LEAST-GREATEST - "friends_id"를 생성하기 위한 것인가요? 어쩌면 당신이 정말로 원하는 것은 "conversation_id"일까요? 현재는 두 명의 사용자가 여러 개의 "대화"를 할 수 없습니다. 그렇죠?

정말 필요한 경우 대화 ID에 대한 새 열을 만드세요. (현재 GROUP BY는 비효율적입니다.) 다음 코드에서는 이러한 ID가 필요하지 않습니다.

으아악

그리고 다음과 같은 "포함" 및 "복합" 색인을 갖습니다.

으아악

새 색인이 이 두 색인의 다른 모든 작업을 처리할 수 있으므로 KEY(to_id), KEY(from_id)를 제거했습니다.

동일한 효과가 있지만 실행 속도가 더 빠른 것 같아요.

결합:

으아악

(이 두 인덱스를 추가하세요)

더보기

저는 한때 UNION DISTINCT가 대화 ID의 필요성을 대체할 수 있다고 잘못 생각한 적이 있습니다. 그러나 그것은 진실이 아니다. 나는 즉시 몇 가지 해결책을 보았습니다:

• conversation_id를 추가하고 중복 제거에 사용하세요. (또한 UNION DISTINCT를 UNION ALL로 변경하여 결과 변경 없이 쿼리 속도를 조금 더 빠르게 만들었습니다.)
• 쿼리 결과를 (from_id, to_id,latestid)를 포함하는 임시 테이블에 넣은 다음 CONCAT-LEAST-GREATEST 트릭을 사용하여 대화를 중복 제거하고 마지막으로 메시지 테이블과 결합하여 다른 열을 가져옵니다.
• 이 임시 테이블 기술을 사용하면 작성 및 디버깅이 더 쉬워집니다. 세 번째 제안은 이러한 부분을 3단계 깊이의 중첩된 Select 문을 사용하여 하나의 (읽을 수 없는) 쿼리로 결합하는 것입니다.