MySQL의 키와 인덱스에 대한 자세한 소개-MySQL 튜토리얼-php.cn

1. 개요

1. 기본 개념

(1) 키는 두 가지 기능 계층으로 구성됩니다. 하나는 제약 조건(제약)으로, 고유성과 무결성을 제한하는 데 사용됩니다. 다른 레이어는 인덱스를 생성하고 쿼리 속도를 최적화하는 데 사용되는 인덱스 기능입니다.

(2) 일반 키: 제약 효과는 없지만 이 키에 인덱스가 설정됩니다.

(3) 기본 키: 기본 키는 단일 기본 키(단 하나의 열만 포함)와 복합 기본 키(결합 기본 키라고도 함)로 구분되는 기본 키를 가질 수 있습니다. 여러 열), 스토리지 기본 키를 지정하고 표준화할 수 있습니다. 동시에 이 키에 대한 인덱스가 설정됩니다. 기본 키는 필수는 아니지만 강력히 권장됩니다. [기본 키 사용에 대한 몇 가지 좋은 습관: 변경하지 않고 재사용하지 않음]

(4) 고유 키: 고유 키는 동시에 데이터의 고유성을 표준화합니다. 이 키에 대한 색인이 설정됩니다.

(5) 외래 키: 외래 키는 데이터의 참조 무결성을 표준화하는 동시에 이 키에 대한 인덱스가 설정됩니다.

(6) 인덱스: 키의 차원으로, 때로는 키워드 키를 대체할 수 있습니다.

2. 기본 키와 고유 키

(1) 유사성: 고유성 제약 조건

(2) 차이점

1) 다른 시작점/기능: 전자는 데이터 행의 고유 식별자이고 후자는 데이터 중복을 피하십시오.
2) 전자의 하나 이상의 열은 모두 null이 아니어야 합니다. 열 중 하나가 null인 경우 기본 키를 추가하면 null이 아닌 열이 됩니다. 뒤쪽에. 후자 열은 null일 수 있습니다.
3) 테이블에는 기본 키가 하나만 있을 수 있으며 고유 키는 여러 개 있을 수 있습니다. [테이블에 기본 키가 없을 수 있나요? ? ? 】
4) 고유 키에 해당하는 열에 대해 null을 여러 번 삽입할 수 있습니다(일종의 반복이기도 함). 이는 인덱스의 null 처리 원칙에 따라 결정됩니다.

2. 구문

1. 생성 시 추가 - 필드 수준

(1) 일반 키: 테이블 생성 t(id int null 키 아님)

(2) 기본 키: 테이블 생성 t (id int null 기본 키가 아님) 두 개는 동일한 기능을 갖습니다. 즉, 키를 지정하면 기본 키도 지정되며 테이블에서 한 번만 지정할 수 있습니다(공동 기본 키로 여러 번 지정할 수 없습니다). )

(3) 고유 키: 테이블 t 생성(id int null 고유 키가 아님);

(4) 외래 키: 작동하지 않아야 합니다

(5) 인덱스: 모든 키를 인덱스로 바꿀 수 없습니다

2. 생성 시 추가 - 테이블 수준

(1) 일반 키: 필드 수준 사양과 달리 여기의 일반 키는 기본 키를 지정하지 않아도 더 이상 기본 키와 동일하지 않습니다. 해당 키를 기본 키로 사용합니다.

create table t(id int, key (id)); id를 사용하는 다른 키(예: 외래 키)가 있는 경우 이름을 지정하려면 id를 사용하세요. 여러 열을 동시에 키로 지정하는 경우 첫 번째 열 이름이 키 이름으로 사용됩니다.

create table t(id int, key kismet(id)); 키 이름 지정

constraint: 사용할 수 없습니다. 결국 일반 키에는 제약 효과가 없습니다

( 2) 기본 키를 실행할 수는 있지만 이름이 작동하지 않습니다.

create table t(id int, Constraint kismet Primary key(id))를 실행할 수 있지만 이름이 작동하지 않습니다

(3) Unique key

create table t(id int, Unique key(id)); 키 이름으로 첫 번째 열만 사용됩니다.

create table t(id int, Unique key kismet(id)); 키 이름을 지정하세요

create table t(id int, Constraint kismet Unique key(id))를 지정하세요. 키 이름

(4) 외래 키 [제 생각에는 소위 두 개의 A 키 생성이 두 가지 논리 수준, 즉 데이터 무결성 제약 조건 및 인덱스 최적화]

