MySQL數(shù)據(jù)庫(kù)項(xiàng)目化教程（第2版）（微課版）課件 項(xiàng)目八 數(shù)據(jù)庫(kù)安全及性能優(yōu)化

MySQL數(shù)據(jù)庫(kù)項(xiàng)目化教程微課版V2項(xiàng)目八數(shù)據(jù)庫(kù)安全及性能優(yōu)化項(xiàng)目任務(wù)知識(shí)目標(biāo)學(xué)習(xí)目標(biāo)任務(wù)一數(shù)據(jù)庫(kù)用戶管理任務(wù)二數(shù)據(jù)庫(kù)權(quán)限管理任務(wù)三數(shù)據(jù)庫(kù)性能優(yōu)化（1）掌握創(chuàng)建用戶、刪除用戶的方法（2）掌握修改用戶密碼的方法（3）掌握用戶權(quán)限授予與回收的方法（4）掌握優(yōu)化服務(wù)、分析數(shù)據(jù)表、優(yōu)化查詢的方法能力目標(biāo)素質(zhì)目標(biāo)學(xué)習(xí)目標(biāo)（1）能夠創(chuàng)建用戶、刪除用戶（2）能夠修改用戶密碼（3）能夠授予與回收用戶權(quán)限（4）能夠優(yōu)化服務(wù)器，分析表，以及優(yōu)化查詢效率（1）形成自主好學(xué)的學(xué)習(xí)態(tài)度（2）養(yǎng)成務(wù)實(shí)解決問(wèn)題的習(xí)慣（3）培養(yǎng)團(tuán)隊(duì)協(xié)作的精神思政引領(lǐng)（素養(yǎng)提示）學(xué)習(xí)目標(biāo)（1）了解數(shù)據(jù)庫(kù)管理員需要遵守的職業(yè)道德，日常工作過(guò)程中提高警惕性，遵守最高級(jí)別的安全措施，確保所有數(shù)據(jù)被保護(hù)在安全環(huán)境中運(yùn)行。（2）數(shù)據(jù)庫(kù)管理員要有工匠精神，以保證數(shù)據(jù)的一致性、準(zhǔn)確性、完整性和安全性，并為用戶提供高質(zhì)量的服務(wù)，要確?？蛻舻膯?wèn)題得到及時(shí)解決和處理。知識(shí)導(dǎo)圖學(xué)習(xí)目標(biāo)任務(wù)分析任務(wù)完成任務(wù)描述Part

2Part

3Part

