MySQL Advanced 13 - 인덱스를 통한 SQL 최적화

1. 개요

이진 트리 ——> 인덱스 파일: 효율성 log2N

10회 검색: 2의 10승. 1024개의 레코드.

인덱스로 인한 오버헤드

데이터 파일(설치 디렉터리 아래 데이터 디렉터리)을 살펴보면

.frm 세 개의 파일이 있는데, table

.myd : 데이터를 표현

.myi : 인덱스된 파일을 표현

인덱스로 인한 문제 : 삽입, 업데이트, 삭제의 효율성을 높인다

자주 업데이트되는 필드는 인덱스 생성에 적합하지 않습니다.

고유성이 낮은 필드는 인덱스 생성에 적합하지 않습니다. 예를 들어, 개인의 성별이 남성 또는 여성

이고 다음 조건을 만족하는 경우에만 인덱스가 생성됩니다

1. where 조건에서 자주 사용해야 합니다.

2. 이 필드는 너무 자주 변경되지 않습니다.

2. 인덱스 사용 시나리오

1. where 조건을 만족하는 레코드를 빠르게 찾습니다.

2. 후보군을 빠르게 결정합니다. where 조건이 여러 개의 인덱스 필드를 사용하는 경우, MySQL은 조건을 충족하지 않는 레코드를 최대한 빨리 제거하기 위해 후보 세트의 크기를 최소화할 수 있는 인덱스 사용에 우선 순위를 부여합니다.

3. 테이블에 여러 필드로 구성된 결합 색인이 있는 경우, 레코드 검색 시 결합 색인의 가장 왼쪽 접두어 일치 필드도 자동으로 색인으로 사용되어 검색 속도가 빨라집니다.

예: 하나의 테이블에 3개의 인덱스가 생성되면 (c1, c2, c3), (c1), (c1, c2), (c1, c2, c3)으로 구성된 조인트 인덱스는 다음과 같습니다. 모두 인덱스로 사용되며, (c2, c3)은 인덱스로 사용되지 않으며, (c1, c3)은 실제로 c1 인덱스만 사용합니다.

4. 여러 테이블을 조인할 때 인덱스가 사용됩니다(조인에 참여하는 필드가 해당 테이블에 인덱스된 경우).

5. 필드가 인덱스된 경우 정렬 또는 이 필드에 대한 그룹 작업을 수행할 때 MySQL은 인덱스를 사용합니다.

3. EXPLAIN 분석을 통한 비효율적인 SQL 실행 방안

예:

mysql> explain select * from taxgrouptaxes\G  
*************************** 1. row ***************************  
           id: 1  
  select_type: SIMPLE  
        table: taxgrouptaxes  
         type: ALL  
possible_keys: NULL  
          key: NULL  
      key_len: NULL  
          ref: NULL  
         rows: 1  
        Extra:

select_type : SELECT 타입을 나타내며,

공용 값은

SIMPLE: 테이블 조인이나 하위 쿼리를 사용하지 않는 간단한 테이블

PRIMARY: 기본 쿼리, 외부 쿼리

union: UNION에서 두 번째 또는 후속 쿼리문

SUBQUERY: 서브쿼리의 첫 번째 선택

table: 결과 집합을 출력하는 테이블

유형은 MYSQL이 테이블에 있음을 나타냅니다. 필요한 행. 또는 액세스 유형이라고 합니다.

일반적인 유형은 다음과 같습니다. 🎜>
const,system

null

위에서 아래로 성능은 최악부터 최고까지 다양합니다.

1: 유형=전체 테이블 스캔.

2: 유형=인덱스 인덱스 스캔

3: 유형=탄지 인덱스 범위 스캔. 7cb9091baf3e2c81106f6565e75575c8 >= between

4와 같은 작업에 일반적으로 사용됩니다. type=ref 는 고유 인덱스 스캔 또는 고유 인덱스의 접두사 스캔을 사용합니다.

5: type=eq_ref는 ref와 유사합니다. 차이점은 사용된 인덱스가 각 인덱스 키 값에 대해 테이블의 레코드 하나만 일치한다는 것입니다.

6: type=const/system 단일 테이블에는 최대 하나의 일치하는 행이 있으며 쿼리 속도가 매우 빠릅니다.

7: type=null MYSQL은 테이블이나 인덱스에 액세스할 필요가 없습니다. 결과를 직접 받아보실 수 있습니다.

possible_keys: 쿼리에 사용될 수 있는 인덱스를 나타냅니다.

key: 실제 사용된 인덱스를 나타냅니다.

key_len: 사용된 인덱스 필드의 길이

rows: 스캔 라인 수

추가: 실행에 대한 설명 및 스캔. 표시에 맞지 않는 다른 열을 포함하는 것은 실행 계획에 중요합니다.

4. 쇼 프로필을 통해 SQL 분석

1. 먼저 MySQL이 쇼 프로필을 지원하는지 확인


2. Open profile at session level


3. 실행 후 showprofiles 문을 통해 현재 SQL의 queryID를 확인할 수 있습니다.

4. 쿼리 ID에 show profile 사용

위 내용은 MySQL Advanced 13 - Optimizing SQL through indexing의 내용이며, 자세한 내용은 PHP 중국어 홈페이지(www. .php.cn)!

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)