實測單一表格、EAV 模型與 JSON 欄位的查詢效能

Posted on In

Oracle Database 23ai 支援 JSON，這讓我們可以同時使用 SQL 和 JSON 來操作同一資料。本篇文章將針對三種不同的資料庫結構進行實際測試，來比較單一表格、EAV 模型和 JSON 欄位的查詢效能。

針對以下三種不同的資料庫結構進行實際測試:

  • 單一表格。
  • EAV 模型。
  • JSON 欄位。

單一表格

將資料放在一張固定結構的表格。

Create

1
2
3
4
5
6
7
8
9
10
11
12
-- 創建1table表結構
CREATE TABLE products_1table (
  product_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_name VARCHAR2(255),
  category VARCHAR2(50),  -- 如電器或衣服
  price NUMBER(10, 2),
  description VARCHAR2(1000),
  brand VARCHAR2(255),     -- 針對電器產品
  warranty VARCHAR2(50),   -- 針對電器產品
  color VARCHAR2(50),      -- 針對衣服產品
  product_size VARCHAR2(10) -- 針對衣服產品
);

Insert

隨機插入 10,000 筆資料到 products_1table 表中。透過循環 FOR i IN 1..10000 LOOP，根據條件動態生成資料並插入表格，使用條件語句來根據產品類型(電器或衣服)分配不同的屬性值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 1table
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO products_1table (product_name, category, price, description, brand, warranty, color, product_size)
    VALUES (
      'Product ' || i,
      CASE
        WHEN MOD(i, 2) = 0 THEN 'Electronics'
        ELSE 'Apparel'
      END,
      ROUND(DBMS_RANDOM.VALUE(100, 1000), 2),
      'Description of product ' || i,
      CASE
        WHEN MOD(i, 2) = 0 THEN 'Brand' || i
        ELSE NULL
      END,
      CASE
        WHEN MOD(i, 2) = 0 THEN '1 year'
        ELSE NULL
      END,
      CASE
        WHEN MOD(i, 2) = 1 THEN 'Red'
        ELSE NULL
      END,
      CASE
        WHEN MOD(i, 2) = 1 THEN 'L'
        ELSE NULL
      END
    );
  END LOOP;
  COMMIT;
END;

Query

在完成插入 10,000 筆資料到 products_1table 表後，我們來計算總筆數確認是否正確。

1
2
3
4
-- 查詢表中的所有資料
SELECT * FROM products_1table;
-- 計算資料筆數，確認插入是否達到 10,000 筆
SELECT COUNT(*) FROM products_1table;

我只想顯示查詢的執行時間，不需要顯示具體的查詢結果，這是避免大量資料在查詢時輸出到畫面上，也同時提高效能測試的準確性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SET TIMING ON;

DECLARE
  v_dummy VARCHAR2(100); -- 假變數
BEGIN
  FOR rec IN (
  SELECT product_name, brand, warranty, color, product_size FROM products_1table
  )
  LOOP
    v_dummy := rec.product_name;  -- 只是為了取結果而不顯示出來
  END LOOP;
END;

SET TIMING OFF;

EAV 模型

EAV 模型(Entity-Attribute-Value)是一種靈活的資料庫設計模式，允許我們為不同類型的實體動態添加屬性，而無需修改現有的資料表結構。這種模式特別適用於處理具有大量可變屬性的資料。

  1. Entity(實體): 實體是我們要描述的具體對象或物件。
    • 在產品資料庫中，電器和衣服都可以是產品的實體。
    • 而每個實體通常有一個唯一ID。
  2. Attribute(屬性): 屬性用來描述實體的特徵、參數或性質。
    • 如電器有品牌和保固期的屬性。
    • 如衣服有顏色和尺寸的屬性。
    • 屬性可以根據需要動態添加，無需修改資料表結構。
  3. Value(屬性值): 屬性值是每個屬性的具體資料或內容。
    • 如電器的品牌屬性值是Sony，保固期屬性值是2 years。
    • 如衣服的顏色屬性值是Red、尺寸屬性值是M。

