[數(shù)據(jù)庫(kù)][華為云數(shù)據(jù)庫(kù)全棧成長(zhǎng)計(jì)劃][學(xué)習(xí)筆記](méi)[02_數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)]

1，數(shù)據(jù)庫(kù)管理簡(jiǎn)介

數(shù)據(jù)庫(kù)管理及其工作范圍

數(shù)據(jù)庫(kù)管理（Database Admin）

數(shù)據(jù)庫(kù)管理工作就是對(duì)數(shù)據(jù)庫(kù)管理系統(tǒng)進(jìn)行管理和維護(hù)的工作。

核心目標(biāo)，保證數(shù)據(jù)庫(kù)管理系統(tǒng)的：穩(wěn)定性，安全性，數(shù)據(jù)一致性，系統(tǒng)的高性能。

數(shù)據(jù)庫(kù)管理員（Database Administrator）：從事管理和維護(hù)數(shù)據(jù)庫(kù)管理系統(tǒng)的相關(guān)人員的統(tǒng)稱。

數(shù)據(jù)庫(kù)管理工作范圍（1）

數(shù)據(jù)庫(kù)對(duì)象管理

物理設(shè)計(jì)工作；

物理實(shí)現(xiàn)工作。

數(shù)據(jù)庫(kù)安全管理

防止未授權(quán)訪問(wèn)，避免受保護(hù)的信息泄露；

防止安全漏洞和不當(dāng)?shù)臄?shù)據(jù)修改；

確保數(shù)據(jù)只提供給授權(quán)用戶使用。

備份恢復(fù)管理

制定合理的備份策略，實(shí)現(xiàn)數(shù)據(jù)定期備份功能；

保證災(zāi)難發(fā)生時(shí)數(shù)據(jù)庫(kù)系統(tǒng)能夠做到最快恢復(fù)和最小損失。

數(shù)據(jù)庫(kù)管理工作范圍（2）

數(shù)據(jù)庫(kù)性能管理

對(duì)影響數(shù)據(jù)庫(kù)性能的因素進(jìn)行監(jiān)控和優(yōu)化。

對(duì)數(shù)據(jù)庫(kù)能使用的資源進(jìn)行優(yōu)化，從而增加系統(tǒng)吞吐量，并減少競(jìng)爭(zhēng)，最大可能地處理工作負(fù)載。

數(shù)據(jù)庫(kù)環(huán)境管理

數(shù)據(jù)庫(kù)的運(yùn)行和維護(hù)管理，包括安裝，配置，升級(jí)，遷移等。

確保數(shù)據(jù)庫(kù)系統(tǒng)在內(nèi)的IT基礎(chǔ)設(shè)施的正常運(yùn)作。

對(duì)象管理

數(shù)據(jù)庫(kù)對(duì)象

什么是數(shù)據(jù)庫(kù)對(duì)象？

數(shù)據(jù)庫(kù)里用來(lái)存儲(chǔ)和指向數(shù)據(jù)的各種概念和結(jié)構(gòu)的總稱。

對(duì)象管理就是使用對(duì)象定義語(yǔ)言或者工具創(chuàng)建，修改或刪除各種數(shù)據(jù)庫(kù)對(duì)象的管理過(guò)程。

常見(jiàn)的基本數(shù)據(jù)庫(kù)對(duì)象：

制定數(shù)據(jù)庫(kù)對(duì)象命名規(guī)范

良好的設(shè)計(jì)是良好的開(kāi)端

數(shù)據(jù)庫(kù)產(chǎn)品本身沒(méi)有嚴(yán)格的限制。

隨意的對(duì)象命名會(huì)導(dǎo)致系統(tǒng)的不可控，不可維護(hù)。

命名規(guī)范的幾點(diǎn)建議

統(tǒng)一名稱的大小寫(xiě)；

利用前綴標(biāo)識(shí)對(duì)象類(lèi)型，如表名前綴t_，視圖前綴v_，函數(shù)前綴f_等；

命名盡量采用富有意義、易于記憶、描述性強(qiáng)、簡(jiǎn)短及具有唯一性的英文詞匯，不建議使用漢語(yǔ)拼音；

以項(xiàng)目為單位，采用名稱詞典，指定一些公共的縮略詞，如amt代表amount（數(shù)量）。

備份恢復(fù)管理

備份和恢復(fù)的基本概念

數(shù)據(jù)庫(kù)備份

備份數(shù)據(jù)庫(kù)就是將數(shù)據(jù)庫(kù)中的數(shù)據(jù)，以及保證數(shù)據(jù)庫(kù)系統(tǒng)正常運(yùn)行的有關(guān)信息保存起來(lái)，以備系統(tǒng)出現(xiàn)故障后恢復(fù)數(shù)據(jù)庫(kù)時(shí)使用。

備份對(duì)象，包括但不限于：

數(shù)據(jù)本身；

和數(shù)據(jù)相關(guān)的數(shù)據(jù)庫(kù)對(duì)象；

用戶及權(quán)限；

數(shù)據(jù)庫(kù)環(huán)境，如配置文件，定時(shí)任務(wù)等。

數(shù)據(jù)庫(kù)恢復(fù)

將數(shù)據(jù)庫(kù)系統(tǒng)從故障或者癱瘓狀態(tài)恢復(fù)到可正常運(yùn)行，并能夠?qū)?shù)據(jù)恢復(fù)到可接受狀態(tài)的活動(dòng)。

災(zāi)難恢復(fù)

企業(yè)級(jí)容災(zāi)

對(duì)于企業(yè)和單位來(lái)說(shuō)，數(shù)據(jù)庫(kù)系統(tǒng)和其他應(yīng)用系統(tǒng)構(gòu)成更大的信息系統(tǒng)平臺(tái)，所以數(shù)據(jù)庫(kù)備份恢復(fù)并不是孤立的功能點(diǎn)，要和其他應(yīng)用系統(tǒng)一并考慮整個(gè)信息信息系統(tǒng)平臺(tái)的容災(zāi)性能。

災(zāi)難備份

