MySQL 비용 기반 최적화기 및 색인 선택

MySQL Query Optimizer를 선택하여 인덱스를 선택하는 핵심 기준은 비용 기반 비용 모델 (CBO)이며, 이는 다양한 실행 경로의 비용을 평가하여 최적 솔루션을 결정합니다. 1. Optimizer는 줄 스캔, 페이지 읽기, 테이블로 돌아와야하는지, 정렬 사용 또는 임시 테이블과 같은 요소를 고려합니다. 2. 선택되지 않은 인덱스의 일반적인 이유는 다음과 같습니다. 고르지 않은 데이터 배포 또는 부정확 한 통계 정보, 잘못된 기타 추정치; 테이블을 뒷받침하는 비용이 너무 높으며 Optimizer는 전체 테이블 스캔이 더 효율적이라고 생각합니다. 쿼리 쓰기는 함수 사용, 선행 퍼지 매칭 또는 일부 또는 조건이 색인이없는 등 색인을 유효하지 않습니다. 3. 정기적으로 분석 테이블을 실행하고, 낮은 분열 필드에서 인덱싱을 피하고, 테이블로 돌아가서 SQL을 합리적으로 작성하고, 실행 계획을 분석하기 위해 설명을 사용하여 커버리지 인덱스를 설정하는 것이 좋습니다. Optimizer 의사 결정 프로세스는 설명 분석 또는 Optimizer Trace를 통해 볼 수 있으며 필요한 경우 인덱스를 강제하는 데 힘을 사용하여 디자인 및 통계를 먼저 최적화해야합니다.

인덱스를 선택할 때 MySQL의 쿼리 옵티마이저는 주로 비용 기반 비용 모델 (CBO) 에 의존합니다. 다양한 실행 계획의 "비용"을 평가 한 다음 최적이라고 생각하는 솔루션을 선택합니다. 그러나 때로는 인덱스가 있지만 MySQL에는 필요하지 않으며 전체 테이블 스캔을 선택할 수도 있습니다. 이에 대한 이유는 실제로 CBO가 비용을 추정하는 방법과 밀접한 관련이 있습니다.

비용 기반 최적화기 란 무엇입니까?

MySQL의 Optimizer는 단순히 인덱스가 있는지 여부를 단순히 보는 것이 아니라 어떤 인덱스가 "보다 비용 효율적"인지 확인합니다. 다음과 같은 일부 추정 데이터를 통해 가능한 각 실행 경로의 비용을 계산합니다.

• 몇 줄의 데이터가 스캔됩니까?
• 몇 페이지를 읽어야합니까?
• 테이블을 반환해야합니까?
• 정렬 또는 임시 테이블이 사용됩니까?

이들은 최종 실행 계획에 영향을 미칩니다. 다시 말해, 인덱스를 추가하더라도 Optimizer는 계산을 통해 인덱스를 통과하는 데 속도가 느리기 때문에 유용하지 않을 수 있습니다 .

인덱스가 선택되지 않은 이유는 무엇입니까? 일반적인 이유

1. 고르지 않은 데이터 배포 또는 부정확 한 통계 정보

MySQL은 저장 엔진이 제공하는 통계를 사용하여 쿼리 비용을 추정합니다. 예를 들어, InnoDB는 인덱스의 카디널리티를 유지합니다. 이 값이 부정확 한 경우 Optimizer가 잘못 판단 할 수 있습니다.

예를 들어:
성별 분야 (남성/여성 가치 만)에 대한 색인이 있습니다. Optimizer 가이 인덱스의 선택성이 너무 열악하다는 것을 알면 전체 테이블을 직접 스캔하는 것이 좋습니다.

제안:

• 통계를 업데이트하려면 ANALYZE TABLE 주기적으로 실행하십시오.
• 낮은 정화 필드를 색인화하지 마십시오.

2. Optimizer는 테이블로의 복귀 가격이 너무 높다고 추정합니다.

일부 쿼리는 인덱스에 도달했지만 실제 데이터를 얻으려면 많은 양의 테이블 복귀가 필요합니다. 이 시점에서 Optimizer는이 인덱스를 사용하여 포기하고 클러스터 된 인덱스 (1 차 키 인덱스)를 통해 직접 스캔 할 수 있습니다.

예를 들어:

 연령> 30 인 사용자 중에서 선택하십시오.

age 색인화했지만 조건을 충족시키는 많은 데이터가있는 경우 Optimizer는 여러 번 테이블로 돌아 가야한다는 것을 알게되므로 기본 키를 직접 스캔하고 빠르게 오는 것이 좋습니다.

제안:

• 종종 특정 필드 만 찾는 경우 테이블을 뒷받침하지 않도록 오버레이 인덱스를 작성하는 것을 고려할 수 있습니다.
• 범위 쿼리의 경우 데이터 볼륨이 너무 큰지에주의하십시오.

3. 인덱스 자체는 현재 쿼리에 적합하지 않습니다.

때로는 색인을 생성하지만 쿼리가 사용될 수 없도록 작성됩니다. 예를 들어:

• 함수 또는 표현식 사용 : WHERE YEAR(create_time) = 2023
• 와일드 카드 이전의 퍼지 매칭 : WHERE name LIKE '%Tom'
• 쿼리 조건이 사용되거나 그 중 하나는 색인이 없습니다.

이러한 종류의 상황은 지수를 무효화하고 자연스럽게 Optimizer 후보 목록에 들어 가지 않을 것입니다.

제안:

• 쿼리 문을 작성할 때 필드에서 작동하지 않도록하십시오.
• 더 EXPLAIN 사용하여 실행 계획을 확인하고 색인이 실제로 사용되는지 확인하십시오.

Optimizer의 의사 결정 과정을 보는 방법은 무엇입니까?

MySQL이 다음 방법으로 실행 계획을 선택하는 방법을 배울 수 있습니다.

• 실행 계획을 보려면 EXPLAIN 또는 EXPLAIN ANALYZE (MySQL 8.0)을 사용하십시오.
• Optimizer Trace 기능을 활성화하고 Optimizer의 내부 결정 프로세스를 참조하십시오.

 set implicizer_trace = "enabled = on";
선택하다 ... ; - query select * from_schema.optimizer_trace;

이런 식으로, 우리는 최적화가 평가하는 실행 경로의 수, 각 경로의 예상 비용 및 기타 세부 사항에 대한 세부 사항을 볼 수 있습니다.

팁 : 색인을 사용하도록 강요?

인덱스가 더 좋지 만 Optimizer가 선택되지 않았다고 확신하면 FORCE INDEX 사용하도록 강요 할 수 있습니다.

 * 30 세 이상인 사용자 Force Index (IDX_AGE)를 선택하십시오.

그러나 이것이 최적화 판단을 우회하고 반드시 더 나은 것은 아닙니다. 장기적으로 설계 및 통계 정보에서 문제를 해결하는 것이 좋습니다 .

기본적으로 그게 다야. CBO는 똑똑하지만 데이터 정확도와 SQL 쓰기가 친숙한 지에도 불구하고 데이터 정확도에도 의존합니다. 인덱스가 적용되지 않으면 MySQL을 의심하기 위해 서두르지 마십시오. 먼저 생략했는지 확인하십시오.

위 내용은 MySQL 비용 기반 최적화기 및 색인 선택의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!