Computer >> Máy Tính >  >> Lập trình >> SQL Server

Tìm hiểu về kiến ​​trúc của MS SQL Server

Trong các bài viết trước, các bạn đã biết sơ qua về SQL Server, cách cài đặt SQL Server trên máy tính. Trong phần này, chúng ta sẽ tìm hiểu về kiến ​​trúc của SQL Server.

Chúng tôi sẽ chia nhỏ kiến ​​trúc SQL Server thành các phần sau để dễ hiểu hơn:

  1. Kiến trúc chung - Chung
  2. Kiến trúc bộ nhớ - Bộ nhớ
  3. Kiến trúc tệp dữ liệu - Tệp dữ liệu
  4. Kiến trúc tệp nhật ký - Tệp nhật ký

Bây giờ chúng ta sẽ đi vào chi tiết của từng loại kiến ​​trúc SQL Server được phân loại ở trên.

Tìm hiểu về kiến ​​trúc của MS SQL Server

Kiến trúc chung - Chung

  1. Khách hàng:The nơi yêu cầu được thực hiện.
  2. Truy vấn: Truy vấn SQL là ngôn ngữ cấp cao.
  3. Đơn vị lôgic: Từ khóa, biểu thức, toán tử,.
  4. Gói N / W: Mã liên quan đến mạng.
  5. Giao thức: Trong SQL Server, chúng tôi có 4 giao thức:
    1. Bộ nhớ dùng chung:Dành cho các kết nối cục bộ và mục đích khắc phục sự cố.
    2. Đường ống được đặt tên:Dành cho các kết nối trong mạng LAN.
    3. TCP / IP:Dành cho kết nối WAN.
    4. VIA-Bộ điều hợp Giao diện Ảo:Các yêu cầu phần cứng đặc biệt do nhà cung cấp đặt ra và không được phiên bản SQL 2012 hỗ trợ.
  6. Máy chủ: Nơi Dịch vụ SQL được cài đặt và có cơ sở dữ liệu.
  7. Công cụ Quan hệ: Đây là nơi mà việc thực thi thực sự sẽ được thực hiện. Nó chứa trình phân tích truy vấn, trình tối ưu hóa truy vấn và thời gian chạy truy vấn.
  8. Trình phân tích cú pháp truy vấn (Command Parser) và Trình biên dịch (Trình dịch):Những hai người chịu trách nhiệm kiểm tra cú pháp của truy vấn và chuyển đổi truy vấn sang ngôn ngữ của máy.
  9. Trình Tối ưu hoá Truy vấn: Nó sẽ chuẩn bị đầu ra dưới dạng Kế hoạch thực thi bằng cách lấy đầu vào dưới dạng truy vấn, thống kê và cây Algebrizer.
  1. Kế hoạch Thực hiện: Giống như bản đồ đường đi, chứa thứ tự các bước được thực hiện như một phần của quá trình thực hiện truy vấn.
  2. Người thực thi truy vấn: Đây là nơi truy vấn được thực thi từng bước, với sự trợ giúp của Kế hoạch thực thi và cũng là nơi liên hệ với Công cụ lưu trữ.
  3. Công cụ lưu trữ: Chịu trách nhiệm lưu trữ và truy xuất dữ liệu trong hệ thống lưu trữ (ổ đĩa, SAN,.), Xử lý dữ liệu, khóa và quản lý các giao dịch.
  4. Hệ điều hành SQL: Nằm giữa máy chủ (HĐH Windows) và SQL Server. Tất cả các hoạt động được thực hiện trên cơ sở dữ liệu được SQL OS "chăm sóc". SQL OS cung cấp các dịch vụ hệ điều hành khác nhau, chẳng hạn như quản lý bộ nhớ với vùng đệm, bộ đệm nhật ký, phát hiện khóa chết, sử dụng cấu trúc khối và khóa.
  5. Trạm kiểm soát: Checkpoint là một quá trình nội bộ, ghi tất cả các trang đã sửa đổi (được gọi là Dirty Page) từ Bộ đệm ẩn vào ổ đĩa vật lý. Ngoài ra, nó cũng ghi nhật ký từ Log Buffer vào tệp vật lý. Ghi các trang bẩn vào ổ đĩa còn được gọi là Quá trình làm cứng các trang bẩn.
  6. Người viết lười biếng: Lazy Writer sẽ push Dirty Pages và ổ cứng vì một lý do hoàn toàn khác, đó là giải phóng bộ nhớ trong Buffer Pool. Điều này xảy ra khi SQL Server sắp hết bộ nhớ. Quy trình này được kiểm soát bởi Quy trình nội bộ và không có cài đặt nào cho quy trình đó.

