SQL索引與檢視最佳化

在資料函式庫效能調校中，索引和檢視扮演著關鍵角色。索引如同書籍目錄，能大幅加快資料查詢速度，尤其在大型資料表中效果顯著。檢視則可簡化複雜查詢，提升程式碼可讀性，並可作為資料存取的控制機制。本文將探討如何利用這些特性提升資料函式庫效能和管理效率。對於資料函式庫交易，我們也將探討其重要性以及如何在不同資料函式庫系統中實作，以確保資料操作的完整性和一致性。此外，文章也會詳細介紹各種 SQL 資料型別，包含數值、字串和日期時間等，並說明如何根據實際需求選擇最合適的型別，以兼顧儲存空間和資料處理效率。

索引與查詢最佳化

在處理大型資料表時，索引的建立對於查詢效能的提升至關重要。假設有一張包含一千萬筆資料的表格，當我們執行一個查詢以取得特定日期的紀錄時，如果沒有索引，資料函式庫將會逐一檢查每一筆資料，導致查詢時間大幅增加。

為何需要索引？

考慮以下查詢：

SELECT *
FROM my_table
WHERE log_date = '2021-01-01';

在沒有索引的情況下，資料函式庫需要檢查每一行資料是否符合條件，這將耗費大量時間。

內容解密：

1. 查詢過程：資料函式庫從第一行開始檢查，直到最後一行。
2. 效能問題：對於大資料量的表格，這種查詢方式極度耗時。

索引的工作原理

索引的工作原理類別似於書籍的目錄。當我們為某個欄位建立索引後，資料函式庫可以快速定位到符合條件的資料，而無需掃描整個表格。

比較書籍目錄與索引

建立索引

為 log_date 欄位建立索引的 SQL 陳述式如下：

CREATE INDEX my_index ON my_table (log_date);

內容解密：

1. CREATE INDEX：建立索引的語法。
2. my_index：索引的名稱。
3. ON my_table (log_date)：指定要建立索引的資料表和欄位。

不同資料函式庫系統對於建立索引的語法可能稍有不同，例如在 Oracle 中，需要將欄位名稱轉換為大寫並加上引號：

CREATE INDEX my_index ON my_table ('LOG_DATE');

內容解密：

1. Oracle 特性：需要將欄位名稱轉換為大寫並加上引號。
2. 自動建立索引：Oracle 會自動為主鍵和唯一約束的欄位建立索引。

多欄位索引

我們也可以為多個欄位建立組合索引，例如：

CREATE INDEX my_index ON my_table (log_date, team);

內容解密：

1. 欄位順序的重要性：查詢時必須包含索引中的第一個欄位（log_date），才能有效利用索引。
2. 部分欄位查詢：如果查詢只包含第二個欄位（team），索引將不會被使用。

索引的刪除

刪除索引的語法根據不同的資料函式庫系統而有所不同：

-- MySQL, SQL Server
DROP INDEX my_index ON my_table;

-- Oracle, PostgreSQL, SQLite
DROP INDEX my_index;

內容解密：

1. DROP INDEX：刪除索引的語法。
2. 不可逆操作：刪除索引後無法復原，需謹慎操作。
3. 資料完整性：刪除索引不會影響原始資料，只是影響查詢效率。

檢視（View）

檢視是一種虛擬的資料表，它根據一個或多個實際資料表的查詢結果。檢視本身不儲存資料，但可以像普通資料表一樣被查詢。

為何使用檢視？

1. 簡化複雜查詢：將複雜的查詢封裝成檢視，方便重複使用。
2. 許可權控制：透過檢視限制使用者對底層資料表的存取許可權。

建立檢視範例

假設有一個複雜的查詢，我們可以將其建立為檢視：

CREATE VIEW customer_view AS
SELECT customer_id, name, email
FROM customer
WHERE status = 'active';

內容解密：

1. CREATE VIEW：建立檢視的語法。
2. AS SELECT：定義檢視的查詢陳述式。
3. 許可權控制：使用者可以查詢 customer_view，但無法直接存取 customer 資料表。

圖表說明

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 圖表說明

rectangle "查詢" as node1
rectangle "直接存取" as node2

node1 --> node2

@enduml

此圖示說明瞭檢視如何作為原始資料表與應用程式之間的橋樑，同時限制了一般使用者的存取許可權。

圖表解說

1. 原始資料表：存放實際資料。
2. 檢視：根據查詢結果的虛擬資料表。
3. 應用程式：透過檢視進行資料存取。
4. 管理者：可以直接存取原始資料表。

資料函式庫交易與檢視表管理

在資料函式倉管理中，交易（Transaction）與檢視表（View）是兩個重要的概念，它們分別用於確保資料的一致性與簡化複雜查詢。

檢視表（View）的建立與管理

檢視表是一種虛擬表，其內容由查詢定義。與實體表不同，檢視表不儲存資料，而是根據查詢結果動態生成資料。

建立檢視表

使用 CREATE VIEW 陳述式可以建立檢視表。例如，若要統計每個擁有者（owner）擁有的瀑布（waterfall）數量，可以建立以下檢視表：

