mysql 인덱스의 기본 동작 요약(4)_MySQL

1. 인덱스를 사용하는 이유:
데이터베이스 개체의 색인은 실제로 책의 목차와 유사하며 주로 테이블에서 데이터를 검색하는 속도를 향상시킵니다. 데이터가 데이터베이스 테이블에 저장되므로 인덱스는 데이터베이스 테이블 개체에 생성되고 테이블의 하나 또는 여러 필드에서 생성된 키로 구성됩니다. 이러한 키는 데이터 구조(B-트리 또는 해시 테이블)에 저장됩니다. . 키와 관련된 필드는 MySQL을 통해 빠르고 효율적으로 찾을 수 있습니다. 인덱스는 저장 형태에 따라 B-트리 인덱스(BTREE)와 해시 인덱스(HASH)로 나눌 수 있습니다. 참고: InnoDB 및 MyISAM 스토리지 엔진은 BTREE 유형 인덱스를 지원하고 MEMORY 스토리지 엔진은 HASH 유형 인덱스를 지원합니다.
MySQL은 일반 인덱스, 고유 인덱스, 전체 텍스트 인덱스, 단일 열 인덱스, 다중 열 인덱스, 공간 인덱스 등 6가지 유형의 인덱스를 지원합니다.
인덱스 생성에 적합한 상황은 다음과 같습니다.
1. 자주 쿼리되는 필드입니다. 즉, WHERE 절에 나타나는 필드입니다.
2. 그룹의 필드, 즉 GROUP BY 절에 나타나는 필드입니다.
3. 종속 관계가 있는 하위 테이블과 상위 테이블, 즉 기본 키 또는 외래 키 필드 간의 공동 쿼리.
4. 고유한 전체 제약 조건 필드를 설정합니다.

2. 색인 생성 및 보기:
인덱스 작업에는 인덱스 생성, 인덱스 보기, 인덱스 삭제가 포함됩니다. 소위 인덱스 생성이란 테이블의 하나의 필드 또는 여러 필드에 인덱스를 생성하는 것입니다. MySQL에서는 일반적으로 테이블을 생성할 때 인덱스를 생성하는 방법, 기존 테이블에 인덱스를 생성하는 방법이 있습니다. 그리고 SQL 문 ALTER를 전달하면 인덱스가 생성됩니다.
2.1 일반 인덱스 생성 및 보기:
소위 일반 인덱스는 인덱스 생성 시 제한(고유, 비어 있지 않음 등)이 첨부되지 않음을 의미합니다. 이 유형의 인덱스는 모든 유형의 필드에 생성될 수 있습니다.
2.1.1 테이블 생성 시 일반 인덱스 생성:

구문 형식은 다음과 같습니다.

CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC]) 
);

설명: INDEX 또는 KEY 매개변수는 필드를 인덱스로 지정하는 데 사용되며, "인덱스 이름" 매개변수는 생성된 인덱스의 이름을 지정하는 데 사용되며, "속성 이름 1" 매개변수는 이름을 지정하는 데 사용됩니다. 인덱스와 연관된 필드의 "length" 매개변수는 인덱스의 길이를 지정하는 데 사용되며 "ASC|DESC" 매개변수는 오름차순 또는 내림차순을 지정하는 데 사용됩니다.
참고: 인덱스를 생성할 때 인덱스 길이를 지정할 수 있습니다. 이는 서로 다른 스토리지 엔진이 테이블의 최대 인덱스 수와 최대 인덱스 길이를 정의하기 때문입니다. MySQL이 지원하는 스토리지 엔진은 각 테이블에 대해 최소 16개의 인덱스를 지원하며, 총 인덱스 길이는 최소 256바이트입니다.

예:

CREATE TABLE t_dept(
 deptno INT,
 dname VARCHAR(30),
 loc VARCHAR(40),
 INDEX index_deptno (deptno)
);

설명:
SHOW CREATE TABLE t_dept G
를 통해 인덱스가 성공적으로 생성되었는지 확인할 수 있습니다. EXPLAIN SELECT * FROM t_dept WHERE deptno=1G;를 사용하여 데이터베이스 테이블의 인덱스가 사용되는지 확인할 수 있습니다. 실행 결과의 available_keys 및 key 필드의 값이 모두 생성된 인덱스 이름 index_deptno인 경우 해당 인덱스가 이미 존재하여 활성화되었음을 의미합니다.