Create

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 創建 EAV 模式表結構
CREATE TABLE products_eav (
  product_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_name VARCHAR2(255),
  category VARCHAR2(50),  -- 如電器或衣服
  price NUMBER(10, 2),
  description VARCHAR2(1000)
);

CREATE TABLE product_attributes (
  attribute_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  product_id NUMBER,
  attribute_name VARCHAR2(255),  -- 屬性名稱，如品牌、保固期、顏色、尺寸
  attribute_value VARCHAR2(255), -- 屬性值，如 Sony、2年、紅色、L
  FOREIGN KEY (product_id) REFERENCES products_eav(product_id)
);

Insert

隨機插入 10,000 筆資料到 products_eav 表中。透過循環 FOR i IN 1..10000 LOOP，根據條件動態生成資料並插入表格，並根據產品類型在屬性表 product_attributes 中插入不同的屬性值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
DECLARE
  product_id NUMBER;
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO products_eav (product_name, category, price, description)
    VALUES (
      'Product ' || i,
      CASE
        WHEN MOD(i, 2) = 0 THEN 'Electronics'
        ELSE 'Apparel'
      END,
      ROUND(DBMS_RANDOM.VALUE(100, 1000), 2),  -- 隨機生成價格
      'Description of product ' || i
    )
    RETURNING product_id INTO product_id;

    -- 根據類別插入不同的屬性
    IF MOD(i, 2) = 0 THEN  -- Electronics 類別的產品插入 brand 和 warranty
      INSERT INTO product_attributes (product_id, attribute_name, attribute_value)
      VALUES (product_id, 'brand', 'Brand' || i);
      INSERT INTO product_attributes (product_id, attribute_name, attribute_value)
      VALUES (product_id, 'warranty', '1 year');
    ELSE  -- Apparel 類別的產品插入 color 和 product_size
      INSERT INTO product_attributes (product_id, attribute_name, attribute_value)
      VALUES (product_id, 'color', CASE WHEN MOD(i, 3) = 0 THEN 'Red' ELSE 'Blue' END);
      INSERT INTO product_attributes (product_id, attribute_name, attribute_value)
      VALUES (product_id, 'product_size', CASE WHEN MOD(i, 2) = 0 THEN 'M' ELSE 'L' END);
    END IF;
  END LOOP;
  COMMIT;
END;

Query

在完成插入 10,000 筆資料到 products_eav 表後，我們來計算總筆數確認是否正確。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查詢表中的所有資料
SELECT p.product_name, a1.attribute_value AS brand, a2.attribute_value AS warranty, a3.attribute_value AS color, a4.attribute_value AS product_size
FROM products_eav p
LEFT JOIN product_attributes a1 ON p.product_id = a1.product_id AND a1.attribute_name = 'brand'
LEFT JOIN product_attributes a2 ON p.product_id = a2.product_id AND a2.attribute_name = 'warranty'
LEFT JOIN product_attributes a3 ON p.product_id = a3.product_id AND a3.attribute_name = 'color'
LEFT JOIN product_attributes a4 ON p.product_id = a4.product_id AND a4.attribute_name = 'product_size'
-- 計算資料筆數，確認插入是否達到 10,000 筆
SELECT COUNT(*)
FROM products_eav p
LEFT JOIN product_attributes a1 ON p.product_id = a1.product_id AND a1.attribute_name = 'brand'
LEFT JOIN product_attributes a2 ON p.product_id = a2.product_id AND a2.attribute_name = 'warranty'
LEFT JOIN product_attributes a3 ON p.product_id = a3.product_id AND a3.attribute_name = 'color'
LEFT JOIN product_attributes a4 ON p.product_id = a4.product_id AND a4.attribute_name = 'product_size'

只顯示查詢的執行時間，不顯示具體的查詢結果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SET TIMING ON;

DECLARE
  v_dummy VARCHAR2(100); -- 假變數
