복붙노트

MySQL과 NoSQL : 올바른 것을 선택하도록 도와주세요.

PHP

MySQL과 NoSQL : 올바른 것을 선택하도록 도와주세요.

스레드라는 큰 데이터베이스, 1,000,000,000 개의 행이 있습니다. 실제로 이러한 스레드는 실제로 존재하기 때문에 내가 즐기기 때문에 더 어렵게 만들지 않습니다. 스레드는 일을 더 빠르게하기 위해 몇 가지 요소 만 있습니다. (int id, string hash, int replycount, int dateline (timestamp), int forumid, string title)

질문:

select thread * from forumid = 100 및 replycount> dateline desc limit 10000, 100에 의한 1 order

1G의 레코드가 있기 때문에 쿼리가 상당히 느립니다. 그래서 저는 생각했습니다.이 1G의 레코드를 많은 포럼 (카테고리)만큼 분할 해 봅시다. 그것은 거의 완벽합니다. 테이블이 많아서 검색 할 레코드가 적어서 정말 빨라졌습니다. 이제 쿼리는 다음과 같습니다.

select * from thread_ {forum_id} 여기서 replycount는 dateline desc limit 10000, 100에 의한 1 order입니다.

포럼 (카테고리)의 99 %는 주제의 수가 적어 (100k-1M) 매우 빠르기 때문에 실제로 더 빠릅니다. 그러나 약 10M의 레코드가 있기 때문에 어떤 쿼리는 여전히 느려질 수 있습니다 (0.1 / 0.2 초, 내 앱의 경우 많이!, 이미 인덱스를 사용하고 있습니다!).

MySQL을 사용하여이를 향상시키는 방법을 모르겠습니다. 방법이 있습니까?

이 프로젝트에서는 10 대의 서버 (12GB 램, 소프트웨어 RAID 10에 4x7200rpm 하드 디스크, 쿼드 코어)

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

이 10 대의 서버에 cassandra를 설치하면 성능을 향상시켜야한다고 생각합니까?

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

