MySQL教程（第4版） 課件 第5章 索引與數(shù)據(jù)完整性

第5章

索引與數(shù)據(jù)完整性——索

引01索引的分類普通索引（INDEX）唯一性索引（UNIQUE）主鍵（PRIMARYKEY）全文索引（FULLTEXT）空間索引（SPATIAL）索引的分類1．普通索引（INDEX）這種索引列的內(nèi)容可以相同。例如，在學(xué)生表按照姓名只能創(chuàng)建普通索引，因為學(xué)生中可能存在姓名相同的記錄。在這類索引中姓名相同的記錄就會排在一起。定義子句如下：{INDEX|KEY}[索引名](鍵,...)按照指定的(鍵,...)創(chuàng)建索引，定義索引名，方便后面對其引用。例如后面可以刪除已經(jīng)創(chuàng)建的指定索引名。鍵如下：列名[(長度)][ASC|DESC]索引的分類2．唯一性索引（UNIQUE）這種索引列的值必須是唯一的。例如學(xué)號就是為了區(qū)分學(xué)生的，在學(xué)生表中不可能重復(fù)出現(xiàn)。定義子句如下，按照標(biāo)識引用：[CONSTRAINT[標(biāo)識]]UNIQUE(鍵,...)3．主鍵（PRIMARYKEY）主鍵是一種唯一性索引，它必須指定為PRIMARYKEY。一個表中唯一性的列可以有多個，但只能選擇一個作為主鍵。例如學(xué)生信息表，除了學(xué)號外，身份證號、電話號碼、微信號也是唯一的，但學(xué)生管理中，應(yīng)該采用學(xué)號作為主鍵，因為它是管理學(xué)生的最重要的信息，而且使用最頻繁。定義子句如下：[CONSTRAINT[標(biāo)識]]PRIMARYKEY(鍵,...)索引的分類4．全文索引（FULLTEXT）主要用來查找文本中的關(guān)鍵字，而不是直接與索引中的值相比較。全文索引跟其它索引大不相同，它更像是一個搜索引擎，需要配合MATCHAGAINST操作使用，而不是一般的WHERE語句加LIKE。目前只有char、varchar、text列上可以創(chuàng)建全文索引。定義子句如下：FULLTEXT{INDEX|KEY}[索引名](鍵,...)在MySQL中，全文索引的類型為FULLTEXT，并且只能在MyISAM存儲引擎的表中創(chuàng)建。5．空間索引（SPATIAL）空間索引是對空間數(shù)據(jù)類型的字段建立的索引。MySQL使用SPATIAL關(guān)鍵字進(jìn)行擴展，使其能夠用于創(chuàng)建空間索引。創(chuàng)建空間索引的列，必須將其聲明為NOTNULL。定義子句如下：SPATIAL{INDEX|KEY}[索引名](鍵,...)02創(chuàng)建和刪除索引在建立表時創(chuàng)建索引在已有表中創(chuàng)建索引修改表結(jié)構(gòu)增加索引刪除索引創(chuàng)建和刪除索引1．在建立表時創(chuàng)建索引CREATE[TEMPORARY]TABLE[IFNOTEXISTS]表名 [([列定義],... |[索引定義])]說明：（1）在列定義的時候，可以指定PRIMARYKEY（主鍵）或者UNIQUE（唯一性鍵）屬性?！纠吭趧?chuàng)建kc1表結(jié)構(gòu)時指定主鍵。USExscj;CREATETABLEkc1(

課程號 char(3) NOTNULLPRIMARYKEY,

課程名 varchar(8)NOTNULL,

開課學(xué)期 tinyint NOTNULL,

學(xué)時 tinyint NOTNULL,

學(xué)分 tinyint NOTNULL);創(chuàng)建和刪除索引（2）當(dāng)主鍵或者唯一性鍵由多個列組成時，必須在最后一列后面加上子句。【例】在創(chuàng)建cj1表結(jié)構(gòu)列后指定多列主鍵。USExscj;CREATETABLEcj1(

學(xué)號 CHAR(6)NOTNULL,

課程號 CHAR(3)NOTNULL,

成績 TINYINT(1), PRIMARYKEY(學(xué)號,課程號), INDEXcj(成績));SHOWINDEXFROMcj1;其中，在所有列定義后定義主鍵PRIMARYKEY(學(xué)號,課程號)和索引INDEXcj(成績)。顯示cj1表索引情況如圖。創(chuàng)建和刪除索引2．在已有表中創(chuàng)建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEX索引名 [索引類型]ON表名(鍵,...) ...鍵：列名[(長度)][ASC|DESC]UNIQUE|FULLTEXT|SPATIAL：UNIQUE表示創(chuàng)建的是唯一性索引；FULLTEXT表示創(chuàng)建全文索引；SPATIAL表示創(chuàng)建空間索引。索引名：索引名在一個表中必須是唯一的，在創(chuàng)建索引時定義、修改表結(jié)構(gòu)時修改，通過該名刪除索引。創(chuàng)建和刪除索引【例】在已有xs1表上創(chuàng)建普通索引和唯一性索引。USExscj;CREATETABLExs1SELECT*FROMxs; CREATEUNIQUEINDEXxm_xh4 ONxs1(姓名,學(xué)號(4)ASC);SHOWINDEXFROMxs1;顯示xs1表索引情況如圖。創(chuàng)建和刪除索引3．修改表結(jié)構(gòu)增加索引ALTERTABLE表名 ...... |ADD索引定義 |DISABLEKEYS|ENABLEKEYS其中：DISABLEKEYS|ENABLEKEYS，只在MyISAM表中有用，DISABLEKEYS可以讓MySQL在更新表時停止更新MyISAM表中的非唯一索引，ENABLEKEYS重新創(chuàng)建丟失的索引，這樣可以加快查詢速度。【例】在已有xs1表的出生日期列上創(chuàng)建一個非唯一索引。ALTERTABLExs1 ADDINDEXcsrq(出生日期);創(chuàng)建和刪除索引4．刪除索引當(dāng)一個索引不再需要時，可以刪除。（1）專門刪除索引。DROPINDEX索引名ON表名本語句刪除各種類型索引，包括普通索引、主鍵（PRIMARYKEY）、唯一索引（UNIQUE）、全文索引（FULLTEXT）和空間索引（SPATIAL）。【例】刪除xs1表上的csrq索引。DROPINDEXcsrqONxs1;（2）修改表結(jié)構(gòu)刪除索引。ALTER[IGNORE]TABLE表名 ...... DROPINDEX索引名

