- vừa được xem lúc

Sử dụng JSON khi thiết kế 04 loại cơ sở dữ liệu Oracle, PostgreSQL, SQL Server và MySQL | Tại sao và có thể tối ưu hiệu năng JSON Column được không ?

#json database
#toi uu hieu nang
#toiuusql
#tranquochuy wecommit

0 0 3

Người đăng: Trần Quốc Huy

Theo Viblo Asia

Bài viết này cung cấp một góc nhìn về sử dụng JSON khi thiết kế Cơ sở dữ liệu, đi kèm với các ví dụ cụ thể trên cả 4 loại cơ sở dữ liệu Oracle, SQL Server, PostgreSQL và MySQL

1. Đầu tiên, chúng ta cùng xem xét một bài toán thiết kế cơ sở dữ liệu kinh điển

Giả sử một công ty bán lẻ cần thiết kế cơ sở dữ liệu để có thể lưu được thông tin

  • Sản phẩm
  • Đơn hàng
  • Thông tin khách hàng

Các thông tin này thường được thiết kế kinh điển như sau

Bảng Products: Mục đích: Lưu trữ thông tin về các sản phẩm mà công ty bán lẻ cung cấp. Các cột:

  • product_id (PRIMARY KEY): Mỗi sản phẩm chỉ có một PRODUCT_ID
  • product_name: Tên của sản phẩm.
  • description: Mô tả về sản phẩm.
  • price: Giá của sản phẩm.

Câu lệnh tạo bảng

CREATE TABLE Products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), description TEXT, price DECIMAL(10, 2) );

Bảng Customers: Mục đích: Lưu thông tin về khách hàng.

Các cột:

  • customer_id (PRIMARY KEY): Mã khách hàng. Mỗi ông khách hàng chỉ có một mã duy nhất
  • first_name: Tên khách hàng.
  • last_name: Họ khách hàng.
  • email: Địa chỉ email của khách hàng.
  • phone: Số điện thoại của khách hàng.

Câu lệnh tạo bảng

CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20)
);

Bảng Orders: Mục đích: Lưu thông tin về các đơn hàng của khách hàng. Các cột:

  • order_id (PRIMARY KEY): ID duy nhất cho mỗi đơn hàng.
  • customer_id (FOREIGN KEY): Liên kết đến bảng Customers để xác định khách hàng đã đặt hàng.
  • product_id (FOREIGN KEY): Liên kết đến bảng Products để xác định sản phẩm trong đơn hàng.
  • quantity: Số lượng sản phẩm trong đơn hàng.
  • order_date: Ngày đặt hàng.

Câu lệnh tạo bảng

CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

2. Vấn đề phát sinh vì thiếu linh hoạt trong thiết kế Cơ sở dữ liệu (khi chưa sử dụng JSON)

Giả sử sau một thời gian kinh doanh, người quản lý của công ty trên muốn lưu thêm các thông tin đặc điểm của khách hàng (để phục vụ các chương trình Marketing, các chương trình bán hàng sau này).

Ví dụ, bây giờ muốn thêm thông tin để lưu lại SỞ THÍCH của mỗi khách hàng thì sao nhỉ.

Tất nhiên anh em có thể nghĩ ngay tới phương án ở đây là: Thêm cột mới trên bảng Customers thôi.

Okie, phương án thêm cột sẽ như sau nhé:

ALTER TABLE Customers
ADD hobbies VARCHAR(255);

Phương án thêm cột mới (ALTER TABLE… ADD COLUMN) có một số điểm không ổn như sau

Vấn đề 1: Nếu mỗi khách hàng có nhiều sở thích

Trường hợp này anh em có thể nghĩ tới ngay, các sở thích đều là dạng TEXT và mỗi sở thích lưu trữ cách nhau bởi dấu phẩy là okie.

Ví dụ như sau với 3 sở thích của 3 khách hàng Wecommit_A, Wecommit_B và Wecommit_C. Thông tin trong cột hobbies sẽ lưu như sau

  • Khách hàng Wecommit_A: “reading, cooking, hiking”
  • Khách hàng Wecommit_B: “photography, gardening”
  • Khách hàng Wecommit_C: “cooking, painting, gaming”

