項目七

數(shù)據(jù)安全與優(yōu)化2023年2月用戶與授權(quán)目錄Content1數(shù)據(jù)庫備份與恢復(fù)2數(shù)據(jù)庫性能優(yōu)化3項目導(dǎo)言零在全球經(jīng)濟體完善數(shù)據(jù)保護立法的當(dāng)下，數(shù)據(jù)安全如何做到位，保障數(shù)據(jù)的長遠(yuǎn)發(fā)展？在全球互聯(lián)網(wǎng)用戶訪問特點，呈現(xiàn)碎片化特征時，如何對數(shù)據(jù)庫進行性能優(yōu)化，助力流量、營收增長？這兩個問題，都是需要在維護數(shù)據(jù)庫時著重注意的。讓我們一起跟隨本項目，一起來了解一下吧。學(xué)習(xí)目標(biāo)零知識目標(biāo)了解用戶與權(quán)限的概念；熟悉用戶的增刪改查操作；了解權(quán)限管理的概念與相關(guān)操作；掌握數(shù)據(jù)庫備份與恢復(fù)的方法；熟悉數(shù)據(jù)庫優(yōu)化方案。技能目標(biāo)具備獨立創(chuàng)建用戶與設(shè)定權(quán)限的能力；具備為數(shù)據(jù)庫備份與恢復(fù)的能力；具備獨立為數(shù)據(jù)庫性能優(yōu)化的能力。素養(yǎng)目標(biāo)具備精益求精、堅持不懈的精神；具備團隊協(xié)作能力；具備靈活的思維和處理分析問題的能力；具備責(zé)任心。任務(wù)7-1：用戶與權(quán)限壹創(chuàng)建用戶賬戶任務(wù)技能查看用戶信息、創(chuàng)建用戶、修改用戶密碼、重命名用戶名權(quán)限管理權(quán)限類型、權(quán)限授予、權(quán)限查詢、權(quán)限回收角色管理角色管理任務(wù)7-1：用戶與權(quán)限壹1創(chuàng)建用戶賬戶（1）查看用戶信息語法格式：usemysql;selectuser,hostfromuser;在MySQL安裝之后，系統(tǒng)會自動創(chuàng)建一個名為mysql的數(shù)據(jù)庫，該數(shù)據(jù)庫中有一個非常重要的表user，該表記錄了服務(wù)器的賬號及權(quán)限信息。在MySQL中，查看user表的用戶名、主機名信息。任務(wù)7-1：用戶與權(quán)限壹（2）創(chuàng)建用戶語法格式：CREATEUSER用戶名@主機名[identifiedby[密碼]];在MySQL中，默認(rèn)情況下只有一個root用戶來管理各類數(shù)據(jù)庫，但考慮其安全等因素，可以在MySQL中創(chuàng)建新的用戶來管理數(shù)據(jù)庫。任務(wù)7-1：用戶與權(quán)限壹在MySQL中，創(chuàng)建一個新用戶“zhang3”，密碼為“123456”。示例：createuser'zhang3'@'localhost'identifiedby'123456';任務(wù)7-1：用戶與權(quán)限壹新用戶創(chuàng)建成功后，可通過selectuser,hostfromuser;命令查看當(dāng)前服務(wù)器上的用戶情況。selectuser,hostfromuser;任務(wù)7-1：用戶與權(quán)限壹（3）修改用戶密碼語法格式：ALTERUSERzhang3@'localhost'IDENTIFIEDWITHMYSQL_NATIVE_PASSWORDBY'123';在MySQL中，只有root用戶才可以設(shè)置或修改當(dāng)前用戶或其他特定用戶的密碼。任務(wù)7-1：用戶與權(quán)限壹（4）重命名用戶名語法格式：RENAMEUSERold_userTOnew_user,[,old_userTOnew_user]...在數(shù)據(jù)庫中，如果用戶名設(shè)置錯誤，可以通過rename語句進行用戶名的重命名。任務(wù)7-1：用戶與權(quán)限壹修改zhang3的用戶名為zhangsan。示例：RENAMEUSERzhang3@localhosttozhangsan@localhost;任務(wù)7-1：用戶與權(quán)限壹2權(quán)限管理（1）權(quán)限類型權(quán)限名稱權(quán)限說明SELECT給予用戶使用SELECT語句訪問特定表的權(quán)限INSERT給予用戶使用INSERT語句向一個特定表中添加行的權(quán)限D(zhuǎn)ELETE