為了災(zāi)難恢復(fù)而對(duì)數(shù)據(jù)、數(shù)據(jù)處理系統(tǒng)、網(wǎng)絡(luò)系統(tǒng)、基礎(chǔ)設(shè)施、專業(yè)技術(shù)能力和運(yùn)行管理能力進(jìn)行備份的過(guò)程。

恢復(fù)時(shí)間目標(biāo)（RTO）

災(zāi)難發(fā)生后，信息系統(tǒng)或業(yè)務(wù)功能從停頓到必須恢復(fù)的時(shí)間要求。

恢復(fù)點(diǎn)目標(biāo)（RPO）

災(zāi)難發(fā)生后，系統(tǒng)和數(shù)據(jù)必須恢復(fù)到的時(shí)間點(diǎn)要求。

災(zāi)難恢復(fù)等級(jí)

災(zāi)難恢復(fù)等級(jí)劃分參考國(guó)家標(biāo)準(zhǔn)規(guī)范：《GT/T 20988-2007：信息系統(tǒng)災(zāi)難恢復(fù)規(guī)范》

某行業(yè)RTO/RPO與災(zāi)難恢復(fù)能力等級(jí)關(guān)系

備份方式

根據(jù)備份的數(shù)據(jù)集合的范圍：

全量備份

差異備份

增量備份

根據(jù)是否停用數(shù)據(jù)庫(kù)

熱備

溫備

冷備

根據(jù)備份內(nèi)容

物理備份

邏輯備份

全量備份

也稱為完全備份

對(duì)某個(gè)指定時(shí)間點(diǎn)的所有數(shù)據(jù)和對(duì)應(yīng)的結(jié)構(gòu)進(jìn)行一個(gè)完全的備份。

特點(diǎn)：

數(shù)據(jù)最完備

安全性最高

備份和恢復(fù)時(shí)間隨著數(shù)據(jù)的體量而明顯增加；

非常重要，是差異備份和增量備份的基礎(chǔ)；

備份期間會(huì)對(duì)系統(tǒng)性能產(chǎn)生一定影響。

差異備份和增量備份

差異備份

差異備份是指上一次全量備份之后，對(duì)發(fā)生變化的數(shù)據(jù)進(jìn)行的備份。

增量備份

增量備份是指上一次備份之后，對(duì)發(fā)生變化的數(shù)據(jù)進(jìn)行的備份。

熱備，溫備和冷備

熱備

在數(shù)據(jù)庫(kù)正常運(yùn)行下進(jìn)行備份。

備份期間，數(shù)據(jù)庫(kù)讀寫(xiě)均可正常進(jìn)行。

溫備

數(shù)據(jù)庫(kù)可用性弱于熱備，備份期間，數(shù)據(jù)庫(kù)只能進(jìn)行讀操作，不能進(jìn)行寫(xiě)操作。

冷備

在備份期間，應(yīng)用的讀寫(xiě)操作不可進(jìn)行。

備份出的數(shù)據(jù)可靠性最高。

物理備份和邏輯備份

物理備份

直接備份數(shù)據(jù)庫(kù)所對(duì)應(yīng)的數(shù)據(jù)文件甚至是整個(gè)磁盤(pán)。

邏輯備份

將數(shù)據(jù)從數(shù)據(jù)庫(kù)中導(dǎo)出，并將導(dǎo)出的數(shù)據(jù)進(jìn)行存檔備份。

安全管理

數(shù)據(jù)庫(kù)系統(tǒng)安全框架

廣義范圍，數(shù)據(jù)庫(kù)安全框架可以分為三個(gè)層次：

網(wǎng)絡(luò)層次安全

從技術(shù)角度講，網(wǎng)絡(luò)系統(tǒng)層次安全方法技術(shù)主要有加密技術(shù)，數(shù)字簽名技術(shù)，防火墻技術(shù)和入侵檢測(cè)技術(shù)等。

操作系統(tǒng)層次安全

核心是要保證服務(wù)器的安全，主要體現(xiàn)在服務(wù)器的用戶賬戶，口令，訪問(wèn)權(quán)限等方面。

數(shù)據(jù)安全主要體現(xiàn)在加密技術(shù)，數(shù)據(jù)存儲(chǔ)的安全性，數(shù)據(jù)傳輸?shù)陌踩缘确矫妫鏚erberos，IPsec，SSL和VPN等技術(shù)。

數(shù)據(jù)管理系統(tǒng)層次安全

數(shù)據(jù)庫(kù)加密；

數(shù)據(jù)存取訪問(wèn)控制；

安全審計(jì)；

數(shù)據(jù)備份。

安全控制模型

安全控制

在數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的不同層次提供對(duì)有意和無(wú)意損害行為的安全防范，例如：

加密存取數(shù)據(jù) -> 有意非法活動(dòng)

用戶身份驗(yàn)證，限制操作權(quán)限 -> 有意的非法操作

提高系統(tǒng)可靠性和數(shù)據(jù)備份 -> 無(wú)意的損害行為

安全控制模型

身份驗(yàn)證

數(shù)據(jù)庫(kù)用戶的身份驗(yàn)證是DBMS提供的最外層安全保護(hù)措施

阻止未經(jīng)授權(quán)的用戶的訪問(wèn)。

對(duì)于數(shù)據(jù)庫(kù)應(yīng)用目前普遍采用用戶名密碼驗(yàn)證模式，所以有必要增強(qiáng)密碼強(qiáng)度。

采用長(zhǎng)度較長(zhǎng)的字符串，如8-20個(gè)字符；

混合數(shù)字，字母和符號(hào)的密碼；

定期更換密碼；

密碼不能重復(fù)使用。

在開(kāi)發(fā)的代碼或者腳本中，禁止出現(xiàn)數(shù)據(jù)庫(kù)用戶的密碼明文。

訪問(wèn)控制

訪問(wèn)控制是數(shù)據(jù)庫(kù)安全中最有效的方法也是最容易出問(wèn)題的地方。

基本原則

對(duì)于不同用戶根據(jù)敏感數(shù)據(jù)的分類(lèi)要求，給予不同的權(quán)限。

