Tối ưu truy vấn SQL – Đối với một lập trình viên chắc hẳn các bạn đã từng làm việc với các câu lệnh SQL. Ngôn ngữ SQL có vẻ dễ học – các lệnh tuân theo cú pháp đơn giản và không mô tả các thuật toán cụ thể được sử dụng để truy xuất dữ liệu. Tuy nhiên, sự đơn giản có thể là lừa dối. Không phải tất cả các chức năng cơ sở dữ liệu đều hoạt động với hiệu quả như nhau. Hai truy vấn rất giống nhau có thể khác nhau đáng kể về thời gian tính toán. Bài viết này trình bày một số phương pháp hay nhất có thể thúc đẩy các truy vấn SQL của bạn.
1. Học cách đánh chỉ mục(Index) hợp lý
- Index là một trong những yếu tố quan trọng nhất góp phần vào việc nâng cao hiệu suất của cơ sở dữ liệu. Index trong SQL tăng tốc độ của quá trình truy vấn dữ liệu bằng cách cung cấp phương pháp truy xuất nhanh chóng tới các dòng trong các bảng, tương tự như cách mà mục lục của một cuốn sách giúp bạn nhanh chóng tìm đến một trang bất kỳ mà bạn muốn trong cuốn sách đó.
- Nên đánh index ở các cột sử dụng where, order by, group by.
- Mặc dù sử dụng INDEX nhằm mục đích để nâng cao hiệu suất của Database, nhưng đôi khi bạn nên tránh dùng chúng trong 1 số trường hợp sau:
- Không nên sử dụng trong các bảng nhỏ, ít bản ghi.
- Không nên sử dụng Index trong bảng mà các hoạt động UPDATE, INSERT xảy ra thường xuyên với tần suất lớn.
- Không nên sử dụng cho các cột mà chứa một số lượng lớn giá trị NULL.
- Không nên dùng Index cho các cột mà thường xuyên bị sửa đổi.
2. Chỉ lấy ra những dữ liệu cần thiết
- Một cách phổ biến để truy xuất các cột mong muốn là sử dụng SELECT * mặc dù không phải tất cả các cột đều thực sự cần thiết:
SELECT *FROM usersWHERE age > 20;
- Nếu bảng nhỏ, việc truy xuất các cột bổ sung sẽ không tạo ra nhiều khác biệt. Tuy nhiên, đối với các tập dữ liệu lớn hơn, việc chỉ định các cột có thể tiết kiệm rất nhiều thời gian tính toán. Vì vậy chúng ta cần xác định các fields trong lệnh SELECT để chỉ lấy ra các dữ liệu cần thiết đáp ứng yêu cầu nghiệp vụ (business requirements).
SELECT id, first_name, last_name, gender, ageFROM usersWHERE age > 20;
3. Tối ưu hóa câu lệnh bằng Union
- Thỉnh thoảng chúng ta cũng cần chạy các câu truy vấn so sánh với ‘like’, ‘or’. Khi sử dụng ‘or’ quá nhiều có thể sql sẽ phải search toàn bộ bảng để tìm kiếm bản ghi. Union có thể giúp câu truy vấn trở nên nhanh hơn đặt biệt là trong trường hợp đã đánh index một cách hợp lý. Ví dụ trong trường hợp dưới đây:
select from students where first_name like 'A%' or last_name like 'B%';
- Câu truy vấn trên có thể được tối ưu hơn bằng cách sử dụng union all để tận dụng index đã đánh.
select from students where first_name like 'A%' union all select from students where last_name like 'B%';
4. Tránh sử dụng các cột đã đánh index với function
- Cùng xem xét câu truy vấn sau
select count(*) from orders where YEAR(finished_at) = ‘2018’
- Chúng ta sử dụng function YEAR cùng với cột finished_at nó sẽ không cho phép database sử dụng index ở cột finished_at bởi vì index giá trị của finished_at chứ không phải YEAR(finished_at). Để có thể tránh được điều này ta có thể sử dụng index cho function bằng generated columns Hoặc một cách khác là tìm cách để viết lại câu truy vấn tương đương mà không phải sử dụng đến function
select count(*) from orders where finished_at >= '2018-01-01' and finished_at < '2019-01-01'
5. Sử dụng từ khoá Like phải hợp lý
- Khi sử dụng LIKE, không nên sử dụng ký tự %, * đặt ở phía trước giá trị tìm kiếm.
- Ta lấy ví dụ ở trên trên bảng students, tìm kiếm như thế này sẽ khiến SQL thực hiện quét toàn bộ bảng ngay cả khi bạn đã đánh index trường ‘first_name’ trên bảng students.
select * from students where first_name like '%nam';
6. Dùng DISTINCT và UNION chỉ khi cần
- Khi sử dụng union và distinct trong trường hợp không cần thiết có thể dẫn đến giảm performance của câu truy vấn. Thay vì sử dụng UNION có thể sử dụng UNION ALL sẽ cho kết quả tốt hơn.
7. Tránh sử dụng điều kiện với loại khác kiểu
- Ví dụ khi so sánh bằng với where một bên là kiểu varchar và kiểu số thì index sẽ không có tác dụng do sẽ phải cast ngầm kiểu để đồng nhất dữ liệu. Trong trường hợp này ta cố gắng để kiểu so sánh giống nhau ngay từ đầu sẽ cho kết quả tốt hơn.
8. Tối ưu hóa kiểu dữ liệu
- Database support nhiều kiểu dữ liệu khác nhau : integer, float, double, date, date_time, varchar, text … Khi thiết kế bảng ta nên tuân theo nguyên tắc “shorter is always better”.
- Ví dụ khi thiết kế bảng user chỉ chứa nhỏ hơn 100 bản ghi, chúng ta nên sử dụng ‘TINYINT’ cho cột user_id.
9. Nên dùng Joins thay cho SubQuery
- SubQuery phụ thuộc vào truy vấn bên ngoài (không độc lập) làm giảm tốc độ của cả quá trình. Với SQL có cơ chế hỗ trợ Join, khiến nó nhanh hơn Subquery, và do vậy chúng ta nên dùng Join, hạn chế dùng Subquery.
- Không nên dùng:
SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c
- Nên dùng:
SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID
10. Sử dụng Stored Procedure thay cho những cây truy vấn phức tạp
- Stored Procedure thực thi mã nhanh hơn và giảm tải băng thông.
- Thực thi nhanh hơn: Stored Procedure sẽ được biên dịch và lưu vào bộ nhớ khi được tạo ra. Điều đó có nghĩa rằng nó sẽ thực thi nhanh hơn so với việc gửi từng đoạn lệnh SQL. Vì nếu bạn gửi từng đoạn lệnh nhiều lần thì SQL cũng sẽ phải biên dịch lại nhiều lần, rất mất thời gian so với việc biên dịch sẵn.
- Giảm tải băng thông: Nếu bạn gửi nhiều câu lệnh SQL thông qua network đến SQL sẽ ảnh hưởng tới hiệu suất đường truyền. Thay vì gửi nhiều lần thì bạn có thể gom các câu lệnh SQL vào 1 Stored Procedure và chỉ phải gọi đến 1 lần duy nhất qua network.
- Thêm nữa viết Stored Procedure sẽ thuận lợi cho việc phân quyền và bảo mật tốt hơn
Nguồn: Internet
Chúng tôi chuyên cung cấp những khóa học về tối ưu câu lệnh (SQL level 2), Xây dựng kho dữ liệu (Data Warehouse), Phân tích dữ liệu,… đăng ký ngay để nhận được tư vấn chi tiết lộ trình dành riêng cho bạn nhé!
Ngoài ra, Chúng tôi chuyên cung cấp các dịch vụ về Xây dựng Kho dữ liệu Data Warehouse/ Xây dựng Báo cáo Power BI cho các doanh nghiệp lớn như: Nakagawa, Mutosi, Tinh Vân Group,….. đăng ký ngay để được Demo và tư vấn miễn phí dành riêng cho doanh nghiệp của bạn.
