Bài 3: SQL Server và chú ý thiết kế CSDL | Tìm ở đây

Cấu trúc của SQL Server

SQL Server gồm có 5 cơ sở dữ liệu ( CSDL ) mạng lưới hệ thống ( system databases ) và một hay nhiều user database. Các system databases gồm có :

• Cơ sở dữ liệu Master: Chứa tất cả những thông tin cấp hệ thống (system-level information) bao gồm thông tin về các database khác trong hệ thống như vị trí của các data files, các login account và các thiết đặt cấu hình hệ thống của SQL Server (system configuration settings).
• Cơ sở dữ liệu Tempdb: Chứa tất cả những table hay stored procedure được tạm thời tạo ra trong quá trình làm việc bởi user hay do bản thân SQL Server engine. Các table hay stored procedure này sẽ biến mất khi khởi động lại SQL Server hay khi ta disconnect.
• Cơ sở dữ liệu Model: Database này đóng vai trò như một bảng mẫu (template) cho các database khác. Nghĩa là khi một user database được tạo ra thì SQL Server sẽ copy toàn bộ các system objects (tables, stored procedures…) từ Model database sang database mới vừa tạo.
• Cơ sở dữ liệu Msdb: được sử dụng cho SQL Server Agent để lập lịch các công việc và các cảnh báo (schedule alerts and jobs).
• Cơ sở dữ liệu Resource: là một CSDL chỉ đọc chứa các object hệ thống  mà được sử dụng trong SQL Server. Các Object hệ thống về mặt vật lý tồn tại trong Resource Database nhưng về mặt logic nó lại xuất lược đồ hệ thống (sys schema) của mỗi cơ sở dữ liệu.

Cấu trúc vật lý của một CSDL SQL Server

Mỗi một database trong SQL Server đều chứa ít nhất một data file chính (primary), có thể có thêm một hay nhiều data file phụ (Secondary) và một transaction log file.

• Primary data file (thường có phần mở rộng .mdf) : đây là file chính chứa data và những system tables.
• Secondary data file (thường có phần mở rộng .ndf) : đây là file phụ thường chỉ sử dụng khi database được phân chia để chứa trên nhiều dĩa.
• Transaction log file (thường có phần mở rộng .ldf) : đây là file ghi lại tất cả những thay đổi diễn ra trong một database và chứa đầy đủ thông tin để có thể roll back hay roll forward khi cần.

Data trong SQL Server được chứa thành từng Page 8KB và 8 page liên tục tạo thành một Extent như hình vẽ dưới đây:

Trước khi SQL Server muốn lưu data vào một table nó cần phải dành riêng một khoảng trống trong data file cho table đó. Những khoảng trống đó chính là các extents. Có 2 loại Extents: Mixed Extents (loại hỗn hợp) dùng để chứa data của nhiều tables trong cùng một Extent và Uniform Extent (loại thuần nhất) dùng để chứa data của một table. Ðầu tiên SQL Server dành các Page trong Mixed Extent để chứa data cho một table sau đó khi data tăng trưởng thì SQL dành hẳn một Uniform Extent cho table đó.

Nguyên tắc hoạt động giải trí của Transaction Log trong SQL Server

Transaction log file trong SQL Server dùng để ghi lại các thay đổi xảy ra trong CSDL. Quá trình này diễn ra như sau: đầu tiên khi có một sự thay đổi dữ liệu như Insert, Update, Delete được yêu cầu từ các ứng dụng, SQL Server sẽ tải (load) trang dữ liệu (data page) tương ứng  vào  bộ nhớ (vùng bộ nhớ này gọi là data cache), sau đó dữ liệu trong data cache được thay đổi (những trang bị thay đổi còn gọi là dirty-page). Tiếp theo mọi sự thay đổi đều được ghi vào transaction log file cho nên người ta gọi là write-ahead log. Cuối cùng thì một quá trình gọi là Check Point Process sẽ kiểm tra và viết tất cả những transaction đã được hoàn tất (commited) vào đĩa cứng (flushing the page).

Ngoài Check Point Process những dirty-page còn được đưa vào đĩa bởi một Lazy writer. Ðây là một anh chàng làm việc âm thầm chỉ thức giấc và quét qua phần data cache theo một chu kỳ nhất định sau đó lại ngủ yên chờ lần quét tới.