1任務(wù)一數(shù)據(jù)庫(kù)用戶管理過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述MySQL數(shù)據(jù)庫(kù)系統(tǒng)中有兩類用戶，分別是root用戶和普通用戶。root用戶是管理員用戶，具有最高的權(quán)限，可以對(duì)整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)進(jìn)行管理操作，如創(chuàng)建用戶、刪除用戶、管理用戶的權(quán)限等。而普通用戶只能夠根據(jù)被賦予的某些權(quán)限進(jìn)行管理操作。在MySQL8.0版本中經(jīng)過(guò)代碼重構(gòu)，優(yōu)化器更加強(qiáng)大，新增了一些特性。8.0版本與先舊版本同時(shí)也存在不一樣的地方，特別是在創(chuàng)建用戶、修改密碼、授權(quán)等語(yǔ)句使用均有所不同。為了更好、更安全地管理數(shù)據(jù)庫(kù)，本任務(wù)以不同的方式進(jìn)行創(chuàng)建用戶、修改用戶密碼，以及刪除用戶等操作。任務(wù)描述過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述安裝MySQL數(shù)據(jù)庫(kù)時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)默認(rèn)安裝一個(gè)名為mysql的數(shù)據(jù)庫(kù)，該數(shù)據(jù)庫(kù)不能刪除，否則系統(tǒng)將無(wú)法正常運(yùn)行，mysql數(shù)據(jù)庫(kù)中包含了大量的表，如user表、coumns_piv表、host表、proc表、event表、servers表等，其中user表就是用戶管理表。任務(wù)分析user表中含有42個(gè)字段，其中在MySQL8.0版本中password字段更換成authentication_string字段，可以用以下語(yǔ)句查看該表中的信息：任務(wù)分析SELECT*FROMmysql.user;這些字段大致可以分為4類，具體如下。（1）用戶類字段當(dāng)應(yīng)用程序需要操作數(shù)據(jù)庫(kù)之前，必須先與數(shù)據(jù)庫(kù)建立連接，建立連接時(shí)用到的主機(jī)名、用戶名和密碼就是user表中的host、user、authentication_string字段，這些字段就是用戶類字段。建立數(shù)據(jù)庫(kù)連接時(shí)，輸入的信息必須與這3個(gè)字段的內(nèi)容相匹配。用以下語(yǔ)句可查看user表中用戶類的字段內(nèi)容：任務(wù)分析SELECThost,user,authentication_stringFROMmysql.user;（2）權(quán)限類字段在user表中，關(guān)于權(quán)限的字段有20多個(gè)，其中包括Select_priv、Insert_priv、Update_priv、Delete_priv、Create_priv等以priv結(jié)尾的字段，這些字段的權(quán)限對(duì)整個(gè)數(shù)據(jù)庫(kù)有效，它的取值只有N或Y，其中N表示該用戶不具有對(duì)應(yīng)的權(quán)限，Y表示該用戶具有對(duì)應(yīng)的權(quán)限。為了安全起見(jiàn)，普通用戶的權(quán)限默認(rèn)是N，也就是說(shuō)，如果普通用戶要具有相應(yīng)的權(quán)限，必須把對(duì)應(yīng)字段的值由N改為Y?？梢酝ㄟ^(guò)以下語(yǔ)句查看以上幾個(gè)權(quán)限類字段的值。任務(wù)分析SELECTSelect_priv,Insert_priv,Update_priv,Delete_priv,Create_privFROMmysql.user;（3）安全類字段在user表中，有6個(gè)字段用于管理用戶的安全信息，具體如下。①ssl_tpye和ssl_cipher：用于加密。②x509_issuer和x509_subject：用于標(biāo)識(shí)用戶。③plugin和authentication_string：用于存儲(chǔ)與授權(quán)相關(guān)的插件。任務(wù)分析（4）資源控制類字段在user表中，用于限制用戶使用資源的字段有4個(gè)，具體如下。①max_questions：表示每小時(shí)內(nèi)允許用戶執(zhí)行查詢數(shù)據(jù)庫(kù)操作的次數(shù)。②max_updates：表示每小時(shí)內(nèi)允許用戶執(zhí)行更新數(shù)據(jù)庫(kù)操作的次數(shù)。③max_connections：表示每小時(shí)內(nèi)允許用戶執(zhí)行連接數(shù)據(jù)庫(kù)操作的次數(shù)。④max_user_conntions：表示單個(gè)用戶同時(shí)連接數(shù)據(jù)庫(kù)的次數(shù)。任務(wù)分析過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述任務(wù)完成（1）使用CREATEUSER語(yǔ)句創(chuàng)建用戶使用CREATEUSER語(yǔ)句創(chuàng)建用戶是比較常用的方法，這種方法安全、準(zhǔn)確、錯(cuò)誤少，但是沒(méi)有任何權(quán)限的，如果需要設(shè)置權(quán)限，再借助其他授權(quán)語(yǔ)句設(shè)置權(quán)限。權(quán)限的管理將在后面的內(nèi)容中詳細(xì)介紹。語(yǔ)法格式如下：1．創(chuàng)建用戶CREATEUSER'username'@'hostname'IDENTIFIEDBY'new_passwrod';任務(wù)完成【例8-1】使用CREATEUSER語(yǔ)句為數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)用戶，用戶名為tc_user，密碼為tc123456。具體代碼如下所示：任務(wù)完成使用以上語(yǔ)句創(chuàng)建用戶之后，用SELECT語(yǔ)句驗(yàn)證創(chuàng)建用戶是否成功，具體代碼如下所示：任務(wù)完成（2）使用INSERT語(yǔ)句創(chuàng)建用戶使用INSERT語(yǔ)句創(chuàng)建用戶，向user表中插入用戶信息，和向普通表中添加一條記錄一樣，但必須擁有對(duì)mysql.user表有INSERT權(quán)限。通常INSERT語(yǔ)句只添加host,user,authentication_string這3個(gè)字段的值。語(yǔ)法格式如下：1．創(chuàng)建用戶INSERTINTOmysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('hostname','username','password','','','');任務(wù)完成語(yǔ)法格式如下：1．創(chuàng)建用戶INSERTINTOmysql.user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)VALUES('hostname','username','password','','','');以上語(yǔ)法中，由于user表中的ssl_cipher、x509_issuer、x509_subject字段沒(méi)有默認(rèn)值，所以需要為這3個(gè)字段添加一個(gè)值，值為空即可。任務(wù)完成【例8-2】使用INSERT語(yǔ)句為數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)用戶，主機(jī)名為hostname，用戶名為username，密碼為password。具體代碼如下所示：1．創(chuàng)建用戶任務(wù)完成使用以上語(yǔ)句創(chuàng)建用戶之后，用SELECT語(yǔ)句驗(yàn)證創(chuàng)建用戶是否成功，具體代碼如下所示：1．創(chuàng)建用戶任務(wù)完成2．刪除用戶在MySQL系統(tǒng)中，可以創(chuàng)建用戶，也可以刪除用戶，刪除用戶有兩種方式，分別為使用DROPUSER語(yǔ)句和使用DELETE語(yǔ)句，下面分別介紹這兩種方法。（1）使用DROPUSER語(yǔ)句刪除用戶使用DROPUSER語(yǔ)句刪除用戶時(shí)，需要具有DROPUSER的權(quán)限。語(yǔ)法格式如下：DROPUSER'username'@'hostname';任務(wù)完成2．刪除用戶【例8-3】使用DROPUSER語(yǔ)句刪除學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中的tc_user用戶。具體代碼如下所示：任務(wù)完成2．刪除用戶使用以上語(yǔ)句刪除用戶之后，用SELECT語(yǔ)句驗(yàn)證是否刪除成功，具體代碼如下所示：任務(wù)完成2．刪除用戶（2）使用DELETE語(yǔ)句刪除用戶使用DELETE語(yǔ)句刪除用戶與刪除普通表中的數(shù)據(jù)一樣，但必須具有DELETE的權(quán)限。語(yǔ)法格式如下：DELETEFROMmysql.userWHEREhost='hostname'ANDuser='username';任務(wù)完成2．刪除用戶【例8-4】使用DELETE語(yǔ)句刪除學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中的username用戶。具體代碼如下所示：任務(wù)完成2．刪除用戶使用以上語(yǔ)句刪除用戶之后，用SELECT語(yǔ)句驗(yàn)證是否刪除成功，具體代碼如下所示：任務(wù)完成3．修改用戶密碼在MySQL數(shù)據(jù)庫(kù)系統(tǒng)中，用戶密碼至關(guān)重要，一旦密碼泄漏給非法用戶，非法用戶就可能獲得或者破壞數(shù)據(jù)庫(kù)中的數(shù)據(jù)。所以密碼一旦丟失，就應(yīng)該立即修改密碼。默認(rèn)用戶root是管理員，root用戶不僅可以修改自己的密碼，還可以修改普通用戶的密碼，而普通用戶只能修改自己的密碼。修改用戶密碼的方法有如下幾種。任務(wù)完成3．修改用戶密碼（1）修改root用戶密碼①使用UPDATE語(yǔ)句修改密碼使用UPDATE語(yǔ)句修改root用戶的密碼和修改普通表中的數(shù)據(jù)一樣，root用戶的密碼保存在mysql.user表中，所以root用戶登錄到數(shù)據(jù)庫(kù)之后，就可以使用UPDATE語(yǔ)句修改密碼了。語(yǔ)法格式如下：UPDATEmysql.userSETauthentication_string='new_password'WHEREUser='username'ANDHost='hostname';任務(wù)完成3．修改用戶密碼【例8-5】通過(guò)使用UPDATE語(yǔ)句修改學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中root用戶的密碼，新密碼為C123456。root用戶登錄到數(shù)據(jù)庫(kù)之后，執(zhí)行SQL語(yǔ)句：任務(wù)完成3．修改用戶密碼②使用ALTER語(yǔ)句修改密碼root用戶在修改密碼時(shí)，可以在MySQL命令行窗口中執(zhí)行ALTERUSER命令修改密碼。ALTER命令語(yǔ)法格式如下：ALTERUSER'username'@'hostname'IDENTIFIEDWITHmysql_native_passwordBY'new_password';任務(wù)完成3．修改用戶密碼【例8-6】在MySQL命令行窗口中，通過(guò)ALTER語(yǔ)句修改學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中root用戶的密碼，新密碼為c123456。root用戶登錄到數(shù)據(jù)庫(kù)之后，執(zhí)行SQL語(yǔ)句：任務(wù)完成3．修改用戶密碼③使用SET語(yǔ)句修改密碼除了前面兩種修改密碼的方法以外，還可以直接使用SET語(yǔ)句修改root的密碼，需要特別注意的是，使用SET語(yǔ)句修改的密碼是不加密的。語(yǔ)法格式如下：SETPASSWORD='new_password';任務(wù)完成3．修改用戶密碼【例8-7】在MySQL命令行窗口中，通過(guò)SET語(yǔ)句修改學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中root用戶的密碼，新密碼為123456。具體代碼如下所示：任務(wù)完成3．修改用戶密碼（2）修改普通用戶的密碼root用戶除可以修改自己的密碼外，還具有修改普通用戶密碼的權(quán)限，root用戶可利用2種方式修改普通用戶的密碼，下面分別介紹。任務(wù)完成3．修改用戶密碼①使用UPDATE語(yǔ)句修改普通用戶的密碼使用UPDATE語(yǔ)句修改普通用戶的密碼與修改root用戶密碼的方法相同，修改成功后也要使用FLUSHPRIVILEGES語(yǔ)句重新加載權(quán)限表，否則修改之后的密碼無(wú)法生效。UPDATEmysql.userSETauthentication_string='new_password'WHEREUser='username'ANDHost='hostname';任務(wù)完成【例8-8】在MySQL命令行窗口中，通過(guò)UPDATE語(yǔ)句修改數(shù)據(jù)庫(kù)中sel_user用戶的密碼，新密碼為123123。具體代碼如下所示：3．修改用戶密碼任務(wù)完成3．修改用戶密碼②使用SET語(yǔ)句修改普通用戶的密碼使用SET語(yǔ)句修改普通用戶的密碼與root用戶的密碼語(yǔ)法格式基本相同，不同的是，需要加FOR子句指定要修改哪個(gè)用戶的密碼，語(yǔ)法格式如下。SETPASSWORDFOR'username'@'hostname'='new_password';任務(wù)完成【例8-9】在MySQL命令行窗口中，使用SET語(yǔ)句修改數(shù)據(jù)庫(kù)系統(tǒng)st_user用戶的密碼，新密碼為st123456。具體代碼如下所示：3．修改用戶密碼任務(wù)完成3．修改用戶密碼（3）普通用戶修改自己的密碼普通用戶也具有修改自己密碼的權(quán)限，方法與root修改密碼方法一樣，但需要先用有效的普通用戶名及原密碼登錄到MySQL，然后再使用SET語(yǔ)句修改自己的密碼，修改語(yǔ)法如下：SETPASSWORD=’new_password’;任務(wù)完成3．修改用戶密碼

