Myql용 인덱스 생성-MySQL 튜토리얼-php.cn

Mysql의 성능을 향상시키기 위해 MySQL의 검색 속도를 향상시키고 MySQL 데이터베이스에 대한 부담을 완화하기 위해 인덱스를 생성할 수 있습니다. MySQL 인덱스와 몇 가지 고급 사용법에 대해 이야기해 보겠습니다.

모든 MySQL 열 유형을 색인화할 수 있습니다. 스토리지 엔진에 따라 각 테이블의 최대 인덱스 수와 최대 인덱스 길이를 정의합니다.
모든 스토리지 엔진은 테이블당 최소 16개의 인덱스를 지원하며, 총 인덱스 길이는 최소 256바이트입니다. 대부분의 스토리지 엔진에는 더 높은 제한이 있습니다.

현재 스토리지 엔진 모드와 특별히 관련된 두 가지 스토리지 유형의 인덱스(btree 및 해시)만 있습니다.
MyISAM . 해시 인덱스 사용

MySQL의 btree 인덱스와 해시 인덱스의 차이점
해시 인덱스 구조의 특수성으로 검색 효율성이 매우 높고, 인덱스 검색이 필요한 btree(B-Tree) 인덱스와 달리 한번에 인덱스 검색 위치를 지정할 수 있습니다. 루트 노드에서 분기 노드로 이동하고, 최종적으로 페이지 노드는 다중 IO 액세스를 통해 액세스할 수 있으므로 해시 인덱스의 쿼리 효율성은 btree(B-Tree) 인덱스보다 훨씬 높습니다.

해시 인덱스는 매우 효율적이지만, 해시 인덱스 자체도 그 특수성으로 인해 다음과 같은 많은 한계와 단점을 가지고 있습니다.
(1) 해시 인덱스는 =, <=>, IN, IS NULL 또는 IS NOT NULL 쿼리만 만족할 수 있으며, 범위 쿼리는 사용할 수 없습니다.
해시 인덱스는 해시 연산 후 해시 값을 비교하기 때문에 등값 필터링에만 사용할 수 있고 범위 기반 필터링에는 사용할 수 없습니다. 해시 작업 전과 정확히 동일하다는 보장은 없습니다.

(2) 데이터 정렬 작업을 피하기 위해 해시 인덱스를 사용할 수 없습니다.
해시 인덱스는 해시 계산 후 해시 값을 저장하고, 해시 값의 크기 관계가 반드시 해시 연산 전의 키 값과 정확히 동일할 필요는 없으므로 데이터베이스는 정렬 연산을 피하기 위해 인덱스 데이터를 사용할 수 없습니다. ;

(3) 부분 인덱스 키를 사용하여 해시 인덱스를 쿼리할 수 없습니다.
결합 인덱스의 경우 해시 인덱스의 해시 값을 계산할 때 해시 값을 별도로 계산하는 것이 아니라 결합된 인덱스 키를 병합한 후 함께 해시 값을 계산하므로 처음 하나 또는 여러 개를 통해 쿼리할 때 사용됩니다. 결합된 인덱스의 인덱스 키, 해시 인덱스도 활용될 수 없습니다.

(4) 해시 인덱스는 언제든지 테이블 스캔을 피할 수 없습니다.
앞서도 알고 있듯이 해시 인덱스는 인덱스 키를 해싱한 후 해시 연산 결과의 해시 값과 해당 행 포인터 정보를 해시 테이블에 저장하는 것입니다. 특정 해시 키 값을 만족하는 레코드 수는 해시 인덱스에서 직접 조회할 수 없으며, 대신 테이블의 실제 데이터에 접근하여 해당 비교를 수행하고 해당 결과를 얻어야 합니다.

