각 그룹의 마지막 레코드 검색 - MySQL

Question

아래와 같이 데이터를 포함하는 messages 테이블이 있습니다.

ID 이름 Other_Columns
-------------
1 A_data_1
2A A_data_2
3A A_data_3
4B B_data_1
5B B_data_2
6C C_data_1

이름으로 메시지 그룹에서 select *를 선택하는 경우 결과는 다음과 같습니다.

1 A A_data_1
4B B_data_1
6C C_data_1

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

3 A A_data_3
5B B_data_2
6C C_data_1

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

현재 제가 사용하는 검색어는 다음과 같습니다.

SELECT
  *
에서(선택
  *
메시지에서
ID DESC로 주문) AS x
GROUP BY 이름

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

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 of only IS NULL 비교가 필요하기 때문에 그룹이 다소 클 경우 쿼리 성능이 저하됩니다.

저는 18684446 rows with 1182 groups. The table contains testresults for functional tests and has the (test_id, request_id) as the primary key. Thus, test_id is a group and I was searching for the last request_id for each test_id.

의 InnoDB 테이블에서 테스트를 했습니다.

Bill의 솔루션은 내 Dell e4310에서 이미 몇 시간 동안 실행되었으며 적용 범위 인덱스에서 작동하더라도 언제 완료될지 알 수 없습니다(따라서 EXPLAIN의 using index).

동일한 아이디어를 기반으로 한 몇 가지 다른 솔루션이 있습니다.

• 기본 인덱스가 BTREE 인덱스인 경우(일반적으로 그렇습니다), 인덱스를 내림차순으로 살펴보면 가장 큰 (group_id, item_value) pair is the last value within each group_id, that is the first for each 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 August 데이터 덤프가 있습니다. 이를 벤치마킹에 사용하겠습니다. Posts 테이블에는 1,114,357개의 행이 있으며 이는 내 Macbook Pro 2.40GHz에서 실행됩니다. . 지정된 사용자 ID(mine)에 대한 최신 게시물을 찾는 쿼리를 작성하겠습니다.

첫 번째로 @Eric이 하위 쿼리에

를 사용하여 shown 기술을 사용하는 방법은 다음과 같습니다.GROUP BY 으아아아 심지어

분석에도 16초 이상 소요됩니다.EXPLAIN 으아아아

이제

내 기술과 :LEFT JOIN을 사용하여 동일한 쿼리 결과를 생성합니다. 으아아아

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

으아아아 EXPLAIN

내

---

테이블에 대한 DDL은 다음과 같습니다.

으아아아 Posts

---

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