1. 인덱스 소개
(1) 인덱스의 의미와 구체성
(2) 인덱스 분류
(3) 인덱스 설계 원칙
2. 인덱스 생성
(1) 테이블 생성 시 인덱스 생성
( 2) 기존 테이블에 인덱스 생성
(3) 인덱스 삭제
(무료 학습 추천: mysql 동영상 튜토리얼 )
1. 인덱스 소개
인덱스는 인덱스가 있는지 빠르게 알아내는 데 사용됩니다. 값의 열 행에 있는 특정 값. 인덱스를 사용하지 않으면 MySQL은 관련 행을 찾을 때까지 첫 번째 레코드부터 시작하여 전체 테이블을 읽어야 합니다. 테이블이 클수록 데이터를 쿼리하는 데 더 많은 시간이 걸립니다. 테이블의 쿼리된 열에 인덱스가 있으면 MySQL은 모든 데이터를 볼 필요 없이 데이터 파일을 검색할 위치로 빠르게 이동할 수 있습니다.
인덱스는 스토리지 엔진에서 구현되므로 각 스토리지 엔진의 인덱스가 반드시 동일할 필요는 없으며 각 스토리지 엔진이 반드시 모든 인덱스 유형을 지원하는 것은 아닙니다. 스토리지 엔진에 따라 각 테이블의 최대 인덱스 수와 최대 인덱스 길이를 정의합니다. 모든 스토리지 엔진은 테이블당 최소 16개의 인덱스를 지원하며, 총 인덱스 길이는 최소 256바이트입니다. 대부분의 스토리지 엔진에는 더 높은 제한이 있습니다.
MySQL에는 두 가지 스토리지 유형이 있습니다.
이는 특히 테이블의 스토리지 엔진과 관련이 있습니다. MyISAM 및 InnoDB 스토리지 엔진은 MEMORY/HEAL 스토리지 엔진만 HASH 및 BTREE 인덱스를 지원할 수 있습니다.BTREE和HASH인덱스의 장점:
2. 데이터 쿼리 속도를 크게 높일 수 있습니다. (인덱스를 생성하는 주된 이유)
3. 데이터의 참조 무결성 달성 측면에서 테이블과 테이블 간의 연결 속도를 높일 수 있습니다.
4. 데이터 쿼리에 그룹화 및 정렬 절을 사용하면 쿼리에서 그룹화 및 정렬하는 시간도 크게 줄일 수 있습니다.
인덱스 추가의 단점:
2. 인덱스는 데이터 테이블이 차지하는 데이터 공간 외에 각 인덱스도 일정량의 물리적 공간을 차지합니다. 인덱스 수가 많은 경우 인덱스 파일이 최대 파일 크기에 더 빨리 도달할 수 있습니다. 데이터 파일보다
3. 테이블의 데이터를 추가, 삭제, 수정하는 경우에도 인덱스를 동적으로 유지해야 하므로 데이터 유지 속도가 저하됩니다.
(2) 인덱스 분류
구문 형식:
create table table_name [col_name date_type][unique|fulltext|spatial] [index|key] [index_name] (col_name [length]) [asc | desc]
①일반 인덱스 생성
일반 인덱스는 고유성 등의 제한이 없는 가장 기본적인 인덱스 종류로, 데이터에 대한 접근 속도를 높이는 역할만 한다.
[예제 1] book 테이블의 year_publication 필드에 일반 인덱스를 생성합니다. SQL 문은 다음과 같습니다.
mysql> create table book -> ( -> bookid int not null, -> bookname varchar(255) not null, -> authors varchar(255) not null, -> info varchar(255) null, -> comment varchar(255) null, -> year_publication year not null, -> index(year_publication) -> );Query OK, 0 rows affected (0.21 sec)mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `year_publication` (`year_publication`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from book where year_publication=1990 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: book partitions: NULL type: ref possible_keys: year_publication key: year_publication key_len: 1 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)
explan 문 출력 결과의 각 라인에 대한 설명은 다음과 같습니다.
possible_key와 key의 값이 year_publication이고, 인덱스가 쿼리에 사용되는 것을 볼 수 있습니다.
②고유 인덱스 만들기
고유 인덱스를 만드는 주된 이유는 특히 상대적으로 큰 데이터 테이블의 경우 인덱스 열 쿼리 작업의 실행 시간을 줄이기 위한 것입니다. 인덱스 컬럼의 값이 유일해야 한다는 점을 제외하면 이전의 일반 인덱스와 유사하지만, null 값은 허용된다. 복합 인덱스의 경우 컬럼 값의 조합이 고유해야 합니다.
【예제 2】테이블 t1을 생성하고 고유 키워드를 사용하여 테이블의 id 필드에 고유 인덱스를 생성합니다.
mysql> create table t1 -> ( -> id int not null -> ,name char(30) not null, -> unique index uniqidx(id) -> );Query OK, 0 rows affected (0.27 sec)mysql> show create table t1 \G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, UNIQUE KEY `uniqidx` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
3단일 열 인덱스 만들기
단일 열 인덱스는 데이터 테이블의 특정 필드에 생성되는 인덱스입니다. 하나의 테이블에 여러 개의 단일 열 인덱스를 생성할 수 있습니다.
【예제 3】테이블 t2를 생성하고 테이블의 이름 필드에 단일 열 인덱스를 생성합니다.
mysql> create table t2 -> ( -> id int not null, -> name char(50) null, -> index singleidx(name(20)) -> );Query OK, 0 rows affected (0.06 sec)mysql> show create table t2 \G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `name` char(50) DEFAULT NULL, KEY `singleidx` (`name`(20))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)
결과에서 볼 수 있듯이 SingleIdx라는 단일 열 인덱스가 id 필드에 인덱스 길이가 20으로 성공적으로 설정되었습니다.
4복합 인덱스 만들기
복합 인덱스는 여러 필드에 인덱스를 만드는 것입니다.
[예제 4] 테이블 t3을 생성하고 테이블의 id, name, age 필드에 결합 인덱스를 생성합니다. SQL 문은 다음과 같습니다.
mysql> create table t3 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> index mulitiidx(id,name,age) -> );Query OK, 0 rows affected (0.07 sec)mysql> show create table t3 \G*************************** 1. row *************************** Table: t3Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, KEY `mulitiidx` (`id`,`name`,`age`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)mysql> explain select * from t3 where id = 1 and name = 'joe' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 partitions: NULL type: ref possible_keys: mulitiidx key: mulitiidx key_len: 124 ref: const,const rows: 1 filtered: 100.00 Extra: Using index condition1 row in set, 1 warning (0.06 sec)
결합 인덱스는 여러 인덱스 역할을 하지만 사용 시에는 어느 필드가 모두 인덱스를 사용할 수 있는지 쿼리할 수는 없지만 "가장 왼쪽 접두사"를 따릅니다. 인덱스의 가장 왼쪽 열 집합이 행을 일치시키는 데 사용됩니다.
예를 들어, 다음은 id, name, age의 세 가지 필드로 구성된 인덱스입니다. 인덱스 행은 id/nam/age 순서로 정렬됩니다. 인덱스는 필드 조합(id, name, age)을 검색할 수 있습니다. ), (ID, 이름) 또는 ID입니다. 해당 열이 인덱스의 가장 왼쪽 접두사가 아닌 경우 MySQL은 로컬 인덱스를 사용할 수 없습니다. 예를 들어 (나이) 또는 (이름, 나이) 조합은 인덱스 쿼리를 사용할 수 없습니다. id 및 name 필드를 쿼리할 때 multiidx 인덱스를 사용합니다. (name, age)를 조합하여 쿼리하거나 name 및 age 필드를 별도로 쿼리하는 경우 인덱스는 null입니다.
⑤전체 텍스트 색인 생성
전체 텍스트 전체 텍스트 색인은 전체 텍스트 검색에 사용할 수 있습니다. MyISAM 스토리지 엔진만이 전체 텍스트 인덱스를 지원하고 char, varchar 및 text 열에 대한 인덱스만 생성합니다. 인덱싱은 항상 전체 열에 대해 수행되며 로컬(접두사) 인덱스는 지원되지 않습니다.
[예제 5] 테이블 t4를 생성하고 테이블의 정보 필드에 전체 텍스트 인덱스를 생성합니다. SQL 문은 다음과 같습니다.
mysql> create table t4 -> ( -> id int not null, -> name char(30) not null, -> age int not null, -> info varchar(255), -> fulltext index fulltxtidx(info) -> )engine=MyISAM;Query OK, 0 rows affected (0.08 sec)mysql> show create table t4 \G*************************** 1. row *************************** Table: t4Create Table: CREATE TABLE `t4` ( `id` int(11) NOT NULL, `name` char(30) NOT NULL, `age` int(11) NOT NULL, `info` varchar(255) DEFAULT NULL, FULLTEXT KEY `fulltxtidx` (`info`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.06 sec)
전체 텍스트 인덱스는 대규모 데이터 세트에 매우 적합합니다. , 그 사용은 상대적으로 적습니다.
6. 공간 인덱스 생성
공간 인덱스는 MyISAM 유형 테이블에서 생성되어야 하며 공간 유형 필드는 null이 아니어야 합니다.
[예제 6] 테이블 t5를 생성하고 공간형 기하학을 갖는 필드에 공간 인덱스를 생성한다. SQL문은 다음과 같다.
mysql> create table t5 -> ( g geometry not null,spatial index spatidx(g) ) ENGINE=MyISAM;Query OK, 0 rows affected, 1 warning (0.07 sec)mysql> show create table t5 \G*************************** 1. row *************************** Table: t5Create Table: CREATE TABLE `t5` ( `g` geometry NOT NULL, SPATIAL KEY `spatidx` (`g`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.05 sec)
테이블 t5의 g 필드에 spatIdx라는 공간 인덱스가 생성되는 것을 볼 수 있다. . 생성 시 공간 유형 필드 값의 Null이 아닌 제약 조건을 지정하고 테이블의 스토리지 엔진은 MyISAM입니다.
기존 테이블에 인덱스를 생성하려면 alter table 문을 사용하거나 index 문을 생성할 수 있습니다.
1. 인덱스를 생성하려면 alter table 문을 사용하세요.
기본 구문:
alter table table_name add [unique|fulltext|spatial] [index|key][index_name] (col_name[length],...) [asc |dec]
[예 7] book 테이블의 bookname 필드에 BkNameIdx라는 공통 인덱스를 생성합니다.
인덱스를 추가하기 전에 show index 문을 사용하여 지정된 테이블에 생성된 인덱스를 확인하세요.
mysql> show index from book \G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.10 sec)
其中，各个主要参数的含义为;
可以看到book表中已经存在一个索引，即year_publication索引，该索引为非唯一索引，下面使用alter table 在bookname字段上添加索引，SQL语句如下：
mysql> alter table book add index bknameidx( bookname(30) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G*************************** 1. row *************************** Table: book Non_unique: 1 Key_name: year_publication Seq_in_index: 1 Column_name: year_publication Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 2. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL2 rows in set (0.05 sec)
可以看到表中有了两个索引，另一个为通过alter table语句添加的名称为bknameidx的索引，该索引为非唯一索引，长度为30。
【例8】在book表的bookid字段上建立名称为uniqididx的唯一索引，SQL语句如下：
mysql> alter table book add unique index uniqididx(bookid);Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...*************************** 3. row *************************** Table: book Non_unique: 1 Key_name: bknameidx Seq_in_index: 1 Column_name: bookname Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL3 rows in set (0.01 sec)
可以看到，Non_unique的属性值为0，表示名称为Uniqididx的索引为唯一索引，创建唯一索引成功。
【例9】在book表的comment字段上建立单列索引，SQL语句如下：
mysql> alter table book add index bkcmtidx ( comment(50) );Query OK, 0 rows affected (0.11 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...*************************** 4. row *************************** Table: book Non_unique: 1 Key_name: bkcmtidx Seq_in_index: 1 Column_name: comment Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL4 rows in set (0.01 sec)
可以看到，语句执行之后再book表的comment字段上建立了名称为bkcmtidx的索引，长度为50，在查询时，只需要检索前50个字符。
【例10】在book表的authors和info字段上建立组合索引，SQL语句如下：
mysql> alter table book add index bkauandinfoidx (authors(30),info(50) );Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from book \G1...2...3...4...*************************** 5. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 1 Column_name: authors Collation: A Cardinality: 0 Sub_part: 30 Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL*************************** 6. row *************************** Table: book Non_unique: 1 Key_name: bkauandinfoidx Seq_in_index: 2 Column_name: info Collation: A Cardinality: 0 Sub_part: 50 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL6 rows in set (0.06 sec)
可以看到名称为bkauandinfoidx的索引由两个字段组成，authors字段长度为30，在组合索引中的序号为1，该字段不允许空值null；info字段长度为50，在组合索引中的序号为2，该字段可以为空值null。
【例11】创建表t6，在t6表上使用alter table创建全文索引，SQL语句如下：
mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t6 add fulltext index infofiidx( info );Query OK, 0 rows affected (0.13 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show index from t6 \G*************************** 1. row *************************** Table: t6 Non_unique: 1 Key_name: infofiidx Seq_in_index: 1 Column_name: info Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: YES Index_type: FULLTEXT Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.05 sec)
可以看到，t6表中已经创建了名称为infoftidx的索引，该索引在info字段上创建，类型为fulltext，允许空值。
【例12】创建表t7，t7的空间类型字段g上创建名称为spatidx的空间索引，SQL语句如下：
mysql> create table t7(g geometry not null)ENGINE=MyISAM;Query OK, 0 rows affected (0.07 sec)mysql> alter table t7 add spatial index spatidx(g);Query OK, 0 rows affected, 1 warning (0.06 sec)Records: 0 Duplicates: 0 Warnings: 1mysql> show index from t7 \G*************************** 1. row *************************** Table: t7 Non_unique: 1 Key_name: spatidx Seq_in_index: 1 Column_name: g Collation: A Cardinality: NULL Sub_part: 32 Packed: NULL Null: Index_type: SPATIAL Comment: Index_comment: Visible: YES Expression: NULL1 row in set (0.01 sec)
可以看到，t7表的g字段上创建了名为spatidx的空间索引。
2.使用create index 创建索引
create index 语句可以在已经存在的表上添加索引，MySQL中create index被映射到一个alter table语句上，基本语法为：
create [unique|fulltext|spatial] index index_nameon table_name (col_name[length],...) [asc|desc]
可以看到create index语句和alter index语句的语法基本一样，只是关键字不同，使用相同的表book，假设该表中没有任何索引值，创建book表语句如下：
create table book(bookid int not null,bookname varchar(255) not null,authors varchar(255) not null,info varchar(255) null,comment varchar(255) null,year_publication year not null);
【例13】在book表的bookname字段上建立名为BkNameIdx的普通索引，SQL语句如下：
mysql> create index BkNameOdx on book(bookname);Query OK, 0 rows affected (0.10 sec)Records: 0 Duplicates: 0 Warnings: 0
【例14】在book表的bookid字段上建立名为UniqidIdx的唯一索引，SQL语句如下：
mysql> create unique index UniqiiIdx on book(bookid);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
【例15】在book表的comment字段上建立单列索引，SQL语句如下：
mysql> create index BkcmtIdx on book(bookid);Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0
【例16】在book表的authors和info字段上建立组合索引，SQL语句如下：
mysql> create index BkAuAndInfoIdx on book (authors(20),info(50));Query OK, 0 rows affected (0.09 sec)Records: 0 Duplicates: 0 Warnings: 0
【例17】删除表t6，重新建立表t6，在t6表中使用create index 语句，在char类型的info字段上创建全文索引。
mysql> drop table t6;Query OK, 0 rows affected (0.02 sec)mysql> create table t6 -> ( -> id int not null, -> info char(255) -> )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create fulltext index infoftidx on t6(info);Query OK, 0 rows affected (0.07 sec)Records: 0 Duplicates: 0 Warnings: 0
【例18】删除表t7，重新创建表t7，在t7表中使用create index语句，在空间数据类型字段g上创建名称为spatIdx的空间索引。
mysql> drop table t7;Query OK, 0 rows affected (0.06 sec)mysql> create table t7 (g geometry not null )ENGINE=MyISAM;Query OK, 0 rows affected (0.06 sec)mysql> create spatial index spatIdx on t7 (g);Query OK, 0 rows affected, 1 warning (0.07 sec)Records: 0 Duplicates: 0 Warnings: 1
MySQL中删除索引使用alter table或者drop index 语句，两者可实现相同的功能，drop index 语句在内部被映射到一个alter table语句中。
1.使用alter table删除索引
alter table 删除索引的基本语法格式:
alter table table_name drop index index_name
【例1】删除book表中的名称为UniqidIdx的唯一索引。
mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, UNIQUE KEY `UniqiIdx` (`bookid`), KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`), KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)mysql> alter table book drop index UniqiIdx;Query OK, 0 rows affected (0.19 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`), KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到，book表中已经没有名称为UniqidIdx的唯一索引，删除索引成功。
注意：添加auto_increment约束字段的唯一索引不能被删除。
2.使用drop index 语句删除索引
drop index语句删除索引的基本语法格式：
drop index inde _name on table_name
【例2】删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：
mysql> drop index BkAuAndInfoIdx on book;Query OK, 0 rows affected (0.08 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table book \G*************************** 1. row *************************** Table: bookCreate Table: CREATE TABLE `book` ( `bookid` int(11) NOT NULL, `bookname` varchar(255) NOT NULL, `authors` varchar(255) NOT NULL, `info` varchar(255) DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `year_publication` year(4) NOT NULL, KEY `BkNameOdx` (`bookname`), KEY `BkcmtIdx` (`bookid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。
注意：删除表中的列时，如果要删除的列为索引的组成部分，则该部分也会从索引中删除。如果组成索引的所有列都被删除，那么整个索引将被删除。
相关免费学习推荐：mysql数据库(视频)
위 내용은 MySQL 인덱스에 대한 지식의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
