Mysql 시리즈(5) 인덱스 기능

인덱스는 특수 파일입니다(InnoDB 데이터 테이블의 인덱스는 테이블 공간의 구성 요소입니다). 여기에는 데이터 테이블의 모든 레코드에 대한 참조 포인터가 포함되어 있습니다. 인덱스는 만병통치약이 아닙니다. 인덱스는 데이터 검색 작업 속도를 높일 수 있지만 데이터 수정 작업 속도를 늦출 수 있습니다. 데이터 레코드가 수정될 때마다 인덱스를 새로 고쳐야 합니다. 이러한 단점을 어느 정도 보완하기 위해 많은 SQL 명령에는 DELAY_KEY_WRITE 항목이 있습니다. 이 옵션의 기능은 각각의 새로운 레코드가 삽입되고 기존의 각 레코드가 이 명령에 의해 수정된 직후 MySQL이 인덱스를 새로 고치는 것을 일시적으로 방지하는 것입니다. 인덱스 새로 고침은 모든 레코드가 삽입/수정될 때까지 기다립니다. DELAY_KEY_WRITE 옵션의 역할은 데이터 테이블에 많은 새 레코드를 삽입해야 할 때 매우 분명해집니다. 또한 인덱스는 하드 드라이브에서 상당한 공간을 차지합니다. 따라서 가장 자주 쿼리되고 가장 자주 정렬되는 데이터 열만 인덱싱되어야 합니다. 데이터 열에 중복된 콘텐츠가 많이 포함되어 있으면 인덱싱해도 실질적인 효과가 크지 않습니다.

이론적으로는 데이터 테이블의 각 필드에 대해 인덱스를 구축하는 것이 가능하지만 MySQL에서는 동일한 데이터 테이블의 총 인덱스 수를 16개로 제한합니다.

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

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

2． 제한 사항

WHERE 절의 쿼리 조건에 부등호(WHERE coloum !=)가 있으면 MySQL은 인덱스를 사용할 수 없습니다. 마찬가지로 WHERE 절의 쿼리 조건에 (WHERE DAY(열) =) 함수를 사용하면 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． 복합 인덱스

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

인덱스 길이

CHAR 및 VARCHAR 유형 데이터 열에 대한 인덱스를 정의할 때 인덱스 길이를 지정된 문자 수로 제한할 수 있습니다. 이 필드에 허용되는 최대 문자 수보다 적습니다. 이 방법의 장점은 크기가 더 작고 검색 속도가 빠른 인덱스 파일을 생성할 수 있다는 것입니다. 대부분의 응용 프로그램에서 데이터베이스의 문자열 데이터는 대부분 다양한 이름을 기반으로 합니다. 인덱스 길이를 10~15자로 설정하면 검색 범위를 몇 가지 데이터 레코드로 좁힐 수 있습니다. BLOB 및 TEXT 유형 데이터 열에 대한 인덱스를 생성할 때 인덱스 길이에 제한을 두어야 합니다. MySQL에서 허용하는 최대 인덱스는 표시되는 문자열의 속도만 높일 수 있는 전체 텍스트 인덱스 텍스트 필드의 일반 인덱스입니다. 필드 내용의 앞부분(즉, 필드 내용의 시작 부분에 있는 문자)을 검색 작업을 수행합니다. 필드가 여러 단어 또는 심지어 여러 단어로 구성된 더 큰 텍스트 조각을 저장하는 경우 일반 인덱스는 거의 사용되지 않습니다. 이러한 검색은 종종 MySQL의 경우 복잡하고 처리해야 하는 데이터 양이 많은 경우 응답 시간이 길어질 수 있는 형식으로 제공됩니다.

이런 상황이 바로 풀텍스트 인덱스(full-textindex)가 그 재능을 발휘할 수 있는 상황이다. 이러한 유형의 인덱스를 생성할 때 MySQL은 텍스트에 나타나는 모든 단어의 목록을 생성하고 쿼리 작업은 이 목록을 사용하여 관련 데이터 레코드를 검색합니다. 전체 텍스트 인덱스는 데이터 테이블과 함께 생성하거나 나중에 필요할 때 다음 명령을 사용하여 추가할 수 있습니다.

ALTER TABLE 테이블 이름 ADD FULLTEXT (column1, column2) 텍스트 인덱스의 경우 SELECT 쿼리 명령을 사용하여 하나 이상의 지정된 단어가 포함된 데이터 레코드를 검색할 수 있습니다. 다음은 이러한 유형의 쿼리 명령의 기본 구문입니다.

SELECT * FROM tablename

WHERE MATCH (column1,column2) 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 데이터 열의 값이 작을수록 좋습니다.

ref 데이터 열은 관계의 다른 데이터 테이블에 있는 데이터 열의 이름을 제공합니다. 행 데이터 열은 이 쿼리를 실행할 때 MySQL이 이 데이터 테이블에서 읽을 것으로 예상하는 데이터 행의 수입니다. 행 데이터 열의 모든 숫자를 곱하면 쿼리가 처리해야 하는 조합 수에 대한 아이디어를 얻을 수 있습니다.

마지막으로 추가 데이터 열은 JOIN 작업에 대한 자세한 정보를 제공합니다. 예를 들어 MySQL이 이 쿼리를 실행할 때 임시 데이터 테이블을 생성해야 하는 경우 추가 열에 usingtemporary라는 단어가 표시됩니다.

위는 Mysql 시리즈(5) 인덱스 기능에 대한 내용입니다. 더 많은 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!