給予用戶使用DELETE語句從一個特定表中刪除行的權(quán)限UPDATE

給予用戶使用UPDATE語句修改特定表中值的權(quán)限REFERENCES給予用戶創(chuàng)建一個外鍵來參照特定表的權(quán)限CREATE給予用戶使用特定的名字創(chuàng)建一個表的權(quán)限ALTER給予用戶使用ALTER

TABLE語句修改表的權(quán)限INDEX給予用戶在表上定義索引的權(quán)限D(zhuǎn)ROP給予用戶刪除表的權(quán)限ALL或ALL

PRIVILEGES給予用戶對表所有的權(quán)限任務(wù)7-1：用戶與權(quán)限壹（2）權(quán)限授予語法格式：grant權(quán)限列表on目標(biāo)數(shù)據(jù)庫to用戶;在MySQL中，可以使用grant語句為用戶授予權(quán)限，授予權(quán)限操作一般由管理員根據(jù)實際工作需要進行授予。任務(wù)7-1：用戶與權(quán)限壹在MySQL中，授予“zhangsan”用戶在myStudent數(shù)據(jù)庫中所有表的SELECT和UPDATE權(quán)限。示例：grantselect,updateonmyStudent.*tozhangsan;任務(wù)7-1：用戶與權(quán)限壹（3）權(quán)限查詢語法格式：showgrantsfor'username'@'hostname';任務(wù)7-1：用戶與權(quán)限壹在MySQL中，查看“root”用戶在myStudent數(shù)據(jù)庫中所擁有的權(quán)限。示例：showgrantsfor'root'@'localhost';任務(wù)7-1：用戶與權(quán)限壹（4）權(quán)限回收語法格式：revoke權(quán)限列表on目標(biāo)數(shù)據(jù)庫from用戶;任務(wù)7-1：用戶與權(quán)限壹在MySQL中，回收“zhang3”用戶在myStudent數(shù)據(jù)庫中所有表的UPDATE權(quán)限。示例：revokeupdateonmyStudent.*fromzhang3;任務(wù)7-1：用戶與權(quán)限壹3角色管理語句描述CREATEROLE與DROPROLE角色創(chuàng)建和刪除GRANT與REVOKE用戶的角色分配和撤銷權(quán)限SHOWGRANTS表示顯示用戶的角色權(quán)限和角色分配SETDEFAULTROLE表示指定哪些賬戶角色默認(rèn)處于活動狀態(tài)SETROLE表示更改當(dāng)前會話中的活動角色CURRENT_ROLE()表示顯示當(dāng)前會話中的活動角色7-1：任務(wù)實施壹權(quán)限分配使用CREATEUSER創(chuàng)建用戶使用CREATEROLE創(chuàng)建角色并用GRANT分配權(quán)限給角色為每個用戶分配其所需的權(quán)限，使用GRANT語句列舉每個用戶的個人權(quán)限使用SHOWGRANTS驗證分配給用戶的權(quán)限使用DROPROLE刪除角色任務(wù)7-2：數(shù)據(jù)庫備份與恢復(fù)貳任務(wù)技能數(shù)據(jù)庫的備份備份數(shù)據(jù)庫的恢復(fù)恢復(fù)任務(wù)7-2：數(shù)據(jù)庫備份與恢復(fù)貳1數(shù)據(jù)庫的備份為了保證數(shù)據(jù)安全，數(shù)據(jù)庫管理員應(yīng)定期對數(shù)據(jù)庫進行備份。為了提升數(shù)據(jù)庫的安全性，備份操作應(yīng)定期進行，并且，在備份時要在不同的磁盤位置保存多個副本，以確保備份安全。語法格式：mysqldump-u用戶名-h主機名-p密碼數(shù)據(jù)庫名>文件名.sql;任務(wù)7-2：數(shù)據(jù)庫備份與恢復(fù)貳將用于測試的數(shù)據(jù)庫myStudent備份，備份文件存儲至“D:/bak.sql”目錄。示例：mysqldump-uroot-hlocalhost--default-character-set=gbk-p123456myStudent>D:/bak.sql任務(wù)7-2：數(shù)據(jù)庫備份與恢復(fù)貳2數(shù)據(jù)庫的恢復(fù)數(shù)據(jù)庫恢復(fù)就是當(dāng)數(shù)據(jù)庫出現(xiàn)故障時，將備份的數(shù)據(jù)庫加載到系統(tǒng)，使數(shù)據(jù)庫恢復(fù)到備份時的正確狀態(tài)。對于使用mysqldump命令備份形成的.sql文件，可以使用mysql命令導(dǎo)入到數(shù)據(jù)庫中。語法格式：mysql-u用戶名-p密碼數(shù)據(jù)庫名<文件名.sql;任務(wù)7-2：數(shù)據(jù)庫備份與恢復(fù)貳將用于測試的數(shù)據(jù)庫testStudent恢復(fù)，采用備份文件為“D:/bak.sql”。示例：mysql-uroot-p123456testStudent<D:/bak.sql7-2：任務(wù)實施貳數(shù)據(jù)庫備份與恢復(fù)打開myStudent數(shù)據(jù)庫，用mysqldump備份Departments表，將文件保存在“D:/”文件夾中刪除數(shù)據(jù)表再用.sql文件導(dǎo)入進行恢復(fù)，查看恢復(fù)情況任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁優(yōu)化MySQL服務(wù)器任務(wù)技能通過修改my.ini文件進行性能優(yōu)化、通過MySQL控制臺進行性能優(yōu)化優(yōu)化表結(jié)構(gòu)通過分解表來提高查詢效率、通過中間表來提高查詢效率、通過保留冗余字段提高查詢效率優(yōu)化查詢分析查詢語句、使用索引優(yōu)化查詢、優(yōu)化子查詢、SQL語句的優(yōu)化任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁1優(yōu)化MySQL服務(wù)器（1）通過修改my.ini文件進行性能優(yōu)化MySQL配置文件（my.ini文件）保存了服務(wù)器的配置信息，通過修改my.ini文件的內(nèi)容可以優(yōu)化服務(wù)器，提高性能。例如，在默認(rèn)情況下，索引的緩沖區(qū)大小為16MB，為得到更好的索引處理性能，可以指定索引的緩沖區(qū)大小?，F(xiàn)要指定索引的緩沖區(qū)大小為256MB，可以打開my.ini文件進行修改。語法格式：key_buffer_size=256M參數(shù)描述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指定MySQL允許的最大連接進程數(shù)任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁（2）通過MySQL控制臺進行性能優(yōu)化數(shù)據(jù)庫管理人員可以使用showstatus或showvariableslike語句來查詢MySQL數(shù)據(jù)庫的性能參數(shù)，然后用set語句對系統(tǒng)變量進行賦值。參數(shù)描述Connections連接MySQL服務(wù)器的次數(shù)UptimeMySQL服務(wù)器的上線時間Slow_queries慢查詢的次數(shù)Com_select查詢操作的次數(shù)Com_insert插入操作的次數(shù)Com_update更新操作的次數(shù)Com_delete刪除操作的次數(shù)任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁查看試圖連接到MySQL（不管是否連接成功）的連接次數(shù)。示例：showStatusLike'Connections';任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁要設(shè)置查詢緩沖區(qū)的系統(tǒng)變量，可先執(zhí)行以下命令進行觀察。示例：SHOW

