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

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

MySQL 인덱스가 어떻게 동작하는지 궁금한데, 구체적으로 어떻게 하면 전체 테이블을 스캔하지 않고 요청된 데이터를 반환할 수 있을까요?

주제에서 벗어난 얘기인 건 알지만 자세히 설명해 줄 수 있는 사람이 있다면 정말 고맙겠습니다.

기본적으로 테이블의 색인은 책의 색인과 같이 작동합니다(이 이름이 유래되었습니다).

데이터베이스에 대한 책이 있고 스토리지에 대한 정보를 찾고 싶다고 가정해 보겠습니다.색인(목차 등 다른 보조 자료가 없는 경우)이 없으면 주제를 찾을 때까지 페이지를 하나씩 검토해야 합니다(즉,full table scan한편, 인덱스는 키워드 목록을 가지고 있기 때문에 인덱스를 참조하면,storage는 113-120,231 및 354페이지에 기재되어 있습니다.그런 다음 검색 없이 이러한 페이지로 직접 이동할 수 있습니다(색인이 있는 검색으로 다소 빠릅니다).

물론 지수가 얼마나 유용한지는 여러 가지에 따라 달라진다. 위의 직유법을 사용하는 몇 가지 예도 있다.

• 데이터베이스에 책이 있고 단어 "dexed"를 색인화했다면 1-59, 61-199, 292-400페이지에 언급되어 있는 것을 알 수 있을 것입니다.이 경우 색인은 별로 도움이 되지 않으며 페이지를 하나씩 살펴보는 것이 더 빠를 수 있습니다(데이터베이스에서는 "선택성이 낮음").
• 10페이지짜리 책이라면 색인을 만드는 것은 말이 안 된다. 왜냐하면 당신은 10페이지짜리 책 앞에 5페이지짜리 색인이 붙게 될지도 모르기 때문이다. 그냥 10페이지를 스캔하고 그것을 끝내라.
• 색인은 또한 유용해야 합니다. 일반적으로 색인화할 포인트가 없습니다(예: 페이지당 문자 "L"의 빈도).

먼저 인덱스는 원하는 결과를 얻기 위해 전체 테이블을 검색하지 않는 방법입니다.

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

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-Tree를 사용할 때 해시 인덱스의 사용을 "에뮬레이트"할 수 있습니다.필드에 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. 프로세스에 단계를 추가하면 속도가 느려지는 것이 아니라 빨라질 수 있습니다.이것은, 데이터·프로세서·프로세서·프로세서·프로세서·프로세서·프로세서입니다.SELECT두 단계로 분할하여 첫 번째 단계는 새로 생성된 메모리 내 테이블에 값을 저장하도록 한 다음 두 번째 테이블에 더 무거운 쿼리를 실행합니다.

MySQL에는 다른 인덱스도 있지만, B+Tree는 지금까지 가장 많이 사용되고 있고 해시도 알 수 있지만 다른 인덱스는 MySQL 문서에서 찾을 수 있습니다.

"High Performance MySQL" 책을 읽어보시길 강력히 추천합니다. 위의 답변은 인덱스에 관한 장에 근거하고 있습니다.

기본적으로 인덱스는 순서대로 정렬된 모든 키의 맵입니다.목록을 순서대로 나열하면 모든 키를 확인하는 대신 다음과 같은 작업을 수행할 수 있습니다.

1: 목록 중앙으로 이동 - 원하는 것보다 높습니까, 낮습니까?

2: 높으면 중간과 아래쪽 중간 지점, 낮으면 중간과 위쪽 중간 지점까지 이동합니다.

3: 높은가요, 낮은가요?다시 중간점으로 점프하는 등

이 논리를 사용하면 모든 항목을 확인하는 대신 약 7단계로 정렬된 목록에서 요소를 찾을 수 있습니다.

분명 복잡성이 있지만, 기본적인 생각을 할 수 있습니다.

다음 링크를 참조하십시오.http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

그들이 일하는 방식은 하나의 SO 포스트에서 다루기에는 너무 광범위한 주제이다.

여기 제가 본 인덱스에 대한 가장 좋은 설명 중 하나가 있습니다.안타깝게도 MySQL이 아닌 SQL Server용입니다.그 둘이 얼마나 비슷한지는 모르겠지만...

MySQL InnoDB에는 두 가지 유형의 인덱스가 있습니다.

1. 클러스터된 인덱스라고 하는 기본 키입니다.인덱스 키워드는 B+Tree 리프 노드에 실제 레코드 데이터와 함께 저장됩니다.

2. 클러스터되지 않은 인덱스의 보조 키입니다.이러한 인덱스는 기본 키의 키워드와 자체 인덱스 키워드만 B+Tree 리프 노드에 저장합니다.따라서 secondary 인덱스에서 검색할 때 먼저 primary 키인덱스 키워드를 찾고 primary 키인 B+Tree를 스캔하여 실제 데이터 레코드를 찾습니다.이렇게 하면 기본 인덱스 검색에 비해 보조 인덱스가 느려집니다.다만,select열은 모두 보조 인덱스에 있으므로 기본 인덱스 B+Tree를 다시 조회할 필요가 없습니다.이것을 커버링 인덱스라고 합니다.

인덱싱에 대한 자세한 내용은 이 비디오를 참조하십시오.

단순 인덱싱 테이블에 고유한 색인을 만들 수 있습니다.고유 인덱스는 두 행이 동일한 인덱스 값을 가질 수 없음을 의미합니다.다음은 테이블에 색인을 만드는 구문입니다.

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

하나 이상의 열을 사용하여 인덱스를 생성할 수 있습니다.를 들어, ''를 만들 수 .tutorials_tbl튜토리얼_작성자

CREATE UNIQUE INDEX AUTHOR_INDEX
ON tutorials_tbl (tutorial_author)

테이블에 단순 인덱스를 생성할 수 있습니다.단순한 인덱스를 작성하려면 쿼리에서 UNIQUIRE 키워드를 생략하기만 하면 됩니다.단순 인덱스는 테이블에서 중복된 값을 허용합니다.

열의 값을 내림차순으로 인덱싱하려면 열 이름 뒤에 예약된 단어 DESC를 추가할 수 있습니다.

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

답변 목록에 시각적인 표현을 추가합니다.

MySQL은 추가 간접 계층을 사용합니다. 보조 인덱스 레코드는 기본 인덱스 레코드를 가리키고 기본 인덱스 자체는 Disk 행 위치를 유지합니다.행 간격띄우기가 변경되면 기본 색인만 업데이트하면 됩니다.

주의: 디스크 데이터 구조는 그림에서는 평평하지만 실제로는 B+ 트리입니다.

출처: 링크

제 2센트를 더하고 싶어요.저는 데이터베이스 전문가와는 거리가 멀지만 최근에 이 주제에 대해 조금 읽었습니다. ELI5를 시험해 볼 수 있을 정도로요.자, 여기 메이맨의 설명이 있습니다.

---

인덱스는 테이블의 미니 미러와 비슷하며 연관 배열과 비슷합니다.일치하는 키를 사용하여 입력한 경우 하나의 "명령"으로 해당 행으로 이동할 수 있습니다.

그러나 인덱스/배열이 없는 경우 쿼리 인터프리터는 for-loop을 사용하여 모든 행을 통과하고 일치하는지 확인해야 합니다(전체 테이블 검색).

인덱스를 보유하면 콘텐츠를 보다 빠르게 검색할 수 있는 "업사이드" 대신 추가 스토리지(미니 미러의 경우)의 "다운사이드"가 있습니다.

(db 엔진에 따라) 프라이머리, 외부 키 또는 고유 키를 생성하면 각 인덱스가 자동으로 설정됩니다.같은 원리가 그 열쇠가 작동하는 이유와 방법입니다.

책, 아마도 소설, 읽을 것이 많은 두꺼운 책이 있다고 가정해 봅시다. 그래서 많은 단어들이 있습니다.자, 가정하건대, 소설에서 최소한 한 번은 사용된 단어들로만 구성된 두 개의 사전을 가져왔다고 가정해 봅시다.두 사전의 모든 단어는 일반적인 알파벳 순서로 저장됩니다.가상사전 A에서는 소설에 인쇄된 횟수만큼 단어를 1회만 인쇄하고, 가상사전 B에서는 단어를 인쇄한다.두 사전 모두 단어가 알파벳 순으로 정렬되어 있다는 것을 기억하십시오.소설을 읽다가 어느 순간 막혔을 때 그 단어의 의미를 그 가설 사전에서 찾을 필요가 있다.어떻게 할 건데?분명 당신은 그 단어의 의미를 찾기 위해 몇 단계 안에 그 단어로 뛰어갈 것이다. 오히려 소설 속의 단어 하나하나의 의미를 처음부터 그 성가신 단어에 도달할 때까지 찾을 것이다.

SQL에서 인덱스는 이렇게 작동합니다.사전 A를 Primary INDEX로, 사전 B를 KEY/SECondary INDEX로, 단어의 의미를 QUERY/SELECT 문으로 파악합니다.인덱스는 매우 빠른 속도로 데이터를 가져오는 데 도움이 됩니다.인덱스가 없으면 데이터를 처음부터 찾아야 합니다. 불필요하게 많은 시간이 소요되는 작업입니다.

인덱스 및 유형에 대한 자세한 내용은 여기를 참조하십시오.

언급URL : https://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work