60000000 개 항목, 특정 월에서 항목을 선택합니다. 어떻게 데이터베이스를 최적화?

나는 6000 만 개 항목이 데이터베이스를 가지고있다.

모든 항목이 포함되어 있습니다 :

약 50 ~ 100 다른 DataSourceIDs이 있습니다.

빨리이 할 수있는 방법이 있나요? 내 옵션은 무엇입니까? 어떻게이 데이터베이스 / 쿼리를 최적화?

편집 : 약이있다. 초당 60-100 삽입!

해결법

==============================

1.이노는 기본 키 인덱스를 클러스터를 활용.

이노는 기본 키 인덱스를 클러스터를 활용.

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

이것은 매우 성능이 좋은됩니다

create table datasources
(
year_id smallint unsigned not null,
month_id tinyint unsigned not null,
datasource_id tinyint unsigned not null,
id int unsigned not null, -- needed for uniqueness
data int unsigned not null default 0,
primary key (year_id, month_id, datasource_id, id)
)
engine=innodb;

select * from datasources where year_id = 2011 and month_id between 1 and 3;

select * from datasources where year_id = 2011 and month_id = 4 and datasouce_id = 100;

-- etc..

EDIT 2

내가 데이터를 3 개월로 첫 번째 테스트 스크립트를 실행중인 잊으. 여기에 한 달에 대한 결과이다 : 0.34 및 0.69 초.

select d.* from datasources d where d.year_id = 2010 and d.month_id = 3 and datasource_id = 100 order by d.id desc limit 10;
+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id      | data  |
+---------+----------+---------------+---------+-------+
|    2010 |        3 |           100 | 3290330 | 38434 |
|    2010 |        3 |           100 | 3290329 |  9988 |
|    2010 |        3 |           100 | 3290328 | 25680 |
|    2010 |        3 |           100 | 3290327 | 17627 |
|    2010 |        3 |           100 | 3290326 | 64508 |
|    2010 |        3 |           100 | 3290325 | 14257 |
|    2010 |        3 |           100 | 3290324 | 45950 |
|    2010 |        3 |           100 | 3290323 | 49986 |
|    2010 |        3 |           100 | 3290322 |  2459 |
|    2010 |        3 |           100 | 3290321 | 52971 |
+---------+----------+---------------+---------+-------+
10 rows in set (0.34 sec)

select d.* from datasources d where d.year_id = 2010 and d.month_id = 3 order by d.id desc limit 10;
+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id      | data  |
+---------+----------+---------------+---------+-------+
|    2010 |        3 |           116 | 3450346 | 42455 |
|    2010 |        3 |           116 | 3450345 | 64039 |
|    2010 |        3 |           116 | 3450344 | 27046 |
|    2010 |        3 |           116 | 3450343 | 23730 |
|    2010 |        3 |           116 | 3450342 | 52380 |
|    2010 |        3 |           116 | 3450341 | 35700 |
|    2010 |        3 |           116 | 3450340 | 20195 |
|    2010 |        3 |           116 | 3450339 | 21758 |
|    2010 |        3 |           116 | 3450338 | 51378 |
|    2010 |        3 |           116 | 3450337 | 34687 |
+---------+----------+---------------+---------+-------+
10 rows in set (0.69 sec)

EDIT 1

약으로 위의 스키마를 테스트하기로 결정했다. 6000 만 개 행을 3 년에 걸쳐. 각 쿼리는 별도로 차가운 즉, 각 실행을 실행되는 MySQL의 후 어떤 버퍼를 삭제없이 쿼리 캐싱으로 다시 시작됩니다.

전체 테스트 스크립트는 여기에서 찾을 수 있습니다 : http://pastie.org/1723506 이하 ...

당신이 볼 수 있듯이 심지어 내 겸손 바탕 화면에 꽤 성능이 좋은 스키마입니다 :)

select count(*) from datasources;
+----------+
| count(*) |
+----------+
| 60306030 |
+----------+

select count(*) from datasources where year_id = 2010;
+----------+
| count(*) |
+----------+
| 16691669 |
+----------+

select
 year_id, month_id, count(*) as counter
from
 datasources
where 
 year_id = 2010
group by
 year_id, month_id;
