느린 MYSQL 쿼리, 인덱스 이해 도움말 필요

느린 MYSQL 쿼리, 인덱스 이해 도움말 필요

이 투고를 위해 문제를 가장 순수한 형태로 단순화했습니다.테이블은 게임, 게임 태그 및 게임 태그 맵의 3가지입니다.

각 게임의 태그 표를 얻으려면 다음과 같이 하십시오.

SELECT `games_tags_map`.`game_id` as 'game_id', GROUP_CONCAT(`games_tags_map`.`tag_id`) as 'tags'
FROM `games_tags_map`
LEFT JOIN `games_tags` on `games_tags`.id = `games_tags_map`.`tag_id`
GROUP BY `games_tags_map`.game_id

~1ms 소요

SELECT `games`.`id` AS 'id' from `games`

이 작업은 1ms 미만이 소요됩니다.

다만, 이 양쪽 모두에 참가하려고 하면, 다음과 같습니다.

SELECT `games`.`id` AS 'id',
t.`tags` as `tags`
FROM `games`
LEFT JOIN (
    SELECT `games_tags_map`.`game_id` as 'game_id', GROUP_CONCAT(`games_tags_map`.`tag_id`) as 'tags'
    FROM `games_tags_map`
    LEFT JOIN `games_tags` on `games_tags`.id = `games_tags_map`.`tag_id`
    GROUP BY `games_tags_map`.game_id
) t ON t.`game_id`=`games`.`id`

소요시간은 100밀리초

단, 동등한 쿼리를 실행하면 다음과 같이 됩니다.

SELECT `games`.`id` AS 'id',
GROUP_CONCAT(DISTINCT `games_tags`.`tag`) AS 'tags'
FROM `games`
LEFT JOIN `games_tags_map` ON `games`.`id` = `games_tags_map`.`game_id`
LEFT JOIN `games_tags` ON `games_tags`.`id` = `games_tags_map`.`tag_id`
WHERE `games`.`active`=1
GROUP BY `games`.`id`

2ms면...단, 프라이머리 열(id) 이외의 순서로 주문해야 할 경우 80ms까지 소요됩니다.

이것은 실제 데이터베이스의 매우 단순화된 버전으로, 로딩 시간이 길어지고 웹 사이트에 문제가 발생하지만, 이러한 쿼리에 문제가 있습니다.

이렇게 매우 다른 로드 시간에 맞게 데이터베이스를 설정하는 방식에 결함이 분명히 있습니다.인덱스를 더 추가하려고 했지만 도움이 되지 않았습니다.'games' 테이블에는 프라이머리 인덱스 'id'가 있고 'games_authors_map' 테이블에는 'game_id'와 'author_id'로 구성된 프라이머리 인덱스가 있습니다.

문제가 있다는 건 알지만 고칠 수 없고 왜 그런지 모르겠어요.

제발 도와주세요.

모든 게임 태그 테이블에 가입하는 대신(그 자체로 문제 없음), 모든 태그가 앞에 있는 메인 게임 테이블에 집약 컬럼을 추가하여 가입할 필요가 없습니다.그런 다음 game_tags_map 테이블에서 태그를 추가하거나 삭제할 때마다 메인 게임 테이블을 갱신하는 트리거를 추가할 수 있습니다.만약 이것이 단지 웹 기반 게임 사이트에 표시하기 위한 목적이라면, 당신은 좋습니다.특정 유형의 게임에 관심이 있는 사람이 있다면 game_tags_map 테이블에 대한 쿼리를 통해 특정 관심사의 목록을 요약할 수 있습니다.

또한 매번 모든 게임에 대한 쿼리를 수행하므로 이 방법이 더 나은 방법일 수 있습니다.

먼저 첫 번째 쿼리를 보고 체크 표시, 따옴표, 긴 테이블 이름을 각각 gtm과 gt로 구분하면 쿼리는 왼쪽 조인이기 때문에 games_tags 테이블조차 사용하지 않습니다.

SELECT 
      gtm.game_id, 
      GROUP_CONCAT(gtm.tag_id) as tags
   FROM 
      games_tags_map gtm
         LEFT JOIN games_tags gt 
            on gtm.tag_id = gt.id
   GROUP BY 
      gtm.game_id

그래서 본질적으로는, 그것은 단지 그 이상도 이하도 아니다.

SELECT 
      gtm.game_id, 
      GROUP_CONCAT(gtm.tag_id) as tags
   FROM 
      games_tags_map gtm
   GROUP BY 
      gtm.game_id

단, ID가 아닌 ID가 나타내는 리터럴한 설명을 표시하기 위해 group_concat()을 의도하지 않은 경우입니다.ID에 의한 경우 두 번째 쿼리에서 games_tags 테이블의 내부 왼쪽 조인도 삭제할 수 있습니다.

SELECT 
      g.id AS id,
      t.tags as tags
   FROM 
      games g
         LEFT JOIN ( SELECT 
                          gtm.game_id, 
                          GROUP_CONCAT(gtm.tag_id) as tags
                       FROM 
                          games_tags_map gtm
                             LEFT JOIN games_tags 
                                on gtm.tag_id = gt.id
                       GROUP BY gtm.game_id ) t 
            ON g.id = t.game_id

마지막 쿼리에서는 태그 대신 실제로 TAG DESCRICTIONS를 얻기 위해 왼쪽 조인하고 있습니다.

SELECT 
      g.id,
      GROUP_CONCAT(DISTINCT gt.tag) AS tags
   FROM 
      games g
         LEFT JOIN games_tags_map gtm 
            ON g.id = gtm.game_id
            LEFT JOIN games_tags gt
               ON gtm.tag_id = gt.id 
   WHERE 
      g.active = 1
   GROUP BY 
      g.id

이 쿼리를 최적화하기 위해 다음과 같은 인덱스를 제공합니다.
이렇게 하면 전체 쿼리가 대상 인덱스와 함께 사용되며 전체 쿼리를 인덱스로 처리할 수 있으므로 원시 기본 데이터로 이동할 필요가 없습니다.

table           index
games           ( active, id )
games_tags_map  ( game_id, tag_id )
games_tags      ( id, tag )

마지막으로, 투고에 보다 상세한 정보를 제공하려고 할 때는, 항상 기존의 투고를 편집해, 보다 상세한 정보를 추가한 후, 유저에게 코멘트를 송신해, 컨텐츠/답변/응답 정보를 추가할 수 있습니다.

내의 ( 「 」 ） 。games_tags_map.tag_id,games_tags_map.game_id) 및 가 해결됩니다. 및 에서는 쿼리를 정렬할 때 사용하는 컬럼을 인덱스합니다.이것에 의해, 문제가 해결됩니다.

언급URL : https://stackoverflow.com/questions/35382780/slow-mysql-queries-need-help-understanding-indexes