MySQL中key与index详细介绍

一、概述

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使用。

create table t(id int, key (id));如果有使用id的其他键（如foreign key），则使用其他键对他的命名；如果都没有命名，则使用id；如果一次指定了多个列作为键，则使用第一个列名作为键名。

create table t(id int, key kismet(id));指定该key的名称

constraint：不能使用，毕竟普通key并没有约束作用

（2）primary key

create table t(id int, primary key (id));

create table t(id int, primary key kismet(id));可以执行，但是名称不起作用

create table t(id int, constraint kismet primary key(id));可以执行，但是名称不起作用

 

（3）unique key

create table t(id int, unique key (id));命名规则与key不同，只使用第一列作为键名

create table t(id int, unique  key kismet(id));指定该key的名称

create table t(id int, constraint kismet unique  key(id));指定该key的名称

 

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

create table t(id int, foreign key (dage_id) references dage(id));可以执行，执行结果为创建了一个自动命名的foreign key和一个自动命名的普通key。

create table t(id int, foreign key kismet(dage_id) references dage(id));可以执行，执行结果为创建了一个自动命名的foreign key和一个名称为kismet的普通key。

create table t(id int, constraint kismet foreign key(dage_id) references dage(id));可以执行，执行结果为创建了一个名称为kismet的foreign key和一个名称为kismet的普通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表示事件触发设置，可设参数：

RESTRICT（限制外表中的外键改动，默认的）

CASCADE（跟随外键改动）

SET NULL（设空值）

SET DEFAULT（设默认值）

NO ACTION（无动作）

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>

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

insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A'<span class="pun">);</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）如果想在没有建立大哥的情况下，强行插入小弟，结果如下

[SQL] insert into xiaodi(dage_id,name) values(2,'旺角_小弟A'<span class="pun">);</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【结果是排序的，因此搜索很快】

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）示例

<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的一个特殊索引类型，主要用于全文检索。

 

 

Atas ialah kandungan terperinci MySQL中key与index详细介绍. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!