MySql 최적화 아키텍처 및 스토리지 엔진(요약 공유)

이 기사는 MySQL 아키텍처 및 스토리지 엔진 요약에 대한 관련 지식을 제공합니다.

1. MySQL 구조 시스템

일반적으로 MySQL을 3개의 레이어로 나눌 수 있는데, 계층을 좀 더 세분화하면 스토리지 엔진 레이어를 엔진 레이어와 저장 계층:

연결 계층
최상위 계층은 로컬 양말 통신과 클라이언트/서버 도구를 기반으로 하는 대부분의 TCP/IP 유사 통신을 포함한 일부 클라이언트 및 링크 서비스입니다. 주로 일부 연결 처리, 권한 인증 및 관련 보안 솔루션을 완료합니다. 이 계층에는 인증을 통해 안전하게 접근하는 클라이언트에게 스레드를 제공하기 위해 스레드 풀(Thread Pool)이라는 개념이 도입되었습니다. SSL 기반 보안 링크도 이 계층에서 구현될 수 있습니다. 또한 서버는 안전하게 액세스하는 각 클라이언트에 대해 가지고 있는 운영 권한을 확인합니다.

서비스 레이어
두 번째 레이어 아키텍처는 주로 SQL 인터페이스와 같은 대부분의 핵심 서비스 기능을 완성하고 캐시된 쿼리, SQL 분석 및 최적화, 일부 내장 기능의 실행을 완료합니다. 프로시저, 함수 등과 같은 모든 교차 스토리지 엔진 기능도 이 계층에서 구현됩니다. 이 계층에서 서버는 쿼리를 구문 분석하고 해당 내부 구문 분석 트리를 생성하며 테이블 쿼리의 순서, 인덱스 사용 여부 등을 결정하는 등 해당 최적화를 완료하고 최종적으로 해당 실행 작업을 생성합니다. select 문인 경우 서버는 내부 캐시도 쿼리합니다. 캐시 공간이 충분히 크면 많은 수의 읽기 작업을 해결하는 환경에서 시스템 성능이 크게 향상될 수 있습니다.

엔진 레이어
스토리지 엔진 레이어는 실제로 MySQL에서 데이터 저장 및 검색을 담당합니다. 서버는 API를 통해 스토리지 엔진과 통신합니다. 다양한 스토리지 엔진에는 다양한 기능이 있으므로 필요에 따라 적절한 스토리지 엔진을 선택할 수 있습니다.

스토리지 레이어
데이터 스토리지 레이어는 주로 파일 시스템에 데이터를 저장하고 스토리지 엔진과의 상호작용을 완성합니다. 다른 데이터베이스와 비교할 때 MySQL은 아키텍처가 약간 다르며 다양한 시나리오에서 잘 작동합니다. 주로 스토리지 엔진에 반영되는 플러그인 스토리지 엔진 아키텍처는 쿼리 처리를 다른 시스템 작업과 데이터 저장 및 추출과 분리합니다. 이 아키텍처를 사용하면 비즈니스 요구 사항과 실제 요구 사항에 따라 적절한 스토리지 엔진을 선택할 수 있습니다.

2. MySQL 서버 구성 요소

MySQL 구조의 각 레이어는 하나 또는 여러 부분으로 구성됩니다.

연결 레이어 구성 요소

커넥터: 대화형 인터페이스

MySQL 외부 제공 java, .net, php 및 기타 언어와 같은 대화형 구성 요소는 이 구성 요소를 사용하여 SQL 문을 작동하고 SQL

서비스 계층 구성 요소

연결 풀: 연결 풀 구성 요소
용도 관리, 사용자 연결 버퍼링, 스레드 처리 및 캐싱이 필요한 기타 요구 사항

관리 서비스 및 유틸리티: 관리 서비스 및 도구 구성 요소
백업 및 복구, Mysql 복제, 클러스터링, 보안과 같은 시스템 관리 및 제어 도구에 사용됩니다.

SQL 인터페이스: SQL 인터페이스 구성요소
DML, DDL, 저장 프로시저 등 사용자의 SQL 명령을 받아들이고 최종 결과를 사용자에게 반환하는 데 사용됩니다.

Parser: 쿼리 분석기 component
SQL 명령이 전달되면 파서가 이를 확인하고 구문 분석합니다. 먼저 SQL 명령 구문의 적법성을 분석하고 SQL 명령을 데이터 구조로 분해하려고 시도합니다. SQL 문이 비합리적입니다

Optimizer: Optimizer 구성 요소
SQL 문은 쿼리하기 전에 쿼리 최적화 프로그램을 사용하여 쿼리를 최적화합니다.
예: 연령 = 20인 사용자의 ID, 이름 선택