Bây giờ chúng ta có một chiến dịch Marketing và muốn tìm những người có sở thích “cooking” để tiếp cận, quảng báo chương trình.

Để tìm kiếm tất cả những người có sở thích như vậy, chúng ta cần sử dụng câu lệnh SQL như sau

SELECT * FROM Customers WHERE hobbies LIKE '%cooking%';

Câu lệnh tìm kiếm kiểu này sẽ có chiến lược thực thi là FULL TABLE SCAN với các loại cơ sở dữ liệu.

Chiến lược thực thi này sẽ quét toàn bộ các BLOCK DỮ LIỆU trong bảng Customers, gây ảnh hưởng hiệu năng của cơ sở dữ liệu.

Trường hợp bạn chưa biết về chiến lược thực thi, bạn có thể xem bài viết mà tôi đã chia sẻ trước đây như

Ngoài vấn đề câu lệnh SELECT không hiệu quả, chúng ta cũng có thể gặp vướng mắc khi muốn chỉnh sửa (UPDATE) một sở thích nào đó của khách hàng .

Vấn đề 2: Giả sử trong tương lai, chúng ta muốn tiếp tục mở rộng cấu trúc thì sẽ tiếp tục gặp khó khăn

Ví dụ: Sau một thời gian hoạt động, anh em lại muốn bổ sung thêm một cột lưu thông tin ĐỊA CHỈ của khách hàng đi.

Lúc này chúng ta lại lọ mọ thêm một cột mới ADDRESS trong bảng CUSTOMERS. Cứ tiếp tục mãi như thế này có vẻ không được ổn lắm nhỉ.

Nếu các anh em đang gặp các vấn đề khó khăn như vậy, có một giải pháp rất linh hoạt: Sử dụng JSON cho một cột có thể mở rộng, thay đổi trong tương lai.

Chi tiết các anh em xem phần đằng sau nhé.

3. Sử dụng JSON trong bài toán thiết kế cơ sở dữ liệu

Tại bài toán đang xét, chúng ta có thể tạo thêm 1 cột dạng JSON để tạo sự linh hoạt trong lưu trữ thông tin.

Ví dụ, tôi sẽ tạo thêm cột với tên là additional_info trong bảng Customers

Câu lệnh thực hiện như sau

Giả sử câu lệnh thực hiện trên cơ sở dữ liệu SQL Server

ALTER TABLE Customers
ADD additional_info NVARCHAR(MAX);

Ghi chú: để thể hiện một cột sử dụng JSON, với các loại cơ sở dữ liệu khác nhau, chúng ta sẽ có cú pháp hơi khác nhau một chút. Cú pháp chi tiết sẽ có ở phần đằng sau nhé anh em. Tại đây thì cứ tập trung vào bản chất để hiểu đã.

Trường additional_info sẽ lưu các loại thông tin bổ sung trong tương lai (sở thích, độ yêu thích của của từng loại sở thích,…)

Trong trường hợp này, để cập nhật thông tin sở thích của 3 khách hàng dưới đây, chúng ta sẽ sử dụng câu lệnh UPDATE tương ứng như sau

  • Khách hàng Wecommit_A: “reading, cooking, hiking”
  • Khách hàng Wecommit_B: “photography, gardening”
  • Khách hàng Wecommit_C: “cooking, painting, gaming”

Câu lệnh để cập nhật thông tin

-- Thêm thông tin sở thích cho từng khách hàng vào cột additional_info sử dụng JSON
UPDATE Customers
SET additional_info = '{"hobbies": ["reading", "cooking", "hiking"]}'
WHERE first_name = 'Wecommit_A'; UPDATE Customers
SET additional_info = '{"hobbies": ["photography", "gardening"]}'
WHERE first_name = 'Wecommit_B'; UPDATE Customers
SET additional_info = '{"hobbies": ["cooking", "painting", "gaming"]}'
WHERE first_name = 'Wecommit_C';