2.1.2 기존 테이블에 일반 인덱스 생성:

SQL 문 CREATE INDEX를 통해 달성되는 구문은 다음과 같습니다.
CREATE INDEX 인덱스 이름
ON 테이블 이름(속성 이름 [(길이)] [ADC|DESC])

2.1.3 SQL문 ALTER TABLE을 통해 일반 인덱스 생성:

문법적 형식은 다음과 같습니다.
ALTER TABLE 테이블_이름 ADD INDEX|KEY 인덱스 이름(속성 이름 [(길이)] [ASC|DESC]);

2.2 고유 인덱스 생성 및 보기:
소위 고유 인덱스는 인덱스를 생성할 때 제한된 인덱스의 값이 고유해야 함을 의미합니다. 이 유형의 인덱스는 레코드를 더 빠르게 쿼리하는 데 도움이 될 수 있습니다. MySQL에서는 인덱스를 생성하는 방법에 따라 자동 인덱싱과 수동 인덱싱의 두 가지 유형으로 나눌 수 있습니다.
소위 자동 인덱싱이란 데이터베이스 테이블에 무결성 제약 조건이 설정되면 시스템이 해당 테이블을 자동으로 인덱싱하는 것을 의미합니다. 수동 인덱스란 테이블에 인덱스를 수동으로 생성하는 것을 말합니다. 테이블의 필드가 기본 키 또는 고유 무결성 제약 조건으로 설정되면 시스템은 해당 필드와 연결된 고유 인덱스를 자동으로 생성합니다.

2.2.1 테이블 생성 시 고유 인덱스 생성:

문법적 형식은 다음과 같습니다.

CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 UNIQUE INDEX|KEY [索引名] (属性名1 [(长度)] [ASC | DESC])
);

설명: UNIQUE INDEX 또는 UNIQUE KEY는 고유한 인덱스를 생성하는 것을 의미합니다.

2.2.2 기존 테이블에 고유 인덱스 생성:

SQL 문 CREATE UNIQUE INDEX를 통해 달성되는 구문은 다음과 같습니다.
고유 인덱스 인덱스 이름 생성
ON 테이블 이름(속성 이름 [(길이)] [ASC|DESC]);

2.2.3 SQL 문 ALTER TABLE을 통해 고유 인덱스 생성:

문법적 형식은 다음과 같습니다.
ALTER TABLE 테이블_이름 ADD UNIQUE INDEX|KEY 인덱스 이름(속성 이름 [(길이)] [ASC|DESC]);

2.3 创建和查看全文索引：
全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能更加快速的查询数据量较大的字符串类型的字段。MySQL只能在存储引擎为MyISAM的数据库表上创建全文引擎。默认情况下，全文引擎的搜索执行方式为不区分大小写，如果全文引擎所关联的字段为二进制数据类型，则以区分大小写的方式执行。

2.3.1 创建表时创建全文索引：

语法形式为：

CREATE TABLE table_name (
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 FULLTEXT INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC])
);

2.3.2 在已经存在的表上创建全文索引：

语法形式为：
CREATE FULLTEXT INDEX 索引名
ON 表名 (属性名 [(长度)] [ASC|DESC]);

2.3.3 通过SQL语句ALTER TABLE 创建全文索引：

语法形式为：
ALTER TABLE table_name
ADD FULLTEXT INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

2.4 创建和查看多列索引：
所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
2.4.1 创建表时创建多列索引：

语法形式如下：

CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 INDEX|KEY [索引名] 
 (属性名1 [(长度)] [ASC|DESC]),
 ......
 (属性名1 [(长度)] [ASC|DESC])
);

上述语句创建索引时，所关联的字段至少大于一个字段。

2.4.2 在已经存在的表上创建多列索引：

语法形式为：

CREATE INDEX 索引名 ON 表名 (
 属性名 [(长度)] [ASC|DESC],
 ......
 属性名n [(长度)] [ASC|DESC]
); 

2.4.3 通过SQL语句ALTRE TABLE 创建多列索引：

语法形式为：
ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名 [(长度)] [ASC|DESC],属性名n [(长度)] [ASC|DESC]);

3. 删除索引：

删除索引的语法形式：
DROP INDEX index_name ON table_name

4. 查看索引：

查看索引的语法形式：
SHOW INDEX FROM table_name

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。