mysql 인덱스 요약 - mysql 인덱스 유형 및 생성에 대한 자세한 소개

MySQL 인덱스의 장점에 대해, 제대로 설계되어 인덱스를 사용하는 MySQL이 람보르기니라면, 설계되지 않고 인덱스를 사용하는 MySQL은 인간 세발자전거입니다. 인덱스가 없는 테이블의 경우 단일 테이블 쿼리에 수십만 개의 데이터가 있을 수 있으며, 이는 병목 현상입니다. 일반적으로 대규모 웹 사이트는 하루에 수십만 또는 심지어 수백만 개의 데이터를 생성할 수 있으며 인덱스가 없는 쿼리는 매우 느려집니다. WordPress를 예로 들면, 여러 데이터 테이블은 자주 쿼리되는 필드에 인덱스를 추가합니다. 예를 들어 wp_comments 테이블에는 5개 필드에 대해 설계된 BTREE 인덱스가 있습니다.

간단한 비교 테스트

작년에 테스트한 데이터를 간단한 예로 들어보겠습니다. 20개가 넘는 데이터 소스가 평균 200만 개 정도의 데이터를 무작위로 생성합니다. 둘째, 테이블 구조는 자동 증가 ID, char 유형, 텍스트 유형 및 int 유형만 포함하여 비교적 간단합니다. 단일 테이블은 크기가 2G이고 MyIASM 엔진을 사용합니다. 인덱스를 추가하지 않고 테스트를 시작합니다.

다음 SQL 문을 실행합니다:

mysql> SELECT id,FROM_UNIXTIME(time) FROM article WHERE a.title='测试标题'

쿼리를 결합하는 데 필요한 시간과 기타 제약 조건이 추가되면 데이터베이스가 엄청난 메모리를 소비하게 됩니다. . , 프런트 엔드 프로그램 실행에 영향을 미칩니다. 이때 제목 필드에 BTREE 인덱스를 추가합니다:

mysql> ALTER TABLE article ADD INDEX index_article_title ON title(200);

위 쿼리문을 다시 실행해 보면 대조가 매우 뚜렷해집니다.

MySQL의 개념 index

인덱스는 데이터 테이블의 모든 레코드에 대한 참조 포인터를 포함하는 특수 파일입니다(InnoDB 데이터 테이블의 인덱스는 테이블 공간의 구성 요소입니다). 보다 일반적으로 말하면, 데이터베이스 인덱스는 책 앞부분의 목차와 같으며, 이는 데이터베이스 쿼리 속도를 높일 수 있습니다. 위의 SQL 문에서 인덱스가 없으면 데이터베이스는 200개의 데이터를 모두 탐색하여 해당 인덱스를 사용하여 조건에 맞는 항목을 선택하고 인덱스에서 조건에 맞는 옵션을 직접 검색합니다. . SQL 문을 "SELECT * FROM Article WHERE id=2000000"으로 변경하면 데이터베이스가 200만 행의 데이터를 순차적으로 읽은 후 결과를 제공하도록 하시겠습니까, 아니면 인덱스에서 직접 찾도록 하시겠습니까? 위의 두 그림 사이의 명확한 시간 비교는 이미 답을 제공합니다(참고: 일반적으로 데이터베이스는 기본적으로 기본 키에 대한 인덱스를 생성합니다).

인덱스는 클러스터형 인덱스와 비클러스터형 인덱스로 구분됩니다. 클러스터형 인덱스는 데이터 저장 위치에 따라 정렬되지만 비클러스터형 인덱스는 행 검색 속도가 많이 다릅니다. 비클러스터형 인덱스는 단일 행 검색의 경우 빠릅니다.

MySQL 인덱스의 종류

1. 일반 인덱스

가장 기본적인 인덱스이며, 예를 들어 위의 제목 필드에 대해 생성되는 인덱스는 다음과 같습니다. MyIASM의 기본 BTREE 유형 인덱스인 일반 인덱스도 대부분의 경우 사용되는 인덱스입니다.

01    –直接创建索引    
02    CREATE INDEX index_name ON table(column(length))    
03    –修改表结构的方式添加索引    
04    ALTER TABLE table_name ADD INDEX index_name ON (column(length))    
05    –创建表的时候同时创建索引    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    INDEX index_name (title(length))    
13    )    
14    –删除索引    
15    DROP INDEX index_name ON table