VARIABLES

LIKE

'%query_cache%';任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁2優(yōu)化表結(jié)構(gòu)（1）通過分解表來提高查詢效率表主要包含兩種方式，其中水平分表，根據(jù)某一列或者某幾列將表按行分割到多張表中，達(dá)到減少每張表行數(shù)的目的，比如可以將一張表中的多個字段拆成兩張表，一張是不經(jīng)常更改的，一張是經(jīng)常改的；垂直分表，將表的一些列拆分到多張表中，達(dá)到減少每張表列數(shù)的目的，比如user表可以拆分為user0,、user1、user2、user3、user4等水平分表垂直分表任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁（2）通過中間表來提高查詢效率對于需要經(jīng)常聯(lián)合查詢的數(shù)據(jù)表，可以建立一張中間數(shù)據(jù)表以提高查詢效率。通過建立中間數(shù)據(jù)表，把需要經(jīng)常查詢的數(shù)據(jù)插入中間數(shù)據(jù)表中，然后將原來的聯(lián)合查詢改為對中間數(shù)據(jù)表的查詢，可以提高查詢效率。中間表任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁在myStudent數(shù)據(jù)庫，假設(shè)要經(jīng)常查詢學(xué)生姓名、課程名和成績情況。由于這些信息分別來自students、course和score這3張表，因此必須進行連接查詢?，F(xiàn)將這些字段添加至一張中間表student_INFO中。示例：CREATETABLEstudent_INFO(S_NOVARCHAR(6)NOTNULL,S_NAMEVARCHAR(6)NOTNULL,C_NAMEVARCHAR(9)NOTNULL,SCOREFLOAT(6,2)NOTNULL);創(chuàng)建表之后在表中，插入學(xué)生姓名、課程名和成績等信息INSERTINTOstudent_INFOSELECTstudents.S_NO,students.S_NAME,course.C_NAME,score.reportFROMstudents,course,scoreWHEREstudents.S_NO=score.S_NOANDcourse.C_NO=score.C_NO;任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁通過查看student_INFO看到信息如圖7-23所示。之后查詢相關(guān)信息就可以從中間表進行查詢統(tǒng)計，不需要進行多表的連接，提高查詢效率。任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁（3）通過保留冗余字段提高查詢效率設(shè)計數(shù)據(jù)表時應(yīng)盡量遵循范式理論的基本規(guī)約，盡可能減少冗余字段，讓數(shù)據(jù)庫中的數(shù)據(jù)表結(jié)構(gòu)精致、優(yōu)雅。但是，合理地加入冗余字段可以提高查詢速度。冗余字段任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁3優(yōu)化查詢（1）分析查詢語句語法格式：Explain[Extended]Select<Select語句的查詢選項>;①使用Explain語句分析查詢語句任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁使用Explain語句分析查詢students數(shù)據(jù)表的語句，了解該查詢語句的執(zhí)行情況，并對分析結(jié)果中各個字段的含義與作用進行解讀。示例：ExplainSelect*Fromstudents;任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁語法格式：Describe|DescSelect<Select語句的查詢選項>;②使用Describe語句分析查詢語句任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁使用Describe語句分析查詢students數(shù)據(jù)表的語句，了解該查詢語句的執(zhí)行情況，并對分析結(jié)果中各個字段的含義與作用進行解讀。示例：DescSelect*Fromstudents;任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁字段名稱

