Nhảy tới nội dung

20 câu hỏi phỏng vấn SQL - Level Senior phần 1 ( từ câu 1 đến 5 )

· 13 phút để đọc
Phạm Quyết Thắng
Phạm Quyết Thắng
Fullstack developer

1. Window functions trong SQL là gì và chúng được sử dụng như thế nào?​

Window functions trong SQL là một nhóm các hàm mà bạn có thể áp dụng trên một "cửa sổ" (window) của dữ liệu, thường được xác định bởi một tập hợp các dòng liên quan đến mỗi dòng trong kết quả của truy vấn. Window functions thường sử dụng với các mệnh đề OVER để xác định phạm vi của cửa sổ.

Các window functions thường được sử dụng để thực hiện các tính toán so sánh giữa mỗi dòng và các dòng xung quanh nó, chẳng hạn như tính tổng tích lũy, tính hàng số dạng rank, tính tỷ lệ phần trăm, và nhiều tính toán khác.

Cú pháp cơ bản của window function như sau:​

SELECT
  column1,
  column2,
  window_function(column3) OVER (PARTITION BY partition_column ORDER BY order_column)
FROM
  table_name;
  • column1, column2: Các cột bạn muốn hiển thị trong kết quả.
  • window_function(column3): Window function bạn muốn áp dụng cho cột cụ thể.
  • PARTITION BY: Phân vùng dữ liệu thành các phạm vi riêng biệt (tùy chọn).
  • ORDER BY: Sắp xếp cửa sổ dữ liệu theo một hoặc nhiều cột (tùy chọn). Ví dụ:
-- Tính tổng tích lũy mức lương cho mỗi phòng ban
SELECT
  employee_id,
  department_id,
  salary,
  SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) AS cumulative_salary
FROM
  employees;

Trong ví dụ này, window function SUM(salary) OVER (PARTITION BY department_id ORDER BY employee_id) tính tổng tích lũy của mức lương theo thứ tự mã số nhân viên (employee_id) cho mỗi phòng ban (department_id).

Các window functions phổ biến bao gồm ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG(), MIN(), MAX(), và nhiều hàm khác. Sử dụng chúng để thực hiện tính toán thông tin so sánh giữa các dòng trong một cửa sổ xác định.

2. Giải thích khái niệm về phân mảnh cơ sở dữ liệu (Database sharding).​

Database sharding là một kỹ thuật trong quản lý cơ sở dữ liệu, nơi dữ liệu của một bảng lớn được phân chia thành nhiều phần nhỏ hơn được gọi là các "shard." Mỗi shard là một phần nhỏ của cơ sở dữ liệu và chứa một phạm vi dữ liệu cụ thể. Mục tiêu của database sharding là tăng cường khả năng mở rộng và hiệu suất của hệ thống cơ sở dữ liệu bằng cách phân chia dữ liệu và giao nhiệm vụ xử lý giữa các shard khác nhau.

Đặc điểm chính của database sharding:​

  1. Phân chia dữ liệu:
  • Dữ liệu của bảng lớn được chia thành các shard nhỏ hơn.
  • Mỗi shard chứa một phạm vi dữ liệu cụ thể, có thể dựa trên giá trị của một cột (ví dụ: theo giá trị của khóa chính) hoặc theo một phương pháp phân chia khác.
  1. Phân chia trách nhiệm:
  • Mỗi shard có trách nhiệm xử lý một phần của dữ liệu và thực hiện các truy vấn chỉ đối với dữ liệu trong phạm vi của mình.
  • Có thể có các shard chịu trách nhiệm cho việc ghi dữ liệu, trong khi các shard khác chỉ xử lý đọc dữ liệu.
  1. Tăng cường khả năng mở rộng:
  • Database sharding giúp tăng khả năng mở rộng của hệ thống bằng cách phân chia gánh nặng xử lý và lưu trữ cho nhiều shard khác nhau.
  • Không giống như kiến trúc không sharded, khi lưu trữ toàn bộ dữ liệu trong một cơ sở dữ liệu, sharding cho phép mỗi shard có thể chạy độc lập với các shard khác.
  1. Hiệu suất cải thiện:
  • Với database sharding, mỗi shard có thể có hiệu suất riêng của mình, giảm gánh nặng xử lý và cải thiện thời gian đáp ứng cho các truy vấn.
  1. Quản lý dữ liệu phân tán:
  • Quản lý và duy trì cơ sở dữ liệu trên nhiều shard đòi hỏi quản lý phức tạp hơn, nhưng đồng thời giúp phân tán dữ liệu và giảm độ trễ khi truy xuất dữ liệu từ xa.

