mysql 최적화 (1) 테이블 최적화 및 컬럼 유형 선택

테이블 최적화:

1: 고정 길이와 가변 길이의 분리

예를 들어 id int는 4바이트를 차지하고 char(4)는 4자를 차지하며 역시 고정 길이입니다. ,
time은 각 단위 값이 차지하는 바이트가 고정되어 있음을 의미합니다.

핵심 필드와 일반적으로 사용되는 필드는 고정된 길이로 빌드되어 테이블에 배치되어야 합니다. , 이러한 유형의 가변 길이 필드는 단일 테이블에 배치하고 기본 키를 사용하여 이를 핵심 테이블과 연결하는 데 적합합니다.

SQL은 모두 일정하므로 100,000개의 데이터를 매우 빠르게 건너뜁니다.

2: 일반적으로 사용되는 필드와 일반적이지 않은 필드를 구분해야 합니다.

웹 사이트의 특정 비즈니스, 해당 필드의 쿼리 시나리오를 기반으로 분석해야 하며 쿼리 빈도가 낮은 필드는 다음과 같습니다.

3: 관련 통계가 필요한 일대다 필드에 중복 필드를 추가합니다.
관련 쿼리 줄이기

다음 BBS 효과 통계를 참조하세요. 게시물 수를 열 아래에 추가하세요. 중복 필드는 기사가 게시될 때마다 기사 수를 +1씩 업데이트하여 쿼리 강도를 줄입니다.

열 선택 원칙:

1 : 필드 유형 우선순위 정수> 날짜, 시간 > enum,char>varchar > blob,text

열 특성 분석: 정수 유형: 고정 길이, 국가/지역 구분 없음

예를 들어tinyint 1,2 ,3,4,5 71fb34173e4ee87dab1f85dc1c283a44 char(1) a,b,c,d,e는 공간적으로 모두 1바이트를 차지하지만 순서는 다릅니다.
정렬하면 전자가 더 빠릅니다

이유: 후자가 문자 집합과 대조 집합을 고려해야 합니다(즉, 정렬 규칙)

시간이 고정 길이이므로 작업이 빠릅니다. , 시간대를 고려하면
> '2005-10-12'; 시간을 int 형식으로 저장하는 것이 불편합니다. value는 내부적으로 정수형으로 저장되지만 char과 결합할 경우 내부 문자열 및 값 변환이 필요합니다.
Char는 문자 집합과 (정렬) 대조 집합
varchar을 고려하여 고정 길이입니다. 가변 길이는 정렬 중에 문자 집합과 데이터 정렬 집합의 변환을 고려해야 하므로 속도가 느립니다.
text/Blob은 메모리 임시 테이블을 사용할 수 없습니다(정렬 등). 작업은 디스크에서만 수행할 수 있습니다. 🎜>
성별: utf8을 예로 들어보세요

char(1), 3단어 길이의 바이트

enum('male',' Female') // 내부적으로 숫자로 변환됩니다. 저장을 위해서는 추가적인 변환 과정이 있습니다.

tinyint(), // 0 1 2 // 고정 길이는 1바이트입니다.

SQL 최적화 책 "MYSQL 고성능 최적화"

날짜/시간 선택과 관련하여 마스터의 명확한 의견은 타임스탬프를 저장하기 위해 null이 아닌 int unsgined를 직접 선택하는 것입니다 http://www.xaprb.com/blog/2014/01 /30/timestamps-in-mysql /

Time--->정수로 저장

2: 충분하지만 관대하지 마세요(예: smallint, varchar(N))

이유: 큰 필드는 메모리를 낭비하고 속도에 영향을 미칩니다.

age를 예로 들면,tinyint unsigned not null은 255년을 저장할 수 있으며 이는 3바이트를 낭비합니다.

varchar에 저장된 콘텐츠입니다. (10)과 varchar(300)은 동일하지만 테이블 조인 쿼리 중에 varchar(300)이 더 많은 메모리를 차지합니다

3: NULL() 사용을 피하세요

이유: NULL은 인덱싱에 도움이 되지 않으며 특수 바이트로 표시해야 합니다.

는 실제로 디스크에서 더 많은 공간을 차지합니다(mysql5.7에서는 null이 개선되었지만 쿼리는 여전히 불편함)

실험 :

동일한 필드를 사용하여 하나는 Null이 허용되고 다른 하나는 Null이 허용되지 않는 두 개의 테이블을 생성하고 각각에 10,000개의 항목을 추가하고 인덱스 파일의 크기를 확인할 수 있습니다. 을 찾을 수 있으면 null에 대한 인덱스가 더 큽니다. (mysql5.5에서는 null이 최적화되었으며 크기 차이가 더 이상 명확하지 않습니다.)

추가로: null은 쿼리에 편리하지 않습니다.

열 이름=null;

열 이름!=null; 값을 찾을 수 없는 경우,

열 이름이 null이거나 쿼리할 null이 아닙니다.

create table dictnn (
id int,
word varchar(14) not null default '',
key(word)
)engine myisam charset utf8;

create table dictyn (
id int,
word varchar(14),
key(word)
)engine myisam charset utf8;

alter table dictnn disable keys;
alter table dictyn disable keys;

insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
insert into dictyn select id,if(id%2,word,null) from dict limit 10000;

alert table dictnn enable keys;
alter table dictyn enable keys;

Enum 컬럼 설명

1: enum 컬럼은 내부적으로 정수

2: enum 열은 가장 빠른 enum 열과 연결됩니다.

3: enum 열은 (var) char보다 약합니다---char와 연결될 때 필요합니다. 변환하는 데 시간이 걸립니다.

4: char이 매우 긴 경우에도 enum은 여전히 ​​고정된 길이의 정수라는 장점이 있습니다.

쿼리되는 데이터의 양이 다음과 같은 경우 클수록 enum의 장점은 더욱 분명해집니다.

5: enum은 char/varchar와 관련이 있습니다. 변환해야 하기 때문에 속도가 enum->enum, char->char보다 느립니다.

그런데 가끔 이렇게도 사용됩니다 ----- 즉, 데이터의 양이 특히 많을 때 IO를 절약할 수 있습니다.

테스트:

create table t2 (
id int,
gender enum('man','woman'),
key(gender)
)engine myisam charset utf8;

create table t3 (
id int,
gender char(5) not null default '',
key(gender)
)engine myisam charset utf8;

alter table t2 disable keys;
alter table t3 disable keys;

열c63bf5d6257ea40a0ddea7fe0604ffc8열

insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;

alter table t2 enable keys;
alter table t3 enable keys;
mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender
mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender

시간

열거< ;--->enum

10.53

문자문자

24.65

Enumchar

18.22

t2 테이블의 장점이 명확하지 않으면 t3의 성별 열을 늘리고, char(15),
char(20)...

t3의 성별 컬럼을 보면 크기가 커질수록 t2 테이블의 장점이 점차 드러나게 됩니다.



이유는 enum('manmaman','womanwomanwoman')에 열거된 문자의 길이에 관계없이

내부적으로는 정수로 표현되며, 생성되는 데이터의 크기는

그러나 char 유형은 메모리에 점점 더 많은 데이터를 생성합니다.



요약: enum 유형과 enum 유형이 더 빠르게 연결됩니다.

Enum형으로 IO 절약

위는 mysql 최적화 (1) 테이블 최적화 및 컬럼형 선택, 관련 내용은 PHP 중국어 홈페이지(www.php.cn) 참고해주세요!