MYSQL 인덱스 모범 사례

현명한 선택이셨습니다

인덱스를 이해하는 것은 개발자와 DBA 모두에게 매우 중요합니다

잘못된 인덱스는 제품 문제의 상당 부분을 담당합니다

인덱싱은 그렇지 않습니다 고급 문제

MySQL 인덱스 개요

인덱스 이해

애플리케이션에 가장 적합한 인덱스 생성

MySQL의 한계 수용

인덱스에 대한 간략한 소개

인덱스의 용도

데이터베이스에서 데이터 읽기 가속화

제약조건 적용(고유 인덱스 UNIQUE, 외래 키 FOREIGN KEY)

쿼리 페이지는 인덱스 없이도 정상적으로 실행될 수 있습니다

그러나 시간이 오래 걸릴 수 있습니다

들어본 적이 있는 인덱스 유형

BTREE 인덱스 – 기본 mysql의 인덱스 유형

RTREE 인덱스 – MyISAM에서만 지원, GIS에서 사용

HASH 인덱스 – MEMORY, NDB 지원

BITMAP 인덱스 – MySQL은 지원하지 않음

FULLTEXT 인덱스 – MyISAM, Innodb(MySQL 5.6 이상에서 지원)

BTREE와 유사한 인덱스 패밀리

accelable에는 다양한 구현이

있습니다. 동일한 속성을 공유합니다. 운영 중

하드 디스크보다 메모리가 삶을 더 좋게 만듭니다

B+ 트리는 일반적으로 하드 디스크 저장에 사용됩니다

데이터는 리프 노드에 저장됩니다

B+ 트리 예시

MyISAM, Innodb 인덱스 비교

MyISAM

데이터 포인터는 데이터 파일의 물리적 위치를 가리킵니다

모든 인덱스는 동일합니다(물리적 위치를 가리킴))

Innodb

기본 키 인덱스(명시적 또는 암시적) - 데이터를 인덱스의 리프 노드에 직접 저장하는 대신 포인터

보조 인덱스 – 기본 키 인덱스의 값을 데이터 포인터로 저장

BTREE 인덱스는 어떤 작업에 사용할 수 있나요?

KEY=로 모든 레코드 쿼리 5(포인트 질의)

KEY>5의 모든 레코드 질의(열림 및 닫힘)

5

해당 없음 : 마지막 KEY를 쿼리합니다. 숫자가 0인 모든 레코드

범위 쿼리 연산으로 정의할 수 없기 때문에

문자 인덱스

이(및 숫자)는 아니요입니다. 다릅니다... 정말

collation은 "AAAA"

와 같은 문자열

에 대해 정의된 정렬 규칙입니다. LIKE 쿼리는 특수 범위 쿼리입니다.

LIKE " ABC%"는 다음을 의미합니다.

"ABC[최소값]"

LIKE "%ABC"는 사용할 수 없습니다. 인덱스 쿼리

결합 인덱스

는 다음과 같이 정렬되어 첫 번째 열, 두 번째 열, 세 번째 열 등을 비교합니다. 예:

KEY( col1,col2,col3)

(1,2,3)

각 레벨마다 별도의 BTREE 인덱스 대신 하나의 BTREE 인덱스 사용

인덱스 오버헤드

인덱스는 비용이 많이 들므로 중복 인덱스를 추가하지 마세요

대부분의 경우 새 인덱스를 추가하는 것보다 인덱스를 확장하는 것이 좋습니다

쓰기 - 인덱스 업데이트는 종종 데이터베이스 쓰기 작업입니다.

읽기의 주요 오버헤드 - 하드 디스크 및 메모리 오버헤드 공간이 필요하며 쿼리 최적화에는 추가 오버헤드가 필요합니다.

인덱스 비용의 영향

긴 기본 키 인덱스(Innodb) – 사용 해당하는 모든 보조 인덱스가 더 길어지고 느려짐

“무작위” 기본 키 인덱스(Innodb) – 삽입으로 인해 대규모 페이지 분할이 발생함

더 긴 인덱스는 일반적으로 느림

무작위 순서로 삽입되는 인덱스 – SHA1('password')

저해상도 인덱스는 열등함 – 성별 필드 기반 인덱스

