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

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

2Part

3Part

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

1Part

2Part

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

1Part

2Part

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

1Part

2Part

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

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

2Part

3Part

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

1Part

2Part

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

1Part

2Part

3任務(wù)描述

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

1Part

2Part

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

2Part

3Part

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

1Part

2Part

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

1Part

2Part

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

1Part

2Part

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

值說

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

明Distinct找到了與查詢條件匹配的第一條記錄后，就不再搜索其他記錄Notexists一旦找到了匹配的LEFTJOIN標準的行，就不再搜索其他記錄Rangecheckedforeachrecord沒有找到合適的索引，對于前一張表的每行連接，它會做一個檢驗，用于決定使用哪個索引，并且使用這個索引從表中取得記錄，這個過程不