Vũ Quang Huy
Trần Lê Kim Danh
Trường Đại học Thương mại
(Quanlynhanuoc.vn) – Hiện nay, ứng dụng cơ sở dữ liệu trong quản lý số phổ biến ở hầu hết các lĩnh vực của đời sống, trong xu hướng này, các trường đại học đã luôn quan tâm đầu tư cho hệ thống cơ sở dữ liệu đào tạo (chủ yếu là đào tạo tín chỉ), giúp nâng cao năng lực quản lý, tiết kiệm chi phí. Các hệ thống được thiết kế chặt chẽ theo quy chế đào tạo tín chỉ của Bộ Giáo dục và Đào tạo, kết hợp với tính chất riêng của mỗi trường. Tuy nhiên, khi đưa vào thực hiện các cơ sở đào tạo lại thay đổi để phù hợp với đặc thù của từng trường, điều này gây ra sự thiếu thống nhất về mặt quy chuẩn và khai thác cơ sở dữ liệu đào tạo. Vì vậy, bài viết tổng quan việc sử dụng cơ sở dữ liệu nói chung và vận hành cơ sở dữ liệu tín chỉ tại các trường đại học nói riêng; đồng thời, đề xuất một số giải pháp nhằm nâng cao hiệu năng khai thác cơ sở dữ liệu đào tạo tín chỉ hiện nay.
Từ khóa: Cơ sở dữ liệu; đào tạo tín chỉ; ứng dụng công nghệ thông tin.
1. Đặt vấn đề
Thiết kế, tổ chức và tối ưu trong khai thác dữ liệu luôn là chủ đề quan trọng trong hầu hết các nghiên cứu, diễn đàn trao đổi về cơ sở dữ liệu, ngày nay, khi công việc đều được số hóa, quản lý bởi công nghệ và các hệ quản trị cơ sở dữ liệu thì nhiệm vụ tổ chức và tối ưu dữ liệu trở thành công việc hàng ngày của các nhà quản trị và lập trình viên cơ sở dữ liệu. Tuy nhiên, việc khảo sát và phân tích nhiều khi mang tính chất đáp ứng nhanh công việc hiện tại, nhằm đẩy nhanh tiến độ xây dựng các hệ thống quản lý, điều này khiến cho các cơ sở dữ liệu thiếu tính chuẩn hóa hoặc tính mở rộng, do đó, khi đưa vào hoạt động sẽ phát sinh các nhu cầu không thể giải quyết, từ đó, các cơ sở dữ liệu ngày càng phi chuẩn và khó bảo trì. Mặt khác, việc sử dụng cơ sở dữ liệu quá lâu mà không có biện pháp sao lưu, bảo trì sẽ làm số lượng bản ghi ngày càng tăng, các bảng quan hệ vòng, dư thừa dữ liệu và phi chuẩn, giảm sự đồng bộ, các lệnh truy vấn chậm chạp và quá tải dẫn đến nguy cơ sụp đổ của các hệ thống thông tin quản lý.
Mặt khác, nghiệp vụ đào tạo mỗi cơ sở đều có đặc thù riêng và trong một cơ sở giáo dục cũng luôn có sự thay đổi nâng cấp các yêu cầu trong quản lý để phù hợp với sự phát triển và định hướng của mỗi cơ sở. Đây chính là lý do khiến cấu trúc của cơ sở dữ liệu này bị can thiệp liên tục qua mỗi học kỳ, dẫn đến hệ thống bị chậm khi sinh viên đăng ký nhập học hoặc bị quá tải khi giảng viên nhập điểm hoặc xảy ra sai sót về mặt dữ liệu. Vì vậy, rà soát bảo trì thường xuyên áp dụng các biện pháp tối ưu thiết kế, tối ưu truy vấn là rất cần thiết giúp tăng cường hoạt động ổn định hiệu quả của các cơ sở dữ liệu quản lý nói chung và đào tạo tín chỉ nói riêng.
2. Các vấn đề về hiệu năng cơ sở dữ liệu và cách giám sát
a. Một số vấn đề liên quan tới hiệu năng cơ sở dữ liệu
(1) Nghẽn cổ chai vi xử lý (CPU bottlenecks) khi khai thác dữ liệu, việc này xuất phát từ việc bất đồng bộ cấu hình tài nguyên phần cứng (quá nhanh, quá chậm) hoặc do điều phối các đoạn mã lập trình chưa tốt dẫn đến quá tải ở một số quy trình.
(2) Nghẽn cổ chai bộ nhớ trong (Memory bottlenecks) khi truy vấn là bộ nhớ trong nhỏ và các thao tác truy vấn không sử dụng tìm kiếm và thay vào đó là các hoạt động duyệt tuần tự toàn bộ các bảng dữ liệu (table scan) khiến chi phí về bộ nhớ, về thời gian bị bùng nổ.
(3) Nghẽn cổ chai mạng (Network bottlenecks) là không kiểm soát được thời gian ngắt các kết nối, cho phép các hệ thống tự động gửi nhận liên tục trực tiếp các về cơ sở dữ liệu.
(4) Nghẽn cổ chai đối với các thiết bị vào ra (I/O bottlenecks) là các thiết bị nhập xuất bị lỗi hoặc có giao tiếp ở thế hệ quá cũ, bộ nhớ Cache nhỏ, các mạch xử lý dữ liệu tốc độ thấp dẫn đến không tương xứng với năng lực hoạt động của vi xử lý hay bộ nhớ trong làm giảm hiệu năng của toàn bộ cơ sở dữ liệu.
(5) Cuối cùng là nội tại các câu truy vấn quá chậm dù hệ thống phần cứng, hạ tầng mạng rất tốt nhưng lệnh truy vấn yếu kém, làm giảm hiệu năng hoạt động của cơ sở dữ liệu, đơn cử, như: lỗi chỉ mục (Index), kế hoạch thực thi câu lệnh kém, dư thừa dữ liệu trong truy vấn, lược đồ thiết kế không chuẩn hóa…
b. Công cụ giám sát và cảnh báo sớm các vấn đề ảnh hưởng tới hiệu năng cơ sở dữ liệu
(1) Công cụ xem quản lý động – Dynamic Management Views, là một trong những tính năng hữu ích được tích hợp sẵn, cung cấp nhiều thông tin về các vấn đề khác nhau, như: thống kê truy vấn, kế hoạch thực thi, truy vấn có nhiều lần đọc, thời gian hoạt động (CPU)…
(2) Công cụ quản trị hiệu suất ứng dụng – Retrace APM, là để người dùng biết có bao nhiêu truy vấn đã được thực hiện, thời gian thực hiện truy vấn trung bình và những giao dịch đang gọi truy vấn, ngoài ra, còn thu thập cả các số liệu thống kê hiệu suất về từng truy vấn và cho phép tìm kiếm các truy vấn có tiềm ẩn.
(3) Công cụ giám sát sự kiện trong cơ sở dữ liệu (SQL Server Profiler), là công cụ ghi chép hiệu quả cho phép quản lý và nắm bắt những hoạt động đang diễn ra trong cơ sở dữ liệu.
3. Phương pháp tăng hiệu năng cơ sở dữ liệu
Thứ nhất, tối ưu trong thiết kế cơ sở dữ liệu. Khi bắt đầu thiết kế cơ sở dữ liệu, phần lớn người thiết kế mặc định áp dụng các quy tắc chuẩn hóa mà chưa nghĩ đến bản chất của ứng dụng (Create, Read, Update and Delete) nghĩa là tạo mới, đọc, cập nhật và xóa bản ghi nhiều hơn, tên thường gọi của loại cơ sở dữ liệu này là OLTP (On-line transactional processing), vì vậy, cần:
(1) Tránh chuẩn hóa quá yếu, thực tế có những cơ sở dữ liệu được thiết kế mà không áp dụng theo những quy tắc chuẩn hóa cơ bản, vì vậy, mọi cơ sở dữ liệu được chuẩn hóa theo dạng 3NF, đó là mô hình tốt nhất để thể hiện các thực thể dữ liệu và cân bằng được hiệu năng truy vấn và thêm – sửa – xóa bản ghi.
(2) Không cứng nhắc, với dữ liệu chuẩn hóa, cần Join nhiều bảng và với dữ liệu không chuẩn hóa, các thao tác Join sẽ giảm đi để làm tăng hiệu năng.
(3) Không thể có nhiều giá trị trong 1 cột trong 1 bản ghi.
(4) Khóa không nên dài quá, nếu chúng ta đang thử quyết định giữa xác định 1 khóa với 2 cột (ví dụ Invoice Number và Date) và 1 khóa với 1 cột (ví dụ, Invoice Number), cả 2 đều xác định duy nhất 1 dòng trong bảng, hãy lựa chọn khóa ngắn hơn trong 2 khóa. Khi định nghĩa 1 khóa, cần chắc chắn rằng tập hợp thông tin này là cần và đủ để xác định 1 hàng.
(5) Sử dụng trường id là số nguyên trong mọi bảng.
(6) Phân vùng các bảng lớn, máy chủ cơ sở dữ liệu và máy chủ web nên để riêng biệt trên 2 Server vật lý. Việc này bảo đảm bảo mật hơn (kẻ tấn công không thể tấn công trực tiếp dữ liệu). Hiệu năng bộ nhớ và CPU của Server sẽ tốt hơn do giảm tải số lượng yêu cầu và các tiến trình xử lý.
Thứ hai, tối ưu trong viết lệnh truy vấn.
(1) Luôn tham chiếu đến các đối tượng bằng tên sở hữu đầy đủ.
(2) Hiểu được cơ chế và thứ tự thực hiện các lệnh Select, Join, Where, Group, Having, Distinct, Contrains, Top… và đặc biệt hiểu về cách thức mà công cụ tối ưu của cơ sở dữ liệu lựa chọn thứ tự thực thi các mệnh đề điều kiện trong câu lệnh.
(3) Hạn chế việc viết và thực thi các câu truy vấn động cũng như viết mã truy vấn trong các chương trình, thay vào đó là thực thi trực tiếp trên môi trường của cơ sở dữ liệu thông qua các cửa sổ.
(4) Tránh dùng mệnh đề HAVING, mệnh đề này dùng để giới hạn kết quả trả về bởi mệnh đề GROUP BY, trong nhiều trường hợp có thể viết câu lệnh SELECT mà chỉ có mệnh đề WHERE, GROUP BY không cần mệnh đề HAVING. Cách viết này sẽ góp phần cải thiện tốc độ câu truy vấn.
(5) Dùng table variables thay vì dùng Temporary tables.
(6) Dùng Constraints thay cho Triggers giúp tăng tốc việc thực hiện.
(7) Dùng câu lệnh UNION ALL thay cho UNION, bởi câu lệnh này sẽ không tìm ra những dòng dữ liệu trùng nhau, còn câu lệnh UNION sẽ mất nhiều thời gian để loại bỏ dòng trùng nhau; đồng thời, nên hạn chế sử dụng mệnh đề DISTINCT.
(8) Tránh dùng câu lệnh Cursor trong SQL Server, vì sẽ dẫn đến giảm tốc độ so với những câu lệnh Select; nên dùng các câu Select lồng nhau hoặc dùng bảng tạm nếu thao tác trên từng dòng dữ liệu. Đặc biệt, khi lấy tổng số dòng trong bảng thì nên thay thế cách dùng câu lệnh thông thường là Select count(*) thành Select count(0). Tương tự như vậy, hạn chế dùng cú pháp Select * from; đồng thời, thay vào đó là chỉ định rõ các cột cần lấy ra. Mặt khác, nên dùng mệnh đề WHERE để hạn chế bớt kết quả truy vấn, kết hợp với từ khóa TOP hoặc câu lệnh SET ROWCOUNT.
(9) Hạn chế sử dụng các toán tử sau trong mệnh đề WHERE. Vì những toán tử này không sử dụng đặc tính Index, thay vào đó là dò tìm toàn bảng gây ảnh hưởng đến tốc độ của câu truy vấn. Nếu phải chọn lựa giữa IN và EXISTS trong câu truy vấn, hãy chọn EXISTS là tốt nhất, tương tự như IN và BETWEEN, hãy chọn BETWEEN.
(10) Để giảm thiểu số lượng “chuyến khứ hồi” giữa máy chủ và máy khách, cần tập hợp các yêu cầu từ máy khách và gửi chúng đồng thời đến máy chủ để thực thi, sau đó, máy chủ sẽ trả về một tập hợp các kết quả tương ứng trong một lần truyền dữ liệu.
(11) Sử dụng tính năng Partition của Database để phân chia dữ liệu Logic Database thành nhiều File lưu trữ vật lý, khi dữ liệu trong Database hoặc Table quá lớn.
(12) Giới hạn thời gian câu truy vấn.
(13) Sử dụng tối đa các loại Index trong quá trình truy vấn đọc dữ liệu. Áp dụng nguyên tắc Sargable khi viết biểu thức điều kiện trong Where để tận dụng sức mạnh của Index, đó là cột cần tìm cần phải đứng một mình ở một phía của biểu thức tìm kiếm, nói cách khác, không có hàm số hay phép tính nào áp dụng trên cột đó.
4. Một số giải pháp tối ưu hóa cơ sở dữ liệu thi trắc nghiệm trong quản lý đào tạo tín chỉ
Một là, việc tối ưu thiết kế cơ sở dữ liệu thi trắc nghiệm học phần
Hệ thống phần mềm quản lý đào tạo tín chỉ là một hệ sinh thái số khá phức tạp với rất nhiều Module chức năng, như: quản lý nhập học; quản lý sinh viên; quản lý đào tạo; quản lý đăng ký học; quản lý tổ chức thi; quản lý điểm; quản lý học phí; quản lý thi trắc nghiệm trên máy tính… Tất cả các Module này đều được kết nối đồng bộ đến cơ sở dữ liệu đào tạo tín chỉ chung, vì vậy, sẽ lựa chọn một Module chức năng và nhóm các đối tượng cơ sở dữ liệu liên quan tới Module trong đào tạo tín chỉ chung để thực hiện một số phương pháp tối ưu, cụ thể:
(1) Cơ sở dữ liệu: lược đồ cơ sở dữ liệu cần được phân tách rõ, gồm: lược đồ dữ liệu lưu trữ và xử lý ngân hàng câu hỏi thi; lược đồ dữ liệu lưu trữ và xử lý bài thi.
(2) Lược đồ quản lý ngân hàng câu hỏi thi: một là, thực thể chính là học phần, câu hỏi, đáp án; hai là, thực thể hỗ trợ là loại câu hỏi, mức độ câu hỏi, chương/nhóm kiến thức. Đồng thời, cần chú ý đến kiểu dữ liệu của các câu hỏi và đáp án… Chính vì vậy, cần đa dạng hóa cách thức lưu trữ bằng các hình thức khác để tiết kiệm về bộ nhớ và tăng tốc truy vấn dữ liệu sau này.
(3) Lược đồ quản lý bài thi: bài thi học phần sinh viên; danh mục câu hỏi bài thi; danh mục đáp án bài thi với nguyên tắc một sinh viên chỉ có một bản ghi bài thi đối với một học phần, tiếp đó, mỗi bài thi có danh mục n câu hỏi, cuối cùng, mỗi câu hỏi có danh mục m đáp án trả lời. Trong đó, chi tiết mỗi thực thể có nhiều thông tin liên quan, như: ngày giờ, trạng thái, mức điểm, đúng, sai… và danh mục các học phần, sinh viên, câu hỏi, đáp án hoàn toàn chỉ lưu khóa ngoại để tránh dư thừa dữ liệu, giảm kích cỡ bảng.
(4) Tối giản kiểu dữ liệu: sử dụng số nguyên làm các khóa chính, khóa ngoại; đặt chỉ mục (Index) cho tất cả thuộc tính có thực hiện phép Join, thực hiện các phép chuẩn hóa đưa cơ sở dữ liệu về mức 3NF. Đây là hình ảnh về lược đồ cơ sở dữ liệu quản lý ngân hàng câu hỏi thi và cơ sở dữ liệu bài thi được thiết kế theo một số quy tắc sau:
Hai là, tối ưu truy vấn cơ sở dữ liệu.
(1) Với Store Procedure sinh đề thi: mục tiêu của bộ lệnh này nhằm tạo đề thi cho một tập sinh viên, trong đó mỗi sinh viên, thuật toán phải bảo đảm truy xuất vào dữ liệu tương ứng với học phần sẽ thi nằm trong bảng Câu hỏi và Đáp án một cách hoàn toàn ngẫu nhiên. Vì vậy, trong thuật toán này phải thực hiện sinh đề lặp đi lặp lại nhiều lần trên các sinh viên khác nhau; đồng thời, truy vấn để tìm ra ngẫu nhiên được Câu hỏi – Đáp án trong một ngân hàng dữ liệu quá lớn.
Sử dụng các bảng tạm (biến bảng) để rút gọn tập dữ liệu ngân hàng câu hỏi trước khi đưa vào truy vấn trong các lần lặp sinh đề thi như lệnh sau:
INSERT INTO @tblTapCauHoi (CH_ID, CH_MDCH_ID, KCC_ID, CH_MucDiem)
SELECT tblCauHoi.CH_ID,tblCauHoi.CH_MDCH_ID, tblCauHoi.CH_KCC_ID, tblCauHoi.CH_MucDiem FROM tblCauHoi WHERE CH_TrangThai = 1 AND CH_HP_ID = @HP_ID
Sử dụng việc sắp xếp mã GUI trên một tập dữ liệu nhỏ để lấy ngẫu nhiên câu hỏi và đáp án thay vì các thuật toán sinh ngẫu nhiên phức tạp. Kết hợp sử dụng phép lấy TOP dữ liệu, ví dụ, số lượng câu hỏi là Top (50), số lượng đáp án là Top (4) sẽ tăng hiệu năng thực thi của bó lệnh, đây chính là câu lệnh áp dụng kỹ thuật tối ưu đã nêu:
INSERT INTO tblChiTietCauHoiDeThi (CTCHDT_SVLHPDT_ID, CTCHDT_CH_ID, CTCHDT_MucDiem)
SELECT TOP(@TCDT_SoLuong) @SVLHPDT_ID, CH_ID, CH_MucDiem FROM @tblTapCauHoi
WHERE KCC_ID = @TCDT_KCC_ID AND CH_MDCH_ID = @TCDT_MDCH_ID
ORDER BY NEWID()
(2) Với Store Procedure lấy đề thi: mục tiêu của Store này để lấy ra 50 câu hỏi cho mỗi sinh viên trong phòng thi, mỗi câu có 4 đáp án, như vậy, tổng cộng có 200 bản ghi dữ liệu sẽ được liệt kê và gom nhóm theo thứ tự từ câu 1 – 50. Vấn đề của bộ lệnh này là việc Join giữa ba bảng dữ liệu SinhVienHocPhanDeThi với ChiTietCauHoiDeThi và ChiTietDapAnDeThi để lấy ra 200 bản ghi dưới dạng ID, sau đó, tiếp tục Join với hai bảng CauHoi và DapAn để lấy ra nội dung câu hỏi và đáp án tương ứng với các ID vừa truy vấn được ở trên. Nếu cùng một thời điểm thực hiện Join 5 bảng trên để lấy dữ liệu thì tốc độ thực thi rất chậm do vấn đề Lookup giữa các tập dữ liệu quá lớn với nhau. Vậy thay vì Join liên tục thì sẽ thực hiện giới hạn dữ liệu trên từng bảng, sau đó, Join từng cặp 2 bảng lại với nhau với những điều kiện nhất định và kết quả thu được đặt thành bảng phụ mới, cuối cùng sẽ đi Join các bảng phụ lại với nhau để ra danh sách cuối cùng. Cách làm này, giúp giảm đáng kể dữ liệu ở mỗi nhóm bảng khi tham gia vào hai vế của mỗi lệnh Join.
5. Kết luận
Bất cứ cơ sở dữ liệu nào trong quá trình sử dụng đều có thể gặp những vấn đề liên quan tới hiệu năng qua sự hoạt động bất thường của phần cứng (CPU bottlneck, RAM bottlneck, Network bottlneck, I/O bottlneck…) hoặc tốc độ thực thi của các bó lệnh T-SQL chậm đi. Đây là nguyên nhân chính gây ra rào cản về hiệu năng trong cơ sở dữ liệu, vì vậy, bài viết đã gợi ý một số công cụ giám sát chuyên dụng, như: DMVs, Retrace APM, SQL Server Profiler nhằm đưa ra nhiều quy tắc tối ưu hóa trong thiết kế, truy vấn giúp các nhà quản trị cơ sở dữ liệu có thể lựa chọn. Đây là một trong những tài liệu tham khảo hữu ích cho tất cả các nhà quản trị cơ sở dữ liệu nói chung, đặc biệt là cho các nhà quản trị cơ sở dữ liệu quản lý đào tạo nói riêng.
Tài liệu tham khảo:
1. Nguyễn Kim Anh (2004). Nguyên lý các hệ quản trị cơ sở dữ liệu. H. NXB Đại học Quốc Gia Hà Nội.
2. Bộ Chính trị (2024). Nghị quyết số 57-NQ/TW ngày 22/12/2024 về đọt phá phát triển khoa học công nghệ, đổi mới sáng tạo và chuyển đổi số quốc gia.
3. Nguyễn Bá Tường (2005). Cơ sở dữ liệu. H. NXB Khoa học và Kỹ thuật.
4. Cơ sở dữ liệu – Tối ưu hóa câu truy vấn. https://lhchuong.wordpress.com/2014/02/17/co-so-du-lieu-toi-uu-hoa-cau-truy-van/
5. Điều chỉnh hiệu suất trong SQL Server: tìm những truy vấn chậm.https://quantrimang.com/dieu-chinh-hieu-suat-trong-sql-server-tim-nhung-truy-van-cham-144819
6. Nguyên nhân và các phương pháp tối ưu truy vấn SQL Server.https://www.howkteam.vn/Course/Kinh-nghiem-va-Thu-thuat-SQL-Server/Nguyen-nhan-va-Phuong-phap-toi-uu-truy-van-SQL-1299
7. Những sai lầm trong thiết kế cơ sở dữ liệu. https://congngheweb.vn/tu-van/nhung-sai-lam-trong-thiet-ke-co-so-du-lieu-11407
8. Trung tâm khoa học tự nhiên, Những bí kíp tối ưu hóa SQL Server – Trải nghiệm từ thực tế.https://csc.edu.vn/lap-trinh-va-csdl/tin-tuc/kien-thuc-lap-trinh/hoc-co-so-du-lieu-sql-nhung-bi-kip-toi-uu-sql-server—trai-nghiem-tu-thuc-te-760
9. Tối ưu hóa Cơ sở dữ liệu SQL Server (Performance Tuning And Optimization Microsoft SQL Database). https://tungnt.net/toi-uu-hoa-co-so-du-lieu-sql-server-performance-tuning-and-optimization-microsoft-sql-database/
10. 20 vấn đề database tuning. http://tayninhit.info/20-van-de-database-tuning-90.html
11. Kinh nghiệm thiết kế cơ sở dữ liệu quan hệ.https://anhtan1987.wordpress.com/2014/12/08/kinh-nghiem-thiet-ke-co-so-du-lieu-quan-he/
