MySQL의 데이터베이스 최적화를 위한 일반적인 SQL 문(요약 공유)

이 글은 mysql에 대한 관련 지식을 제공합니다. 주로 MySQL의 데이터베이스 최적화를 위한 몇 가지 일반적인 SQL 문을 요약하고 있습니다. 글의 샘플 코드가 자세히 설명되어 있으므로 함께 살펴보시기 바랍니다. 당신에게.

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

1.SHOW ENGINES

실행 엔진 및 기본 엔진을 확인하세요.

2.SHOW PROCESSLIST

SHOW PROCESSLIST는 현재 데이터베이스 연결의 사용량과 다양한 상태 정보를 보는 데 매우 유용합니다. SHOW PROCESSLIST; 항목을 모두 나열하려면 SHOW FULL PROCESSLIST를 사용하세요.

속성 열 및 의미:

id	다음 경우에 매우 유용합니다. 당신은 성명을 죽이고 싶습니다.
user	현재 사용자를 표시합니다. 루트가 아닌 경우 이 명령은 권한 내의 SQL 문만 표시합니다.
host	이 명령문이 전송된 IP와 포트를 표시합니다. 문제가 있는 글을 게시한 사용자를 추적하는 데 사용할 수 있습니다.
db	이 프로세스가 현재 연결된 데이터베이스를 표시합니다.
command	현재 연결의 실행된 명령(일반적으로 절전 모드, 쿼리 및 연결)을 표시합니다.

상태 열과 그 의미, mysql에 나열된 상태:

테이블 확인	데이터 테이블 확인(자동).
Closing tables	은 테이블의 수정된 데이터를 디스크로 플러시하는 동시에 다 사용한 테이블을 닫는 것입니다. 이는 빠른 작업이지만 그렇지 않은 경우에는 디스크 공간이 꽉 찼는지 또는 디스크 부하가 심한지 확인해야 합니다.
Connect Out	복제 슬레이브 서버가 마스터 서버에 연결 중입니다.
디스크의 tmp 테이블에 복사	임시 결과 세트가 tmp_table_size(기본값 16M)보다 크기 때문에 메모리 절약을 위해 임시 테이블을 메모리 저장소에서 디스크 저장소로 변환하는 중입니다.
Creating tmp table	일부 쿼리 결과를 저장할 임시 테이블을 만듭니다.
deleting from main table	서버가 다중 테이블 삭제의 첫 번째 부분을 수행하고 있으며 방금 첫 번째 테이블을 삭제했습니다.

3.SHOW STATUS LIKE 'InnoDB_row_lock%'

InnoDB의 행 수준 잠금 상태 변수입니다.

InnoDB의 행 수준 잠금 상태 변수에는 잠금 대기 횟수뿐만 아니라 전체 잠금 기간, 각 시간의 평균 기간, 최대 기간도 기록됩니다. 현재 잠금을 기다리고 있는 잠금 수를 표시하는 상태 변수입니다. 각 상태 수량에 대한 설명은 다음과 같습니다.

• InnoDB_row_lock_current_waits: 현재 잠금을 기다리는 잠금 수
• InnoDB_row_lock_time: 시스템 시작부터 지금까지의 총 잠금 시간
• InnoDB_row_lock_time_avg: 매번 대기하는 데 소요된 평균 시간.
• InnoDB_row_lock_time_max: 시스템 시작부터 지금까지 가장 일반적인 시간을 기다리는 데 소요된 시간
• InnoDB_row_lock_waits: 시스템 시작부터 지금까지의 총 대기 횟수

이 5가지 상태 변수에서 더 중요한 것은 InnoDB_row_lock_time_avg입니다. 평균 대기 시간), InnoDB_row_lock_waits(총 대기 수) 및 InnoDB_row_lock_time(총 대기 시간). 특히 대기 횟수가 많고 각 대기 시간이 짧지 않은 경우 시스템에 대기가 왜 그렇게 많은지 분석하고 분석 결과에 따라 최적화 계획을 구체화하기 시작해야 합니다.

InnoDB_row_lock_waits 및 InnoDB_row_lock_time_avg 값이 상대적으로 높은 등 잠금 경합이 심각한 것으로 확인되면 InnoDB 모니터를 설정하여 잠금 충돌이 발생한 테이블과 데이터 행을 추가로 관찰하고 충돌 원인을 분석할 수도 있습니다. 잠금 경합.

4.SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS 명령은 현재 InnoDB 모니터에서 모니터링하는 많은 정보를 출력하며, 내용은 행과 열이 없는 단일 문자열입니다. 세그먼트는 InnoDB 스토리지 엔진의 다양한 부분에 대한 정보에 해당하며, 그 중 일부는 InnoDB 개발자에게 매우 유용합니다.

