數(shù)據(jù)庫優(yōu)化課件VIP

1、第八章第八章 綜合實(shí)戰(zhàn)（二）綜合實(shí)戰(zhàn)（二） 數(shù)據(jù)轉(zhuǎn)換的原因是什么？數(shù)據(jù)轉(zhuǎn)換的原因是什么？有哪些數(shù)據(jù)轉(zhuǎn)換工具？有哪些數(shù)據(jù)轉(zhuǎn)換工具？數(shù)據(jù)轉(zhuǎn)換之前需要考慮哪些問題？數(shù)據(jù)轉(zhuǎn)換之前需要考慮哪些問題？監(jiān)控?cái)?shù)據(jù)庫運(yùn)行狀態(tài)監(jiān)控?cái)?shù)據(jù)庫運(yùn)行狀態(tài)利用索引實(shí)現(xiàn)優(yōu)化查詢利用索引實(shí)現(xiàn)優(yōu)化查詢根據(jù)查詢結(jié)果進(jìn)行數(shù)據(jù)庫的優(yōu)化根據(jù)查詢結(jié)果進(jìn)行數(shù)據(jù)庫的優(yōu)化備份恢復(fù)數(shù)據(jù)庫，并實(shí)現(xiàn)自動(dòng)化功能備份恢復(fù)數(shù)據(jù)庫，并實(shí)現(xiàn)自動(dòng)化功能建立建立SSIS包實(shí)現(xiàn)復(fù)雜的工作應(yīng)用包實(shí)現(xiàn)復(fù)雜的工作應(yīng)用使用使用SQL函數(shù)編寫復(fù)雜函數(shù)編寫復(fù)雜SQL語句完成查詢工作語句完成查詢工作Tariff是電信公司的計(jì)費(fèi)數(shù)據(jù)庫，計(jì)費(fèi)數(shù)據(jù)每月都是電信公司的計(jì)費(fèi)數(shù)據(jù)庫，計(jì)費(fèi)數(shù)據(jù)每

2、月都要進(jìn)行維護(hù)要進(jìn)行維護(hù) 將程控交換機(jī)下來的原始通話記錄文件導(dǎo)入數(shù)據(jù)庫將程控交換機(jī)下來的原始通話記錄文件導(dǎo)入數(shù)據(jù)庫對(duì)通話記錄進(jìn)行批價(jià)、合帳形成賬單對(duì)通話記錄進(jìn)行批價(jià)、合帳形成賬單 用戶可以查詢當(dāng)月的賬單情況也可以對(duì)賬單進(jìn)行繳費(fèi)用戶可以查詢當(dāng)月的賬單情況也可以對(duì)賬單進(jìn)行繳費(fèi)用戶可以隨時(shí)查看自己的歷史通話記錄詳單和歷史賬單用戶可以隨時(shí)查看自己的歷史通話記錄詳單和歷史賬單情況情況 電信查看每月收入、欠費(fèi)用戶情況等電信查看每月收入、欠費(fèi)用戶情況等每 月 初 程 控交 換 機(jī) 提 供上 月 的 原 始通 話 記 錄 文件計(jì)費(fèi)中心導(dǎo)入原始通話記錄每月5號(hào)前，完成批價(jià)合帳形成繳費(fèi)賬單準(zhǔn) 備 接受繳費(fèi)準(zhǔn)備接受

3、繳費(fèi)每月26日前用戶對(duì)上月話費(fèi)進(jìn)行繳費(fèi)月底前將本月已繳費(fèi)數(shù)據(jù)導(dǎo)出到歷史數(shù)據(jù)1.系統(tǒng)提供各類統(tǒng)計(jì)查詢工作2.用戶在26日后繳費(fèi)，需要繳滯納金，滯納金每天按欠費(fèi)1%收取每月26日后將本月已繳費(fèi)數(shù)據(jù)導(dǎo)出到歷史數(shù)據(jù)需求描述需求描述電信公司在月底準(zhǔn)備進(jìn)行用戶的繳費(fèi)工作，需要對(duì)通電信公司在月底準(zhǔn)備進(jìn)行用戶的繳費(fèi)工作，需要對(duì)通話記錄話記錄call表中所有用戶通話記錄進(jìn)行批價(jià)表中所有用戶通話記錄進(jìn)行批價(jià),批價(jià)日期批價(jià)日期為為2008-09-05 建立性能監(jiān)視器監(jiān)視建立性能監(jiān)視器監(jiān)視tempdb文件大小和文件大小和Tariff數(shù)據(jù)庫數(shù)據(jù)庫日志文件大小，分析監(jiān)視后的結(jié)果并給出改進(jìn)建議日志文件大小，分析監(jiān)視后的結(jié)果

