SQL - 대규모 데이터 집합에서 여러 레코드의 최신 정보를 반환합니다.

SQL - 대규모 데이터 집합에서 여러 레코드의 최신 정보를 반환합니다.

배경

나는 가지고 있다stock_price약 1000개의 주식에 대한 장중 역사적 주가를 저장하는 테이블오래된 데이터는 정기적으로 삭제되지만 테이블에는 정기적으로 500만 개 이상의 레코드가 있습니다.구조가 느슨하다:

| id     | stock_id | value | change |  created_at         |
|--------|----------|-------|--------|---------------------|
| 12345  | 1        | 50    | 2.12   | 2020-05-05 17:39:00 |
| 12346  | 2        | 25    | 1.23   | 2020-05-05 17:39:00 |

API endpoint를 위해 20개 정도의 주식의 최신 주가를 정기적으로 취득해야 합니다.최초 구현에서는 다음과 같이 주식당 단일 쿼리를 실행했습니다.

select * from stock_prices where stock_id = 1 order by created_at desc limit 1

파트 1: 비효율적인 쿼리

20개 이상의 쿼리로 다소 비효율적이었지만 효과가 있었습니다.올바른 관계(stock has Many stock_price)를 사용하도록 코드(Laravel 6)가 업데이트되었고, 이에 따라 다음과 같은 쿼리가 생성되었습니다.

select
  *
from
  `stock_prices`
where
  `stock_prices`.`stock_id` in (1, 2, 3, 4, 5)
order by
  `id` desc

이렇게 하면 쿼리는 절약되지만 실행에는 1~2초가 걸립니다.입니다.explain는 외부 키 인덱스를 사용하더라도 항상 50k 이상의 행을 쿼리해야 함을 나타냅니다.내 다음 생각은 내가 더해서limit원하는 재고의 수와 동일한 행의 수만 반환하도록 쿼리로 이동합니다.지금 쿼리:

select
  *
from
  `stock_prices`
where
  `stock_prices`.`stock_id` in (1, 2, 3, 4, 5)
order by
  `id` desc
limit
  5

파트 2: 쿼리에서 레코드가 누락될 수 있음

퍼포먼스는 매우 뛰어납니다.이것에 의해 밀리초 레벨의 처리가 가능합니다.그러나 주식의 1/2에 대한 가격을 반환하지 않는 문제가 발생할 수 있습니다.그 이후로는limit추가된 경우, 다음 재고 전에 둘 이상의 가격(행)이 있는 주식이 있으면 행 개수 중 하나를 "추정"합니다.

일부 종목은 1분마다, 다른 종목은 15분마다 데이터를 끌어오기 때문에 이는 매우 현실적인 시나리오입니다.그래서 위의 질문들이 있을 수 있습니다.limit는 하나의 스톡에 대해 여러 행을 끌어온 후 다른 스톡에 대한 데이터를 반환하지 않습니다.

| id   | stock_id | value | change | created_at     |
|------|----------|-------|--------|----------------|
| 5000 | 1        | 50    | 0.5    | 5/5/2020 17:00 |
| 5001 | 1        | 51    | 1      | 5/5/2020 17:01 |
| 6001 | 2        | 25    | 2.2    | 5/5/2020 17:00 |
| 6002 | 3        | 35    | 3.2    | 5/5/2020 17:00 |
| 6003 | 4        | 10    | 1.3    | 5/5/2020 17:00 |

이 시나리오에서는, 다음과 같은 것을 알 수 있습니다.stock_id의1에는 데이터 간격이 더 많기 때문에 쿼리가 실행되면 해당 ID에 대한 레코드가 2개 반환된 후 목록 아래로 계속됩니다.5개의 기록을 달성한 후 멈췄습니다. 즉,stock id의5데이터는 있지만 반환되지 않았습니다.짐작하신 바와 같이 데이터가 반환되지 않았을 때 앱에서 작업이 중단됩니다.

파트 3: 해결 시도

1. 가장 확실한 답은 이 문제를 해결하는 것입니다'를 추가하는 것 같습니다.GROUP BY stock_id재고당 예상과 동일한 수의 결과를 얻도록 요구하는 방법으로요.아쉽게도 Part 1로 돌아갑니다.이 경우 쿼리는 동작하는 동안 1-2초 걸립니다.이는 이전에 제한 없이 동일한 50k+ 행을 통과해야 하기 때문입니다.이래도 내 살림살이가 나아지지 않는다.

2. 그 다음 생각은 자의적으로 만들자는 것이었다.LIMIT모든 행을 캡처할 수 있습니다.쿼리는 각각 사용 가능한 데이터 간격이 다른 수천 개의 주식을 조합할 수 있기 때문에 이는 예측 가능한 솔루션이 아닙니다.가장 극단적인 예는 매분마다 주가가 오르는 것입니다. 즉, 두 번째 주식이 나타나기 전에 약 350개 이상의 열을 가질 수 있습니다.한 쿼리의 재고 수(예: 50)를 곱해도 여전히 15k+ 행 이상의 쿼리가 필요합니다.실현 가능하지만 이상적이지 않으며 잠재적으로 확장성이 없습니다.

제4부: 제안사항

단지 주가 데이터를 얻기 위해 50개 이상의 DB 쿼리를 하나의 API 호출로 시작하는 것이 그렇게 나쁜 관행입니까?LIMIT실패 가능성을 최소화해서 편안할 수 있도록 해야 하나요?큰 테이블 청크를 쿼리하지 않고 필요한 행을 반환할 수 있는 SQL의 다른 방법이 있습니까?

아무쪼록 잘 부탁드립니다.

은 " " " " 입니다.union all:

(select * from stock_prices where stock_id = 1 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 2 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 3 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 4 order by created_at desc limit 1)
union all
(select * from stock_prices where stock_id = 5 order by created_at desc limit 1)

할 수 .stock_prices(stock_id, created_at [desc])도 쉽쉽,를 사용하면in인덱스를 효과적으로 사용할 수 없습니다.

Groupwise-max

SELECT b.*
    FROM ( SELECT stock_id, MAX(created_at) AS created_at
            FROM stock_proces
            GROUP BY stock_id
         ) AS a
    JOIN stock_prices AS b  USING(stock_id, created_at)

필요:

INDEX(stock_id, created_at)

같은 재고의 2열을 1초에 할 수 있으면, 2열이 됩니다.다른 방법은 아래 링크를 참조하십시오.

, 을 약약면면면면, 으로 합니다.PRIMARY KEY and 를 없애다id퍼포먼스에도 도움이 됩니다.

자세한 내용은 http://mysql.rjweb.org/doc.php/groupwise_max#using_an_uncorrelated_subquery 를 참조해 주세요.

언급URL : https://stackoverflow.com/questions/61649768/sql-return-latest-of-multiple-records-from-large-data-set