Ghi chú: Thực thế người ta có thể tìm và cập nhật theo CUSTOMER_ID để tránh trường hợp nhiều người có cùng FIRST_NAME, tuy nhiên bài viết với mục đích đơn giản hoá trong việc tiếp cận, nên để câu lệnh cập nhật theo FIRST_NAME nhé anh em.

Thông tin lưu trữ trong bảng Customers lúc này như sau

select first_name,additional_info from customers;

first_name additional_info Wecommit_A {“hobbies”: [“reading”, “cooking”, “hiking”]} Wecommit_B {“hobbies”: [“photography”, “gardening”]} Wecommit_C {“hobbies”: [“cooking”, “painting”, “gaming”]}

Giả sử bây giờ chúng ta muốn thêm thông tin ADDRESS cho các khách hàng của mình.

  • Khách hàng có mã Wecommit_A có ADDRESS là 200 Le Van Luong, Ha Noi
  • Khách hàng có mã Wecommit_B có ADDRESS là 100 Tran Hung Dao, Nam Dinh
  • Khách hàng có mã Wecommit_C có ADDRESS là 105 Hoang Mai, Ha Noi

Câu lệnh thực hiện như sau

UPDATE Customers
SET additional_info = JSON_MODIFY( COALESCE(additional_info, '{}'), '$.address', '200 Le Van Luong, Ha Noi'
)
WHERE first_name = 'Wecommit_A'; UPDATE Customers
SET additional_info = JSON_MODIFY( COALESCE(additional_info, '{}'), '$.address', '100 Tran Hung Dao, Nam Dinh'
)
WHERE first_name = 'Wecommit_B'; UPDATE Customers
SET additional_info = JSON_MODIFY( COALESCE(additional_info, '{}'), '$.address', '105 Hoang Mai, Ha Noi'
)
WHERE first_name = 'Wecommit_C';

Lúc này, thông tin lưu trong Table Customers khi sử dụng cột JSON sẽ như sau

select first_name,additional_info from customers;

first_name additional_info Wecommit_A {“hobbies”: [“reading”, “cooking”, “hiking”],”address”:”200 Le Van Luong, Ha Noi”} Wecommit_B {“hobbies”: [“photography”, “gardening”],”address”:”100 Tran Hung Dao, Nam Dinh”}} Wecommit_C {“hobbies”: [“cooking”, “painting”, “gaming”],”address”:”105 Hoang Mai, Ha Noi”}

4. Anh em có thể sử dụng JSON trong thiết kế với loại cơ sở dữ liệu nào và từ phiên bản nào?

Cả 4 loại cơ sở dữ liệu phổ biến Oracle, PostgreSQL, SQL Server và MySQL đều có thể sử dụng JSON khi thiết kế được anh em nhé.

Dưới đây là chi tiết về phiên bản anh em có thể áp dụng và thông tin dẫn chứng chi tiết

4.1. Sử dụng JSON trong Cơ sở dữ liệu Oracle

Các dự án sử dụng Oracle phiên bản Oracle 12c Release 2 trở đi đều có thể sử dụng JSON

Cú pháp để sử dụng JSON như sau

CREATE TABLE Customers (
customer_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
phone VARCHAR2(20),
additional_info JSON
);

Thông tin chi tiết về việc sử dụng JSON trong Oracle: Anh em xem tài liệu này nhé.

4.2. Sử dụng JSON trong Cơ sở dữ liệu SQL Server

Từ phiên bản SQL Server 2016 anh em có thể thiết kế cơ sở dữ liệu sử dụng JSON.

Để định nghĩa cột chứa dữ liệu kiểu JSON, anh em sử dụng kiểu NVARCHAR(MAX). Ví dụ như sau

CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
additional_info NVARCHAR(MAX)
);

Thông tin chi tiết về sử dụng JSON trong SQL Server anh em có thể xem tại tài liệu chính hãng sau: Click vào đây để xem.

4.3. Sử dụng JSON trong Cơ sở dữ liệu PostgreSQL