最小權(quán)限原則

檢查關(guān)鍵權(quán)限

檢查關(guān)鍵數(shù)據(jù)庫(kù)對(duì)象的權(quán)限

基于角色的權(quán)限管理

對(duì)于大型數(shù)據(jù)庫(kù)系統(tǒng)或者用戶數(shù)量多的系統(tǒng)，權(quán)限管理主要使用基于角色的訪問(wèn)控制（Role Based Access Control，RBAC）。

開(kāi)啟審計(jì)

審計(jì)可以幫助數(shù)據(jù)庫(kù)管理員發(fā)現(xiàn)現(xiàn)存架構(gòu)和使用中的漏洞。

數(shù)據(jù)庫(kù)審計(jì)的層次

訪問(wèn)及身份驗(yàn)證審計(jì)，數(shù)據(jù)庫(kù)用戶登入（Logon），登出（Logoff）的相關(guān)信息，如登入登出時(shí)間，連接方式及參數(shù)信息，登入途徑等。

用戶與管理員審計(jì)：針對(duì)用戶和管理員執(zhí)行的活動(dòng)進(jìn)行分析和報(bào)告。

安全活動(dòng)監(jiān)控：記錄數(shù)據(jù)庫(kù)中任何未授權(quán)或者可疑的活動(dòng)生成審計(jì)報(bào)告。

漏洞與威脅審計(jì)：發(fā)現(xiàn)數(shù)據(jù)庫(kù)可能存在的漏洞，以及想要利用這些漏洞的"用戶"。

數(shù)據(jù)庫(kù)加密

數(shù)據(jù)庫(kù)加密的不同層次：

DBMS內(nèi)核層

數(shù)據(jù)在物理存取之前完成加/解密工作；

對(duì)于數(shù)據(jù)庫(kù)用戶來(lái)說(shuō)是透明的，沒(méi)有感覺(jué)的；

采用加密存儲(chǔ)，加密運(yùn)算在服務(wù)器端運(yùn)行，在一定程度上會(huì)加重服務(wù)器的負(fù)載。

DBMS外層加密

開(kāi)發(fā)專門(mén)的加解密工具，或者定義加解密方法；

可以控制加密對(duì)象粒度，到表或者字段級(jí)別進(jìn)行加解密；

用戶只需要關(guān)注敏感信息范圍。

性能管理

資源

供給類(lèi)資源

這類(lèi)資源也稱為基礎(chǔ)資源，是計(jì)算機(jī)硬件對(duì)應(yīng)的資源。

操作系統(tǒng)管理的資源。

處理能力：CPU>內(nèi)存>>磁盤(pán)≈網(wǎng)絡(luò)。

并發(fā)性控制資源

這類(lèi)資源包括但不限于：鎖，隊(duì)列，緩存，互斥信號(hào)等。

數(shù)據(jù)庫(kù)系統(tǒng)管理的資源。

性能管理的基本原則

充分利用資源不浪費(fèi)。

技術(shù)指標(biāo)圖表

性能管理的意義

資源的高效使用

數(shù)據(jù)庫(kù)實(shí)際上總是在有限的環(huán)境下運(yùn)行。

對(duì)資源的有效管理確保數(shù)據(jù)庫(kù)系統(tǒng)在高峰時(shí)期能夠滿足用戶對(duì)系統(tǒng)的性能要求。

偵測(cè)系統(tǒng)問(wèn)題

實(shí)時(shí)的系統(tǒng)性能監(jiān)控（通過(guò)數(shù)據(jù)庫(kù)提供的日志或者工具進(jìn)行實(shí)時(shí)監(jiān)控系統(tǒng)性能）。

系統(tǒng)歷史性能數(shù)據(jù)跟蹤（歷史性能數(shù)據(jù)的分析）。

容量規(guī)劃

性能管理所收集到的數(shù)據(jù)是進(jìn)行系統(tǒng)容量規(guī)劃及其他前瞻性規(guī)劃的基礎(chǔ)。

用事實(shí)而不是感覺(jué)說(shuō)話。

性能管理的目標(biāo)

數(shù)據(jù)庫(kù)系統(tǒng)的基本指標(biāo)

吞吐量

響應(yīng)時(shí)間

OLTP

在可接受的響應(yīng)時(shí)間基礎(chǔ)之上提供盡可能高的吞吐量

降低單位資源消耗，快速通過(guò)并發(fā)共享區(qū)域，減少瓶頸制約

OLAP

在有限的資源內(nèi)盡可能地縮短響應(yīng)時(shí)間

一個(gè)事務(wù)應(yīng)該充分利用資源來(lái)加速處理時(shí)間

性能優(yōu)化工作的一些場(chǎng)景

上線優(yōu)化或未達(dá)到性能期望的性能優(yōu)化；

響應(yīng)速度逐漸變慢的系統(tǒng)優(yōu)化；

系統(tǒng)運(yùn)行過(guò)程中突然變慢的系統(tǒng)優(yōu)化（應(yīng)急處理）；

突然變慢，持續(xù)一段時(shí)間以后又恢復(fù)正常；

基于降低資源消耗的系統(tǒng)優(yōu)化；

預(yù)防性的日常巡檢工作。

性能管理需要采集的數(shù)據(jù)

性能管理需要采集的數(shù)據(jù)范圍，包括但不限于：

CPU使用數(shù)據(jù)

空間使用率

使用數(shù)據(jù)庫(kù)系統(tǒng)的用戶和角色

心跳查詢的響應(yīng)時(shí)間

提交到數(shù)據(jù)的SQL為基本單元的性能數(shù)據(jù)

數(shù)據(jù)庫(kù)工具提交的作業(yè)相關(guān)的性能數(shù)據(jù)（如加載，卸載，備份，恢復(fù)等）

關(guān)注的時(shí)間范圍

日常范圍：一周高峰時(shí)段的時(shí)間；月度結(jié)束的時(shí)間；季節(jié)變化數(shù)據(jù)。

