MySQL index_MySQL의 종류와 장단점에 대한 자세한 설명

인덱스 는 특수 파일입니다(InnoDB 데이터 테이블의 인덱스는 테이블 공간의 필수 부분입니다). 여기에는 데이터 테이블의 모든 레코드에 대한 참조 포인터가 포함되어 있습니다.
참고:
[1] 인덱스는 전능하지 않다! 인덱스는 데이터 검색 작업 속도를 높일 수 있지만 데이터 수정 작업은 더 느리게 만듭니다. 데이터 레코드가 수정될 때마다 인덱스를 새로 고쳐야 합니다. 특정 프로그램에서 이러한 단점을 보완하기 위해 많은 SQL 명령에는 DELAY_KEY_WRITE 항목이 있습니다. 이 옵션의 기능은 각각의 새로운 레코드가 삽입되고 각 기존 레코드가 이 명령에 의해 수정된 직후 MySQL이 인덱스를 새로 고치는 것을 일시적으로 방지하는 것입니다. 인덱스 새로 고침은 모든 레코드가 삽입/수정될 때까지 기다립니다. 많은 새로운 레코드를 데이터 테이블에 삽입해야 하는 상황에서는 DELAY_KEY_WRITE 옵션의 역할이 매우 분명해집니다.
[2] 또한 인덱스도 하드디스크에서 상당한 공간을 차지합니다. 따라서 가장 자주 쿼리되고 가장 자주 정렬되는 데이터 열만 인덱싱되어야 합니다. 데이터 열에 중복된 콘텐츠가 많이 포함되어 있으면 인덱싱해도 실질적인 효과가 크지 않습니다.
이론적으로는 데이터 테이블의 각 필드에 대한 인덱스를 구축하는 것이 가능하지만 MySQL은 동일한 데이터 테이블의 총 인덱스 수를 16개로 제한합니다.

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

MyISAM 데이터 테이블에 비해 InnoDB 데이터에서는 인덱스가 훨씬 더 중요합니다. InnoDB 데이터 테이블에서 인덱스는 InnoDB 데이터 테이블에 비해 훨씬 더 중요합니다. InnoDB 데이터 테이블에서 인덱스는 데이터 레코드를 검색하는 역할을 할 뿐만 아니라 데이터 행 수준 잠금 메커니즘의 기초이기도 합니다. "데이터 행 수준 잠금"은 트랜잭션 작업 실행 중에 처리되는 개별 레코드를 잠가서 다른 사용자가 액세스할 수 없도록 하는 것을 의미합니다. 이 잠금은 SELECT...LOCK IN SHARE MODE, SELECT...FOR UPDATE 명령, INSERT, UPDATE 및 DELETE 명령에 영향을 주지만 이에 국한되지는 않습니다.
효율성상의 이유로 InnoDB 데이터 테이블의 행 수준 잠금은 실제로 데이터 테이블 자체가 아닌 해당 인덱스에서 발생합니다. 분명히 데이터 행 수준 잠금 메커니즘은 관련 데이터 테이블에 잠금에 적합한 인덱스가 있는 경우에만 효과적일 수 있습니다.

제한사항

WEHERE 절의 쿼리 조건에 부등호(WHERE coloum != ...)가 있으면 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 열 = ...)이나 정렬 조건(ORDER BY 열)에서 가장 자주 나타나는 데이터 열에 대해서만 인덱스를 생성해야 합니다. 가능하면 가장 깔끔하고 컴팩트한 데이터가 포함된 데이터 열(예: 정수형 데이터 열)을 선택하여 인덱스를 생성해야 합니다.