4、并給出改進(jìn)建議 實(shí)現(xiàn)思路：實(shí)現(xiàn)思路：本次實(shí)驗(yàn)依賴于上一次課的實(shí)驗(yàn)結(jié)果（本次實(shí)驗(yàn)依賴于上一次課的實(shí)驗(yàn)結(jié)果（8月份的原始通月份的原始通話記錄已經(jīng)導(dǎo)入數(shù)據(jù)庫）話記錄已經(jīng)導(dǎo)入數(shù)據(jù)庫）對(duì)所有用戶進(jìn)行批價(jià)的流程參考個(gè)人批價(jià)對(duì)所有用戶進(jìn)行批價(jià)的流程參考個(gè)人批價(jià)pExec procTariffAll 在批價(jià)之前先建立性能監(jiān)視器，至少添加跟蹤在批價(jià)之前先建立性能監(jiān)視器，至少添加跟蹤tempdb數(shù)據(jù)庫文件和數(shù)據(jù)庫文件和tariff數(shù)據(jù)庫日志文件大小計(jì)數(shù)器數(shù)據(jù)庫日志文件大小計(jì)數(shù)器學(xué)員練習(xí)：學(xué)員練習(xí)：建立性能監(jiān)視器，跟蹤建立性能監(jiān)視器，跟蹤tempdb數(shù)據(jù)庫文件和數(shù)據(jù)庫文件和tariff數(shù)據(jù)數(shù)據(jù)庫日志文件大小計(jì)數(shù)器庫

5、日志文件大小計(jì)數(shù)器 使用使用BIDS建立建立SSIS包包TariffAll，完成批價(jià)功能，完成批價(jià)功能 查看批價(jià)過程監(jiān)視計(jì)數(shù)器的結(jié)果，并給出分析和優(yōu)化查看批價(jià)過程監(jiān)視計(jì)數(shù)器的結(jié)果，并給出分析和優(yōu)化建議建議 查詢查詢Accountbill表中批價(jià)記錄數(shù)與合計(jì)金額表中批價(jià)記錄數(shù)與合計(jì)金額 2020分鐘完成分鐘完成記錄數(shù)合計(jì)金額需求描述需求描述本次實(shí)驗(yàn)依賴于前一實(shí)驗(yàn)結(jié)果，即本次實(shí)驗(yàn)依賴于前一實(shí)驗(yàn)結(jié)果，即8月份所有用戶的批月份所有用戶的批價(jià)已經(jīng)完成價(jià)已經(jīng)完成查詢號(hào)碼為的用戶未繳費(fèi)信息查詢號(hào)碼為的用戶未繳費(fèi)信息(賬單日期，賬單金額賬單日期，賬單金額)p帳單表帳單表accountbill表中的列表中的列i

6、sPaid=0為未繳費(fèi)為未繳費(fèi)優(yōu)化查詢速度，給出優(yōu)化改進(jìn)建議優(yōu)化查詢速度，給出優(yōu)化改進(jìn)建議查詢未繳費(fèi)賬單滯納金查詢未繳費(fèi)賬單滯納金(可選任務(wù)可選任務(wù)) 實(shí)現(xiàn)思路：實(shí)現(xiàn)思路：利用利用SQL語句查詢的用戶未繳費(fèi)信息語句查詢的用戶未繳費(fèi)信息查看優(yōu)化前的查看優(yōu)化前的SQL執(zhí)行計(jì)劃和客戶端統(tǒng)計(jì)信息執(zhí)行計(jì)劃和客戶端統(tǒng)計(jì)信息使用數(shù)據(jù)庫引擎優(yōu)化顧問優(yōu)化數(shù)據(jù)庫使用數(shù)據(jù)庫引擎優(yōu)化顧問優(yōu)化數(shù)據(jù)庫再次查看再次查看SQL執(zhí)行計(jì)劃和客戶端統(tǒng)計(jì)信息執(zhí)行計(jì)劃和客戶端統(tǒng)計(jì)信息使用使用SQL語句查詢用戶的滯納金語句查詢用戶的滯納金p滯納金：繳費(fèi)日期每超過賬單所在月滯納金：繳費(fèi)日期每超過賬單所在月26號(hào)一天，收取賬單金號(hào)一天，收取

