반응형
범위별 그룹화 및 임시 없는 상수 기준
TokuDB 스토리지 엔진을 활용하는 여러 Exchange의 계측기에 대한 견적을 포함하는 매우 기본적인 표:
CREATE TABLE `quotes` (
`ticker` char(4) NOT NULL,
`timestamp` time(3) NOT NULL,
`price` decimal(7,2) unsigned NOT NULL,
`size` smallint(5) unsigned NOT NULL,
`exchange` char(3) NOT NULL,
KEY `best_price` (`ticker`,`timestamp`,`exchange`,`price`),
KEY `best_size` (`exchange`,`ticker`,`price`,`timestamp`)
) ENGINE=TokuDB
모든 거래소에서 가장 좋은 가격을 문의하면 항상 임시 테이블을 사용합니다.의 존재exchange그리고.price인덱스에서 클러스터된 키와 동일한 인덱스 스캔만 생성하는 것으로 나타납니다.(ticker, timestamp)TokuDB에 있습니다.
EXPLAIN SELECT max(price),exchange
FROM quotes
WHERE
ticker="A" AND
timestamp BETWEEN "15:15:22.328961" AND "15:17:22.328961"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: quotes
type: range
possible_keys: best_price
key: best_price
key_len: 9
ref: NULL
rows: 2690
Extra: Using where; Using index; Using temporary
임시 테이블을 사용하지 않는 설정을 정의할 수 있습니까?이것은, 를 드롭 할 때만 가능한 것처럼 보입니다.timestamp기사:
EXPLAIN SELECT max(price),exchange
FROM quotes
WHERE
ticker="A"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: quotes
type: range
possible_keys: best_price
key: best_size
key_len: 7
ref: NULL
rows: 96
Extra: Using where; Using index for group-by
베스트 프라이스 쿼리의 출력 예:
+------------+----------+
| max(price) | exchange |
+------------+----------+
| 41.06 | BTY |
| 41.06 | DEA |
| 41.07 | NYS |
| 41.07 | THM |
| 41.06 | PSE |
| 41.07 | BAT |
| 41.06 | DEX |
| 41.06 | BOS |
| 41.06 | ADC |
| 41.06 | XPH |
+------------+----------+
10 rows in set (0.01 sec)
또한 병목(3ms)은 시간 범위 내의 각 행을 처리합니다.
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000071 |
| checking permissions | 0.000005 |
| Opening tables | 0.000016 |
| After opening tables | 0.000006 |
| System lock | 0.000014 |
| Table lock | 0.000002 |
| After table lock | 0.000005 |
| init | 0.000038 |
| optimizing | 0.000024 |
| statistics | 0.000155 |
| preparing | 0.000028 |
| executing | 0.000003 |
| Copying to tmp table | 0.000031 |
| Copying to tmp table | 0.003381 |
| Sending data | 0.000017 |
| end | 0.000004 |
| removing tmp table | 0.000020 |
| end | 0.000002 |
| query end | 0.000005 |
| closing tables | 0.000005 |
| freeing items | 0.000006 |
| updating status | 0.000011 |
| cleaning up | 0.000002 |
+----------------------+----------+
시간 범위에는 2316개의 행이 포함되어 있으며 교환별로 구분됩니다.
+----------+----------+
| exchange | count(*) |
+----------+----------+
| ADC | 71 |
| BAT | 298 |
| BOS | 129 |
| BTY | 266 |
| DEA | 153 |
| DEX | 60 |
| NYS | 530 |
| PSE | 325 |
| THM | 453 |
| XPH | 31 |
+----------+----------+
covering index의 모든 치환을 추가해봤지만 MariaDB는 이보다 더 좋은 키를 찾을 수 없었다.대신 봐야 할 다른 데이터베이스가 있나요?
시간 범위 및 티커 기호에 대한 데이터 세트의 예: http://pastebin.com/b5RcTXAs
답은 모두 최적화에 관한 것입니다.MySQL은 인덱스를 사용하여 각 교환을 가져오는 것보다 더 합리적인 것으로 간주되기 때문에 임시 테이블을 사용하도록 선택했습니다.클러스터된 인덱스를 사용하는 경우exchange,ticker,timestamp그러면 쿼리는 임시 없이 작동할 수 있습니다.
MariaDB [trth]> explain SELECT min(ask_price),exchange
FROM quotes
USE INDEX (exchange_ticker_timestamp)
WHERE exchange IN ("NYS","BOS","CIN","ADC","DEX","DEA","MID","PSE","THM","WCB","BAT","XPH","BTY") AND
ticker="A" AND
timestamp BETWEEN "15:15:22.328961" AND "15:17:22.328961"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: quotes
type: range
possible_keys: exchange_ticker_timestamp
key: exchange_ticker_timestamp
key_len: 10
ref: NULL
rows: 2589
Extra: Using where; Using index
MariaDB [trth]> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000079 |
| checking permissions | 0.000006 |
| Opening tables | 0.000014 |
| After opening tables | 0.000011 |
| System lock | 0.000014 |
| Table lock | 0.000003 |
| After table lock | 0.000005 |
| init | 0.000043 |
| optimizing | 0.000019 |
| statistics | 0.000234 |
| preparing | 0.000027 |
| executing | 0.000008 |
| Sorting result | 0.000002 |
| Sending data | 0.002985 |
| end | 0.000006 |
| query end | 0.000010 |
| closing tables | 0.000006 |
| freeing items | 0.000007 |
| updating status | 0.000138 |
| cleaning up | 0.000004 |
+----------------------+----------+
임시 테이블 그룹화와 비교:
MariaDB [trth]> explain SELECT min(ask_price),exchange
FROM quotes
WHERE ticker="A" AND
timestamp BETWEEN "15:15:22.328961" AND "15:17:22.328961"
GROUP BY exchange
ORDER BY NULL \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: quotes
type: range
possible_keys: ticker_timestamp
key: ticker_timestamp
key_len: 9
ref: NULL
rows: 1515
Extra: Using where; Using temporary
MariaDB [trth]> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000091 |
| checking permissions | 0.000009 |
| Opening tables | 0.000037 |
| After opening tables | 0.000009 |
| System lock | 0.000052 |
| Table lock | 0.000004 |
| After table lock | 0.000009 |
| init | 0.000049 |
| optimizing | 0.000025 |
| statistics | 0.000144 |
| preparing | 0.000039 |
| executing | 0.000003 |
| Copying to tmp table | 0.000040 |
| Copying to tmp table | 0.004674 |
| Sending data | 0.000020 |
| end | 0.000003 |
| removing tmp table | 0.000015 |
| end | 0.000003 |
| query end | 0.000004 |
| closing tables | 0.000006 |
| freeing items | 0.000006 |
| updating status | 0.000204 |
| cleaning up | 0.000004 |
+----------------------+----------+
여기서 흥미로운 점은 첫 번째 쿼리는 더 많은 행을 스캔하지만 두 번째 쿼리에 비해 더 빨리 실행된다는 것입니다.
언급URL : https://stackoverflow.com/questions/27644089/group-by-where-range-and-const-ref-without-temporary
반응형
'programing' 카테고리의 다른 글
| strict type은 PHP에서 무엇을 합니까? (0) | 2022.09.22 |
|---|---|
| 전용 mariadb 서버에 적합한 구성 찾기 - 거대한 innodb 테이블에 적합 (0) | 2022.09.22 |
| 양방향 암호화:검색할 수 있는 암호를 저장해야 합니다. (0) | 2022.09.22 |
| 특정 table.column을 참조하는 외부 키가 있고 외부 키에 대한 값이 있는 모든 테이블을 찾으려면 어떻게 해야 합니까? (0) | 2022.09.22 |
| file_get_contents를 사용하여 PHP에 데이터를 게시하려면 어떻게 해야 합니까? (0) | 2022.09.22 |