第13章 數(shù)據(jù)庫(kù)管理及優(yōu)化技術(shù)

數(shù)據(jù)庫(kù)虛擬教研室制作數(shù)據(jù)庫(kù)《數(shù)據(jù)庫(kù)原理及應(yīng)用》目錄01數(shù)據(jù)庫(kù)故障02數(shù)據(jù)庫(kù)恢復(fù)的實(shí)現(xiàn)技術(shù)03數(shù)據(jù)庫(kù)恢復(fù)策略第13章數(shù)據(jù)庫(kù)管理及優(yōu)化技術(shù)數(shù)據(jù)庫(kù)故障盡管數(shù)據(jù)庫(kù)系統(tǒng)中采取了多種保護(hù)措施來(lái)防止數(shù)據(jù)庫(kù)的安全性和完整性被破壞，保障數(shù)據(jù)庫(kù)系統(tǒng)的正常運(yùn)行，但是故障仍不可避免，影響甚至是破壞數(shù)據(jù)庫(kù)，造成數(shù)據(jù)丟失。同時(shí)還存在一些可能因素導(dǎo)致數(shù)據(jù)不一致。從數(shù)據(jù)庫(kù)恢復(fù)的角度，數(shù)據(jù)庫(kù)系統(tǒng)運(yùn)行過(guò)程中常見(jiàn)的故障可以分為：事務(wù)故障、系統(tǒng)故障、介質(zhì)故障、計(jì)算機(jī)病毒。數(shù)據(jù)庫(kù)故障事務(wù)故障：意味著事務(wù)沒(méi)有達(dá)到預(yù)期的終點(diǎn)(COMMIT或者ROLLBACK)，因此數(shù)據(jù)庫(kù)可能處于不正確狀態(tài)。系統(tǒng)故障：系統(tǒng)故障又稱(chēng)為軟故障。系統(tǒng)故障是指造成系統(tǒng)停止運(yùn)轉(zhuǎn)的任何事情，使得系統(tǒng)要重新啟動(dòng)。介質(zhì)故障：使存儲(chǔ)在外存上的數(shù)據(jù)部分損失或全部損失，稱(chēng)為介質(zhì)故障。介質(zhì)故障又稱(chēng)為硬故障。計(jì)算機(jī)病毒：具有破壞性的、可以自我復(fù)制的計(jì)算機(jī)程序，會(huì)破壞計(jì)算機(jī)的軟件和硬件系統(tǒng)。數(shù)據(jù)庫(kù)故障數(shù)據(jù)庫(kù)已被破壞：根本無(wú)法從數(shù)據(jù)庫(kù)中讀取數(shù)據(jù)，或者數(shù)據(jù)庫(kù)中大部分?jǐn)?shù)據(jù)都有錯(cuò)誤。此時(shí)原有的數(shù)據(jù)庫(kù)已不能使用。某些數(shù)據(jù)可能不正確：這是因?yàn)槭聞?wù)的運(yùn)行被非正常終止造成的。此時(shí)原有的數(shù)據(jù)庫(kù)還能使用，但須改正錯(cuò)誤數(shù)據(jù)。各類(lèi)故障對(duì)數(shù)據(jù)庫(kù)的影響有兩種可能性：數(shù)據(jù)庫(kù)恢復(fù)數(shù)據(jù)庫(kù)恢復(fù)是指當(dāng)數(shù)據(jù)庫(kù)或數(shù)據(jù)遭到破壞時(shí)，通過(guò)技術(shù)手段快速準(zhǔn)確地進(jìn)行恢復(fù)的過(guò)程。數(shù)據(jù)庫(kù)恢復(fù)技術(shù)的核心是建立冗余數(shù)據(jù)。建立冗余數(shù)據(jù)的方法有數(shù)據(jù)備份和登記日志文件。數(shù)據(jù)備份是指定期將數(shù)據(jù)庫(kù)復(fù)制到另一個(gè)磁盤(pán)或其他存儲(chǔ)介質(zhì)上保存起來(lái)的過(guò)程。備用的數(shù)據(jù)稱(chēng)為后備副本。當(dāng)數(shù)據(jù)庫(kù)遭到破壞后，可以將后備副本重新載入。按備份的內(nèi)容可將備份分為：物理備份和邏輯備份。物理備份：直接復(fù)制數(shù)據(jù)庫(kù)文件進(jìn)行備份。與邏輯備份相比，其速度快，但占用的存儲(chǔ)空間比較大。邏輯備份：指利用工具從數(shù)據(jù)庫(kù)中導(dǎo)出數(shù)據(jù)并寫(xiě)入一個(gè)文件。該文件格式與原數(shù)據(jù)庫(kù)文件格式不同，通常備份的是SQL命令(即DDL和INSERT命令)?；謴?fù)時(shí)，執(zhí)行備份文件中的SQL命令實(shí)現(xiàn)數(shù)據(jù)庫(kù)的重現(xiàn)。因此，邏輯備份支持跨平臺(tái)備份。數(shù)據(jù)備份1.使用mysqldump命令數(shù)據(jù)備份MySQL數(shù)據(jù)庫(kù)使用mysqldump命令進(jìn)行備份，屬于邏輯備份?；菊Z(yǔ)法格式如下：mysqldump–uusername-hhost–ppassworddatabasename[tablename…]>filename.sql數(shù)據(jù)備份（1）備份數(shù)據(jù)表【例1】將數(shù)據(jù)庫(kù)teaching_manage中的所有表備份到d:\bk_all.sql。mysqldump-uroot-pteaching_manage>d:\backup\bk_all.sql【例2】將數(shù)據(jù)庫(kù)teaching_manage中的student表備份到d:\bk_student.sql。mysqldump-uroot-pteaching_managestudent>d:\backup\bk_student.sql數(shù)據(jù)備份（2）備份數(shù)據(jù)庫(kù)【例3】將數(shù)據(jù)庫(kù)teaching_manage被備份到d:\bk_teaching_manage.sqlmysqldump-uroot-p--databasesteaching_manage>d:\backup\bk_teaching_manage.sql【例4】備份所有數(shù)據(jù)庫(kù)到d:\backup_alldb.sql。mysqldump-uroot-p--all_databases>d:\backup_alldb.sql數(shù)據(jù)備份2.使用客戶(hù)端工具備份數(shù)據(jù)庫(kù)【例5】在SQLyog主界面上，找到需要備份的數(shù)據(jù)庫(kù)，右擊該數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇【備份導(dǎo)出】→【備份數(shù)據(jù)庫(kù)，備份到SQL】數(shù)據(jù)備份數(shù)據(jù)庫(kù)恢復(fù)就是讓數(shù)據(jù)庫(kù)根據(jù)備份的數(shù)據(jù)恢復(fù)到備份時(shí)的狀態(tài)，也稱(chēng)為數(shù)據(jù)庫(kù)還原。當(dāng)數(shù)據(jù)庫(kù)丟失或意外遭到破壞時(shí)，用戶(hù)可以通過(guò)數(shù)據(jù)庫(kù)還原功能將數(shù)據(jù)庫(kù)還原，盡量減少數(shù)據(jù)丟失或意外破壞造成的損失。數(shù)據(jù)恢復(fù)1.使用mysql命令數(shù)據(jù)還原使用mysql命令可以直接執(zhí)行備份文件中的SQL語(yǔ)句，使數(shù)據(jù)庫(kù)還原。數(shù)據(jù)庫(kù)還原的語(yǔ)法格式如下：mysql–uusername–p[databasename]<filename.sql【例6】使用myslq命令和備份文件d:\bk_student.sql還原數(shù)據(jù)庫(kù)。mysql-uroot-pteaching_manage<d:\backup\bk_student.sql2.使用客戶(hù)端工具恢復(fù)數(shù)據(jù)庫(kù)SQLyog工具支持.sql文件的導(dǎo)入，導(dǎo)入的文件中如果包含創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句，則需要先將表中存在的數(shù)據(jù)庫(kù)刪除，然后再執(zhí)行導(dǎo)入操作。數(shù)據(jù)恢復(fù)日志文件在實(shí)際操作中，用戶(hù)和管理員不可能隨時(shí)備份數(shù)據(jù)，但當(dāng)數(shù)據(jù)丟失時(shí)，或者數(shù)據(jù)庫(kù)目錄中的文件損壞時(shí)，只能恢復(fù)已經(jīng)備份的文件，而在這之后更新的數(shù)據(jù)就沒(méi)法恢復(fù)了。為了解決這個(gè)問(wèn)題，就必須使用日志文件。日志文件是用來(lái)記錄數(shù)據(jù)庫(kù)的運(yùn)行情況、用戶(hù)操作和錯(cuò)誤信息等。通過(guò)分析這些日志文件，可以了解數(shù)據(jù)庫(kù)的運(yùn)行情況、日常操作、錯(cuò)誤信息和需要進(jìn)行優(yōu)化的內(nèi)容。日志文件記錄事務(wù)對(duì)數(shù)據(jù)庫(kù)的更新操作，以記錄或數(shù)據(jù)塊為單位。為了保證數(shù)據(jù)庫(kù)的可恢復(fù)性，撰寫(xiě)日志文件應(yīng)遵循兩條原則：（1）撰寫(xiě)的次序嚴(yán)格按照并發(fā)事務(wù)執(zhí)行的時(shí)間次序；（2）應(yīng)先寫(xiě)日志文件后寫(xiě)數(shù)據(jù)庫(kù)。日志文件常用的日志文件有：錯(cuò)誤日志、二進(jìn)制日志、通用查詢(xún)?nèi)罩?、慢查?xún)?nèi)罩镜热罩疚募﨧ySQL8配置文件my.ini中默認(rèn)的日志設(shè)置如下。日志文件主機(jī)名二進(jìn)制日志文件當(dāng)數(shù)據(jù)庫(kù)遭到破壞后可以將后備副本重新裝入，但重裝后備副本只能將數(shù)據(jù)庫(kù)恢復(fù)到備份時(shí)的狀態(tài)，要想恢復(fù)到故障發(fā)生時(shí)的狀態(tài)，必須借助二進(jìn)制日志文件重新運(yùn)行自轉(zhuǎn)儲(chǔ)以后的所有更新事務(wù)。備份運(yùn)行事務(wù)故障時(shí)刻重裝后備副本利用日志重新運(yùn)行事務(wù)正常運(yùn)行TaTbTc恢復(fù)--查看二進(jìn)制日志文件是否開(kāi)啟二進(jìn)制日志文件SHOWVARIABLESLIKE'log_bin%';--查看所有binlog日志列表SHOWBINARYLOGS;--刷新log日志，自此刻開(kāi)始產(chǎn)生一個(gè)新編號(hào)的binlog日志文件FLUSHLOGS;--刪除所有binlog日志，重新建立二進(jìn)制日志文件擴(kuò)展名的編號(hào)從000001開(kāi)始。RESETMASTER;--查看binlog日志內(nèi)容SHOWBINLOGEVENTSIN'WIN102106231722-bin.000003';--查看最后一條日志狀態(tài)SHOWMASTERSTATUS;二進(jìn)制日志文件【例】刪除teaching_manage中的score表，使用mysqldump完全備份teaching_manage,并刷新日志；新建score表；刷新日志；在score表是插入全部記錄；誤刪除數(shù)據(jù)庫(kù)teaching_manage；要求利用備份文件和二進(jìn)制日志文件恢復(fù)數(shù)據(jù)庫(kù)中的數(shù)據(jù)。(1)刪除teaching_manage中的score表，使用mysqldump完全備份teaching_managemysqldump-uroot-p--flush-logs--databasesteaching_manage>d:\backup\bk_teaching_manage.sql(2)新建score表；刷新日志；CREATETABLEscore;FLUSHLOGS;1.備份數(shù)據(jù)過(guò)程二進(jìn)制日志文件(3)在score表是插入全部記錄；INSERTINTOscoreVALUES(NULL,'001101','101',80);INSERTINTOscoreVALUES(NULL,'001101','102',78);(4)誤刪除數(shù)據(jù)庫(kù)teaching_manage；DROPDATABASEteaching_manage;二進(jìn)制日志文件2.恢復(fù)數(shù)據(jù)過(guò)程(2)利用備份文件bk_teaching_manage.sql恢復(fù)數(shù)據(jù)庫(kù)；mysql-uroot-p<d:\backup\bk_teaching_manage.sql

