itsource

ORDERBY 사용 시 MySQL Slow JOIN 쿼리

mycopycode 2022. 9. 14. 22:27
반응형

ORDERBY 사용 시 MySQL Slow JOIN 쿼리

이 쿼리에 문제가 있습니다.

SELECT a.*
FROM smartressort AS s
JOIN smartressort_to_ressort AS str
    ON s.id = str.smartressort_id
JOIN article_to_ressort AS atr
    ON str.ressort_id = atr.ressort_id
JOIN article AS a FORCE INDEX (source_created)
    ON atr.article_id = a.id    
WHERE
    s.id = 1
ORDER BY
    a.created_at DESC
LIMIT 25;

이건 정말 느리네요. 어떤 때는 14초 걸리기도 하고요.

설명:

1   SIMPLE  s   const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  str ref PRIMARY,ressort_id  PRIMARY 4   const   1   Using index
1   SIMPLE  atr ref PRIMARY,article_id  PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index
1   SIMPLE  a   ALL NULL    NULL    NULL    NULL    146677  Using where; Using join buffer (flat, BNL join)

마지막 '모두' 타입은 정말 나쁜 타입입니다.하지만 이미 지수를 강제로 사용하려 했지만 소용이 없었어요.

문서 테이블은 다음과 같습니다.

CREATE TABLE `article` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`node_id` varchar(255) NOT NULL DEFAULT '',
`object_id` varchar(255) DEFAULT NULL,
`headline_1` varchar(255) NOT NULL DEFAULT '',
`created_at` datetime(3) NOT NULL,
`updated_at` datetime(3) NOT NULL,
`teaser_text` longtext NOT NULL,
`content_text` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `article_nodeid` (`node_id`),
KEY `article_objectid` (`object_id`),
KEY `source_created` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=161116 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

FORCE INDEX를 제거하면 설명 기능이 향상되지만 쿼리는 여전히 느립니다.

힘 지수 없이 설명:

1   SIMPLE  s   const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  str ref PRIMARY,ressort_id  PRIMARY 4   const   1   Using index
1   SIMPLE  atr ref PRIMARY,article_id  PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index
1   SIMPLE  a   eq_ref  PRIMARY PRIMARY 4   com.nps.lvz-prod.atr.article_id 1

다른 Smartressort ID(3)의 경우 다음과 같습니다.

1   SIMPLE  s   const   PRIMARY PRIMARY 4   const   1   Using index; Using temporary; Using filesort
1   SIMPLE  str ref PRIMARY,ressort_id  PRIMARY 4   const   13  Using index
1   SIMPLE  atr ref PRIMARY,article_id  PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index
1   SIMPLE  a   eq_ref  PRIMARY PRIMARY 4   com.nps.lvz-prod.atr.article_id 1

여기 1개의 Smartressort에 13명의 Resort가 있습니다.행: 1x1x13x1262x1 = 16.406

1) 이 요청을 더 빨리 진행하려면 어떻게 해야 합니까?

2) 의 문제점은 무엇입니까?source_created인덱스?

그SELECT *당신의 질의는 추악하고, 이것은 종종 지수 킬러가 될 수 있습니다.정의하려는 대부분의 인덱스가 모든 열을 포함하지는 않을 것이기 때문에 인덱스의 사용이 금지될 수 있습니다.SELECT *이 답변의 접근법은 쿼리의 다른 모든 테이블을 인덱싱하는 것입니다. 따라서 MySQL은 이 명령어를 통해 한 번의 스캔만 수행하도록 장려합니다.article테이블.

CREATE INDEX idx1 ON article_to_ressort (article_id, ressort_id);
CREATE INDEX idx2 ON smartressort_to_ressort (ressort_id, smartressort_id);

이 두 지수는 결합 프로세스를 가속화할 것입니다.에 대한 인덱스는 정의하지 않았습니다.smartressort테이블, 예를 들어맞는다는 전제 하에id열은 이미 기본 키입니다.저는 아마도 당신의 질문을 다음부터 쓸 것입니다.article테이블과 바깥쪽으로 합류하는 건 상관없을 거예요

또한 인덱스를 강제하는 것은 대부분 나쁜 생각이거나 필요하지 않습니다.옵티마이저는 일반적으로 인덱스를 사용하는 것이 가장 좋은 시기를 파악할 수 있습니다.

SELECT many columns FROM tables ORDER BY something LIMIT few는 악명 높은 퍼포먼스 안티패턴입니다.결과 세트의 몇 행을 제외한 모든 행을 폐기하기 위해 행과 열의 난잡한 모든 것을 검색 및 정렬해야 합니다.

비결은 어떤 가치가 있는지 알아내는 것이다.article.id결과 세트에 필요한 값만 가져옵니다.지연 결합이라고 합니다.

이것으로, 그 세트는id가치.아마도 이 프로그램에 참여할 필요가 없을 것이다.smartressort표로 작성 이유smartressort_to_ressort를 포함합니다.id필요한 가치를 제공합니다.

                 SELECT a.id
                   FROM article a
                   JOIN article_to_ressort atr ON a.id = atr.article_id
                   JOIN smartressort_to_ressort str ON atr.ressort_id = str.ressort_id
                  WHERE str.smartressort_id = 1
                  ORDER BY a.created_at DESC
                  LIMIT 25

그런 다음 이를 하위 쿼리로 사용하여 필요한 행을 가져올 수 있습니다.

SELECT a.*
  FROM article a
 WHERE a.id IN (
                 SELECT a.id
                   FROM article a
                   JOIN article_to_ressort atr ON a.id = atr.article_id
                   JOIN smartressort_to_ressort str ON atr.ressort_id = str.ressort_id
                  WHERE str.smartressort_id = 1
                  ORDER BY a.created_at DESC
                  LIMIT 25
               )
 ORDER BY a.created_at DESC

