MySQL 목차: 생성, 추가 및 삭제 튜토리얼

무엇이 MySQL 색인?

An 색인 in MySQL 인덱스는 열 값을 순서대로 저장하여 엔진이 행을 빠르게 검색할 수 있도록 하는 데이터 구조입니다. 인덱스는 데이터를 필터링하는 데 가장 자주 사용되는 열 또는 열들에 생성됩니다. 인덱스를 알파벳순으로 정렬된 목록처럼 생각해 보세요. 정렬된 목록에서 이름을 찾는 것이 정렬되지 않은 목록에서 찾는 것보다 훨씬 빠릅니다.

인덱스에는 장단점이 있습니다. 모든 INSERT 또는 UPDATE 작업은 인덱스를 유지 관리해야 하므로 쓰기 작업이 많은 테이블에 너무 많은 인덱스를 추가하면 전반적인 성능이 저하될 수 있습니다. 일반적으로 WHERE, JOIN 및 ORDER BY 절에 나타나는 열과 쓰기 작업보다 읽기 작업이 더 자주 발생하는 테이블의 열에 인덱스를 생성하는 것이 좋습니다.

인덱스를 사용하는 이유는 무엇일까요?

누구도 느린 시스템을 좋아하지 않습니다. 고성능은 거의 모든 데이터베이스 기반 애플리케이션의 최우선 과제입니다. 기업들은 쿼리 속도를 높이기 위해 하드웨어에 막대한 투자를 하지만, 하드웨어만으로는 한계가 있습니다. 인덱스 최적화는 하드웨어보다 저렴하고 효과적인 해결책입니다.

응답 속도가 느린 경우는 대개 디스크에 행이 물리적 순서대로 저장되어 있기 때문입니다. 인덱스가 없으면, MySQL 조건자와 일치하는 행을 찾으려면 모든 행을 스캔해야 합니다. 이를 "전체 테이블 스캔"이라고 합니다. 인덱스를 사용하면 이러한 작업을 줄일 수 있습니다. MySQL 일치하는 행으로 바로 이동하면 B-트리 조회에 대한 쿼리 계획이 O(n)에서 대략 O(log n)으로 변환됩니다.

구문: 인덱스 생성

인덱스는 두 곳에서 정의할 수 있습니다.

1. 테이블 생성 시점에.
2. 테이블이 이미 존재하는 경우.

예시: CREATE TABLE 문에 인덱스를 직접 생성합니다.

다음 myflixdb 데이터베이스에서 전체 이름 열에 대한 검색이 많을 것으로 예상됩니다. 아래 스크립트는 새 열을 생성합니다. members_indexed 인덱스가 있는 테이블 full_names 열입니다.

CREATE TABLE `members_indexed` (
    `membership_number` INT(11) NOT NULL AUTO_INCREMENT,
    `full_names`        VARCHAR(150) DEFAULT NULL,
    `gender`            VARCHAR(6)   DEFAULT NULL,
    `date_of_birth`     DATE         DEFAULT NULL,
    `physical_address`  VARCHAR(255) DEFAULT NULL,
    `postal_address`    VARCHAR(255) DEFAULT NULL,
    `contact_number`    VARCHAR(75)  DEFAULT NULL,
    `email`             VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`membership_number`),
    INDEX (`full_names`)
) ENGINE = InnoDB;

스크립트를 실행하세요 MySQL 작업대 대 myflixdb 데이터 베이스.

새로 고침 myflixdb 새로운 것을 보기 위해 members_indexed 테이블. 그만큼 full_names 이제 해당 열이 아래에 표시됩니다. 색인 마디.

회원 수가 증가함에 따라 검색 쿼리도 증가합니다. members_indexed WHERE 절과 ORDER BY 절을 사용하는 경우 full_names 이는 기존 시스템에서 동일한 쿼리를 실행하는 것보다 훨씬 빠릅니다. members 인덱스가 없는 테이블.

테이블이 이미 존재하는 경우 그 뒤에 인덱스를 추가합니다.

기존 테이블에 인덱스가 필요한 경우가 종종 있습니다. 검색 쿼리가 느리고, EXPLAIN 실행 계획을 보면 WHERE 절에 포함된 열에 대해 전체 테이블 스캔이 수행되는 것을 확인할 수 있습니다. CREATE INDEX 이 구문은 테이블을 다시 생성하지 않고 인덱스를 추가합니다.

