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 * from message group by name 쿼리를 실행하면 다음과 같은 결과를 얻습니다.

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, MySQL 버전 5.7.5에는 ONLY_FULL_GROUP_BY 스위치가 기본적으로 활성화되어 있습니다(따라서 비결정적 GROUP BY 쿼리는 비활성화됩니다). 또한 GROUP BY 구현을 업데이트했으며 스위치가 비활성화된 경우에도 솔루션이 더 이상 예상대로 작동하지 않을 수 있습니다. 검사가 필요합니다.

Bill Karwin의 솔루션은 그룹 내 항목 수가 적을 때 잘 작동하지만 솔루션에 약 n*n/2 + n/2次IS NULL비교가 필요하기 때문에 그룹이 클수록 쿼리 성능이 저하됩니다.

나는 18684446行和1182个组的InnoDB表上进行了测试。该表包含功能测试的测试结果，并且(test_id, request_id)是主键。因此，test_id是一个组，我正在寻找每个test_id的最后一个request_id에 포함되어 있습니다.

Bill의 솔루션은 내 Dell e4310에서 몇 시간 동안 실행되었습니다. 비록 해당 솔루션이 포함된 인덱스에서 작동하지만(따라서 EXPLAIN이 표시됨using index) 언제 완료될지 모르겠습니다.

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

• 기본 인덱스가 BTREE 인덱스인 경우(일반적인 경우) 각 group_id中的最大(group_id, item_value)对就是每个group_id的最后一个值，如果我们按降序遍历索引，则是每个group_id의 첫 번째 값;
인덱스에 포함된 값을 읽으면 인덱스 순서대로 값이 읽혀집니다.
• 각 인덱스에는 추가 기본 키 열이 암시적으로 포함되어 있습니다(즉, 기본 키가 포함 인덱스에 있음). 아래 솔루션에서는 기본 키를 직접 조작했습니다. 귀하의 경우에는 결과에 기본 키 열만 추가하면 됩니다.
• 대부분의 경우 더 저렴한 접근 방식은 하위 쿼리에서 원하는 순서로 필수 행 ID를 수집하고 하위 쿼리 결과를 ID와 연결하는 것입니다. MySQL은 하위 쿼리 결과의 각 행에 대한 기본 키를 기반으로 한 단일 가져오기를 요구하므로 하위 쿼리는 조인에서 먼저 배치되고 행은 하위 쿼리에서 ID 순서대로 출력됩니다(명시적인 ORDER BY의 생략 시). 조인)

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

은 몇 가지 세부 사항을 이해하는 데 좋은 기사입니다.

해결책 1

이 솔루션은 매우 빠르며 1,800만 개 이상의 데이터 행에 대해 약 0.8초가 걸립니다.

으아악

순서를 오름차순으로 변경하려면 하위 쿼리에 넣고 ID만 반환하고 다른 열과 하위 쿼리로 조인하세요.

으아악

제 데이터의 경우 이 솔루션은 약 1.2초 정도 소요됩니다.

해결책 2

여기 또 다른 해결책이 있습니다. 제 테이블에서는 약 19초가 걸립니다.

으아악

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

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

Answer 2

MySQL 8.0은 이제 거의 모든 널리 사용되는 SQL 구현과 마찬가지로 Window 함수를 지원합니다. 이 표준 구문을 사용하여 그룹당 최대 n개 쿼리를 작성할 수 있습니다.

으아악

MySQL 매뉴얼에서는 이 방법과 그룹화된 가장 큰 행을 찾는 다른 방법을 보여줍니다.

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

---

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

으아악

성능과 관련하여 데이터의 성격에 따라 솔루션 중 하나가 더 나을 수도 있습니다. 따라서 두 쿼리를 모두 테스트하고 데이터베이스 성능에 따라 더 나은 쿼리를 선택해야 합니다.

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

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

하위 쿼리에서 를 사용하는 EricGROUP BY의 기술을 처음 사용했습니다:

으아악

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

으아악

이제 LEFT JOIN using 내 기술 을 사용하면 동일한 쿼리 결과가 생성됩니다.

으아악

EXPLAIN분석에 따르면 두 테이블 모두 인덱스를 사용할 수 있는 것으로 나타났습니다.

으아악

---

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

으아악

---

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