數(shù)據(jù)庫是一組數(shù)據(jù)，其排列方式易于檢索、管理和更新。數(shù)據(jù)存儲在行、列和表中。它被編入索引以更快地檢索所需信息。首先我們簡要討論基礎(chǔ)知識，然后我們將繼續(xù)討論這個常見問題——如何優(yōu)化數(shù)據(jù)庫響應(yīng)時間？

數(shù)據(jù)庫類型

主要有四種結(jié)構(gòu)的數(shù)據(jù)庫管理系統(tǒng)：

• 分層數(shù)據(jù)庫
• 網(wǎng)絡(luò)數(shù)據(jù)庫
• 關(guān)系型數(shù)據(jù)庫
• 面向?qū)ο蟮臄?shù)據(jù)庫

最近，NoSQL 和面向?qū)ο蟮臄?shù)據(jù)庫已經(jīng)發(fā)展到用于存儲數(shù)據(jù)。NoSQL 數(shù)據(jù)庫不遵循行/列/表方法。NoSQL 數(shù)據(jù)庫是基于文檔的，包括鍵值對的集合，圖形數(shù)據(jù)庫沒有 RDBMS 中的傳統(tǒng)模式。NoSQL 數(shù)據(jù)庫非常適合處理大量分布式數(shù)據(jù)。只有有限的外部專家可以協(xié)助設(shè)置和部署大規(guī)模 NoSQL 部署。

關(guān)系數(shù)據(jù)庫是最常用的數(shù)據(jù)庫系統(tǒng)。SQL Server、Oracle 數(shù)據(jù)庫、Sybase 和 MySql 是關(guān)系數(shù)據(jù)庫的一些示例。RDBMS 允許多個用戶同時訪問數(shù)據(jù)，而不會影響安全因素。RDBMS 中的表的工作方式類似于電子表格。一組表被稱為模式。許多模式組合在一起構(gòu)成了一個數(shù)據(jù)庫。單個服務(wù)器可以包含多個數(shù)據(jù)庫。

現(xiàn)在讓我們討論如何優(yōu)化數(shù)據(jù)庫響應(yīng)時間

在設(shè)計數(shù)據(jù)庫之前和編寫 SQL 腳本以優(yōu)化數(shù)據(jù)庫響應(yīng)之前，必須考慮下面提到的技巧。

1. 數(shù)據(jù)庫統(tǒng)計信息：?SQL 優(yōu)化器必須非常了解每個目錄中不同表中包含的統(tǒng)計信息。有關(guān)索引及其分布的詳細信息稱為統(tǒng)計信息。使用這些細節(jié)，優(yōu)化器決定滿足查詢的最佳路徑。過時或丟失的統(tǒng)計詳細信息將導(dǎo)致優(yōu)化器采用非最佳路徑，從而導(dǎo)致響應(yīng)時間增加。考慮以下示例查詢：

select *

from Customer

where city = “Pune”

and phone =213-345-346;

這里的 Customer 是表名。

在上面的查詢中，Where 子句有兩個字段。定義了兩個索引，每個索引包含一個字段。優(yōu)化器每個表只能使用一個索引。在這種情況下，如果優(yōu)化器使用 phone 字段作為返回最少行數(shù)的索引，查詢將運行得更快。所以，如果數(shù)據(jù)庫沒有正確更新，如果沒有選擇正確的字段作為索引，響應(yīng)時間將會大大增加。

2. 構(gòu)建優(yōu)化索引：索引對于構(gòu)建任何查詢都非常重要。但是，許多索引會降低（插入、更新或刪除）查詢的速度。有必要保持表上索引的適當平衡。表中包含的字段及其順序也起著至關(guān)重要的作用。

(a) 復(fù)合索引：當一個索引包含多個字段時，稱為復(fù)合索引。當執(zhí)行在 where 子句中包含多個字段的查詢時，會構(gòu)建復(fù)合索引。在這種情況下，與單獨的第一個字段相比，所有字段組合在一起將顯著生成最少的行數(shù)。