두 번째 ORDER BY는 문서의 행이 예측 가능한 순서인지 확인합니다.따라서 인덱스 최적화 작업은 하위 쿼리에만 적용하면 됩니다.

@TimBiegelsen의 훌륭한 답변과 더불어, 저는 당신의 주소를 수정하는 것을 추천합니다.source_created 링크:

...
KEY `source_created` (`id`, `created_at`)

MySQL은 정렬에 사용할 수 있으며 16406 행을 모두 가져올 필요가 없습니다.도움이 될 수도 있고 아닐 수도 있지만 시도해 볼 가치가 있습니다(아마도 사용하기 위한 설명문 포함).

「」를 할 수 .smartressort테이블에는 아무것도 추가되지 않으므로 쿼리에서 테이블을 선택합니다.

다음은 당신의 쿼리를 다시 쓴 것입니다.smart resort #1에 대한 모든 resorts와 이러한 resort에 대한 모든 문서를 원합니다.이 중에서 최신 25개를 보여드리지요.

SELECT *
FROM article
WHERE id IN
(
  SELECT article_id
  FROM article_to_ressort 
  WHERE ressort_id IN
  (
    SELECT ressort_id
    FROM smartressort_to_ressort
    WHERE smartressort_id = 1
  )
)
ORDER BY created_at DESC
LIMIT 25;

그러면 DBMS를 지원하기 위해 필요한 인덱스는 무엇입니까?테이블부터 합니다.smartressort_to_ressort는, 할 때, 의 레코드로 합니다.smartressort_id 있는 「이것들」을 하고 싶다.ressort_id따라서 인덱스는 이 두 개의 열을 이 순서로 포함해야 합니다.일일도 입니다.article_to_ressort 그 ★★★ressort_id ★★★★★★★★★★★★★★★★★」article_id ID를 으로 기사를 하고, 검색된 기사 ID를 created_at.

CREATE INDEX idx1 ON smartressort_to_ressort (smartressort_id, ressort_id);
CREATE INDEX idx2 ON article_to_ressort (ressort_id, article_id);
CREATE INDEX idx3 ON article (id, created_at);

어쨌든 이 인덱스는 DBMS에 대한 제안일 뿐입니다. DBMS에 반대 결정을 내릴 수도 있습니다.은 특히 됩니다.article의 ". DBMS" 1에 몇 에 액세스 할 합니까?smartressort_id 몇 할 수 IN?? 이 전체의 10% 여러했을 수 .DBMS가 이것이 전체 문서 ID의 약 10%라고 생각하는 경우, 이미 여러 행에 대해 인덱스를 혼동하기보다는 표를 순차적으로 읽기로 결정한 것일 수 있습니다.

그래서 저에게 해결책은 다음과 같았습니다.

SELECT a.*
FROM article as a  USE INDEX (source_created)
where a.id in (
             SELECT atr.article_id
               from smartressort_to_ressort str 
               JOIN article_to_ressort atr  ON atr.ressort_id = str.ressort_id
              WHERE str.smartressort_id = 1
) 
ORDER BY a.created_at DESC
LIMIT 25;

최대 35ms만 필요합니다.다음과 같이 설명합니다.

1   PRIMARY a   index   NULL    source_created  7   NULL    1   
1   PRIMARY <subquery2> eq_ref  distinct_key    distinct_key    4   func    1
2   MATERIALIZED    str ref PRIMARY,ressort_id,idx1 PRIMARY 4   const   1   Using index
2   MATERIALIZED    atr ref PRIMARY,article_id,idx2 PRIMARY 4   com.nps.lvz-prod.str.ressort_id 1262    Using index

그래도 이 쿼리 설명서가 더 나은 것 같습니다만, 정확한 이유는 알 수 없습니다.

explain SELECT a.*, NOW()
FROM article as a  USE INDEX (source_created)
where a.id in (SELECT atr.article_id
    FROM smartressort AS s
    JOIN smartressort_to_ressort AS str
    ON s.id = str.smartressort_id
    JOIN article_to_ressort AS atr
    ON str.ressort_id = atr.ressort_id
    WHERE s.id = 1
) 
ORDER BY a.created_at DESC
LIMIT 25;

출력:

1   PRIMARY s   const   PRIMARY PRIMARY 4   const   1   Using index
1   PRIMARY a   index   NULL    source_created  7   NULL    25  
1   PRIMARY str ref PRIMARY,ressort_id,idx1 PRIMARY 4   const   1   Using index
1   PRIMARY atr eq_ref  PRIMARY,article_id,idx2 PRIMARY 8   com.nps.lvz-prod.str.ressort_id,com.nps.lvz-prod.a.id   1   Using index; FirstMatch(a)

언급URL : https://stackoverflow.com/questions/55119336/mysql-slow-join-query-when-using-orderby

반응형

'itsource' 카테고리의 다른 글

PHP에서 1달러 금액으로 숫자를 포맷하려면 어떻게 해야 합니까?  (0) 2022.09.14
업데이트 후 Android Studio에서 리소스 오류 발생:리소스를 찾을 수 없습니다.  (0) 2022.09.14
컨텍스트 경로 없이 요청 URI를 얻는 방법  (0) 2022.09.14
String Builder를 루프에서 재사용하는 것이 좋습니까?  (0) 2022.09.14
목록의 마지막 항목을 삭제하려면 어떻게 해야 합니까?  (0) 2022.09.14

'itsource'의 다른글

  • 현재글ORDERBY 사용 시 MySQL Slow JOIN 쿼리

관련글

댓글

티스토리툴바