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`<span class="pln"> <span class="str">(<span class="pln"><span class="pun"></span></span></span></span>

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

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

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

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

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

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

<span class="pln"><span class="str"> <span class="pln"><span class="kwd"><span class="pun"><span class="lit"><span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="pun"></span></span></span></span></span></span></span></span></span></span></span>

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

<span class="pln"><span class="pun"><span class="str">##`name`<span class="pun"> varchar</span>#(</span>32</span>)</span> default

'',<span class="pln"><span class="str"><span class="pln"><span class="pun"><span class="str"><span class="pun"></span></span> </span></span></span></span>

###### PRIMARY KEY ###(###`id`###),######### ################## KEY ###`dage_id`#### ###(###`dage_id`###),######## #################

<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"><span class="kwd"><span class="pln"><span class="pun">#insert <span class="pln">into<span class="pun"> dage<span class="pln">(<span class="pun">name <span class="str">)<span class="pun"> values</span>(</span>'銅鑼灣'</span>);</span></span></span></span></span></span></span>

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

#（2）如果在還有小弟的情況下刪除大哥，結果如下

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

<span class="pun"><span class="typ"><span class="pun"><span class="pln">[<span class="lit">Err<span class="pln">]<span class="pun"> <span class="pln">1451<span class="typ"> <span class="pln">-<span class="kwd"> <span class="pln">Cannot<span class="kwd"> <span class="pln">delete<span class="pun"> <span class="pln">or<span class="pun"> update a parent row<span class="str">:<span class="pun"> a foreign key constraint fails <span class="str">(<span class="pun">`sample`<span class="pln">.<span class="str">`xiaodi`<span class="pln">,<span class="pun"> CONSTRAINT <span class="str">`xiaodi_ibfk_1`<span class="pun"> FOREIGN KEY <span class="pln">(<span class="str">`dage_id`<span class="pln">)<span class="pun"> REFERENCES <span class="str">`dage `<span class="pun"> </span>(</span>`id`</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"><span class="pun"><span class="pln"><span class="kwd"><span class="pln"><span class="pun">[<span class="pln">SQL<span class="pun">]<span class="pln"> insert <span class="pun">into<span class="pln"> xiaodi<span class="pun"> (<span class="lit">dage_id<span class="pun">,<span class="str">name<span class="pun">)</span> values</span>(</span>2</span>,</span>'旺角_小弟A'</span>);</span> </span></span></span></span></span></span></span></span></span></span>

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

<span class="pln"><span class="pun"> </span></span>（4）修改事件觸發設定

######################show create table xiaodi###;#查看鍵名稱#############

<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的一個特殊索引類型，主要用於全文檢索。

 

 

#

以上是MySQL中key與index詳細介紹的詳細內容。更多資訊請關注PHP中文網其他相關文章！