Với anh em nào sử dụng phiên bản cơ sở dữ liệu PostgreSQL từ 9.2 trở đi đều có thể thiết kế Table sử dụng JSON.

Câu lệnh tạo Table sử dụng JSON như sau

CREATE TABLE Customers ( customer_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100), phone VARCHAR(20), additional_info JSON );

Câu lệnh tạo bảng nếu sử dụng JSONB như sauThông tin chi tiết anh em có thể đọc tài liệu chuẩn hãng tại đây nhé: Click vào đây để xem

4.4. Sử dụng JSON trong Cơ sở dữ liệu MySQL

Việc sử dụng JSON trên MySQL có thể áp dụng từ phiên bản MySQL 5.7.8

Câu lệnh tạo Table sử dụng JSON trong MySQL như sau:

CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
phone VARCHAR(20),
additional_info JSON
);

Thông tin chi tiết anh em có thể đọc tài liệu chuẩn hãng tại đây: Click vào đây để xem

5. Hiệu năng của câu lệnh SQL khi làm việc với cột sử dụng JSON

5.1. Giả lập dữ liệu để đánh giá hiệu năng với Table sử dụng JSON

Tại bài viết này, tôi sẽ phân tích trên một Cơ sở dữ liệu là PostgreSQL, tuy nhiên nguyên lý được phân tích ở đây có thể áp dụng với các loại cơ sở dữ liệu khác (Oracle, SQL Server, MySQL)

Tôi sẽ thực hiện giả lập một Table sử dụng JSON và Insert vào 10.000 bản ghi để giả lập tình huống

CREATE TABLE JsonTableWecommit (
id SERIAL PRIMARY KEY,
json_data JSON
);

INSERT INTO JsonTableWecommit (json_data)
SELECT json_build_object(
'name', 'Wecommit' || id,
'email', 'Wecommit' || id || '@example.com',
'age', (RANDOM()*50 + 18)::int
)
FROM generate_series(1, 10000) id;

Dữ liệu giả lập có dạng như sau

id json_data 1 {“name” : “Wecommit1”, “email” : “Wecommit1@example.com”, “age” : 49} 2 {“name” : “Wecommit2”, “email” : “Wecommit2@example.com”, “age” : 50} 3 {“name” : “Wecommit3”, “email” : “Wecommit3@example.com”, “age” : 25} 4 {“name” : “Wecommit4”, “email” : “Wecommit4@example.com”, “age” : 62} 5 {“name” : “Wecommit5”, “email” : “Wecommit5@example.com”, “age” : 19} 6 {“name” : “Wecommit6”, “email” : “Wecommit6@example.com”, “age” : 22} 7 {“name” : “Wecommit7”, “email” : “Wecommit7@example.com”, “age” : 47} 8 {“name” : “Wecommit8”, “email” : “Wecommit8@example.com”, “age” : 42}

5.2. Phân tích câu lệnh SQL trước khi tối ưu

Giả sử bây giờ chúng ta cần thực hiện câu lệnh tìm kiếm trên thông tin NAME (thuộc cột JSON_DATA)

SELECT *
FROM JsonTableWecommit
WHERE json_data ->> 'name' = 'Wecommit2';

Thực hiện phân tích chiến lược thực thi của câu lệnh hiện tại. Để thực hiện việc này tôi sẽ sử dụng EXPLAIN ANALYZE.

  • Trong trường hợp anh em muốn biết cách thức hiển thị chiến lược thực thi của câu lệnh SQL trên các loại database khác nhau, anh em có thể đọc 1 bài viết khác của tôi: Click vào đây để đọc. Thực hiện kiểm tra chiến lược thực thi của câu lệnh
EXPLAIN ANALYZE
SELECT *
FROM JsonTableWecommit
WHERE json_data ->> 'name' = 'Wecommit2';

Kết quả như sau

"QUERY PLAN"
"Seq Scan on jsontablewecommit (cost=0.00..284.00 rows=50 width=79) (actual time=0.010..5.386 rows=1 loops=1)"
" Filter: ((json_data ->> 'name'::text) = 'Wecommit2'::text)"
" Rows Removed by Filter: 9999"
"Planning Time: 0.046 ms"
"Execution Time: 5.399 ms"

