MySQL中key與index詳細說明
發(fā)表時間：2023-09-02 來源：明輝站整理相關(guān)軟件相關(guān)文章人氣：
[摘要]一、概述1、基本概念（1）key是數(shù)據(jù)庫的物理結(jié)構(gòu)，有兩層作用，一層是約束作用（constraint），用于約束數(shù)據(jù)的唯一性、完整性；一層是索引作用，用來建立索引，優(yōu)化查詢速度，與index作用相同。（2）普通key：沒有約束作用，但會在此key上建立一個index。（3）primary key：...
一、概述
1、基本概念
（1）key是數(shù)據(jù)庫的物理結(jié)構(gòu)，有兩層作用，一層是約束作用（constraint），用于約束數(shù)據(jù)的唯一性、完整性；一層是索引作用，用來建立索引，優(yōu)化查詢速度，與index作用相同。
（2）普通key：沒有約束作用，但會在此key上建立一個index。
（3）primary key：主鍵；一個表可以有一個主鍵，主鍵分為單一主鍵（只包含一列）和復(fù)合主鍵（也叫聯(lián)合主鍵，可以包含多列）；可以規(guī)定一個存儲主鍵，并規(guī)范數(shù)據(jù)的唯一性；同時會在此key上建立一個index。主鍵并不是必須的，但是強烈建議的【使用主鍵幾個好習慣：不更改、不重用】
（4）unique key：唯一鍵；規(guī)范數(shù)據(jù)的唯一性；同時會在此key上建立一個index。
（5）foreign key：外鍵；規(guī)范數(shù)據(jù)的引用完整性；同時會在此key上建立一個index。
（6）index：key作用的一個維度，在有些時候可以代替關(guān)鍵字key。
2、primary key與unique key
（1）相同點：唯一性約束
（2）不同點
1）出發(fā)點/作用不同：前者是一行數(shù)據(jù)的唯一標識，后者只是用來避免數(shù)據(jù)重復(fù)。
2）前者的一個列或多個列必須全部為not null；如果其中一個列為null，在添加為主鍵時，會變?yōu)閚ot null，如果再刪除主鍵，列的nullable性質(zhì)會變回去。后者的列可以為null。
3）一個表只能有一個primary key，可以有多個unique key。【一個表可以沒有primary key嗎？？？】
4）對于unique key對應(yīng)的列，可以多次插入null（雖然也是一種重復(fù)）；這是由索引的原理，即索引對null的處理決定的。
二、語法
1、創(chuàng)建時添加-字段級
（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，且在一個表中都只能指定一次（不能通過指定多次來當做聯(lián)合主鍵）
（3）unique key：create table t (id int not null unique key);
（4）foreign key：應(yīng)該是不行
（5）index：所有的key不可以換位index
2、創(chuàng)建時添加-表級
（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并沒有約束作用
create table t(id int, primary key (id));
create table t(id int, primary key kismet(id));可以執(zhí)行，但是名稱不起作用
create table t(id int, constraint kismet primary key(id));可以執(zhí)行，但是名稱不起作用
create table t(id int, unique key (id));命名規(guī)則與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【個人認為，所謂創(chuàng)建兩個key，是邏輯上的兩個層面，即數(shù)據(jù)完整性約束和索引優(yōu)化】
create table t(id int, foreign key (dage_id) references dage(id));可以執(zhí)行，執(zhí)行結(jié)果為創(chuàng)建了一個自動命名的foreign key和一個自動命名的普通key。
create table t(id int, foreign key kismet(dage_id) references dage(id));可以執(zhí)行，執(zhí)行結(jié)果為創(chuàng)建了一個自動命名的foreign key和一個名稱為kismet的普通key。
create table t(id int, constraint kismet foreign key(dage_id) references dage(id));可以執(zhí)行，執(zhí)行結(jié)果為創(chuàng)建了一個名稱為kismet的foreign key和一個名稱為kismet的普通key。
（5）index：key和unique key（表級）中的key可以換位index，作用一樣。
3、創(chuàng)建后
（1）添加鍵：add，舉例如下：alter table t add primary key(id);
（2）刪除鍵，drop，primary key使用alter table t drop primary key;其他key使用名字進行drop即可，注意刪除鍵和刪除列的區(qū)別。
4、查看信息：show create table table_name;可以查看表的各種屬性，包括鍵屬性、存儲引擎、字符集、分區(qū)情況等。
三、外鍵
1、作用：可以使得兩張表關(guān)聯(lián)，保證數(shù)據(jù)的一致性和實現(xiàn)一些級聯(lián)操作；
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表示事件觸發(fā)設(shè)置，可設(shè)參數(shù)：
4、示例
（1）創(chuàng)建表格，設(shè)置外鍵，并插入數(shù)據(jù)
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）如果在還有小弟的情況下刪除大哥，結(jié)果如下
[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）如果想在沒有建立大哥的情況下，強行插入小弟，結(jié)果如下
[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）修改事件觸發(fā)設(shè)置
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）如果在還有小弟的情況下刪除大哥：大哥和大哥對應(yīng)的小弟一起被刪除；如果想在沒有建立大哥的情況下，強行插入小弟，結(jié)果并不變，即失敗。
四、索引【參考：】
1、索引入門
（1）作用：索引對查詢的速度有著至關(guān)重要的影響。如果沒有索引，查詢將對整個表進行掃描；如果有索引，查詢只對索引進行。由于數(shù)據(jù)庫的數(shù)據(jù)不在內(nèi)存中，每次查詢都需要將數(shù)據(jù)由硬盤調(diào)入內(nèi)存，IO將浪費大量時間�？紤]到索引比數(shù)據(jù)小的多，使用索引可以大幅提高查詢速度；尤其是在數(shù)據(jù)量大時。
（2）索引是在存儲引擎中實現(xiàn)的，而不是在服務(wù)器層中實現(xiàn)的。所以，每種存儲引擎的索引都不一定完全相同，并不是所有的存儲引擎都支持所有的索引類型。目前最常用的存儲引擎是InnoDB。
2、選擇索引的數(shù)據(jù)類型：MySQL支持很多數(shù)據(jù)類型，選擇合適的數(shù)據(jù)類型存儲數(shù)據(jù)對性能有很大的影響。通常來說，可以遵循以下一些指導原則【（1）（2）條不適用于哈希索引】：
（1）越小的數(shù)據(jù)類型通常更好：越小的數(shù)據(jù)類型通常在磁盤、內(nèi)存和CPU緩存中都需要更少的空間，處理起來更快。
（2）簡單的數(shù)據(jù)類型更好：整型數(shù)據(jù)比起字符，處理開銷更小，因為字符串的比較更復(fù)雜。在MySQL中，應(yīng)該用內(nèi)置的日期和時間數(shù)據(jù)類型，而不是用字符串來存儲時間；以及用整型數(shù)據(jù)類型存儲IP地址。注意，對于索引，能用整型，就不要用字符串，尤其是在數(shù)據(jù)量大的時候；整型的一個弊端是，與客戶端的配合可能需要一些額外的工作(尤其是大整型)，但是對效率幾乎沒有影響。
（3）盡量避免NULL：應(yīng)該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優(yōu)化，因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復(fù)雜。你應(yīng)該用0、一個特殊的值或者一個空串代替空值。
3、B-tree索引：結(jié)果為B-tree（平衡二叉樹）
（1）概述：索引存儲的值按索引列中的順序排列�？梢岳肂-Tree索引進行全關(guān)鍵字、關(guān)鍵字范圍和關(guān)鍵字前綴查詢。如果對多列進行索引(組合索引)，列的順序非常重要，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【結(jié)果是排序的，因此搜索很快】
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）限制
2）不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。
3）存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。
1）Hash索引通過哈希函數(shù)計算Hash值進行檢索，可以查到要查數(shù)據(jù)的行指針，從而定位數(shù)據(jù)。
2）Hash值不取決于列的數(shù)據(jù)類型，一個TINYINT列的索引與一個長字符串列的索引一樣大。
3）Memory存儲引擎支持非唯一hash索引，如果多個值有相同的hash code，索引把它們的行指針用鏈表保存到同一個hash表項中。
（2）限制
1）由于索引僅包含hash code和記錄指針，所以，MySQL不能通過使用索引避免讀取記錄。但是訪問內(nèi)存中的記錄是非常迅速的，不會對性造成太大的影響。
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支持空間索引，主要用于地理空間數(shù)據(jù)類型，例如GEOMETRY。
（2）全文(Full-text)索引：全文索引是MyISAM的一個特殊索引類型，主要用于全文檢索。
以上就是MySQL中key與index詳細介紹的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！
學習教程快速掌握從入門到精通的SQL知識。
