隨記

筆記

索引 (Index)

索引是資料庫中用於加速查詢的一種資料結構。SQL Server 提供了兩種主要的索引類型：聚集索引 (Clustered Index) 和 非聚集索引 (Non-Clustered Index)。

聚集索引 (Clustered Index)

定義：
聚集索引會改變資料表中資料的物理存儲順序，使資料按照索引鍵的順序存儲。
每個資料表最多只能有一個聚集索引，因為資料只能按照一種順序存儲。
特點：
資料表的資料行會直接存儲在索引的葉節點中。
適合用於篩選範圍查詢（例如 BETWEEN、<、>）。
聚集索引的鍵值應該是唯一的，否則 SQL Server 會自動添加一個唯一標識符。
優點：
提升範圍查詢的效能。
減少 I/O 操作，因為資料直接存儲在索引中。
缺點：
插入、更新和刪除操作可能會因為需要維護索引的順序而變慢。
需要更多的磁碟空間來存儲索引。

非聚集索引 (Non-Clustered Index)

定義：
非聚集索引不改變資料表中資料的物理存儲順序，而是建立一個獨立的資料結構來存儲索引鍵和指向資料行的指標。
每個資料表可以有多個非聚集索引。
特點：
索引的葉節點存儲的是指向資料行的指標，而不是資料本身。
適合用於精確查詢（例如 WHERE 條件中的等值查詢）。
優點：
支援多個索引，適合多種查詢模式。
不會改變資料的物理存儲順序。
缺點：
查詢時需要額外的查找步驟（回表操作），可能會增加 I/O 操作。
插入、更新和刪除操作可能會因為需要維護多個索引而變慢。

聚集索引與非聚集索引的比較

特性	聚集索引 (Clustered Index)	非聚集索引 (Non-Clustered Index)
資料存儲順序	改變資料的物理存儲順序	不改變資料的物理存儲順序
每表數量限制	每表最多一個	每表可以有多個
適用場景	範圍查詢	精確查詢
查詢效能	範圍查詢效能更高	精確查詢效能更高
插入/更新/刪除效能	可能較慢	可能較慢

索引的最佳實踐

選擇合適的索引鍵：
索引鍵應該是選擇性高的欄位，避免使用重複值多的欄位。
限制索引數量：
過多的索引會增加維護成本，影響插入、更新和刪除操作的效能。
定期重建索引：
使用 ALTER INDEX REBUILD 或 ALTER INDEX REORGANIZE 來維護索引的效能。
使用覆蓋索引：
在非聚集索引中包含查詢所需的所有欄位，避免回表操作。
監控索引效能：
使用 SQL Server 的動態管理檢視（DMV）來分析索引的使用情況，例如 sys.dm_db_index_usage_stats。

透過正確設計和維護索引，可以顯著提升資料庫的查詢效能。

Index Seek 與 Index Scan

在資料庫查詢中，索引的使用方式主要分為兩種：Index Seek 和 Index Scan。了解這兩者的差異有助於優化查詢效能。

Index Seek

定義：
Index Seek 是指資料庫引擎利用索引快速定位到符合條件的資料列。
它只會掃描索引中相關的部分，而不是整個索引。
特點：
效能較高，因為只讀取必要的資料。
適用於高選擇性的查詢條件，例如 WHERE 子句中使用等值條件或範圍條件（如 =、BETWEEN）。
優點：
減少 I/O 操作，查詢速度更快。
適合用於大資料表的精確查詢。
範例：

SELECT *
FROM Orders
WHERE OrderID = 12345;

在上述查詢中，如果 OrderID 欄位有索引，資料庫會使用 Index Seek。

Index Scan

定義：
Index Scan 是指資料庫引擎掃描整個索引來查找符合條件的資料列。
當查詢條件的選擇性較低或沒有適合的索引時，會使用 Index Scan。
特點：
效能較低，因為需要掃描整個索引。
適用於低選擇性的查詢條件，例如 WHERE 子句中使用模糊查詢（如 LIKE '%value%'）。
缺點：
增加 I/O 操作，查詢速度較慢。
當資料表或索引很大時，可能會對效能造成顯著影響。
範例：

SELECT *
FROM Orders
WHERE CustomerName LIKE '%Smith%';

在上述查詢中，如果 CustomerName 欄位有索引，但查詢條件使用了模糊匹配，資料庫可能會使用 Index Scan。

Index Seek 與 Index Scan 的比較

特性	Index Seek	Index Scan
掃描範圍	部分索引	整個索引
效能	高效	效能較低
適用場景	高選擇性查詢	低選擇性查詢
I/O 操作	較少	較多

最佳化建議