관련 인덱스가 저렴함 – insert_time은 자동 증가 ID와 관련됩니다

Innodb 테이블 인덱스

데이터는 기본 키로 집계됩니다

가장 적합한 필드를 기본 키로 선택

예를 들어 댓글 테이블 – (POST_ID,COMMENT_ID)는 기본 키로 좋은 선택이므로 단일 게시물에 대한 댓글을 함께 클러스터링

하거나 단일 BIGINT(필드)

로 "패킹"할 수 있습니다.

인덱스의 모든

KEY(A)에 암시적으로 첨부된 기본 키는 본질적으로 인덱스를 덮는 KEY(A,ID)

이므로 정렬에 유용합니다

MySQL이 인덱스를 사용하는 방법

쿼리

정렬

데이터 읽기 방지(인덱스 읽기만)

기타 특수 최적화

쿼리에 인덱스 사용

SELECT * FROM EMPLOYEES WHERELAST_NAME="Smith"

일반적인 인덱스입니다 KEY(LAST_NAME)

복합 인덱스를 사용할 수 있습니다

SELECT * FROM EMPLOYEES WHERELAST_NAME="Smith" AND DEPT="회계"

색인 KEY(DEPT,LAST_NAME)가 사용됩니다

복합 색인이 더 복잡합니다

색인(A,B,C) - 필드 순서 문제

다음 상황에서는 Index가 쿼리(전체 조건)에 사용됩니다.

A>5

A=5 AND B>6

A=5 AND B=6 AND C=7

A=5 AND B IN (2,3) AND C>5

다음 조건은 인덱스를 사용하지 않습니다

B>5 – 조건 없음 B 필드 앞의 A

B=6 AND C=7 - 조건의 B 및 C 필드 앞에 A가 없습니다

다음 상황에서 인덱스의 일부를 사용하세요

A>5 AND B=2 - 필드 A의 첫 번째 범위 쿼리로 인해 인덱스에서 필드 A의 일부만 사용됩니다.

A=5 AND B>6 AND C=2 - 범위 쿼리 필드 B, 결과적으로 인덱스에서 필드 A의 일부만 사용되고 B 필드

MySQL 옵티마이저의 첫 번째 규칙

복합 인덱스에서 MySQL은 다음을 발견할 때 중지됩니다. return query (,BETWEEN) 나머지 부분(인덱스)의 사용을 중단하고 IN(...)의 "범위 쿼리"를 사용하여 오른쪽의 인덱스(추가 부분)를 계속 사용합니다.

사용된 인덱스로 정렬

SELECT * FROM PLAYERS ORDER BY SCOREDESC LIMIT 10

인덱스를 사용합니다. KEY(SCORE)

인덱스를 사용하지 않으면 수행됩니다. 매우 비용이 많이 드는 "파일 정렬" 작업(외부 정렬)

자주 사용됨 쿼리용 인덱스 결합

SELECT * FROM PLAYERS WHERE COUNTRY=“US” ORDER BY SCORE DESC LIMIT 10

최선의 선택은 KEY(COUNTRY,SCORE)

효율적인 정렬입니다. 공동 인덱스

가 더욱 제한됩니다!

KEY(A,B)

다음 상황에서는 정렬에 인덱스를 사용합니다.

ORDER BY A - 인덱스의 첫 번째 필드를 정렬합니다.

A=5 ORDER BY B - 첫 번째 필드에서 포인트 쿼리를 수행하고 두 번째 필드를 정렬합니다.

ORDER BY A DESC, B DESC - 두 필드를 동일한 순서로 정렬

A>5 ORDER BY A - 첫 번째 필드에 대해 범위 쿼리를 수행하고 첫 번째 필드를 정렬

다음 상황에서는 정렬에 인덱스를 사용하지 않습니다.

ORDER BY B - 두 번째 필드 정렬(첫 번째 필드는 사용되지 않음)

A>5 ORDER BY B - 수행 첫 번째 필드에 대한 범위 쿼리, 두 번째 필드 정렬

A IN(1,2) ORDER BY B - 첫 번째 필드에 대한 IN 쿼리 수행 및 두 번째 필드 정렬