CREATE VIEW owner_waterfalls_vw AS
SELECT o.id, o.name, COUNT(w.id) AS num_waterfalls
FROM owner o LEFT JOIN waterfall w
ON o.id = w.owner_id
GROUP BY o.id, o.name;

查詢這個檢視表可以得到每個擁有者的瀑布數量：

SELECT * FROM owner_waterfalls_vw;

更新檢視表

不同的資料函式庫系統更新檢視表的方式有所不同。在 MySQL、Oracle 和 PostgreSQL 中，可以使用 CREATE OR REPLACE VIEW 陳述式：

CREATE OR REPLACE VIEW my_view AS
SELECT *
FROM my_table
WHERE country = 'CA';

在 SQL Server 中，則使用 CREATE OR ALTER VIEW：

CREATE OR ALTER VIEW my_view AS
SELECT *
FROM my_table
WHERE country = 'CA';

而在 SQLite 中，需要先刪除舊的檢視表再建立新的：

DROP VIEW IF EXISTS my_view;
CREATE VIEW my_view AS
SELECT * FROM my_table WHERE country = 'CA';

刪除檢視表

使用 DROP VIEW 陳述式可以刪除檢視表：

DROP VIEW my_view;

交易（Transaction）管理

交易是一系列操作的集合，這些操作要麼全部成功，要麼全部失敗，以確保資料的一致性。

為什麼使用交易

交易可以避免部分更新的問題。例如，在進行多個插入、更新或刪除操作時，如果其中一個操作失敗，交易可以回復（Rollback）所有操作，保持資料的一致性。

交易的基本操作

1. 開始交易：使用 START TRANSACTION 或 BEGIN TRANSACTION 陳述式開始一個新的交易。

   -- MySQL 和 PostgreSQL
   START TRANSACTION;
   或
   BEGIN;
   
   -- SQL Server 和 SQLite
   BEGIN TRANSACTION;
   

2. 執行操作：在交易中執行需要的 SQL 操作，如插入、更新或刪除。

   INSERT INTO page_views (user_id, page) VALUES (525, 'home');
   INSERT INTO page_views (user_id, page) VALUES (525, 'contact us');
   DELETE FROM new_users WHERE user_id = 525;
   UPDATE page_views SET page = 'request info' WHERE page = 'contact us';
   

3. 提交或回復：如果所有操作都正確，使用 COMMIT 提交交易；否則，使用 ROLLBACK 回復交易。

   -- 提交交易
   COMMIT;
   
   -- 回復交易
   ROLLBACK;
   

交易的實際應用場景

1. 雙重檢查變更：在進行重要變更前，使用交易可以預覽變更結果。如果結果正確，則提交交易；否則，回復交易。

2. 確保資料一致性：在進行多個相關操作時，交易可以確保這些操作要麼全部成功，要麼全部失敗，避免資料不一致的問題。

綜上所述，檢視表和交易是資料函式倉管理中非常重要的工具。檢視表簡化了複雜查詢，而交易則確保了資料的一致性和安全性。正確使用這兩個工具，可以大大提高資料函式倉管理的效率和資料的可靠性。

第 6 章：資料型別

在 SQL 資料表中，每個欄位（column）只能包含一種資料型別的數值。本章將討論常見的資料型別，以及何時應該使用它們。

以下 SQL 陳述式定義了一個名為 my_table 的資料表，包含三個欄位：id、name 和 dt，並分別指定了它們的資料型別：

CREATE TABLE my_table (
  id INT,
  name VARCHAR(30),
  dt DATE
);

INT、VARCHAR 和 DATE 只是 SQL 眾多資料型別中的三種。表格 6.1 列出了四類別常見的資料型別及其子類別。不同關聯式資料函式倉管理系統（RDBMS）對資料型別的語法有很大差異，我們將在本章的各個章節中詳細討論這些差異。

常見的資料型別

表格 6.1：SQL 中的資料型別

表格 6.2：SQL 中的常數

如何選擇資料型別

選擇欄位的資料型別時，需要在儲存空間和靈活性之間取得平衡。

表格 6.3：整數資料型別的範例

假設有一個欄位儲存了某個教室中的學生人數：

15
25
50
70
100

這個欄位包含的是整數數值。根據表格 6.3 中的整數資料型別，我們可以選擇以下三種資料型別之一：

• 使用 INT：如果儲存空間不是問題，那麼選擇 INT 是最簡單、最可靠的選擇。
• 使用 TINYINT：由於所有數值都在 0 至 255 之間，因此使用 TINYINT 可以節省儲存空間。
• 使用 SMALLINT：如果未來可能會在這個欄位中新增更大的數值，那麼選擇 SMALLINT 可以提供更大的靈活性，同時佔用的儲存空間比 INT 少。

沒有絕對正確的答案。選擇合適的資料型別取決於儲存空間和靈活性兩方面的考量。

如果已經建立了資料表，但想要變更某個欄位的資料型別，可以使用 ALTER TABLE 陳述式來修改欄位的限制。詳情請參閱第 5 章「變更限制」一節。

數值資料

本文將討論數值資料在 SQL 中的表示形式，並探討整數、小數和浮點數等資料型別。

