Mysql 인덱스 유형 및 장단점에 대한 자세한 설명

인덱스특수한 파일입니다(InnoDB 데이터 테이블의 인덱스는 테이블 공간의 일부입니다), 그들은 데이터 테이블에 있는 모든 기록에 대한 참조 포인터를 포함하고 있습니다.
주의:
[1]인덱스는 모든 것을 할 수 없습니다! 인덱스는 데이터 검색 작업을 가속화할 수 있지만, 데이터 수정 작업을 늦추게 합니다. 데이터 기록을 수정할 때마다 인덱스는 새로 고쳐져야 합니다. 이 결함을 최소화하기 위해 많은 SQL 명령에는 DELAY_KEY_WRITE 항목이 있습니다. 이 옵션의 역할은 MySQL이 명령을 통해 새로운 기록을 삽입하거나 기존 기록을 수정할 때마다 즉시 인덱스를 새로 고쳐지지 않도록 임시로 제한하며, 인덱스의 새로 고침은 모든 기록이 삽입된 후에만 수행됩니다./수정이 완료된 후에 수행합니다. 많은 새로운 기록을 특정 데이터 테이블에 삽입해야 하는 경우, DELAY_KEY_WRITE 옵션의 역할이 매우 명확합니다.
[2또한, 인덱스는 하드 디스크에서 상당한 공간을 차지합니다. 따라서, 가장 자주 쿼리하고 가장 자주 정렬되는 데이터 열에만 인덱스를 만들어야 합니다. 주의하십시오, 특정 데이터 열이 많은 중복 내용을 포함하고 있을 경우, 인덱스를 만드는 것은 실질적인 효과가 크지 않습니다.
이론적으로, 데이터 테이블의 각 필드에 별도로 인덱스를 만들 수 있지만, MySQL은 동일한 데이터 테이블의 인덱스 총 수를 제한합니다.16개.

1. InnoDB 데이터 테이블의 인덱스

MyISAM 데이터 테이블에 비해, 인덱스는 InnoDB 데이터에 더 중요합니다. InnoDB 데이터 테이블에서 인덱스는 InnoDB 데이터 테이블에 더 중요합니다. 인덱스는 데이터 검색 기록 시에만 작용하는 것이 아니라, 데이터 행 수준 잠금 메커니즘의 기본이 됩니다. '데이터 행 수준 잠금'이란, 트랜잭션 작업 실행 중에 처리 중인 개별 기록을 잠금하고, 다른 사용자가 접근하지 못하게 하는 것을 의미합니다. 이 잠금은 SELECT...LOCK IN SHARE MODE, SELECT...FOR UPDATE 명령 및 INSERT, UPDATE 및 DELETE 명령에 영향을 미칩니다.
효율성을 고려하여, InnoDB 데이터 테이블의 데이터 행 수준 잠금은 실제로 그들의 인덱스에서 발생하며, 데이터 테이블 자체에서는 아닙니다. 명백히, 데이터 행 수준 잠금 메커니즘은 잠금할 수 있는 적절한 인덱스가 있는 경우에만 효과를 발휘합니다.

2. 제한

WHERE 조건문에 불 등호(WHERE column != ...)가 포함되어 있을 때, MySQL은 인덱스를 사용할 수 없습니다.
WHERE 절의 쿼리 조건에서 함수(WHERE DAY(column) = …)을 사용하는 경우에도 MySQL은 인덱스를 사용할 수 없습니다.
JOIN 작업에서(여러 데이터 테이블에서 데이터를 추출해야 할 때) MySQL은 주키와 외래키의 데이터 타입이 같을 때에만 인덱스를 사용할 수 있습니다.
WHERE 절의 쿼리 조건에서 LIKE와 REGEXP 비교 연산자를 사용하는 경우, MySQL은 검색 템플릿의 첫 번째 문자가 와일드 카드가 아닌 경우에만 인덱스를 사용할 수 있습니다. 예를 들어, 쿼리 조건이 LIKE 'abc%'인 경우 MySQL은 인덱스를 사용합니다; LIKE '%abc'인 경우 MySQL은 인덱스를 사용하지 않습니다.
ORDER BY 작업에서 MySQL은 정렬 조건이 쿼리 조건 표현식이 아닌 경우에만 인덱스를 사용합니다.(그러나 여러 데이터 테이블 쿼리에서는, 인덱스가 사용 가능하지만, 이 인덱스가 ORDER BY를 가속화하는 데는 거의 아무런 도움이 되지 않습니다.)
데이터 열에 많은 중복된 값이 포함되어 있으면, 인덱스를 생성했을 때도 좋은 효과를 얻지 못할 수 있습니다. 예를 들어, 데이터 열에 "0"과 같은 값만 포함된 경우./1" 또는 "Y/N"와 같은 값이면, 인덱스를 생성할 필요가 없습니다.　

일반 인덱스, 유일 인덱스 및 메인 인덱스

1일반 인덱스

일반 인덱스(키워드 KEY나 INDEX로 정의된 인덱스)의 유일한 임무는 데이터에 대한 액세스 속도를 빠르게 만드는 것입니다. 따라서, 가장 자주 쿼리 조건(WHERE column = …)이나 정렬 조건(ORDER BY column)에 등장하는 데이터 열에 인덱스를 생성해야 합니다. 가능하다면, 가장 정렬되고 촉적된 데이터 열(예: 정수 타입의 데이터 열)을 선택하여 인덱스를 생성해야 합니다.

2유일 인덱스
일반 인덱스는 인덱스에 포함된 데이터 열이 중복된 값을 포함할 수 있습니다. 예를 들어, 사람이 같은 이름을 가질 수 있으므로, 동일한 이름이 동일한 "직원 개인 정보" 데이터 테이블에서 두 번이나 더 많이 나타날 수 있습니다.
데이터 열이 서로 다른 값만 포함할 것이라고 확정된다면, 이 데이터 열에 인덱스를 생성할 때 UNIQUE 키워드를 사용하여 유일한 인덱스로 정의해야 합니다. 이렇게 한 이유는 첫째, MySQL이 이 인덱스를 관리하는 데 더 간편해지고, 이 인덱스가 더 효율적으로 됩니다. 둘째, MySQL은 새로운 레코드가 데이터 테이블에 삽입될 때마다 새로운 레코드의 이 필드의 값이 이미 어떤 레코드의 이 필드에 존재하는지 자동으로 확인합니다. 만약 그렇다면, MySQL은 그 새로운 레코드를 삽입하지 않습니다. 즉, 유일한 인덱스는 데이터 레코드의 유일성을 보장합니다. 사실, 많은 경우에, 유일한 인덱스를 생성하는 목적은 액세스 속도를 높이기보다는 중복된 데이터가 발생하지 않도록 하는 것입니다.

3. 주 인덱스

앞에서 여러 번 강조한 바와 같이: 주키 필드에 인덱스를 생성해야 합니다. 이 인덱스는 '주 인덱스'라고 불립니다. 주 인덱스와 유일 인덱스의 유일한 차이는: 정의할 때 사용하는 키워드가 PRIMARY 대신 UNIQUE라는 것입니다.

4. 외래 키 인덱스

만약 특정 외래 키 필드에 외래 키 제약 조건을 정의하면, MySQL은 효율적으로 외래 키 제약 조건을 관리하고 사용하기 위해 내부 인덱스를 정의합니다.

5. 복합 인덱스

인덱스는 여러 데이터 컬럼을 다루bilir, 예를 들어 INDEX(columnA, columnB) 인덱스와 같이. 이러한 인덱스의 특징은 MySQL이 이러한 인덱스 중 하나를 선택적으로 사용할 수 있다는 것입니다. 쿼리 작업이 columnA 데이터 컬럼에 대한 인덱스를 사용하면 INDEX(columnA, columnB)와 같은 복합 인덱스를 사용할 수 있습니다. 그러나, 이러한 사용은 복합 인덱스에서 앞쪽에 있는 데이터 컬럼 조합에만 적용됩니다. 예를 들어, INDEX(A, B, C)는 A나 (A, B)의 인덱스로 사용될 수 있지만, B, C나 (B, C)의 인덱스로 사용될 수는 없습니다.

6. 인덱스의 길이

CHAR와 VARCHAR 타입의 데이터 컬럼에 인덱스를 정의할 때, 인덱스의 길이를 주어진 문자 개수로 제한할 수 있습니다(이 숫자는 필드가 허용하는 최대 문자 개수보다 작아야 합니다). 이렇게 하면 크기가 작고 검색 속도가 빠른 인덱스 파일을 생성할 수 있습니다. 대부분의 애플리케이션에서 데이터베이스의 문자 데이터는 다양한 이름으로 구성되어 있으며, 인덱스의 길이를10~15글자가 이미 검색 범위를 매우 적은 데이터 레코드로 축소할 수 있도록 충분합니다.
BLOB와 TEXT 타입의 데이터 컬럼에 인덱스를 생성할 때, 인덱스의 길이를 제한해야 합니다; MySQL이 허용하는 최대 인덱스 길이는255글자.
전체 인덱스

　　 텍스트 필드에 대한 일반 인덱스는 필드 내용의 앞쪽에 나타나는 문자(즉 필드 내용의 첫 번째 문자)를 검색하는 작업을 가속화할 수 있습니다. 필드에 여러 단어나 여러 단어로 구성된 큰 텍스트가 저장되어 있다면 일반 인덱스는 거의 아무 의미가 없습니다. 이러한 검색은 대부분 LIKE %word% 형식으로 나타나며, MySQL에게는 복잡하며, 처리할 데이터의 양이 많다면 응답 시간이 매우 길어질 수 있습니다.
이러한 상황은 전체 텍스트 인덱스(full)-text index)는 이러한 유형의 인덱스를 생성할 때, MySQL은 텍스트에서 등장하는 모든 단어를 목록으로 만들고, 쿼리 작업은 이 목록을 기반으로 관련 데이터 레코드를 검색합니다. 전체 텍스트 인덱스는 테이블과 함께 생성할 수도 있으며, 필요할 때에만 사용할 수도 있습니다.