一天范圍內(nèi)：用戶集中使用系統(tǒng)的時(shí)間段；系統(tǒng)壓力較高的時(shí)間段等。

建立性能報(bào)表

數(shù)據(jù)庫(kù)系統(tǒng)內(nèi)置很多監(jiān)控報(bào)表

提取性能相關(guān)的數(shù)據(jù)建立定期性能報(bào)表（日?qǐng)?bào)，周報(bào)，月報(bào)）。

建立常見(jiàn)指標(biāo)的性能趨勢(shì)分析報(bào)表，可以對(duì)當(dāng)前系統(tǒng)性能有直觀的展現(xiàn)。

特定趨勢(shì)類(lèi)型的報(bào)表，包括但不限于：

基于異常事件的報(bào)表；

消耗大量資源的SQL或是作業(yè)；

特定用戶、用戶群的資源消耗報(bào)表；

特定應(yīng)用的資源消耗報(bào)表。

思考題

把數(shù)據(jù)庫(kù)中的數(shù)據(jù)遷移到其他異構(gòu)的數(shù)據(jù)庫(kù)中，可以采用（邏輯備份）的方式。

為提升表的查詢速度，可以創(chuàng)建的數(shù)據(jù)庫(kù)對(duì)象是（索引）

某單位指定災(zāi)備標(biāo)準(zhǔn)時(shí)，希望在災(zāi)難發(fā)生后能夠1小時(shí)內(nèi)系統(tǒng)恢復(fù)成對(duì)外可服務(wù)的狀態(tài)，這個(gè)指標(biāo)指的是（RTO）

運(yùn)維管理

數(shù)據(jù)庫(kù)安裝

傳統(tǒng)數(shù)據(jù)庫(kù)安裝（開(kāi)始安裝->獲取安裝包->準(zhǔn)備安裝環(huán)境->規(guī)劃安裝方案->安裝數(shù)據(jù)庫(kù)->安裝集群組件（選裝）->安裝后檢查/參數(shù)調(diào)整->初次啟動(dòng)）

云數(shù)據(jù)庫(kù)安裝（登陸云平臺(tái)->購(gòu)買(mǎi)數(shù)據(jù)庫(kù)實(shí)例->規(guī)劃配置參數(shù)->完成購(gòu)買(mǎi)->登陸）

數(shù)據(jù)庫(kù)卸載

在實(shí)際場(chǎng)景中，多發(fā)生在數(shù)據(jù)庫(kù)的版本升級(jí)之前，需要對(duì)老版本的數(shù)據(jù)庫(kù)進(jìn)行卸載或者清理。

基本步驟

傳統(tǒng)數(shù)據(jù)庫(kù)

（可選）對(duì)數(shù)據(jù)庫(kù)進(jìn)行一次全備。

停止數(shù)據(jù)庫(kù)服務(wù)。

卸載數(shù)據(jù)庫(kù)。

云數(shù)據(jù)庫(kù)

（可選）對(duì)數(shù)據(jù)庫(kù)進(jìn)行一次全備。

云平臺(tái)刪除數(shù)據(jù)庫(kù)實(shí)例。

不同架構(gòu)場(chǎng)景下

單機(jī)，主備或一主多備的卸載方式都是類(lèi)似的，需要在每個(gè)節(jié)點(diǎn)上執(zhí)行相同的卸載操作。

分布式集群卸載一般使用專有的卸載工具。

卸載后

對(duì)于一些客戶，數(shù)據(jù)庫(kù)卸載后需要對(duì)存儲(chǔ)介質(zhì)上的數(shù)據(jù)再進(jìn)行銷(xiāo)毀處理，保證數(shù)據(jù)不外泄。

數(shù)據(jù)庫(kù)遷移

需要依據(jù)不同的遷移場(chǎng)景需求設(shè)計(jì)遷移方案。

考慮的要素

遷移可用的時(shí)間窗口。

遷移可以使用的工具。

遷移過(guò)程中數(shù)據(jù)源系統(tǒng)是否停止寫(xiě)入操作。

遷移過(guò)程的數(shù)據(jù)源系統(tǒng)和目標(biāo)系統(tǒng)之間的網(wǎng)絡(luò)情況如何。

根據(jù)遷移的數(shù)據(jù)量估算備份/恢復(fù)時(shí)間。

遷移后，源和目標(biāo)數(shù)據(jù)庫(kù)系統(tǒng)之間的數(shù)據(jù)一致性稽核。

數(shù)據(jù)庫(kù)擴(kuò)容

任何一個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的容量都是在某個(gè)時(shí)間點(diǎn)的基礎(chǔ)上對(duì)未來(lái)一段時(shí)間內(nèi)的數(shù)據(jù)量進(jìn)行估算后確定的，容量不僅僅是數(shù)據(jù)存儲(chǔ)量，還需要考慮一下幾個(gè)方面：

計(jì)算能力不足（整個(gè)系統(tǒng)CPU日均繁忙程度>90%）；

響應(yīng)/并發(fā)能力不足（QPS，TPS顯著下降，無(wú)法滿足SLA）；

數(shù)據(jù)容量不足（可用的數(shù)據(jù)空間低于15%）。

擴(kuò)容方案的選擇

垂直擴(kuò)容

垂直擴(kuò)容是增加數(shù)據(jù)庫(kù)服務(wù)器硬件，如增加內(nèi)存，增大存儲(chǔ)，提升網(wǎng)絡(luò)帶寬，提升單機(jī)硬件方面性能配置。這種方式相對(duì)簡(jiǎn)單，但是會(huì)遭遇單機(jī)硬件性能瓶頸。

水平擴(kuò)容

橫向增加服務(wù)器數(shù)量，利用集群中服務(wù)器數(shù)量的優(yōu)勢(shì)來(lái)增加整體系統(tǒng)的性能。

停機(jī)擴(kuò)容

簡(jiǎn)單，但是時(shí)間窗口有限，出現(xiàn)問(wèn)題會(huì)導(dǎo)致擴(kuò)容失敗。而且如果時(shí)間過(guò)長(zhǎng)，不易被客戶接受。