2. 고유 인덱스
일반 인덱스를 사용하면 인덱스된 데이터 열에 중복 값이 ​​포함될 수 있습니다. 예를 들어, 사람들은 동일한 이름을 가질 수 있으므로 동일한 "직원 프로필" 데이터 테이블에 동일한 이름이 두 번 이상 나타날 수 있습니다.
특정 데이터 열에 서로 다른 값만 포함될 것이라고 판단할 수 있는 경우 해당 데이터 열에 대한 인덱스를 생성할 때 UNIQUE 키워드를 사용하여 이를 고유 인덱스로 정의해야 합니다. 이것의 이점은 다음과 같습니다. 첫째, MySQL의 이 인덱스 관리가 단순화되고 인덱스가 더욱 효율적이 됩니다. 둘째, MySQL은 새 레코드가 데이터 테이블에 삽입될 때 새 레코드에서 이 필드의 값을 자동으로 확인합니다. 이 필드는 이미 레코드에 나타났습니다. 그렇다면 MySQL은 새 레코드 삽입을 거부합니다. 즉, 고유 인덱스는 데이터 레코드의 고유성을 보장할 수 있습니다. 실제로 고유 인덱스를 생성하는 목적은 액세스 속도를 높이는 것이 아니라 단지 데이터 중복을 피하기 위한 경우가 많습니다.

3. 주요지수

이전에도 여러 번 강조한 바 있습니다. 기본 키 필드에 대해 인덱스를 만들어야 합니다. 이 인덱스를 소위 "기본 인덱스"라고 합니다. 기본 인덱스와 고유 인덱스의 유일한 차이점은 전자를 정의할 때 사용되는 키워드가 UNIQUE 대신 PRIMARY라는 것입니다.

4. 외래키 인덱스

외래 키 필드에 대해 외래 키 제약 조건이 정의된 경우 MySQL은 가장 효율적인 방법으로 외래 키 제약 조건을 관리하고 사용할 수 있도록 내부 인덱스를 정의합니다.

5. 종합지수

인덱스(열A, 열B) 인덱스와 같이 인덱스는 여러 데이터 열을 포함할 수 있습니다. 이 인덱스의 특징은 MySQL이 그러한 인덱스를 선택적으로 사용할 수 있다는 것이다. 쿼리 작업에서 컬럼A 데이터 열의 인덱스만 사용해야 하는 경우 복합 인덱스 INDEX(columnA, 컬럼B)를 사용할 수 있습니다. 그러나 이 사용법은 복합 인덱스에서 1순위에 있는 데이터 열의 조합에만 적용됩니다. 예를 들어 INDEX(A, B, C)는 A 또는 (A, B)의 인덱스로 사용할 수 있지만 B, C 또는 (B, C)의 인덱스로는 사용할 수 없습니다.

6. 인덱스 길이

CHAR 및 VARCHAR 유형 데이터 열에 대한 인덱스를 정의할 때 인덱스 길이를 지정된 문자 수로 제한할 수 있습니다(이 숫자는 이 필드에 허용되는 최대 문자 수보다 작아야 합니다). 이 방법의 장점은 크기가 더 작고 검색 속도가 빠른 인덱스 파일을 생성할 수 있다는 것입니다. 대부분의 애플리케이션에서 데이터베이스의 문자열 데이터는 대부분 다양한 이름을 기반으로 합니다. 색인 길이를 10~15자로 설정하면 검색 범위를 몇 가지 데이터 레코드로 좁힐 수 있습니다.
BLOB 및 TEXT 유형의 데이터 열에 대한 인덱스를 생성할 때 인덱스 길이는 제한되어야 하며 MySQL에서 허용하는 최대 인덱스 길이는 255자입니다.
전문 색인

텍스트 필드의 일반 인덱스는 필드 콘텐츠 앞에 나타나는 문자열(즉, 필드 콘텐츠 시작 부분의 문자)에 대해서만 검색 작업 속도를 높일 수 있습니다. 필드가 여러 단어 또는 심지어 여러 단어로 구성된 더 큰 텍스트 조각을 저장하는 경우 일반 인덱스는 거의 사용되지 않습니다. 이런 종류의 검색은 LIKE %word% 형식으로 나타나는 경우가 많으며 이는 MySQL에서는 처리해야 할 데이터 양이 많을 경우 응답 시간이 매우 길어집니다.
여기서는 전체 텍스트 색인이 유용할 수 있습니다. 이러한 유형의 인덱스를 생성할 때 MySQL은 텍스트에 나타나는 모든 단어의 목록을 생성하고 쿼리 작업은 이 목록을 기반으로 관련 데이터 레코드를 검색합니다. 전체 텍스트 인덱스는 데이터 테이블과 함께 생성하거나 나중에 필요할 때 사용할 수 있습니다

다음 명령을 추가하세요:
ALTER TABLE 테이블명 ADD FULLTEXT(열1, 열2)