【例】刪除x1s表上的xm_xh4索引。ALTERTABLExs1 DROPINDEXxm_xh4;SHOWINDEXFROMxs1;03索引建立、分析和使用索引項的選擇復(fù)合索引的選擇索引失效的情況建立索引的原則索引建立、分析和使用1．索引項的選擇一般來說，索引項的選擇需要考慮一些基本原則：（1）對于學(xué)生表（xs），由于學(xué)號不可能重復(fù)，它也是標(biāo)識學(xué)生的信息，應(yīng)該作為主鍵。因為會經(jīng)常按學(xué)號查詢學(xué)生信息，定義為主鍵，可以很快找到對應(yīng)記錄。（2）以姓名查詢的機會也很多，但姓名可以重名，所以該列只能創(chuàng)建普通索引。（3）以出生日期查詢的可能性較小，所以不要建立索引。因為創(chuàng)建索引除了需要額外占用存儲資源外，系統(tǒng)需要根據(jù)表記錄內(nèi)容的變化更新索引文件內(nèi)容，降低系統(tǒng)運行效率。（4）雖然（xsk表）家庭地址區(qū)分度很好，但其數(shù)據(jù)類型（json）不適合建立索引。索引建立、分析和使用2．復(fù)合索引的選擇如果表頻繁查詢的多列（大多為二列）一起才能保證唯一性，那么就需要多列聯(lián)合創(chuàng)建索引。創(chuàng)建聯(lián)合索引需要注意以下幾點：（1）查詢關(guān)注度高和區(qū)分度好的列需要排列在前面。例如，xs表如果需要對（姓名，專業(yè)，性別）一起創(chuàng)建索引，因為按照姓名查詢比較頻繁，而且姓名相同的可能性小，所以姓名應(yīng)放在第一項，雖然性別比專業(yè)可能查詢更多，但區(qū)分度不如專業(yè)，所以專業(yè)應(yīng)放第二列。（2）對構(gòu)成復(fù)合索引的列組合進(jìn)行查詢，均可使用索引。例如，按（姓名，專業(yè)，性別）創(chuàng)建了索引，如下WHERE的查詢條件都可使用該索引：WHERE姓名=?WHERE姓名=?AND專業(yè)=?WHERE姓名=?AND性別=?WHERE姓名=?AND專業(yè)=?AND性別=?（3）合理創(chuàng)建聯(lián)合索引，避免冗余。對于a、b、c列集合(a)，(a,b)，(a,b,c)，只要創(chuàng)建了(a,b,c)索引，其他按照(a)和(a,b)創(chuàng)建索引就是冗余的，因為后者已經(jīng)包含在(a,b,c)索引中。索引建立、分析和使用3．索引失效的情況下列情況下，MySQL不會使用已有的索引：（1）索引列進(jìn)行數(shù)據(jù)運算或者函數(shù)運算。例如：WHERESUBSTR(學(xué)號,3,2)='12'，即使按“學(xué)號”創(chuàng)建了索引，但對學(xué)號進(jìn)行函數(shù)運算的條件卻不能使用該索引。（2）如果LIKE是以％開始就不能使用索引。也就是說，查詢索引列只能是全部或者是前面一部分。例如：WHERE專業(yè)LIKE'%工程%'