다음과 같이 마지막으로 기록된 교착 상태 정보인 LATEST DETECTED DEADLOCK 섹션이 있습니다.

• "(1) TRANSACTION"은 첫 번째 트랜잭션의 정보를 표시합니다.
• "(1 ) WAITING FOR THIS LOCK TO BE GRANTED"는 첫 번째 트랜잭션이 기다리고 있는 잠금 정보를 표시합니다.
• "(2) TRANSACTION"은 두 번째 트랜잭션의 정보를 표시하고,
• "(2) HOLDS THE LOCK(S)"을 표시합니다. 두 번째 트랜잭션이 보유한 잠금 정보
• "(2) WAITING FOR THIS LOCK TO BE GRANTED"는 두 번째 트랜잭션을 기다리는 잠금 정보를 표시합니다.
• 마지막 줄은 "WE ROLL BACK TRANSACTION (2) ), 롤백을 나타냅니다.

5.SHOW INDEXS

SHOW INDEXS는 테이블의 인덱스 정보를 쿼리합니다. SHOW INDEXES FROM table_name;

테이블을 생성하는 SQL은 다음과 같습니다.

CREATE TABLE contacts(
    contact_id INT AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL comment 'first name',
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY(contact_id),
    UNIQUE(email),
    INDEX phone(phone) ,
    INDEX names(first_name, last_name) comment 'By first name and/or last name'
);

저장 프로시저 50,000개의 데이터 삽입:

CREATE PROCEDURE zqtest ( ) BEGIN
	DECLARE
		i INT DEFAULT 0;
	DECLARE
		j VARCHAR ( 100 ) DEFAULT 'first_name';
	DECLARE
		k VARCHAR ( 100 ) DEFAULT 'last_name';
	DECLARE
		l VARCHAR ( 100 ) DEFAULT 'email';
	DECLARE
		m VARCHAR ( 20 ) DEFAULT '11111111111';
	
	SET i = 0;
	START TRANSACTION;
	WHILE
			i < 50000 DO
		IF
			MOD ( i, 100 ) = 0 THEN
				
				SET j = CONCAT( &#39;first_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 200 ) = 0 THEN
				
				SET k = CONCAT( &#39;last_name&#39;, i );
			
		END IF;
		IF
			MOD ( i, 50 ) = 0 THEN
				
				SET m = CONCAT( &#39;&#39;, CAST( m as UNSIGNED) + i );
			
		END IF;
		INSERT INTO contacts ( first_name, last_name, email, phone )
		VALUES
			( j, k, CONCAT(l,i), m );
		
		SET i = i + 1;
		
	END WHILE;
	COMMIT;
	
END;

연락처에서 show index를 사용한 결과는 다음과 같습니다.

필드 설명:

Seq_in_index 1부터 시작하는 열 순서 번호입니다. 또한 공동 인덱스에서 열의 순서를 나타낼 수도 있습니다. Column_name인덱스 열 이름은 공동 인덱스인 경우입니다. 특정 열의 이름 Collation 인덱스에 저장되는 방식은 아마도 문자 순서를 의미할 것입니다. Cardinality인덱스에 있는 다양한 값의 수를 "카디널리티"라고도 합니다. 이 카디널리티는 인덱스가 클수록 정확하지 않으며 ANALYZE TABLE을 사용하여 수정할 수 있습니다. 전체 열 값이 인덱싱된 경우 NULL입니다. 압축되지 않은 경우 PackedNULL입니다. 압축된 열 솔루션 및 압축된 테이블 솔루션입니다. 열 값에 null이 포함될 수 있습니다. YESIndex_type인덱스 구조 유형, 일반적인 유형은 FULLTEXT, HASH, BTREE, RTREEComment, Index_commentComments입니다.

6.ALTER TABLE xx ENGINE = INNODB

인덱스 구조를 포함하여 테이블을 다시 빌드합니다. 데이터 삭제 시 남은 인덱스 페이지 분할 및 디스크 조각화를 제거할 수 있습니다.

7.ANALYZE TABLE

은 테이블을 재구축하지 않으며, 데이터를 수정하지 않고 테이블의 인덱스 정보만 다시 계산하는 과정에 MDL 읽기 잠금이 추가됩니다. tablename의 show index에 있는 통계 인덱스의 Cardinality가 비정상적인 데이터인 상황을 수정하는 데 사용할 수 있습니다.

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

Table	테이블 이름
Non_unique	고유 인덱스 는 0이고 나머지 인덱스는 1입니다. 고유 인덱스입니다.
Key_name	인덱스 이름이 동일하면 동일한 인덱스이며 각 행은 조인트의 열을 나타냅니다.

위 내용은 MySQL의 데이터베이스 최적화를 위한 일반적인 SQL 문(요약 공유)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!