第5章-MySQL索引與完整性約束

1、第5章 MySQL索引與完整性約束MySQL索引5 . 15 . 2MySQL索引創(chuàng)建MySQL數(shù)據(jù)完整性約束5 . 35.1 MySQL索引5.1.1 索引及作用1. 索引索引是根據(jù)表中一列或若干列按照一定順序建立的列值與記錄行之間的對(duì)應(yīng)關(guān)系表。在列上創(chuàng)建了索引之后，查找數(shù)據(jù)時(shí)可以直接根據(jù)該列上的索引找到對(duì)應(yīng)行的位置，從而快速地找到數(shù)據(jù)。例如，如果用戶創(chuàng)建了xs表中學(xué)號(hào)列的索引，MySQL將在索引中排序?qū)W號(hào)列，對(duì)于索引中的每一項(xiàng)，MySQL在內(nèi)部為它保存一個(gè)數(shù)據(jù)文件中實(shí)際記錄所在位置的“指針”。因此，如果要查找學(xué)號(hào)為“081241”的學(xué)生信息，MySQL能在學(xué)號(hào)列的索引中找到“081241”

2、的值，然后直接轉(zhuǎn)到數(shù)據(jù)文件中相應(yīng)的行，準(zhǔn)確地返回該行的數(shù)據(jù)。5.1.1 索引及作用2索引作用在數(shù)據(jù)庫系統(tǒng)中建立索引主要有以下作用：快速讀取數(shù)據(jù)；保證數(shù)據(jù)記錄的唯一性；實(shí)現(xiàn)表與表之間的參照完整性；在使用GROUP BY、ORDER BY子句進(jìn)行數(shù)據(jù)檢索時(shí)，利用索引可減少排序和分組的時(shí)間。5.1.1 索引及作用3. MySQL索引在MySQL 5.6中，所有的MySQL列類型都能被索引，但要注意以下幾點(diǎn)：只有當(dāng)表類型為MyISAM、InnoDB或BDB時(shí)，才可以向有NULL、BLOB或TEXT列中添加索引。一個(gè)表最多可有16個(gè)索引。最大索引長(zhǎng)度是256個(gè)字節(jié)。對(duì)于CHAR和VARCHAR列，可以索

3、引列的前綴。這樣索引的速度更快并且比索引整個(gè)列需要較少的磁盤空間。MySQL能在多個(gè)列上創(chuàng)建索引。索引可以由最多15個(gè)列組成（在CHAR和VARCHAR列上，也可以使用列的前綴作為索引的部分）。5.1.2 索引的分類1. BTREE索引目前大部分MySQL索引都是以B-樹（BTREE）方式存儲(chǔ)的，索引類型分成下列幾個(gè)。（1）普通索引（INDEX）這是最基本的索引類型，它沒有唯一性之類的限制。創(chuàng)建普通索引的關(guān)鍵字是INDEX。（2）唯一性索引（UNIQUE）這種索引和前面的普通索引基本相同，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須是唯一的。創(chuàng)建唯一性索引的關(guān)鍵字是UNIQUE。（3）主

4、鍵（PRIMARY KEY）主鍵是一種唯一性索引，它必須指定為“PRIMARY KEY”。主鍵一般在創(chuàng)建表的時(shí)候指定，也可以通過修改表的方式加入主鍵。但是每個(gè)表只能有一個(gè)主鍵。（4）全文索引（FULLTEXT）MySQL支持全文檢索和全文索引。在MySQL中，全文索引的索引類型為FULLTEXT。5.1.2 索引的分類2哈希索引（HASH）當(dāng)表類型為MEMORY或HEAP時(shí)，除了BTREE索引，MySQL還支持哈希索引（HASH）。使用哈希索引，不需要建立樹結(jié)構(gòu)，但是所有的值都保存在一個(gè)列表中，這個(gè)列表指向相關(guān)頁和行。當(dāng)根據(jù)一個(gè)值獲取一個(gè)特定的行時(shí)，哈希索引非?？?。5.2 MySQL索引創(chuàng)建1

