집 >데이터 베이스 >MySQL 튜토리얼 >MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진)

MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진)

php是最好的语言원래의: 2018-07-26 16:42:592644검색

MySQL 성능 최적화에는 테이블 최적화와 열 유형 선택이 포함됩니다. 테이블 최적화는 무엇으로 나눌 수 있나요? 1. 고정 길이 필드와 가변 길이 필드를 분리합니다. 2. 일반적으로 사용되는 필드를 일반적이지 않은 필드와 분리합니다. 3. 상관 통계가 필요한 일대다 필드에 중복 필드를 추가합니다.

1. 테이블 최적화 및 열 유형 선택

테이블 최적화:

# 🎜 🎜##1#1 고정 증가 및 변경과 분리#🎜🎜 ## 🎜🎜

예: ID int, 4바이트를 차지, CHAR(4) 길이가 4자를 차지하며 또한 고정됩니다. , 시간, 즉 각 단위 값이 차지하는 바이트는 고정됩니다. #🎜🎜 ## 核#핵심 및 일반적으로 사용되는 분야는 고정 성장을 구축하여 테이블 위에 올려 놓는 것이 좋습니다. #🎜🎜 ## 而# 및 VARCHAR, Text, BLOB, 긴 필드는 단일 테이블을 배치하는 데 적합하며 핵심 테이블과 연결됩니다. #🎜🎜 ###2, 일반적으로 사용되는 필드와 일반적이지 않은 필드를 구분합니다#🎜🎜 ## 🎜🎜 ## 🎜🎜#웹사이트의 특정 비즈니스를 분석해야 하며, 해당 필드의 쿼리 장면을 분석해야 합니다. , 조회 조회 빈도가 낮은 필드는 개별적으로 분리됩니다.

3. 관련 통계가 필요한 일대다 필드에 중복 필드를 추가합니다.

다음 효과를 확인하세요.

홈페이지에 표시되는 게시물 섹션 정보 및 게시물 개수 섹션 아래.

# 🎜🎜# 게시물 테이블을 다시 확인하고, 게시판 ID별로 게시물 그룹에서 개수(*)를 선택하여 각 섹션의 게시물 수를 가져옵니다.

2. 열 유형 선택

1. 필드 유형 우선 순위

#🎜🎜 # ,,, ,, 정수형 : 고정길이, 국가/지역 없음 문자셋에는 차이가 없습니다. 예: MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진)

tinyint 1,2,3,4,5 char(1) a,b,c,d,e

공간에서 관점에서는 둘 다 1바이트를 차지하지만 order by가 더 빠릅니다. 그 이유는 문자 집합과 대조 집합(즉, 정렬 규칙)을 고려해야 하기 때문일 수 있습니다.

시간이 고정되어 있고 작업이 빠르며 공간이 절약됩니다. 시간대를 고려하면 > `2018-08-08`;

MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진) enum을 내부적으로 정수로 저장하는 SQL을 작성하는 것은 불편하지만, cahr, 내부적으로 문자열과 값의 변환을 거쳐야 합니다.

문자는 고정 길이이며 문자 세트 및 (정렬) 교정 세트를 고려해야 합니다. 🎜# varchar는 길이가 가변적이며 문자 집합의 변환 및 변환을 고려해야 합니다. 정렬 중 대조 설정이 느리다는 것은 마스터의 명확한 의견입니다. 타임스탬프를 저장하려면 null이 아닌 int unsgined를 직접 선택하세요.

예:

성별: utf8을 예로 사용 char(1) , 3단어 긴 바이트

#🎜🎜 # enum('male','female'); 내부적으로 숫자로 변환하여 저장, 변환 과정을 한 번 더 수행

tinyint(), 고정 길이 1바이트

# 🎜🎜 # 2. 충분히 사용하고 관대하게 사용하지 마십시오(예: smallint varchar(N))

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

나이를 예로 들면tinyint unsigned not null은 255세를 저장할 수 있으며 이 정도면 충분합니다. int를 사용하면 3바이트가 낭비됩니다.

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

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

이유: NULL은 색인 생성에 도움이 되지 않으며 특수 문자로 표시해야 합니다.

디스크에서 차지하는 공간은 실제로 더 크다(MySQL5.5에서는 null이 개선되었으나 여전히 쿼리가 불편함)

3. 인덱스 최적화 전략# 🎜🎜#

1. 색인 유형