SQL Server liên tục giám sát việc sử dụng bộ nhớ để đánh giá tính khả dụng và cạnh tranh của tài nguyên, đảm bảo luôn có một lượng không gian trống nhất định. Khi phát hiện bất kỳ xung đột tài nguyên nào, nó sẽ kích hoạt Lazy Writer di chuyển một số Trang Bẩn vào ổ đĩa và giải phóng bộ nhớ. Nó sử dụng thuật toán Ít được sử dụng Gần đây (LRU) để xác định trang nào sẽ được đẩy vào ổ cứng. Nếu Lazy Writer luôn hoạt động, nó có thể tạo ra tắc nghẽn với bộ nhớ.

Kiến trúc bộ nhớ - Bộ nhớ

Sau đây là các đặc điểm nổi bật của kiến ​​trúc bộ nhớ:

  1. Một trong những mục tiêu thiết kế cơ bản của tất cả phần mềm cơ sở dữ liệu là giảm thiểu I / O trên đĩa vì quá trình đọc và ghi đĩa là một trong những hành động sử dụng nhiều tài nguyên nhất.
  2. Bộ nhớ trong của Windows có thể được gọi bằng Không gian địa chỉ ảo, được chia sẻ bởi chế độ Kernel (chế độ hệ điều hành) và Người dùng (ứng dụng như SQL Server).
  3. Không gian địa chỉ người dùng của SQL Server được chia thành hai phần:MemToLeave và Buffer Pool.
  4. Kích thước của MemToLeave (MTL) và Buffer Pool (BPool) do SQL Server quyết định trong quá trình khởi động.
  5. Quản lý bộ đệm là một thành phần quan trọng nếu bạn muốn đạt được hiệu suất I / O cao. Nó bao gồm hai cơ chế:Trình quản lý bộ đệm để truy cập và cập nhật cơ sở dữ liệu và các trang Bộ đệm để cắt các tệp I / O vào cơ sở dữ liệu.
  6. Nhóm đệm được chia thành nhiều phần, quan trọng nhất là Bộ đệm đệm và Bộ đệm thủ tục. Bộ đệm đệm lưu giữ các trang dữ liệu trong bộ nhớ để dữ liệu được truy cập thường xuyên có thể được trích xuất từ ​​bộ đệm. Quá trình thay thế sẽ đọc các trang dữ liệu từ ổ đĩa. Việc đọc dữ liệu từ bộ nhớ đệm sẽ tối ưu hóa hiệu suất bằng cách giảm thiểu số lượng các thao tác nhập / xuất chậm hơn so với việc truy cập dữ liệu từ bộ nhớ.
  7. Bộ đệm thủ tục lưu giữ các thủ tục được lưu trữ và kế hoạch loại trừ để tối ưu hóa số lần kế hoạch thực thi được tạo. Bạn có thể tìm thông tin về dung lượng và hoạt động trong Bộ đệm thủ tục bằng lệnh DBCC PROCCACHE.
  8. Các phần khác của Nhóm đệm bao gồm:
    1. Cấu trúc dữ liệu cấp hệ thống:Chứa dữ liệu cấp Phiên bản về cơ sở dữ liệu và khóa.
    2. Log Cache:Dành riêng cho việc đọc và ghi các trang giao dịch.
    3. Bối cảnh kết nối:Mỗi kết nối với Instance có một vùng bộ nhớ nhỏ để ghi lại trạng thái hiện tại của kết nối. Thông tin này bao gồm các thủ tục được lưu trữ và các tham số chức năng do người dùng xác định, vị trí con trỏ chuột và hơn thế nữa.
    4. Không gian ngăn xếp:Windows phân bổ không gian ngăn xếp cho mỗi luồng bắt đầu với SQL Server.