5、CREATE INDEX語句創(chuàng)建使用CREATE INDEX語句可以在一個(gè)已有表上創(chuàng)建索引，一個(gè)表可以創(chuàng)建多個(gè)索引。語法格式：CREATE UNIQUE | FULLTEXT | SPATIAL INDEX 索引名 索引類型 ON 表名 ( 索引列名 .) 索引選項(xiàng) .索引列名=：列名 (長(zhǎng)度) ASC | DESC說明：UNIQUE | FULLTEXT | SPATIAL：UNIQUE表示創(chuàng)建的是唯一性索引；FULLTEXT表示創(chuàng)建全文索引；SPATIAL表示為空間索引，可以用來索引幾何數(shù)據(jù)類型的列（本書不討論這種索引）。索引名：索引在一個(gè)表中名稱必須是唯一的。索引類型：MySQL支持的索

6、引類型有BTREE和HASH。索引列名：創(chuàng)建索引的列名后的長(zhǎng)度表示該列前面創(chuàng)建索引字符個(gè)數(shù)。這可使索引文件大大減小，從而節(jié)省磁盤空間。5.2 MySQL索引創(chuàng)建【例5.1】根據(jù)xs表的學(xué)號(hào)列上的前5個(gè)字符建立一個(gè)升序索引xh_xs。use xscjcreate index xh_xs on xs(學(xué)號(hào)(5) asc);也可以在一個(gè)索引的定義中包含多個(gè)列，中間用逗號(hào)隔開，但它們屬于同一個(gè)表，這樣的索引叫作復(fù)合索引?！纠?.2】在xs_kc表的學(xué)號(hào)列和課程號(hào)列上建立一個(gè)復(fù)合索引xskc_in。create index xskc_in on xs_kc(學(xué)號(hào),課程號(hào));5.2 MySQL索引創(chuàng)建2A

7、LTER TABLE語句創(chuàng)建前面章節(jié)介紹了如何使用ALTER TABLE語句修改表，其中也包括向表中添加索引。語法格式如下：ALTER IGNORE TABLE 表名. | ADD INDEX|KEY 索引名 /*添加索引*/索引類型 (索引列名.) 索引選項(xiàng) . | ADD CONSTRAINT symbol PRIMARY KEY/*添加主鍵*/索引類型 (索引列名.) 索引選項(xiàng) . | ADD CONSTRAINT symbolUNIQUE INDEX|KEY 索引名索引類型 (索引列名.) 索引選項(xiàng) . /*添加唯一性索引*/ | ADD FULLTEXT INDEX|KEY 索引名(

8、索引列名.)索引選項(xiàng) . /*添加全文索引*/ | ADD SPATIAL INDEX|KEY 索引名(索引列名.)索引選項(xiàng) . /*添加空間索引*/ | ADD CONSTRAINT symbol FOREIGN KEY 索引名 (索引列名.) 參照性定義 /*添加外鍵*/ | DISABLE KEYS | ENABLE KEYS5.2 MySQL索引創(chuàng)建【例5.3】在xs表的姓名列上創(chuàng)建一個(gè)非唯一的索引。alter table xsadd index xs_xm using btree (姓名);【例5.4】以xs表為例（假設(shè)表中主鍵未定），創(chuàng)建這樣的索引，以加速表的檢索速度：alter

9、table xsadd index mark(出生日期,性別);這個(gè)例子創(chuàng)建了一個(gè)復(fù)合索引。5.2 MySQL索引創(chuàng)建如果想要查看表中創(chuàng)建的索引的情況，可以使用SHOW INDEX FROM 表名語句，例如：show index from xs;系統(tǒng)顯示已創(chuàng)建的索引信息如下：5.2 MySQL索引創(chuàng)建3在建立表時(shí)創(chuàng)建索引在前兩種情況下，索引都是在表建立之后創(chuàng)建的。索引也可以在創(chuàng)建表時(shí)一起創(chuàng)建。在創(chuàng)建表的CREATE TABLE語句中可以包含索引的定義。語法格式：CREATE TEMPORARY TABLE IF NOT EXISTS 表名 ( 列定義 , . | 索引定義 ) 表選項(xiàng) sele

10、ct語句;索引定義=： CONSTRAINT symbolPRIMARY KEY 索引類型 (索引列名.)/*主鍵*/ | INDEX | KEY 索引名 索引類型(索引列名 . )/*索引*/| CONSTRAINT symbol UNIQUE INDEX|KEY 索引名 索引類型 (索引列名.) /*唯一性索引*/| FULLTEXT|SPATIAL INDEX|KEY 索引名 (索引列名.)/*全文索引*/| CONSTRAINT symbol FOREIGN KEY 索引名 (索引列名.)參照性定義 /*外鍵*/5.2 MySQL索引創(chuàng)建【例5.5】在mytest數(shù)據(jù)庫中創(chuàng)建成績(jī)（cj