~이름은 BTREE 인덱스인데, 광범위하게 사용되는 균형 트리이지만, 구체적인 구현 측면에서는 각 엔진이 조금씩 다릅니다. 예를 들어 엄밀히 말하면 NDB 엔진은 T-Tree를 사용합니다.

하지만 추상화합니다. 비트 추상. B-트리 시스템은 "정렬된 빠른 쿼리 구조"로 이해될 수 있습니다.

1.2 해시 인덱스

기본값은 메모리 테이블의 해시 인덱스이며, 해시의 이론적 쿼리 시간 복잡도는 O(1)입니다.

질문: 해시 검색이 매우 효율적이므로 해시 인덱스를 사용하면 어떨까요?

답변:

1. 해시 함수로 계산한 결과는 무작위입니다. 데이터가 디스크에 배치되면 기본 키를 id로 가정하면 id가 증가함에 따라 해당 id에 해당하는 행이 됩니다. 디스크 장소에서 무작위로 행동하십시오.

2. 범위 쿼리는 최적화할 수 없습니다.

3. 접두사 인덱스를 사용할 수 없습니다. 예를 들어 btree에서는 필드 열의 값이 "helloworld"이고 인덱스 쿼리 x=helloworld는 자연스럽게 인덱스를 사용할 수 있으며 x=hello도 인덱스를 사용할 수 있습니다. (왼쪽 접두사 색인).

4. 정렬을 최적화할 수 없습니다.

5. 행 백업이 필요합니다. 즉, 인덱스를 통해 데이터 위치를 얻으려면 테이블로 돌아가 데이터를 가져와야 합니다. Re t 2, BTREE 인덱스에 대한 일반적인 오해

2.1 다음과 같이 WHERE 조건에 인덱스를 추가합니다. 여기서 cat_id = 3이고 가격 & gt; 세 번째 열, 100위안 이상의 제품을 쿼리합니다.

오해: cat_id와 가격 모두에 색인을 추가하세요.

오류: cat_id 또는 가격 지수는 독립적인 지수이므로 하나만 사용할 수 있으며 동시에 하나만 사용할 수 있습니다.

2.2 여러 열(조인트 인덱스)에 인덱스를 생성한 후에는 어떤 열을 쿼리하든 인덱스가 작동합니다.

오해: 인덱스가 다중 열 인덱스에서 작동하려면 왼쪽 접두사 요구 사항을 충족해야 합니다.

인덱스(a, b, c)를 예로 들어 보겠습니다. (순서와 관련이 있음에 유의하세요.)

4. 인덱스 실험 MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진)

사용 색인(a, b, c) 사용 예를 들어, select * from t4 where c1=3 and c2 = 4 and c4> 5 and c3 = 2 인덱스는 무엇입니까:

Select * from T4 Where C1 = 3 and C2 = 4 and C4 & GT; 4)

5. 클러스터형 인덱스와 비클러스터형 인덱스

Myisam과 innodb 엔진, 인덱스 파일의 유사점과 차이점

Myisam: news.myd와 new.myi라는 두 파일로 구성됩니다. 인덱스 파일과 데이터 파일은 별개이며, 비클러스터형 인덱스라고 합니다. 기본 인덱스와 보조 인덱스 모두 물리적 행(디스크 상의 위치)을 가리킵니다. MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진)

innodb: 인덱스와 데이터가 함께 클러스터링되어 있으므로 클러스터형 인덱스입니다. 데이터 행은 innodb의 기본 인덱스 파일에 직접 저장되며 보조 인덱스는 기본 키 인덱스에 대한 참조를 가리킵니다.

참고: innodb의 경우:

1. 기본 키 인덱스는 인덱스 값을 저장하고 행 데이터를 리프에 저장합니다.

2. 기본 키가 없으면 고유 키가 기본 키로 사용됩니다.

3. 고유 키가 없으면 시스템은 기본 키로 내부 rowid를 생성합니다.

4. innodb와 마찬가지로 기본 키 인덱스 구조에는 기본 키 값과 행 데이터가 모두 저장되는 구조를 클러스터형 인덱스라고 합니다.

클러스터형 인덱스

장점: 기본 키를 기반으로 한 쿼리 항목이 상대적으로 적은 경우 행 백업이 필요하지 않습니다(데이터가 기본 키 노드 아래에 있음)

단점: 불규칙한 데이터가 삽입되는 경우, 페이지 분할이 자주 발생합니다

MySQL 빅데이터 쿼리 성능 최적화 튜토리얼(사진)

관련 기사