建站
咨詢
售后
建站咨詢
新聞中心
在現(xiàn)代科技時代中，數(shù)據(jù)庫系統(tǒng)是每個公司的重要數(shù)據(jù)管理工具。無論是大型企業(yè)還是小型企業(yè)，數(shù)據(jù)庫系統(tǒng)都有其極其重要的地位，數(shù)據(jù)庫不僅僅是單純的存儲數(shù)據(jù)的工具，更是公司發(fā)展過程中重要的信息管理工具。然而，隨著數(shù)據(jù)庫規(guī)模的擴大和數(shù)據(jù)量的增加，數(shù)據(jù)庫優(yōu)化也越來越引起廣泛關(guān)注。本篇文章將告訴您如何提升性能、空間、安全性，并全方位解析數(shù)據(jù)庫優(yōu)化的必要性。
創(chuàng)新互聯(lián)是一家企業(yè)級云計算解決方案提供商,超15年IDC數(shù)據(jù)中心運營經(jīng)驗。主營GPU顯卡服務(wù)器，站群服務(wù)器，四川電信機房托管，海外高防服務(wù)器，機柜大帶寬，動態(tài)撥號VPS，海外云手機，海外云服務(wù)器，海外服務(wù)器租用托管等。
什么是數(shù)據(jù)庫優(yōu)化？
在談?wù)搩?yōu)化之前，我們必須先了解什么是數(shù)據(jù)庫。數(shù)據(jù)庫是一個電子系統(tǒng)，其中包含操作系統(tǒng)和應(yīng)用程序軟件，旨在存儲和管理企業(yè)數(shù)據(jù)。數(shù)據(jù)庫優(yōu)化是一種改進數(shù)據(jù)庫系統(tǒng)性能的過程，以使其更快，更安全，更可靠。數(shù)據(jù)庫中的優(yōu)化并不是一項單一的任務(wù)，而是一系列任務(wù)，這些任務(wù)需要針對特定的性能目標(biāo)進行優(yōu)化。
數(shù)據(jù)庫優(yōu)化的好處
在今天的數(shù)碼時代中，數(shù)據(jù)庫被廣泛應(yīng)用于各個領(lǐng)域。充足的數(shù)據(jù)能夠讓企業(yè)在經(jīng)營上走得更加穩(wěn)定，因此數(shù)據(jù)庫的重要性也日益顯現(xiàn)。數(shù)據(jù)庫優(yōu)化有助于改善系統(tǒng)性能、減少數(shù)據(jù)恢復(fù)時間、提高數(shù)據(jù)可用性、節(jié)省成本以及保護企業(yè)數(shù)據(jù)。
提升數(shù)據(jù)庫性能
數(shù)據(jù)庫性能是評估數(shù)據(jù)庫有效性的關(guān)鍵指標(biāo)之一。光速訪問和數(shù)據(jù)加速的需求使得優(yōu)化數(shù)據(jù)庫性能成為企業(yè)的首要任務(wù)。性能優(yōu)化可以通過以下多種方式來實現(xiàn)：
1.索引優(yōu)化：索引是管理數(shù)據(jù)庫的重要工具，索引優(yōu)化可以加速查詢數(shù)據(jù)的速度。
2.查詢優(yōu)化：這是提高查詢性能的關(guān)鍵。查詢優(yōu)化可以通過調(diào)整查詢方式的方法來優(yōu)化性能。
3.緩存優(yōu)化：緩存優(yōu)化可以避免無謂的查詢操作，減少數(shù)據(jù)庫的負(fù)載。緩存是一個由操作系統(tǒng)管理的物理內(nèi)存塊，使得數(shù)據(jù)可以更快地存儲和訪問。
4.數(shù)據(jù)分區(qū)：把數(shù)據(jù)分割成多個分區(qū)，可以使查詢速度更快，同時減輕數(shù)據(jù)庫負(fù)載。
5.負(fù)載均衡：這可以提高系統(tǒng)的可伸縮性和效率，可以將負(fù)載平均分配到多個數(shù)據(jù)庫中。
提高數(shù)據(jù)庫空間
擴大數(shù)據(jù)庫空間可以提高數(shù)據(jù)容量。通常，數(shù)據(jù)庫空間被限制在一定的空間大小內(nèi)，如果已經(jīng)用滿、而且無法擴大空間的話，那么就需要優(yōu)化數(shù)據(jù)庫。以下是一些提高容量的方法。
1.壓縮數(shù)據(jù)：壓縮數(shù)據(jù)是一種節(jié)省空間的方法。對于數(shù)字類型的數(shù)據(jù)，這種方式比對字符串類型數(shù)據(jù)更加有效。
2.不使用日志：禁用日志或刪除日志可以減少磁盤空間的占用。
3.移除冗余數(shù)據(jù)：移除無用的數(shù)據(jù)、歸檔舊數(shù)據(jù)以及被重復(fù)的數(shù)據(jù)是優(yōu)化存儲空間的有效方法。
加強數(shù)據(jù)庫安全
數(shù)據(jù)庫安全是每個企業(yè)都需要考慮的問題。數(shù)據(jù)泄露和黑客攻擊可以導(dǎo)致沉重的損失，包括信息泄露、資金損失、聲譽受損等。以下是一些提高數(shù)據(jù)庫安全性的方法。
1.授權(quán)和訪問控制：管理數(shù)據(jù)庫的用戶名和密碼應(yīng)該合理地分配給需要訪問的用戶。
2.數(shù)據(jù)加密：受密碼保護或加密后的數(shù)據(jù)是維護數(shù)據(jù)私密性的有效方法。
3.定期備份和還原：定期備份數(shù)據(jù)是減輕數(shù)據(jù)丟失和恢復(fù)操作的重要方案。
結(jié)語
數(shù)據(jù)庫優(yōu)化是為了更大化數(shù)據(jù)庫系統(tǒng)的效率和可靠性。一些技術(shù)經(jīng)常被用于數(shù)據(jù)庫優(yōu)化，但對于不同的數(shù)據(jù)庫來說，需要一個定制的戰(zhàn)略。當(dāng)您決定要對數(shù)據(jù)庫進行優(yōu)化時，請確保參考以上方法來確定優(yōu)化方案的更佳選擇，并實施適當(dāng)?shù)拇胧┮员ＷC數(shù)據(jù)的可靠性和安全性。
相關(guān)問題拓展閱讀：
- 誰知道數(shù)據(jù)庫優(yōu)化設(shè)計方案有哪些？
- mysql數(shù)據(jù)庫怎么優(yōu)化，有幾方面的優(yōu)化?
- 數(shù)據(jù)庫優(yōu)化是什么意思？
誰知道數(shù)據(jù)庫優(yōu)化設(shè)計方案有哪些？
本文首先討論了基于第三范式的數(shù)據(jù)庫表的基本設(shè)計，著重論述了建立主鍵和索引的策略和方案，然后從數(shù)據(jù)庫表的擴展設(shè)計和庫表對象的放置等角度概述了數(shù)據(jù)庫管理系統(tǒng)的優(yōu)化方案。
關(guān)鍵詞： 優(yōu)化（Optimizing） 第三范式（3NF） 冗余數(shù)據(jù)（Redundant Data） 索引（Index） 數(shù)據(jù)分割（Data Partitioning） 對象放置（Object Placement）
1 引言
數(shù)據(jù)庫優(yōu)化的目標(biāo)無非是避免磁盤I/O瓶頸、減少CPU利用率和減少資源競爭。為了便于讀者閱讀和理解，筆者參閱了Sybase、Informix和Oracle等大型數(shù)據(jù)庫系統(tǒng)參考資料，基于多年的工程實踐經(jīng)驗，從基本表設(shè)計、擴展設(shè)計和數(shù)據(jù)庫表對象放置等角度進行討論，著重討論了如何避免磁盤I/O瓶頸和減少資源競爭，相信讀者會一目了然。
2 基于第三范式的基本表設(shè)計
在基于表驅(qū)動的信息管理系統(tǒng)（MIS）中，基本表的設(shè)計規(guī)范是第三范式（3NF）。第三范式的基本特征是非主鍵屬性只依賴于主鍵屬性?；诘谌妒降臄?shù)據(jù)庫表設(shè)計具有很多優(yōu)點：一是消除了冗余數(shù)據(jù)，節(jié)省了磁盤存儲空間；二是有良好的數(shù)據(jù)完整性限制，即基于主外鍵的參照完整限制和基于主鍵的實體完整性限制，這使得數(shù)據(jù)容易維護，也容易移植和更新；三是數(shù)據(jù)的可逆性好，在做連接（Join）查詢或者合并表時不遺漏、也不重復(fù)；四是因消除了冗余數(shù)據(jù)（冗余列），在查詢（Select）時每個數(shù)據(jù)頁存的數(shù)據(jù)行就多，這樣就有效地減少了邏輯I/O，每個Cash存的頁面就多，也減少物理I/O；五是對大多數(shù)事務(wù)(Transaction)而言，運行性能好；六是物理設(shè)計(Physical Design)的機動性較大，能滿足日益增長的用戶需求。
在基本表設(shè)計中，表的主鍵、外鍵、索引設(shè)計占有非常重要的地位，但系統(tǒng)設(shè)計人員往往只注重于滿足用戶要求，而沒有從系統(tǒng)優(yōu)化的高度來認(rèn)識和重視它們。實際上，它們與系統(tǒng)的運行性能密切相關(guān)?，F(xiàn)在從系統(tǒng)數(shù)據(jù)庫優(yōu)化角度討論這些基本概念及其重早跡純要意義：
（1）主鍵(Primary Key)：主鍵被用于復(fù)雜的SQL語句時，頻繁地在數(shù)據(jù)訪問中被用到。一個表只有一個主鍵。主鍵應(yīng)該有固定值（不能為Null或缺省值，要有相對穩(wěn)定性），不含代碼信息，易訪問。把常用（眾所周知）的列作為主鍵才有意義。短主鍵更佳（小于25bytes），主鍵的長短影響索引的大小，索引的大小影響索引頁的大小，從而影響磁盤I/O。主鍵分為自然主鍵和人為主鍵。自然主鍵由實體的屬性構(gòu)成，自然主鍵可以是復(fù)合性的，在形成復(fù)合主鍵時，主鍵列不能太多，復(fù)合主鍵使得Join*作復(fù)雜化、也增加了外鍵表的大小。人為主鍵是，在沒有合適的自然屬性鍵、或自然屬性復(fù)雜或靈敏度高時，人為形成的。人為主鍵一般是整型值（滿足最小化要求），沒有實際意義，也略微增加了表的大小；但減少了把它作為外鍵的表的大小。
（2）外鍵（Foreign Key）：外鍵的作用是建立關(guān)系型數(shù)據(jù)庫中表之間的關(guān)系（參照完整性），主鍵只能從獨立的實體遷移到非獨立的實體，成為后者的一個屬性，被稱為外鍵。
（3）索引(Index)：利用索引優(yōu)化系統(tǒng)性能是顯而易見的，對所有常用于查詢中的Where子句的列和所有用于排序的列創(chuàng)建索引，可以避免整表掃描或訪問，在不改變表的物理結(jié)構(gòu)的情況下，直接訪問特定的數(shù)據(jù)列，這樣減少數(shù)據(jù)存取時間；利用索引可以優(yōu)化或排除耗時的分類*作；把數(shù)據(jù)分散到不同的頁面上，就分散了插入的數(shù)據(jù)；主鍵自動建立了唯一索引，因此唯一索引也能確保數(shù)據(jù)的唯一性（即實體完整性）；索引碼越小，定位就越直接；新建的索引效能更好，因此定期更新索引非常必要。索引也有代價：有空間開銷，建立它也要花費時間，在進行Insert、Delete和Update*作時，也有維護代價。索引有兩種：聚陸咐族索引和非聚族索引。一個表只能有一個聚族索引，可有多個非聚族索引。使用聚族索引查詢數(shù)據(jù)要比使用非聚族索引快。在建索引前，應(yīng)利用數(shù)據(jù)庫系統(tǒng)函數(shù)估算索引的大小。
① 聚族索引（Clustered Index）：聚族索引的數(shù)據(jù)頁按物理有序儲存，占用空間小。選擇策略是，被用于Where子句的列：包括范圍查詢、模糊查詢或高度重復(fù)的列（連續(xù)磁盤掃描）；被用于連接Join*作的列；被用于Order by和Group by子句的列。聚族索引不利于插入*作，另外沒有必要用主鍵建聚族索州洞引。
② 非聚族索引（Nonclustered Index）：與聚族索引相比，占用空間大，而且效率低。選擇策略是，被用于Where子句的列：包括范圍查詢、模糊查詢（在沒有聚族索引時）、主鍵或外鍵列、點（指針類）或小范圍（返回的結(jié)果域小于整表數(shù)據(jù)的20%）查詢；被用于連接Join*作的列、主鍵列（范圍查詢）；被用于Order by和Group by子句的列；需要被覆蓋的列。對只讀表建多個非聚族索引有利。索引也有其弊端，一是創(chuàng)建索引要耗費時間，二是索引要占有大量磁盤空間，三是增加了維護代價（在修改帶索引的數(shù)據(jù)列時索引會減緩修改速度）。那么，在哪種情況下不建索引呢？對于小表（數(shù)據(jù)小于5頁）、小到中表（不直接訪問單行數(shù)據(jù)或結(jié)果集不用排序）、單值域（返回值密集）、索引列值太長（大于20bitys）、容易變化的列、高度重復(fù)的列、Null值列，對沒有被用于Where子語句和Join查詢的列都不能建索引。另外，對主要用于數(shù)據(jù)錄入的，盡可能少建索引。當(dāng)然，也要防止建立無效索引，當(dāng)Where語句中多于5個條件時，維護索引的開銷大于索引的效益，這時，建立臨時表存儲有關(guān)數(shù)據(jù)更有效。
批量導(dǎo)入數(shù)據(jù)時的注意事項：在實際應(yīng)用中，大批量的計算（如電信話單計費）用C語言程序做，這種基于主外鍵關(guān)系數(shù)據(jù)計算而得的批量數(shù)據(jù)（文本文件），可利用系統(tǒng)的自身功能函數(shù)（如Sybase的BCP命令）快速批量導(dǎo)入，在導(dǎo)入數(shù)據(jù)庫表時，可先刪除相應(yīng)庫表的索引，這有利于加快導(dǎo)入速度，減少導(dǎo)入時間。在導(dǎo)入后再重建索引以便優(yōu)化查詢。
（4）鎖：鎖是并行處理的重要機制，能保持?jǐn)?shù)據(jù)并發(fā)的一致性，即按事務(wù)進行處理；系統(tǒng)利用鎖，保證數(shù)據(jù)完整性。因此，我們避免不了死鎖，但在設(shè)計時可以充分考慮如何避免長事務(wù)，減少排它鎖時間，減少在事務(wù)中與用戶的交互，杜絕讓用戶控制事務(wù)的長短；要避免批量數(shù)據(jù)同時執(zhí)行，尤其是耗時并用到相同的數(shù)據(jù)表。鎖的征用：一個表同時只能有一個排它鎖，一個用戶用時，其它用戶在等待。若用戶數(shù)增加，則Server的性能下降，出現(xiàn)“假死”現(xiàn)象。如何避免死鎖呢？從頁級鎖到行級鎖，減少了鎖征用；給小表增加無效記錄，從頁級鎖到行級鎖沒有影響，若在同一頁內(nèi)競爭有影響，可選擇合適的聚族索引把數(shù)據(jù)分配到不同的頁面；創(chuàng)建冗余表；保持事務(wù)簡短；同一批處理應(yīng)該沒有網(wǎng)絡(luò)交互。
（5）查詢優(yōu)化規(guī)則：在訪問數(shù)據(jù)庫表的數(shù)據(jù)(Access Data)時，要盡可能避免排序（Sort）、連接(Join)和相關(guān)子查詢*作。經(jīng)驗告訴我們，在優(yōu)化查詢時，必須做到：
① 盡可能少的行；
② 避免排序或為盡可能少的行排序，若要做大量數(shù)據(jù)排序，更好將相關(guān)數(shù)據(jù)放在臨時表中*作；用簡單的鍵（列）排序，如整型或短字符串排序；
③ 避免表內(nèi)的相關(guān)子查詢；
④ 避免在Where子句中使用復(fù)雜的表達式或非起始的子字符串、用長字符串連接；
⑤ 在Where子句中多使用“與”（And）連接，少使用“或”(Or)連接；
⑥ 利用臨時數(shù)據(jù)庫。在查詢多表、有多個連接、查詢復(fù)雜、數(shù)據(jù)要過濾時，可以建臨時表（索引）以減少I/O。但缺點是增加了空間開銷。
除非每個列都有索引支持，否則在有連接的查詢時分別找出兩個動態(tài)索引，放在工作表中重新排序。
3 基本表擴展設(shè)計
基于第三范式設(shè)計的庫表雖然有其優(yōu)越性（見本文之一部分），然而在實際應(yīng)用中有時不利于系統(tǒng)運行性能的優(yōu)化：如需要部分?jǐn)?shù)據(jù)時而要掃描整表，許多過程同時競爭同一數(shù)據(jù)，反復(fù)用相同行計算相同的結(jié)果，過程從多表獲取數(shù)據(jù)時引發(fā)大量的連接*作，當(dāng)數(shù)據(jù)來源于多表時的連接*作；這都消耗了磁盤I/O和CPU時間。
尤其在遇到下列情形時，我們要對基本表進行擴展設(shè)計：許多過程要頻繁訪問一個表、子集數(shù)據(jù)訪問、重復(fù)計算和冗余數(shù)據(jù)，有時用戶要求一些過程優(yōu)先或低的響應(yīng)時間。
如何避免這些不利因素呢？根據(jù)訪問的頻繁程度對相關(guān)表進行分割處理、存儲冗余數(shù)據(jù)、存儲衍生列、合并相關(guān)表處理，這些都是克服這些不利因素和優(yōu)化系統(tǒng)運行的有效途徑。
3.1 分割表或儲存冗余數(shù)據(jù)
分割表分為水平分割表和垂直分割表兩種。分割表增加了維護數(shù)據(jù)完整性的代價。
水平分割表：一種是當(dāng)多個過程頻繁訪問數(shù)據(jù)表的不同行時，水平分割表，并消除新表中的冗余數(shù)據(jù)列；若個別過程要訪問整個數(shù)據(jù)，則要用連接*作，這也無妨分割表；典型案例是電信話單按月分割存放。另一種是當(dāng)主要過程要重復(fù)訪問部分行時，更好將被重復(fù)訪問的這些行單獨形成子集表（冗余儲存），這在不考慮磁盤空間開銷時顯得十分重要；但在分割表以后，增加了維護難度，要用觸發(fā)器立即更新、或存儲過程或應(yīng)用代碼批量更新，這也會增加額外的磁盤I/O開銷。
垂直分割表（不破壞第三范式），一種是當(dāng)多個過程頻繁訪問表的不同列時，可將表垂直分成幾個表，減少磁盤I/O（每行的數(shù)據(jù)列少，每頁存的數(shù)據(jù)行就多，相應(yīng)占用的頁就少），更新時不必考慮鎖，沒有冗余數(shù)據(jù)。缺點是要在插入或刪除數(shù)據(jù)時要考慮數(shù)據(jù)的完整性，用存儲過程維護。另一種是當(dāng)主要過程反復(fù)訪問部分列時，更好將這部分被頻繁訪問的列數(shù)據(jù)單獨存為一個子集表（冗余儲存），這在不考慮磁盤空間開銷時顯得十分重要；但這增加了重疊列的維護難度，要用觸發(fā)器立即更新、或存儲過程或應(yīng)用代碼批量更新，這也會增加額外的磁盤I/O開銷。垂直分割表可以達到更大化利用Cache的目的。
總之，為主要過程分割表的方法適用于：各個過程需要表的不聯(lián)結(jié)的子集，各個過程需要表的子集，訪問頻率高的主要過程不需要整表。在主要的、頻繁訪問的主表需要表的子集而其它主要頻繁訪問的過程需要整表時則產(chǎn)生冗余子集表。
注意，在分割表以后，要考慮重新建立索引。
3.2 存儲衍生數(shù)據(jù)
對一些要做大量重復(fù)性計算的過程而言，若重復(fù)計算過程得到的結(jié)果相同（源列數(shù)據(jù)穩(wěn)定，因此計算結(jié)果也不變），或計算牽扯多行數(shù)據(jù)需額外的磁盤I/O開銷，或計算復(fù)雜需要大量的CPU時間，就考慮存儲計算結(jié)果（冗余儲存）?，F(xiàn)予以分類說明：
若在一行內(nèi)重復(fù)計算，就在表內(nèi)增加列存儲結(jié)果。但若參與計算的列被更新時，必須要用觸發(fā)器更新這個新列。
若對表按類進行重復(fù)計算，就增加新表（一般而言，存放類和結(jié)果兩列就可以了）存儲相關(guān)結(jié)果。但若參與計算的列被更新時，就必須要用觸發(fā)器立即更新、或存儲過程或應(yīng)用代碼批量更新這個新表。
若對多行進行重復(fù)性計算（如排名次），就在表內(nèi)增加列存儲結(jié)果。但若參與計算的列被更新時，必須要用觸發(fā)器或存儲過程更新這個新列。
總之，存儲冗余數(shù)據(jù)有利于加快訪問速度；但違反了第三范式，這會增加維護數(shù)據(jù)完整性的代價，必須用觸發(fā)器立即更新、或存儲過程或應(yīng)用代碼批量更新，以維護數(shù)據(jù)的完整性。
3.3 消除昂貴結(jié)合
對于頻繁同時訪問多表的一些主要過程，考慮在主表內(nèi)存儲冗余數(shù)據(jù)，即存儲冗余列或衍生列（它不依賴于主鍵），但破壞了第三范式，也增加了維護難度。在源表的相關(guān)列發(fā)生變化時，必須要用觸發(fā)器或存儲過程更新這個冗余列。當(dāng)主要過程總同時訪問兩個表時可以合并表，這樣可以減少磁盤I/O*作，但破壞了第三范式，也增加了維護難度。對父子表和1：1關(guān)系表合并方法不同：合并父子表后，產(chǎn)生冗余表；合并1：1關(guān)系表后，在表內(nèi)產(chǎn)生冗余數(shù)據(jù)。
4 數(shù)據(jù)庫對象的放置策略
數(shù)據(jù)庫對象的放置策略是均勻地把數(shù)據(jù)分布在系統(tǒng)的磁盤中，平衡I/O訪問，避免I/O瓶頸。
⑴ 訪問分散到不同的磁盤，即使用戶數(shù)據(jù)盡可能跨越多個設(shè)備，多個I/O運轉(zhuǎn)，避免I/O競爭，克服訪問瓶頸；分別放置隨機訪問和連續(xù)訪問數(shù)據(jù)。
⑵ 分離系統(tǒng)數(shù)據(jù)庫I/O和應(yīng)用數(shù)據(jù)庫I/O。把系統(tǒng)審計表和臨時庫表放在不忙的磁盤上。
⑶ 把事務(wù)日志放在單獨的磁盤上，減少磁盤I/O開銷，這還有利于在障礙后恢復(fù)，提高了系統(tǒng)的安全性。
⑷ 把頻繁訪問的“活性”表放在不同的磁盤上；把頻繁用的表、頻繁做Join*作的表分別放在單獨的磁盤上，甚至把把頻繁訪問的表的字段放在不同的磁盤上，把訪問分散到不同的磁盤上，避免I/O爭奪；
⑸ 利用段分離頻繁訪問的表及其索引（非聚族的）、分離文本和圖像數(shù)據(jù)。段的目的是平衡I/O，避免瓶頸，增加吞吐量，實現(xiàn)并行掃描，提高并發(fā)度，更大化磁盤的吞吐量。利用邏輯段功能，分別放置“活性”表及其非聚族索引以平衡I/O。當(dāng)然更好利用系統(tǒng)的默認(rèn)段。另外，利用段可以使備份和恢復(fù)數(shù)據(jù)更加靈活，使系統(tǒng)授權(quán)更加靈活。
利用合理建表、索引、存儲過程。
系統(tǒng)優(yōu)化，加內(nèi)存
●數(shù)據(jù)庫性能的優(yōu)化
一個數(shù)據(jù)庫系統(tǒng)的生命周期可以分成：設(shè)計、開發(fā)和成品三個階段。在設(shè)計階段進行數(shù)據(jù)庫性能優(yōu)化的成本更低，收益更大。在成品階段進行數(shù)據(jù)庫性能優(yōu)化的成本更高，收益最小。
數(shù)據(jù)庫的優(yōu)化通常可以通型沒過對網(wǎng)絡(luò)、硬件、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)和應(yīng)用程序的優(yōu)化來進行。最常見的優(yōu)化手段就是對硬件的歲友升級。根據(jù)統(tǒng)計，對網(wǎng)絡(luò)、硬件、操作系統(tǒng)、數(shù)據(jù)庫參數(shù)進行優(yōu)化所獲得的性能提升，全部加起來只占數(shù)據(jù)庫系統(tǒng)性能提升的40%左右，其余的60%系統(tǒng)性能乎租槐提升來自對應(yīng)用程序的優(yōu)化。許多優(yōu)化專家認(rèn)為，對應(yīng)用程序的優(yōu)化可以得到80%的系統(tǒng)性能的提升。
●應(yīng)用程序的優(yōu)化
應(yīng)用程序的優(yōu)化通?？煞譃閮蓚€方面：源代碼和SQL語句。由于涉及到對程序邏輯的改變，源代碼的優(yōu)化在時間成本和風(fēng)險上代價很高，而對數(shù)據(jù)庫系統(tǒng)性能的提升收效有限。
我陪你等，關(guān)注ing
mysql數(shù)據(jù)庫怎么優(yōu)化，有幾方面的優(yōu)化?
我列舉幾雹租彎個我熟悉的，
1，存儲引擎，根據(jù)應(yīng)用源悶選擇合適的引擎
2，索引 —-這個就有很多文章了，具體需要你自己去了解
3，sql語句優(yōu)化，查詢條件的選擇之類
4，mysql自身系統(tǒng)配置，需要針對應(yīng)用去定制
5，表的選擇，臨時表，或者分區(qū)型棗表，也需要針對應(yīng)用的情況去選擇使用
在鍵租謹(jǐn)開始演示之前，我們先介紹下兩個概念。
概念一，數(shù)據(jù)的可選擇性基數(shù)，也就是常說的cardinality值。
查詢優(yōu)化器在生成各種執(zhí)行計劃之前，得先從統(tǒng)計信息中取得相關(guān)數(shù)據(jù)，這樣才能估算每步操作所涉及到的記錄數(shù)，而這個相關(guān)數(shù)據(jù)就是cardinality。簡單來說，就是每個值在每個字段中的唯一值分布狀態(tài)。
比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數(shù)可以是100個，也可以是1個，當(dāng)然也可以是1到100之間的任何一個數(shù)字。這里唯一值越的多少，就是這個列的可選擇基數(shù)。
那看到這里我們就明白了，為什么要在基數(shù)高的字段上建立索引，而基數(shù)低的的字段建立索引反而沒有全表掃描來的快。當(dāng)然這個只是一方面，至于更深入的探討就不在我這篇探討的范圍了。
概念二，關(guān)于HINT的使用。
這里我來說下HINT是什么，在什么時候用。
HINT簡單來說就是在某些特定的場景下人工協(xié)助MySQL優(yōu)化器的工作，使她生成更優(yōu)的執(zhí)行計劃。一般來說，優(yōu)化器的執(zhí)行計劃都是更優(yōu)化的，不過在某些特定場景下，執(zhí)行計劃可能不是更優(yōu)化。
比如：表t1經(jīng)過大稿基量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經(jīng)很不準(zhǔn)確了，這時候剛好執(zhí)行了一條SQL，那么有可能這條SQL的執(zhí)行計劃就不是更優(yōu)的。為什么說有可能呢？
來看下具體演示
譬如，以下兩條SQL，
A：
select * from t1 where f1 = 20;
B：
select * from t1 where f1 = 30;
如果f1的值剛好頻繁更新的值為30，并且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設(shè)置了手動更新又或者用戶減少了sample page等等，那么對這兩條語句來說，可能不準(zhǔn)確的就是B了。
這里順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。
那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。
示例表結(jié)構(gòu)：
mysql> desc t1;+++——+—–++–+| Field | Type| Null | Key | Default | Extra|+++——+—–++–+| id| int(11) | NO | PRI | NULL | auto_increment || rank| int(11) | YES | MUL | NULL | || rank| int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc| text| YES | | NULL | || rank| int(11) | YES | MUL | NULL | 型啟|+++——+—–++–+7 rows in set (0.00 sec)
表記錄數(shù)：
mysql> select count(*) from t1;++| count(*) |++||++1 row in set (0.01 sec)
這里我們兩條經(jīng)典的SQL：
SQL C：
select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;
SQL D：
select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;
表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。
那我們來看SQL C的查詢計劃。
顯然，沒有用到任何索引，掃描的行數(shù)為32023，cost為3243.65。
mysql> explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “3243.65” }, “table”: { “table_name”: “t1”, “access_type”: “ALL”, “possible_keys”: , “rows_examined_per_scan”: 32023, “rows_produced_per_join”: 115, “filtered”: “0.36”, “cost_info”: {“read_cost”: “3232.07”,”eval_cost”: “11.58”,”prefix_cost”: “3243.65”,”data_read_per_join”: “49K” }, “used_columns”: , “attached_condition”: “((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))” } }}1 row in set, 1 warning (0.00 sec)
我們加上hint給相同的查詢，再次看看查詢計劃。
這個時候用到了index_merge,union了三個列。掃描的行數(shù)為1103，cost為441.09，明顯比之前的快了好幾倍。
mysql> explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “441.09” }, “table”: { “table_name”: “t1”, “access_type”: “index_merge”, “possible_keys”: , “key”: “union(idx_rank1,idx_rank2,idx_rank3)”, “key_length”: “5,5,5”, “rows_examined_per_scan”: 1103, “rows_produced_per_join”: 1103, “filtered”: “100.00”, “cost_info”: {“read_cost”: “330.79”,”eval_cost”: “110.30”,”prefix_cost”: “441.09”,”data_read_per_join”: “473K” }, “used_columns”: , “attached_condition”: “((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))” } }}1 row in set, 1 warning (0.00 sec)
我們再看下SQL D的計劃：
不加HINT，
mysql> explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “534.34” }, “table”: { “table_name”: “t1”, “access_type”: “ref”, “possible_keys”: , “key”: “idx_rank1”, “used_key_parts”: , “key_length”: “5”, “ref”: , “rows_examined_per_scan”: 555, “rows_produced_per_join”: 0, “filtered”: “0.07”, “cost_info”: {“read_cost”: “478.84”,”eval_cost”: “0.04”,”prefix_cost”: “534.34”,”data_read_per_join”: “176” }, “used_columns”: , “attached_condition”: “((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))” } }}1 row in set, 1 warning (0.00 sec)
加了HINT，
mysql> explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { “query_block”: { “select_id”: 1, “cost_info”: { “query_cost”: “5.23” }, “table”: { “table_name”: “t1”, “access_type”: “index_merge”, “possible_keys”: , “key”: “intersect(idx_rank1,idx_rank2,idx_rank3)”, “key_length”: “5,5,5”, “rows_examined_per_scan”: 1, “rows_produced_per_join”: 1, “filtered”: “100.00”, “cost_info”: {“read_cost”: “5.13”,”eval_cost”: “0.10”,”prefix_cost”: “5.23”,”data_read_per_join”: “440” }, “used_columns”: , “attached_condition”: “((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))” } }}1 row in set, 1 warning (0.00 sec)
對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。
數(shù)據(jù)庫優(yōu)化是什么意思？
數(shù)據(jù)庫優(yōu)化的意思 是減少代碼的冗余 提高重用性
數(shù)據(jù)庫優(yōu)化，首先最初硬件方面就可以優(yōu)化硬盤IO，內(nèi)存分配，就是安裝時候調(diào)整的一系列操作系統(tǒng)級的內(nèi)核參數(shù)，之后就是數(shù)據(jù)庫架構(gòu)上的優(yōu)化運渣了大并，邏輯、數(shù)據(jù)結(jié)構(gòu)等等，最后就是代碼上滾悄跡的優(yōu)化。當(dāng)然優(yōu)化是一個長期的工作，沒有更優(yōu)只有更優(yōu)。
MRR 是 MySQL 針對特定查詢的一種優(yōu)化手段。假設(shè)一個查詢有二級索引可用，讀完二級索引后要回表才能查到那些不在當(dāng)前二級索引上的列值，由于二級索引上引用的森仿腔主鍵值不一定是有序的，因此就有可能造成此衫大量的隨機 IO，如果回表前把主鍵值給它排一下序，那么在回表的時候就可以用順序 IO 取代原本的隨機 IO。
如果想關(guān)閉 MRR 優(yōu)化的話，就要把優(yōu)化器開關(guān) mrr 設(shè)置為 off。
默大攜認(rèn)只有在優(yōu)化器認(rèn)為 MRR 可以帶來優(yōu)化的情況下才會走 MRR，如果你想不管什么時候能走 MRR 的都走 MRR 的話，你要把 mrr_cost_based 設(shè)置為 off，不過更好不要這么干，因為這確實是一個坑，MRR 不一定什么時候都好，全表掃描有時候會更加快，如果在這種場景下走 MRR 就完成了。
MRR 要把主鍵排個序，這樣之后對磁盤的操作就是由順序讀代替之前的隨機讀。從資源的使用情況上來看就是讓 CPU 和內(nèi)存多做點事，來換磁盤的順序讀。然而排序是需要內(nèi)存的，這塊內(nèi)存的大小就由參數(shù) read_rnd_buffer_size 來控制。
數(shù)據(jù)庫優(yōu)化包括的介紹就聊到這里吧，感謝你花時間閱讀本站內(nèi)容，更多關(guān)于數(shù)據(jù)庫優(yōu)化包括,提升性能、空間、安全性：數(shù)據(jù)庫優(yōu)化全方位解析,誰知道數(shù)據(jù)庫優(yōu)化設(shè)計方案有哪些？,mysql數(shù)據(jù)庫怎么優(yōu)化，有幾方面的優(yōu)化?,數(shù)據(jù)庫優(yōu)化是什么意思？的信息別忘了在本站進行查找喔。
香港服務(wù)器選創(chuàng)新互聯(lián)，2H2G首月10元開通。
創(chuàng)新互聯(lián)（www.cdcxhl.com）互聯(lián)網(wǎng)服務(wù)提供商,擁有超過10年的服務(wù)器租用、服務(wù)器托管、云服務(wù)器、虛擬主機、網(wǎng)站系統(tǒng)開發(fā)經(jīng)驗。專業(yè)提供云主機、虛擬主機、域名注冊、VPS主機、云服務(wù)器、香港云服務(wù)器、免備案服務(wù)器等。
分享文章：提升性能、空間、安全性：數(shù)據(jù)庫優(yōu)化全方位解析(數(shù)據(jù)庫優(yōu)化包括)
文章鏈接：http://www.5511xx.com/article/dhijeoj.html
