MySQL 인덱스 최적화를 사용하는 방법

보통 각 책의 처음 몇 페이지는 목차이고, 마지막 몇 페이지에는 키워드 색인이 있습니다.

데이터베이스의 경우 시스템 테이블(예: sysobjects 등)이 디렉터리이며 대상 필드의 인덱스는 책 뒤의 키워드 인덱스와 같습니다.

데이터베이스에서 디렉터리(데이터 사전)와 인덱스의 차이점은 디렉터리가 세로이고 인덱스가 가로입니다.

1. 인덱스 기능에 영향을 미치는 요소

차별(검색 비율)

데이터베이스가 인덱스에 대한 통계 정보를 수집하지 않으면 최적화 프로그램을 시작할 수 없습니다. . 단계별로 진행하고 전체 테이블 스캔을 통과하여 쿼리를 실행할 수 있습니다. 따라서 새로 생성된 인덱스는 통계를 다시 실행해야 합니다. 그렇지 않으면 인덱스가 유효하지 않게 됩니다.

예를 들어, "1", "2", "3"의 세 가지 값이 있는 COL1 필드가 있는 테이블 TABLE1이 있고 통계를 실행한 결과는 데이터베이스에 다양한 값을 알려주는 것입니다. TABLE1의 데이터에 있는 COL1 필드의 값 비율입니다. 표현은 다음과 같습니다:

“1” – 12%,

“2” – 66%,

“3” – 22%.

필드 COL2 값이 있고 데이터 비율이 다음과 같다고 가정합니다.

"A" - 50%;

"B" - 50%.

다음 쿼리 문 1:

select * from TABLE1 where COL1 = "1" and COL2 = "A",

데이터베이스 최적화 프로그램은 COL1 필드의 인덱스에 우선순위를 부여하여 테이블에서 데이터를 찾습니다. COL1 인덱스는 12%의 작은 범위 내에서 결과 집합을 빠르게 찾을 수 있습니다. 반대로 쿼리 문 2:

select * from TABLE1 where COL1 = "2" 및 COL2 = "A"의 경우

데이터베이스는 COL2의 인덱스에 우선 순위를 부여합니다. 왜냐하면 문 2의 쿼리 조건의 경우 COL2의 색인은 더 나은 차별성을 갖습니다.

위에서 볼 수 있듯이 데이터베이스 최적화 프로그램은 일반적으로 차별성이 높은 인덱스에 우선 순위를 부여합니다(쿼리 조건의 경우 선택되는 인덱스는 조건에 따라 다를 수 있음).

데이터베이스의 데이터가 변경되므로 특정 시점에 수집된 통계 정보가 일정 시간이 지나면 오래된 정보가 되거나 데이터베이스 최적화 프로그램을 오도하여 운영 성능이 저하될 수도 있습니다. 따라서 인덱스를 처음 생성할 때 통계를 실행해야 하는 것 외에도 테이블의 데이터가 변경될 때에도 통계를 실행해야 합니다. 경험: 테이블의 데이터 양이 10% 변경되면 통계를 다시 실행해야 합니다.

2. 집계

범위 스캔

테이블 크기:

작은 테이블

중형 및 대형 테이블

매우 큰 테이블

비즈니스 유형

OLTP 및 OLAP

함수 및 인덱스

함수, 문장과 같습니다. . .

하위 문자열(col_name,1, 3) 대 하위 문자열(col_name, 3, 3)

like 'QQQ% vs. like '%QQQ'

인덱스 오버헤드

성능 도구

양날의 검

Index pair 삽입 작업의 영향(Oracle)

삽입 작업에 대한 인덱스의 영향(MySQL)

성능에 대한 인덱스 및 활성화 요소의 영향 비교

인덱스 요약

인덱스를 사용하여 중요한 데이터에 효율적으로 액세스할 수 있습니다. 그러나 각 인덱스가 데이터베이스 업데이트에 추가 오버헤드를 가져온다는 점을 알아야 합니다. 이는 비효율적인 인덱스가 데이터베이스에 재앙을 가져올 수 있음을 의미합니다.

