当前位置:Gxlcms > mysql > MySQL中key与index详细介绍

MySQL中key与index详细介绍

时间:2021-07-01 10:21:17 帮助过:78人阅读

一、概述
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)创建表格,设置外键,并插入数据
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`),
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;#查看键名称
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,
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;
5、其他索引
(1)空间(R-Tree)索引:MyISAM支持空间索引,主要用于地理空间数据类型,例如GEOMETRY。
(2)全文(Full-text)索引:全文索引是MyISAM的一个特殊索引类型,主要用于全文检索。

以上就是MySQL中key与index详细介绍的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行