mysql 성능 최적화 가이드

이 글의 주요 목표는 MySQL 데이터베이스를 최적화하는 방법을 소개하는 것입니다. 정기적으로 테이블을 분석하고 테이블을 확인하고, 정기적으로 테이블을 최적화하고, 몇 가지 일반적인 SQL 문을 최적화함으로써 MySQL에서 보다 효율적인 SQL을 작성하는 방법을 배우는 데 도움이 될 수 있습니다. 개발.

1. 주기 분석 테이블과 체크 테이블

분석 테이블의 구문은 다음과 같습니다.

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name] ...

위 문장은 테이블의 키워드 분포를 분석하고 저장하는 데 사용됩니다. 분석 결과를 통해 시스템은 다음을 얻을 수 있습니다. 정확한 통계 정보를 통해 SQL이 올바른 실행 계획을 생성할 수 있습니다. 실제 실행 계획이 예상한 실행 계획과 다르다고 사용자가 느끼는 경우 분석 테이블을 실행하면 문제가 해결될 수 있습니다. 분석 중에 테이블은 읽기 잠금으로 잠깁니다. 이는 MyISAM, DBD 및 InnoDB 테이블에서 작동합니다.

예를 들어 데이터 테이블을 분석하려면

analyze table table_name

의 구문은 다음과 같습니다. 확인 테이블은 다음과 같습니다.

CHECK TABLE tb1_name[,tbl_name]...[option]...option = {QUICK | FAST CHANGED}

체크 테이블의 기능은 하나 이상의 테이블에 오류가 있는지 확인하는 것입니다. MyISAM 및 InnoDB 테이블의 경우 키워드 통계가 업데이트됩니다.

CHECK TABLE은 존재하지 않는 뷰 정의에서 참조된 테이블과 같은 오류가 있는지 뷰를 확인할 수도 있습니다.

2. 정기적으로 테이블을 최적화합니다.

최적화된 테이블의 구문은 다음과 같습니다.

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [, tbl_name]...

테이블의 상당 부분이 삭제되었거나 가변 길이 행이 포함된 테이블(VARCHAR, BLOB가 포함된 테이블)인 경우 또는 TEXT 열)이 추가되었습니다. 더 많은 변경 사항이 있는 경우 테이블 최적화를 위해 OPTIMIZE TABLE 명령을 사용해야 합니다. 이 명령은 테이블의 공간 조각을 병합하고 삭제 또는 업데이트로 인한 공간 낭비를 제거할 수 있지만 OPTIMIZE TABLE 명령은 MyISAM, BDB 및 InnoDB 테이블에서만 작동합니다.

예: 최적화 테이블 table_name

참고: 분석, 확인, 최적화 실행 중에 테이블이 잠기므로 관련 작업은 반드시 MySQL 데이터베이스가 사용 중이지 않을 때 수행하세요. .

자주 사용하는 SQL 최적화

우리가 개발 중에 자주 사용하는 SQL 문은 INSERT, GROUPBY 등이 전부입니다. 이러한 SQL 문을 어떻게 최적화합니까?

1. 데이터 대량 삽입

load 명령을 사용하여 데이터를 가져올 때 적절한 설정을 사용하면 가져오기 속도를 높일 수 있습니다.

MyISAM 스토리지 엔진 테이블의 경우 다음과 같은 방법으로 대량의 데이터를 빠르게 가져올 수 있습니다.

코드는 다음과 같습니다.

ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;

DISABLE KEYS 및 ENABLE KEYS 사용 MyISAM 테이블에서 고유하지 않은 인덱스의 업데이트를 켜거나 끄는 데 사용됩니다. 비어 있지 않은 MyISAM 테이블로 많은 양의 데이터를 가져올 때 이 두 명령을 설정하면 가져오기 효율성이 향상될 수 있습니다.

빈 MyISAM 테이블에 많은 양의 데이터를 가져올 때 기본적으로 데이터를 먼저 가져온 다음 인덱스를 설정할 필요가 없습니다.

데이터 인파일 '/home/mysql/text_txt'를 테이블 텍스트로 로드

InnoDB 유형 테이블의 경우 이 방법은 데이터 가져오기의 효율성을 향상시킬 수 없지만 InnoDB 유형 테이블을 최적화하는 방법에는 여러 가지가 있습니다.

1. InnoDB 유형의 테이블은 기본 키 순서로 저장되므로 가져온 데이터를 기본 키 순서로 정렬하면 가져온 데이터의 효율성을 효과적으로 높일 수 있습니다.

2. 데이터를 가져오기 전에 SET UNIQUE_CHECKS=0을 실행하여 고유성 확인을 해제합니다. 가져오기가 완료된 후 SET UNIQUE_CHECKS=1을 실행하여 고유성 확인을 복원하면 가져오기 효율성이 향상됩니다.

3. 애플리케이션이 자동 제출을 사용하는 경우 가져오기 전에 SET AUTOCOMMIT=0을 실행하여 자동 제출을 끄는 것이 좋습니다. 가져오기가 완료된 후 SET AUTOCOMMIT=1을 실행하여 자동 제출을 켜는 것이 좋습니다. 수입 효율성도 향상시킬 수 있습니다.

INSERT 문 최적화

데이터 INSERT를 수행할 때 다음과 같은 최적화 방법을 고려할 수 있습니다

1. 고객으로부터 동시에 많은 행이 삽입되는 경우 시도해 보세요. 여러 값 테이블의 INSERT 문을 사용하려면 이 방법을 사용하면 클라이언트와 MySQL 데이터베이스 간의 연결, 종료 및 기타 소비가 크게 줄어들어 개별적으로 실행되는 단일 INSERT 문보다 효율성이 더 빨라집니다.

예:

i

nsert into test values(1,2)
insert into test values(3,4)
insert into test values(5,6)

위의 세 문장을 다음과 같이 변경합니다. 테스트 값에 삽입(1,2),(3,4),(5 ,6)…

2. 서로 다른 클라이언트에서 여러 행을 삽입하는 경우 INSERT DELAYED 문을 사용하면 더 빠른 속도를 얻을 수 있습니다.

DELAYED는 INSERT 문을 즉시 실행한다는 의미입니다. 실제로 데이터는 메모리 대기열에 저장되며 실제로는 디스크에 기록되지 않습니다. 이는 각 문을 별도로 삽입하는 것보다 훨씬 빠릅니다. other 사용자가 테이블을 읽고 쓴 후 삽입합니다.

3. 인덱스 파일과 데이터 파일을 다른 디스크에 저장

4. 일괄 삽입을 수행하는 경우에는bulk_insert_buffer_size 변수의 값을 늘려서 속도를 높일 수 있습니다. MyISAM 테이블의 경우.

5. 텍스트 파일에서 테이블을 로드할 때는 LOAD DATA INFILE을 사용하세요. 이는 일반적으로 많은 insert 문을 사용하는 것보다 약 20배 빠릅니다.

위의 내용은 MySQL 성능 최적화에 있어 주의가 필요한 몇 가지 사항을 결합하여 mysql의 성능을 최대한 활용해 보겠습니다.