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

數(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ā)展到用于存儲(chǔ)數(shù)據(jù)。NoSQL 數(shù)據(jù)庫不遵循行/列/表方法。NoSQL 數(shù)據(jù)庫是基于文檔的，包括鍵值對(duì)的集合，圖形數(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 允許多個(gè)用戶同時(shí)訪問數(shù)據(jù)，而不會(huì)影響安全因素。RDBMS 中的表的工作方式類似于電子表格。一組表被稱為模式。許多模式組合在一起構(gòu)成了一個(gè)數(shù)據(jù)庫。單個(gè)服務(wù)器可以包含多個(gè)數(shù)據(jù)庫。

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

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

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

select *

from Customer

where city = “Pune”

and phone =213-345-346;

這里的 Customer 是表名。

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

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

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

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

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

選擇 *

from Customer

where YEAR(Sample CreatedOn) == 2009

AND Month(SampleCreatedON) = 6;

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

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

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

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

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

select * from Customer

with (Index(IdxPhone))

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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