Kiến trúc tệp dữ liệu - Tệp dữ liệu

Kiến trúc này có các thành phần sau:

Nhóm Tệp:

Các tệp cơ sở dữ liệu có thể được nhóm lại với nhau thành các nhóm tệp cho mục đích phân phối và quản lý. Một tệp chỉ có thể là thành viên của một nhóm tệp. Không thể nhóm tệp nhật ký thành Nhóm tệp vì kích thước tệp nhật ký được quản lý riêng biệt với khối lượng dữ liệu.

Có hai loại Nhóm tệp trong SQL Server là Chính và Do người dùng xác định. Chính chứa các tệp dữ liệu chính và bất kỳ tệp nào không được chỉ định cụ thể cho Nhóm Tệp. Tất cả các trang cho bảng hệ thống được phân bổ trong chính. Người dùng xác định là nhóm tệp do người dùng xác định, được chỉ định bằng cách sử dụng nhóm tệp file group từ khóa trong lệnh để tạo cơ sở dữ liệu hoặc xóa cơ sở dữ liệu.

Một Nhóm Tệp trong mỗi cơ sở dữ liệu hoạt động như một nhóm tệp mặc định. Khi SQL Server gán một trang cho một bảng hoặc chỉ mục (không nằm trong bất kỳ Nhóm tệp nào khi tạo), trang đó sẽ nằm trong nhóm tệp mặc định. Để chuyển đổi nhóm tệp mặc định từ Nhóm tệp sang Nhóm tệp khác, vai trò cơ sở dữ liệu cố định db_owner là bắt buộc.

Chính là nhóm tệp mặc định. Người dùng cần có vai trò cơ sở dữ liệu cố định db_owner để sao lưu tệp và nhóm tệp riêng lẻ.

Tệp

Cơ sở dữ liệu có 3 loại tệp Primary (tệp dữ liệu chính), Secondary (tệp dữ liệu phụ) và Log (tệp nhật ký). Chính là điểm bắt đầu của cơ sở dữ liệu và trỏ đến các tệp khác trong cơ sở dữ liệu.

Mỗi cơ sở dữ liệu có một Cơ sở dữ liệu chính. Bạn cũng có thể đặt phần mở rộng cho tệp dữ liệu chính, nhưng khuyến nghị là .mdf. Tệp dữ liệu phụ là tệp khác với tệp dữ liệu chính. Một cơ sở dữ liệu có thể có nhiều hoặc chỉ một tệp dữ liệu bổ sung. Phần mở rộng cho tệp dữ liệu bổ sung phải được đặt thành .ndf.

Tệp nhật ký giữ tất cả thông tin được sử dụng để khôi phục cơ sở dữ liệu. Cơ sở dữ liệu phải có ít nhất một tệp nhật ký. Chúng tôi có thể có nhiều tệp nhật ký cho một cơ sở dữ liệu. Phần mở rộng phải được đặt thành .ldf.

Vị trí của tất cả các tệp trong cơ sở dữ liệu được ghi lại trong cả cơ sở dữ liệu chính và tệp chính của cơ sở dữ liệu. Trong hầu hết các trường hợp, công cụ cơ sở dữ liệu sử dụng vị trí tệp từ cơ sở dữ liệu chính.