BEGIN
  FOR rec IN (
  SELECT p.product_name, a1.attribute_value AS brand, a2.attribute_value AS warranty, a3.attribute_value AS color, a4.attribute_value AS product_size
  FROM products_eav p
  LEFT JOIN product_attributes a1 ON p.product_id = a1.product_id AND a1.attribute_name = 'brand'
  LEFT JOIN product_attributes a2 ON p.product_id = a2.product_id AND a2.attribute_name = 'warranty'
  LEFT JOIN product_attributes a3 ON p.product_id = a3.product_id AND a3.attribute_name = 'color'
  LEFT JOIN product_attributes a4 ON p.product_id = a4.product_id AND a4.attribute_name = 'product_size'
  )
  LOOP
    v_dummy := rec.product_name;  -- 只是為了取結果而不顯示出來
  END LOOP;
END;

SET TIMING OFF;

JSON 欄位

將資料以 JSON 格式存放在欄位。

Create

1
2
-- 創建 JSON 集合表
CREATE JSON COLLECTION TABLE products_json;

Insert

隨機插入 10,000 筆資料到 products_json 表中。透過循環 FOR i IN 1..10000 LOOP，根據條件動態生成資料並插入表格，並將以 JSON 文件的形式儲存。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- json
BEGIN
  FOR i IN 1..10000 LOOP
    DECLARE
      json_data CLOB;
    BEGIN
      json_data :=
        '{"_id": ' || i || ', "product_name": "Product ' || i || '", "category": "' ||
        CASE
          WHEN MOD(i, 2) = 0 THEN 'Electronics'
          ELSE 'Apparel'
        END || '", "price": ' || ROUND(DBMS_RANDOM.VALUE(100, 1000), 2) || ',
        "description": "Description of product ' || i || '",
        "attributes": ' ||
        CASE
          WHEN MOD(i, 2) = 0 THEN '{"brand": "Brand' || i || '", "warranty": "1 year"}'
          ELSE '{"color": "Red", "product_size": "L"}'
        END || '}';

      INSERT INTO products_json (DATA) VALUES (json_data);
    END;
  END LOOP;
  COMMIT;
END;

Query

在完成插入 10,000 筆資料到 products_json 表後，我們來計算總筆數確認是否正確。

1
2
3
4
-- 查詢表中的所有資料
SELECT DATA FROM products_json;
-- 計算資料筆數，確認插入是否達到 10,000 筆
SELECT COUNT(DATA)FROM products_json

只顯示查詢的執行時間，不顯示具體的查詢結果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SET TIMING ON;

DECLARE
  v_dummy VARCHAR2(100); -- 假變數
BEGIN
  FOR rec IN (SELECT
                JSON_VALUE(DATA, '$.product_name') AS product_name,
                JSON_VALUE(DATA, '$.attributes.brand') AS brand,
                JSON_VALUE(DATA, '$.attributes.warranty') AS warranty,
                JSON_VALUE(DATA, '$.attributes.color') AS color,
                JSON_VALUE(DATA, '$.attributes.product_size') AS product_size
              FROM products_json)
  LOOP
    v_dummy := rec.product_name;  -- 只是為了取結果而不顯示出來
  END LOOP;
END;

SET TIMING OFF;

比較查詢效能

實測一下單一表格、EAV 模型與 JSON 欄位這三種資料庫結構的查詢效能吧!

測試類型 單一表格 EAV 模型 JSON 欄位
平均查詢時間(秒) 0.0155 0.0284 0.0595
結構靈活度 低
任何屬性變更都需修改資料表結構		 高
允許動態新增屬性而無需改變表結構		 高
允許完全動態且不固定的結構

查詢效能比較結果:

  • 單一表格: 0.0155秒
    單一表格的查詢速度最快，這是預期中的結果。由於這種表格結構是固定的，所有欄位都直接儲存在同一表內，查詢相對簡單且不需要 JOIN 操作，因此效率最高。
  • EAV 模型: 0.0284秒
    EAV 模型的性能次之，主要原因在於資料分散儲存和多表聯結。
    • 資料分散: 產品的屬性被存放在不同表中，增加了管理和查詢的複雜度。
    • 多表 JOIN: 需要多次關聯 JOIN 來組合完整的產品資訊，這會顯著增加查詢時間。
    • 不過，EAV 模型提供高度靈活性，允許動態添加屬性，適合具有可變結構的數據。
  • JSON 欄位: 0.0595秒
    JSON 欄位的查詢時間較長，可能是因為 JSON 查詢需要對每筆資料進行解析。使用 JSON 儲存資料在結構靈活性上有較大優勢，特別適合於資料屬性變動較大的情境。

