2백만 개가 넘는 행이 있는 SQL 쿼리를 최적화하는 방법
P粉127901279
P粉127901279 2023-09-01 18:09:31
0
2
592
<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>
P粉127901279
P粉127901279

모든 응답(2)
P粉141455512

쿼리가 제대로 실행되려면 다음 인덱스가 필요할 것 같습니다(데이터베이스 설계의 일부로 한 번만 수행하면 됩니다).

으아악

처음 두 열은 어떤 순서로든 정렬될 수 있습니다.

P粉297434909

동일한 code, stationID 쌍이 동일한 날짜/시간의 두 행을 가질 수 없는 한, 창 기능을 사용하는 것은 큰 망치를 사용하여 너트를 깨는 것과 약간 비슷합니다.

으아악

다음 색인이 필요합니다:

으아악

파생 테이블은 인덱스에서 구축된 다음 테이블에서 필요한 행만 읽을 수 있으므로 이 쿼리는 1밀리초 미만이 소요됩니다.

또는 각 code, stationID 쌍이 특정 기간(1시간, 1일, 1주) 내에 업데이트된 가격을 수신한다는 것을 알고 있는 경우 where 절을 추가하여 창 기능이 수행해야 하는 작업량을 크게 줄일 수 있습니다. :

으아악
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