(3)利用二進(jìn)制日志文件‘WIN102106231722-bin.000005’恢復(fù)score表結(jié)構(gòu)；mysqlbinlogWIN102106231722-bin.000005|mysql-uroot-prootteaching_manage(4)利用二進(jìn)制日志文件‘WIN102106231722-bin.000006’恢復(fù)score表中的數(shù)據(jù)；mysqlbinlog--stop-position=13517WIN102106231722-bin.000003|mysql-uroot-pteaching_manage(1)刷新日志，將恢復(fù)過(guò)程寫(xiě)入新的日志中FLUSHLOGS;主從復(fù)制數(shù)據(jù)庫(kù)鏡像技術(shù)常常用于要求高可靠性的數(shù)據(jù)庫(kù)系統(tǒng)。數(shù)據(jù)庫(kù)以雙副本的形式存放在二個(gè)獨(dú)立的磁盤(pán)系統(tǒng)中，每個(gè)磁盤(pán)系統(tǒng)有各自控制器和CPU，且可以互相自動(dòng)切換。DBMS自動(dòng)利用鏡像磁盤(pán)數(shù)據(jù)進(jìn)行數(shù)據(jù)庫(kù)的恢復(fù)。主從復(fù)制使用數(shù)據(jù)庫(kù)鏡像技術(shù)建立一個(gè)和主數(shù)據(jù)庫(kù)master完全一樣的數(shù)據(jù)庫(kù)環(huán)境，稱(chēng)為從數(shù)據(jù)庫(kù)slave；在復(fù)制過(guò)程中，一個(gè)服務(wù)器充當(dāng)主服務(wù)器，而另外一臺(tái)服務(wù)器充當(dāng)從服務(wù)器。主從復(fù)制的基礎(chǔ)是bin-log二進(jìn)制日志文件。一臺(tái)MySQL數(shù)據(jù)庫(kù)作為master，數(shù)據(jù)庫(kù)中所有更新操作都會(huì)記錄在二進(jìn)制日志中；slave數(shù)據(jù)庫(kù)通過(guò)一個(gè)I/O線程與主服務(wù)器保持通信，如果發(fā)現(xiàn)master二進(jìn)制日志文件發(fā)生變化，則會(huì)把變化復(fù)制到自己的中繼日志中；slave的SQL線程會(huì)把相關(guān)的“更新”寫(xiě)到自己的數(shù)據(jù)庫(kù)中，以此實(shí)現(xiàn)從數(shù)據(jù)庫(kù)和主數(shù)據(jù)庫(kù)的一致性。主從復(fù)制原理高可用性：作為后備數(shù)據(jù)庫(kù)，主數(shù)據(jù)庫(kù)master故障后，可切換到從數(shù)據(jù)庫(kù)slave繼續(xù)工作，避免數(shù)據(jù)丟失。提高數(shù)據(jù)庫(kù)的吞吐量：業(yè)務(wù)量越來(lái)越大，I/O訪問(wèn)頻率過(guò)高，單機(jī)無(wú)法滿(mǎn)足，此時(shí)做多庫(kù)的存儲(chǔ)，降低磁盤(pán)I/O訪問(wèn)的頻率，提高單個(gè)機(jī)器的I/O性能。讀寫(xiě)分離：使數(shù)據(jù)庫(kù)能支撐更大的并發(fā)。在報(bào)表中尤其重要。由于部分報(bào)表sql語(yǔ)句非常的慢，導(dǎo)致鎖表，影響前臺(tái)服務(wù)。如果前臺(tái)使用master，報(bào)表使用slave，那么報(bào)表sql將不會(huì)造成前臺(tái)鎖，保證了前臺(tái)速度。實(shí)現(xiàn)服務(wù)器負(fù)載均衡：通過(guò)服務(wù)器復(fù)制功能，可以在主服務(wù)器和從服務(wù)器之間實(shí)現(xiàn)負(fù)載均衡。即可以通過(guò)在主服務(wù)器和從服務(wù)器之間切分處理客戶(hù)查詢(xún)的負(fù)荷，從而得到更好地客戶(hù)相應(yīng)時(shí)間。主從復(fù)制的特點(diǎn)數(shù)據(jù)遷移數(shù)據(jù)遷移是把數(shù)據(jù)從一個(gè)系統(tǒng)移動(dòng)到另一個(gè)系統(tǒng)上。數(shù)據(jù)遷移具有以下原因：數(shù)據(jù)庫(kù)版本更新：把低版本的數(shù)據(jù)導(dǎo)入到高版本的系統(tǒng)中；數(shù)據(jù)庫(kù)工具變更：把數(shù)據(jù)從一個(gè)工具導(dǎo)入到另一個(gè)工具中；數(shù)據(jù)遷移方式：數(shù)據(jù)導(dǎo)出導(dǎo)入：此方式會(huì)導(dǎo)出一個(gè)中間文件（如備份文件），遷移數(shù)據(jù)時(shí)導(dǎo)入到另外的數(shù)據(jù)庫(kù)中。直接傳輸：在原數(shù)據(jù)庫(kù)服務(wù)與目標(biāo)數(shù)據(jù)庫(kù)服務(wù)均開(kāi)啟的情況下，直接進(jìn)行數(shù)據(jù)的傳輸。數(shù)據(jù)遷移數(shù)據(jù)庫(kù)管理系統(tǒng)發(fā)生了變更，此時(shí)應(yīng)該采用直接傳輸?shù)姆绞桨褦?shù)據(jù)從一個(gè)系統(tǒng)移動(dòng)到另一個(gè)系統(tǒng)上。數(shù)據(jù)遷移具有步驟如下：定義數(shù)據(jù)源（工具C:\Windows\System32\odbcad32.exe）；在客戶(hù)端連接上選擇“導(dǎo)入外部數(shù)據(jù)”；選擇導(dǎo)入的位置；執(zhí)行數(shù)據(jù)導(dǎo)入。數(shù)據(jù)遷移【例】將Oracle數(shù)據(jù)庫(kù)中的xs表導(dǎo)入到MySQL中，存放在teaching_manage數(shù)據(jù)庫(kù)中，對(duì)比導(dǎo)入前后表結(jié)構(gòu)的變化。（1）使用odbcad32定義系統(tǒng)數(shù)據(jù)源xs_data;（2）在SQLYog中使用root用戶(hù)連接MySQL服務(wù);（3）的root連接上點(diǎn)擊右鍵選擇“導(dǎo)入外部數(shù)據(jù)”;（4）選擇導(dǎo)入位置：teaching_manage;（5）執(zhí)行導(dǎo)入操作（6）對(duì)比導(dǎo)入前后表結(jié)構(gòu)的變化分庫(kù)分表分庫(kù)：將一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)按照某種規(guī)則分拆到多個(gè)數(shù)據(jù)庫(kù)中，以緩解單服務(wù)器的壓力（CPU、內(nèi)存、磁盤(pán)、IO）。分表：將一個(gè)表中的數(shù)據(jù)按照某種規(guī)則分拆到多張表中，降低鎖粒度以及索引樹(shù)，提升數(shù)據(jù)查詢(xún)效率。分庫(kù)分表垂直拆庫(kù)：垂直拆庫(kù)則在垂直拆表的基礎(chǔ)上，將一個(gè)系統(tǒng)中的不同業(yè)務(wù)場(chǎng)景進(jìn)行拆分，比如訂單表、用戶(hù)表、商品表。