Xin lý giải thêm một chút ít về khái niệm transaction trong CSDL. Một transaction hay một thanh toán giao dịch là một loạt những hoạt động giải trí xảy ra được xem như một đơn vị chức năng việc làm ( unit of work ) nghĩa là hoặc thành công xuất sắc hàng loạt hoặc không làm gì cả ( all or nothing ). Sau đây là một ví dụ cổ xưa về transaction :

Chúng ta muốn chuyển một số tiền VND500000 từ tài khoản A sang 
tài khoản B như vậy công việc này cần làm các bước sau:

1. Trừ VND500000 từ tài khoản A

2. Cộng VND500000 vào tài khoản B

Tuy nhiên việc chuyển tiền trên phải được thực hiện dưới dạng một transaction nghĩa là giao dịch chỉ được xem là hoàn tất (commited) khi cả hai bước trên đều thực hiện thành công. Nếu vì một lý do nào đó ta chỉ có thể thực hiện được bước 1 (chẳng hạn như vừa xong bước 1 thì điện cúp hay máy bị treo) thì xem như giao dịch không hoàn tất và cần phải được phục hồi lại trạng thái ban đầu (roll back).

Thế thì Check Point Process hoạt động giải trí như thế nào để hoàn toàn có thể bảo vệ một transaction được thực thi mà không làm “ dơ ” CSDL .
Trong hình vẽ trên, một transaction được màn biểu diễn bằng một mũi tên. Trục nằm ngang là trục thời hạn. Giả sử một Check Point được lưu lại vào thời gian giữa transaction 2 và 3 như hình vẽ và sau đó sự cố xảy ra trước khi gặp một Check point sau đó. Như vậy khi SQL Server được restart nó sẽ dựa trên những gì ghi trong transaction log file để phục sinh dữ liệu ( xem hình vẽ ) .
Điều đó có nghĩa là SQL Server sẽ không cần làm gì cả so với transaction 1 vì tại thời gian Check point dữ liệu đã được lưu vào đĩa rồi. Trong khi đó transaction 2 và 4 sẽ được roll forward vì tuy đã được hoàn thành xong nhưng do sự cố xảy ra trước thời gian Check point tiếp nối nên dữ liệu chưa kịp lưu vào đĩa. Tức là dựa trên những thông tin được ghi trên file log SQL Server trọn vẹn có vừa đủ cơ sở để viết vào đĩa cứng. Còn transaction 3 và 5 thì chưa được hoàn thành xong ( do bị down giật mình ) cho nên vì thế SQL Server sẽ roll back hai transaction này dựa trên những gì được ghi trên file log .

Cấu trúc logic của một CSDL SQL Server

Hầu như mọi thứ trong SQL Server được tổ chức thành những objects ví dụ như tables, views, stored procedures, indexes, constraints…. Những system objects trong SQL Server thường có bắt đầu bằng chữ sys hay sp. Các objects trên sẽ được nghiên cứu lần lượt trong các bài sau do đó trong phần này chúng ta chỉ bàn sơ qua một số system object thông dụng trong CSDL SQL Server mà thôi.

Một số Sytem objects thường dùng :

Ví dụ :
sp_helpdb ‘ Northwind ’ sẽ cho tác dụng có dạng như bảng dưới đây

name             db_size     owner     dbid     created         status …..
——————————————————————————————————————————– ——-
Northwind     3.94 MB     sa           6         Aug 6 2000     Status=ONLINE, Updateability=READ_WRITE, …..

stored procedure sp_spaceused như ví dụ sau

USE Northwind
Go
sp_spaceused 'Customers'

sẽ cho biết thông tin về table Customer :

name             rows     reserved       data        index_size     unused
————————————- ——————————————
Customers     91         104 KB         24 KB      80 KB             0 KB

Tạo mới một CSDL Người sử dụng

Chúng ta hoàn toàn có thể tạo một CSDL mới thuận tiện dùng SQL Server Enterprise bằng cách kích chuột phải lên trên “ Database ” và chọn “ New Database ” như hình vẽ sau :
Sau đó tất cả chúng ta chỉ việc đánh tên của CSDL và click OK .

