mysql 인덱스의 설계 및 사용

인덱스 설계 및 사용
5.1 Mysql 인덱스 개요
모든 MySQL 열 유형을 인덱싱할 수 있습니다. 관련 열에 인덱스를 사용하는 것이 SELECT 작업의 성능을 향상시키는 가장 좋은 방법입니다. 스토리지 엔진에 따라 각 테이블의 최대 인덱스 수와 최대 인덱스 길이를 정의합니다. 모든 스토리지 엔진은 테이블당 최소 16개의 인덱스를 지원하며, 총 인덱스 길이는 최소 256바이트입니다. 대부분의 스토리지 엔진에는 더 높은 제한이 있습니다.
MySQL 5.1에서 MyISAM 및 InnoDB 테이블의 경우 접두사는 최대 1000바이트까지 가능합니다. 접두사 제한은 바이트 단위로 측정해야 하는 반면 CREATE TABLE 문의 접두사 길이는 문자 수로 해석됩니다. 멀티바이트 문자 집합을 사용하는 열의 접두사 길이를 지정할 때 이 점을 고려해야 합니다.
FULLTEXT 인덱스도 생성할 수 있습니다. 인덱스는 전체 텍스트 검색에 사용될 수 있습니다. MyISAM 스토리지 엔진만이 FULLTEXT 인덱스를 지원하며 CHAR, VARCHAR 및 TEXT 열에 대해서만 지원합니다. 인덱스는 항상 전체 열에 대해 수행되며, 부분(접두사) 인덱스는 지원되지 않습니다. 공간 열 유형에 대해서도 인덱스를 생성할 수 있습니다. MyISAM 스토리지 엔진만이 공간 유형을 지원합니다. 공간 인덱스는 R-트리를 사용합니다. 기본적으로 MEMORY(HEAP) 스토리지 엔진은 해시 인덱스를 사용하지만 B-트리 인덱스도 지원됩니다.
5.2 인덱스 디자인의 원칙
1. 검색할 인덱스 컬럼이 반드시 선택되는 컬럼은 아닙니다. 즉, 인덱싱에 가장 적합한 컬럼은 SELECT 키워드 뒤의 SELECT 목록에 나타나는 컬럼이 아닌 WHERE 절에 나타나는 컬럼이나 Join 절에 지정된 컬럼이다.
2. 고유 인덱스를 사용합니다. 열의 값 분포를 고려하십시오. 인덱스는 고유한 값이 있는 열에 가장 잘 작동하고 중복 값이 ​​여러 개 있는 열에 가장 나쁘게 작동합니다. 예를 들어, 열 보유 기간은 서로 다른 값을 가지므로 행을 쉽게 구분할 수 있습니다.
성별을 기록하는 데 사용되는 열에는 "M"과 "F"만 포함되어 있으므로 이 열을 색인화하는 것은 거의 사용되지 않습니다. (어떤 값을 검색해도 행의 약 절반이 얻어집니다.)
3. 짧은 인덱스. 문자열을 인덱싱하는 경우 접두사 길이를 지정해야 하며 가능할 때마다 이 작업을 수행해야 합니다.
예를 들어 CHAR(200) 열이 있는 경우 대부분의 값이 처음 10~20자 내에서 고유한 경우 전체 열을 인덱싱하지 마세요. 처음 10~20자를 색인화하면 색인 공간이 많이 절약되고 쿼리가 더 빨라질 수 있습니다. 인덱스가 작을수록 디스크 I/O가 줄어들고 값이 짧을수록 비교 속도가 빨라집니다.
더 중요한 것은 더 짧은 키 값의 경우 인덱스 캐시의 블록이 더 많은 키 값을 보유할 수 있으므로 MySQL도 메모리에 더 많은 값을 보유할 수 있다는 것입니다. 이렇게 하면 인덱스에서 더 큰 블록을 읽지 않고도 행을 찾을 가능성이 높아집니다.
(물론 상식이 필요합니다. 열 값의 첫 번째 문자만으로 인덱싱하는 것은 이 인덱스에 다양한 값이 많지 않기 때문에 큰 이점이 없을 것 같습니다.)
4. 가장 왼쪽 접두사를 악용합니다. n열 인덱스를 생성하면 실제로 MySQL이 사용할 수 있는 n개의 인덱스가 생성됩니다.
다중 열 인덱스는 인덱스의 가장 왼쪽 열 집합을 사용하여 행을 일치시킬 수 있으므로 여러 인덱스로 작동할 수 있습니다. 이러한 열 집합을 가장 왼쪽 접두사라고 합니다. (이는 열의 처음 n자를 인덱스 값으로 사용하는 열의 접두사를 인덱싱하는 것과 다릅니다.)
5. 과도한 인덱스를 사용하지 마세요. "인덱스가 많을수록 좋다"고 생각하지 마십시오. 모든 것에 인덱스를 사용하는 것은 잘못된 것입니다. 각 추가 인덱스는 추가 디스크 공간을 차지하고 이미 소개한 쓰기 작업의 성능을 저하시킵니다. 테이블의 내용이 수정되면 인덱스를 업데이트해야 하고 때로는 재구성해야 할 수도 있습니다. 따라서 인덱스가 많을수록 시간이 더 오래 걸립니다
. 거의 사용되지 않거나 전혀 사용되지 않는 인덱스가 있는 경우 테이블 수정 속도가 불필요하게 느려집니다.
또한 MySQL은 실행 계획을 생성할 때 각 인덱스를 고려해야 하며 이 역시 시간이 걸립니다. 중복 인덱스를 생성하면 쿼리 최적화를 위한 작업이 더 늘어납니다. 인덱스가 너무 많으면 MySQL이 사용할 최상의 인덱스를 선택하지 못할 수도 있습니다. 필요한 인덱스만 유지하면 쿼리 최적화가 쉬워집니다. 이미 인덱싱된 테이블에 인덱스를 추가하려면 추가할 인덱스가 기존 다중 컬럼 인덱스의 가장 왼쪽 인덱스인지 여부를 고려해야 합니다. 그렇다면 이 색인이 이미 존재하므로 굳이 추가하지 마세요.
6. 열에 대한 비교 유형을 고려하세요. 인덱스는 " < ", " < = ", " = ", " > = ", " >" 작업과 함께 사용할 수 있습니다. 패턴에 리터럴 접두사가 있는 경우 인덱스는 LIKE 작업에도 사용됩니다. 열이 다른 유형의 작업(예: STRCMP())에만 사용되는 경우 해당 열을 인덱싱할 때 값이 없습니다.
5.3 btree 인덱스 및 해시 인덱스
BTREE 및 HASH 인덱스의 경우 =, <=>, IN, IS NULL 또는 IS NOT NULL 연산자를 사용할 때 핵심 요소와 상수 값 간의 비교 관계가 일치합니다. 하나의 범위 조건으로. 해시 인덱스에는 몇 가지 추가 기능이 있습니다. 즉, = 또는 <=> 연산자를 사용한 동등 비교에만 사용됩니다(그러나 빠릅니다). 최적화 프로그램은 해시 인덱스를 사용하여 ORDER BY 작업 속도를 높일 수 없습니다.
(이 유형의 색인은 순서대로 다음 항목을 검색하는 데 사용할 수 없습니다.) MySQL은 두 값 사이에 대략 몇 개의 행이 있는지 결정할 수 없습니다(이는 사용할 인덱스를 결정하기 위해 범위 최적화 프로그램에서 사용됩니다). MyISAM 테이블을 해시 인덱스 MEMORY 테이블로 변경하면 일부 쿼리가 영향을 받습니다. 전체 키워드만 사용하여 행을 검색할 수 있습니다. (B-트리 인덱스를 사용하면 모든 키의 가장 왼쪽 접두사를 사용하여 행을 찾을 수 있습니다.)
BTREE 인덱스의 경우 >, <, >=, <=, BETWEEN, != 또는 <> 또는 LIKE 'pattern'('pattern'은 와일드카드로 시작하지 않음)을 사용하는 경우 연산자 일 때, 핵심 요소와 상수 값의 비교 관계는 범위 조건에 해당합니다.
"상수 값"은 쿼리 문자열의 상수, 동일한 조인의 const 또는 시스템 테이블의 열, 상관되지 않은 하위 쿼리의 결과, 이전 유형의 하위 표현식으로 완전히 구성된 표현식을 의미합니다.
다음은 WHERE 절에 범위 조건이 포함된 쿼리의 몇 가지 예입니다.
다음 범위 쿼리는 btree 인덱스 및 해시 인덱스에 적합합니다.
SELECT * FROM t1WHEREkey_col = 1ORkey_col IN (15,18,20);
다음 범위 쿼리는 btree 인덱스에 적용됩니다.
SELECT * FROM t1WHERE key_col > 1AND key_col < 10;
SELECT * FROM t1WHERE key_col LIKE 'ab%'OR key_col BETWEEN 'bar' AND
' foo';
5.4 Mysql이 인덱스를 사용하는 방법
인덱스는 열에서 특정 값을 가진 행을 빠르게 찾는 데 사용됩니다. 인덱스를 사용하지 않으면 MySQL은 레코드 1에서 시작하여 관련 행을 찾을 때까지 전체 테이블을 읽어야 합니다. 테이블이 클수록 시간이 더 걸립니다. 테이블의 쿼리된 열에 인덱스가 있으면 MySQL은 모든 데이터를 볼 필요 없이 데이터 파일의 중간을 검색하는 지점에 빠르게 도달할 수 있습니다. 예를 들어
테이블에 1000개의 행이 있는 경우 이는 순차 읽기보다 최소 100배 빠릅니다. 행의 많은 부분에 액세스해야 하는 경우 해당 시점에서는 디스크 검색을 방지하므로 순차 읽기가 훨씬 빠릅니다.
대부분의 MySQL 인덱스(PRIMARY KEY, UNIQUE, INDEX 및 FULLTEXT)는 B-트리에 저장됩니다. 공간 컬럼형 인덱스만 R-tree를 사용하며, MEMORY 테이블도 해시 인덱스를 지원합니다.
데이터베이스가 인덱스를 사용하는 상황과 데이터베이스가 인덱스를 사용하지 않는 상황에 대한 자세한 설명은 최적화 장의 관련 장을 참조하세요. 여기서는 반복하지 않습니다.

더 많은 관련 글은 PHP 중국어 홈페이지(www.php.cn)를 주목해주세요!