ORDER BY A ASC, B DESC - 두 필드에 대해 IN 쿼리 수행 서로 다른 순서로 정렬

MySQL은 인덱스 정렬 규칙을 사용합니다

두 필드를 서로 다른 순서로 정렬할 수 없습니다

만 ORDER BY 부분에 포함되지 않은 필드에 포인트 쿼리를 사용할 수 있습니다. (=) – 이 경우 IN()도 작동하지 않습니다.

데이터 읽기 방지(인덱스 읽기만)

“Covering index” – 여기서는 인덱스의 종류가 아닌 특정 쿼리에 적합한 인덱스를 의미합니다

인덱스만 읽고 데이터는 읽지 않음

SELECT STATUS FROM ORDERS WHERECUSTOMER_ID=123

KEY(CUSTOMER_ID,STATUS)

인덱스는 일반적으로 데이터 자체보다 작습니다

(인덱스) 읽기는 더 규칙적입니다. 데이터 포인터 읽기는 일반적으로 무작위

최소/최대 최적화

인덱스는 MIN()/MAX()와 같은 통계 함수를 최적화하는 데 도움이 될 수 있지만 다음만 포함합니다.

SELECT MAX(ID) FROM TBL;

SELECT MAX( SALARY) FROM EMPLOYEEGROUP BY DEPT_ID

는 KEY(DEPT_ID,SALARY)

"그룹별 인덱스 사용"

의 이점을 누릴 수 있습니다.

공동 테이블 쿼리에서 인덱스 사용

MySQL은 공동 테이블 쿼리에 "중첩 루프"를 사용합니다

SELECT * FROM POSTS,COMMENTS WHEREAAUTHOR="Peter" AND COMMENTS.POST_ID=POSTS.ID

POSTS 테이블을 스캔하여 복합 조건이 있는 모든 게시물을 쿼리합니다.

게시물을 반복하고 COMMENTS 테이블에서 각 게시물의 모든 댓글을 찾습니다.

각 게시물에 대해 상위 인덱스를 사용하는 것이 매우 중요합니다. 연관 테이블(연관 필드) 중요

인덱스는 쿼리되는 필드에만 필요합니다. POSTS.ID 필드의 인덱스는 이 쿼리에서 사용되지 않습니다.

모든 인덱스를 다시 디자인하세요. 좋지 않습니다 조인 쿼리

다중 인덱스 사용

MySQL은 2개 이상의 인덱스를 사용할 수 있습니다

"인덱스 병합"

SELECT * FROM TBL WHERE A= 5 AND B=6 – 인덱스 KEY(A)와 KEY(B)를 각각 사용할 수 있습니다

인덱스 KEY(A,B)가 더 나은 선택

SELECT * FROM TBL WHERE A= 5 OR B=6 – 두 개의 인덱스가 동시에 사용됩니다.

이 쿼리에서는 인덱스 KEY(A,B)를 사용할 수 없습니다.

접두사 인덱스

마지막 왼쪽을 추가할 수 있습니다. 접두사 색인

ALTER TABLE TITLE ADD KEY(TITLE(20));

BLOB/TEXT 유형 필드를 색인화해야 함

공간 사용량을 크게 줄일 수 있음

인덱스를 가리는 데 사용할 수 없습니다

접두사 길이 선택이 문제가 됩니다

접두사 길이 선택

접두사가 충분히 구별되어야 합니다

구별을 비교하세요 접두사 및 고유 필드 값

mysql> select count(distinct(title)) total,count(distinct(left(title,10))) p10,count(distinct(left(title, 20))) p20 제목에서;

세트의 1개 행(44.19초)

이상값 확인

많은 레코드 사용이 없는지 확인 동일한 접두사

가장 많이 사용되는 Titlemysql> select count(*) cnt, title tl from title group by tl order by cnt desclimit 3;

3행 세트(27.49초)

가장 많이 사용되는 제목 접두사 mysql> 제목 그룹에서 count(*) cnt, left(title,20) tl 선택 by tl order by cnt desclimit 3;

세트의 3개 행(33.23초)

MySQL은 사용할 인덱스를 어떻게 선택합니까?

각 쿼리에 대한 동적 선택 - 쿼리 텍스트의 상수가 중요합니다