Thách thức và cân nhắc:​

  1. Đồng bộ hóa dữ liệu:
  • Cần cân nhắc đến cách đồng bộ hóa dữ liệu giữa các shard để đảm bảo tính nhất quán.
  1. Chọn phương pháp phân chia:
  • Phương pháp phân chia dữ liệu cần được chọn cẩn thận để tránh tình trạng "hot spot" (một shard phải xử lý nhiều dữ liệu hơn so với các shard khác).
  1. Quản lý metadata:
  • Metadata của các shard cần được quản lý một cách hiệu quả để theo dõi trạng thái và vị trí của dữ liệu phân chia.

Database sharding là một giải pháp phức tạp và thường được triển khai khi cần mở rộng quy mô cơ sở dữ liệu để đáp ứng yêu cầu tăng trưởng lớn.

3. Làm thế nào để bạn thiết kế một cơ sở dữ liệu hiệu quả?​

Thiết kế một cơ sở dữ liệu hiệu quả đòi hỏi sự cân nhắc kỹ lưỡng về nhu cầu và yêu cầu của ứng dụng cũng như về cách dữ liệu sẽ được truy xuất và cập nhật. Dưới đây là một số bước để thiết kế một schema cơ sở dữ liệu hiệu quả:

1. Hiểu Rõ Yêu Cầu Ứng Dụng:​

  • Đảm bảo bạn hiểu rõ yêu cầu của ứng dụng và cách mà dữ liệu sẽ được sử dụng. Điều này giúp xác định các mối quan hệ giữa các thực thể và thuộc tính cần thiết.

2. Normaliz hóa Dữ liệu:​

  • Áp dụng các nguyên tắc của normalization để giảm thiểu sự trùng lặp dữ liệu và tối ưu hóa cấu trúc dữ liệu. Normalization giúp duy trì tính nhất quán và giảm rủi ro mất mát dữ liệu.

3. Xác Định Khóa Chính và Khóa Ngoại:​

  • Xác định các khóa chính để định danh duy nhất cho mỗi bản ghi.
  • Sử dụng khóa ngoại để xác định các mối quan hệ giữa các bảng.

4. Chọn Kiểu Dữ Liệu Phù Hợp:​

  • Chọn kiểu dữ liệu phù hợp với mỗi cột dựa trên loại dữ liệu và độ chính xác cần thiết.

5. Phân Loại Dữ Liệu:​

  • Phân loại dữ liệu theo tính chất và ứng dụng của chúng. Điều này giúp xác định xem dữ liệu nào nên được lưu trữ trong bảng nào.

6. Thiết Kế Cho Hiệu Suất:​

  • Tối ưu hóa cấu trúc để hỗ trợ các truy vấn phổ biến của ứng dụng. Sử dụng index và các kỹ thuật tối ưu hóa để cải thiện hiệu suất truy xuất.

7. Sử Dụng Partitioning và Sharding (Nếu Cần):​

  • Partitioning (phân vùng) và sharding (phân chia) có thể được sử dụng để tối ưu hóa hiệu suất và mở rộng quy mô cơ sở dữ liệu.

8. Quản Lý Đồng Bộ Hóa Dữ Liệu:​

  • Xác định cách đồng bộ hóa dữ liệu giữa các bảng và các thành phần khác của hệ thống.

9. Xác Định Các Ràng Buộc:​

  • Sử dụng ràng buộc để đảm bảo tính toàn vẹn của dữ liệu, chẳng hạn như ràng buộc khóa ngoại.