程式碼範例

-- 建立一個包含不同整數資料型別的資料表
CREATE TABLE integer_example (
  id INT,
  small_id SMALLINT,
  tiny_id TINYINT
);

-- 插入範例資料
INSERT INTO integer_example (id, small_id, tiny_id)
VALUES (100, 100, 100);

-- 檢視結果
SELECT * FROM integer_example;

程式碼解密

1. 建立一個名為 integer_example 的資料表，包含三個欄位：id（INT）、small_id（SMALLINT）和 tiny_id（TINYINT）。
2. 向 integer_example 資料表中插入一筆範例資料。
3. 使用 SELECT 陳述式檢視插入的結果。

這個範例示範瞭如何在 SQL 中使用不同的整數資料型別，並展示瞭如何建立資料表和插入資料。

數值資料型別詳解

在資料函式倉管理系統中，數值資料型別的選擇對於資料的儲存和運算至關重要。本篇文章將探討整數、十進位數和浮點數的特性及其在不同資料函式庫系統中的實作方式。

整數型別

整數是不包含小數部分的數字，用於儲存不需要小數精確度的數值。整數型別包括有符號和無符號兩種，其中有符號整數可以儲存正負整數，而無符號整數僅能儲存非負整數。

建立整數欄位範例

CREATE TABLE my_table (
    my_integer_column INT
);

INSERT INTO my_table VALUES
(25),
(-525),
(2500252);

SELECT * FROM my_table;

輸出結果

+
---
-
---
-
---
-
---
-
---
+
| my_integer_column |
+
---
-
---
-
---
-
---
-
---
+
| 25                |
| -525              |
| 2500252           |
+
---
-
---
-
---
-
---
-
---
+

各資料函式庫系統的整數型別對照表

| 資料函式庫系統 | 資料型別 | 可儲存範圍 | 儲存大小 | |

-|

|

-|

| | MySQL | TINYINT | -128 至 127 | 1 位元組 | | MySQL | SMALLINT | -32768 至 32767 | 2 位元組 | | PostgreSQL | SMALLINT | -32768 至 32767 | 2 位元組 | | SQL Server | INT | -2147483648 至 2147483647 | 4 位元組 |

內容解密：

1. 資料型別選擇：不同的資料函式庫系統提供不同的整數型別，如 MySQL 的 TINYINT 和 PostgreSQL 的 SMALLINT。
2. 儲存大小：不同型別的整數佔用的儲存空間不同，例如 TINYINT 為 1 位元組，而 BIGINT 為 8 位元組。
3. 範圍限制：每種整數型別都有其特定的可儲存範圍，超出範圍的數值將無法儲存。

十進位數型別

十進位數用於儲存需要精確小數位數的數值，例如貨幣金額。十進位數的精確度由其定義時的精確度和小數位數決定。

建立十進位數欄位範例

CREATE TABLE my_table (
    my_decimal_column DECIMAL(5,2)
);

INSERT INTO my_table VALUES
(123.45),
(-123),
(12.3);

SELECT * FROM my_table;

輸出結果

+
---
-
---
-
---
-
---
-
---
+
| my_decimal_column |
+
---
-
---
-
---
-
---
-
---
+
| 123.45            |
| -123.00           |
| 12.30             |
+
---
-
---
-
---
-
---
-
---
+

十進位數型別特性

• 精確度：總共可儲存的數字位數。
• 小數位數：小數點右側的數字位數。

內容解密：

1. DECIMAL(5,2)定義：表示總共可儲存 5 位數字，其中小數點後佔 2 位。
2. 精確儲存：十進位數適合用於需要精確計算的場景，如財務資料。
3. 不同資料函式庫的實作差異：各資料函式庫系統對十進位數的最大精確度和小數位數有不同的限制。

浮點數型別

浮點數用於儲存非常大或非常小的近似數值。浮點數採用科學記號表示法，以節省儲存空間。

建立浮點數欄位範例

CREATE TABLE my_table (
    my_float_column FLOAT,
    my_double_column DOUBLE
);

INSERT INTO my_table VALUES
(123.45, 123.45),
(-12345.6789, -12345.6789),
(1234567.890123456789, 1234567.890123456789);

SELECT * FROM my_table;

輸出結果

+
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-+
| my_float_column | my_double_column   |
+
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-+
| 123.45          | 123.45             |
| -12345.7        | -12345.6789        |
| 1234570         | 1234567.8901234567 |
+
---
-
---
-
---
-
---
--+
---
-
---
-
---
-
---
-
---
-+

浮點數特性

• 近似儲存：浮點數儲存的是近似值，可能導致計算結果與預期不同。
• 單精確度與雙精確度：單精確度浮點數（如 FLOAT）提供較低的精確度，而雙精確度浮點數（如 DOUBLE）提供更高的精確度。

內容解密：

1. 浮點數的近似性質：由於浮點數是近似值，因此在需要精確比較或計算時可能會出現誤差。
2. 適用場景：浮點數適合用於科學計算或對精確度要求不高的應用場景。
3. 精確度選擇：根據需求選擇單精確度或雙精確度浮點數，以平衡儲存空間和精確度。