，即使按專業(yè)建立了索引，該查詢也不能使用索引；而WHERE學(xué)號LIKE'20%'則可以使用包含學(xué)號列的索引。（3）WHERE條件使用NOT、<>、!=、IN和NOTIN運算符，無法使用索引。例如：WHERE學(xué)號<>'221201'，無法使用學(xué)號列索引。（4）使用OR分割的條件，如果OR前的條件中的列有索引，后面的列中沒有索引，那么涉及到的索引都不會使用。（5）對WHERE后邊條件為字符串的一定要加引號，字符串如果為數(shù)字MySQL會自動轉(zhuǎn)為字符串，但是不會使用索引。索引建立、分析和使用4．建立索引的原則（1）唯一性索引可以更快速的通過它來確定某條記錄，應(yīng)優(yōu)先使用。不唯一的盡量選擇區(qū)分度高的列作為普通索引。（2）為經(jīng)常作為查詢條件的列建立索引，也經(jīng)常需要排序、分組和聯(lián)合操作的列建立索引。（3）如果索引的值很長，那么查詢速度會受到影響。如果查詢內(nèi)容為列前部，盡量使用前綴來建立索引。（4）列需要計算或者經(jīng)函數(shù)處理后才能查詢的，不能加入索引。（5）限制索引的數(shù)目，索引越多，更新表效率會越低。不建非必要的索引，刪除不再使用或很少使用的索引。04查詢索引使用評估MySQL查詢優(yōu)化器EXPLAIN查看執(zhí)行計劃評估查詢使用索引查詢索引使用評估1．MySQL查詢優(yōu)化器MySQL中有專門負(fù)責(zé)優(yōu)化SELECT語句的優(yōu)化器模塊，它通過計算分析系統(tǒng)中收集到的統(tǒng)計信息，為客戶端請求的查詢提供最優(yōu)的執(zhí)行計劃，根據(jù)查詢進(jìn)行相應(yīng)的計算分析，然后再得出最后的執(zhí)行計劃。2．EXPLAIN查看執(zhí)行計劃使用EXPLAIN語句可以模擬優(yōu)化器執(zhí)行SQL查詢語句，分析其中的查詢語句或是表結(jié)構(gòu)的性能瓶頸。EXPLAIN查看執(zhí)行計劃（Explain+SQL語句）：EXPLAINSELECT語句\G;可以獲取下列信息：表的讀取順序和操作類型、分區(qū)情況、哪些索引可以使用、哪些索引被實際使用、執(zhí)行時計劃掃描的記錄行數(shù)以及是否使用外部排序等。查詢索引使用評估3．評估查詢使用索引（1）單表索引使用【例5.7】xs表查詢評估。USExscj;EXPLAIN SELECT*FROMxs WHERE學(xué)號='221201';評估查詢運行結(jié)果如圖。查詢索引使用評估（2）多表連接查詢【例】xs、kc和cj表連接查詢評估。USExscj;EXPLAIN SELECTxs.學(xué)號,姓名,課程名,成績 FROMcjJOINxsONcj.學(xué)號=xs.學(xué)號 JOINkcONcj.課程號=kc.課程號 WHERE課程名='計算機導(dǎo)論'AND成績>=80;評估查詢運行結(jié)果如圖。查詢索引使用評估（3）沒有使用索引【例】xs表沒有使用索引的查詢評估。USExscj;EXPLAIN SELECT姓名,學(xué)號,總學(xué)分 FROMxs WHERE專業(yè)='計算機'AND性別=0;評估查詢運行結(jié)果如圖。第5章