建立適當的索引：
確保查詢條件中的欄位有適合的索引。
避免低選擇性的查詢條件：
儘量避免使用模糊匹配（如 LIKE '%value%'）。
使用查詢分析工具：
使用 EXPLAIN 或類似工具檢查查詢計劃，確保索引被正確使用。
定期重建索引：
維護索引的效能，避免碎片化影響查詢速度。

透過理解 Index Seek 和 Index Scan 的差異，可以更有效地設計索引並優化查詢效能。

如何找出索引過多的資料表

在 SQL Server 中，可以使用動態管理檢視 (DMV) 來找出索引過多的資料表。以下是步驟：

使用 sys.indexes 和 sys.dm_db_index_usage_stats
這些檢視可以幫助你分析索引的使用情況，並找出哪些索引很少被使用或從未被使用。
查詢範例
以下是 SQL 查詢範例，用於找出索引過多的資料表：

SELECT 
    t.name AS TableName,
    COUNT(i.index_id) AS IndexCount
FROM 
    sys.tables t
INNER JOIN 
    sys.indexes i ON t.object_id = i.object_id
WHERE 
    i.type IN (1, 2) -- 1: Clustered Index, 2: Non-Clustered Index
GROUP BY 
    t.name
HAVING 
    COUNT(i.index_id) > 5 -- 假設超過 5 個索引視為過多
ORDER BY 
    IndexCount DESC;

分析未使用的索引
找出未被使用的索引，這些索引可能是多餘的：

SELECT 
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    u.user_seeks, 
    u.user_scans, 
    u.user_lookups, 
    u.user_updates
FROM 
    sys.indexes i
LEFT JOIN 
    sys.dm_db_index_usage_stats u 
    ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE 
    OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
    AND i.type IN (1, 2) -- 1: Clustered Index, 2: Non-Clustered Index
    AND (u.user_seeks IS NULL AND u.user_scans IS NULL AND u.user_lookups IS NULL)
ORDER BY 
    TableName, IndexName;

最佳化建議
刪除未使用的索引。
合併功能重疊的索引。
減少不必要的索引數量，特別是對於頻繁進行插入、更新和刪除操作的資料表。

透過這些步驟，你可以有效地找出索引過多的資料表並進行優化。

資料庫正規化與優化

資料庫正規化

資料庫正規化是一種設計資料庫結構的方法，目的是減少資料冗餘並確保資料的一致性。正規化過程通常分為以下幾個階段：

第一正規化 (1NF)
確保每個欄位的值都是不可分割的原子值。
消除重複的欄位，將重複的資料分成多個表格。
第二正規化 (2NF)
滿足 1NF 的條件。
消除非主鍵欄位對主鍵的部分依賴。
將與主鍵無直接關係的欄位分離到其他表格中。
第三正規化 (3NF)
滿足 2NF 的條件。
消除非主鍵欄位之間的傳遞依賴。
確保每個非主鍵欄位只依賴於主鍵。
BCNF (Boyce-Codd Normal Form)
滿足 3NF 的條件。
確保每個函數依賴關係的左側都是候選鍵。
第四正規化 (4NF)
消除多值依賴。
確保每個多值依賴都被分解成獨立的表格。
第五正規化 (5NF)
消除連接依賴。
確保資料表無法再進一步分解而不會丟失資訊。

正規化的目的是提高資料的一致性和完整性，但過度正規化可能會導致查詢效能下降，因此需要在正規化和效能之間取得平衡。

資料庫優化

資料庫優化是為了提高資料庫的效能和可擴展性，常見的優化方法包括：

索引優化
建立適當的索引以加速查詢。
刪除未使用或重複的索引。
使用覆蓋索引來避免回表操作。
查詢優化
使用 EXPLAIN 或類似工具分析查詢計劃。
避免使用 SELECT *，只選取需要的欄位。
優化 WHERE 條件，使用索引友好的條件。
資料分區 (Partitioning)
將大型資料表分區以提高查詢效能。
使用水平分區或垂直分區根據需求分割資料。
快取機制
使用資料庫快取（如 Redis 或 Memcached）來減少資料庫負載。
啟用查詢快取功能（如果資料庫支援）。
連線池 (Connection Pooling)
使用連線池來減少建立和關閉資料庫連線的開銷。
資料庫參數調整
根據工作負載調整資料庫的緩衝區大小、快取大小等參數。
監控與分析
使用資料庫監控工具（如 SQL Server Profiler、pg_stat_statements）來分析效能瓶頸。
定期檢查慢查詢日誌並進行優化。
資料壓縮
使用資料壓縮技術來減少磁碟空間使用並提高 I/O 效能。
分散式資料庫
將資料分散到多個節點以提高可用性和效能。