【例8-10】普通用戶testuser通過(guò)原密碼st123456登錄到MySQL之后，將密碼改為123456，具體代碼如下所示：任務(wù)總結(jié)本任務(wù)主要介紹數(shù)據(jù)庫(kù)用戶管理，通過(guò)3個(gè)方面介紹了用戶的管理方式，一是以root用戶的身份采用2種方法創(chuàng)建普通用戶，以及采用2種方法刪除普通用戶；二是以root用戶的身份修改自己的密碼，以及修改普通用戶的密碼；三是普通用戶修改自己的密碼。通過(guò)以上的介紹，希望讀者學(xué)會(huì)利用root管理員用戶管理普通用戶的方法，了解普通用戶只能夠根據(jù)被賦予的某些權(quán)限進(jìn)行管理操作。任務(wù)分析任務(wù)完成任務(wù)描述Part

2Part

3Part

1任務(wù)二數(shù)據(jù)庫(kù)權(quán)限管理過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述數(shù)據(jù)庫(kù)的安全關(guān)系到整個(gè)應(yīng)用系統(tǒng)的安全，其很大程度上依賴于用戶權(quán)限的管理，數(shù)據(jù)庫(kù)的管理員應(yīng)該為每個(gè)數(shù)據(jù)庫(kù)的普通用戶設(shè)置相應(yīng)的權(quán)限。本任務(wù)主要涉及學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)用戶的權(quán)限管理，包括權(quán)限的授予、權(quán)限的查看、權(quán)限的回收。任務(wù)描述過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述