(5) 해시 인덱스가 동일한 해시 값을 많이 만나더라도 성능이 반드시 B-Tree 인덱스보다 높지는 않습니다.
선택도가 낮은 인덱스 키의 경우 해시 인덱스를 생성하면 동일한 해시 값과 연관된 레코드 포인터 정보가 대량으로 존재하게 됩니다. 이런 식으로 특정 레코드를 찾는 것은 매우 번거롭고 테이블 데이터에 대한 다중 액세스를 낭비하게 되어 전반적인 성능이 저하됩니다. B-Tree 인덱스는 다음을 제외하고 MySQL 데이터베이스에서 가장 자주 사용되는 인덱스 유형입니다. Archive 스토리지 엔진은 B-Tree 인덱스를 지원합니다. 이는 MySQL의 경우뿐만 아니라 실제로 다른 많은 데이터베이스 관리 시스템에서도 B-Tree 인덱스가 주요 인덱스 유형입니다. 이는 주로 B-Tree 인덱스의 저장 구조가 데이터베이스 데이터 검색에 특정 기능을 가지고 있기 때문입니다. . 아주 좋은 성능.
일반적으로 MySQL의 B-Tree 인덱스의 물리적 파일의 대부분은 Balance Tree 구조로 저장됩니다. 즉, 실제로 필요한 모든 데이터는 트리의 Leaf 노드에 저장되며 모든 Leaf에 대한 최단 경로는 노드는 경로의 길이가 정확히 동일하므로 우리는 모두 B-Tree 인덱스라고 부릅니다. 물론, 다양한 데이터베이스(또는 MySQL의 다양한 스토리지 엔진)는 자체 B-Tree 인덱스를 저장할 때 스토리지 구조를 약간 변경할 수 있습니다. .변신을 해라.
예를 들어 Innodb 스토리지 엔진의 B-Tree 인덱스가 사용하는 실제 스토리지 구조는 실제로 B+Tree인데, 이는 B-Tree 데이터 구조를 기반으로 약간의 수정이 이루어지고 스토리지 인덱스가 배치된다는 의미입니다. 키 관련 정보 외에도 리프 노드에 인접한 다음 리프 노드를 가리키는 포인터 정보도 저장됩니다. 이는 주로 인접한 여러 리프 노드를 검색하는 속도를 높이기 위한 것입니다.
Innodb 스토리지 엔진에는 두 가지 형태의 인덱스가 있는데, 하나는 Cluster 형태의 기본 키 인덱스(Primary Key)이고, 다른 하나는 기본적으로 다른 스토리지 엔진과 동일한 스토리지 형태인 일반 B( MyISAM 스토리지 엔진과 같은) -트리 인덱스, 이 인덱스를 Innodb 스토리지 엔진에서는 보조 인덱스라고 합니다.
Innodb에서는 기본 키를 통해 데이터에 접근하는 것이 매우 효율적이지만, 보조 인덱스를 통해 데이터에 접근하는 경우 Innodb는 먼저 보조 인덱스의 관련 정보와 해당 인덱스 키를 통해 리프 노드를 검색한 후 필요한 to Leaf Node에 저장된 기본 키 값은 기본 키 인덱스를 통해 해당 데이터 행을 얻는 데 사용됩니다.
MyISAM 스토리지 엔진의 기본 키 인덱스와 기본 키가 아닌 인덱스의 차이는 기본 키 인덱스의 인덱스 키가 고유하고 비어 있지 않은 키라는 점을 제외하면 매우 작습니다. 또한 MyISAM 스토리지 엔진의 인덱스 저장 구조는 기본적으로 Innodb의 Secondary Index와 동일하다. 가장 큰 차이점은 MyISAM 스토리지 엔진이 리프 노드에 인덱스 키 정보를 저장하고 해당 스토리지를 직접 저장할 수 있다는 점이다. MyISAM 데이터 파일에 위치하지만, 기본 키의 키 값 정보는 저장되지 않습니다.

인덱스는 단일 열 인덱스와 결합 인덱스로 구분됩니다. 단일 열 인덱스는 인덱스가 단일 열만 포함한다는 의미입니다. 테이블은 여러 개의 단일 열 인덱스를 가질 수 있지만 이는 결합된 인덱스가 아닙니다. 결합 인덱스, 즉 하나의 인덱스에 여러 열이 포함됩니다.
MySQL 인덱스 유형은 다음과 같습니다.
(1) 일반 인덱스, 가장 기본적인 인덱스이며 제한이 없습니다. 다음과 같은 생성 방법이 있습니다.

-- 색인 만들기

CREATE INDEX indexName ON mytable(username(10)) ,city(10)) --Combined index

--indexName은 색인입니다. name, mytable 테이블 이름, username 및 city는 열 이름이고, 10은 접두사 길이, 즉 열의 가장 왼쪽 문자부터 시작하여 인덱스에 의해 저장되는 정보의 길이, 단위 byte