1. 이 선택 쿼리는 모든 테이블을 먼저 쿼리한 다음 연령별로 필터링하는 대신 먼저 where 문을 기반으로 선택합니다.
2. 이 선택 쿼리는 먼저 다음을 기반으로 선택합니다. id 및 name 모든 속성을 꺼내고 필터링하는 대신 속성 프로젝션을 수행합니다.
3. 이 두 쿼리 조건을 연결하여 최종 쿼리 결과를 생성합니다.

Caches & Buffers: 버퍼 풀 구성 요소
쿼리 캐시에 적중 쿼리가 있는 경우 결과적으로 쿼리 문은 쿼리 캐시에서 데이터를 직접 가져올 수 있습니다. 이 캐싱 메커니즘은 일련의 작은 캐시로 구성됩니다. 예를 들어 테이블 캐시, 레코드 캐시, 키 캐시, 권한 캐시 등이 있습니다.

엔진 레이어 구성 요소

플러그형 스토리지 엔진: 스토리지 엔진
스토리지 엔진은 데이터 저장, 인덱스 생성, 쿼리 데이터 업데이트 등과 같은 기술을 구현한 것입니다. 스토리지 엔진은 라이브러리 기반이 아닌 테이블 기반입니다. 따라서 스토리지 엔진을 테이블 유형이라고도 부를 수 있습니다. 테이블 생성, 데이터 저장, 검색, 업데이트 등은 모두 MySQL 스토리지 엔진에 의해 완료되며, 이는 MySQL 스토리지 엔진이 MySQL에서 수행하는 중요한 역할이기도 합니다.

MYSQL은 플러그인 스토리지 엔진 아키텍처를 제공하므로 사용자는 다양한 필요에 따라 데이터 테이블에 대해 다양한 스토리지 엔진을 선택할 수도 있고 필요에 따라 스토리지 엔진을 작성할 수도 있습니다. 다른 스토리지 엔진을 사용하는 라이브러리의 다른 테이블도 허용됩니다.

스토리지 계층 구성 요소

파일 시스템: 파일 시스템

MySQL 데이터베이스 파일 및 일부 로그 파일 등을 실제로 저장하는 데 사용되는 시스템입니다. 일반적인 것에는 Linux, Windows 등이 포함됩니다.

3. 스토리지 엔진

일반적으로 사용되는 스토리지 엔진의 차이점

MySQL 버전 5.5 이전에는 기본 스토리지 엔진이 MySQL과 함께 제공되는 MyISAM이었습니다. 버전 5.5 이후에는 기본 스토리지 엔진이 MySQL용으로 타사에서 개발한 InnoDB로 변경되었습니다. 왜 바꾸나요?

주된 이유는 InnoDB가 트랜잭션 및 행 수준 잠금을 지원하기 때문입니다. 이는 비즈니스 일관성 요구 사항이 높은 시나리오에 더 적합합니다.

다양한 스토리지 엔진을 위한 테이블 생성 명령문:

CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_myisam` (
  `id` int(11) PRIMARY KEY  AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_memory` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_archive` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=Archive DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_csv` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `phone` varchar(11) NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8mb4;

InnoDB
InnoDB 스토리지 엔진은 오늘날 MySQL의 기본 스토리지 엔진입니다. InnoDB 스토리지 엔진은 커밋, 롤백 및 충돌 복구 기능을 통해 트랜잭션 보안을 제공합니다. 그러나 MyISAM의 스토리지 엔진에 비해 InnoDB의 쓰기 처리 효율성은 덜 효율적이며 데이터와 인덱스를 유지하는 데 더 많은 디스크 공간을 차지합니다.

InnoDB 스토리지 엔진의 특성은 다른 스토리지 엔진과 다릅니다.

1. 트랜잭션 제어
2. 외래 키 제약 조건

InnoDB에는 테이블과 인덱스를 저장하는 다음 두 가지 방법이 있습니다.

1. 공유 테이블 공간 스토리지를 사용합니다. 이렇게 생성된 테이블의 테이블 구조는 .frm 파일에 저장되며, 데이터와 인덱스는 innodb_data_home_dir 및 innodb_data_file_path로 정의된 테이블스페이스(여러 파일일 수 있음)에 저장됩니다.
2. 다중 테이블 공간 스토리지를 사용하면 이렇게 생성된 테이블의 테이블 구조는 여전히 .frm 파일에 존재하지만, 각 테이블의 데이터와 인덱스는 .ibd에 별도로 저장됩니다.

InnoDB 적합한 사용 환경:
InnoDB는 MySQL의 기본 스토리지 엔진으로 트랜잭션 처리 애플리케이션에 사용되며 외래 키를 지원합니다. 애플리케이션이 트랜잭션 무결성에 대한 요구 사항이 상대적으로 높고 동시 조건에서 데이터 일관성이 필요하며 데이터 작업에 삽입 및 쿼리 외에도 많은 업데이트 및 삭제 작업이 포함되는 경우 InnoDB 스토리지 엔진이 더 적합한 선택입니다.

