MySQL 아키텍처 연구 및 성능 최적화

요약

MySQL은 David Axmark, Allan Larsson 및 Michael Widenius가 설립한 MySQL AB에서 만든 C 및 C++ 프로그래밍 언어로 개발된 관계 데이터베이스 관리 시스템입니다. 최신 버전은 MySQL 9.0입니다.

MySQL은 GNU 라이센스가 있는 오픈 소스 프로젝트입니다.

MySQL은 오픈 소스이고 모든 기능을 갖추고 고성능을 갖추고 있기 때문에 현재 가장 인기 있고 유용한 데이터베이스 관리 시스템입니다.

건축학

• MySQL 아키텍처 다이어그램: 소스

클라이언트 계층

• MySQL 시스템 아키텍처의 첫 번째 계층
• 이 계층의 중요한 서비스는 연결 처리, 인증 및 보안입니다.
  • 연결 처리: 클라이언트의 연결을 관리합니다. 클라이언트가 MySQL 서버에 연결하려고 하면 연결을 위해 새 스레드가 생성됩니다. 서버는 새 연결이 생성될 때 스레드를 캐시합니다.
  • 인증: 클라이언트가 서버에 연결할 때 연결을 설정하려면 인증 정보를 제공해야 합니다. 일반적으로 클라이언트는 사용자 이름과 비밀번호를 사용하여 인증합니다.
  • 보안: MySQL은 데이터베이스를 보호하고 인증 후 사용자 권한을 확인하는 다양한 기능을 제공합니다.

서버 계층

• MySQL의 다음 계층은 쿼리문 처리와 다양한 유틸리티 제공을 담당합니다.
• 주요 기능:
  • 파서: 클라이언트가 쿼리문을 요청하면 서버는 이를 분석하여 쿼리 트리를 생성하고 쿼리를 다시 작성하며 쿼리문 실행에 사용할 순서와 인덱스를 정의합니다.
  • Optimizer: 쿼리문을 실행하기 전에 최적화합니다. 옵티마이저는 어떤 엔진이 사용되는지 상관하지 않지만 스토리지 엔진은 특정 쿼리에 대한 최적화에 영향을 미칠 수 있습니다.
  • 쿼리 캐시: 쿼리 캐시는 쿼리 문의 결과를 저장합니다. 클라이언트가 동일한 쿼리를 다시 요청하면 서버는 구문 분석 및 최적화 단계를 우회하고 캐시된 결과를 반환합니다. 이 기능은 병목 현상 문제로 인해 버전 8.0 이상에서 더 이상 사용되지 않습니다.
  • 서비스 및 유틸리티:
    • 백업 및 복원
    • 보안: 공급자 사용자 및 권한 시스템
    • 복제 : 메인 서버의 데이터를 여러 하위 서버로 복사하여 동기화하는 과정입니다.
    • 클러스터
    • 파티셔닝: 특정 논리를 사용하여 테이블을 여러 부분으로 분할합니다.
    • Workbench: MySQL과 상호 작용하고 활용하기 위해 설계된 시각적 도구입니다.

스토리지 계층

• 데이터베이스에 데이터를 저장하는 방식에 대한 책임 계층입니다.
• 기본적으로 MySQL은 InnoDB 스토리지 엔진을 사용합니다.
• MySQL은 다양한 스토리지 엔진을 지원합니다.
  • InnoDB(기본값)
  • 마이ISAM
  • 메모리
  • CSV
  • 아카이브
  • 블랙홀
  • 병합
  • 연합

MySQL 성능 최적화

SQL 문 최적화

설명문

• 실행 없이 쿼리 예측

explain sql_statememnt

• 쿼리 실행 후 설명

explain analyze sql_statement

인덱싱 기법

• 조회, 검색 시 성능이 업그레이드됩니다.
• 여러 열에 대한 인덱스를 생성할 때 첫 번째 열이 매우 중요합니다. 쿼리문의 성능을 결정합니다. 예를 들어 (이름, 생일)에 대한 인덱스를 생성하는 경우 '이름' 열을 사용하는 쿼리는 이 인덱스를 활용하지만 '생일' 열을 사용하는 쿼리는 이 인덱스를 활용하지 않습니다.
• 새 인덱스 생성 구문:

create index idx_birthday on customers(birthday);