索引與數(shù)據(jù)完整性——數(shù)據(jù)完整性01實體完整性約束主鍵約束唯一鍵約束實體完整性約束1．主鍵約束通過定義表一列為PRIMARYKEY約束來創(chuàng)建主鍵，其組成主鍵的列值唯一而且不能取NULL值，就可以用此標(biāo)識表中的行。如果主鍵約束是由多列組合定義的復(fù)合主鍵，則某一列的值可以相同，但定義主鍵中的所有列的組合值必須唯一，不能包含NULL值。例如：學(xué)生表（xs）中的學(xué)號列，課程表（kc）中的課程號列。實體完整性約束2．唯一鍵約束在關(guān)系模型中，唯一鍵像主鍵一樣，可以由表的一列或多列組成，它是唯一性索引，關(guān)鍵字是UNIQUE。它們的值在任何時候都是唯一的，但可以包含一行或者多行NULL值。（1）在列中定義唯一鍵【例】在表xs1中將姓名列定義為一個唯一鍵。USExscj;DROPTABLEIFEXISTSxs1;CREATETABLExs1(學(xué)號 varchar(6) NOTNULL,姓名 varchar(8) UNIQUE,出生日期 datetime NULL, PRIMARYKEY(學(xué)號));實體完整性約束（2）在表中定義唯一鍵【例】在表xs1中列定義后將姓名定義為唯一鍵。DROPTABLEIFEXISTSxs1;CREATETABLExs1(學(xué)號 varchar(6) NOTNULL,姓名 varchar(8) NULL,出生日期 datetime NULL, PRIMARYKEY(學(xué)號), UNIQUExm(姓名));02參照完整性約束參照完整性約束定義參照完整性外鍵規(guī)則刪除參照完整性約束參照完整性約束1．參照完整性約束定義參照完整性約束可以在創(chuàng)建表或修改表時定義一個外鍵聲明。[CONSTRAINT]參照名 FOREIGNKEY(外鍵列)REFERENCES表名(主鍵) [ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}] [ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]參照語句和動作：（1）參照語句