+---------+----------+---------+
| year_id | month_id | counter |
+---------+----------+---------+
|    2010 |        1 | 1080108 |
|    2010 |        2 | 1210121 |
|    2010 |        3 | 1160116 |
|    2010 |        4 | 1300130 |
|    2010 |        5 | 1860186 |
|    2010 |        6 | 1220122 |
|    2010 |        7 | 1250125 |
|    2010 |        8 | 1460146 |
|    2010 |        9 | 1730173 |
|    2010 |       10 | 1490149 |
|    2010 |       11 | 1570157 |
|    2010 |       12 | 1360136 |
+---------+----------+---------+
12 rows in set (5.92 sec)


select 
 count(*) as counter
from 
 datasources d
where 
 d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100;

+---------+
| counter |
+---------+
|   30003 |
+---------+
1 row in set (1.04 sec)

explain
select 
 d.* 
from 
 datasources d
where 
 d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100
order by
 d.id desc limit 10;

+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  |rows    | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | d     | range | PRIMARY       | PRIMARY | 4       | NULL |4451372 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)


select 
 d.* 
from 
 datasources d
where 
 d.year_id = 2010 and d.month_id between 1 and 3 and datasource_id = 100
order by
 d.id desc limit 10;

+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id      | data  |
+---------+----------+---------------+---------+-------+
|    2010 |        3 |           100 | 3290330 | 38434 |
|    2010 |        3 |           100 | 3290329 |  9988 |
|    2010 |        3 |           100 | 3290328 | 25680 |
|    2010 |        3 |           100 | 3290327 | 17627 |
|    2010 |        3 |           100 | 3290326 | 64508 |
|    2010 |        3 |           100 | 3290325 | 14257 |
|    2010 |        3 |           100 | 3290324 | 45950 |
|    2010 |        3 |           100 | 3290323 | 49986 |
|    2010 |        3 |           100 | 3290322 |  2459 |
|    2010 |        3 |           100 | 3290321 | 52971 |
+---------+----------+---------------+---------+-------+
10 rows in set (0.98 sec)


select 
 count(*) as counter
from 
 datasources d
where 
 d.year_id = 2010 and d.month_id between 1 and 3;

+---------+
| counter |
+---------+
| 3450345 |
+---------+
1 row in set (1.64 sec)

explain
select 
 d.* 
from 
 datasources d
where 
 d.year_id = 2010 and d.month_id between 1 and 3
order by
 d.id desc limit 10;

+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  |rows    | Extra                       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | d     | range | PRIMARY       | PRIMARY | 3       | NULL |6566916 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-----------------------------+
1 row in set (0.00 sec)


select 
 d.* 
from 
 datasources d
where 
 d.year_id = 2010 and d.month_id between 1 and 3
order by
 d.id desc limit 10;

+---------+----------+---------------+---------+-------+
| year_id | month_id | datasource_id | id      | data  |
+---------+----------+---------------+---------+-------+
|    2010 |        3 |           116 | 3450346 | 42455 |
|    2010 |        3 |           116 | 3450345 | 64039 |
|    2010 |        3 |           116 | 3450344 | 27046 |
|    2010 |        3 |           116 | 3450343 | 23730 |
|    2010 |        3 |           116 | 3450342 | 52380 |
|    2010 |        3 |           116 | 3450341 | 35700 |
|    2010 |        3 |           116 | 3450340 | 20195 |
|    2010 |        3 |           116 | 3450339 | 21758 |
|    2010 |        3 |           116 | 3450338 | 51378 |
|    2010 |        3 |           116 | 3450337 | 34687 |
+---------+----------+---------------+---------+-------+
10 rows in set (1.98 sec)

도움이 되었기를 바랍니다 :)

==============================
2.특정 월의 항목을 얻으려면, 특정 연도에 대한 빠른 - 당신은 인덱스 시간 열이 필요합니다 :

특정 월의 항목을 얻으려면, 특정 연도에 대한 빠른 - 당신은 인덱스 시간 열이 필요합니다 :
```
CREATE INDEX idx_time ON ENTRIES(time) USING BTREE;
```
또한, 사용 :
```
SELECT e.* 
  FROM ENTRIES e
 WHERE e.time BETWEEN '2010-04-01' AND DATE_SUB('2010-05-01' INTERVAL 1 SECOND)
```
... BETWEEN 포함하기 때문에, 그래서 당신은 당신이 게시 된 쿼리 "2010-05-01 0시 0분 0초"일자 아무것도 얻을 것입니다.