• 이 명령은 지정된 인덱스를 쿼리 최적화 프로그램이 액세스할 수 없도록 렌더링하여 쿼리 실행 계획에서 해당 인덱스가 고려되지 않도록 합니다.

alter table customers alter index idx_birthday invisible;

분할

• 데이터베이스 크기가 2GB(또는 레코드 1,000만 개 이상)보다 큰 경우 해당 데이터베이스에 대해 파티셔닝을 사용해야 합니다.
• 파티셔닝을 사용하면 성능이 향상됩니다.
• WHERE 절에서 자주 사용되는 컬럼을 기준으로 파티션 계획을 세워야 합니다.

데이터베이스 매개변수 최적화

버퍼 캐시 적중

• 시스템이 물리적 스토리지가 아닌 버퍼 캐시에서 로드하는 데이터의 비율입니다.
• 비율이 90%보다 낮으면 최적화해야 합니다.
• 2가지 매개변수에 따라 다름:
  • Innodb_buffer_pool_read_requests: 버퍼 풀로 전송된 총 요청 수입니다. 명령: '%Innodb_buffer_pool_read_requests%와 같은 전역 상태 표시
  • Innodb_buffer_pool_reads: 메모리의 버퍼 풀에 없는 전체 요청을 디스크에서 읽어야 합니다. 명령: '%Innodb_buffer_pool_reads%와 같은 전역 상태 표시
• 계산 공식:

(Innodb_buffer_pool_read_requestss - Innodb_buffer_pool_reads) x100 / Innodb_buffer_pool_read_requests

테이블 캐시 적중

• MySQL은 테이블을 캐시하여 메모리에서 빠르게 쿼리합니다.
• 비율이 80%보다 낮으면 최적화해야 합니다.
• 2가지 매개변수에 따라 다름:
  • Open_tables: 전체 테이블이 캐시에 열려 있습니다. 명령: 'Open_tables'와 같은 전역 상태를 표시합니다.
  • Opened_tables: 총 테이블이 열려 있습니다. 명령: 'Opened_tables'와 같은 전역 상태를 표시합니다.
• 계산식: Open_tables/Opened_tables

테이블 정의 캐시 적중

• 쿼리를 실행할 때 MySQL은 테이블 이름, 열 수, 행 수 등 테이블에 대한 정보를 요구합니다. 이 정보를 테이블 정의라고 합니다.
• 비율이 80%보다 낮으면 최적화해야 합니다.
• 2가지 매개변수에 따라 다름:
  • Open_table_definitions: 전체 정의 테이블이 캐시에 있습니다. 명령: 'Open_table_definitions'와 같은 전역 상태 표시
  • Opened_tables: 전체 정의 테이블이 요청됩니다. 명령: 'Opened_tables'와 같은 전역 상태를 표시합니다.
• 계산식: Open_table_definitions/Opened_table_definitions

메모리의 임시 테이블

• Order, Group By 등과 같은 많은 SQL 문은 임시 테이블을 사용해야 합니다.
• 비율이 80%보다 낮으면 최적화해야 합니다.
• 2가지 매개변수에 따라 다름:
  • Created_tmp_disk_tables: 전체 임시 테이블이 디스크에 생성됩니다. 명령: '%Created_tmp_disk_tables%와 같은 전역 상태 표시
  • Created_tmp_tables: 전체 임시 테이블이 생성됩니다. 명령: '%Created_tmp_tables%'와 같은 전역 상태를 표시합니다.
• 계산식: (Created_tmp_tables - Created_tmp_disk_tables)/ Created_tmp_tables

결론

이 블로그에서는 MySQL에 대한 연구 기반 지식을 제공합니다. MySQL은 저렴한 비용, 광범위한 유틸리티 및 고성능으로 잘 알려진 관계형 데이터베이스 관리 시스템으로 광범위한 프로젝트에 적합합니다. 이 블로그에서는 MySQL의 많은 기능과 측면을 다루고 있지만 더 살펴볼 내용이 많이 있습니다. 향후 블로그에서 추가 기능과 지식에 대해 더 자세히 살펴보겠습니다.

읽어주셔서 감사합니다. 다음 블로그에서 뵙겠습니다.

참고문서

• MySQL 문서
• MySQL 강좌 - Tran Quoc Huy
• 위키피디아 - MySQL

위 내용은 MySQL 아키텍처 연구 및 성능 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!