MySQL 데이터베이스 최적화 기술에 대한 인덱스 활용 기술 요약_MySQL

이 기사의 예에는 MySQL 데이터베이스 최적화 기술의 인덱스 사용이 요약되어 있습니다. 참고할 수 있도록 모든 사람과 공유하세요. 자세한 내용은 다음과 같습니다.

다음은 인덱스 최적화 기술을 더 자세히 분석하기 위해 이전 기사 "MySQL 데이터베이스 최적화 기술의 구성 기술 요약"을 따릅니다.

(7) 테이블 최적화

1. 적절한 데이터 엔진을 선택하세요

MyISAM: 대량의 읽기 작업에 적합한 테이블

InnoDB: 대량의 쓰기와 읽기에 적합한 테이블

2. 적절한 열 유형을 선택하세요

SELECT * FROM TB_TEST PROCEDURE ANALYSE()를 사용하여 이 테이블의 각 필드를 분석하고 열 유형 최적화를 위한 제안 제공

3. NULL 값을 저장하지 않는 열에는 NOT NULL을 사용하세요. 이는 색인을 생성하려는 열에 특히 중요합니다.

4. 적절한 색인 생성

5. 가변 길이 필드보다 빠른 고정 길이 필드를 사용하세요

(8) 색인 생성 원칙

1. 인덱스를 적절하게 사용하세요

테이블은 쿼리에서 하나의 인덱스만 사용할 수 있습니다. EXPLAIN 문을 사용하여 최적화 프로그램의 작동을 확인하세요

분석을 사용하면 최적화 프로그램이 인덱스 사용량에 대해 더 정확하게 예측할 수 있습니다.

2. 검색, 정렬, 그룹화 및 기타 작업과 관련된 데이터 열에 대해 인덱스를 생성해야 합니다.

3. 중복 데이터가 적은 데이터 열에 인덱스를 구축하는 것이 가장 좋습니다

예: 생일 열은 색인화할 수 있지만 성별 열은 색인화하면 안 됩니다

4. 더 짧은 값을 색인화해 보세요.

디스크 IO 작업을 줄이고 인덱스 버퍼에 더 많은 키 값을 수용할 수 있으며 적중률이 향상됩니다

긴 문자열을 색인화하는 경우 접두사 길이를 지정할 수 있습니다

5. 다중 열 인덱스의 올바른 사용

쿼리를 위해 여러 조건을 자주 조합해야 하는 경우 다중 열 인덱스를 사용해야 합니다(테이블은 하나의 쿼리에 하나의 인덱스만 사용할 수 있고, 하나만 사용하여 여러 단일 열 인덱스를 생성할 수 있기 때문입니다)

6. 가장 왼쪽 접두어를 최대한 활용하세요

즉, 다중 열 인덱스에서 열의 순서가 합리적으로 배열되어야 하며, 가장 많이 사용되는 열이 먼저 순위를 매겨야 합니다

7. 인덱스를 너무 많이 만들지 마세요

where, order by, group by에서 자주 사용되는 필드만 색인화하면 됩니다.

8. 느린 쿼리 로그를 사용하여 느린 쿼리 찾기(log-slow-queries, long_query_time)

(9) 인덱스를 최대한 활용

1. 동일한 데이터 유형의 데이터 열을 비교해 보세요

2. 비교식에서 인덱스 열을 최대한 독립적으로 만듭니다. WHERE mycol 62da62dc7afb8e3640a70f216cc8260b 예: a.id=b.id의 내부 조인 b에서 *를 선택하십시오.

EXPLAIN 분석 결과 매개변수에 대한 자세한 설명:

1.table: 테이블의 이름입니다.

2.type: 연결 작업 유형입니다.

시스템: 테이블에 레코드가 하나만 있습니다(실제 응용에서는 데이터가 하나만 있는 테이블은 거의 사용되지 않음)

const: 테이블에는 PRIMARY KEY 또는 UNIQUE 인덱스의 모든 부분을 상수 값과 비교할 때 사용되는 최대 하나의 일치 행이 있습니다.

예:

select * from song_lib where song_id=2

(song_id는 테이블의 기본 키입니다)

eq_ref: 이전 테이블의 각 행 조합에 대해 UNIQUE 또는 PRIMARY KEY 인덱스를 사용하여 테이블에서 행을 읽습니다.

예:

코드는 다음과 같습니다.

song_lib에서 *를 선택하고 a.singer_id=b.singer_id에서 singer_lib b 내부 조인

(b의 유형 값은 eq_ref입니다)

ref: 이전 테이블의 각 행 조합에 대해 UNIQUE 또는 PRIMARY KEY 이외의 인덱스를 사용하여 테이블에서 행을 읽습니다.

예:

코드는 다음과 같습니다.

song_lib에서 *를 선택하고 내부 조인 singer_lib b on a.singer_name=b.singer_name

그리고

코드는 다음과 같습니다.

select * from singer_lib b where singer_name='ccc'

(b.singer_name은 일반 인덱스이므로 b의 유형 값은 ref입니다.)

ref_or_null: 이 조인 유형은 ref와 비슷하지만 NULL 값이 포함된 행을 구체적으로 검색하기 위해 MySQL이 추가되었습니다.

예:

코드는 다음과 같습니다.

singer_name='ccc' 또는 singer_name이 null인 singer_lib에서 *를 선택하세요

index_merge: 이 조인 유형은 인덱스 병합 최적화 방법이 사용됨을 나타냅니다

키: MySQL에서 실제로 사용하는 인덱스 이름을 나타냅니다. 비어 있거나 NULL이면 MySQL은 인덱스를 사용하지 않습니다.

key_len: 인덱스에서 사용된 부분의 길이(바이트)입니다.

3.ref: ref 열은 테이블에서 행을 선택하기 위해 키와 함께 사용되는 열 또는 상수를 보여줍니다.

4.rows: MySQL이 올바른 결과를 찾기 전에 스캔해야 한다고 생각하는 레코드 수입니다. 분명히 여기서 이상적인 숫자는 1입니다.

5.추가: 여기에는 다양한 옵션이 나타날 수 있으며, 대부분은 쿼리에 부정적인 영향을 미칩니다. 일반적으로:

using where: where 조건이 사용됨을 나타냅니다

using filesort: 파일 정렬을 사용한다는 의미, 즉 order by 절을 사용하고, order by의 필드 인덱스를 사용하지 않으므로 추가적인 정렬 오버헤드가 필요합니다. 따라서 using filesort가 나타나는 경우입니다. 이는 정렬 효율이 매우 높다는 것을 의미하며, 힘 지수를 사용하는 등의 최적화가 필요합니다

더 많은 MySQL 관련 콘텐츠에 관심이 있는 독자는 이 사이트의 특별 주제인 "MySQL 인덱스 작업 기술 요약", "MySQL 로그 작업 기술 종합 모음", "MySQL 트랜잭션 작업 기술 요약"을 확인할 수 있습니다. , "MySQL 저장 프로시저 기술 종합 모음" , "MySQL 데이터베이스 잠금 관련 기술 요약" 및 "자주 사용되는 MySQL 함수 요약"

이 기사가 MySQL 데이터베이스를 계획하는 모든 사람에게 도움이 되기를 바랍니다.