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 N

#（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詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！