SQL server Archikiến trúc (Giải thích)
Fiona Nâu
⚡ Tóm tắt thông minh
SQL server ArchiKiến trúc này tuân theo mô hình máy khách-máy chủ được tổ chức thành ba lớp cốt lõi: Lớp giao thức để giao tiếp mạng, Công cụ quan hệ để xử lý truy vấn và Công cụ lưu trữ để quản lý và truy xuất dữ liệu.
MS SQL Server sử dụng kiến trúc máy chủ-máy khách. Quá trình hoạt động của MS SQL Server bắt đầu khi ứng dụng máy khách gửi yêu cầu. SQL Server chấp nhận, xử lý và trả lời yêu cầu bằng dữ liệu đã được xử lý. Chúng ta hãy cùng thảo luận chi tiết về toàn bộ kiến trúc được thể hiện bên dưới:
Như sơ đồ bên dưới minh họa, SQL Server có ba thành phần chính. Archikiến trúc:
- Lớp giao thức
- Công cụ quan hệ
- Công cụ lưu trữ
Lớp giao thức – SNI
Lớp giao thức SQL Server, còn được gọi là Giao diện mạng máy chủ (SNI), hỗ trợ ba loại kiến trúc máy khách-máy chủ. Mỗi giao thức phục vụ một kịch bản mạng khác nhau. Hiểu rõ các giao thức này là điều cần thiết trước khi tìm hiểu cách các truy vấn được xử lý bên trong.
Bộ nhớ dùng chung
Hãy xem xét một tình huống hội thoại vào sáng sớm. Tom và mẹ anh ấy đang ở cùng một địa điểm, nhà của họ. Tom yêu cầu cà phê và mẹ anh ấy pha ngay lập tức. Tương tự, SQL Server cung cấp giao thức Bộ nhớ dùng chung khi máy khách và máy chủ chạy trên cùng một máy. Cả hai giao tiếp thông qua bộ nhớ dùng chung mà không cần bất kỳ chi phí mạng nào.
Sự giống nhau: Tom tương ứng với Client, Mom tương ứng với SQL Server, Home tương ứng với Machine, và giao tiếp bằng lời nói tương ứng với giao thức Bộ nhớ chia sẻ.
Ghi chú cấu hình: In Xưởng quản lý SQLTùy chọn “Tên máy chủ” cho kết nối cục bộ có thể là “.”, “localhost”, “127.0.0.1” hoặc “Machine\Instance”.
TCP / IP
Bây giờ hãy xem xét trường hợp Tom muốn mua cà phê từ một cửa hàng cách đó 10 km. Tom đang ở nhà và cửa hàng cà phê nằm ở một khu chợ đông đúc. Họ liên lạc với nhau qua mạng di động. Tương tự, SQL Server cung cấp... Giao thức TCP / IP khi máy khách và máy chủ SQL Server nằm trên các máy riêng biệt được kết nối qua mạng.
Sự giống nhau: Tom được liên kết với máy khách, quán cà phê được liên kết với máy chủ SQL Server, nhà riêng và chợ được liên kết với các địa điểm từ xa, và mạng di động được liên kết với giao thức TCP/IP.
Ghi chú cấu hình: Trong SQL Management Studio, tùy chọn “Tên máy chủ” cho kết nối TCP/IP phải là “Máy/Phiên bản của máy chủ”. SQL Server sử dụng cổng 1433 theo mặc định cho các kết nối TCP/IP.
Ống được đặt tên
Cuối cùng, Tom muốn xin trà xanh từ người hàng xóm Sierra. Họ ở cùng một vị trí địa lý, là hàng xóm của nhau, và giao tiếp thông qua mạng nội bộ. Tương tự, SQL Server cung cấp giao thức Named Pipe khi máy khách và máy chủ được kết nối qua mạng cục bộ (LAN).
Sự giống nhau: Tom tương ứng với máy khách, Sierra tương ứng với SQL Server, các thiết bị lân cận tương ứng với mạng LAN, và mạng nội bộ tương ứng với giao thức Named Pipe.
Ghi chú cấu hình: Named Pipes bị vô hiệu hóa theo mặc định và phải được kích hoạt thông qua SQL Configuration Manager.
TDS là gì?
Giờ đây, khi ba loại kiến trúc máy chủ-máy khách đã được làm rõ, hãy cùng tìm hiểu về TDS:
- TDS là viết tắt của Luồng dữ liệu dạng bảng.
- Cả ba giao thức đều sử dụng gói dữ liệu TDS.
- TDS được đóng gói trong các gói mạng, cho phép truyền dữ liệu từ máy khách đến máy chủ.
- TDS được phát triển lần đầu bởi Sybase và hiện thuộc sở hữu của... Microsoft.
Bảng sau đây so sánh ba giao thức kết nối của SQL Server:
| Tính năng | Bộ nhớ dùng chung | TCP / IP | Ống được đặt tên |
|---|---|---|---|
| Phạm vi mạng | Cùng một máy | Từ xa (WAN/Internet) | Chỉ mạng LAN |
| Cổng mặc định | N/A | 1433 | 445 |
| HIỆU QUẢ | Nhanh nhất (không tốn tài nguyên mạng) | Tốt (tối ưu hóa cho WAN) | Tốt (đã tối ưu hóa cho mạng LAN) |
| Được bật theo mặc định | Có | Có | Không |
| Trường hợp sử dụng tốt nhất | Phát triển và thử nghiệm tại địa phương | Truy cập từ xa trong sản xuất | Môi trường mạng LAN đáng tin cậy |
Với Lớp Giao thức xử lý việc truyền thông mạng, bước tiếp theo trong kiến trúc SQL Server là xử lý chính truy vấn. Đây là lúc Công cụ Quan hệ (Relational Engine) đảm nhiệm.
Công cụ quan hệ
Công cụ quan hệ (Relational Engine) còn được gọi là Bộ xử lý truy vấn (Query Processor). Nó chứa các thành phần của SQL Server xác định những gì một truy vấn cần làm và cách thức thực thi hiệu quả nhất. Nó chịu trách nhiệm thực thi các truy vấn của người dùng bằng cách yêu cầu dữ liệu từ Công cụ lưu trữ (Storage Engine) và xử lý các kết quả trả về.
Như được thể hiện trong sơ đồ kiến trúc, có ba thành phần chính của Công cụ Quan hệ:
Trình phân tích cú pháp CMD
Dữ liệu nhận được từ Lớp Giao thức được chuyển đến Bộ xử lý Quan hệ. Trình phân tích cú pháp CMD là thành phần đầu tiên nhận dữ liệu truy vấn. Nhiệm vụ chính của nó là kiểm tra truy vấn xem có lỗi cú pháp và ngữ nghĩa hay không, sau đó tạo ra cây truy vấn.
Kiểm tra cú pháp: Giống như mọi ngôn ngữ lập trình khác, SQL Server có một tập hợp các từ khóa và quy tắc ngữ pháp được định sẵn. SELECT, INSERT, UPDATE và nhiều từ khóa khác thuộc danh sách từ khóa được định sẵn. Trình phân tích cú pháp CMD sẽ kiểm tra xem đầu vào có tuân theo các quy tắc này hay không. Nếu đầu vào của người dùng khác với cú pháp dự kiến, trình phân tích cú pháp sẽ trả về lỗi.
Ví dụ: Hãy tưởng tượng một người Nga bước vào một nhà hàng Nhật Bản và gọi món bằng tiếng Nga. Người phục vụ chỉ hiểu tiếng Nhật và không thể xử lý đơn đặt hàng. Tương tự, nếu người dùng gõ “SELECR” thay vì “SELECT”, trình phân tích cú pháp CMD sẽ trả về lỗi vì nó không nhận ra từ khóa này.
Kiểm tra ngữ nghĩa: Quá trình này được thực hiện bởi Normalizer. Nó kiểm tra xem tên cột, tên bảng và các đối tượng khác được truy vấn có thực sự tồn tại trong lược đồ hay không. Nếu chúng tồn tại, Normalizer sẽ liên kết chúng với truy vấn. Quá trình này cũng được gọi là Liên kết (Binding). Khi các truy vấn của người dùng chứa một VIEW, Normalizer sẽ thay thế nó bằng định nghĩa view được lưu trữ nội bộ.
Ví dụ: Chạy SELECT * from USER_ID Điều này sẽ khiến trình phân tích cú pháp báo lỗi trong quá trình kiểm tra ngữ nghĩa nếu bảng USER_ID không tồn tại trong cơ sở dữ liệu.
Tạo cây truy vấn: Bước này tạo ra các cây thực thi khác nhau, thể hiện các cách khác nhau để chạy một truy vấn. Tất cả các cây đều tạo ra cùng một kết quả mong muốn.
Trình tối ưu hóa
Trình tối ưu hóa tạo ra một kế hoạch thực thi cho truy vấn của người dùng. Kế hoạch này xác định cách truy vấn sẽ được thực thi. Không phải tất cả các truy vấn đều được tối ưu hóa. Việc tối ưu hóa áp dụng cho các lệnh DML (Ngôn ngữ sửa đổi dữ liệu) như SELECT, INSERT, DELETE và UPDATE. Các lệnh DDL như CREATE và ALTER không được tối ưu hóa mà được biên dịch thành một dạng nội bộ.
Chi phí truy vấn được tính toán dựa trên các yếu tố như mức sử dụng CPU, mức sử dụng bộ nhớ và nhu cầu nhập/xuất dữ liệu. Vai trò của Trình tối ưu hóa là tìm ra kế hoạch thực thi hiệu quả về chi phí và tiết kiệm nhất, chứ không nhất thiết phải là kế hoạch tốt nhất tuyệt đối.
Ví dụ: Hãy tưởng tượng bạn muốn mở một tài khoản ngân hàng trực tuyến. Một ngân hàng mất tối đa 2 ngày. Bạn cũng có một danh sách 20 ngân hàng khác, có thể mất ít hoặc nhiều thời gian hơn. Tìm kiếm trong tất cả 20 ngân hàng có thể không tìm thấy lựa chọn nào nhanh hơn, và bản thân việc tìm kiếm cũng tốn thời gian. Sẽ tốt hơn nếu chọn ngân hàng đầu tiên. Tương tự, Trình tối ưu hóa SQL sử dụng các thuật toán toàn diện và thuật toán phỏng đoán để giảm thiểu thời gian thực thi truy vấn.
Công cụ tối ưu hóa tìm kiếm theo ba giai đoạn:
Giai đoạn 0: Tìm kiếm kế hoạch đơn giản
Đây là giai đoạn tiền tối ưu hóa. Đối với một số truy vấn, chỉ tồn tại một kế hoạch thực thi khả thi duy nhất, được gọi là kế hoạch đơn giản. Không cần thiết phải tìm kiếm thêm vì bất kỳ tìm kiếm bổ sung nào cũng sẽ tìm thấy cùng một kế hoạch thực thi với chi phí phát sinh.
Giai đoạn 1: Tìm kiếm các kế hoạch xử lý giao dịch
Quá trình này bao gồm tìm kiếm cả các kế hoạch đơn giản và phức tạp. Tìm kiếm kế hoạch đơn giản sử dụng phân tích thống kê dữ liệu cột và chỉ mục, thường chỉ giới hạn ở một chỉ mục trên mỗi bảng. Nếu không tìm thấy kế hoạch đơn giản nào, một tìm kiếm phức tạp hơn liên quan đến nhiều chỉ mục trên mỗi bảng sẽ được thực hiện.
Giai đoạn 2: Xử lý song song và tối ưu hóa
Nếu các chiến lược trước đó không tạo ra một kế hoạch phù hợp, Trình tối ưu hóa sẽ tìm kiếm các khả năng xử lý song song dựa trên khả năng xử lý của máy. Nếu xử lý song song không khả thi, một giai đoạn tối ưu hóa cuối cùng sẽ bắt đầu, sử dụng tất cả các tùy chọn còn lại để tìm ra kế hoạch thực thi tốt nhất có thể.
Trình thực thi truy vấn
Trình thực thi truy vấn gọi phương thức truy cập trong công cụ lưu trữ. Nó cung cấp một kế hoạch thực thi chứa logic tìm nạp dữ liệu cần thiết cho quá trình thực thi. Sau khi dữ liệu được nhận từ công cụ lưu trữ, kết quả được công bố lên lớp giao thức và gửi đến người dùng cuối.
Sau khi bộ xử lý quan hệ xác định cách thực thi truy vấn, bộ xử lý lưu trữ sẽ xử lý các thao tác dữ liệu vật lý. Lớp này quản lý cách dữ liệu được lưu trữ, lưu vào bộ nhớ đệm và truy xuất từ đĩa.
Công cụ lưu trữ
Bộ xử lý lưu trữ (Storage Engine) chịu trách nhiệm lưu trữ dữ liệu trong hệ thống lưu trữ như ổ đĩa hoặc SAN và truy xuất dữ liệu khi cần thiết. Trước khi xem xét các thành phần của Bộ xử lý lưu trữ, điều quan trọng là phải hiểu cách dữ liệu được lưu trữ vật lý.
Tệp dữ liệu và phạm vi
Các tập tin dữ liệu lưu trữ dữ liệu vật lý dưới dạng các trang dữ liệu, mỗi trang có kích thước 8KB. Đây là đơn vị lưu trữ nhỏ nhất trong... SQL serverCác trang dữ liệu được nhóm lại một cách logic thành các vùng nhớ (extent). Không có đối tượng nào được gán trực tiếp một trang riêng lẻ; thay vào đó, việc bảo trì được thực hiện thông qua các vùng nhớ. Mỗi trang có một tiêu đề trang (96 byte) chứa siêu dữ liệu như loại trang, số trang, dung lượng đã sử dụng, dung lượng trống và con trỏ đến trang tiếp theo và trang trước đó.
Các loại tập tin
Tệp chính: Mỗi cơ sở dữ liệu đều chứa một tệp chính. Tệp này lưu trữ tất cả dữ liệu quan trọng liên quan đến các bảng, khung nhìn, trình kích hoạt và các đối tượng khác. Phần mở rộng thường là .mdf nhưng cũng có thể là bất kỳ phần mở rộng nào khác.
Tệp phụ: Một cơ sở dữ liệu có thể chứa hoặc không chứa nhiều tệp phụ. Các tệp phụ này là tùy chọn và chứa dữ liệu dành riêng cho người dùng. Phần mở rộng thường là .ndf nhưng có thể là bất kỳ phần mở rộng nào khác.
Tệp nhật ký: Còn được gọi là nhật ký ghi trước (Write-Ahead Logs). Phần mở rộng là .ldf. Các tệp nhật ký được sử dụng để quản lý giao dịch, khôi phục từ các trường hợp không mong muốn và thực hiện hoàn tác các giao dịch chưa được cam kết.
Hệ thống lưu trữ (Storage Engine) có ba thành phần chính. Mỗi thành phần đóng một vai trò cụ thể trong việc quản lý quyền truy cập và tính toàn vẹn dữ liệu.
Phương thức truy cập
Phương thức truy cập đóng vai trò là giao diện giữa Trình thực thi truy vấn và... Buffer Trình quản lý hoặc nhật ký giao dịch. Nó không tự thực thi mà chỉ xác định loại truy vấn:
- Nếu truy vấn là một Câu lệnh SELECT (DML)nó được chuyển cho Buffer Quản lý quá trình xử lý tiếp theo.
- Nếu truy vấn là một Câu lệnh không phải SELECT (DDL và DML)Thông tin này được chuyển đến Trình quản lý giao dịch. Điều này chủ yếu bao gồm các câu lệnh UPDATE, INSERT và DELETE.
Buffer Quản Lý
Buffer Trình quản lý đảm nhiệm các chức năng cốt lõi cho bộ nhớ đệm kế hoạch, phân tích dữ liệu và xử lý trang thay đổi.
Lập kế hoạch bộ đệm
Kế hoạch truy vấn hiện có: Buffer Trình quản lý kiểm tra xem kế hoạch thực thi có tồn tại trong bộ nhớ đệm kế hoạch đã lưu trữ hay không. Nếu có, kế hoạch truy vấn đã được lưu trong bộ nhớ đệm và bộ nhớ đệm dữ liệu liên quan sẽ được sử dụng trực tiếp.
Kế hoạch tìm kho báu lần đầu: Nếu kế hoạch thực thi truy vấn lần đầu phức tạp, nó sẽ được lưu trữ trong Bộ nhớ đệm kế hoạch (Plan Cache). Điều này đảm bảo tốc độ truy cập nhanh hơn vào lần tiếp theo khi SQL Server nhận được cùng một truy vấn.
Phân tích cú pháp dữ liệu: Buffer Bộ nhớ đệm và lưu trữ dữ liệu
Buffer Trình quản lý cung cấp quyền truy cập vào dữ liệu cần thiết. Có hai cách tiếp cận khả thi tùy thuộc vào việc dữ liệu có tồn tại trong bộ nhớ đệm hay không:
Buffer Bộ nhớ đệm – Phân tích cú pháp mềm
Buffer Người quản lý tìm kiếm dữ liệu trong Buffer Bộ nhớ đệm. Nếu dữ liệu có sẵn, Trình thực thi truy vấn sẽ sử dụng trực tiếp dữ liệu đó. Điều này giúp cải thiện hiệu suất vì việc lấy dữ liệu từ bộ nhớ đệm yêu cầu ít thao tác I/O hơn so với việc lấy dữ liệu từ bộ nhớ đĩa.
Lưu trữ dữ liệu – Phân tích cú pháp phức tạp
Nếu dữ liệu không có trong Buffer Bộ nhớ đệm (cache) cho phép tìm kiếm dữ liệu cần thiết trong kho lưu trữ dữ liệu trên đĩa. Dữ liệu sau đó cũng được lưu trữ trong bộ nhớ đệm để sử dụng trong tương lai.
Người quản lý giao dịch
Trình quản lý giao dịch được gọi khi Phương thức truy cập xác định rằng một truy vấn không phải là câu lệnh SELECT. Nó đảm bảo tính nhất quán và độ bền của dữ liệu thông qua một số thành phần con:
Trình quản lý nhật ký
Trình quản lý nhật ký giữ track của tất cả các bản cập nhật được thực hiện trong hệ thống thông qua nhật ký được lưu trữ trong Nhật ký Giao dịch. Mỗi mục nhật ký chứa Số thứ tự Nhật ký cùng với ID Giao dịch và Bản ghi Sửa đổi Dữ liệu. Cơ chế này tracCác giao dịch đã được cam kết và hoàn tác của ks.
Trình quản lý khóa
Trong quá trình giao dịch, dữ liệu liên quan trong bộ nhớ sẽ chuyển sang trạng thái bị khóa. Trình quản lý khóa (Lock Manager) xử lý quá trình này, đảm bảo tính nhất quán và sự cô lập của dữ liệu. Các thuộc tính này cũng được gọi là ACID (Account, Computing ...Atombăng giá, tính nhất quán, sự cô lập, độ bền).
Quá trình thực hiện
Quy trình thực thi tuân theo các bước sau:
- Trình quản lý nhật ký bắt đầu ghi nhật ký và Trình quản lý khóa sẽ khóa dữ liệu liên quan.
- Một bản sao của dữ liệu được lưu giữ trong... Buffer Bộ nhớ cache.
- Một bản sao dữ liệu cần cập nhật được lưu giữ trong Nhật ký. Buffervà tất cả các sự kiện đều cập nhật dữ liệu trong Data Buffer.
- Các trang lưu trữ dữ liệu đã được sửa đổi được gọi là trang bẩn.
Điểm kiểm tra và ghi nhật ký trước
Quá trình kiểm tra điểm dừng (checkpoint) chạy khoảng một phút một lần và đánh dấu tất cả các trang dữ liệu chưa được ghi để lưu vào đĩa. Tuy nhiên, trang dữ liệu trước tiên được đẩy vào trang dữ liệu của tập tin nhật ký từ... Buffer Ghi nhật ký. Cơ chế này được gọi là Ghi nhật ký trước khi ghi (Write-Ahead Logging). Các trang dữ liệu chưa được ghi vẫn nằm trong bộ nhớ cache ngay cả sau khi đã được ghi vào đĩa.
Lười biếng Writer
Khi SQL Server nhận thấy tải trọng lớn và cần bộ nhớ đệm cho các giao dịch mới, nó sẽ giải phóng các trang dữ liệu đã thay đổi khỏi bộ nhớ cache. (Cơ chế "lười biếng" - Lazy) Writer Hoạt động dựa trên thuật toán LRU (Least Recently Used) để chuyển các trang từ bộ nhớ đệm sang đĩa.
Cách SQL Server xử lý một truy vấn từ đầu đến cuối
Hiểu rõ từng lớp riêng lẻ là điều quan trọng, nhưng việc nhìn thấy cách chúng hoạt động cùng nhau sẽ làm sáng tỏ bức tranh toàn cảnh. Khi một ứng dụng khách gửi truy vấn SQL, trình tự sau sẽ xảy ra:
Lớp giao thức Nhận yêu cầu thông qua Bộ nhớ chia sẻ, TCP/IP hoặc Đường ống được đặt tên và đóng gói nó vào một gói TDS. Công cụ quan hệ Sau đó, trình phân tích cú pháp CMD tiếp quản: trình phân tích cú pháp kiểm tra cú pháp và ngữ nghĩa, trình tối ưu hóa tạo ra kế hoạch thực thi tiết kiệm nhất, và trình thực thi truy vấn bắt đầu truy xuất dữ liệu.
Trình thực thi truy vấn gọi Công cụ lưu trữ của Phương thức truy cập, phương thức này định tuyến các truy vấn SELECT đến... Buffer Quản lý và các truy vấn chỉnh sửa đối với Trình quản lý giao dịch. Buffer Quản lý kiểm tra bộ nhớ đệm kế hoạch và Buffer Ưu tiên lưu vào bộ nhớ cache (phân tích cú pháp mềm). Nếu dữ liệu không được lưu vào bộ nhớ cache, hệ thống sẽ thực hiện đọc dữ liệu từ đĩa (phân tích cú pháp cứng). Đối với các thao tác ghi, Trình quản lý giao dịch phối hợp với Trình quản lý nhật ký, Trình quản lý khóa và quy trình điểm kiểm tra để đảm bảo tuân thủ ACID.
Sau khi Công cụ Lưu trữ trả về dữ liệu được yêu cầu, Công cụ Quan hệ sẽ định dạng tập kết quả, và Lớp Giao thức sẽ chuyển tiếp tập kết quả đó đến ứng dụng khách thông qua cùng một giao thức TDS.
Cách chọn giao thức phù hợp cho kết nối SQL Server
Việc lựa chọn giao thức phù hợp phụ thuộc vào mối quan hệ vật lý giữa máy khách và máy chủ, cũng như các yêu cầu về hiệu năng.
Sử dụng bộ nhớ dùng chung Khi ứng dụng khách chạy trên cùng một máy với SQL Server, đây là tùy chọn nhanh nhất vì nó loại bỏ tất cả chi phí mạng. Nó lý tưởng cho việc phát triển cục bộ, thử nghiệm và triển khai trên một máy duy nhất.
Sử dụng TCP/IP Khi máy khách và máy chủ nằm trên các máy khác nhau được kết nối qua mạng diện rộng (WAN) hoặc internet. Đây là giao thức được sử dụng phổ biến nhất trong môi trường sản xuất. Theo mặc định, SQL Server lắng nghe trên cổng 1433, và giao thức này hỗ trợ các kết nối được mã hóa thông qua TLS.
Sử dụng Named Pipes Khi máy khách và máy chủ nằm trên cùng một mạng LAN đáng tin cậy và hiệu suất trên mạng nội bộ là ưu tiên hàng đầu, Named Pipes bị vô hiệu hóa theo mặc định và phải được kích hoạt thông qua Trình quản lý cấu hình SQL Server. Tính năng này ít phổ biến hơn trong các triển khai hiện đại nhưng vẫn hữu ích cho các ứng dụng mạng nội bộ cũ.
