MySQL中key与index详细介绍

来源：这里教程网时间：2026-02-28 10:36:33 作者：

一、概述

1、基本概念

（1）key是数据库的物理结构，有两层作用，一层是约束作用（constraint），用于约束数据的唯一性、完整性；一层是索引作用，用来建立索引，优化查询速度，与index作用相同。

（2）普通key：没有约束作用，但会在此key上建立一个index。

（3）primary key：主键；一个表可以有一个主键，主键分为单一主键（只包含一列）和复合主键（也叫联合主键，可以包含多列）；可以规定一个存储主键，并规范数据的唯一性；同时会在此key上建立一个index。主键并不是必须的，但是强烈建议的【使用主键几个好习惯：不更改、不重用】

（4）unique key：唯一键；规范数据的唯一性；同时会在此key上建立一个index。

（5）foreign key：外键；规范数据的引用完整性；同时会在此key上建立一个index。

（6）index：key作用的一个维度，在有些时候可以代替关键字key。

2、primary key与unique key

（1）相同点：唯一性约束

（2）不同点

1）出发点/作用不同：前者是一行数据的唯一标识，后者只是用来避免数据重复。
2）前者的一个列或多个列必须全部为not null；如果其中一个列为null，在添加为主键时，会变为not null，如果再删除主键，列的nullable性质会变回去。后者的列可以为null。
3）一个表只能有一个primary key，可以有多个unique key。【一个表可以没有primary key吗？？？】
4）对于unique key对应的列，可以多次插入null（虽然也是一种重复）；这是由索引的原理，即索引对null的处理决定的。

二、语法

1、创建时添加-字段级

（1）普通key：create table t (id int not null key);

（2）primary key：create table t (id int not null primary key);二者作用相同，即指明key也是指定primary key，且在一个表中都只能指定一次（不能通过指定多次来当做联合主键）

（3）unique key：create table t (id int not null unique key);

（4）foreign key：应该是不行

（5）index：所有的key不可以换位index

2、创建时添加-表级

（1）普通key：与字段级指定不同，这里的普通key不再与primary key相同，即便没有指定primary key，MySQL也不会将key作为primary key使用。

（2）primary key

（3）unique key

（4）foreign key【个人认为，所谓创建两个key，是逻辑上的两个层面，即数据完整性约束和索引优化】

（5）index：key和unique key（表级）中的key可以换位index，作用一样。

3、创建后

（1）添加键：add，举例如下：alter table t add primary key(id);

（2）删除键，drop，primary key使用alter table t drop primary key;其他key使用名字进行drop即可，注意删除键和删除列的区别。

4、查看信息：show create table table_name;可以查看表的各种属性，包括键属性、存储引擎、字符集、分区情况等。

三、外键

1、作用：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

2、支持外键的存储引擎：InnoDB、Memory验证支持，其他未验证。

3、完整语法

（1）[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...) REFERENCES tbl_name (index_col_name, ...)

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

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

（2）使用：该语法可以在create table和alter table时使用

（3）CONSTRAINT symbol指定键的名字，如果没有指定，则自动生成

（4）on delete和on update表示事件触发设置，可设参数：

4、示例

（1）创建表格，设置外键，并插入数据