2. 고유 인덱스

는 인덱스 열의 값이 고유해야 한다는 점을 제외하면 일반 인덱스와 유사하지만 null 값입니다. 허용됩니다(기본 키와 기본 키는 다릅니다). 결합 인덱스인 경우 컬럼 값의 조합은 유일해야 하며, 생성 방법은 일반 인덱스와 유사하다.

01    –创建唯一索引    
02    CREATE UNIQUE INDEX indexName ON table(column(length))    
03    –修改表结构    
04    ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))    
05    –创建表的时候直接指定    
06    CREATE TABLE `table` (    
07    `id` int(11) NOT NULL AUTO_INCREMENT ,    
08    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
09    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
10    `time` int(10) NULL DEFAULT NULL ,    
11    PRIMARY KEY (`id`),    
12    UNIQUE indexName (title(length))    
13    );

3. 전체 텍스트 인덱스(FULLTEXT)

MySQL은 버전 3.23부터 전체 텍스트 인덱싱 및 전체 텍스트 검색을 지원합니다. 23. FULLTEXT 인덱스는 MyISAM 테이블에만 사용할 수 있습니다. CREATE TABLE 문의 일부로 CHAR, VARCHAR 또는 TEXT 열에서 생성하거나 나중에 ALTER TABLE 또는 CREATE INDEX를 사용하여 추가할 수 있습니다. ////더 큰 데이터 세트의 경우 FULLTEXT 인덱스 없이 테이블에 데이터를 입력한 다음 인덱스를 생성하는 것이 기존 FULLTEXT 인덱스에 데이터를 입력하는 것보다 빠릅니다. 그러나 대용량 데이터 테이블의 경우 전체 텍스트 인덱스를 생성하는 것은 시간과 하드 디스크 공간을 많이 소모하는 프로세스라는 점을 기억하십시오.

01    –创建表的适合添加全文索引    
02    CREATE TABLE `table` (    
03    `id` int(11) NOT NULL AUTO_INCREMENT ,    
04    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,    
05    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,    
06    `time` int(10) NULL DEFAULT NULL ,    
07    PRIMARY KEY (`id`),    
08    FULLTEXT (content)    
09    );    
10    –修改表结构添加全文索引    
11    ALTER TABLE article ADD FULLTEXT index_content(content)    
12    –直接创建索引    
13    CREATE FULLTEXT INDEX index_content ON article(content)

4. 단일 열 인덱스, 다중 열 인덱스

여러 단일 열 인덱스와 단일 다중 열 인덱스의 쿼리 효과 쿼리가 실행되기 때문에 열 인덱스가 다릅니다. 현재 MySQL은 하나의 인덱스만 사용할 수 있으며 여러 인덱스 중에서 가장 제한적인 인덱스를 선택합니다.

5. 복합 인덱스(가장 왼쪽 접두사)

일반적으로 사용되는 SQL 쿼리문은 일반적으로 더 많은 제한이 있으므로 MySQL의 효율성을 더욱 추출하기 위해서는 복합 인덱스 구축을 고려해야 합니다. . 예를 들어 위 테이블에서는 제목과 시간에 대한 결합 인덱스가 생성됩니다. ALTER TABLE 기사 ADD INDEX index_titme_time(제목(50), 시간(10)). 이러한 결합지수를 구축하는 것은 실제로 다음과 같은 두 개의 결합지수를 구축하는 것과 같습니다.

–제목, 시간

–제목

왜 시간과 같은 결합지수는 없는 걸까요? ? 모직물? 이는 MySQL 복합 인덱스의 "가장 왼쪽 접두사"의 결과입니다. 간단한 이해는 가장 왼쪽부터 조합을 시작하는 것입니다. 이 두 열을 포함하는 쿼리뿐만 아니라 다음 SQL과 같이 결합된 인덱스를 사용합니다.

1    –使用到上面的索引    
2    SELECT * FROM article WHREE title='测试' AND time=1234567890;    
3    SELECT * FROM article WHREE utitle='测试';    
4    –不使用上面的索引    
5    SELECT * FROM article WHREE time=1234567890;

위는 mysql 인덱스-mysql 인덱스 유형에 대한 요약 및 생성에 대한 자세한 소개입니다. PHP 중국어 홈페이지(www.php.cn)를 주목해주세요!