Tệp có hai tên:Lôgic và Vật lý. Logical được sử dụng để tham chiếu đến tệp trong tất cả các lệnh T-SQL. Tên vật lý là OS_file_name, nó phải tuân theo các quy tắc của hệ điều hành. Tệp dữ liệu và tệp nhật ký có thể được đặt trên hệ thống tệp FAT hoặc NTFS, nhưng không thể đặt trên hệ thống tệp nén. Có thể có tới 32.767 tệp trong cơ sở dữ liệu.

Mức độ

Extent là đơn vị cơ bản trong đó không gian được phân bổ cho mỗi bảng và chỉ mục. Mỗi Extent là 8 trang liền kề hoặc 64KB. SQL Server có 2 loại Extent là Uniform và Mixed. Đồng phục được tạo thành từ một đối tượng hỗn hợp duy nhất được tạo thành từ tối đa 8 đối tượng.

Trang

Trang (trang) là đơn vị cơ bản trong lưu trữ dữ liệu của SQL Server. Kích thước của một trang là 8KB. Bắt đầu mỗi trang là 96byte tiêu đề, được sử dụng để lưu trữ thông tin hệ thống như loại trang, dung lượng trống trên trang và ID của chủ sở hữu trang. Có 9 loại trang dữ liệu trong SQL Server:

  1. Dữ liệu:Các hàng dữ liệu với tất cả dữ liệu từ văn bản, văn bản và hình ảnh.
  2. Chỉ mục:Chỉ mục các mục.
  3. TexImage:Dữ liệu văn bản, văn bản và hình ảnh.
  4. GAM:Thông tin về phạm vi được chỉ định.
  5. SGAM:Thông tin về phạm vi được phân bổ ở cấp hệ thống.
  6. Dung lượng trống của trang (PFS):Thông tin về dung lượng trống có sẵn trên các trang.
  7. Bản đồ phân bổ chỉ mục (IAM):Thông tin về phạm vi được sử dụng bởi các bảng hoặc chỉ mục.
  8. Bản đồ được thay đổi hàng loạt (BCM):Phạm vi thông tin được thay đổi bằng thao tác hàng loạt từ lệnh sao lưu cuối cùng.
  9. Bản đồ thay đổi khác biệt (DCM):Thông tin về phạm vi đã thay đổi kể từ lệnh sao lưu cơ sở dữ liệu cuối cùng.

Kiến trúc tệp nhật ký - Tệp nhật ký

Nhật ký giao dịch trên SQL Server hoạt động bình thường khi nó là một chuỗi các bản ghi nhật ký. Mỗi nhật ký được xác định bằng Số thứ tự nhật ký (LSN), chứa ID của giao dịch mà nó thuộc về.

Nhật ký ghi lại các sửa đổi hoặc hoạt động dữ liệu được thực hiện hoặc truy xuất hình ảnh trước và sau khi dữ liệu được chỉnh sửa. Hình ảnh trước là bản sao dữ liệu trước khi thực hiện thao tác, hình ảnh sau là bản sao dữ liệu sau khi thao tác đã được thực hiện.

Các bước khôi phục thao tác phụ thuộc vào loại nhật ký.

  1. Hoạt động logic đã được ghi lại.
  2. Để chuyển sang thao tác logic trước đó, thao tác sẽ được thực hiện lại.
  3. Để quay lại thao tác logic phía sau, thao tác logic ngược sẽ được thực hiện.
  4. Hình ảnh trước đó và hình ảnh tiếp theo được ghi lại.
  5. Để chuyển sang thao tác trước đó, hình ảnh sau sẽ được áp dụng.
  6. Để quay lại thao tác sau, hình ảnh trước đó sẽ được áp dụng.