MySQL服務(wù)器通過(guò)MySQL權(quán)限表控制用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，MySQL權(quán)限表存放在mysql數(shù)據(jù)庫(kù)中，這些MySQL權(quán)限表包括user、db、table_priv、columns_priv、host等，下面分別介紹。①user權(quán)限表：記錄允許連接到服務(wù)器的用戶信息，里面的權(quán)限是全局級(jí)的。②db權(quán)限表：記錄各個(gè)用戶在各個(gè)數(shù)據(jù)庫(kù)上的操作權(quán)限。③table_priv權(quán)限表：記錄數(shù)據(jù)表級(jí)的操作權(quán)限。④columns_priv權(quán)限表：記錄數(shù)據(jù)列級(jí)的操作權(quán)限。⑤host權(quán)限表：配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫(kù)級(jí)的操作權(quán)限進(jìn)行更細(xì)致的控制。這個(gè)權(quán)限表不受GRANT和REVOKE語(yǔ)句的影響。任務(wù)分析下面對(duì)MySQL數(shù)據(jù)庫(kù)中的權(quán)限做如下具體說(shuō)明。①INSERT權(quán)限：代表允許向表中插入數(shù)據(jù)，同時(shí)，在執(zhí)行ANALYZETABLE、OPTIMIZETABLE、REPAIRTABLE語(yǔ)句時(shí)也需要INSERT權(quán)限。②DELETE權(quán)限：代表允許刪除行數(shù)據(jù)。③DROP權(quán)限：代表允許刪除數(shù)據(jù)庫(kù)、表、視圖。④EVENT權(quán)限：代表允許查詢、創(chuàng)建、修改、刪除MySQL事件。任務(wù)分析⑤EXECUTE權(quán)限：代表允許執(zhí)行存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)。⑥FILE權(quán)限：代表允許在MySQL可以訪問(wèn)的目錄中進(jìn)行讀/寫(xiě)磁盤(pán)文件的操作，可使用的命令包括LOADDATAINFILE，SELECT...INTOOUTFILE，LOADFILE()。⑦GRANTOPTION權(quán)限：代表允許此用戶授予或者回收其他用戶的權(quán)限。⑧INDEX權(quán)限：代表允許創(chuàng)建和刪除索引。⑨LOCK權(quán)限：代表允許對(duì)擁有SELECT權(quán)限的表進(jìn)行鎖定，以防止其他鏈接對(duì)此表的讀或?qū)?。任?wù)分析過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述MySQL數(shù)據(jù)庫(kù)中的root用戶默認(rèn)擁有權(quán)限，普通用戶默認(rèn)不擁有權(quán)限。也就是說(shuō)，普通用戶默認(rèn)不能對(duì)數(shù)據(jù)庫(kù)進(jìn)行增、刪、改、查等操作。在MySQL數(shù)據(jù)庫(kù)中，可以用GRANT語(yǔ)句為用戶授予權(quán)限，但是MySQL8.0以前的版本支持GRANT語(yǔ)句在授權(quán)的時(shí)候隱式的創(chuàng)建用戶，MySQL8.0以后版本必須先創(chuàng)建用戶再授權(quán)。任務(wù)完成1．MySQL權(quán)限的授予任務(wù)完成語(yǔ)法格式如下：GRANTprivileges[(columns)]ONdatabase.tableTO'username'@'hostname'WITHwith_option;1．MySQL權(quán)限的授予以上語(yǔ)法格式中，privileges表示權(quán)限的類型，columns表示權(quán)限作用的字段，可以省略，如果省略代表權(quán)限作用于整張表，database.table表示數(shù)據(jù)庫(kù)的表名，username表示數(shù)據(jù)庫(kù)的用戶名，hostname表示主機(jī)名，passwrod表示用戶的密碼。任務(wù)完成WITH后面的with_option有5個(gè)選項(xiàng)，具體如下。1．MySQL權(quán)限的授予①GRANTOPTION：將權(quán)限授予用戶。②MAX_QUERIES_PER_HOUR：一個(gè)用戶在一個(gè)小時(shí)內(nèi)可以執(zhí)行查詢的次數(shù)（基本包含所有語(yǔ)句）。③MAX_UPDATES_PER_HOUR：一個(gè)用戶在一個(gè)小時(shí)內(nèi)可以執(zhí)行修改的次數(shù)（僅包含修改數(shù)據(jù)庫(kù)或表的語(yǔ)句）。④MAX_CONNECTIONS_PER_HOUR：一個(gè)用戶在一個(gè)小時(shí)內(nèi)可以連接MySQL的時(shí)間。⑤MAX_USER_CONNECTIONS：一個(gè)用戶在同一時(shí)間可以連接MySQL實(shí)例的數(shù)量。任務(wù)完成【例8-13】使用CREATE語(yǔ)句為學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)的用戶授權(quán)，使用戶名為st_user對(duì)competition數(shù)據(jù)庫(kù)中的所有表具有查詢和插入操作的權(quán)限，使用GRANTOPTION子句實(shí)現(xiàn)。具體代碼如下所示：1．MySQL權(quán)限的授予2．MySQL權(quán)限的查看任務(wù)完成在MySQL數(shù)據(jù)庫(kù)中，查看用戶權(quán)限的方法很簡(jiǎn)單，直接用SHOWGRANTS語(yǔ)句即可，其中，root用戶需要用FOR子句指定查看的用戶。語(yǔ)法格式如下：SHOWGRANTSFOR'username'@'hostname';2．MySQL權(quán)限的查看任務(wù)完成【例8-14】使用SHOWGRANTS語(yǔ)句查看上一節(jié)中創(chuàng)建的st_user用戶的權(quán)限。具體代碼如下所示：3．MySQL權(quán)限的回收任務(wù)完成在MySQL數(shù)據(jù)庫(kù)中，權(quán)限授予某個(gè)用戶之后，還可以根據(jù)具體需要回收部分或者全部權(quán)限，使用REVOKE語(yǔ)句可實(shí)現(xiàn)權(quán)限的回收。語(yǔ)法格式如下：REVOKEprivileges[(columns)]ONdatabase.tableFROM'username'@'hostname';以上語(yǔ)法格式中，privileges表示權(quán)限的類型，columns表示權(quán)限作用的字段，可以省略，如果省略代表權(quán)限作用于整張表，database.table表示數(shù)據(jù)庫(kù)的表名，username表示數(shù)據(jù)庫(kù)的用戶名，hostname表示主機(jī)名。3．MySQL權(quán)限的回收任務(wù)完成【例8-15】使用REVOKE語(yǔ)句回收用戶st_user對(duì)學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中所有表的INSERT權(quán)限。具體代碼如下所示：3．MySQL權(quán)限的回收任務(wù)完成以上語(yǔ)句執(zhí)行成功之后，使用SHOWGRANTS語(yǔ)句查看st_suer用戶的權(quán)限，具體代碼如下所示：3．MySQL權(quán)限的回收任務(wù)完成使用REVOKE語(yǔ)句還可以一次性回收所有權(quán)限，語(yǔ)法格式如下：REVOKEALLprivileges,GRANTOPTIONFROM'username'@'hostname';3．MySQL權(quán)限的回收任務(wù)完成【例8-16】使用REVOKE語(yǔ)句回收用戶st_user的所有權(quán)限。以上語(yǔ)句執(zhí)行成功之后，使用SHOWGRANTS語(yǔ)句查看st_user用戶的權(quán)限，具體代碼如下所示：任務(wù)總結(jié)本任務(wù)介紹學(xué)生競(jìng)賽項(xiàng)目管理系統(tǒng)數(shù)據(jù)庫(kù)中用戶的權(quán)限管理，包括三個(gè)方面，一是權(quán)限的授予，二是權(quán)限的查看，三是權(quán)限的回收。數(shù)據(jù)庫(kù)權(quán)限的管理關(guān)系到整個(gè)應(yīng)用系統(tǒng)的安全，在實(shí)際應(yīng)用中，數(shù)據(jù)庫(kù)管理員應(yīng)該為數(shù)據(jù)庫(kù)的每個(gè)普通用戶以最小權(quán)限原則設(shè)置相應(yīng)的權(quán)限。任務(wù)分析任務(wù)完成任務(wù)描述Part