7、賬單金額的額的1%學(xué)員練習(xí)：學(xué)員練習(xí)：使用使用SQL語句查詢用戶的未繳費(fèi)信息語句查詢用戶的未繳費(fèi)信息使用數(shù)據(jù)庫引擎優(yōu)化顧問優(yōu)化數(shù)據(jù)庫使用數(shù)據(jù)庫引擎優(yōu)化顧問優(yōu)化數(shù)據(jù)庫建立索引，從而優(yōu)化查詢速度建立索引，從而優(yōu)化查詢速度使用使用SQL語句查詢用戶的滯納金語句查詢用戶的滯納金3535分鐘完成分鐘完成未繳費(fèi)信息用戶滯納金需求描述需求描述電信公司的數(shù)據(jù)庫電信公司的數(shù)據(jù)庫Tariff對(duì)數(shù)據(jù)的穩(wěn)定性有非常高的要對(duì)數(shù)據(jù)的穩(wěn)定性有非常高的要求求p對(duì)數(shù)據(jù)庫執(zhí)行完整對(duì)數(shù)據(jù)庫執(zhí)行完整+事務(wù)日志備份事務(wù)日志備份用戶繳費(fèi)后，數(shù)據(jù)丟失需要能夠完全恢復(fù)用戶繳費(fèi)后，數(shù)據(jù)丟失需要能夠完全恢復(fù) 定期讓數(shù)據(jù)庫自動(dòng)完成完整、差異、事務(wù)

8、日志備份定期讓數(shù)據(jù)庫自動(dòng)完成完整、差異、事務(wù)日志備份(可可選任務(wù)選任務(wù)) 實(shí)現(xiàn)思路：實(shí)現(xiàn)思路：對(duì)用戶進(jìn)行繳費(fèi)對(duì)用戶進(jìn)行繳費(fèi)p Exec procPaybill Accountbill表中記錄用戶賬單表中記錄用戶賬單pisPaid列為列為0（表示未繳費(fèi)）（表示未繳費(fèi)）pisPaid列為列為1（表示己繳費(fèi)）（表示己繳費(fèi)）通過停止數(shù)據(jù)庫服務(wù)，刪除數(shù)據(jù)文件通過停止數(shù)據(jù)庫服務(wù)，刪除數(shù)據(jù)文件Tariff.mdf來模擬來模擬數(shù)據(jù)破壞數(shù)據(jù)破壞備份數(shù)據(jù)庫并利用備份執(zhí)行還原備份數(shù)據(jù)庫并利用備份執(zhí)行還原通過作業(yè)來執(zhí)行自動(dòng)備份（可選）通過作業(yè)來執(zhí)行自動(dòng)備份（可選）學(xué)員練習(xí)：學(xué)員練習(xí)：備份數(shù)據(jù)庫備份數(shù)據(jù)庫用戶繳費(fèi)后，模

9、擬數(shù)據(jù)丟失用戶繳費(fèi)后，模擬數(shù)據(jù)丟失p刪除數(shù)據(jù)文件刪除數(shù)據(jù)文件利用備份進(jìn)行恢復(fù)利用備份進(jìn)行恢復(fù)還原后查詢用戶的繳費(fèi)狀態(tài)還原后查詢用戶的繳費(fèi)狀態(tài)p應(yīng)有己繳納費(fèi)用的記錄應(yīng)有己繳納費(fèi)用的記錄 制定備份策略，設(shè)置作業(yè)讓數(shù)據(jù)庫自動(dòng)備份（可選）制定備份策略，設(shè)置作業(yè)讓數(shù)據(jù)庫自動(dòng)備份（可選）2020分鐘完成分鐘完成需求描述需求描述Tariff數(shù)據(jù)庫在完成了批價(jià)、查詢、收費(fèi)和備份后，需數(shù)據(jù)庫在完成了批價(jià)、查詢、收費(fèi)和備份后，需要將已經(jīng)繳費(fèi)的通話記錄和賬單數(shù)據(jù)導(dǎo)出到歷史賬單要將已經(jīng)繳費(fèi)的通話記錄和賬單數(shù)據(jù)導(dǎo)出到歷史賬單表中，以便于日后的歸檔和查閱工作表中，以便于日后的歸檔和查閱工作p從從call批量導(dǎo)出已批價(jià)電話

