반응형
MariaDB 쿼리가 너무 느립니다.
오늘 이 시나리오에 대한 MariaDB 쿼리와 관련하여 성능 문제가 발생했습니다.주유소가 저장되는 스테이션이라는 테이블에 post_code, uuid, 이름 및 이 경우에 사용되지 않는 기타 정보가 포함된 약 17k 항목이 있습니다.그리고 가격(~210Mio 항목)이라는 다른 표가 있는데, 단일 항목은 특정 날짜 시간과 station_uuid로 단일 스테이션에 대한 단일 가격 변경을 나타냅니다.
우선 하루 30분 간격으로 경유 최저가를 회수해야 했습니다.이것은 잘 작동합니다.그런데 가격이 변경된 역의 이름을 추가해야 했습니다.그래서 저는 특정 join Logic을 추가했지만 시간별로 그룹화할 때 정확한 스테이션 이름을 사용하는 것이 보장되지 않는다는 것을 기억했습니다.그래서 하위 쿼리를 사용하려고 했지만 시간 초과 오류가 발생했습니다.
price.dates, price.date, stations.post _code에 대해 단일 열 BTREE 인덱스를 사용하고 있습니다.
지금까지 사용해 본 SQL 쿼리는 다음과 같습니다.
- 이름 없음:
SELECT
MIN(diesel) AS "Minimal",
date - interval minute(date)%30 minute AS time
FROM prices
LEFT JOIN stations
ON prices.station_uuid = stations.uuid
WHERE `date` BETWEEN (SELECT FROM_UNIXTIME(1684627200)) AND (SELECT FROM_UNIXTIME(1684713599))
AND diesel > 0.0
AND post_code = 59929
GROUP BY date_format(time, '%Y-%m-%d %H:%i');
- 이름 포함:
SELECT
MIN(diesel) AS "Minimal",
name AS "Tankstelle"
date - interval minute(date)%30 minute AS time
FROM prices
LEFT JOIN stations
ON prices.station_uuid = stations.uuid
WHERE `date` BETWEEN (SELECT FROM_UNIXTIME(1684627200)) AND (SELECT FROM_UNIXTIME(1684713599))
AND diesel > 0.0
AND post_code = 33106
GROUP BY date_format(time, '%Y-%m-%d %H:%i');
- 하위 쿼리 사용:
SELECT
stations.name AS "Tankstelle",
date - interval minute(date)%30 minute as time
FROM prices
LEFT JOIN stations
ON prices.station_uuid = stations.uuid
WHERE `date` BETWEEN (SELECT FROM_UNIXTIME(1684627200)) AND (SELECT FROM_UNIXTIME(1684713599))
AND diesel > 0.0
AND post_code = 59929
AND diesel = (
SELECT MIN(diesel) as Min
FROM prices p
WHERE p.date BETWEEN (prices.date - interval minute(prices.date)%30 minute) AND (prices.date - interval ((minute(prices.date)%30)+30) minute))
처음 세 개의 의견에 답하기 위한 EDIT(23.05.2023):
MariaDB 버전:10.11.3-MariaDB-1:10.11.3+maria~ubu2204
"디젤"은 디젤의 가격이지만 e10에 대한 변경인 경우 디젤은 0이 되므로 해당 행을 사용하지 않도록 0 값에 대해 필터링합니다.
다소 무지하게 들릴 수도 있지만, 저는 "관계를 가진" 것이 어떻게 작용하는지 정확히 알지 못하기 때문에, 저는 아직 의견이 없습니다.그리고 클러스터된 키가 기본 키입니다. 그렇지 않나요?예인 경우 표 정의로 응답해야 합니다.그렇지 않다면 기본 키와 어떻게 다른지 설명해야 하기 때문에 훨씬 더 무지하게 들립니다.
미리 도와주셔서 감사합니다.
EXPLE의 결과는 다음과 같습니다.
쿼리 1:
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| 1 | SIMPLE | prices | range | date,diesel | date | 5 | NULL | 695600 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | stations | eq_ref | PRIMARY,post_code | PRIMARY | 16 | tankonix.prices.station_uuid | 1 | Using where |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
질문 2:
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
| 1 | SIMPLE | prices | range | date,diesel | date | 5 | NULL | 695600 | Using index condition; Using where; Using temporary; Using filesort |
| 1 | SIMPLE | stations | eq_ref | PRIMARY,post_code | PRIMARY | 16 | tankonix.prices.station_uuid | 1 | Using where |
+------+-------------+----------+--------+-------------------+---------+---------+------------------------------+--------+---------------------------------------------------------------------+
질문 3: 결과 설명
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
| 1 | PRIMARY | prices | range | date,diesel | date | 5 | NULL | 695600 | Using index condition; Using where |
| 1 | PRIMARY | stations | eq_ref | PRIMARY,post_code | PRIMARY | 16 | tankonix.prices.station_uuid | 1 | Using where |
| 4 | DEPENDENT SUBQUERY | p | ALL | date | NULL | NULL | NULL | 207323157 | Using where |
+------+--------------------+----------+--------+-------------------+---------+---------+------------------------------+-----------+------------------------------------+
내 테이블 정의:
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| prices | CREATE TABLE `prices` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`station_uuid` text NOT NULL,
`diesel` float NOT NULL,
`e10` float NOT NULL,
`e5` float NOT NULL,
`dieselchange` tinyint(4) NOT NULL,
`e5change` tinyint(4) NOT NULL,
`e10change` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `date` (`date`) USING HASH,
KEY `e5` (`e5`),
KEY `e10` (`e10`),
KEY `diesel` (`diesel`)
) ENGINE=InnoDB AUTO_INCREMENT=381367318 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| stations | CREATE TABLE `stations` (
`uuid` uuid NOT NULL,
`name` varchar(127) NOT NULL,
`brand` varchar(127) DEFAULT NULL,
`street` varchar(127) NOT NULL,
`house_number` varchar(7) NOT NULL DEFAULT '',
`post_code` varchar(5) NOT NULL,
`city` varchar(31) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`openingtimes_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`openingtimes_json`)),
`first_active` date NOT NULL,
PRIMARY KEY (`uuid`),
KEY `post_code` (`post_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
BETWEEN ( SELECT FROM_UNIXTIME(1684627200) ) AND ...
-->
BETWEEN FROM_UNIXTIME(1684627200) AND ...
즉, 단순히 표현을 사용하십시오. 그럴 필요는 없습니다.SELECT그것.
그 이후로WHERE절은 다른 테이블의 열을 테스트합니다. Optimizer는 테이블 중 하나를 선택합니다.p대s)부터 시작합니다.실수할 수도 있습니다.
에 대한 테스트가 있기 때문에s.PostCode에서WHERE,그LEFT JOIN정말로JOIN인간이 쿼리를 읽을 수 있도록 변경하십시오. JOIN으로 할 수 .s로 시작하도록 강요받는 대신에p.
FROM prices AS p
JOIN stations AS s ON s.uuid = p.station_uuid
WHERE p.date BETWEEN FROM_UNIXTIME(1684627200)
AND FROM_UNIXTIME(1684713599)
AND p.diesel > 0.0
AND s.post_code = 1
s로 시작:
s: (you effectively have this)
INDEX(post_code, -- because of =
uuid) -- for "covering"
p: (you have this)
INDEX(station_uuid, -- =
date, -- assuming it is more selective than diesel
diesel) -- covering
p로 시작:
p: INDEX(date, diesel, station_uuid) -- covering, but otherwise unexciting
s: the PRIMARY KEY(uuid) will be used effectively.
사변항후권(장경변후(▁changing)로 )JOIN두 ): 두째번번p색인을 달다
주의:
- 는 옵마이다중하선택다니합으로 하는 것 중 입니다.
s그리고.p대략적인 통계에 따라 다릅니다. - 으로 각보인암다복포다함니의 합니다.
PRIMARY KEY(나머지 행에 도달할 수 있도록).이런 이유로(post_code, uuid)는 와동합다니와 .(post_code)당신이 이미 가지고 있는 것. - 는 것을 발견하지 못했습니다.
OVER(PARTITION BY...)마법의 성능을 가지고 있기 때문에, 저는 그것이 질문의 가장 느린 부분이 될 것이라고 걱정합니다. - 나의 Groupwise-Max는 그것이 가장 빠르지는 않지만 상관없는 하위 쿼리가 겪는 중복을 보여주는 것을 피한다는 것을 발견했습니다.
언급URL : https://stackoverflow.com/questions/76314100/mariadb-query-is-way-too-slow
반응형
'programing' 카테고리의 다른 글
| R 패키지를 언로드할 때 동적 라이브러리를 언로드해야 합니까? (0) | 2023.06.14 |
|---|---|
| 동일한 페이지에서 다른 버전의 jQuery를 실행하려면 어떻게 해야 합니까? (0) | 2023.06.14 |
| 웹 팜 사이트의 web.config에 machineKey 추가 (0) | 2023.06.14 |
| MariaDB는 함수 인덱스(함수 기반 인덱스)를 만들 수 있습니까? (0) | 2023.06.09 |
| 전력 쿼리에서 COALESCE를 수행하려면 어떻게 해야 합니까? (0) | 2023.06.09 |