Phân tích kết quả thực hiện

  • Câu lệnh hiện tại thực thi với chi phí là 284
  • Thơi gian dự kiến thực hiện câu lệnh là 5.399 ms
  • Chiến lược để hệ thống có thể lấy được kết quả là SEQ SCAN (quét toàn bộ dữ liệu của bảng JsonTableWecommit)

5.3. Thực hiện tối ưu câu lệnh SELECT trên Table sử dụng JSON

Để tăng tốc câu lệnh SELECT bên trên, chúng ta có nhiều cách thức, trong đó sử dụng Index là một cách tiếp cận dễ dàng trong tình huống này.

CREATE INDEX idx_json_name ON JsonTableWecommit ((json_data ->> 'name'));

Kiểm tra lại chiến lược thực thi của câu lệnh SQL sau khi thêm Index

EXPLAIN ANALYZE
SELECT *
FROM JsonTableWecommit
WHERE json_data ->> 'name' = 'Wecommit2';

Kết quả

"QUERY PLAN"
"Bitmap Heap Scan on jsontablewecommit (cost=4.67..104.35 rows=50 width=79) (actual time=0.062..0.063 rows=1 loops=1)"
" Recheck Cond: ((json_data ->> 'name'::text) = 'Wecommit2'::text)"
" Heap Blocks: exact=1"
" -> Bitmap Index Scan on idx_json_name (cost=0.00..4.66 rows=50 width=0) (actual time=0.057..0.057 rows=1 loops=1)"
" Index Cond: ((json_data ->> 'name'::text) = 'Wecommit2'::text)"
"Planning Time: 0.428 ms"
"Execution Time: 0.093 ms"

Phân tích kết quả

Chiến lược thực thi sau khi tối ưu có COST = 104.35 ( chỉ gần bằng 1/2 Cost trước khi tối ưu = 284) Thời gian ước tính đã giảm từ 5.399 ms xuống còn 0.093 ms Chiến lược thực thi của câu lệnh đã ghi nhận việc sử dụng Index idx_json_name thay (Index đã tạo để tối ưu)

6. Tôi có một chương trình có phí – Từ điển tối ưu 100x hiệu năng – chương trình này sẽ giúp bạn HOÀN TOÀN TỰ TIN và KHÁC BIỆT so với các đồng nghiệp về năng lực tối ưu

Trong chương trình này bạn sẽ nhận được rất nhiều các KIẾN THỨC, KINH NGHIỆM ĐỘC QUYỀN, có nhiều thứ các anh em DEV còn không biết về sự tồn tại.

Tôi sẽ đồng hành 1 năm cùng anh em, để anh em hoàn toàn tự tin khi áp dụng các kiến thức vào dự án thực tế trên công ty.

Đây là một chương trình có phí, anh em có thể** tham gia chương trình 01 năm ngay từ mức phí 8.000.000 VNĐ.**

Các anh em có thể đăng ký trải nghiệm để hiểu rõ mọi thứ trong chương trình để tự đánh giá được chương trình có phù hợp với mục tiêu của bản thân hay không.

Buổi trải nghiệm này diễn ra qua Zoom Online, cách thức đăng ký tham gia trải nghiệm như sau:

  • Cách 1: Đăng ký trực tiếp trên Website wecommit.com.vn qua Form đăng ký
  • Cách 2: Inbox Zalo 0888549190

7. Nếu bạn muốn liên hệ với tôi

Tác giả: Trần Quốc Huy – Founder & CEO Wecommit. Các bạn có thể liên hệ, thảo luận các kiến thức về tối ưu cùng tôi qua kênh FB cá nhân của tôi: Facebook: [https://www.facebook.com/tranquochuy.toiuu/](Facebook: https://www.facebook.com/tranquochuy.toiuu/)

Ghi chú: Bài viết gốc được đăng trên Wecommit. Link bài viết gốc tại đây: https://wecommit.com.vn/su-dung-json-khi-thiet-ke-co-so-du-lieu/

Bình luận