11、）表，學(xué)號(hào)和課程號(hào)的聯(lián)合主鍵，并在成績(jī)列上創(chuàng)建索引。use mytestcreate table xs_kc(學(xué)號(hào) char(6) not null,課程號(hào) char(3) not null,成績(jī) tinyint(1),學(xué)分 tinyint(1),primary key(學(xué)號(hào),課程號(hào)),index cj(成績(jī));5.2 MySQL索引創(chuàng)建4刪除索引當(dāng)一個(gè)索引不再需要的時(shí)候，可以用DROP INDEX語句或ALTER TABLE語句刪除它。（1）使用DROP INDEX刪除語法格式：DROP INDEX 索引名 ON 表名（2）使用ALTER TABLE刪除語法格式：ALTER IGNORE T

12、ABLE 表名. | DROP PRIMARY KEY/*刪除主鍵*/ | DROP INDEX|KEY 索引名/*刪除索引*/ | DROP FOREIGN KEY fk_symbol/*刪除外鍵*/5.2 MySQL索引創(chuàng)建【例5.6】刪除xs表上的mark索引。alter table xsdrop index mark;讀者可使用SHOW INDEX FROM 表名語句查看執(zhí)行結(jié)果。5.3 MySQL數(shù)據(jù)完整性約束5.3.1 主鍵約束可以用兩種方式定義主鍵，作為列或表的完整性約束。作為列的完整性約束時(shí)，只需在列定義的時(shí)候加上關(guān)鍵字PRIMARY KEY，這個(gè)在節(jié)中已作過介紹。作為表的完整

13、性約束時(shí)，需要在語句最后加上一條PRIMARY KEY(col_name,)語句?！纠?.7】創(chuàng)建表xs1，將姓名定義為主鍵。create table xs1( 學(xué)號(hào) varchar(6) null, 姓名 varchar(8) not null primary key , 出生日期 datetime);5.3.1 主鍵約束當(dāng)表中的主鍵為復(fù)合主鍵時(shí)，只能定義為表的完整性約束?！纠?.8】創(chuàng)建course表來記錄每門課程的學(xué)生學(xué)號(hào)、姓名、課程號(hào)、學(xué)分和畢業(yè)日期。其中學(xué)號(hào)、課程號(hào)和畢業(yè)日期構(gòu)成復(fù)合主鍵。create table course(學(xué)號(hào) varchar(6) not null,姓名 va

14、rchar(8) not null,畢業(yè)日期date not null,課程號(hào)varchar(3) ,學(xué)分tinyint ,primary key (學(xué)號(hào), 課程號(hào), 畢業(yè)日期);5.3.1 主鍵約束原則上，任何列或者列的組合都可以充當(dāng)一個(gè)主鍵。但是主鍵列必須遵守一些規(guī)則。這些規(guī)則源自于關(guān)系模型理論和MySQL所制定的以下規(guī)則：（1）每個(gè)表只能定義一個(gè)主鍵。來自關(guān)系模型的這一規(guī)則也適用于MySQL。（2）關(guān)系模型理論要求必須為每個(gè)表定義一個(gè)主鍵。然而，MySQL并不要求這樣，可以創(chuàng)建一個(gè)沒有主鍵的表。（3）表中的兩個(gè)不同的行在主鍵上不能具有相同的值，即所謂的“唯一性規(guī)則”。（4）如果從一個(gè)復(fù)合

15、主鍵中刪除一列后，剩下的列構(gòu)成的主鍵仍然滿足唯一性原則，那么，這個(gè)復(fù)合主鍵是不正確的，這條規(guī)則稱為“最小化規(guī)則”（Minimality Rule）。也就是說，復(fù)合主鍵不應(yīng)包含任何不必要的列。（5）一個(gè)列名在一個(gè)主鍵的列的列表中只能出現(xiàn)一次。5.3.1 主鍵約束MySQL自動(dòng)地為主鍵創(chuàng)建一個(gè)索引。通常，這個(gè)索引名為PRIMARY。然而，可以重新給這個(gè)索引取名。【例5.9】創(chuàng)建【例5.9】中的course表，把主鍵創(chuàng)建的索引命名為index_course。create table course(學(xué)號(hào)varchar(6) not null,姓名varchar(8) not null,畢業(yè)日期date

16、 not null,課程號(hào)varchar(3),學(xué)分tinyint ,primary key index_course(學(xué)號(hào), 課程號(hào), 畢業(yè)日期);5.3.2 替代鍵約束在關(guān)系模型中，替代鍵像主鍵一樣，是表的一列或一組列，它們的值在任何時(shí)候都是唯一的。替代鍵是沒有被選作主鍵的候選鍵。定義替代鍵的關(guān)鍵字是UNIQUE?！纠?.10】在表xs1中將姓名列定義為一個(gè)替代鍵。create table xs1( 學(xué)號(hào) varchar(6) null, 姓名 varchar(8) not null unique, 出生日期 datetime null, primary key(學(xué)號(hào));5.3.2 替代鍵

17、約束替代鍵還可以定義為表的完整性約束，故前面語句也可這樣定義：create table xs1( 學(xué)號(hào) varchar(6) null, 姓名 varchar(8) not null, 出生日期 datetime null, primary key(學(xué)號(hào)), unique(姓名);5.3.2 替代鍵約束在MySQL中，替代鍵和主鍵的區(qū)別主要有以下幾點(diǎn)：（1）一個(gè)數(shù)據(jù)表只能創(chuàng)建一個(gè)主鍵。但一個(gè)表可以有若干個(gè)UNIQUE鍵，并且它們甚至可以重合，例如，在C1和C2列上定義了一個(gè)替代鍵，并且在C2和C3上定義了另一個(gè)替代鍵，這兩個(gè)替代鍵在C2列上重合了，而MySQL允許這樣。（2）主鍵字段的值不允許

18、為NULL，而UNIQUE字段的值可取NULL，但是必須使用NULL或NOT NULL聲明。（3）一般創(chuàng)建PRIMARY KEY約束時(shí)，系統(tǒng)會(huì)自動(dòng)產(chǎn)生PRIMARY KEY索引。創(chuàng)建UNIQUE約束時(shí)，系統(tǒng)自動(dòng)產(chǎn)生UNIQUE索引。5.3.3 參照完整性約束定義外鍵的語法格式已經(jīng)在介紹索引時(shí)給出了，這里列出“參照性定義”。語法格式：參照性定義=：REFERENCES 表名 (索引列名 . ) ON DELETE RESTRICT | CASCADE | SET NULL | NO ACTION ON UPDATE RESTRICT | CASCADE | SET NULL | NO ACTIO

19、N索引列名=：列名 (長(zhǎng)度) ASC | DESC5.3.3 參照完整性約束參照動(dòng)作包含兩部分：在第一部分中，指定這個(gè)參照動(dòng)作應(yīng)用哪一條語句。這里有兩條相關(guān)的語句，即UPDATE和DELETE語句；在第二部分中，指定采取哪個(gè)動(dòng)作?？赡懿扇〉膭?dòng)作是RESTRICT、CASCADE、SET NULL、NO ACTION和SET DEFAULT。5.3.3 參照完整性約束接下來說明這些不同動(dòng)作的含義。RESTRICT：當(dāng)要?jiǎng)h除或更新父表中被參照列上在外鍵中出現(xiàn)的值時(shí)，拒絕對(duì)父表的刪除或更新操作。CASCADE：從父表刪除或更新行時(shí)自動(dòng)刪除或更新子表中匹配的行。SET NULL：當(dāng)從父表刪除或更新行時(shí)

20、，設(shè)置子表中與之對(duì)應(yīng)的外鍵列為NULL。如果外鍵列沒有指定NOT NULL限定詞，這就是合法的。NO ACTION：NO ACTION意味著不采取動(dòng)作，就是如果有一個(gè)相關(guān)的外鍵值在被參考的表里，刪除或更新父表中主要鍵值的企圖不被允許，和RESTRICT一樣。SET DEFAULT：作用和SET NULL一樣，只不過SET DEFAULT是指定子表中的外鍵列為默認(rèn)值。5.3.3 參照完整性約束外鍵目前只可以用在那些使用InnoDB存儲(chǔ)引擎創(chuàng)建的表中，對(duì)于其他類型的表，MySQL服務(wù)器能夠解析CREATE TABLE語句中的FOREIGN KEY語法，但不能使用或保存它。【例5.11】創(chuàng)建xs1表

21、，所有的xs表中學(xué)生學(xué)號(hào)都必須出現(xiàn)在xs1表中，假設(shè)已經(jīng)使用學(xué)號(hào)列作為主鍵創(chuàng)建了xs表。create table xs1( 學(xué)號(hào) varchar(6) null, 姓名 varchar(8) not null, 出生日期 datetime null, primary key (姓名), foreign key (學(xué)號(hào)) references xs (學(xué)號(hào)) on delete restrict on update restrict);5.3.3 參照完整性約束這意味著，對(duì)于xs1表中的每一個(gè)學(xué)號(hào)，都執(zhí)行一次檢查，看這個(gè)號(hào)碼是否已經(jīng)出現(xiàn)在xs表的學(xué)號(hào)列（主鍵）中。如果情況不是這樣，用戶或應(yīng)用程序

22、會(huì)接收到一條出錯(cuò)消息，并且更新被拒絕。這也適用于使用UPDATE語句更新xs1表中的學(xué)號(hào)列。即MySQL確保了xs1表中的學(xué)號(hào)列的內(nèi)容總是xs表中學(xué)號(hào)列的內(nèi)容的一個(gè)子集。也就是說，下面的SELECT語句不會(huì)返回任何行：select * from xs1 where 學(xué)號(hào) not in ( select 學(xué)號(hào) from xs );5.3.3 參照完整性約束當(dāng)指定一個(gè)外鍵的時(shí)候，以下的規(guī)則適用：（1）被參照表必須已經(jīng)用一條CREATE TABLE語句創(chuàng)建了，或者必須是當(dāng)前正在創(chuàng)建的表。在后一種情況下，參照表是同一個(gè)表。（2）必須為被參照表定義主鍵。（3）必須在被參照表的表名后面指定列名（或列名的組

23、合）。這個(gè)列（或列組合）必須是這個(gè)表的主鍵或替代鍵。（4）盡管主鍵是不能夠包含空值的，但允許在外鍵中出現(xiàn)一個(gè)空值。這意味著，只要外鍵的每個(gè)非空值出現(xiàn)在指定的主鍵中，這個(gè)外鍵的內(nèi)容就是正確的。（5）外鍵中的列的數(shù)目必須和被參照表的主鍵中的列的數(shù)目相同。（6）外鍵中的列的數(shù)據(jù)類型必須和被參照表的主鍵中的列的數(shù)據(jù)類型對(duì)應(yīng)相等。5.3.3 參照完整性約束與外鍵相關(guān)的被參照表和參照表可以是同一個(gè)表。這樣的表稱為自參照表（Self-referencing Table），這種結(jié)構(gòu)稱為自參照完整性（Self-referential Integrity）。例如，可以創(chuàng)建這樣的xs2表：create table

24、xs2( 學(xué)號(hào) varchar(6) not null, 姓名 varchar(8) not null, 出生日期 datetime null, primary key (學(xué)號(hào)), foreign key (學(xué)號(hào)) references xs1 (學(xué)號(hào));5.3.3 參照完整性約束【例5.12】創(chuàng)建帶有參照動(dòng)作ASCADE的xs1表。create table xs1( 學(xué)號(hào) varchar(6) not null, 姓名 varchar(8) not null, 出生日期 datetime null, primary key (學(xué)號(hào)), foreign key (學(xué)號(hào)) references

25、xs (學(xué)號(hào)) on update cascade);5.3.4 CHECK完整性約束CHECK完整性約束在創(chuàng)建表的時(shí)候定義?？梢远x為列完整性約束，也可定義為表完整性約束。語法格式：CHECK(expr)【例5.13】創(chuàng)建表student，只包括學(xué)號(hào)和性別兩列，性別只能是男或女。create table student( 學(xué)號(hào) char(6) not null, 性別 char(1) not null check(性別 in (男, 女);5.3.4 CHECK完整性約束【例5.14】創(chuàng)建表student1，只包括學(xué)號(hào)和出生日期兩列，出生日期必須大于1990年1月1日。create table student1( 學(xué)號(hào) char(6) not null, 出生日期 date not null check(出生日期1990-01-01);5.3.4 CHECK完整性約束前面的CHECK完整性約束中使用的表達(dá)式都很簡(jiǎn)單，MySQL還允許使用更為復(fù)雜的表達(dá)式。例如，可以在條件中加入子查詢，下面舉個(gè)例子?！纠?.15】創(chuàng)建表student2，只包括學(xué)號(hào)和性別兩列，并且確認(rèn)性