create table t(id int, 외래 키(dage_id) 참조 dage(id))를 실행할 수 있으며, 실행 결과 자동으로 이름이 지정된 외래 키와 자동으로 이름이 지정된 일반 키가 생성됩니다.

create table t(id int, foreign key kismet(dage_id) reference dage(id))를 실행할 수 있으며, 실행 결과는 자동으로 이름이 지정된 외래 키와 kismet이라는 일반 키가 생성됩니다.

create table t(id int, Constraint kismet foreign key(dage_id) reference dage(id))를 실행할 수 있으며, 실행 결과 kismet이라는 외래 키와 kismet이라는 일반 키가 생성됩니다.

(5) 인덱스: 키에 있는 키와 고유 키(테이블 수준)를 인덱스로 전치할 수 있으며 효과는 동일합니다.

3. 생성 후

(1) 키 추가: 예: alter table t add 기본 키(id);

(2) 키 삭제, 삭제, 기본 키는 alter table t 기본 키 삭제를 사용합니다. 삭제할 이름을 사용하세요. 키 삭제와 열 삭제의 차이점에 주의하세요.

4. 정보 보기: show create table table_name; 키 속성, 스토리지 엔진, 문자 세트, 파티션 상태 등을 포함한 테이블의 다양한 속성을 볼 수 있습니다.

3. 외래 키

1. 기능: 두 테이블을 연결하고 데이터 일관성을 보장하며 일부 계단식 작업을 구현할 수 있습니다.

2 외래 키를 지원하는 스토리지 엔진: InnoDB, 메모리 확인이 지원됩니다. , 다른 것들은 확인되지 않았습니다.

3. 완전한 구문

(1) [CONSTRAINT 기호] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...)

[ON DELETE {RESTRICT | CASCADE | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE SET NULL | NO ACTION SET DEFAULT}]

(2) 사용법: 테이블을 생성하고 변경할 때 사용할 수 있습니다

(3 ) CONSTRAINT 기호는 키 이름을 지정하지 않으면 삭제 및 업데이트 시 자동으로 생성됩니다.

RESTRICT(외래 키 제한) 모양 변경, 기본값)

CASCADE (외래 키 변경 사항 따르기)

SET NULL (NULL 값 설정)

SET DEFAULT (기본값 설정)

NO ACTION(액션 없음)

4. 예시

(1) 테이블 생성, 외래 키 설정 및 데이터 삽입

테이블 만들기 `dage` ( 스팬>
 `id` int(11) NULL이 아닙니다 auto_increment,
 `name` varchar(32) 기본값 '', span>CREATE TABLE `dage` (

 `id` int(11) NOT NULL auto_increment,

 `name` varchar(32) default '',

 PRIMARY KEY (`id`)

);

CREATE TABLE `xiaodi` (

 `id` int(11) NOT NULL auto_increment,

 `dage_id` int(11) default NULL,

 `name` varchar(32) default '',

 PRIMARY KEY (`id`),

 KEY `dage_id` (`dage_id`),
 기본 키 (`id`)

);🎜
테이블 만들기 `xiaodi` ( 스팬>🎜
 `id` int(11) NULL이 아닙니다 auto_increment,🎜 `dage_id` int(11) 기본 NULL, 🎜
 `이름 ` varchar(32) 기본값 '', 🎜
 기본 키 (`id`), 🎜🎜<code class="언어-sql"> 키 `dage_id` (`dage_id`), 스팬>코드>🎜<div class="L1"> <code class="언어-sql"> 제약 조건 `xiaodi_ibfk_1` FOREIGN KEY ( `dage_id`) 참조 `dage` (`id`) CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)

);

insert into dage(name) values('铜锣湾');

insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');

（2）如果在还有小弟的情况下删除大哥，结果如下

[SQL] delete from dage where id=1;

[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

（3）如果想在没有建立大哥的情况下，强行插入小弟，结果如下

[SQL] insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');

[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))

（4）修改事件触发设置

show create table xiaodi;#查看键名称
);