平滑擴(kuò)容

對(duì)數(shù)據(jù)庫(kù)服務(wù)無(wú)影響；

技術(shù)方案相對(duì)復(fù)雜，尤其是數(shù)據(jù)庫(kù)服務(wù)器數(shù)量增多，擴(kuò)容復(fù)雜度急劇上升。

例行維護(hù)工作

數(shù)據(jù)庫(kù)故障處理

配置數(shù)據(jù)庫(kù)監(jiān)控指標(biāo)和警告值；

針對(duì)故障事件的等級(jí)設(shè)置警告通知流程；

接受警告信息后，根據(jù)日志進(jìn)行故障定位；

對(duì)于遇到的問(wèn)題，應(yīng)詳細(xì)記錄原始信息；

嚴(yán)格遵守操作規(guī)程和行業(yè)安全規(guī)程；

對(duì)于重大操作，在操作前要確認(rèn)操作可行性，做好相應(yīng)備份、應(yīng)急和安全措施后，由有權(quán)限的操作人員執(zhí)行。

數(shù)據(jù)庫(kù)健康巡檢

查看健康檢查任務(wù)；

管理健康檢查報(bào)告；

修改健康檢查配置。

2，數(shù)據(jù)庫(kù)重要概念

數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)實(shí)例（1）

數(shù)據(jù)庫(kù)（Database）

物理操作系統(tǒng)文件或磁盤(pán)數(shù)據(jù)塊的集合

比如數(shù)據(jù)文件，索引文件，結(jié)構(gòu)文件。

并非所有的數(shù)據(jù)庫(kù)系統(tǒng)都是基于文件的，也有直接把數(shù)據(jù)寫(xiě)入數(shù)據(jù)存儲(chǔ)的形式。

數(shù)據(jù)庫(kù)實(shí)例（Database Instance）

實(shí)例指的就是操作系統(tǒng)中一系列的進(jìn)程以及為這些進(jìn)程所分配的內(nèi)存塊。

數(shù)據(jù)庫(kù)實(shí)例是訪問(wèn)數(shù)據(jù)庫(kù)的通道。

通常來(lái)說(shuō)一個(gè)數(shù)據(jù)庫(kù)實(shí)例對(duì)應(yīng)一個(gè)數(shù)據(jù)庫(kù)。

數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)實(shí)例(2)

多實(shí)例

利用多實(shí)例操作，可以更充分的利用硬件資源，讓服務(wù)器性能最大化。

分布式集群

集群就是一組相互獨(dú)立的服務(wù)器，通過(guò)高速的網(wǎng)絡(luò)組成一個(gè)計(jì)算機(jī)系統(tǒng)。

分布式集群中，每個(gè)服務(wù)器都可能有數(shù)據(jù)庫(kù)的一份完整副本，或者部分副本，所有服務(wù)器通過(guò)網(wǎng)絡(luò)相互連接，共同組成一個(gè)完整的、全局的，邏輯上集中、物理上分布的大型數(shù)據(jù)庫(kù)。

數(shù)據(jù)庫(kù)連接和會(huì)話

數(shù)據(jù)庫(kù)連接（Connection）

物理層面的通信連接，指的是一個(gè)通過(guò)網(wǎng)絡(luò)建立的客戶端和專有服務(wù)器（Dedicated Server）或調(diào)度器（Shared Server）的一個(gè)網(wǎng)絡(luò)連接。

建立連接時(shí)候指定連接參數(shù)，如服務(wù)器主機(jī)名或ip，端口號(hào)，連接用戶名和口令等。

數(shù)據(jù)庫(kù)會(huì)話（Session）

客戶端和數(shù)據(jù)庫(kù)之間通信的邏輯概念。

通信雙方從開(kāi)始通信到結(jié)束通信期間的一個(gè)上下文（Context）。這個(gè)上下文是一段位于服務(wù)器端的內(nèi)存：記錄了本次連接的客戶端機(jī)器、對(duì)應(yīng)的應(yīng)用程序進(jìn)程號(hào)、對(duì)應(yīng)的用戶登錄等信息。

數(shù)據(jù)庫(kù)連接建立流程步驟

客戶端登陸請(qǐng)求

監(jiān)聽(tīng)進(jìn)程處理和響應(yīng)

產(chǎn)生服務(wù)進(jìn)程

進(jìn)程初始化和Session初始化

用戶驗(yàn)證和權(quán)限判斷

Session審計(jì)

響應(yīng)客戶端

連接建立

數(shù)據(jù)庫(kù)連接池

建立數(shù)據(jù)庫(kù)連接是有代價(jià)的

頻繁的建立和關(guān)閉數(shù)據(jù)庫(kù)連接，會(huì)使得對(duì)連接資源的分配和釋放成為數(shù)據(jù)庫(kù)的瓶頸，從而降低數(shù)據(jù)庫(kù)系統(tǒng)的性能。

連接池：數(shù)據(jù)庫(kù)連接的復(fù)用（解決方案）

負(fù)責(zé)分配、管理和釋放數(shù)據(jù)庫(kù)連接，它允許應(yīng)用程序重復(fù)使用一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)連接，而不是再重新建立一個(gè)。

數(shù)據(jù)庫(kù)連接可以得到高效、安全的復(fù)用。

Schema

Schema是數(shù)據(jù)庫(kù)形式語(yǔ)言描述的一種結(jié)構(gòu)，是對(duì)象的集合。

允許多個(gè)用戶使用一個(gè)數(shù)據(jù)庫(kù)而不干擾其他用戶。

把數(shù)據(jù)庫(kù)對(duì)象組織成邏輯組，讓他們更便于管理。

形成命名空間，避免對(duì)象的名字沖突。

Schema包含表及其他數(shù)據(jù)庫(kù)對(duì)象，數(shù)據(jù)類(lèi)型、函數(shù)、操作符等。

表空間（Tablespace）

表空間是由一個(gè)或者多個(gè)數(shù)據(jù)文件組成的。

通過(guò)表空間定義數(shù)據(jù)庫(kù)對(duì)象文件的存放位置。