당신은 어느 datasourceid 열에 대한 별도의 인덱스를 추가 할 수 있습니다 :
```
CREATE INDEX idx_time ON ENTRIES(datasourceid) USING BTREE;
```
... 또는 설치 a를 모두 열을 포함하는 인덱스를 커버 :
```
CREATE INDEX idx_time ON ENTRIES(time, datasourceid) USING BTREE;
```
커버링 인덱스는 왼쪽 열 인덱스를 사용하는 쿼리에서 사용할 수 있어야합니다. 이 예제에서, 당신이 언급 한 두 경우 모두를 위해 작동 처음 시간을 보내고 - datasourceid은 사용되기를 인덱스에 사용할 수 없습니다. 그러나, 당신은 정말 당신의 데이터에 가장 적합한 및 쿼리가 데이터에 수행되는 것을 알 수있는 출력을 EXPLAIN 보면 쿼리를 테스트 할 수 있습니다.

즉 인덱스는 INSERT, UPDATE 및 DELETE 문을 느려지 며 말했다. 중요도가 낮기 때문에, 부울 열이 인덱스에 나쁜 선택이다 : IE - 컬럼 데이터가 몇 가지 고유 한 값을 가지고있는 경우 그리고 인덱스는 많은 가치를 제공하지 않습니다.
==============================
3.당신은 쿼리 속도 디스크 사용을 거래하는 인덱스를 사용할 수 있습니다. 특정 월을 요청 쿼리 속도를 높일 수 시간 열을 시작 인덱스 :

당신은 쿼리 속도 디스크 사용을 거래하는 인덱스를 사용할 수 있습니다. 특정 월을 요청 쿼리 속도를 높일 수 시간 열을 시작 인덱스 :
```
create index IX_YourTable_Date on YourTable (time, DataSourceID, ID, SomeData)
```
인덱스 시간 필드로 시작하기 때문에, MySQL은 인덱스의 키 범위 스캔을 할 수 있습니다. 즉 빨리이수록되어야한다. 인덱스는 쿼리의 모든 열을 포함해야한다, 또는 MySQL은 각 행의 테이블 데이터에 대한 인덱스에서 볼 필요가있다. 당신이 2 개 백만 행을 요구하고 있기 때문에, MySQL은 가능성이 커버되지 않은 인덱스를 무시합니다. (쿼리의 모든 행을 포함하는 인덱스 = 커버링 인덱스).

당신이 결코 쿼리 ID에 사용할 테이블을 재정의 할 수있는 경우 (시간, DataSourceID, ID) 기본 키 :
```
alter table YourTable add primary key (time, DataSourceID, ID)
```
이 디스크 공간에서 무료로 시간에 검색 속도를하지만, ID에 대한 검색 속도가 매우 느려질 수 있습니다.
==============================
4.난 당신이 아직없는 시간 필드에있는 경우 인덱스를 넣어 시도 할 것입니다.

난 당신이 아직없는 시간 필드에있는 경우 인덱스를 넣어 시도 할 것입니다.

DataSourceID를 들어, 대신 VARCHAR / INT의 열거를 사용하여 시도 할 수 있습니다.

from https://stackoverflow.com/questions/5451190/60-million-entries-select-entries-from-a-certain-month-how-to-optimize-databas by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 MySQL의 데이터를 생성하는? (0)	2020.04.16
[SQL] "운영자가 존재하지 않습니다 : 정수 =?" 포스트 그레스를 사용하는 경우 (0)	2020.04.16
[SQL] 오라클에서 SQL 쿼리에서 연결할 결과 (0)	2020.04.16
[SQL] phpPgAdmin에서 PostgreSQL을 삽입 쿼리 구문 오류 (0)	2020.04.16
[SQL] SQL Server의 문자열 CONCAT 필드 값 (0)	2020.04.16

복붙노트

[SQL] 60000000 개 항목, 특정 월에서 항목을 선택합니다. 어떻게 데이터베이스를 최적화?

60000000 개 항목, 특정 월에서 항목을 선택합니다. 어떻게 데이터베이스를 최적화?

해결법

1.이노는 기본 키 인덱스를 클러스터를 활용.

2.특정 월의 항목을 얻으려면, 특정 연도에 대한 빠른 - 당신은 인덱스 시간 열이 필요합니다 :

3.당신은 쿼리 속도 디스크 사용을 거래하는 인덱스를 사용할 수 있습니다. 특정 월을 요청 쿼리 속도를 높일 수 시간 열을 시작 인덱스 :

4.난 당신이 아직없는 시간 필드에있는 경우 인덱스를 넣어 시도 할 것입니다.

'SQL' 카테고리의 다른 글

티스토리툴바