쿼리할 행 수를 평가합니다. 특정 인덱스에 대해 테이블을 "다이빙"합니다

( dive)은 가능한 행이 아닙니다. 통계에 "카디널리티"를 사용하세요. 이는 ANALYZE TABLE을 수행할 때 업데이트됩니다.

인덱싱에 대한 추가 옵션

스캔된 행 수를 최소화하는 것뿐만 아니라

기타 많은 휴리스틱(시도) 및 해킹 – Innodb에서는 기본 키가 매우 중요합니다.

Covered index의 이점

전체 테이블 스캔이 모두 동일하므로 더 빠릅니다(이 문장은 명확하지 않습니다)

정렬을 위해 인덱스를 사용할 수도 있습니다

메모

MYSQL에서 실제로 사용하는 실행 계획을 확인하세요

상수와 상수에 따라 동적으로 변경될 수 있다는 점에 유의하세요. 데이터

EXPLAIN 사용

EXPLAIN은 MYSQL이

mysql> 제목 그룹에서 생산연도별로 select max(season_nr)를 쿼리하는 방법을 확인하는 좋은 도구입니다.

http://dev.mysql.com/doc/refm...

실제 쿼리는 실행 계획과 다를 수 있음을 기억하세요

1개 행 세트(0.01초)

MySQL 설명 101

"type" 최고에서 최악으로 정렬: – system,const,eq_ref,ref,range,index,ALL

"행" 보기 - 값이 클수록 쿼리 속도가 느려짐

"key_len" 확인 - 인덱스의 어느 부분이 실제로 사용되는지 표시

"추가" 보기

인덱스 사용 - 좋음

파일 정렬 사용, 임시 사용 - 나쁨

인덱싱 전략

성능이 중요한 쿼리 세트 인덱싱 – 하나하나 살펴보는 대신 전체적인 보기 하나

모든 쿼리 조건과 조인 테이블 조건에는 인덱스를 사용하는 것이 가장 좋습니다. 적어도 가장 차별화되는 부분은

입니다. 일반적으로 가능하면 인덱스를 확장하고, 새로 생성하는 대신 인덱스를 확장합니다. index

수정 시 성능에 미치는 영향을 확인하는 것을 잊지 마세요

인덱스 전략의 예

더 많은 쿼리를 지원할 수 있는 순서로 인덱스 생성

SELECT * FROM TBL WHERE A=5 AND B=6

SELECT * FROM TBL WHERE A>5 AND B=6– KEY(B,A)는 두 쿼리 모두에 더 나은 선택입니다.

모두 입력 인덱스 시작 부분에 포인트 쿼리인 필드

성능이 중요하지 않은 쿼리에는 인덱스를 추가하지 마세요. 인덱스가 너무 많으면 MYSQL 속도가 느려집니다.

트릭 # 1: 열거 범위

KEY (A,B)

SELECT * FROM TBL WHERE A BETWEEN 2AND 4 AND B=5

는 인덱스 필드 부분 중 첫 번째 부분만 사용합니다

SELECT * FROM TBL WHERE A IN (2,3,4) ANDB=5

인덱스의 두 필드 부분 모두 사용

트릭 #2: 가짜 조건 추가

KEY (GENDER,CITY)

SELECT * FROM PEOPLE WHERE CITY=“NEWYORK”

색인이 전혀 사용되지 않습니다

SELECT * FROM PEOPLE WHERE GENDER IN(" M","F") AND CITY="NEW YORK"

색인을 사용합니다

이 트릭은 식별력이 낮은 필드에 유용할 수 있습니다.

성별, 상태 사용 , 부울 유형 등

트릭 #3: 가상 및 실제 파일 정렬

KEY(A,B)

SELECT * FROM TBL WHERE A IN (1,2) ORDER BY B LIMIT 5;

인덱스를 사용하여 정렬할 수 없습니다

(SELECT FROM TBL WHERE A=1 ORDER BY B LIMIT 5) UNION ALL (SELECT FROM TBL WHERE A=2 ORDER BY B LIMIT 5 ) ORDER BY B LIMIT 5;

은 인덱스를 사용하며 "filesort"는 10줄 이하의 레코드에만 사용됩니다.