대용량 데이터 테이블에서 각 사용자의 첫 번째/마지막 행을 선택하는 SQL 퍼포먼스

대용량 데이터 테이블에서 각 사용자의 첫 번째/마지막 행을 선택하는 SQL 퍼포먼스

그룹당 최대 n개의 투고를 꽤 많이 읽었지만 성능 면에서 좋은 해결책을 찾지 못한 것 같습니다.10.1.43-MariaDB를 실행하고 있습니다.

주어진 기간 동안 데이터 값의 변화를 가져오려고 하기 때문에 이 기간 중 가장 빠른 행과 최신 행을 가져와야 합니다.현재 계산해야 하는 시간 범위 중 가장 많은 행 수는 약 70만 개이며 앞으로 증가할 것입니다.지금으로서는 최근 문의와 빠른 문의 두 가지를 하고 있습니다만, 이마저도 현재로선 성능이 저하되고 있습니다.표는 다음과 같습니다.

user_id    data          date        
4567          109          28/06/2019 11:04:45        
4252          309          18/06/2019 11:04:45      
4567          77          18/02/2019 11:04:45        
7893          1123          22/06/2019 11:04:45         
4252          303          11/06/2019 11:04:45        
4252          317          19/06/2019 11:04:45              

날짜 및 user_id 열이 인덱싱됩니다.데이터베이스 내에서 행 순서를 지정하지 않으면 행이 특정 순서로 정렬되지 않습니다.

이 문제에 대해 가장 많이 알게 된 것은 현재 연간(700k 데이터 포인트)에 대한 다음과 같은 쿼리입니다.

    SELECT user_id, 
    MIN(date) as date, data
    FROM datapoint_table 
    WHERE date >= '2019-01-14'
    GROUP BY user_id

이를 통해 대략 0.05초만에 정확한 날짜와 user_id를 알 수 있습니다.단, 그룹당 최대 n개의 일반적인 문제와 마찬가지로 나머지 행(이 경우 데이터)은 날짜와 같은 행이 아닙니다.다른 유사한 질문들을 읽고 다음과 같은 서브쿼리를 시도했습니다.

SELECT a.user_id, a.date, a.data
FROM datapoint_table a
INNER JOIN (
    SELECT datapoint_table.user_id, 
    MIN(date) as date, data
    FROM datapoint_table 
    WHERE date >= '2019-01-01'
    GROUP BY user_id
) b ON a.user_id = b.user_id AND a.date = b.date

이 쿼리는 완료하는 데 약 15초가 걸리고 올바른 데이터 값을 가져옵니다.15대는 너무 길어서 첫 번째 쿼리가 너무 빨랐는데 내가 뭔가 잘못하고 있는 게 틀림없어.또한 user_id에 대해 group by로 데이터(MAX)-(MIN)를 시도했지만 성능도 느렸습니다.

각 사용자에 대해 날짜와 동일한 데이터 값 또는 최신 및 초기 데이터 차이를 얻는 보다 효율적인 방법은 무엇입니까?

MariaDB 또는 MySQL의 최신 버전을 사용하고 있다고 가정하면,ROW_NUMBER각 사용자의 초기 레코드를 찾는 가장 효율적인 방법이 될 수 있습니다.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) rn
    FROM datapoint_table
    WHERE date > '2019-01-14'
)

SELECT user_id, data, date
FROM cte
WHERE rn = 1;

위에 다음 인덱스를 추가하는 것도 고려할 수 있습니다.

CREATE INDEX ON datapoint_table (user_id, date);

열을 반대로 하여 다음과 같은 변형 인덱스를 시도할 수도 있습니다.

CREATE INDEX ON datapoint_table (date, user_id);

데이터 및 실행 계획에 따라 최적의 인덱스 버전이 무엇인지 명확하지 않습니다.이상적으로는 위의 두 인덱스 중 하나가 데이터베이스 실행에 도움이 됩니다.ROW_NUMBER WHERE절을 클릭합니다.

이 를 하지 않는 ROW_NUMBER 있는접근법으로 진행해도

SELECT d1.user_id, d1.data, d1.date
FROM datapoint_table d1
INNER JOIN
(
    SELECT user_id, MIN(date) AS min_date
    FROM datapoint_table
    WHERE date > '2019-01-14'
    GROUP BY user_id
) d2
    ON d1.user_id = d2.user AND d1.date = d2.min_date
WHERE
    d1.date > '2019-01-14';

한 번 '아, 아, 아, 아, 아, 아, 아, 아, 아'의 실행 속도를 높여야 합니다.GROUP BY서브쿼리

언급URL : https://stackoverflow.com/questions/59729151/sql-performance-on-selecting-first-last-row-for-each-user-on-bigger-data-table