(b) 聚集索引： 表中數(shù)據(jù)排列的物理順序稱為聚集索引。這意味著實際數(shù)據(jù)是根據(jù)索引字段排序的。示例：其中數(shù)據(jù)按某人的姓氏排列的電話簿。每個表只能有一個聚集索引。聚集索引通常用于經(jīng)常在列中搜索一系列值的情況。

3. 避免在運算符的 RHS 上使用函數(shù)：?函數(shù)和方法經(jīng)常用于SQL 查詢。考慮下面的例子：

選擇 *

from Customer

where YEAR(Sample CreatedOn) == 2009

AND Month(SampleCreatedON) = 6;

在上面的查詢中，SampleCreatedOn 有一個索引。但是， where 子句以這樣的方式更改，無法使用該索引。以下面的方式修改查詢會極大地增加響應(yīng)時間。

select * from Customer where SampleCreatedOn between '6/1/2009' and '6/30/2009';

4. 指定索引的預(yù)期增長：?最小化索引負面影響的一種方法是在創(chuàng)建索引時為預(yù)期增長指定適當?shù)闹怠?/p>

索引列的數(shù)據(jù)通常存儲在磁盤上。如果表中包含新行或修改索引列中的現(xiàn)有值，數(shù)據(jù)庫必須重新組織數(shù)據(jù)存儲以累積新行。這種重組會影響查詢的響應(yīng)時間。在這種情況下，如果定期合并新行，則可以預(yù)先指定索引的預(yù)期增長。在 MS SQL Server 中，用于表示預(yù)期增長的術(shù)語是 FILL FACTOR，而對于 Oracle 和 DB2，它是 PCTFREE，意思是免費百分比。

5. 在 SELECT 中包含優(yōu)化器提示：?在選擇查詢中提及索引名稱始終是一個好習(xí)慣。考慮下面的例子。

select * from Customer

with (Index(IdxPhone))

where city = “Pune” AND phone = '213-345-346';

FROM 后面的附加 WITH 子句指示索引名稱將有助于增加查詢的響應(yīng)時間。上面的示例特定于 MS SQL SERVER。

6、EXPLAIN的使用：?優(yōu)化器創(chuàng)建的SELECT語句的執(zhí)行計劃，多數(shù)情況下是由數(shù)據(jù)庫返回的。這種執(zhí)行對微調(diào) SQL 查詢有很大幫助。

執(zhí)行計劃的 SQL 語法是 Set SHOWPLAN_ALL ON>Query<。WINSQL Professional等工具也可用于運行 EXPLAIN 命令。

7. 避免外鍵約束：?使用外鍵約束可確保數(shù)據(jù)完整性，但以性能為代價。如果優(yōu)化響應(yīng)時間是主要目標，則可以避免外鍵約束。例如： RDBMS 中的 System 表包含有關(guān)用戶數(shù)據(jù)庫的元數(shù)據(jù)信息。此處包含的表包含關(guān)系但沒有外鍵。此處的客戶端強制執(zhí)行這些規(guī)則以優(yōu)化數(shù)據(jù)庫響應(yīng)時間。

8、多塊硬盤的使用：?隨著數(shù)據(jù)庫容量的增加，硬盤上的I/O操作明顯變慢。可以將數(shù)據(jù)庫拆分到多個物理硬盤驅(qū)動器以加快 I/O。也可以將一個表的內(nèi)容拆分到多個磁盤中。隨著更多磁頭并行檢索數(shù)據(jù)，使用多個磁盤可提高 I/O 操作的速度

9. 選擇最少數(shù)據(jù)：?檢索到的最少數(shù)據(jù)，查詢運行得越快。過濾盡量在服務(wù)端進行，而不是在客戶端進行過濾。這將導(dǎo)致處理的數(shù)據(jù)最少，并提供更快的結(jié)果。消除任何計算列或不必要的列。考慮下面的例子：

選擇名字、姓氏、城市，其中城市 =“浦那”；