Các thao tác khác nhau đã được ghi lại trong nhật ký giao dịch. Các thao tác sau sẽ khả dụng ở đó:

  1. Bắt đầu và kết thúc mỗi giao dịch.
  2. Tất cả các sửa đổi dữ liệu (chèn, cập nhật, xóa), bao gồm các thay đổi đối với quy trình lưu trữ hệ thống hoặc lệnh ngôn ngữ định nghĩa dữ liệu (DDL) đối với bảng, bao gồm cả bảng hệ thống.
  3. Tất cả mức độ và sự phân bổ, hủy bỏ việc phân bổ trang.
  4. Tạo hoặc xóa các bảng và chỉ mục.

Thao tác khôi phục cũng được ghi lại. Mỗi giao dịch sẽ giữ một khoảng trống trong nhật ký để đảm bảo có đủ không gian nhật ký cần thiết cho việc khôi phục để thực hiện lệnh hoặc thông báo lỗi. Không gian này sẽ được giải phóng khi giao dịch hoàn tất.

Phần của tệp nhật ký từ nhật ký đầu tiên (bắt buộc để khôi phục thành công toàn bộ cơ sở dữ liệu) đến nhật ký cuối cùng được gọi là phần nhật ký hoạt động hoặc nhật ký hoạt động. Đây là phần nhật ký bắt buộc để khôi phục toàn bộ cơ sở dữ liệu. Không có phần nào của nhật ký hoạt động bị cắt xén. LSN của bản ghi nhật ký đầu tiên được gọi là LSN khôi phục tối thiểu (Min LSN).

SQL Server Database Engine chia mỗi tệp nhật ký Vật lý thành nhiều tệp nhật ký ảo. Tệp nhật ký ảo có kích thước không cố định và không có số tệp nhật ký ảo cố định cho mỗi tệp nhật ký Vật lý.

Database Engine tự động chọn kích thước của tệp nhật ký ảo khi nó tạo hoặc mở rộng tệp nhật ký. Cơ sở dữ liệu cố gắng duy trì một số lượng nhỏ các tệp ảo. Kích thước của tệp nhật ký ảo không thể được định cấu hình hoặc thiết lập bởi quản trị viên. Chỉ khi tệp nhật ký Vật lý được xác định ở kích thước nhỏ và giá trị grow_increment, tệp nhật ký ảo mới ảnh hưởng đến hiệu suất hệ thống.

Giá trị kích thước là kích thước ban đầu của tệp nhật ký và growth_increment là dung lượng được thêm vào tệp mỗi khi tệp yêu cầu dung lượng mới. Khi tệp nhật ký đạt đến kích thước lớn vì có nhiều gia số nhỏ, chúng sẽ có nhiều tệp nhật ký ảo. Điều này có thể làm chậm quá trình khởi động cơ sở dữ liệu cũng như các hoạt động sao lưu và khôi phục nhật ký.

Lời khuyên là bạn nên gán giá trị kích thước cho tệp nhật ký gần với kích thước cuối cùng được yêu cầu và giá trị growth_increment tương đối lớn. SQL Server sử dụng nhật ký ghi trước (WAL), đảm bảo rằng không có sửa đổi nào đối với dữ liệu được ghi vào ổ đĩa trước khi nhật ký liên quan được ghi vào ổ đĩa. Điều này giúp duy trì ACID cho các thuộc tính giao dịch.

Algebrizer trong SQL

