Mysql 최적화: 스토리지 엔진 및 인덱스 최적화에 대한 심층적인 이해

데이터베이스에서 두 스토리지 엔진의 저장 방법을 비교하세요.

MyIsam: 주의 깊게 관찰하면 이 엔진을 사용하는 데이터베이스에는 일반적으로 **.frm,* 세 개 이상의 파일이 포함되어 있음을 알 수 있습니다. *.myi, (인덱스 넣기) **.myd (데이터 넣기), 인덱스(**.myi 파일)를 통해 데이터가 저장된 데이터 파일에서 행을 찾으면 라인 리턴이 생성됩니다. 행 리턴, 즉 인덱스 커버리지가 없으면 속도가 매우 빠릅니다

InnoDb: 파일이고 인덱스와 데이터가 함께 들어 있는 클러스터형 인덱스의 한 가지 단점은 다음과 같습니다. 파일이 크고 디스크가 회전하며 검색이 상대적으로 낮습니다. 이는 또한 페이징된 블록 파일을 생성합니다.

Create table: myisam 엔진과 innodb 엔진을 각각 사용하여 기본 키 인덱스와 조인트 인덱스를 포함합니다.

create table smth (
id int auto_increment ,
ver int(11) 기본 null,
content varchar(1000) not null,
intro varchar(1000) not null,
기본 키(id) ,
키 IDver(id,ver)

)engine = myisam 기본 charset = utf8;

smth1 테이블 생성(
id int auto_increment ,
ver int(11) 기본 null,
content varchar(1000) not null,
intro varchar(1000) not null,
기본 key(id),
key idver(id,ver)

)engine = innodb default charset = utf8;
파일 디렉토리 분석은 위와 같습니다:

이제 10,000개의 데이터를 삽입하는 두 개의 저장 프로시저를 생성합니다

프로시저 smthTest() 생성
begin
선언 num int 기본값 1050 ;
while num set num := num +1;
smth 값에 삽입 ​​(num ,num,'I am step','who am I');
end while ;

end;


smthTest1()
begin
num int default 0;
while num < 10000 do
설정 num := num +1;
smth1 값에 삽입 ​​(num ,num,'나는 단계','나는 누구인가');
end while ;

end;

프로파일링 설정 = 1; 세부 정보 표시

결과 분석 실행:

진술 분석 결과:

mysql> 설명 
 ID별 smth 순서에서 ID, 버전, 콘텐츠를 선택하세요.  
+----+-------------+-------+------+------------ ---+------+---------+------+------+--------------- -+
| 아이디 | 선택_유형 | 테이블 | 유형 | 가능한_키 | 키  | key_len | 참조  | 행 | 추가          |
+----+---------------+-------+------+------------ ------+------+---------+------+------+------------- ---+
|  1 | 단순      | 정말  | 모두  | NULL          | NULL | NULL    | NULL | 9946 | 파일 정렬 사용 |
+----+-------------+-------+------+--------- ------+------+---------+------+------+------------ ----+
세트의 행 1개
mysql> 설명
 ID, 버전별 smth 순서에서 ID, 버전, 콘텐츠 선택;
+----+-------------+-------+- ------+---------------+------+---------+------+---- ---+----------------+
| 아이디 | 선택_유형 | 테이블 | 유형 | 가능한_키 | 키  | key_len | 참조  | 행 | 추가          |
+----+---------------+-------+------+------------ ------+------+---------+------+------+------------- ---+
|  1 | 단순      | 정말  | 모두  | NULL          | NULL | NULL    | NULL | 9946 | 파일 정렬 사용 |
+----+-------------+-------+------+--------- ------+------+---------+------+------+------------ ----+
세트의 행 1개
mysql> 설명
 smth1에서 id,ver,content를 id별로 선택하세요.
+----+-------------+---------+--- ----+---------------+---------+---------+------+-- ----+---------+
| 아이디 | 선택_유형 | 테이블 | 유형  | 가능한_키 | 키     | key_len | 참조  | 행 | 추가 |
+------+-------------+---------+---------+--------- ------+---------+---------+------+------+-------+
|  1 | 단순      | smth1 | 색인 | NULL          | 기본 | 4       | NULL | 9932 | NULL  |
+----+-------------+-------+---------+--------- ------+---------+---------+------+------+-------+
세트의 행 1개
mysql> 설명 선택 ID
,ver,smth1의 콘텐츠 ID별 순서,ver;
+----+---------------+---------+- ------+---------------+------+---------+------+---- ---+----------------+
| 아이디 | 선택_유형 | 테이블 | 유형 | 가능한_키 | 키  | key_len | 참조  | 행 | 추가          |
+----+---------------+-------+------+------------ ------+------+---------+------+------+------------- ---+
|  1 | 단순      | smth1 | 모두  | NULL          | NULL | NULL    | NULL | 9932 | 파일 정렬 사용 |
+----+-------------+-------+------+--------- ------+------+---------+------+------+------------ ----+

mysql> 설명
 ID별 smth 순서에서 ID 선택;
+----+---------------+-------+--------- +---------------+---------+---------+------+------ +-------------+
| 아이디 | 선택_유형 | 테이블 | 유형  | 가능한_키 | 키     | key_len | 참조  | 행 | 추가       |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+--------- ----+
|  1 | 단순      | 정말  | 색인 | NULL          | 기본 | 4       | NULL | 9946 | 인덱스 사용 |
+----+---------------+-------+---------+--------- -------+---------+---------+------+------+-------- -----+
세트의 행 1개
mysql> 설명
 id,ver별 smth 순서에서 id 선택;
+----+-------------+-------+------ ---+---------------+-------+---------+------+------ +-------------+
| 아이디 | 선택_유형 | 테이블 | 유형  | 가능한_키 | 키   | key_len | 참조  | 행 | 추가       |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+----------- --+
|  1 | 단순      | 정말  | 색인 | NULL          | 아이디버 | 9       | NULL | 9946 | 인덱스 사용 |
+----+---------------+-------+---------+--------- --------+-------+---------+------+------+---------- ---+
세트의 행 1개
mysql> 설명
 smth1에서 ID 선택 ID별 순서;
+----+-------------+-------+--------- +---------------+---------+---------+------+------ +-------------+
| 아이디 | 선택_유형 | 테이블 | 유형  | 가능한_키 | 키     | key_len | 참조  | 행 | 추가       |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+--------- ----+
|  1 | 단순      | smth1 | 색인 | NULL          | 기본 | 4       | NULL | 9932 | 인덱스 사용 |
+----+---------------+-------+---------+--------- -------+---------+---------+------+------+-------- -----+
세트의 행 1개
mysql> 설명
 smth1에서 ID 선택 ID, 버전별 순서;
+----+-------------+-------+------ ---+---------------+-------+---------+------+------ +-------------+
| 아이디 | 선택_유형 | 테이블 | 유형  | 가능한_키 | 키   | key_len | 참조  | 행 | 추가       |
+----+-------------+-------+-------+--------- ------+---------+---------+------+------+----------- --+
|  1 | 단순      | smth1 | 색인 | NULL          | 아이디버 | 9       | NULL | 9932 | 인덱스 사용 |
+----+---------------+-------+-------+--------- --------+-------+---------+------+------+---------- ---+
세트의 행 1개