2Part

3Part

1任務(wù)三數(shù)據(jù)庫(kù)性能優(yōu)化過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述本任務(wù)通過(guò)服務(wù)器優(yōu)化、表結(jié)構(gòu)優(yōu)化、查詢優(yōu)化等技術(shù)提高數(shù)據(jù)庫(kù)的整體性能，包括使用EXPLAIN語(yǔ)句對(duì)SELECT語(yǔ)句的執(zhí)行效果進(jìn)行分析，并通過(guò)分析提出優(yōu)化查詢的方法；使用ANALYZETABLE語(yǔ)句分析表；使用CHECK語(yǔ)句檢查表；使用OPTIMIZETABLE語(yǔ)句優(yōu)化表；使用REPAIRTABLE語(yǔ)句修復(fù)表等。任務(wù)描述過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述優(yōu)化MySQL數(shù)據(jù)庫(kù)是一項(xiàng)非常重要的技術(shù)，是數(shù)據(jù)庫(kù)管理員的必備技能之一，不論是進(jìn)行數(shù)據(jù)庫(kù)表結(jié)構(gòu)的設(shè)計(jì)，還是創(chuàng)建索引，創(chuàng)建、查詢數(shù)據(jù)庫(kù)，都需要注意數(shù)據(jù)庫(kù)的性能優(yōu)化，數(shù)據(jù)庫(kù)的性能優(yōu)化包括很多方面，例如，優(yōu)化MySQL服務(wù)器，優(yōu)化數(shù)據(jù)庫(kù)表結(jié)構(gòu)，優(yōu)化查詢速度，優(yōu)化更新速度等，其目的都是使MySQL數(shù)據(jù)庫(kù)運(yùn)行速度更快，占用磁盤(pán)空間更小。任務(wù)分析過(guò)渡頁(yè)任務(wù)分析任務(wù)完成Part