데이터베이스의 경우 핵심 데이터를 읽는 데 집중하고 가장 효율적인 액세스 경로를 제공해야 합니다. 이를 위한 기본 전략은 인덱스를 구축하는 것입니다. 인덱스는 효율적인 액세스를 제공하지만 추가적인 시스템 오버헤드도 발생합니다. 오버헤드는 디스크 공간 오버헤드와 프로세서 오버헤드로 구분됩니다. 다음으로 프로세서 오버헤드에 대해 논의합니다. 테이블에 레코드가 삽입되거나 삭제될 때마다 테이블의 모든 인덱스가 그에 따라 조정되어야 합니다. 이 조정은 인덱스된 필드가 업데이트될 때마다 발생합니다. 예를 들어, 인덱싱되지 않은 테이블에 데이터를 삽입하는 데 100단위의 시간이 걸리는 경우 각 추가 인덱스는 100~250단위의 시간을 추가합니다. 흥미롭게도 인덱스를 유지하는 데 드는 오버헤드는 단순 트리거의 오버헤드와 대략 동일합니다.

인덱싱의 최전선에서 가장 인기 있는 정보를 소개합니다. 이 정보는 일반적으로 참고할 가치가 있다고 생각하여 나열됩니다.

1. 쿼리가 합리적인 시간에 종료되어야 하는 경우. 업데이트 작업 속도가 느려지고 추가 공간을 소비하므로 인덱스를 추가하지 않는 것이 좋습니다. 때로는 여러 쿼리를 포함하는 큰 인덱스가 있을 수 있습니다.

1. 카디널리티가 큰 열은 인덱싱에 매우 적합합니다.

3. 관리 오버헤드를 고려하여 인덱스에 5개 이상의 열을 사용하지 마세요.

4. 다중 열 인덱스의 경우 쿼리에서 가장 많이 참조되는 열을 정의 앞에 배치합니다.

5. 기존 인덱스와 유사한 인덱스를 추가하지 마세요. 이로 인해 최적화 프로그램에 더 많은 작업이 발생하고 업데이트 작업 속도가 느려집니다. 대신 추가 열을 포함하도록 기존 인덱스를 수정해야 합니다. 예를 들어, 테이블(c1,c2)에 인덱스 i1이 있다고 가정합니다. 쿼리에 "wherec2=?"가 사용되었으므로 (c2)에 인덱스 i2를 만듭니다. 하지만 이 유사한 인덱스는 아무것도 추가하지 않으며 단지 i1에 대한 중복일 뿐이며 이제 추가 오버헤드가 됩니다.

6 테이블이 읽기 전용이고 많은 행을 포함하는 경우 인덱스를 정의하고 CREATE INDEX의 INCLUDE 절을 사용하여 인덱스에 쿼리에서 참조된 모든 열이 포함되도록 할 수 있습니다(INCLUDE 절에 포함된 열은 포함되지 않음). 포함) 인덱스의 일부가 아니지만 단순히 추가 데이터 가져오기를 피하기 위해 인덱스 페이지의 일부로 저장됩니다.

데이터 웨어하우스(쿼리 시스템 데이터베이스)의 경우 더 많은 인덱스를 설정할 수 있습니다(인덱스와 데이터의 비율은 1:1 가능).

인덱스 사용 여부를 결정할 때 검색 비율에 집중할 수 있습니다. 즉, 인덱스의 유효성을 판단하는 기준은 키 값을 고유한 조건으로 사용하여 검색된 데이터의 비율입니다. 백분율이 낮을수록 인덱스가 더 효율적입니다. 이 결론은 디스크 액세스의 상대적 성능과 같은 몇 가지 가정을 기반으로 합니다.

데이터는 블록을 통해 동작하기 때문에 인덱스 키 값과 관련된 레코드의 물리적 위치가 인접해 있는지 여부도 중요합니다. 인덱스가 생성된 후, 인덱스 키가 가리키는 레코드가 테이블 전체에 분산되어 있다면, 이들 레코드가 테이블 내에서 작은 비중을 차지하더라도 전체에 분산되어 있기 때문에 인덱스의 성능이 크게 저하됩니다. 디스크.

또한 함수 및 유형 변환으로 인해 인덱스가 유효하지 않게 될 수 있다는 점도 주목할 가치가 있습니다.

위 내용은 MySQL 인덱스 최적화를 사용하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!