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

數據庫類(lèi)型

主要有四種結構的數據庫管理系統：

• 分層數據庫
• 網(wǎng)絡(luò )數據庫
• 關(guān)系型數據庫
• 面向對象的數據庫

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

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

現在讓我們討論如何優(yōu)化數據庫響應時(shí)間

在設計數據庫之前和編寫(xiě) SQL 腳本以?xún)?yōu)化數據庫響應之前，必須考慮下面提到的技巧。

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

select *

from Customer

where city = “Pune”

and phone =213-345-346;

這里的 Customer 是表名。

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

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

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

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

3. 避免在運算符的 RHS 上使用函數：?函數和方法經(jīng)常用于SQL 查詢(xún)?？紤]下面的例子：

選擇 *

from Customer

where YEAR(Sample CreatedOn) == 2009

AND Month(SampleCreatedON) = 6;

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

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

4. 指定索引的預期增長(cháng)：?最小化索引負面影響的一種方法是在創(chuàng )建索引時(shí)為預期增長(cháng)指定適當的值。

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

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

select * from Customer

with (Index(IdxPhone))

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

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

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

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

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

8、多塊硬盤(pán)的使用：?隨著(zhù)數據庫容量的增加，硬盤(pán)上的I/O操作明顯變慢?？梢詫祿觳鸱值蕉鄠€(gè)物理硬盤(pán)驅動(dòng)器以加快 I/O。也可以將一個(gè)表的內容拆分到多個(gè)磁盤(pán)中。隨著(zhù)更多磁頭并行檢索數據，使用多個(gè)磁盤(pán)可提高 I/O 操作的速度

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

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

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

10. 加載數據前刪除索引。

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

對于需要插入數千行的在線(xiàn)系統，可以使用臨時(shí)表來(lái)加載數據。這個(gè)臨時(shí)表不應該有任何索引。將數據從一個(gè)表傳輸到另一個(gè)表總是比從外部源加載快得多。刪除主表上的索引，將數據從臨時(shí)表傳輸到最終表，最后再次創(chuàng )建索引。

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

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

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

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

更改表緩沖區大小 (read_buffer_size)

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

設置最大打開(kāi)表數（table_cache）

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

為長(cháng)查詢(xún)指定時(shí)間限制 (long_query_time)

使用 long_query_time 變量允許跟蹤低效或行為不當的查詢(xún)。這個(gè)變量對提高響應時(shí)間和優(yōu)化性能有很大幫助。