功能說明idSelect語句的ID（查詢序列號）select_typeSelect語句的類型，其主要取值如表10-10所示table查詢的數(shù)據(jù)表名稱partitions分區(qū)type數(shù)據(jù)表的連接類型，從最佳類型到最差類型的連接類型分別為system、const、eq_reg、ref、ref_or_Null、index_merge、unique_subquery、index_subquery、range、index和ALL，一般來說，得保證查詢至少達(dá)到range級別，最好能達(dá)到ref級別。其中ALL表示對數(shù)據(jù)表的任意記錄組合，進行完整的數(shù)據(jù)表掃描possible_keys可供選擇使用的索引，如果該列的值為Null，則表示沒有相關(guān)的索引key查詢實際使用的索引，如果該列的值為Null，則表示沒有使用索引?？梢栽赟elect語句中使用Force

Index（index_name）來強制使用possible_keys字段的索引或者用Ignore

Index（index_name）來強制忽略possible_keys字段的索引key_len實際使用索引的長度（按字節(jié)計算），在不損失準(zhǔn)確性的情況下，長度越短越好，如果鍵是Null，則長度為Nullref顯示索引的哪一個字段或常數(shù)被使用了rows查詢時檢查的行數(shù)filtered依據(jù)數(shù)據(jù)表查詢條件過濾記錄所占的比例Extra查詢時的其他信息，常用的信息及含義如下。Using