數(shù)據(jù)庫(kù)中所有對(duì)象在邏輯上都存放在表空間中。

在物理上存儲(chǔ)在表空間所屬的數(shù)據(jù)文件中。

表空間作用

根據(jù)數(shù)據(jù)庫(kù)對(duì)象使用模式安排數(shù)據(jù)物理存放位置，提高性能。

頻繁使用的索引放置在性能穩(wěn)定且運(yùn)算速度快的磁盤(pán)上。

歸檔數(shù)據(jù)，使用頻率低，對(duì)訪問(wèn)性能要求低的表存放在速度慢的磁盤(pán)上。

通過(guò)表空間指定數(shù)據(jù)占用的物理磁盤(pán)空間。

通過(guò)表空間限制物理空間使用上限，避免磁盤(pán)空間被耗盡。

例如：

create tablespace `ts1` add datafile 'ts1.ibd' engine = innodb;

#在表空間中創(chuàng)建表 create table tb1 ( col1 int, col2 varchar(64), col3 datetime ) #指定對(duì)應(yīng)的表空間 tablespace ts1;

表（Table）

在關(guān)系數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)表就是一系列二維數(shù)組的集合

用來(lái)記錄和存儲(chǔ)數(shù)據(jù)對(duì)象之間的關(guān)系。

記錄

表中的每一行稱為一個(gè)記錄，由若干個(gè)字段組成。

字段

也稱為域，表中的每一列稱為一個(gè)字段。

每個(gè)字段都包含兩個(gè)屬性：列名和數(shù)據(jù)類(lèi)型。

create table `t_author` ( `author_id` integer, `author_name` char(60), `author_age` integer, `author_address` varchar(255) )

臨時(shí)表

GaussDB(for MySQL)支持創(chuàng)建臨時(shí)表

臨時(shí)表用來(lái)保存一個(gè)會(huì)話中需要的數(shù)據(jù)。當(dāng)會(huì)話退出的時(shí)候，臨時(shí)表的數(shù)據(jù)自動(dòng)清空。

臨時(shí)表中的數(shù)據(jù)是臨時(shí)的，過(guò)程性的，不需要和普通數(shù)據(jù)表那樣永久保留的。

使用SHOW TABLES命令無(wú)法顯示臨時(shí)表。

create temporary table `staff_history_session` ( `startdate` date, `enddate` date )

表的存儲(chǔ)方式

按照數(shù)據(jù)的存儲(chǔ)方式，表分為兩種（行存儲(chǔ)表，列存儲(chǔ)表）

行存儲(chǔ)（記錄順序從上到下），數(shù)據(jù)存儲(chǔ)序列方向從上往下

寫(xiě)入過(guò)程要把記錄拆開(kāi)，不同的列數(shù)據(jù)分別寫(xiě)入不同的存儲(chǔ)區(qū)域，多次寫(xiě)入過(guò)程會(huì)導(dǎo)致IO次數(shù)增加，效率相對(duì)較慢。

列存儲(chǔ)（記錄順序從上到下），數(shù)據(jù)存儲(chǔ)序列方向從上往下

select name,age from employee; 對(duì)于列存儲(chǔ)表的查詢，只要掃描少量所需要列對(duì)應(yīng)的存儲(chǔ)即可，IO開(kāi)銷(xiāo)比較少。

存儲(chǔ)方式的選擇

列存儲(chǔ)適合的場(chǎng)景

統(tǒng)計(jì)分析類(lèi)查詢（group，join多的場(chǎng)景）；

適合OLAP，數(shù)據(jù)挖掘等大量查詢的應(yīng)用查詢。

行存儲(chǔ)適合的場(chǎng)景

點(diǎn)查詢（返回記錄少，基于索引的簡(jiǎn)單查詢）；

適合OLTP，這種輕量級(jí)事務(wù)，大量寫(xiě)操作，數(shù)據(jù)增刪改比較多的場(chǎng)景。

分區(qū)（Partition）

分區(qū)表是將大表的數(shù)據(jù)分成許多小的數(shù)據(jù)子集，稱為分區(qū)。

范圍分區(qū)表，列表分區(qū)表，哈希分區(qū)表，間隔分區(qū)表

分區(qū)表的收益

改善查詢性能，增強(qiáng)可用性，方便維護(hù)，均衡I/O

create table `tp` ( `id` int, `name` varchar(50), `purchased` date ) partition by range (year(`purchased`)) ( partition p0 values less than (2015), partition p1 values less than (2016), partition p2 values less than (2017), partition p3 values less than (2018), partition p4 values less than (2019), partition p5 values less than (2020), partition p6 values less than (2021) )

分區(qū)剪枝的原理

分區(qū)剪枝

對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索效率。

磁盤(pán)順序從上到下

普通表

分區(qū)表

假設(shè)表中所有10年數(shù)據(jù)，按年份分區(qū)的話，分區(qū)表執(zhí)行對(duì)應(yīng)sql，經(jīng)過(guò)分區(qū)剪枝后只需要掃描其中一個(gè)分區(qū)，掃描數(shù)據(jù)量為1/10

-- 計(jì)算2019年的所有訂單的銷(xiāo)售總額 select `name` from `tp` where `purchased` between date_format('2019-01-01', '%Y%m%d') and date_format('2019-12-31', '%Y%m%d'); select * from tr partition (p4);

分區(qū)適用場(chǎng)景

數(shù)據(jù)分布

GaussDB(DWS)分布式數(shù)據(jù)庫(kù)的數(shù)據(jù)表是分散在所有數(shù)據(jù)節(jié)點(diǎn)(DataNode,DN)上的，所以創(chuàng)建表的時(shí)候需要指定分布列。

-- Hash分布方式 create table `sales_fact` ( `region_id` integer, `depart_id` integer, `product_id` integer, `sale_amt` numeric(9, 2), `sale_qty` integer ) distribute by hash(region_id,depart_id,product_id);

-- Replication復(fù)制分布方式 create table `depart_dim` ( `depart_id` integer, `depart_name` varchar(60) ) distribute by replication;