CREATE INDEX `id_index` ON `table_name` (`column_name`);

구체적인 예 - 검색 속도 향상 title 열 movies 표:

CREATE INDEX `title_index` ON `movies` (`title`);

필터링하는 모든 쿼리 movies.title 이제 새로운 인덱스가 적용되었습니다. 다른 열을 기준으로 필터링하는 쿼리는 해당 열에 자체 인덱스가 없는 한 여전히 테이블을 스캔합니다.

참고 : 쿼리에서 항상 동일한 필터링 또는 정렬 조합을 사용하는 경우 여러 열에 걸쳐 복합 인덱스를 생성할 수 있습니다. 순서가 중요하며, 먼저 오는 열이 인덱스 사용 가능 여부를 결정합니다.

테이블의 인덱스 목록

SHOW INDEXES 테이블에 정의된 모든 인덱스를 보려면.

SHOW INDEXES FROM `table_name`;

예시 — 목록 인덱스 movies 표:

SHOW INDEXES FROM `movies`;

명령문을 실행하세요 MySQL 워크 벤치 반대 myflixdb 기존 인덱스와 해당 인덱스가 포함하는 열을 확인하려면.

참고 : 기본 키와 외래 키는 자동으로 인덱싱됩니다. MySQL각 인덱스는 고유한 이름을 가지며, 해당 인덱스가 포함하는 열(들)의 목록을 보여줍니다.

구문: 인덱스 삭제

DROP INDEX 테이블에서 기존 인덱스를 제거합니다. 이는 쓰기 작업이 많은 테이블에서 읽기 측면에서 더 이상 제 역할을 하지 못하는 인덱스로 인해 속도가 느려지는 경우에 유용합니다.

DROP INDEX `index_id` ON `table_name`;

구체적인 예 — 떨어뜨리다 full_names 인덱스에서 members_indexed:

DROP INDEX `full_names` ON `members_indexed`;

유형 MySQL 색인

MySQL 다양한 인덱스 유형을 지원하며, 각 인덱스 유형은 서로 다른 작업 부하에 적합합니다.

타입	목적
기본 키	고유 행 식별자이며, InnoDB의 테이블 데이터와 함께 클러스터링됩니다.
UNIQUE	인덱스 역할을 하면서 고유성을 보장합니다.
인덱스(B-트리)	범위 쿼리 및 같음 조회에 사용되는 기본 보조 인덱스입니다.
전체 텍스트	MATCH … AGAINST 구문을 사용한 자연어 텍스트 검색에 최적화되어 있습니다.
공간	POINT 및 POLYGON과 같은 GIS 데이터 유형에 대한 R-트리 인덱스.
해시시	상수 시간 동등성 조회; MEMORY 저장 엔진에서 사용됩니다.
복합(다중 열)	여러 열을 하나의 인덱스로 결합하며, 가장 왼쪽에 있는 접두사 규칙을 따릅니다.

최고의 실습 MySQL 색인

아래와 같은 습관들을 따르면 인덱스가 유용하게 유지되고 쓸모없는 존재가 되는 것을 막을 수 있습니다.

• 쿼리 패턴에 대한 인덱스이지, 컬럼 이름에 대한 인덱스가 아닙니다. 실제 WHERE, JOIN, ORDER BY 절에 맞는 인덱스를 추가해야지, "중요해 보이는 모든 열"에 인덱스를 추가해서는 안 됩니다.
• 종합지수 순번을 확인하세요: 인덱스를 사용하려면 첫 번째 열이 쿼리에 나타나야 합니다.
• 중복 인덱스를 피하세요: 복합 인덱스의 선행 접두사는 이미 해당 접두사에 대한 단일 열 조회를 포함합니다.
• EXPLAIN으로 검토하세요: 계획 담당자가 실제로 새로운 지수를 선택하고 있는지 확인하십시오.
• 사용하지 않는 인덱스를 삭제합니다. 사용 sys.schema_unused_indexes in MySQL 5.7 이상 버전에서는 아무것도 읽지 않는 인덱스를 찾을 수 있습니다.
• 데이터 유형을 일치시키세요: WHERE 절에서 VARCHAR 열을 숫자와 비교하는 경우, 암시적 형변환 때문에 인덱스를 사용할 수 없습니다.

자주 묻는 질문