삭제 및 업데이트로 인한 잠금을 효과적으로 줄이는 것 외에도 InnoDB 스토리지 엔진은 트랜잭션의 완전한 제출 및 롤백을 보장할 수 있습니다. 청구 시스템이나 금융 시스템과 같이 데이터 정확도가 높은 시스템의 경우 InnoDB가 가장 적합한 선택입니다.

MyISAM
MyISAM은 트랜잭션이나 외래 키를 지원하지 않으며 액세스 속도가 빠르고 SELECT 및 INSERT 기반 트랜잭션이나 애플리케이션의 무결성에 대한 요구 사항이 없다는 것이 장점입니다. 표면.

에는 다음 두 가지 중요한 기능이 있습니다.

• 트랜잭션을 지원하지 않습니다.
• 파일 저장 방법

각 MyISAM은 디스크에 3개의 파일로 저장되며 파일 이름은 테이블 이름과 동일하지만 확장자는 다릅니다:

• .frm(저장 테이블 정의);
• .MYD(MYData, 저장 데이터);
• .MYI(MYIndex, 저장 인덱스)

MyISAM 적합한 사용 환경:
읽기 주로 작업과 삽입 작업을 수행하고 업데이트 및 삭제 작업이 몇 개만 있으며 트랜잭션 무결성 및 동시성에 대한 요구 사항이 그리 높지 않으므로 이 스토리지 엔진을 선택하는 것이 매우 적합합니다.

MEMORY
메모리 저장 엔진은 테이블 데이터를 메모리에 저장합니다. 각 MEMORY 테이블은 실제로 .frm 형식의 디스크 파일에 해당합니다. 이 파일은 테이블의 구조만 저장하고 해당 데이터 파일은 메모리에 저장되므로 빠른 데이터 처리에 도움이 되며 전체 테이블의 효율성이 향상됩니다. .

MEMORY형 테이블은 데이터가 메모리에 저장되고 기본적으로 HASH 인덱스를 사용하므로 매우 빠르게 액세스할 수 있습니다. 그러나 서비스가 종료되면 테이블의 데이터는 손실됩니다.

메모리 적합한 사용 환경:
빠른 위치 기록 및 기타 유사한 데이터 환경이 필요할 때 여러 액세스 블록을 제공할 수 있는 RAM에 모든 데이터를 저장합니다.

MEMORY의 단점은 테이블 크기에 제한이 있다는 것입니다. 테이블이 너무 크면 메모리에 캐시할 수 없습니다. 둘째, 테이블에 있는 데이터를 복원할 수 있는지 확인해야 합니다. 데이터베이스가 비정상적으로 종료된 후 테이블을 복원할 수 있습니다. MEMORY 테이블은 일반적으로 액세스 결과를 빠르게 얻기 위해 자주 업데이트되지 않는 작은 테이블에 사용됩니다.

MERGE
MERGE 스토리지 엔진은 MyISAM 테이블 집합의 조합입니다. 이러한 MyISAM 테이블은 동일한 구조를 가져야 합니다. MERGE 테이블 자체는 MERGE 유형에서 데이터를 저장하지 않습니다. 이러한 작업은 실제로 내부 MyISAM 테이블에서 수행됩니다.

MERGE 유형 테이블의 삽입 작업의 경우 삽입된 테이블은 INSERT_METHOD 절을 통해 정의되며, 이는 세 가지 다른 값을 가질 수 있습니다. FIRST 또는 LAST 값을 사용하면 그에 따라 첫 번째 또는 마지막 테이블에 삽입 작업이 적용됩니다. 이 절을 정의하거나 NO로 정의하여 이 MERGE 테이블에서 삽입 작업을 수행할 수 없음을 나타냅니다.

MERGE 테이블에 대해 DROP 작업을 수행할 수 있지만 이 작업은 MERGE 테이블의 정의만 삭제하고 내부 테이블에는 영향을 미치지 않습니다.

MERGE 적합한 사용 환경:
일련의 동등한 MyISAM 테이블을 논리적으로 결합하고 이를 객체로 참조하는 데 사용됩니다.

MERGE 테이블의 장점은 단일 MyISAM 테이블의 크기 제한을 뛰어넘을 수 있다는 점이며, 서로 다른 테이블을 여러 디스크에 분산함으로써 MERGE 테이블의 액세스 효율성을 효과적으로 향상시킬 수 있습니다. 이는 데이터 웨어하우징과 같은 VLDB 환경에 매우 적합합니다.

추천 학습: mysql 비디오 튜토리얼

위 내용은 MySql 최적화 아키텍처 및 스토리지 엔진(요약 공유)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!