-- CHAR인 경우, VARCHAR 유형인 경우 접두어 길이는 필드의 실제 길이보다 작을 수 있습니다. BLOB 및 TEXT 유형인 경우 접두어 길이를 아래와 동일하게 지정해야 합니다.

테이블 구조를 수정하여 인덱스 만들기

ALTER TABLE mytable ADD INDEX indexName (사용자 이름(10));

-- ALTER TABLE mytable ADD INDEX indexName (사용자 이름(10),city(10)) ;

-- 여기에 인덱스 이름을 쓸 필요는 없습니다. 시스템이 자동으로 사용자 이름, 사용자 이름_2, 사용자 이름_3,...

테이블을 생성할 때

CREATE TABLE mytable(

)을 직접 지정합니다.

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

INDEX indexName (사용자 이름(10))-- INDEX indexName (사용자 이름(10),city(10))

);

- - 여기서 indexName 인덱스 이름도 생략 가능

(2) 고유 인덱스, 인덱스 열의 값이 고유해야 한다는 점을 제외하면 이전 일반 인덱스와 유사하지만 null 값 허용됩니다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다. 다음과 같은 생성 방법이 있습니다(일반 인덱스 생성 시 키워드 INDEX 앞에 UNIQUE만 추가):

Create index

CREATE UNIQUE INDEX indexName ON mytable(username(10));

- -

테이블 구조를 수정하여 인덱스 만들기

ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10));-- ALTER TABLE mytable ADD UNIQUE indexName (username(10));

--로 축약할 수도 있습니다.

Create 직접 지정

CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),

age INT,

UNIQUE INDEX indexName (username(10)) - - UNIQUE indexName (username(10))

);

이라고도 약칭할 수 있습니다. (3) 기본 키 인덱스는 Null 값을 허용하지 않는 특수한 고유 인덱스입니다. 테이블 생성과 동시에 생성되는 기본 키가 기본 키 인덱스입니다. 기본 키 인덱스는 이름을 지정할 필요가 없습니다. 하나의 기본 키만 가질 수 있습니다. 기본 키 인덱스는 고유 인덱스 또는 전체 텍스트 인덱스가 동시에 될 수 있지만 고유 인덱스 또는 전체 텍스트 인덱스는 동일한 인덱스에 공존할 수 없습니다.

-- 테이블 구조를 수정하여 index ALTER TABLE mytable ADD PRIMARY KEY (id)

-- 테이블 생성 CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR(16),
age INT,PRIMARY KEY( id)
);

(4) 전체 텍스트 인덱스, InnoDB 저장소 엔진은 전체 텍스트 인덱싱을 지원하지 않습니다.

--인덱스 생성 CREATE FULLTEXT INDEX indexName ON mytable(username(10));

--인덱스를 생성하도록 테이블 구조를 수정합니다. ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10)) ;

-- ALTER TABLE mytable ADD FULLTEXT indexName (username(10));로 축약할 수도 있습니다.

-- 테이블 생성 시 CREATE TABLE mytable(

id INT,

username VARCHAR(16),

city VARCHAR)을 직접 지정합니다(16),
age INT,
FULLTEXT INDEX indexName(username(10))

-- FULLTEXT indexName (username(10)))ENGINE=MYISAM;

-- 테이블 생성 시 전체 텍스트 인덱스를 생성합니다. 테이블의 스토리지 엔진을 기본 InnoDB 스토리지 엔진인 MYISAM으로 설정합니다. mysql의 새 버전은 전체 텍스트 인덱스를 지원하지 않습니다.

-- 인덱스 삭제 DROP INDEX indexName ON mytable

인덱스는 쿼리 속도를 크게 향상시키지만 테이블 업데이트 속도도 감소시킵니다. 테이블에 대한 INSERT, UPDATE 및 DELETE. 왜냐하면 테이블을 업데이트할 때 MySQL은 데이터를 저장할 뿐만 아니라 인덱스 파일도 저장해야 하기 때문입니다.

인덱스 파일을 생성하면 디스크 공간을 차지하게 됩니다. 일반적으로 이 문제는 심각하지 않지만, 큰 테이블에 여러 개의 결합된 인덱스를 생성하면 인덱스 파일이 빠르게 확장됩니다.

위 내용은 MySQL에서 인덱스 생성에 대해 요약한 내용입니다. 앞으로 모든 분들께 도움이 되기를 바랍니다.