好處：降低單數(shù)據(jù)庫(kù)服務(wù)的壓力（物理存儲(chǔ)、內(nèi)存、IO等）、降低單機(jī)故障的影響面。垂直拆表：即大表拆小表，將一張表中數(shù)據(jù)不同“字段”分拆到多張表中，比如商品庫(kù)將商品基本信息、商品庫(kù)存、賣(mài)家信息等分拆到不同庫(kù)表中。

考慮因素有將不常用的，數(shù)據(jù)較大，長(zhǎng)度較長(zhǎng)（比如text類(lèi)型字段）的拆分到“擴(kuò)展表”，表和表之間通過(guò)“主鍵外鍵”進(jìn)行關(guān)聯(lián)。

好處：降低表數(shù)據(jù)規(guī)模，提升查詢(xún)效率，也避免查詢(xún)時(shí)數(shù)據(jù)量太大造成的“跨頁(yè)”問(wèn)題。分庫(kù)分表水平拆表將數(shù)據(jù)按照某種維度拆分為多張表，但是由于多張表還是從屬于一個(gè)庫(kù)，其降低鎖粒度，一定程度提升查詢(xún)性能，但是仍然會(huì)有IO性能瓶頸。

水平拆庫(kù)

將數(shù)據(jù)按照某種維度分拆到多個(gè)庫(kù)中，降低單機(jī)單庫(kù)的壓力，提升讀寫(xiě)性能。分庫(kù)分表分庫(kù)分表帶來(lái)的問(wèn)題：事務(wù)性問(wèn)題：分庫(kù)可能導(dǎo)致執(zhí)行一次事務(wù)所需的數(shù)據(jù)分布在不同服務(wù)器上，數(shù)據(jù)庫(kù)層面無(wú)法實(shí)現(xiàn)事務(wù)性操作，需要更上層業(yè)務(wù)引入分布式事務(wù)操作。主鍵(自增ID)唯一性問(wèn)題：在數(shù)據(jù)庫(kù)表設(shè)計(jì)時(shí)，經(jīng)常會(huì)使用自增ID作為數(shù)據(jù)主鍵，這就導(dǎo)致后續(xù)在遷庫(kù)遷表、或者分庫(kù)分表操作時(shí)，會(huì)因?yàn)橹麈I的變化或者主鍵不唯一產(chǎn)生沖突，要解決主鍵不唯一問(wèn)題。跨庫(kù)多表join問(wèn)題：join操作往往會(huì)給