날짜(이름) 값('铜锣湾'); 스팬>🎜
삽입 xiaodi(dage_id,이름) 값(1,'铜锣湾_작은A');🎜🎜🎜
(2)如果在还無弟的情况下删除大哥，结果如下🎜🎜
[SQL] 삭제 날짜 여기서 ID=1; span>🎜🎜[오류] 1451 - 할 수 없음 삭제 또는 상위 행 업데이트: 외래 키 제약 조건이 실패합니다 (`sample`.`xiaodi`, 제약 조건 `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) 참조 `dage` (`id`)) 스팬> 🎜🎜🎜
(3)如果想在没有建立大哥的情况下，强行插入小弟，结果如下🎜🎜

[SQL] 를 xiaodi(에 삽입 dage_id,이름) 값(2,'旺角_小弟A') span>🎜🎜[오류] 1452 - 할 수 없음 하위 행 추가 또는 업데이트: 외래 키 제약 조건이 실패합니다 (`sample`.`xiaodi` , 제약 조건 `xiaodi_ibfk_1` FOREIGN KEY (`dage_id `) 참조 `dage` (`id`)) 스팬>🎜🎜🎜(4)修改事件触发设置🎜🎜

샤오디 테이블 생성 표시;#查看键name称🎜
alter table xiaodi drop foreign key xiaodi_ibfk_1;

alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;

（5）如果在还有小弟的情况下删除大哥：大哥和大哥对应的小弟一起被删除；如果想在没有建立大哥的情况下，强行插入小弟，结果并不变，即失败。

四、索引【参考：】

1、索引入门

（1）作用：索引对查询的速度有着至关重要的影响。如果没有索引，查询将对整个表进行扫描；如果有索引，查询只对索引进行。由于数据库的数据不在内存中，每次查询都需要将数据由硬盘调入内存，IO将浪费大量时间。考虑到索引比数据小的多，使用索引可以大幅提高查询速度；尤其是在数据量大时。

（2）索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。目前最常用的存储引擎是InnoDB。

2、选择索引的数据类型：MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。通常来说，可以遵循以下一些指导原则【（1）（2）条不适用于哈希索引】：

（1）越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

（2）简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。注意，对于索引，能用整型，就不要用字符串，尤其是在数据量大的时候；整型的一个弊端是，与客户端的配合可能需要一些额外的工作(尤其是大整型)，但是对效率几乎没有影响。

（3）尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

3、B-tree索引：结果为B-tree（平衡二叉树）

（1）概述：索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询。如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL仅能对索引最左边的前缀进行有效的查找。

（2）示例：其索引包含表中每一行的last_name、first_name和dob列。

CREATE TABLE People (

 last_name varchar(50) not null,

 first_name varchar(50) not null,

 dob date not null,

 gender enum('m', 'f') not null,

 키(last_name, 이름, dob) key(last_name, first_name, dob)

);

（3）匹配方式：既可以查找，也可以order by【结果是排序的，因此搜索很快】

1）匹配全值：对索引中的所有列都指定具体的值。

2）匹配最左前缀：你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列。

3）匹配列前缀：例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列。

4）匹配值的范围查询：可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列。

5）匹配部分精确而其它部分进行范围匹配：可以利用索引查找last name为Allen，而first name以字母K开始的人。

6）仅对索引进行查询：如果查询的列都位于索引中，则不需要读取元组的值。

7）如果索引字段为A+B，查询A+C时，会使用A索引吗->会，使用explain可以证实

（4）限制

1）查询必须从索引的最左边的列开始。

2）不能跳过某一索引列。例如，你不能利用索引查找last name为Smith且出生于某一天的人。

3）存储引擎不能使用索引中范围条件右边的列。例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询。

4、Hash索引

（1）概述

1）Hash索引通过哈希函数计算Hash值进行检索，可以查到要查数据的行指针，从而定位数据。

2）Hash值不取决于列的数据类型，一个TINYINT列的索引与一个长字符串列的索引一样大。

3）Memory存储引擎支持非唯一hash索引，如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。

（2）限制

1）由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。

2）不能使用hash索引排序。

3）Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。

4）Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。

（3）示例

CREATE TABLE testhash (

 fname VARCHAR(50) NOT NULL,

 lname VARCHAR(50) NOT NULL,

 KEY USING HASH(fname)

)ENGINE=MEMORY;
) span>

(3) 매칭 방법 : [결과가 정렬되어 있어서 검색이 빠르다]

1) 전체 매칭으로 검색하거나 정렬할 수 있습니다. value : 인덱스의 모든 열에 대해 특정 값을 지정합니다.

2) 가장 왼쪽 접두사 일치: 색인을 사용하여 성이 Allen인 사람을 찾을 수 있습니다. 색인의 첫 번째 열을 사용하면 됩니다.

🎜🎜🎜🎜3) 열 접두어 일치: 예를 들어 색인을 사용하여 성이 J로 시작하는 사람을 찾을 수 있습니다. 이는 색인의 열 1만 사용합니다. 🎜🎜🎜🎜🎜🎜4) 일치하는 값의 범위 쿼리: 인덱스를 사용하면 인덱스의 첫 번째 열만 사용하여 성이 Allen과 Barrymore 사이에 있는 사람을 찾을 수 있습니다. 🎜🎜🎜🎜🎜🎜5) 일치하는 부분은 정확하고 다른 부분은 범위 일치입니다. 색인을 사용하여 성이 Allen이고 이름이 문자 K로 시작하는 사람을 찾을 수 있습니다. 🎜🎜🎜🎜🎜🎜6) 인덱스에서만 쿼리: 쿼리의 열이 모두 인덱스에 있으면 튜플의 값을 읽을 필요가 없습니다. 🎜🎜🎜🎜7) 인덱스 필드가 A+B인 경우 A+C 쿼리 시 A 인덱스가 사용됩니까? -> 설명을 사용하여 확인할 수 있습니다. /span>🎜
(4) 제한 사항

🎜🎜🎜1) 쿼리는 인덱스의 가장 왼쪽 열부터 시작해야 합니다. 🎜🎜🎜🎜🎜🎜2) 인덱스 열은 건너뛸 수 없습니다. 예를 들어, 성이 Smith이고 특정 날짜에 태어난 사람을 찾는 데 인덱스를 사용할 수 없습니다. 🎜🎜🎜🎜🎜🎜3) 스토리지 엔진은 인덱스의 범위 조건 오른쪽에 있는 열을 사용할 수 없습니다. 예를 들어 쿼리 문이 WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'인 경우 LIKE는 범위 쿼리이므로 쿼리는 인덱스의 처음 두 열만 사용합니다. . 🎜🎜🎜
🎜🎜 🎜🎜4. 해시 인덱스 🎜🎜🎜 (1) 개요 🎜🎜🎜
🎜🎜🎜1) 해시 인덱스는 검색용 해시 함수를 통해 해시 값을 계산하여 찾을 수 있습니다. 확인하려는 내용 데이터를 찾기 위한 데이터의 행 포인터입니다. 🎜🎜🎜🎜🎜🎜2) 해시 값은 열의 데이터 유형에 의존하지 않습니다. TINYINT 열의 인덱스는 긴 문자열 열의 인덱스만큼 큽니다. 🎜🎜🎜🎜🎜🎜3) 메모리 저장 엔진은 고유하지 않은 해시 인덱스를 지원합니다. 여러 값에 동일한 해시 코드가 있는 경우 인덱스는 해당 행 포인터를 연결된 목록에 동일한 해시 테이블 항목에 저장합니다. 🎜🎜🎜

(2) 제한 사항

🎜🎜🎜1) 인덱스에는 해시 코드와 레코드 포인터만 포함되어 있으므로 MySQL은 인덱스를 사용하여 레코드를 읽는 것을 피할 수 없습니다. 그러나 메모리의 레코드에 액세스하는 속도는 매우 빠르며 성능에 큰 영향을 미치지 않습니다. 🎜🎜🎜🎜🎜🎜2) 해시 인덱스 정렬을 사용할 수 없습니다. 🎜🎜🎜🎜🎜🎜3) 해시 인덱스는 전체 인덱스 값을 통해 해시 값을 계산하므로 키의 부분 일치를 지원하지 않습니다. 🎜🎜🎜🎜🎜🎜4) 해시 인덱스는 =, IN() 및 <=> 사용과 같은 동등 비교만 지원합니다. WHERE 가격>100의 경우 쿼리 속도가 빨라지지 않습니다. 🎜🎜🎜
🎜🎜 (3) 예 🎜🎜🎜
🎜CREATE TABLE testhash (🎜
 fname VARCHAR(50) NULL이 아님, 🎜
 lname VARCHAR(50) NULL이 아님,🎜
 해시를 사용하는 키(fname)🎜
)엔진=메모리;🎜🎜🎜🎜🎜 🎜🎜5. 기타 인덱스🎜🎜🎜 (1) space (R-Tree) 인덱스: MyISAM은 GEOMETRY와 같은 지리공간 데이터 유형에 주로 사용되는 공간 인덱스를 지원합니다. 🎜🎜 (2) 전체 텍스트 인덱스: 전체 텍스트 인덱스는 MyISAM의 특수 인덱스 유형으로 주로 전체 텍스트 검색에 사용됩니다. 🎜🎜🎜 🎜🎜 🎜🎜

위 내용은 MySQL의 키와 인덱스에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!