[mysql] MySQL 인덱스는 어떻게 작동합니까?

가장 먼저 알아야 할 것은 인덱스가 원하는 결과를 얻기 위해 전체 테이블을 스캔하지 않는 방법이라는 것입니다.

다양한 종류의 인덱스가 있으며 스토리지 계층에서 구현되므로 인덱스간에 표준이 없으며 사용중인 스토리지 엔진에 따라 다릅니다.

InnoDB 및 B + Tree 인덱스

InnoDB의 경우 가장 일반적인 인덱스 유형은 요소를 정렬 된 순서대로 저장하는 B + Tree 기반 인덱스입니다. 또한 인덱스 된 값을 얻기 위해 실제 테이블에 액세스 할 필요가 없으므로 쿼리 반환 속도가 빨라집니다.

이 인덱스 유형에 대한 “문제”는 인덱스를 사용하기 위해 가장 왼쪽의 값을 쿼리해야한다는 것입니다. 따라서 인덱스에 last_name 및 first_name과 같은 두 개의 열이있는 경우 이러한 필드를 쿼리하는 순서가 중요합니다 .

따라서 다음 표를 참고하십시오.

CREATE TABLE person (
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    INDEX (last_name, first_name)
);

이 쿼리는 인덱스를 활용합니다.

SELECT last_name, first_name FROM person
WHERE last_name = "John" AND first_name LIKE "J%"

그러나 다음 중 하나는

SELECT last_name, first_name FROM person WHERE first_name = "Constantine"

당신이 쿼리하기 때문에 first_name먼저 열을 하고 인덱스에서 가장 왼쪽 열이 아니기 때문입니다.

이 마지막 예는 훨씬 더 나쁩니다.

SELECT last_name, first_name FROM person WHERE first_name LIKE "%Constantine"

이제 인덱스에서 가장 오른쪽 필드의 가장 오른쪽 부분을 비교하고 있습니다.

해시 인덱스

불행히도 메모리 백엔드 만 지원하는 다른 인덱스 유형입니다. 그것은의 번개 빠르지 만 작업이 좋아하는 당신이 그것을 사용할 수 없음을 의미합니다 전체 조회에 대한 유용 >, <또는 LIKE.

메모리 백엔드에서만 작동하므로 자주 사용하지 않을 것입니다. 내가 지금 생각할 수있는 주요 사례는 다른 선택의 결과 집합으로 메모리에 임시 테이블을 만들고 해시 인덱스를 사용 하여이 임시 테이블에서 다른 많은 선택을 수행하는 경우입니다.

큰 VARCHAR필드 가있는 경우 B- 트리를 사용할 때 다른 열을 만들고 큰 값의 해시를 저장하여 해시 인덱스 사용을 “에뮬레이션”할 수 있습니다. 필드에 URL을 저장하고 있으며 값이 상당히 크다고 가정 해 봅시다. 또한 정수 필드를 생성 url_hash하고 해시 함수 CRC32또는 다른 해시 함수를 사용하여 URL을 삽입 할 때 해시 할 수 있습니다. 그런 다음이 값을 쿼리해야 할 때 다음과 같은 작업을 수행 할 수 있습니다.

SELECT url FROM url_table WHERE url_hash=CRC32("http://gnu.org");

위 예제의 문제점은 CRC32함수가 아주 작은 해시를 생성하므로 해시 된 값에서 많은 충돌이 발생한다는 것입니다. 정확한 값이 필요한 경우 다음을 수행하여이 문제를 해결할 수 있습니다.

SELECT url FROM url_table
WHERE url_hash=CRC32("http://gnu.org") AND url="http://gnu.org";

충돌 횟수가 크더라도 반복되는 해시에 대한 두 번째 비교 (문자열 1) 만 수행 할 경우에도 여전히 해싱 할 가치가 있습니다.

불행히도이 기술을 사용하면 url필드 를 비교하기 위해 여전히 테이블을 쳐야합니다 .

마무리

최적화에 대해 이야기하고 싶을 때마다 고려해야 할 몇 가지 사실 :

1. 정수 비교는 문자열 비교보다 훨씬 빠릅니다. 의 해시 인덱스 에뮬레이션에 대한 예를 통해 설명 할 수 있습니다 InnoDB.

2. 프로세스에 단계를 추가하면 속도가 느려지지 않고 빨라질 수 있습니다. a SELECT를 두 단계로 나눠서 최적화 하여 첫 번째 값을 새로 만든 인 메모리 테이블에 저장 한 다음이 두 번째 테이블에서 더 많은 쿼리를 실행할 수 있다는 사실을 알 수 있습니다 .

MySQL에는 다른 인덱스도 있지만 B + Tree는 가장 많이 사용되며 해시는 잘 알고 있지만 MySQL 설명서 에서 다른 것을 찾을 수 있다고 생각합니다. .

“High Performance MySQL”책을 읽는 것이 좋습니다. 위의 답변은 인덱스에 대한 장을 기반으로 한 것입니다.

답변

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author DESC)