10. Thực Hiện Testing và Tối Ưu Hóa:​

  • Thực hiện kiểm thử và theo dõi hiệu suất để xác định cơ hội tối ưu hóa và điều chỉnh cơ sở dữ liệu nếu cần.

11. Bảo Dưỡng và Quản Lý:​

  • Xây dựng quy trình bảo dưỡng và quản lý cơ sở dữ liệu để theo dõi và duy trì cấu trúc dữ liệu hiệu quả theo thời gian.

Thiết kế cơ sở dữ liệu hiệu quả là một quá trình liên tục và đòi hỏi sự cân nhắc và kiểm soát liên tục để đảm bảo rằng nó đáp ứng được yêu cầu và đồng thời đáp ứng các tiêu chí hiệu suất.

4. Sự khác biệt giữa database OLAP và OLTP​

OLAP (Online Analytical Processing) và OLTP (Online Transaction Processing) là hai loại cơ sở dữ liệu được thiết kế để phục vụ các mục đích khác nhau trong quá trình xử lý thông tin.

OLAP (Online Analytical Processing):​

1. Mục Đích:​

  • Dùng để thực hiện và hỗ trợ các hoạt động phân tích và xử lý dữ liệu phức tạp.
  • Được sử dụng cho việc tạo các báo cáo, truy xuất dữ liệu đa chiều, và phân tích đa chiều.

2. Loại Câu Truy Vấn:​

  • Thường sử dụng các câu truy vấn phức tạp và phức tạp về mặt tính toán.

3. Đặc Điểm Cơ Bản:​

  • Dữ liệu được tổ chức theo kiểu đa chiều (multidimensional).
  • Có thể chứa lượng lớn dữ liệu lịch sử.
  • Thường chứa các tỷ lệ, tổng hợp, và các chỉ số tính toán.

4. Phục Vụ Người Dùng:​

  • Phục vụ người dùng ở cấp độ quản lý và quyết định.
  • Cung cấp cái nhìn tổng quan về dữ liệu.

OLTP (Online Transaction Processing):​

1. Mục Đích:​

  • Dùng để thực hiện và hỗ trợ các giao dịch kinh doanh hàng ngày.
  • Chủ yếu tập trung vào việc thêm, sửa, xóa dữ liệu.

2. Loại Câu Truy Vấn:​

  • Sử dụng các câu truy vấn đơn giản, chủ yếu liên quan đến các thao tác cơ bản như INSERT, UPDATE, DELETE.

3. Đặc Điểm Cơ Bản:​

  • Dữ liệu được tổ chức theo kiểu dòng (row-oriented).
  • Giữ các giao dịch trong thời gian ngắn và chú trọng vào tính nhất quán của dữ liệu.
  • Thường không lưu trữ lịch sử của dữ liệu một cách chi tiết.

4.Phục Vụ Người Dùng:​

  • Phục vụ người dùng ở cấp độ nhân viên và tổ chức.
  • Tập trung vào việc đảm bảo tính nhất quán và hiệu suất cao cho các giao dịch hàng ngày.

Tóm Tắt:​

  • OLAP tập trung vào việc phân tích và xử lý dữ liệu phức tạp, trong khi OLTP tập trung vào các giao dịch hàng ngày.
  • Câu truy vấn OLAP thường phức tạp, trong khi câu truy vấn OLTP thường đơn giản.
  • OLAP sử dụng tổ chức dữ liệu đa chiều, trong khi OLTP sử dụng tổ chức dữ liệu theo dòng.
  • OLAP thường phục vụ người dùng quản lý và quyết định, trong khi OLTP phục vụ người dùng nhân viên và tổ chức.

5. Cần làm gì để tối ưu hóa query execution plan ?​