이 명령어를 추가합니다:
ALTER TABLE tablename ADD FULLTEXT(column1, column2)

전체 텍스트 인덱스가 있는 경우, SELECT 쿼리 명령어를 사용하여 특정 단어를 포함하는 데이터 레코드를 검색할 수 있습니다. 이러한 쿼리 명령어의 기본 문법은 다음과 같습니다:
SELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST('word1′, 'word2′, 'word3′)

이 명령어는 column1와 column2필드에 word가 있습니다.1와 word2와 word3의 데이터 레코드 전체를 검색합니다.

비고InnoDB 테이블은 전체 텍스트 인덱스를 지원하지 않습니다.

쿼리와 인덱스 최적화

　　　　　 데이터베이스에 충분히 많은 테스트 데이터가 있을 때만, 그 성능 테스트 결과는 실제 참고 가치가 있습니다. 테스트 데이터베이스에 수백 개의 데이터 레코드가 있을 때, 이들은 대체로 첫 번째 쿼리 명령어가 실행된 후에 모두 메모리에 로드되어, 이후의 쿼리 명령어들은 인덱스를 사용하지 않고도 매우 빠르게 실행됩니다. 데이터베이스의 레코드가1000개의 데이터가 있을 때, 데이터의 총량도 MySQL 서버의 메모리 총량을 초과했을 때, 데이터베이스의 성능 테스트 결과가 의미가 있습니다.

　　　　　 데이터 열에 인덱스를 생성해야 할 데이터열을 결정하는 것이 불확실할 때, 사람들은 often EXPLAIN SELECT 명령어에서 도움을 받습니다. 이는 단순히 SELECT 명령어에 EXPLAIN 키워드를 접두어로 추가하는 것뿐입니다. 이 키워드를 통해 MySQL은 해당 SELECT 명령어를 실행하는 대신 분석을 수행합니다. MySQL은 쿼리 실행 과정과 사용된 인덱스(있을 경우) 등의 정보를 테이블 형식으로 나열합니다.
EXPLAIN 명령어의 출력 결과에서,1데이터베이스에서 읽어들인 테이블 이름은 열로, 읽어들인 순서대로 정렬됩니다. type 열은 이 테이블과 다른 테이블 간의 관계(JOIN)를 지정합니다. 여러 가지 유형의 관계 중에서 가장 효율적인 것은 system입니다. 그 다음으로는 const, eq_ref, ref, range, index, 그리고 All(All은: 상위 테이블의 각 레코드에 대해 이 테이블의 모든 레코드가 읽어야 하는 경우를 의미합니다. 이 경우 일반적으로 인덱스를 사용하여 피할 수 있습니다.)입니다.

possible_keys 데이터 열은 MySQL이 데이터 레코드를 검색할 때 사용할 수 있는 각 인덱스를 제공합니다. key 데이터 열은 MySQL이 실제로 사용한 인덱스로, 이 인덱스의 길이는 byte 단위로 key_len 데이터 열에 제공됩니다. 예를 들어, INTEGER 데이터 열의 인덱스는 이 바이트 길이로 계산됩니다.4복합 인덱스를 사용하면 key_len 데이터 열에서 MySQL이 사용한 인덱스의 일부를 볼 수 있습니다. 일반적으로 key_len 데이터 열의 값이 작을수록 좋습니다. (뜻은 더 빠르다는 것입니다).
ref 데이터 열은 관계에서 다른 데이터 테이블의 데이터 열 이름을 제공합니다. row 데이터 열은 MySQL이 이 쿼리를 실행할 때 이 데이터 테이블에서 읽을 예상되는 데이터 행의 개수입니다. row 데이터 열에 있는 모든 숫자의 곱은 이 쿼리가 처리해야 할 조합의 양을 약간 알려줍니다.
마지막으로, extra 데이터 열은 JOIN 작업과 관련된 더 많은 정보를 제공합니다. 예를 들어, MySQL이 이 쿼리를 실행할 때 임시 데이터 테이블을 생성해야 한다면, extra 열에서 using temporary와 같은 텍스트를 볼 수 있습니다.

이것이 이 문서의 전체 내용입니다. 여러분의 학습에 도움이 되길 바라며, 모두들이呐喊 강의를 많이 지지해 주시길 바랍니다.

언급: 이 문서의 내용은 인터넷에서 가져왔으며, 저작권자가 소유하고 있으며, 인터넷 사용자가 자발적으로 기여하고 자체적으로 업로드한 내용입니다. 이 사이트는 소유권을 가지지 않으며, 인공 편집 처리를 하지 않았으며, 관련 법적 책임도 부담하지 않습니다. 저작권 침해가 의심되는 내용이 있다면, notice#w로 이메일을 보내 주세요.3codebox.com에 이메일을 보내서 신고해 주세요. (#을 @으로 변경하십시오.) 관련 증거를 제공하고, 실제로 확인되면, 이 사이트는 즉시 저작권 침해 내용을 삭제합니다.