Ngoài ra đôi khi chúng ta cũng dùng SQL Script để tạo một CSDL. Khi đó ta phải chỉ rõ vị trí của primary data file và transaction log file.

Ví dụ :

USE master
GO
CREATE DATABASE Products
ON
(  NAME = prods_dat,
   FILENAME = 'c:\program files\microsoft SQL server\mssql\data\prods.mdf',
   SIZE = 4,
   MAXSIZE = 10,
   FILEGROWTH = 1
)
GO

Trong ví dụ trên ta tạo một database tên là Products với logical file name là prods_dat và physical file name là prods.mdf, kích cỡ bắt đầu là 4 MB và file dữ liệu sẽ tự động hóa tăng lên mỗi lần 1 MB cho tới tối đa là 10 MB. Nếu ta không chỉ định một transaction log file thì SQL sẽ tự động hóa tạo ra 1 log file với kích cỡ khởi đầu là 1 MB .

Lưu ý:

Khi tạo ra một database chúng ta cũng phải lưu ý một số điểm sau: Ðối với các hệ thống nhỏ mà ở đó vấn đề tốc độ của server không thuộc loại nhạy cảm thì chúng ta thường chọn các giá trị mặc định (default) cho Initial size, Automatically growth file. Nhưng trên một số ứng dụng của các hệ thống có CSDL kích thước lớn phải được người quản trị CSDL ước lượng trước tùy theo tầm cỡ của các nghiệp vụ  và thông thường người ta không chọn Autogrowth (tự động tăng trưởng) và Autoshrink (tự động nén). Câu hỏi được đặt ra ở đây là vì sao ta không để SQL Server chọn một giá trị khởi đầu cho datafile và sau đó khi cần thì nó sẽ tự động nới rộng ra mà lại phải ước lượng trước? Nguyên nhân là nếu chọn Autogrowth (hay Autoshrink) thì chúng ta có thể sẽ gặp 2 vấn đề sau:

• Hiệu năng – Performance hit: Ảnh hưởng đáng kể đến khả năng làm việc của SQL Server. Do nó phải thường xuyên kiểm tra xem có đủ khoảng trống cần thiết hay không và nếu không đủ nó sẽ phải mở rộng bằng cách dành thêm khoảng trống từ dĩa cứng và chính quá trình này sẽ làm chậm đi hoạt động của SQL Server.
• Phân mảnh dữ liệu – Disk fragmentation : Việc mở rộng trên cũng sẽ làm cho dữ liệu  không được liên tục mà chứa ở nhiều nơi khác nhau trong đĩa cứng điều này cũng gây ảnh hưởng lên tốc độ làm việc của SQL Server.