ONDELETE|ONUPDATE：刪除記錄語句和更新記錄語句。（2）采取的動作RESTRICT：拒絕對父表被參照列的刪除或更新操作。它是默認(rèn)值。CASCADE：從父表刪除或更新行時自動刪除或更新子表中匹配的行。SETNULL：當(dāng)從父表刪除或更新行時，設(shè)置子表中與之對應(yīng)的外鍵列為NULL。NOACTION：作用和RESTRICT一樣。SETDEFAULT：父表有變更時,子表將外鍵列設(shè)置成一個默認(rèn)的值（但I(xiàn)nnoDB不能識別）。參照完整性約束2．參照完整性外鍵規(guī)則（1）被參照表必須已經(jīng)存在，或者與參照表是同一個表。（2）必須為被參照表定義主鍵。（3）盡管主鍵是不能夠包含空值的，但允許在外鍵中出現(xiàn)一個空值。這意味著，只要外鍵的每個非空值出現(xiàn)在指定的主鍵中，這個外鍵的內(nèi)容就是正確的。（4）外鍵中列的數(shù)目必須和被參照表的主鍵中列的數(shù)目和數(shù)據(jù)類型相同?！纠慷x學(xué)生表（xs1）、課程表（kc1）和成績表（cj1）參照完整性。參照完整性包括下列3個方面：（1）在成績表（cj1）中插入一條記錄，如果學(xué)生表（xs1）中沒有該學(xué)號對應(yīng)的記錄則不能插入。在學(xué)生表（xs1）中刪除指定學(xué)號記錄，同時也會刪除成績表（cj1）中對應(yīng)該學(xué)號的所有記錄。（2）在成績表（cj1）中插入一條記錄，如果課程表（kc1）中沒有該課程號對應(yīng)的記錄，則不能插入。在課程表（kc1）中刪除指定課程號記錄，同時也會刪除成績表（cj1）中對應(yīng)該課程號的所有記錄。（3）如果被引用表xs1表和kc1表學(xué)號鍵值更改了，對該鍵值的所有引用cj1表外鍵學(xué)號鍵值一致進(jìn)行更改。參照完整性約束因為成績表（cj1）已經(jīng)創(chuàng)建，所以需要修改（cj1）表結(jié)構(gòu)，添加表完整性約束，SQL語句如下：USExscj;ALTERTABLEcj1 ADDCONSTRAINTfr_xh_xsFOREIGNKEY(學(xué)號)REFERENCESxs1(學(xué)號) ONUPDATERESTRICT ONDELETECASCADE, ADDCONSTRAINTfr_kch_kcFOREIGNKEY(課程號)REFERENCESkc1(課程號) ONUPDATERESTRICT ONDELETECASCADE;3．刪除參照完整性約束在修改表結(jié)構(gòu)時刪除表參照完整性約束。ALTERTABLE表名 DROPFOREIGNKEY約束名;03域完整性約束列定義完整性約束表完整性約束完整性約束命名應(yīng)用程序保證數(shù)據(jù)完整性域完整性約束1．列定義完整性約束下列方式指定需要檢查的條件，在更新表數(shù)據(jù)的時候，MySQL會檢查更新后的數(shù)據(jù)行是否滿足CHECK的條件。[CONSTRAINT[約束名]]CHECK(條件)例如：創(chuàng)建表xs2，性別只能是男（1）或女（0），出生日期只能在2001年1月1日以后。USExscj;DROPTABLEIFEXISTSxs2;CREATETABLExs2(

學(xué)號 char(6)NOTNULL,

姓名 char(4),

性別 tinyintCHECK(性別IN(1,0)),

出生日期 dateNOTNULL CHECK(出生日期>'2001-01-01'),

專業(yè) varchar(10));域完整性約束2．表完整性約束（1）列的完整性約束可以在所有列定義后作為表完整性約束。例如：USExscj;DROPTABLEIFEXISTSxs2;CREATETABLExs2(

學(xué)號 char(6)NOTNULL,

姓名 char(4),

性別 tinyint,

出生日期 date,

專業(yè) varchar(10), CHECK(性別IN(1,0)), CHECK(出生日期>'2001-01-01'));域完整性約束（2）如果完整性約束中需要表的兩列或多列，那么該完整性約束只能定義為表完整性約束。例如：USExscj;DROPTABLEIFEXISTSxs2;CREATETABLExs2(

學(xué)號 char(6)NOTNULL,

姓名 char(4),

性別 tinyint,

出生