10、記錄到歷史通話記錄數(shù)據(jù)表批量導(dǎo)出已批價(jià)電話記錄到歷史通話記錄數(shù)據(jù)表history-callp從賬單表從賬單表accountbill導(dǎo)出已繳費(fèi)賬單到歷史帳單表導(dǎo)出已繳費(fèi)賬單到歷史帳單表history-accountbill 實(shí)現(xiàn)思路：實(shí)現(xiàn)思路：先執(zhí)行存儲(chǔ)過程，模擬全部用戶進(jìn)行繳費(fèi)先執(zhí)行存儲(chǔ)過程，模擬全部用戶進(jìn)行繳費(fèi)利用利用SSIS建立工程包建立工程包p導(dǎo)出己批價(jià)的通話記錄。導(dǎo)出己批價(jià)的通話記錄。Accountbillid為空表示未批價(jià)，不為空表示未批價(jià)，不為空表示已批價(jià)為空表示已批價(jià) p導(dǎo)出已經(jīng)繳費(fèi)的賬單信息。導(dǎo)出已經(jīng)繳費(fèi)的賬單信息。isPaid列為列為0表示未繳費(fèi)，表示未繳費(fèi)， isPaid列

11、為列為1表示己繳費(fèi)表示己繳費(fèi)p刪除己批價(jià)的通話記錄刪除己批價(jià)的通話記錄p刪除己繳費(fèi)的賬單記錄刪除己繳費(fèi)的賬單記錄注意：數(shù)據(jù)源的訪問模式選擇“SQL命令”學(xué)員練習(xí)：學(xué)員練習(xí)：建立數(shù)據(jù)流，移動(dòng)當(dāng)前已批價(jià)通話記錄到歷史通話記建立數(shù)據(jù)流，移動(dòng)當(dāng)前已批價(jià)通話記錄到歷史通話記錄錄建立數(shù)據(jù)流，移動(dòng)當(dāng)前已繳費(fèi)賬單信息到歷史賬單信建立數(shù)據(jù)流，移動(dòng)當(dāng)前已繳費(fèi)賬單信息到歷史賬單信息息執(zhí)行執(zhí)行SQL任務(wù)，清空已批價(jià)通話記錄任務(wù)，清空已批價(jià)通話記錄執(zhí)行執(zhí)行SQL任務(wù)，清空已繳費(fèi)賬單記錄任務(wù)，清空已繳費(fèi)賬單記錄3535分鐘完成分鐘完成需求描述需求描述Tariff數(shù)據(jù)庫在完成了日常的維護(hù)工作之后，需要將當(dāng)數(shù)據(jù)庫在完成了日常的維護(hù)工作之后，需要將當(dāng)月所產(chǎn)生的話費(fèi)進(jìn)行分類匯總，以便于月度工作總結(jié)月所產(chǎn)生的話費(fèi)進(jìn)行分類匯總，以便于月度工作總結(jié)時(shí)提供有效的數(shù)據(jù)依據(jù)時(shí)提供有效的數(shù)據(jù)依據(jù)p查詢號(hào)碼在查詢號(hào)碼在2008年年3月的賬單記錄，并優(yōu)化查詢性能月的賬單記錄，并優(yōu)化查詢性能p查詢當(dāng)前所有欠費(fèi)用戶和合計(jì)欠費(fèi)金額查詢當(dāng)前所有欠費(fèi)用戶和合計(jì)欠費(fèi)金額p統(tǒng)計(jì)統(tǒng)計(jì)08年年2月到月到7月的每月話費(fèi)收入月的每月話費(fèi)收入 實(shí)現(xiàn)思路：實(shí)現(xiàn)思路：使用使用SQL語句完成查詢?nèi)蝿?wù)語句完成查詢?nèi)蝿?wù)使用數(shù)據(jù)庫引擎優(yōu)化顧問優(yōu)化數(shù)據(jù)庫使用數(shù)據(jù)庫引擎優(yōu)化顧問優(yōu)化數(shù)據(jù)庫在賬單表在賬單表AccountBill表中表中isPaid=0表示未繳