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

第5章

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

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

課程號(hào) char(3) NOTNULLPRIMARYKEY,

課程名 varchar(8)NOTNULL,

開(kāi)課學(xué)期 tinyint NOTNULL,

學(xué)時(shí) tinyint NOTNULL,

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

學(xué)號(hào) CHAR(6)NOTNULL,

課程號(hào) CHAR(3)NOTNULL,

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

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

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

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

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

學(xué)號(hào) char(6)NOTNULL,

姓名 char(4),

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

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

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

學(xué)號(hào) char(6)NOTNULL,

姓名 char(4),

性別 tinyint,

出生日期 date,

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

學(xué)號(hào) char(6)NOTNULL,

姓名 char(4),

性別 tinyint,

出生