전체 텍스트 인덱스를 사용하면 SELECT 쿼리 명령을 사용하여 주어진 단어가 하나 이상 포함된 데이터 레코드를 검색할 수 있습니다. 다음은 이러한 유형의 쿼리 명령의 기본 구문입니다.
SELECT * FROM 테이블 이름
WHERE MATCH(열1, 열2) AGAINST('word1', 'word2', 'word3')

위 명령은 컬럼1 및 컬럼2 필드에 word1, word2 및 word3이 포함된 모든 데이터 레코드를 쿼리합니다.

참고: InnoDB 데이터 테이블은 전체 텍스트 인덱싱을 지원하지 않습니다.

쿼리 및 인덱스 최적화

데이터베이스에 충분한 테스트 데이터가 있는 경우에만 성능 테스트 결과가 실제 참고값을 갖게 됩니다. 테스트 데이터베이스에 데이터 레코드가 수백 개만 있는 경우 첫 번째 쿼리 명령이 실행된 후 모두 메모리에 로드되는 경우가 많으므로 인덱스 사용 여부에 관계없이 후속 쿼리 명령이 매우 빠르게 실행됩니다. 데이터베이스 성능 테스트 결과는 데이터베이스의 레코드 개수가 1,000개를 초과하고, 총 데이터량이 MySQL 서버의 총 메모리를 초과하는 경우에만 의미가 있습니다.

인덱스를 생성하는 데 어떤 데이터 열을 사용해야 할지 확신할 수 없는 경우 EXPLAIN SELECT 명령을 사용하여 도움을 얻을 수 있는 경우가 많습니다. 이는 실제로 일반 SELECT 명령 앞에 EXPLAIN 키워드를 붙이는 것입니다. 이 키워드를 사용하면 MySQL은 SELECT 명령을 실행하지 않고 분석합니다. MySQL은 쿼리 실행 프로세스, 사용된 인덱스(있는 경우) 등의 정보를 테이블 형식으로 나열합니다.
EXPLAIN 명령의 출력에서 ​​1열은 데이터베이스에서 읽은 데이터 테이블의 이름이며 읽은 순서대로 정렬됩니다. 유형 열은 이 데이터 테이블과 다른 데이터 테이블 간의 연관 관계(JOIN)를 지정합니다. 다양한 유형의 관계 중에서 가장 효율적인 관계는 system이고 그 다음으로 const, eq_ref, ref, range, index, All(All은 상위 데이터 테이블의 각 레코드에 해당함을 의미하며, 이 데이터가 테이블의 모든 레코드는 반드시 읽을 수 있습니다 - 이는 종종 색인을 사용하여 피할 수 있습니다).

possible_keys 데이터 열은 MySQL이 데이터 레코드를 검색할 때 사용할 수 있는 다양한 인덱스를 제공합니다. 키 데이터 열은 MySQL이 실제로 선택한 인덱스입니다. 이 인덱스의 길이(바이트)는 key_len 데이터 열에 제공됩니다. 예를 들어, INTEGER 데이터 열의 인덱스의 경우 바이트 길이는 4입니다. 복합 인덱스를 사용하는 경우 key_len 데이터 열에서 MySQL이 사용하는 부분을 확인할 수도 있습니다. 일반적으로 key_len 데이터 열의 값이 작을수록 좋습니다(빠름을 의미함).
참조 데이터 열은 관계의 다른 데이터 테이블에 있는 데이터 열의 이름을 제공합니다. 행 데이터 열은 이 쿼리를 실행할 때 MySQL이 이 데이터 테이블에서 읽을 것으로 예상하는 데이터 행의 수입니다. 행 데이터 열의 모든 숫자를 곱하면 이 쿼리가 처리해야 하는 조합 수에 대한 아이디어를 얻을 수 있습니다.
마지막으로 추가 데이터 열은 JOIN 작업에 대한 자세한 정보를 제공합니다. 예를 들어 MySQL이 이 쿼리를 실행할 때 임시 데이터 테이블을 생성해야 하는 경우 추가 열에 임시를 사용하는 단어가 표시됩니다.

이상은 이 글의 전체 내용입니다. 모든 분들의 공부에 도움이 되길 바라며, 많은 응원 부탁드립니다.