2백만 개가 넘는 행이 있는 SQL 쿼리를 최적화하는 방법
P粉127901279
2023-09-01 18:09:31
<p>행이 200만 개 이상인 SQL 데이터베이스가 있는데 빠르게 성장하고 있습니다. 열은 많지 않고 <code>코드, 가격, 날짜 및 역 ID</code>만 있습니다. </p>
<p>목적은 코드와 스테이션 ID로 최신 가격을 얻는 것입니다.
쿼리는 훌륭하게 작동하지만 10초 이상 걸립니다. </p>
<p>쿼리를 최적화할 수 있는 방법이 있나요? </p>
<pre class="brush:php;toolbar:false;">$statement = $this->pdo->prepare(
'CTE AS 사용
(
SELECT stationID AS ind, 코드, CAST(price AS DOUBLE ) AS 가격, 날짜
,ROW_NUMBER() 이상(
코드별 PARTITION, 스테이션 ID
날짜별 주문 DESC
) 최신으로
가격에서
)
선택하다 *
CTE에서
최신 = 1
'
);
$문->execute();
$results = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);</pre>
<p>편집:
첫 번째 열에는 "id"라는 인덱스가 있습니다. 이것이 도움이 되는지 모르겠습니다. </p>
<p>데이터베이스(InnoDB)는 다음과 같습니다. </p>
<pre class="brush:php;toolbar:false;">id 기본 - int
stationID - 정수
코드 - 정수
가격-십진수(10,5)
날짜 - 날짜/시간</pre>
<p>편집 2:</p>
<p>결과는 stationID별로 그룹화되어야 하며, 각 stationID에 대해 여러 행이 표시되어야 합니다. 최신 날짜가 포함된 각 코드마다 한 줄씩 표시됩니다.영상这样:</p>
<pre class="brush:php;toolbar:false;">22456:
코드: 1
가격: 3
날짜: 2023-06-21
코드: 2
가격: 2
날짜: 2023-06-21
코드: 3
가격: 5
날짜: 2023-06-21
22457:
코드: 1
가격: 10
날짜: 2023-06-21
코드: 2
가격: 1
날짜: 2023-06-21
코드: 3
가격: 33
날짜: 2023년 6월 21일</pre>
<p>json 출력은 다음과 같아야 합니다. image这样:</p>
<pre class="brush:php;toolbar:false;">{"1000001":[{"코드":1,"가격":1.661,"날짜":"2023-06- 06 12:46:32","최신":1},{"코드":2, "가격":1.867, "날짜":"2023-06-06 12:46:32", "최신":1},{"코드":3, "가격":1.05, "날짜":"2023-06-06 12:46:32","최신":1}, {"코드":5,"가격":1.818,"날짜":"2023-06-06 12:46:32","최신":1},{"코드":6, "가격":1.879,"날짜":"2023-06-06 12:46:32","최신":1}],"1000002":[{"코드":1," ;가격":1.65,"날짜":"2023-06-03 08:53:26","최신":1},{"코드":2,"가격":1.868," 날짜":"2023-06-03 08:53:26","최신":1},{"코드":6,"가격":1.889,"날짜":"2023-06 -03 08:53:27","최신":1}],…</pre></p>
쿼리가 제대로 실행되려면 다음 인덱스가 필요할 것 같습니다(데이터베이스 설계의 일부로 한 번만 수행하면 됩니다).
으아악처음 두 열은 어떤 순서로든 정렬될 수 있습니다.
동일한
으아악code, stationID
쌍이 동일한 날짜/시간의 두 행을 가질 수 없는 한, 창 기능을 사용하는 것은 큰 망치를 사용하여 너트를 깨는 것과 약간 비슷합니다.다음 색인이 필요합니다:
으아악파생 테이블은 인덱스에서 구축된 다음 테이블에서 필요한 행만 읽을 수 있으므로 이 쿼리는 1밀리초 미만이 소요됩니다.
또는 각
으아악code, stationID
쌍이 특정 기간(1시간, 1일, 1주) 내에 업데이트된 가격을 수신한다는 것을 알고 있는 경우 where 절을 추가하여 창 기능이 수행해야 하는 작업량을 크게 줄일 수 있습니다. :