집 >데이터 베이스 >MySQL 튜토리얼 >MySQL의 키와 인덱스에 대한 자세한 소개

MySQL의 키와 인덱스에 대한 자세한 소개

PHP中文网원래의: 2017-06-20 14:10:3410669검색

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 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：

이전 기사：csv 가져오기 데이터를 mysql 인스턴스에 공유다음 기사：csv 가져오기 데이터를 mysql 인스턴스에 공유