MySQL: 각 그룹의 최신 항목 가져오기

Question

다음과 같은 데이터가 포함된 테이블messages이 있습니다.

으아악

쿼리select * from messages group by name를 실행하면 얻을 수 있는 결과는 다음과 같습니다.

으아악

다음 결과를 반환하는 쿼리는 무엇입니까?

으아악

즉, 각 그룹의 마지막 레코드가 반환되어야 합니다.

현재 내가 사용하는 쿼리는 다음과 같습니다.

으아악

하지만 이는 매우 비효율적인 것 같습니다. 동일한 결과를 얻는 다른 방법이 있습니까?

Answer 1

UPD: 2017-03-31, 버전 5.7.5 MySQL은 기본적으로 ONLY_FULL_GROUP_BY 스위치를 활성화합니다(따라서 비결정적 GROUP BY 쿼리는 비활성화됩니다). 또한 GROUP BY 구현을 업데이트했으며 스위치가 비활성화된 경우에도 솔루션이 예상대로 작동하지 않을 수 있습니다. 확인해 볼 필요가 있습니다.

Bill Karwin의 위 솔루션은 그룹 내의 항목 수가 매우 적을 때 잘 작동하지만 그룹이 상당히 클 경우 솔루션에 대략 n*n/2 + n/2가 필요하므로 쿼리 성능이 저하됩니다. 비교만 하세요 는 NULLIS NULL입니다.

1868444618684446 行和 1182 组的 InnoDB 表上进行了测试。该表包含功能测试的测试结果，并以 (test_id, request_id) 作为主键。因此，test_id 是一个组，我正在为每个test_id 搜索最后一个request_id 행과 1182

그룹이 포함된 InnoDB 테이블에서 테스트했습니다. 이 테이블에는 기능 테스트에 대한 테스트 결과가 포함되어 있으며 기본 키로 (test_id, request_id)

가 있습니다. 그래서 test_id

는 그룹이고 저는 각 test_id

에 대해 마지막 request_id

를 검색하고 있습니다.

• Bill의 솔루션은 현재 몇 시간 동안 내 Dell e4310에서 실행되고 있으며 커버링 인덱스(따라서 EXPLAIN의 인덱스 사용)에서 실행 중이지만 언제 완료될지 알 수 없습니다. (group_id, item_value) 对是每个 group_id 中的最后一个值，即如果我们按降序遍历索引，则为每个 group_id
동일한 아이디어를 기반으로 한 몇 가지 다른 솔루션이 있습니다.
• 기본 인덱스가 BTREE 인덱스인 경우(일반적인 경우) 가장 큰 (group_id, item_value)
쌍은 각 group_id
• 의 마지막 값입니다. 즉, 내림차순, 그러면 각 group_id
;

의 첫 번째 것입니다.

인덱스에 포함된 값을 읽으면 인덱스 순서대로 값이 읽혀집니다. 모든 인덱스에는 해당 인덱스에 연결된 기본 키 열이 암시적으로 포함되어 있습니다(즉, 기본 키가 포함 인덱스에 있음). 아래 솔루션에서는 기본 키에 대해 직접 작업을 수행합니다. 귀하의 경우 기본 키 열을 결과에 추가하기만 하면 됩니다.

많은 경우 하위 쿼리에서 원하는 순서로 필수 행 ID를 수집하고 하위 쿼리 결과를 ID에 연결하는 것이 훨씬 저렴합니다. 하위 쿼리 결과의 각 행에 대해 MySQL은 기본 키를 기반으로 가져오기를 수행해야 하기 때문에 하위 쿼리가 조인에 먼저 들어가고 행은 하위 쿼리의 ID 순서대로 출력됩니다(만약 우리가 조인에 대한 명시적인 ORDER BY를 생략하세요 )

MySQL이 인덱스를 사용하는 3가지 방법

은 일부 세부 사항을 이해하는 데 도움이 되는 훌륭한 기사입니다.

해결책 1

이 작업은 1,800만 개 이상의 행에서 약 0.8초가 소요되는 믿을 수 없을 정도로 빠릅니다. 으아아아 순서를 ASC로 변경하려면 ID만 반환하는 하위 쿼리에 넣고 나머지 열을 조인하는 하위 쿼리로 사용하세요.

으아아아

내 데이터에는 약 1.2초 정도 소요됩니다.

🎜🎜해결책 2🎜🎜 🎜내 시계에 약 19초가 걸린 또 다른 솔루션은 다음과 같습니다. 🎜으아악

또한 테스트를 내림차순으로 반환합니다. 전체 인덱스 스캔을 수행하기 때문에 속도는 훨씬 느리지만 각 그룹별로 최대 N개의 행을 출력하는 방법에 대한 아이디어를 제공합니다.

이 쿼리의 단점은 쿼리 캐시가 결과를 캐시할 수 없다는 것입니다.

Answer 2

MySQL 8.0은 이제 거의 모든 널리 사용되는 SQL 구현과 같은 창 기능을 지원합니다. 이 표준 구문을 사용하면 그룹당 최대 n개의 쿼리를 작성할 수 있습니다.

으아아아

이 방법과 그룹화된 최대 행 수를 찾는 다른 방법은 MySQL 매뉴얼에 설명되어 있습니다.

다음은 제가 2009년에 이 질문에 쓴 원래 답변입니다.

---

저는 다음과 같이 솔루션을 작성했습니다:

으아아아

성능에 관해서는 데이터의 성격에 따라 하나의 솔루션이 더 나을 수도 있습니다. 따라서 두 쿼리를 모두 테스트하고 데이터베이스에 따라 성능이 더 좋은 쿼리를 사용해야 합니다.

예를 들어 StackOverflow 8월 데이터 덤프의 사본이 있습니다. 벤치마킹 목적으로 사용하겠습니다. Posts 테이블에는 1,114,357개의 행이 있습니다. 내 Macbook Pro 2.40GHz의 MySQL 5.0.75에서 실행 중입니다.

지정된 사용자 ID(mine)에 대한 최신 게시물을 찾는 쿼리를 작성하겠습니다.

먼저 하위 쿼리에서 를 사용하여 @Eric이 표시한 기술 GROUP BY을 사용하세요.

으아아아

심지어 EXPLAIN분석까지 16초 이상 소요:

으아아아

이제 사용 팁 LEFT JOIN을 사용하세요.

으아아아

EXPLAIN 분석에 따르면 두 테이블 모두 인덱스를 사용할 수 있습니다.

으아아아

---

이것은 내 Posts 테이블의 DDL입니다.

으아아아

---

댓글 작성자 참고 사항: 다른 버전의 MySQL, 다른 데이터 세트 또는 다른 테이블 디자인을 사용하여 또 다른 벤치마크를 실행하려면 직접 수행하십시오. 위의 기술을 시연해봤습니다. Stack Overflow는 모든 작업을 대신해 주는 것이 아니라 소프트웨어 개발 작업을 수행하는 방법을 보여주기 위해 왔습니다.