SQL 資料庫調優

---

---

SQL 資料庫調優

SQL 資料庫調優是一組用於最佳化資料庫並防止其成為瓶頸的活動。

有多種技術可以配置特定資料庫的最佳效能。資料庫調優與查詢調優重疊；因此，良好的索引和避免不正確的查詢有助於提高資料庫效率。此外，增加儲存空間、更新到最新的資料庫版本以及投資更強大的 CPU（如果需要）也是一些常用的技術。

資料庫調優技術

我們可以實現以下技術來最佳化資料庫的效能：

資料庫規範化

規範化是從資料庫中刪除重複資料的過程。我們可以透過將更大的表分解成更小的相關表來規範化資料庫。這提高了資料庫的效能，因為它需要更少的時間從小的表中檢索資料，而不是從一個大的表中檢索資料。

合適的索引

在 SQL 中，索引是指向資料庫中特定資料位置的指標（記憶體地址）。我們在資料庫中使用索引來減少查詢時間，因為資料庫引擎可以使用其索引跳轉到特定記錄的位置，而不是掃描整個資料庫。

避免不正確的查詢

選擇正確的查詢以高效地檢索資料也可以提高資料庫的效能。例如，當我們只需要單個列中的資料時，選擇檢索整個表會不必要地增加查詢時間。因此，要明智地查詢資料庫。

讓我們討論一些常見的錯誤查詢以及如何糾正它們以最佳化資料庫效能。

1. 使用 SELECT *fields* 代替 SELECT (*)

在大型資料庫中，我們應該始終只從資料庫中檢索所需的列，而不是檢索所有列，即使不需要它們也是如此。我們可以透過在 SELECT 語句中指定列名而不是使用 SELECT (*) 語句輕鬆地做到這一點。

示例

假設我們在 MySQL 資料庫中使用 CREATE TABLE 語句建立了一個名為 CUSTOMERS 的表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

以下查詢使用 INSERT 語句將值插入此表：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

假設我們只需要 CUSTOMERS 表的 ID、NAME 和 SALARY 列中的資料。因此，我們應該只在 SELECT 語句中指定這三列，如下所示：

SELECT ID, NAME, SALARY FROM CUSTOMERS;

輸出

獲得的輸出如下所示：

2. 使用萬用字元

萬用字元 (%) 是我們用來根據模式搜尋資料的字元。這些與索引配對的萬用字元只會提高效能，因為資料庫可以快速找到與模式匹配的資料。

示例

如果我們想從 CUSTOMERS 表中檢索所有以 K 開頭的客戶的姓名，則以下查詢將提供最快的結果：

SELECT ID, NAME FROM CUSTOMERS WHERE NAME LIKE 'K%';

輸出

以下是上述查詢的輸出：

3. 使用顯式連線

SQL JOIN 用於基於公共列組合兩個表。建立 JOIN 有兩種方法：隱式連線和顯式連線。顯式連線表示法使用 JOIN 關鍵字和 ON 子句連線兩個表，而隱式連線表示法不使用 JOIN 關鍵字，並與 WHERE 子句一起使用。

從效能方面來看，它們都在同一水平上。但是，在更復雜的情況下，隱式連線表示法可能會產生與預期完全不同的結果。因此，首選顯式連線。

4. 避免使用 SELECT DISTINCT

SQL 中的 DISTINCT 運算子用於從資料庫中檢索唯一記錄。在具有唯一索引的正確設計的資料庫表上，我們很少使用它。

但是，如果我們仍然必須在表上使用它，則使用 GROUP BY 子句代替 DISTINCT 關鍵字會顯示更好的查詢效能（至少在某些資料庫中）。

5. 避免使用多個 OR

OR 運算子用於在篩選資料庫時組合多個條件。每當我們在篩選條件中使用 OR 時，每個語句都會單獨處理。這會降低資料庫效能，因為必須多次掃描整個表才能檢索與篩選條件匹配的資料。

相反，我們可以使用更最佳化的解決方案；透過將不同的 OR 條件分解成單獨的查詢，這些查詢可以由資料庫並行處理。然後，可以使用 UNION 組合這些查詢的結果。

示例

例如，假設我們需要獲取所有年齡大於 25 歲或工資大於 2,000 的客戶的詳細資訊。最佳化的查詢將如下所示：

SELECT ID, NAME FROM CUSTOMERS WHERE AGE > 25 
UNION
SELECT ID, NAME FROM CUSTOMERS WHERE SALARY > 2000;

輸出

執行上述程式碼後，我們將得到以下輸出：

6. 使用 WHERE 代替 HAVING

WHERE 和 HAVING 子句都用於篩選 SQL 中的資料。但是，WHERE 子句比 HAVING 子句更有效。使用 WHERE 子句，只檢索與條件匹配的記錄。但對於 HAVING 子句，它首先檢索所有記錄，然後根據條件篩選它們。因此，WHERE 子句更可取。

資料庫碎片整理

當資料儲存在資料庫中時，它們被放置在連續的物理位置。在這種情況下，邏輯位置和物理位置順序相同。

但是，當透過刪除或更新記錄來更改資料庫表時，索引也會更改以適應所做的更改。這將導致索引分散在儲存中。物理位置也失去了連續分配。從而降低資料庫效能。

碎片整理是解決此問題的方案。它將重新組織/重建索引的邏輯順序以匹配物理順序。但是，此過程首先會分析索引並選擇它們是否只需要重新組織或完全重建。

內建調優工具

某些資料庫提供內建的調優工具來監控資料庫效能。例如，Oracle資料庫提供以下調優工具：

• EXPLAIN − 在SQL中，EXPLAIN命令顯示查詢執行的順序以及每個步驟的估計成本。我們可以用它來找到成本最低的查詢，從而最佳化資料庫。

• tkprof − tkprof是一個命令，它提供各種統計資訊，例如查詢的CPU和I/O使用情況。透過使用這些統計資訊，我們可以調整查詢以減少CPU和I/O利用率，從而提高資料庫效率。