完整程式碼

為了測試這三種不同的資料庫結構，我使用 Python 編寫一個腳本用來執行查詢效能測試。

需要事先安裝 Oracle 的資料庫程式，並且設定連接資訊，如資料庫的伺服器地址、使用者名稱與密碼等。腳本會自動執行 100 次查詢，並將每次的執行時間記錄下來，最後計算出平均查詢時間，最後也會將結果輸出至 CSV 檔案。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
import oracledb
from dotenv import load_dotenv
import os
import pandas as pd
import time

# 加載環境變量
load_dotenv()
username = os.getenv("username")
password = os.getenv("password")
dsn = os.getenv("dsn")

# 連接到 Oracle 資料庫
try:
    connection = oracledb.connect(user=username, password=password, dsn=dsn)
    print("資料庫連接成功！")
except Exception as e:
    print("資料庫連接失敗！", e)

# 查詢語句
query_1table = """
SELECT product_name, brand, warranty, color, product_size
FROM products_1table
"""

query_eav = """
SELECT p.product_name, a1.attribute_value AS brand, a2.attribute_value AS warranty,
       a3.attribute_value AS color, a4.attribute_value AS product_size
FROM products_eav p
LEFT JOIN product_attributes a1 ON p.product_id = a1.product_id AND a1.attribute_name = 'brand'
LEFT JOIN product_attributes a2 ON p.product_id = a2.product_id AND a2.attribute_name = 'warranty'
LEFT JOIN product_attributes a3 ON p.product_id = a3.product_id AND a3.attribute_name = 'color'
LEFT JOIN product_attributes a4 ON p.product_id = a4.product_id AND a4.attribute_name = 'product_size'
"""

query_json = """
SELECT
  JSON_VALUE(DATA, '$.product_name') AS product_name,
  JSON_VALUE(DATA, '$.attributes.brand') AS brand,
  JSON_VALUE(DATA, '$.attributes.warranty') AS warranty,
  JSON_VALUE(DATA, '$.attributes.color') AS color,
  JSON_VALUE(DATA, '$.attributes.product_size') AS product_size
FROM products_json
"""

# 儲存結果的 DataFrame
results_df = pd.DataFrame(columns=["1table", "EAV", "JSON"])

# 測試 100 次查詢
for i in range(100):
    with connection.cursor() as cursor:
        # 執行 1table 查詢
        start_time = time.time()
        cursor.execute(query_1table)
        cursor.fetchall()
        execution_time_1table = time.time() - start_time

        # 執行 EAV 查詢
        start_time = time.time()
        cursor.execute(query_eav)
        cursor.fetchall()
        execution_time_eav = time.time() - start_time

        # 執行 JSON 查詢
        start_time = time.time()
        cursor.execute(query_json)
        cursor.fetchall()
        execution_time_json = time.time() - start_time

        # 將執行時間加入 DataFrame
        results_df.loc[i] = [execution_time_1table, execution_time_eav, execution_time_json]

# 計算每個結構的查詢平均值
avg_1table_time = results_df["1table"].mean()
print(f'1table: {avg_1table_time:.4f} 秒')
avg_eav_time = results_df["EAV"].mean()
print(f'eav: {avg_eav_time:.4f} 秒')
avg_json_time = results_df["JSON"].mean()
print(f'json: {avg_json_time:.4f} 秒')

# 加平均值到 DataFrame
results_df.loc[len(results_df)] = [avg_1table_time, avg_eav_time, avg_json_time]

# 將結果保存為 CSV
results_df.to_csv("query-performance-results.csv", index=False)

# 關閉連接
connection.close()

print("查詢測試完成，結果已保存至 'query-performance-results.csv'")
1
2
3
4
5
資料庫連接成功！
1table: 0.0155 秒
eav: 0.0284 秒
json: 0.0595 秒
查詢測試完成，結果已保存至 'query-performance-results.csv'

參考資料

彈性資料庫架構：存 JSON format 與 EAV model 的優劣比較