Trong các hệ thống lớn người ta có thể dự đoán trước kích thước của CSDL bằng cách tính toán kích thước của các tables, đây cũng chỉ là kích thước ước đoán mà thôi (Search thêm “Estimating the size of a database” trên google để biết thêm về cách tính) và sau đó thường xuyên dùng một số câu lệnh SQL (thường dùng các câu lệnh bắt đầu bằng DBCC. Phần này sẽ được bàn qua trong các bài sau kiểm tra xem có đủ khoảng trống hay không nếu không đủ ta có thể chọn một thời điểm mà SQL server ít bận rộn nhất (như ban đêm hay sau giờ làm việc) để nới rộng data file như thế sẽ không làm ảnh hưởng tới hiệu năng của Server.

Chú ý giả sử ta dành sẵn 2 GB cho datafile, khi dùng Window Explorer để xem ta sẽ thấy kích cỡ của file là 2 GB nhưng data trong thực tiễn hoàn toàn có thể chỉ chiếm vài chục MB mà thôi .

Những điểm cần quan tâm khi phong cách thiết kế một CSDL

Trong khoanh vùng phạm vi bài này tất cả chúng ta không hề nói sâu về triết lý phong cách thiết kế CSDL mà chỉ đưa ra một vài lời khuyên mà bạn nên vận dụng khi phong cách thiết kế .

Trước hết bạn phải nắm vững về các loại dữ liệu (data type). Ví dụ bạn phải biết rõ sự khác biệt giữa char(10), nchar(10) varchar(10), nvarchar(10). Loại dữ liệu Char là một loại string có kích thước cố định nghĩa là trong ví dụ trên nếu data đưa vào “This is a really long character string” (lớn hơn 10 ký tự) thì SQL Server sẽ tự động cắt phần đuôi và ta chỉ còn “This is a”. Tương tự nếu string đưa vào nhỏ hơn 10 thì SQL sẽ thêm khoảng trống vào phía sau cho đủ 10 ký tự. Ngược lại loại varchar sẽ không thêm các khoảng trống phía sau khi string đưa vào ít hơn 10. Còn loại data bắt đầu bằng chữ n chứa dữ liệu dạng unicode.

Một lưu ý khác là trong SQL Server ta có các loại Integer như : tinyint, smallint, int, bigint. Trong đó kích thước từng loại tương ứng là 1,2,4,8 bytes. Nghĩa là loại smallint tương đương với Integer và loại int tương đương với Long trong VB.

Khi phong cách thiết kế table nên :

• Có ít nhất một cột thuộc loại ID dùng để xác định một bản ghi (hàng dữ liệu) dễ dàng.
• Chỉ chứa dữ liệu của một thực thể (thực thể)

Trong ví dụ sau thông tin về Sách và Nhà Xuất Bản được chứa trong cùng một table
Books

Ta nên tách ra thành bảng Books và bảng Publisher như sau:
Books

và Publishers

• Tránh dùng cột có chứa NULL và nên luôn có giá trị Default cho các cột
• Tránh lập lại một giá trị hay cột nào đó

Ví dụ một cuốn sách có thể được viết bởi hơn một tác giả và như thế ta có thể dùng một trong 2 cách sau để chứa data:
Books

hay Books

Tuy nhiên việc lập đi lập lại cột Author sẽ tạo nhiều vấn đề sau này. Chẳng hạn như nếu cuốn sách có nhiều hơn 3 tác giả thì chúng ta sẽ gặp phiền phức ngay….Trong ví dụ này ta nên chặt ra thành 3 table như sau:
Books

Authors

AuthorBook

Ngoài ra một trong những điều quan trọng là phải biết rõ quan hệ (Relationship) giữa các bảng:

• One-to-One Relationships hay quan hệ 1-1: trong mối quan hệ này thì một hàng bên bảng A không thể liên kết với hơn 1 hàng bên bảng B và ngược lại.
• One-to-Many Relationships hay quan hệ 1-n : trong mối quan hệ này thì một hàng bên bảng A có thể liên kết với nhiều hàng bên bảng B.
• Many-to-Many Relationships hay quan hệ n-n : trong mối quan hệ này thì một hàng bên bảng A có thể liên kết với nhiều hàng bên bảng B và một hàng bên bảng B cũng có thể liên kết với nhiều hàng bên bảng A. Như ta thấy trong ví dụ trên một cuốn sách có thể được viết bởi nhiều tác giả và một tác giả cũng có thể viết nhiều cuốn sách. Do đó mối quan hệ giữa Books và Authors là quan hệ Many to Many. Trong trường hợp này người ta thường dùng một bảng trung gian để giải quyết vấn đề (bảng AuthorBook).

Ðể có một CSDL tương đối hoàn hảo nghĩa là thiết kế sao cho dữ liệu chứa trong CSDL không dư thừa không thiếu bạn cần biết thêm về các thủ thuật Chuẩn hoá CSDL (3NF). Tuy nhiên trong phạm vi khóa học này chúng tôi không muốn bàn sâu hơn về đề tài này, bạn có thể xem thêm trong các sách dạy lý thuyết cơ sở dữ liệu.

Kết luận

Trong bài này tất cả chúng ta đã tìm hiểu và khám phá về cấu trúc của cơ sở dữ liệu SQL Server và những quan tâm khi phong cách thiết kế một cơ sở dữ liệu để bảo vệ tính toàn vẹn và tránh những yếu tố về dư thừa dữ liệu. Trong bài sau tất cả chúng ta sẽ bàn về Backup và Restore một CSDL .

Tham khảo

Source: https://dichvubachkhoa.vn
Category : Tư Vấn Sử Dụng