Để tối ưu hóa kế hoạch thực thi của một truy vấn, bạn có thể thực hiện một số điều chỉnh để cách hệ thống cơ sở dữ liệu truy xuất và xử lý dữ liệu nhằm cải thiện hiệu suất của một truy vấn SQL. Dưới đây là một số kỹ thuật để tối ưu hóa kế hoạch thực thi của truy vấn:

  • Sử dụng Chỉ Mục:

    • Đảm bảo rằng các cột tham gia trong các điều kiện WHERE, các điều kiện JOIN, và các điều kiện ORDER BY có chỉ mục phù hợp. Chỉ mục có thể giúp cải thiện đáng kể quá trình truy xuất dữ liệu.

  • Cập Nhật Thống Kê:

    • Bảo đảm rằng thống kê về dữ liệu của bảng và chỉ mục được cập nhật. Trình tối ưu hóa truy vấn sử dụng thống kê để đưa ra quyết định thông tin về cách thức hiệu quả nhất để thực hiện một truy vấn.

  • __*Tránh Sử Dụng SELECT __:

    • Chỉ rõ các cột bạn cần trong câu SELECT. Điều này giảm lượng dữ liệu mà hệ thống cơ sở dữ liệu phải xử lý và truyền đi.

  • Sử Dụng JOIN Cẩn Thận:

    • Sử dụng loại JOIN phù hợp (INNER JOIN, LEFT JOIN, v.v.) dựa trên các mối quan hệ giữa các bảng. Chọn loại JOIN giảm thiểu số lượng dòng tham gia vào truy vấn.

  • Xem Xét Thứ Tự Bộ Lọc:

    • Sắp xếp các điều kiện lọc trong mệnh đề WHERE sao cho giảm kích thước kết quả nhanh nhất có thể. Đặt điều kiện lọc chọn lọc nhất trước.

  • Giới Hạn Kích Thước Kết Quả:

    • Sử dụng mệnh đề LIMIT (hoặc tương tự) để hạn chế số lượng dòng trả về bởi truy vấn, đặc biệt là khi xử lý với các bảng lớn.

  • Phân Vùng Dữ Liệu:

    • Nếu thích hợp, xem xét việc phân vùng các bảng lớn dựa trên một số tiêu chí. Điều này có thể cải thiện hiệu suất bằng cách giảm lượng dữ liệu cần quét.

  • Tránh Sử Dụng Hàm Trong WHERE:

    • Tránh sử dụng hàm trên cột trong mệnh đề WHERE, vì điều này có thể ngăn cản việc sử dụng chỉ mục. Thay vào đó, cố gắng sửa đổi điều kiện để cho phép sử dụng chỉ mục.

  • Cập Nhật Thông Tin Thống Kê Cơ Sở Dữ Liệu:

    • Thường xuyên cập nhật thông tin thống kê cơ sở dữ liệu để đảm bảo rằng trình tối ưu hóa có thông tin chính xác về phân phối dữ liệu trong các bảng và chỉ mục.

  • Sử Dụng Công Cụ Tối Ưu Hóa:

    • Sử dụng công cụ tối ưu hóa và công cụ theo dõi cụ thể cho cơ sở dữ liệu cụ thể để phân tích hiệu suất, xác định các nút cổ chai, và đề xuất cải tiến.

  • Xem Xét Kế Hoạch Thực Thi:

    • Xem xét kế hoạch thực thi được tạo ra bởi hệ thống cơ sở dữ liệu để xác định bất kỳ không hiệu quả hoặc hành vi không mong muốn nào. Điều này giúp điều chỉnh truy vấn hoặc cấu trúc cơ sở dữ liệu.

  • Cân Nhắc Denormalization:

    • Trong một số trường hợp, cân nhắc denormalization cho một số phần của cơ sở dữ liệu có thể cải thiện hiệu suất, đặc biệt là với các ứng dụng có khối lượng đọc lớn.

Nhớ rằng hiệu suất của các kỹ thuật tối ưu hóa có thể thay đổi tùy thuộc vào các đặc điểm cụ thể của cơ sở dữ liệu, cấu trúc dữ liệu, và công việc làm. Thường xuyên thử nghiệm, đo lường hiệu suất, và điều chỉnh chiến lược tối ưu hóa là quan trọng để đạt được hiệu suất tối ưu.