Tôi muốn nói một chút về Algebrizer:Algebrizer là một tiến trình trong quá trình thực thi truy vấn. Nó bắt đầu hoạt động sau khi phân tích cú pháp. Khi Query Parser tìm thấy một truy vấn chính xác, nó sẽ chuyển đến Algebrizer và công việc của Algebrizer bắt đầu:. Algebrizer chịu trách nhiệm xác minh các đối tượng và tên cột (mà bạn đã cung cấp trong truy vấn hoặc được tham chiếu bởi truy vấn). Ví dụ:nếu tên cột bị viết nhầm trong truy vấn, Algebrizer có trách nhiệm xác nhận nó và tạo ra lỗi. Algebrizer cũng xác định tất cả các loại dữ liệu đang được xử lý trong một truy vấn nhất định. Algebrizer xác minh xem GROUP BY và các cột kết hợp có được đặt ở đúng vị trí hay không. Ví dụ:nếu bạn viết truy vấn sau và chỉ nhấn Ctrl + F5 để phân tích cú pháp, sẽ không có lỗi. Nhưng nếu bạn nhấn F5 để chạy truy vấn, Algebrizer sẽ hoạt động và trả về lỗi. 

 USE AdventureWorks 
 GO 
 SELECT MakeFlag,SUM(ListPrice) 
 FROM Production.Product 
  GROUP BY ProductNumber

Trạm kiểm soát trong SQL Server

Có 4 loại điểm kiểm tra trong SQL Server 2012:

  1. Tự động: Loại này là điểm kiểm tra phổ biến nhất, chạy dưới dạng quy trình nền để đảm bảo rằng Cơ sở dữ liệu máy chủ SQL có thể được khôi phục trong thời gian được chỉ định bởi Khoảng thời gian phục hồi trong Tùy chọn cấu hình máy chủ.
  2. Gián tiếp: Điểm kiểm tra này chỉ khả dụng trên SQL Server 2012. Đây cũng là một quá trình chạy nền nhưng chỉ dành cho một số người dùng nhất định chỉ định thời gian khôi phục cho một cơ sở dữ liệu cụ thể trong các tùy chọn cấu hình. Khi Target_Recovery_Time cho một cơ sở dữ liệu cụ thể được chọn, nó sẽ ghi đè Khoảng thời gian khôi phục được chỉ định cho máy chủ, tránh điểm kiểm tra Tự động trên cơ sở dữ liệu.
  3. Thủ công: Checkpoint này chạy giống như mọi lệnh SQL khác, khi bạn tạo lệnh checkpoint thì nó sẽ chạy để hoàn thành. Trạm kiểm soát này chỉ chạy trên cơ sở dữ liệu hiện tại. Bạn có thể chỉ định Checkpoint_Duration trong tùy chọn để chỉ định khoảng thời gian bạn muốn hoàn thành điểm kiểm tra.
  4. Nội bộ: Là người dùng, bạn không thể kiểm soát loại điểm kiểm tra này trong các hành động cụ thể như:
    1. Tắt tác vụ điểm kiểm tra trên tất cả cơ sở dữ liệu trừ khi quá trình tắt không thành công, không bình thường (sử dụng Shutdown với lệnh now with).
    2. Khi mô hình khôi phục được thay đổi từ FullBulk-log thành Đơn giản.
    3. Trong khi sao lưu cơ sở dữ liệu.
    4. Nếu cơ sở dữ liệu đang ở chế độ khôi phục Đơn giản, quá trình điểm kiểm tra sẽ tự động thực hiện hoặc khi nhật ký đầy 70% hoặc dựa trên tùy chọn Khoảng thời gian khôi phục của máy chủ.
    5. Lệnh ALTER DATABASE để thêm hoặc xóa các tệp nhật ký / dữ liệu cũng bắt đầu một điểm kiểm tra.
    6. Checkpoint cũng diễn ra khi mô hình khôi phục cơ sở dữ liệu được ghi nhật ký hàng loạt và thao tác ghi tối thiểu được thực hiện.

Đây có lẽ là phần "ngọt ngào" nhất của SQL Server, nhưng ý tưởng hiểu được kiến ​​trúc của nó sẽ giúp hiểu được mọi thứ hoạt động như thế nào, nếu có lỗi thì phần nào ,. Điều này giúp làm việc với cơ sở dữ liệu dễ dàng hơn.

Trong phần tiếp theo, chúng ta sẽ tìm hiểu về Management Studio và dần dần đi sâu vào các lệnh cơ bản của SQL Server.