數(shù)據(jù)策略選擇

復(fù)制（Replication）

適合于記錄集較小的表。

表中數(shù)據(jù)在各節(jié)點(diǎn)上完全復(fù)制，各DN都擁有全量數(shù)據(jù)。

Hash/List/Range

這三種方式適合數(shù)據(jù)量較大的表

分布列選擇原則

選擇分布列的時(shí)候，一般遵循下面三個(gè)原則:

盡量選擇離散值比較多的列，保證數(shù)據(jù)均勻分布。分布均勻是為了避免木桶效應(yīng)，各個(gè)DN對(duì)等執(zhí)行。

在滿足第一條原則的情況下，不要選擇存在常量過(guò)濾的列。

滿足前兩條原則的情況下，盡量選擇關(guān)聯(lián)字段或聚合字段做分布列，這種方式是為了避免數(shù)據(jù)節(jié)點(diǎn)之間數(shù)據(jù)重分布，降低IO的開(kāi)銷(xiāo)從而提升關(guān)聯(lián)操作和聚合操作的性能。

數(shù)據(jù)類(lèi)型

基本數(shù)據(jù)類(lèi)型

數(shù)值類(lèi)型，字符類(lèi)型，二進(jìn)制類(lèi)型，日期/時(shí)間類(lèi)型，布爾類(lèi)型，枚舉型等。

序列號(hào)類(lèi)型，幾何類(lèi)型

字段設(shè)計(jì)建議

盡量使用高效數(shù)據(jù)類(lèi)型

盡量使用執(zhí)行效率比較高的數(shù)據(jù)類(lèi)型

盡量使用短字段的數(shù)據(jù)類(lèi)型

使用一致的數(shù)據(jù)類(lèi)型

當(dāng)多個(gè)表存在邏輯關(guān)系時(shí)，表示同一含義的字段應(yīng)該使用相同的數(shù)據(jù)類(lèi)型。

對(duì)于字符串?dāng)?shù)據(jù)，建議使用變長(zhǎng)字符串?dāng)?shù)據(jù)類(lèi)型，并指定最大長(zhǎng)度。

視圖（View）

視圖與基本表不同，不是物理上實(shí)際存在的，是一個(gè)虛表。

視圖的作用

視圖作用

簡(jiǎn)化操作，把經(jīng)常使用的數(shù)據(jù)定義為視圖。

安全性，用戶只能查詢和修改能看到的數(shù)據(jù)。

邏輯上的獨(dú)立性，屏蔽了真實(shí)表的結(jié)構(gòu)帶來(lái)的影響。

限制性

性能問(wèn)題：查詢可能很簡(jiǎn)單，但是封裝的視圖語(yǔ)句很復(fù)雜。

修改限制：對(duì)于復(fù)雜視圖，用戶不能通過(guò)視圖修改基表數(shù)據(jù)。

-- 通過(guò)視圖封裝較為復(fù)雜的邏輯 create view stu_class(id, name, class) as select student.s_id, student.name, stu_info.class from student, stu_info where student.s_id = stu_info.sid; select * from stu_class where class = 'shanghai';

索引（index）

索引提供指向存儲(chǔ)在表的指定列中的數(shù)據(jù)值的指針，如同圖書(shū)的目錄，能夠加快表的查詢速度，但同時(shí)也增加了插入，更新和刪除操作的處理時(shí)間。

在創(chuàng)建索引時(shí)，以下建議作為參考：

在經(jīng)常需要搜索查詢的列上創(chuàng)建索引，可以加快搜索速度。

在作為主鍵的列上創(chuàng)建索引，強(qiáng)制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)。

在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的。

在經(jīng)常需要排序的列上創(chuàng)建索引，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時(shí)間。

在經(jīng)常使用WHERE字句的列上創(chuàng)建索引，加快條件的判斷速度。

為經(jīng)常出現(xiàn)在關(guān)鍵字ORDER BY,GROUP BY,DISTINCT后面的字段建立索引。

有效索引

創(chuàng)建索引 不等于 索引一定被使用

索引創(chuàng)建成功后，系統(tǒng)會(huì)自動(dòng)判斷何時(shí)引用索引。當(dāng)系統(tǒng)認(rèn)為使用索引比順序掃描更快時(shí)，就會(huì)使用索引。

索引創(chuàng)建成功后，必須和表保持同步以保證能夠準(zhǔn)確地找到新數(shù)據(jù)，這樣就增加了數(shù)據(jù)操作地負(fù)荷。

需要定期刪除無(wú)用的索引。

判斷方法

通過(guò)執(zhí)行explain語(yǔ)句查看執(zhí)行計(jì)劃來(lái)判斷是否使用索引。

索引方式

約束

數(shù)據(jù)的完整性是指數(shù)據(jù)的正確性和一致性，可以通過(guò)定義表時(shí)定義完整性約束。

完整性約束是一種規(guī)則，本身不占用數(shù)據(jù)庫(kù)空間。

完整性約束和表結(jié)構(gòu)定義一起保存在數(shù)據(jù)字典中。

常見(jiàn)的約束類(lèi)型

唯一性和主鍵約束（UNIQUE/PRIMARY KEY）

外鍵約束（FOREIGN KEY）

檢查約束（CHECK）

非空約束（NOT NULL）

默認(rèn)約束（DEFAULT）

約束的設(shè)計(jì)

如果能夠從業(yè)務(wù)層面補(bǔ)全字段值，就不建議使用DEFAULT約束，避免數(shù)據(jù)加載時(shí)產(chǎn)生不符合預(yù)期的結(jié)果。

給明確不存在NULL值的字段加上NOT NULL約束，優(yōu)化器會(huì)對(duì)其進(jìn)行自動(dòng)優(yōu)化。

給可以顯式命名的約束顯式命名。除了NOT NULL和DEFAULT約束外，其他約束都可以顯式命名。

數(shù)據(jù)庫(kù)對(duì)象間關(guān)系

事務(wù)（Transaction）