<span class="pln">CREATE TABLE <span class="str">`dage`<span class="pln"> <span class="pun">(</span></span></span></span>

<span class="pln"> <span class="str">`id`<span class="pln"> <span class="kwd">int<span class="pun">(<span class="lit">11<span class="pun">)<span class="pln"> NOT NULL auto_increment<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> <span class="str">`name`<span class="pln"> varchar<span class="pun">(<span class="lit">32<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> <span class="str">''<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> PRIMARY KEY <span class="pun">(<span class="str">`id`<span class="pun">)</span></span></span></span>

<span class="pun">);</span>

<span class="pln">CREATE TABLE <span class="str">`xiaodi`<span class="pln"> <span class="pun">(</span></span></span></span>

<span class="pln"> <span class="str">`id`<span class="pln"> <span class="kwd">int<span class="pun">(<span class="lit">11<span class="pun">)<span class="pln"> NOT NULL auto_increment<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> <span class="str">`dage_id`<span class="pln"> <span class="kwd">int<span class="pun">(<span class="lit">11<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> NULL<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> <span class="str">`name`<span class="pln"> varchar<span class="pun">(<span class="lit">32<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> <span class="str">''<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> PRIMARY KEY <span class="pun">(<span class="str">`id`<span class="pun">),</span></span></span></span>

<span class="pln"> KEY <span class="str">`dage_id`<span class="pln"> <span class="pun">(<span class="str">`dage_id`<span class="pun">),</span></span></span></span></span></span>

<span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">)</span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pun">);</span>

<span class="pln">insert <span class="kwd">into<span class="pln"> dage<span class="pun">(<span class="pln">name<span class="pun">)<span class="pln"> values<span class="pun">(<span class="str">'铜锣湾'<span class="pun">);</span></span></span></span></span></span></span></span></span></span>

<span class="pln">insert <span class="kwd">into<span class="pln"> xiaodi<span class="pun">(<span class="pln">dage_id<span class="pun">,<span class="pln">name<span class="pun">)<span class="pln"> values<span class="pun">(<span class="lit">1<span class="pun">,<span class="str">'铜锣湾_小弟A'<span class="pun">);</span></span></span></span></span></span></span></span></span></span></span></span></span></span>

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

<span class="pun">[<span class="pln">SQL<span class="pun">]<span class="pln"> <span class="kwd">delete<span class="pln"> <span class="kwd">from<span class="pln"> dage <span class="kwd">where<span class="pln"> id<span class="pun">=<span class="lit">1<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pun">[<span class="typ">Err<span class="pun">]<span class="pln"> <span class="lit">1451<span class="pln"> <span class="pun">-<span class="pln"> <span class="typ">Cannot<span class="pln"> <span class="kwd">delete<span class="pln"> <span class="kwd">or<span class="pln"> update a parent row<span class="pun">:<span class="pln"> a foreign key constraint fails <span class="pun">(<span class="str">`sample`<span class="pun">.<span class="str">`xiaodi`<span class="pun">,<span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">))</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

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

<span class="pun">[<span class="pln">SQL<span class="pun">]<span class="pln"> insert <span class="kwd">into<span class="pln"> xiaodi<span class="pun">(<span class="pln">dage_id<span class="pun">,<span class="pln">name<span class="pun">)<span class="pln"> values<span class="pun">(<span class="lit">2<span class="pun">,<span class="str">'旺角_小弟A'<span class="pun">);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pun">[<span class="typ">Err<span class="pun">]<span class="pln"> <span class="lit">1452<span class="pln"> <span class="pun">-<span class="pln"> <span class="typ">Cannot<span class="pln"> add <span class="kwd">or<span class="pln"> update a child row<span class="pun">:<span class="pln"> a foreign key constraint fails <span class="pun">(<span class="str">`sample`<span class="pun">.<span class="str">`xiaodi`<span class="pun">,<span class="pln"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pln"> FOREIGN KEY <span class="pun">(<span class="str">`dage_id`<span class="pun">)<span class="pln"> REFERENCES <span class="str">`dage`<span class="pln"> <span class="pun">(<span class="str">`id`<span class="pun">))</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

（4）修改事件触发设置

<span class="pln">show create table xiaodi<span class="pun">;#查看键名称</span></span>

<span class="pln">alter table xiaodi drop foreign key xiaodi_ibfk_1<span class="pun">;</span></span>

<span class="pln">alter table xiaodi add foreign key<span class="pun">(<span class="pln">dage_id<span class="pun">)<span class="pln"> references dage<span class="pun">(<span class="pln">id<span class="pun">)<span class="pln"> on <span class="kwd">delete<span class="pln"> cascade on update cascade<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span>

（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列。

<span class="pln">CREATE TABLE <span class="typ">People<span class="pln"> <span class="pun">(</span></span></span></span>

<span class="pln"> last_name varchar<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> first_name varchar<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span>

<span class="pln"> dob date <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span>

<span class="pln"> gender <span class="kwd">enum<span class="pun">(<span class="str">'m'<span class="pun">,<span class="pln"> <span class="str">'f'<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">,</span></span></span></span></span></span></span></span></span></span></span></span></span>

<span class="pln"> key<span class="pun">(<span class="pln">last_name<span class="pun">,<span class="pln"> first_name<span class="pun">,<span class="pln"> dob<span class="pun">)</span></span></span></span></span></span></span></span>

<span class="pun">);</span>

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

（4）限制

4、Hash索引

（1）概述

（2）限制

（3）示例

<span class="pln">CREATE TABLE testhash <span class="pun">(</span></span><span class="pln">   fname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,</span></span></span></span></span></span><span class="pln">   lname VARCHAR<span class="pun">(<span class="lit">50<span class="pun">)<span class="pln"> NOT NULL<span class="pun">,</span></span></span></span></span></span><span class="pln">   <em><span style="color: #000000">KEY USING HASH</span></em><em><span style="color: #000000"><span class="pun">(<span class="pln">fname<span class="pun">)</span></span></span></span></em></span><span class="pun">)<span class="pln">ENGINE<span class="pun">=<span class="pln">MEMORY<span class="pun">;</span></span></span></span></span>5、其他索引
（1）空间(R-Tree)索引：MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。
（2）全文(Full-text)索引：全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。