Posted inMySQL

Hướng dẫn sử dụng Stored Procedure và Function trong MySQL: Tối ưu logic nghiệp vụ và tăng hiệu suất

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Tại sao logic lại phải chạy ngay tại Database?

Có một sai lầm mình từng mắc khi xây dựng hệ thống thương mại điện tử: mỗi lần cần tính doanh thu theo tháng, code backend lại kéo cả bảng orders về để xử lý. Khi table vượt 10 triệu row, trang dashboard mất gần 8 giây để load — khách hàng đã chuyển tab từ lâu rồi.

Giải pháp không phải nâng server. Stored Procedure và Function cho phép bạn gói gọn một chuỗi thao tác SQL phức tạp vào một cái tên duy nhất. Thay vì gửi 10 câu lệnh riêng lẻ từ app, bạn gọi một lần — Database xử lý nội bộ, trả kết quả cuối cùng.

Quick Start: Tạo Procedure đầu tiên trong 5 phút

Trước khi gõ dòng đầu tiên, bạn cần hiểu tại sao có từ khóa DELIMITER. MySQL mặc định dùng dấu ; để kết thúc lệnh. Bên trong Procedure có rất nhiều dấu ;, nên ta đổi tạm sang // để MySQL không xử lý sớm.

Ví dụ tạo thủ tục tính tổng tiền đặt hàng của một khách hàng:

DELIMITER //

CREATE PROCEDURE GetCustomerTotal(IN customerId INT, OUT totalAmount DECIMAL(10,2))
BEGIN
    SELECT SUM(amount) INTO totalAmount 
    FROM orders 
    WHERE customer_id = customerId;
END //

DELIMITER ;

Chạy thử:

CALL GetCustomerTotal(1, @total);
SELECT @total;

Đơn giản vậy thôi. Logic đã được đóng gói — gọi lại bao nhiêu lần cũng được, từ bất kỳ ứng dụng nào kết nối vào DB.

Procedure vs Function: Chọn cái nào?

Câu hỏi này hay gặp ở những bạn mới. Cách phân biệt nhanh nhất:

  • Function (Hàm): Trả về đúng 1 giá trị. Dùng được trực tiếp trong SELECT. Ví dụ: SELECT format_price(price) FROM products;
  • Stored Procedure (Thủ tục): Có thể trả về nhiều kết quả, hoặc không trả về gì. Mạnh ở chỗ thực hiện logic phức tạp: INSERT, UPDATE hàng loạt, transaction nhiều bước. Không dùng được trong SELECT.

Ví dụ Function trong thực tế

Dự án mình hay dùng Function để tính giá VAT — gọn hơn nhiều so với nhúng công thức trực tiếp vào từng query:

DELIMITER //

CREATE FUNCTION GetVatPrice(price DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    RETURN price * 1.1;
END //

DELIMITER ;

Dùng ngay trong query: SELECT name, GetVatPrice(price) as price_with_vat FROM products;

Xử lý logic phức tạp và Error Handling

Thực tế không chỉ có SELECT đơn giản. Khi migrate dữ liệu 200.000 user sang hệ thống mới, mình cần xử lý từng bản ghi và bỏ qua các row lỗi thay vì để cả batch bị rollback. DECLARE CONTINUE HANDLER là thứ cứu mình lúc đó:

DELIMITER //

CREATE PROCEDURE ProcessDailyInvoices()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE invoiceId INT;
    
    DECLARE cur CURSOR FOR SELECT id FROM invoices WHERE status = 'pending';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO invoiceId;
        IF done THEN
            LEAVE read_loop;
        END IF;

        UPDATE invoices SET status = 'processed', processed_at = NOW() WHERE id = invoiceId;
        
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

Lưu ý quan trọng: Cursor xử lý từng row nên tốn tài nguyên đáng kể. Nếu bài toán giải quyết được bằng một câu UPDATE ... WHERE status = 'pending', hãy dùng SQL thuần — nhanh hơn vài chục lần so với vòng lặp cursor.

4 nguyên tắc từ kinh nghiệm thực tế

1. Gom round-trip để giảm latency

App ở Singapore, DB ở Tokyo — mỗi query tốn khoảng 70ms network latency. Chạy 5 câu riêng lẻ là mất 350ms chỉ vì mạng. Gom vào 1 Procedure, con số đó xuống còn 70ms. Mình đã dùng cách này để cải thiện response time của một API từ 1.2 giây xuống còn 300ms — không nâng server, không refactor toàn bộ code.

2. Kiểm soát quyền truy cập chi tiết hơn

Bạn có thể cấp quyền cho user chỉ được EXECUTE một Procedure cụ thể, không có SELECT hay DELETE trực tiếp vào table. Thêm vào đó, tham số trong Procedure được MySQL bind tách biệt khỏi câu lệnh SQL — SQL Injection qua tham số gần như bị loại bỏ hoàn toàn.

3. Đừng nhét business logic phức tạp vào DB

Mình từng đưa cả logic tính hoa hồng đại lý vào Procedure — 300 dòng SQL, điều kiện lồng nhau, không có unit test. Debug một lỗi nhỏ mất cả ngày. Code PHP/Java có IDE, có debugger, có test suite; còn Procedure trong MySQL thì chỉ có SELECT statement để in giá trị ra màn hình. Bài học rút ra: chỉ đưa xuống DB những gì gắn chặt với dữ liệu — aggregation, transformation, không phải toàn bộ business rule.

4. Lưu Procedure vào version control

Code backend có Git. Procedure thì nằm trong DB. Quy tắc của team mình: mọi Procedure phải có file .sql tương ứng trong repository, tên theo format sp_ten_procedure_v1.sql. Không sửa trực tiếp trên MySQL Workbench rồi bỏ đó — một tháng sau bạn sẽ không nhớ mình đã thay đổi gì.

Dùng Stored Procedure khi nào thực sự hợp lý?

Câu trả lời thực dụng: khi có bottleneck rõ ràng. Latency cao do nhiều round-trip, logic tính toán gắn chặt với dữ liệu, hoặc cần hạn chế quyền truy cập table trực tiếp — đó là những lúc Procedure phát huy tác dụng. Dùng đúng chỗ, hệ thống gọn và nhanh hơn đáng kể. Dùng sai, bạn sẽ có một cái DB khổng lồ mà chỉ mình bạn hiểu cách vận hành.

Share:
Tags: