MySQL 및 NoSQL: 올바른 것을 선택할 수 있도록 도와줍니다.

MySQL 및 NoSQL: 올바른 것을 선택할 수 있도록 도와줍니다.

스레드(Threads)라는 큰 데이터베이스가 있습니다. 1,000,000,000개의 행(실제로 이러한 스레드가 존재합니다. 단지 즐긴다고 일을 더 어렵게 만드는 것은 아닙니다.스레드에는 몇 가지 정보만 포함되어 있으므로 작업 속도를 높일 수 있습니다. (intid, 문자열 해시, int reply count, int dateline(타임스탬프), int formid, 문자열 제목)

쿼리:

select * from thread where forumid = 100 and replycount > 1 order by dateline desc limit 10000, 100

1G의 기록이 있기 때문에 상당히 느린 질의입니다.그래서 저는 이 1G 레코드를 제가 가진 포럼(카테고리)의 수만큼 테이블로 나누어보자고 생각했습니다.그것은 거의 완벽합니다.테이블이 많아서 검색 기록도 적고 속도도 정말 빠릅니다.이제 쿼리는 다음과 같습니다.

select * from thread_{forum_id} where replycount > 1 order by dateline desc limit 10000, 100

대부분의 포럼이 몇 개의 주제(100k-1M)만을 가지고 있기 때문에 포럼(카테고리)의 99%에서는 이 속도가 매우 빠릅니다.그러나 약 1,000만 개의 레코드를 가진 일부가 있기 때문에 일부 쿼리는 여전히 느립니다(0.1/.2초, 내 앱에 너무 많은 양!, 이미 인덱스를 사용하고 있습니다!).

MySQL을 이용해서 어떻게 개선해야 할지 모르겠습니다.방법이 있습니까?

이 프로젝트를 위해 서버 10대(12GB 램, 4x7200rpm 하드디스크(소프트웨어 raid 10, 쿼드코어)를 사용할 예정입니다.

이 아이디어는 단순히 서버 간에 데이터베이스를 분할하는 것이었지만 위에서 설명한 문제로는 아직 충분하지 않습니다.

이 10개의 서버에 카산드라를 설치하면(원래대로 작동할 시간이 생긴다고 가정하면) 성능이 향상되어야 합니까?

어떻게 해야 하나?여러 시스템에서 MySQL과 분산 데이터베이스를 계속 사용하거나 카산드라 클러스터를 구축하시겠습니까?

인덱스가 무엇인지 게시해 달라는 요청을 받았습니다. 여기 있습니다.

mysql> show index in thread;
PRIMARY id
forumid
dateline
replycount

설명 선택:

mysql> explain SELECT * FROM thread WHERE forumid = 655 AND visible = 1 AND open <> 10 ORDER BY dateline ASC LIMIT 268000, 250;
+----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref         | rows   | Extra                       |
+----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+
|  1 | SIMPLE      | thread | ref  | forumid       | forumid | 4       | const,const | 221575 | Using where; Using filesort | 
+----+-------------+--------+------+---------------+---------+---------+-------------+--------+-----------------------------+

다음을 읽고 잘 설계된 nodb 테이블의 장점과 클러스터 인덱스를 가장 잘 사용하는 방법에 대해 조금 알아야 합니다. innodb에서만 사용할 수 있습니다!

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

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

그런 다음 다음 단순화된 예제의 행을 따라 시스템을 설계합니다.

예제 스키마(간소화)

중요한 기능은 테이블이 innodb 엔진을 사용하고 스레드 테이블의 기본 키가 더 이상 단일 auto_incrementing 키가 아니라 formula_id와 thread_id의 조합을 기반으로 하는 복합 클러스터 키라는 것입니다.

threads - primary key (forum_id, thread_id)

forum_id    thread_id
========    =========
1                   1
1                   2
1                   3
1                 ...
1             2058300  
2                   1
2                   2
2                   3
2                  ...
2              2352141
...

각 포럼 행에는 next_thread_id(unsigned int)라는 카운터가 포함되어 있으며, 이 카운터는 트리거에 의해 유지되며 스레드가 지정된 포럼에 추가될 때마다 증분됩니다.또한 thread_id에 대해 단일 auto_increment primary 키를 사용할 경우 포럼당 총 40억 개의 스레드를 저장하는 것이 아니라 40억 개의 스레드를 저장할 수 있습니다.

forum_id    title   next_thread_id
========    =====   ==============
1          forum 1        2058300
2          forum 2        2352141
3          forum 3        2482805
4          forum 4        3740957
...
64        forum 64       3243097
65        forum 65      15000000 -- ooh a big one
66        forum 66       5038900
67        forum 67       4449764
...
247      forum 247            0 -- still loading data for half the forums !
248      forum 248            0
249      forum 249            0
250      forum 250            0

복합 키 사용의 단점은 다음과 같이 더 이상 단일 키 값으로 스레드를 선택할 수 없다는 것입니다.

select * from threads where thread_id = y;

다음을 수행해야 합니다.

select * from threads where forum_id = x and thread_id = y;

그러나 응용 프로그램 코드는 사용자가 어떤 포럼을 검색하고 있는지 알고 있어야 하므로 구현이 정확히 어렵지 않습니다 - 현재 보는 form_id를 세션 변수 또는 숨겨진 폼 필드에 저장하는 등.

단순화된 스키마는 다음과 같습니다.

drop table if exists forums;
create table forums
(
forum_id smallint unsigned not null auto_increment primary key,
title varchar(255) unique not null,
next_thread_id int unsigned not null default 0 -- count of threads in each forum
)engine=innodb;


drop table if exists threads;
create table threads
(
forum_id smallint unsigned not null,
thread_id int unsigned not null default 0,
reply_count int unsigned not null default 0,
hash char(32) not null,
created_date datetime not null,
primary key (forum_id, thread_id, reply_count) -- composite clustered index
)engine=innodb;

delimiter #

create trigger threads_before_ins_trig before insert on threads
for each row
begin
declare v_id int unsigned default 0;

  select next_thread_id + 1 into v_id from forums where forum_id = new.forum_id;
  set new.thread_id = v_id;
  update forums set next_thread_id = v_id where forum_id = new.forum_id;
end#

delimiter ;

(form_id, thread_id) composite 자체가 고유하기 때문에 제가 reply_count를 기본 키의 일부로 포함시킨 것을 알아차렸을 수도 있습니다.이는 reply_count를 사용하는 쿼리를 실행할 때 일부 I/O를 절약하는 인덱스 최적화입니다.이에 대한 자세한 내용은 위의 2개 링크를 참조하시기 바랍니다.

쿼리 예제

저는 여전히 제 예제 테이블에 데이터를 로드하고 있으며 지금까지 약 5억 행(당신의 시스템의 절반)을 로드했습니다.로드 프로세스가 완료되면 대략 다음과 같은 결과가 발생할 것으로 예상됩니다.

250 forums * 5 million threads = 1250 000 000 (1.2 billion rows)

저는 의도적으로 일부 포럼에 500만 개 이상의 스레드가 포함되도록 했습니다. 예를 들어, 포럼 65에는 1,500만 개의 스레드가 있습니다.

forum_id    title   next_thread_id
========    =====   ==============
65        forum 65      15000000 -- ooh a big one

조회 실행 시간

select sum(next_thread_id) from forums;

sum(next_thread_id)
===================
539,155,433 (500 million threads so far and still growing...)

innodb에서 next_ thread_ids를 합산하여 총 스레드 수를 지정하면 일반적인 것보다 훨씬 빠릅니다.

select count(*) from threads;

포럼 65의 스레드 수:

select next_thread_id from forums where forum_id = 65

next_thread_id
==============
15,000,000 (15 million)

다시 말하지만, 이것은 평소보다 더 빠릅니다.

select count(*) from threads where forum_id = 65

지금까지 약 5억 개의 스레드가 있으며 포럼 65의 스레드 수는 1,500만 개입니다. 스키마가 어떻게 작동하는지 살펴보겠습니다.

select forum_id, thread_id from threads where forum_id = 65 and reply_count > 64 order by thread_id desc limit 32;

runtime = 0.022 secs

select forum_id, thread_id from threads where forum_id = 65 and reply_count > 1 order by thread_id desc limit 10000, 100;

runtime = 0.027 secs

제가 보기에는 꽤 성능이 좋아 보입니다. 5억 개 이상의 행(그리고 증가하는)이 있는 단일 테이블로, 1,500만 개의 행을 0.02초 만에 포함하는 쿼리가 있습니다(부하 상태에서!).

추가 최적화

여기에는 다음이 포함됩니다.

• 범위별 파티셔닝

• 부스러기 같은

• 돈과 하드웨어를 쏟아붓기

등..

이 답변이 도움이 되길 바랍니다 :)

편집: 한 열 인덱스가 충분하지 않습니다.최소한 세 개의 관련된 열을 다루어야 합니다.

고급 솔루션: 교체replycount > 1와 함께hasreplies = 1새것을 만듦으로써hasreplies필드는 다음과 같습니다.replycount > 1. 이 작업이 완료되면 세 열에 다음 순서로 색인을 작성합니다.INDEX(forumid, hasreplies, dateline)확인합니다 주문을 지원하기 위한 BTREE 지수인지 확인합니다.

다음을 기준으로 선택합니다.

• 기정사실인forumid
• 기정사실인hasreplies
• 명을 받은dateline

이렇게 하면 쿼리 실행에 필요한 작업은 다음과 같습니다.

• BTREE 아래로 이동하여 일치하는 하위 트리 찾기forumid = X :수)) . ( : log()) 입니다.
• BTREE 아래로 더 이동하여 일치하는 하위 트리 찾기hasreplies = 1(계속 일치하는 동안)forumid = X). 이것은 상수 시간 연산입니다. 왜냐하면hasreplies0 또는 1 밖에 되지 않습니다.
• 포럼의 전체 항목 목록을 읽고 다시 sort할 필요 없이 필요한 결과를 얻기 위해 dateline-sorted 하위 트리를 이동합니다.

이전에 제안했던 것은 다음과 같습니다.replycount틀렸습니다, 왜냐하면 그것은 범위 질의였고 따라서 a의 사용을 막았기 때문입니다.dateline결과를 정렬하려면(따라서 응답이 있는 스레드를 매우 빠르게 선택했을 것이지만, 결과적으로 백만 줄짜리 목록은 필요한 100개의 요소를 찾기 전에 완전히 정렬되어야 했을 것입니다).

중요: 이렇게 하면 모든 경우에 성능이 향상되지만 MySQL이 BTRE를 통해 바로 읽음에도 불구하고 앞으로 건너뛸 수 없는 것처럼 보이기 때문에 큰 OFFSET 값(10000!)은 성능이 저하됩니다. 따라서 OFFSET이 클수록 요청 속도가 느려집니다.

여러 계산에 걸쳐 계산을 퍼트리면 OFFSET 문제가 자동으로 해결되지 않는 것 같습니다(어쨌든 오프셋을 병렬로 건너뛰는 방법은 무엇입니까?).또는 NoSQL로 이동합니다.NoSQL을 포함한 모든 솔루션은 다음을 기반으로 OFFSET 시뮬레이션으로 압축됩니다.dateline(basically들의 말은)dateline > Y LIMIT 100대신에LIMIT Z, 100어디에Y오프셋에서 품목의 날짜입니다.Z오프셋과 관련된 성능 문제를 제거하지만 200페이지 중 100페이지로 바로 이동할 수는 없습니다.

NoSQL 또는 MySQL 옵션과 관련된 의문의 부분이 있습니다.사실 이것은 여기에 숨겨진 근본적인 것입니다.SQL 언어는 사람이 쓰기 쉽고 컴퓨터는 읽기가 조금 어렵습니다.대용량 데이터베이스에서는 SQL 백엔드를 사용하지 않는 것이 좋습니다. 이것은 추가적인 단계 명령 구문 분석이 필요하기 때문입니다.저는 광범위한 벤치마킹을 해보았는데 SQL 파서가 가장 느린 경우가 있습니다.당신이 그것에 대해 할 수 있는 일이 없습니다.네, 미리 파싱된 문을 사용하여 액세스할 수 있습니다.

그건 그렇고, 널리 알려지지는 않았지만 MySQL은 NoSQL 데이터베이스에서 성장했습니다.MySQL David와 Monty의 저자들이 일했던 회사는 데이터 웨어하우징 회사였으며, 종종 일반적이지 않은 작업에 대한 맞춤형 솔루션을 작성해야 했습니다.이로 인해 Oracle 등의 성능이 좋지 않을 때 데이터베이스 기능을 수동으로 작성하는 데 사용되는 홈브루 C 라이브러리가 크게 쌓이게 되었습니다.SQL은 거의 20년 된 이 동물원에 1996년 재미로 추가되었습니다.알고 난 뒤에 뭐가 왔을까요?

실제로 MySQL을 사용하면 SQL 오버헤드를 피할 수 있습니다.하지만 보통 SQL 파싱은 가장 느린 부분은 아니지만 알아두면 좋습니다.파서 오버헤드를 테스트하려면 "SELECT 1"(예: ;)을 벤치마크하면 됩니다.

구입하려는 하드웨어에 데이터베이스 아키텍처를 맞추려고 하는 것이 아니라 데이터베이스 아키텍처에 맞게 하드웨어를 구입하려고 계획해야 합니다.

메모리에 작동하는 인덱스 집합을 유지할 수 있는 충분한 RAM이 있으면 인덱스를 사용할 수 있는 모든 쿼리가 빨라집니다.키 버퍼가 인덱스를 저장할 수 있을 정도로 충분히 크게 설정되었는지 확인합니다.

따라서 12GB로 충분하지 않은 경우 12GB RAM이 있는 서버를 10대 사용하지 말고 32GB 또는 64GB RAM이 있는 서버를 적게 사용하십시오.

인덱스는 필수이지만 올바른 유형의 인덱스를 선택해야 합니다. BTREE는 WHERE 절에서 "<" 또는 ">"로 쿼리를 사용할 때 더 적합한 반면 해시는 한 열에 여러 개의 다른 값이 있고 WHERE 절에서 "=" 또는 "<=>"를 사용할 때 더 적합합니다.

더보기 http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

언급URL : https://stackoverflow.com/questions/4419499/mysql-and-nosql-help-me-to-choose-the-right-one