事務(wù)是用戶定義的數(shù)據(jù)操作系列，這些操作作為一個(gè)完整的工作單元執(zhí)行。

原子性（Atomicity）：事務(wù)是數(shù)據(jù)庫(kù)的邏輯工作單位，事務(wù)中的操作，要么都做，要么都不做。

一致性（Consistency）：事務(wù)的執(zhí)行結(jié)果必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)轉(zhuǎn)到另一個(gè)一致性狀態(tài)。

隔離性（Isolation）：數(shù)據(jù)庫(kù)中一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個(gè)事務(wù)的內(nèi)部操作及使用的數(shù)據(jù)對(duì)其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)不能相互干擾。

持久性（Durability）：事務(wù)一旦提交，對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變是永久的。提交后的操作或者故障不會(huì)對(duì)事務(wù)的操作結(jié)果產(chǎn)生任何影響。

事務(wù)結(jié)束的標(biāo)記有兩個(gè)：

正常結(jié)束，COMMIT（提交事務(wù)）。

異常結(jié)束，ROLLBACK（回滾事務(wù)）。

事務(wù)處理模型

事務(wù)處理模型分為兩類(lèi)：

隱式事務(wù)：每一條數(shù)據(jù)操作語(yǔ)句都自動(dòng)地成為一個(gè)事務(wù)，GaussDB(for MySQL)默認(rèn)是隱式提交。

顯式事務(wù)：事務(wù)有顯式的開(kāi)始和結(jié)束標(biāo)記。

數(shù)據(jù)不一致情況 - 臟讀

“Dirty” Reads（臟讀）

一個(gè)事務(wù)讀取到了其他事務(wù)中還沒(méi)有提交（Committed）的數(shù)據(jù)。

因?yàn)槲刺峤粩?shù)據(jù)存在回滾的可能，所以稱為"臟"數(shù)據(jù)。

數(shù)據(jù)不一致情況 - 不可重復(fù)讀

Non-repeatable Reads（不可重復(fù)讀）

一個(gè)事務(wù)所獲取到的數(shù)據(jù)是可以被其他事務(wù)修改的。

一個(gè)事務(wù)在處理過(guò)程中多次讀取同一個(gè)數(shù)據(jù)（重復(fù)讀），這個(gè)數(shù)據(jù)是可能發(fā)生變化的，因此被稱為不可重復(fù)讀。

Phantom Read（幻影讀）

是不可重復(fù)讀的更為特殊的一個(gè)場(chǎng)景。

事務(wù)T1按照一定條件讀取數(shù)據(jù)（使用了WHERE條件過(guò)濾）后，事務(wù)T2刪除了部分記錄或者插入了一些新的記錄，這些變更的數(shù)據(jù)是滿足WHERE條件過(guò)濾的。

那么當(dāng)T1再次按照相同條件讀取數(shù)據(jù)時(shí)，就會(huì)發(fā)現(xiàn)莫名其妙地少了（也可能多了）一些數(shù)據(jù)。

這些變化的數(shù)據(jù)就被稱為"幻影"數(shù)據(jù)。

todo img10

事務(wù)隔離級(jí)別（1）

ANSI SQL標(biāo)準(zhǔn)定義了4種事務(wù)隔離級(jí)別來(lái)避免3種數(shù)據(jù)不一致的問(wèn)題。事務(wù)等級(jí)從高到低，分別是：

Serializable（序列化）

系統(tǒng)中所有的事務(wù)以串行地方式逐個(gè)執(zhí)行，所以能避免所有數(shù)據(jù)不一致情況。

但是這種以排他方式來(lái)控制并發(fā)事務(wù)，串行化執(zhí)行方式會(huì)導(dǎo)致事務(wù)排隊(duì)，系統(tǒng)的并發(fā)量大幅下降，使用的時(shí)候要絕對(duì)慎重。

Repeatable Read（可重復(fù)讀）

一個(gè)事務(wù)一旦開(kāi)始，事務(wù)過(guò)程中所讀取的所有數(shù)據(jù)不允許被其他事務(wù)修改。

這個(gè)隔離級(jí)別沒(méi)有辦法解決"幻影讀"問(wèn)題。

因?yàn)樗?保護(hù)"了它讀取的數(shù)據(jù)不被修改，但是其他數(shù)據(jù)會(huì)被修改。如果其他數(shù)據(jù)被修改后恰好滿足了當(dāng)前事務(wù)的過(guò)濾條件（where語(yǔ)句），那么就會(huì)發(fā)生"幻影讀"的情況。

事務(wù)隔離級(jí)別（2）

其他兩種事務(wù)隔離等級(jí)為：

Read Committed（已提交讀）

一個(gè)事務(wù)能讀取到其他事務(wù)提交過(guò)（Committed）的數(shù)據(jù)。

一個(gè)事務(wù)在處理過(guò)程中如果重復(fù)讀取某一個(gè)數(shù)據(jù)，而且這個(gè)數(shù)據(jù)恰好被其他事務(wù)修改并提交了，那么當(dāng)前重復(fù)讀取數(shù)據(jù)的事務(wù)就會(huì)出現(xiàn)同一個(gè)數(shù)據(jù)前后不同的情況。

在這個(gè)隔離級(jí)別會(huì)發(fā)生"不可重復(fù)讀"的場(chǎng)景。

Read Uncommitted（未提交讀）

一個(gè)事務(wù)能讀取到其他事務(wù)修改過(guò)，但是還沒(méi)有提交的（Uncommitted）的數(shù)據(jù)。

數(shù)據(jù)被其他事務(wù)修改過(guò)，但還沒(méi)有提交，就存在著回滾的可能性，這時(shí)候讀取這些"未提交"數(shù)據(jù)的情況就是"臟讀"。

在這個(gè)隔離級(jí)別會(huì)發(fā)生"臟讀"場(chǎng)景。

事務(wù)隔離級(jí)別與問(wèn)題對(duì)應(yīng)表

云數(shù)據(jù)庫(kù) GaussDB(for MySQL) 云數(shù)據(jù)庫(kù) GaussDB(for openGauss) 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。