해결법

  1. ==============================

    1.다음을 읽고 잘 디자인 된 innodb 테이블의 장점과 클러스터 된 인덱스를 사용하는 것이 좋은지 조금 배우십시오 - innodb에서만 사용 가능합니다!

    다음을 읽고 잘 디자인 된 innodb 테이블의 장점과 클러스터 된 인덱스를 사용하는 것이 좋은지 조금 배우십시오 - 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 엔진을 사용하고 threads 테이블의 기본 키가 더 이상 하나의 auto_incrementing 키가 아니라 forum_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 기본 키를 사용하는 경우 총 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;

    그러나 응용 프로그램 코드는 사용자가 탐색중인 포럼을 인식해야하므로 구현하기가 어렵지 않습니다. 현재 표시된 forum_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 ;

    당신은 (forum_id, thread_id) composite가 그 자체로 유일하기 때문에 약간 이상한 기본 키의 일부로 reply_count를 포함했음을 눈치 챘을 것입니다. 이는 단지 reply_count를 사용하는 쿼리가 실행될 때 일부 I / O를 저장하는 인덱스 최적화에 불과합니다. 이에 대한 자세한 정보는 위의 2 링크를 참조하십시오.

    나는 여전히 예제 테이블에 데이터를로드하고 있으며, 지금까지로드 된 약이 있습니다. 5 억 행 (시스템의 절반). 로드 프로세스가 완료되면 대략 다음과 같이 예상해야합니다.

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

    나는 의도적으로 일부 포럼에 5 백만 개 이상의 스레드를 포함 시켰습니다. 포럼 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 summing에서 total_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

    나에게 꽤 잘 어울리는 것 같습니다. 즉,로드 중 500 만 행 (및 성장)과 1 천 5 백만 행을 처리하는 단일 테이블입니다.

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

    기타...

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

  2. ==============================

    2.편집 : 귀하의 1 열 인덱스 충분하지 않습니다. 적어도 관련 열 3 개를 처리해야합니다.

    편집 : 귀하의 1 열 인덱스 충분하지 않습니다. 적어도 관련 열 3 개를 처리해야합니다.

    보다 진보 된 해결책 : replycount> 1 일 때 1과 같은 새로운 hasreplies 필드를 생성하여 replycount> 1을 hasreplies = 1로 바꾸십시오.이 작업이 완료되면 세 열에 INDEX (forumid, hasreplies, dateline ). 순서 지정을 지원하는 BTREE 색인인지 확인하십시오.

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

    이렇게하면 쿼리가 실행됩니다.

    범위 쿼리 였기 때문에 결과를 정렬하는 데 dateline을 사용할 수 없었기 때문에 replycount에 대한 색인 생성에 대한 나의 이전 제안은 정확하지 않았습니다 (따라서 응답이 매우 빠른 스레드를 선택했을 것입니다 만 결과 만 줄리스트 당신이 필요로하는 100 가지 요소를 찾기 전에 완전히 정렬되어야했을 것입니다.)

    IMPORTANT : 모든 경우에 성능이 향상되지만 엄청난 OFFSET 값 (10000!)은 MySQL이 BTREE를 통해 곧바로 읽음에도 불구하고 건너 뛸 수있는 것처럼 보이지 않기 때문에 성능이 저하 될 것입니다. 따라서 OFFSET이 클수록 요청 속도가 느려집니다.

    오프셋 (offset) 문제는 자동으로 여러 계산 (예 : 병렬로 오프셋을 건너 뛰는 방법)을 분산 시키거나 NoSQL으로 이동하여 자동으로 해결되지 않습니다. 모든 솔루션 (NoSQL을 포함하여)은 dateline (기본적으로 dateline> LIMIT Z 대신 LIMIT 100, 100은 Y가 오프셋 Z에있는 항목의 날짜 임)을 기반으로 OFFSET 시뮬레이션에 이릅니다. 이렇게하면 오프셋과 관련된 성능 문제는 해결되지만 200 페이지 중 100 페이지로 바로 이동하는 것을 방지 할 수 있습니다.

  3. ==============================

    3.NoSQL 또는 MySQL 옵션과 관련된 질문이 있습니다. 실제로 이것은 여기에 숨겨진 하나의 근본적인 것입니다. SQL 언어는 사람이 쓰기 쉽고 컴퓨터에서 읽기가 어렵습니다. 대용량 데이터베이스에서는 SQL 백엔드를 피하기 위해 별도의 단계 명령 구문 분석이 필요하므로이 방법을 사용하는 것이 좋습니다. 광범위한 벤치마킹을 수행했으며 SQL 구문 분석기가 가장 느린 경우가 있습니다. 그것에 대해 할 수있는 일이 없습니다. 좋습니다. 미리 구문 분석 된 문을 사용하고 액세스 할 수 있습니다.

    NoSQL 또는 MySQL 옵션과 관련된 질문이 있습니다. 실제로 이것은 여기에 숨겨진 하나의 근본적인 것입니다. SQL 언어는 사람이 쓰기 쉽고 컴퓨터에서 읽기가 어렵습니다. 대용량 데이터베이스에서는 SQL 백엔드를 피하기 위해 별도의 단계 명령 구문 분석이 필요하므로이 방법을 사용하는 것이 좋습니다. 광범위한 벤치마킹을 수행했으며 SQL 구문 분석기가 가장 느린 경우가 있습니다. 그것에 대해 할 수있는 일이 없습니다. 좋습니다. 미리 구문 분석 된 문을 사용하고 액세스 할 수 있습니다.

    BTW, 그것은 널리 알려져 있지 않지만 MySQL은 NoSQL 데이터베이스에서 성장했습니다. MySQL David와 Monty의 저자는 데이터웨어 하우징 회사였으며 흔히 흔히 사용하지 않는 작업을위한 맞춤형 솔루션을 작성해야했습니다. 이것은 오라클 및 기타가 제대로 수행되지 않을 때 데이터베이스 기능을 수동으로 작성하는 데 사용되는 자작 C 라이브러리의 큰 스택으로 이어졌습니다. 재미로 1996 년에이 거의 20 세의 동물원에 SQL이 추가되었습니다. 당신이 알고 난 후에 무엇이 올 것인가.

    사실 MySQL을 사용하면 SQL 오버 헤드를 피할 수 있습니다. 그러나 일반적으로 SQL 구문 분석은 가장 느린 부분이 아니라 알고있는 것이 좋습니다. 구문 분석기 오버 헤드를 테스트하려면 예를 들어 "SELECT 1"에 대한 벤치 마크를 작성하면됩니다.

  4. ==============================

    4.구매하려는 하드웨어에 데이터베이스 아키텍처를 맞추려고하지 말고 대신 데이터베이스 아키텍처에 맞는 하드웨어를 구입할 계획을 세우십시오.

    구매하려는 하드웨어에 데이터베이스 아키텍처를 맞추려고하지 말고 대신 데이터베이스 아키텍처에 맞는 하드웨어를 구입할 계획을 세우십시오.

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

    따라서 12GB가 충분하지 않으면 12GB RAM이 장착 된 10 대의 서버를 사용하지 말고 32GB 또는 64GB RAM을 더 적게 사용하십시오.

  5. ==============================

    5.색인은 필수 항목이지만 올바른 유형의 색인을 선택하는 것이 좋습니다. BTREE는 WHERE 절에 "<"또는 ">"가 포함 된 검색어를 사용하는 것이 더 적합하지만 HASH는 한 열에 여러 고유 값이있는 경우에 더 적합합니다. WHERE 절에서 "="또는 "<=>"을 사용하고 있습니다.

    색인은 필수 항목이지만 올바른 유형의 색인을 선택하는 것이 좋습니다. BTREE는 WHERE 절에 "<"또는 ">"가 포함 된 검색어를 사용하는 것이 더 적합하지만 HASH는 한 열에 여러 고유 값이있는 경우에 더 적합합니다. WHERE 절에서 "="또는 "<=>"을 사용하고 있습니다.

    추가 정보 http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

    6. from https://stackoverflow.com/questions/4419499/mysql-and-nosql-help-me-to-choose-the-right-one by cc-by-sa and MIT license

'PHP' 카테고리의 다른 글

PHP에 대한 각도 HTTP 게시 및 정의되지 않음  (0) 2018.09.06
파일을 다운로드하지 않고 원격 파일 크기  (0) 2018.09.06
PHP는 어디에서 에러 로그를 저장합니까? (php5, apache, fastcgi, cpanel)  (0) 2018.09.06
PHP 연산자 "?"와 ":"는 무엇이며 무엇을합니까?  (0) 2018.09.06
PHP에서 데이터베이스에 문자열을 제출할 때 htmlspecialchars ()를 사용하여 불법 문자를 처리하거나 정규 표현식을 사용해야합니까?  (0) 2018.09.06
,

티스토리툴바