1Part

2Part

3任務(wù)描述（1）通過(guò)修改MySQL的my.ini文件進(jìn)行服務(wù)器的性能優(yōu)化通過(guò)修改my.ini文件的配置可以提高服務(wù)器的性能。在MySQL配置文件中，索引的緩沖區(qū)大小默認(rèn)為16M，可以修改這個(gè)值來(lái)提高索引的處理性能。例如，將默認(rèn)值修改為256M。打開(kāi)my.ini文件，直接在[mysqld]后面加一行代碼如下：任務(wù)完成1．優(yōu)化MySQL服務(wù)器Key_buffer_size=256M若數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存容量為4GB，推薦設(shè)置參數(shù)值如下：任務(wù)完成1．優(yōu)化MySQL服務(wù)器sort_buffer_size=6M //排序查詢操作的緩沖區(qū)大小read_buffer_size=4M //讀查詢操作的緩沖區(qū)大小join_buffer_size=8M //聯(lián)合查詢操作的緩沖區(qū)大小query_cache_size=64M //查詢緩沖區(qū)的大小max_connections=800 //允許最大連接的進(jìn)程數(shù)（1）為多表連接查詢添加中間表在進(jìn)行數(shù)據(jù)查詢時(shí)，往往需要進(jìn)行多表連接查詢，但如果經(jīng)常進(jìn)行多表連接查詢，會(huì)影響數(shù)據(jù)庫(kù)的性能。為提高數(shù)據(jù)庫(kù)性能，可以建立一個(gè)中間表。中間表的字段就是經(jīng)常要查詢的來(lái)自多張表的字段，通過(guò)連接查詢，將數(shù)據(jù)插入中間表中，中間表的內(nèi)容來(lái)自基表，在以后的查詢中，就可以直接查詢中間表，提高查詢速度。任務(wù)完成2．優(yōu)化表結(jié)構(gòu)與數(shù)據(jù)操作【例8-17】在數(shù)據(jù)庫(kù)competition中，假設(shè)要經(jīng)常查詢學(xué)生的姓名、班級(jí)、院系信息，但這些字段分布在student、department、class三張數(shù)據(jù)表，每次查詢必須進(jìn)行連接查詢，為了提高查詢效率，可以創(chuàng)建一張中間表，實(shí)現(xiàn)數(shù)據(jù)查詢的優(yōu)化。具體操作如下：任務(wù)完成2．優(yōu)化表結(jié)構(gòu)與數(shù)據(jù)操作通過(guò)鏈接查詢，將數(shù)據(jù)插入中間表中，具體代碼如下所示：任務(wù)完成2．優(yōu)化表結(jié)構(gòu)與數(shù)據(jù)操作查詢此信息，只需要直接通過(guò)中間表student_info快速查詢。具體代碼如下所示：任務(wù)完成2．優(yōu)化表結(jié)構(gòu)與數(shù)據(jù)操作（2）增加冗余字段在創(chuàng)建數(shù)據(jù)表的時(shí)候，通過(guò)增加冗余字段，可以減少連接查詢，從而提高查詢性能，例如，在數(shù)據(jù)庫(kù)competition中，院系名存在于院系表department中，競(jìng)賽項(xiàng)目表project中有院系表的主鍵dp_id，如果要查詢project表中已有的院系名，必須通過(guò)這兩張表的dp_id字段進(jìn)行連接查詢，但這樣會(huì)增加數(shù)據(jù)庫(kù)的負(fù)擔(dān)。為了提高性能，在project表中增加冗余字段dp_name，用來(lái)存儲(chǔ)院系名，這樣就可以優(yōu)化查詢的性能。任務(wù)完成2．優(yōu)化表結(jié)構(gòu)與數(shù)據(jù)操作3．設(shè)置合理的數(shù)據(jù)類型和屬性任務(wù)完成（1）合理設(shè)置字段類型在創(chuàng)建數(shù)據(jù)表時(shí)，字段的寬度可以設(shè)置得盡可能小，例如，數(shù)據(jù)庫(kù)competition中project表中的字段dp_address，考慮到地址信息的長(zhǎng)度只有50個(gè)字符左右，因此沒(méi)必要將其數(shù)據(jù)類型設(shè)置為CHAR(255)，而可以設(shè)置為CHAR(50)或者VARCHAR(50)。對(duì)于長(zhǎng)度取值比較固定的字段，可以使用ENUM類型代替VARCHAR類型，如性別、民族、省份等，使用ENUM類型的處理速度更快。3．設(shè)置合理的數(shù)據(jù)類型和屬性任務(wù)完成（2）為每張表設(shè)置一個(gè)ID作為其主鍵在創(chuàng)建數(shù)據(jù)表時(shí)，可為表設(shè)置一個(gè)ID作為表的主鍵，并且設(shè)置為INT類型（推薦使用UNSIGNED）、自動(dòng)增長(zhǎng)（AUTO_INCREMENT）。（3）盡量避免定義字段為NULL根據(jù)實(shí)際情況，盡量將字段設(shè)置為NOTNULL，這樣在執(zhí)行查詢時(shí)，數(shù)據(jù)庫(kù)就無(wú)須比較NULL值，從而提高查詢效率。4．優(yōu)化插入記錄的速度任務(wù)完成有很多種方法可以優(yōu)化插入記錄的速度，下面主要介紹兩種方法。（1）如果數(shù)據(jù)表中有大量記錄，可以采用先加載數(shù)據(jù)再建立索引的方法，如果已經(jīng)建立了索引，可以先將索引禁止。原因是每當(dāng)有新記錄要插入表時(shí)都會(huì)刷新索引，這樣會(huì)降低插入的速度。5．對(duì)表進(jìn)行分析、檢查、優(yōu)化和修復(fù)任務(wù)完成（1）使用ANALYZETABLE語(yǔ)句分析表MySQL的Optimizer（優(yōu)化器）在優(yōu)化SQL語(yǔ)句時(shí)，首先需要收集一些相關(guān)信息，其中就包括表的cardinality（散列程度），它表示某個(gè)索引對(duì)應(yīng)的字段包含多少個(gè)不同的值，如果cardinality大于數(shù)據(jù)的實(shí)際散列程度，那么索引就基本失效了?？梢允褂肧HOWINDEX語(yǔ)句查看索引的散列程度，語(yǔ)法格式如下：SHOWINDEXFROMtable_name;5．對(duì)表進(jìn)行分析、檢查、優(yōu)化和修復(fù)任務(wù)完成查看學(xué)生數(shù)據(jù)表索引的散列程度，具體代碼如下所示：5．對(duì)表進(jìn)行分析、檢查、優(yōu)化和修復(fù)任務(wù)完成（2）使用CHECKTABLE語(yǔ)句檢查表在實(shí)際應(yīng)用數(shù)據(jù)庫(kù)的過(guò)程中，可能會(huì)遇到數(shù)據(jù)庫(kù)錯(cuò)誤的情況，例如，數(shù)據(jù)寫(xiě)入磁盤(pán)時(shí)出錯(cuò)，或者數(shù)據(jù)庫(kù)沒(méi)有正常關(guān)閉?？梢允褂肅HECKTABLE語(yǔ)句來(lái)檢查數(shù)據(jù)庫(kù)是否有錯(cuò)誤，例如，檢查calss表是否有錯(cuò)誤的SQL語(yǔ)句，具體代碼如下所示：5．對(duì)表進(jìn)行分析、檢查、優(yōu)化和修復(fù)任務(wù)完成（3）使用OPTIMIZETABLE語(yǔ)句優(yōu)化表對(duì)數(shù)據(jù)表執(zhí)行刪除操作時(shí)，數(shù)據(jù)所占用的磁盤(pán)空間不會(huì)立即收回，另外，利用VARCHAR定義的字段，長(zhǎng)時(shí)間后也會(huì)產(chǎn)生碎片，這些碎片浪費(fèi)了很大空間，同時(shí)也對(duì)查詢效率產(chǎn)生很大影響。使用OPTIMIZETABLE語(yǔ)句可以實(shí)現(xiàn)回收碎片的功能，提升查詢性能，達(dá)到優(yōu)化的效果。5．對(duì)表進(jìn)行分析、檢查、優(yōu)化和修復(fù)任務(wù)完成例如，優(yōu)化student表的SQL語(yǔ)句，具體代碼如下所示：5．對(duì)表進(jìn)行分析、檢查、優(yōu)化和修復(fù)任務(wù)完成（4）使用REPAIRTABLE語(yǔ)句修復(fù)表使用REPAIRTABLE語(yǔ)句可以修復(fù)表的索引，提高查詢索引的性能，例如，修復(fù)student表的SQL語(yǔ)句，具體代碼如下所示：6．優(yōu)化查詢?nèi)蝿?wù)完成在MySQL數(shù)據(jù)庫(kù)中，可以使用EXPLAIN和DESCRIBE語(yǔ)句分析表，以幫助用戶選擇更好的索引，寫(xiě)出更優(yōu)化的查詢語(yǔ)句。（1）使用EXPLAIN語(yǔ)句分析表在MySQL中，捕捉性能問(wèn)題最常用的方法就是打開(kāi)慢查詢，定位執(zhí)行效率差的SQL。當(dāng)定位到一個(gè)SQL后，還需要知道該SQL的執(zhí)行計(jì)劃，比如是全表掃描還是索引掃描，這些都需要通過(guò)EXPLAIN語(yǔ)句來(lái)完成。EXPLAIN語(yǔ)句是查看優(yōu)化器如何決定查詢的主要方法，可以幫助我們深入了解MySQL基于開(kāi)銷的優(yōu)化器，還可以使我們獲得很多優(yōu)化器考慮到的訪問(wèn)策略的細(xì)節(jié)。6．優(yōu)化查詢?nèi)蝿?wù)完成使用EXPLAIN語(yǔ)句優(yōu)化student查詢，具體代碼如下所示：6．優(yōu)化查詢?nèi)蝿?wù)完成表8-1對(duì)EXPLAIN語(yǔ)句輸出的相關(guān)信息進(jìn)行了說(shuō)明。列