index

condition：只用到索引，可以避免訪問數(shù)據(jù)表，性能很好。Using

Where：用到Where來過濾數(shù)據(jù)。Using

temporary：用到臨時表去處理當(dāng)前的查詢。Using

filesort：用到額外的排序，此時MySQL會根據(jù)連接類型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字按順序檢索行。Range

checked

for

each

record(index

map:N)：沒有好的索引可以使用。Using

index

for

group-by：表明可以在索引中找到分組所需的所有數(shù)據(jù)，不需要查詢實際的表分析結(jié)果中各個字段的含義與功用任務(wù)4-3：嵌套查詢?nèi)Q含義Simple簡單查詢，沒有使用連接查詢（Union）或者子查詢Primary最外層的查詢語句或者主查詢Union在一個連接查詢（Union）中的第2個或后面的Select語句Dependent

Union在一個連接查詢（Union）中的第2個或后面的Select語句，并且依賴于外層查詢Union

Result連接查詢（Union）的結(jié)果Subquery子查詢中的第1個Select語句Dependent

Subquery子查詢中的第1個Select語句，并且依賴于外層查詢Derived派生表（Derived

Table）Materialized實例化子查詢（Materialized

Subquery）Uncacheable

Subquery不能緩存結(jié)果的子查詢，并且必須為外部查詢的每一行重新計算結(jié)果Uncacheable

Union在一個uncacheable

Subquery的Union語句中的第2個或后面的Select語句分析結(jié)果中select_type的主要取值任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁（2）使用索引優(yōu)化查詢語法格式：explainselect*fromstudentswheresex='男'\G使用Like關(guān)鍵字進行查詢的查詢語句中，如果匹配字符串的第1個字符為“%”，索引就不會起作用。只有“%”不在第1個位置，索引才會起作用MySQL中可以使用多個字段創(chuàng)建索引，一個索引可以包括16個字段查詢語句的查詢條件中有Or關(guān)鍵字，并且當(dāng)Or關(guān)鍵字前后的兩個條件中的字段都是索引時，查詢中才使用索引。否則，查詢將不使用索引。任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁對性別增加索引。示例：ALTER

TABLE

student

ADD

INDEX

(SEX);查看Explain語句的運行結(jié)果。explainselect*fromstudentswheresex='男'\G;任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁（3）優(yōu)化子查詢在MySQL中，可以使用連接（Join）查詢來替代子查詢。連接查詢不需要建立臨時表，其速度比子查詢要快，如果在查詢中使用索引的話，性能會更好。連接查詢之所以效率更高一些，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成查詢工作。任務(wù)7-3：數(shù)據(jù)庫性能優(yōu)化叁查找不及格的學(xué)生姓名，先運行子查詢，從score表中找出不及格的學(xué)生學(xué)號S_NO，創(chuàng)建一張臨時表，再將子查詢的結(jié)果傳遞給主查詢，執(zhí)行主查詢。示例：FROMstudentsWHERES_NOIN(SELECTS_NOFROMscoreWHEREscore<60);如果把上面的SQL語句改為使用JOIN連接，由于S_NO字段創(chuàng)建了索引，性能會更好。SELE