在上面的示例中，可以避免城市列，因為它始終是 Pune。盡管它不會產(chǎn)生很大的影響，但它可以改善大型數(shù)據(jù)集的響應(yīng)時間。

10. 加載數(shù)據(jù)前刪除索引。

可以在加載大量數(shù)據(jù)之前刪除表上的索引。這允許插入語句更快地工作。插入完成后，可以再次重新創(chuàng)建索引。

對于需要插入數(shù)千行的在線系統(tǒng)，可以使用臨時表來加載數(shù)據(jù)。這個臨時表不應(yīng)該有任何索引。將數(shù)據(jù)從一個表傳輸?shù)搅硪粋€表總是比從外部源加載快得多。刪除主表上的索引，將數(shù)據(jù)從臨時表傳輸?shù)阶罱K表，最后再次創(chuàng)建索引。

優(yōu)化響應(yīng)時間的更多技巧

• 避免使用 Group By、Order By 和 Distinct： 必須盡可能避免使用 Group By、Order By 和 Distinct。使用上述子句時，SQL Server 會創(chuàng)建一個臨時表并將數(shù)據(jù)加載到該表中。根據(jù)查詢的請求在此臨時表中處理數(shù)據(jù)并檢索最終結(jié)果。因此，在絕對必要時，始終建議在查詢中使用 Group By、Order By 和 Distinct。
• Use Set NOCOUNT ON： Set NOCOUNT ON 必須用于 DML 操作（插入、更新、刪除和選擇）。SQL server 默認計算并返回受影響的行數(shù)。小型查詢不會受到太大影響，但是對于具有大量連接的大型復(fù)雜查詢，這會導(dǎo)致很大的性能問題。設(shè)置 NOCOUNT ON 肯定會增加響應(yīng)時間，因為它不會計算受影響的行數(shù)。
• 包括所有者/架構(gòu)名稱： 表的對象名稱，存儲過程必須與所有者/架構(gòu)名稱一起作為前綴。如果省略所有者/架構(gòu)名稱，則服務(wù)器將在所有架構(gòu)中搜索該對象，直到找到該對象。指定所有者/模式指示服務(wù)器僅在該特定模式中搜索表。
• 可空列： 在與可空列進行比較時，避免使用 NOT IN 并使用 NOT EXISTS。當在查詢中使用 NOT IN 時，SQL Server 將檢查每個結(jié)果（即使沒有包含空值的行）以確定它是否為空。NOT EXISTS 不執(zhí)行與空值的比較檢查。
• 調(diào)整內(nèi)部變量： 微調(diào)一些默認設(shè)置有助于加快響應(yīng)時間和提高性能。

更改索引緩沖區(qū)大小 (key_buffer)

在管理表索引（讀取和寫入操作）時，可以借助此變量控制緩沖區(qū)大小。該變量的值可以增加到系統(tǒng)總內(nèi)存的 25% 左右。要縮短響應(yīng)時間，請嘗試為此變量使用不同的值。

更改表緩沖區(qū)大小 (read_buffer_size)

在查詢需要按順序檢查表的情況下，MySQL 為該查詢提供內(nèi)存緩沖區(qū)。緩沖區(qū)大小由 read_buffer_size 變量控制。如果順序掃描需要更多時間，則可以通過增加此值的大小以及內(nèi)存緩沖區(qū)的大小來提高性能。

設(shè)置最大打開表數(shù)（table_cache）

在任何特定時間可以打開的最大表數(shù)由 table_cache_variable 管理。此變量類似于 max_connections 變量。增加此變量的值允許大量表保持打開狀態(tài)。如果服務(wù)器收到對各種數(shù)據(jù)庫和表的查詢，則可以更改 table_cache 值。

為長查詢指定時間限制 (long_query_time)

使用 long_query_time 變量允許跟蹤低效或行為不當?shù)牟樵儭＿@個變量對提高響應(yīng)時間和優(yōu)化性能有很大幫助。