值說(shuō)

明idSELECT標(biāo)識(shí)符select_typeSELECT類型：SIMPLE，PRIMARY，UNION，SUBQUERYtable輸出行分析表的表名partitions匹配的分區(qū)type連接類型（最重要，具體在圖8-2中說(shuō)明）possible_keys可供選擇的索引key實(shí)際使用的索引key_len實(shí)際使用的索引長(zhǎng)度ref與索引進(jìn)行比較的字段，也就是關(guān)聯(lián)表使用的字段rows將要被檢查的估算的行數(shù)filtered被表?xiàng)l件過(guò)濾的行數(shù)的百分比Extra附件信息6．優(yōu)化查詢?nèi)蝿?wù)完成EXPLAIN語(yǔ)句輸出的相關(guān)信息中，type最重要，它表示使用了哪種連接類別，是否使用了索引，它是使用EXPLAIN語(yǔ)句分析表的性能的重要指標(biāo)之一，表8-2中列出了type常用的取值。參數(shù)值說(shuō)明system表示表中只有一條記錄const表示表中有多條記錄，但只從中查詢一條記錄eq_ref表示多表連接時(shí)，后面的表使用了UNIQUE或者PRIMARYKEYref表示多表查詢時(shí)，后面的表使用了普通索引unique_subquery表示子查詢中使用了UNIQUE或者PRIMARYKEYindex_subquery表示子查詢中使用了普通索引range表示查詢語(yǔ)句給出了查詢范圍index表示對(duì)表中的索引進(jìn)行了完整掃描，速度比較慢all表示對(duì)表中的數(shù)據(jù)全部掃描，速度非常慢6．優(yōu)化查詢?nèi)蝿?wù)完成EXPLAIN語(yǔ)句輸出的相關(guān)信息中，除type外，Extra項(xiàng)也是關(guān)鍵指標(biāo)之一，要想讓查詢盡可能快，應(yīng)該注意Extra字段的取值情況，如表8-3所示。字段值說(shuō)

明Distinct找到了與查詢條件匹配的第一條記錄后，就不再搜索其他記錄Notexists一旦找到了匹配的LEFTJOIN標(biāo)準(zhǔn)的行，就不再搜索其他記錄Rangecheckedforeachrecord沒(méi)有找到合適的索引，對(duì)于前一張表的每行連接，它會(huì)做一個(gè)檢驗(yàn)，用于決定使用哪個(gè)索引，并且使用這個(gè)索引從表中取得記錄，這個(gè)過(guò)程不