目錄 零、參考網頁 一、概述 二、語法 三、外鍵 四、索引 零、參考網頁 http://zhidao.baidu.com/link?url=ik8ZtrHL2qfZMgQStSFEcP2ORechkwBzbxBQjMQ15SoCV11-rRv5buPIPLZBZu570-YWRoAFsqANBiII ...
目錄 零、參考網頁 一、概述 二、語法 三、外鍵 四、索引 零、參考網頁 http://zhidao.baidu.com/link?url=ik8ZtrHL2qfZMgQStSFEcP2ORechkwBzbxBQjMQ15SoCV11-rRv5buPIPLZBZu570-YWRoAFsqANBiIIsQbBsK http://www.cppblog.com/wolf/articles/69089.html http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html 一、概述 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的處理決定的。
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）如果在還有小弟的情況下刪除大哥：大哥和大哥對應的小弟一起被刪除；如果想在沒有建立大哥的情況下，強行插入小弟，結果並不變，即失敗。
四、索引【參考：http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html】
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的一個特殊索引類型，主要用於全文檢索。
