Câu lệnh SQL lúc nhanh lúc chậm trên SQL Server thường sẽ do nguyên nhân liên quan đến thay đổi chiến lược thực thi. Bài viết này tôi sẽ giúp anh em Demo và phân tích tình huống thất thường này nhé.

1. Mô tả thiết kế Table trong Demo này

Bảng EMPLOYEE có cấu trúc như sau

create table EMPLOYEES (
id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(50),
gender VARCHAR(50),
salary INT,
bonus INT
);

select gender, count(*) as "Count_gender" from EMPLOYEES
group by gender

create index idx_wecommit_gender on employees(gender);

2. Phân tích tình huống câu lệnh SQL lúc nhanh lúc chậm

2.1. Đánh giá câu lệnh SQL khi thực hiện đơn lẻ

 select * from EMPLOYEES where gender='Male'

select * from EMPLOYEES where gender='Female'

select * from EMPLOYEES where gender='Male'

StmtText
|--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
|--Index Seek(OBJECT:([MYDB].[dbo].[EMPLOYEES].[idx_wecommit_gender]), SEEK:([MYDB].[dbo].[EMPLOYEES].[gender]='Male') ORDERED FORWARD)
|--RID Lookup(OBJECT:([MYDB].[dbo].[EMPLOYEES]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

(1 row affected)
Table 'EMPLOYEES'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

select * from EMPLOYEES where gender='Female'

StmtText
|--Table Scan(OBJECT:([MYDB].[dbo].[EMPLOYEES]), WHERE:([MYDB].[dbo].[EMPLOYEES].[gender]='Female'))

(999 rows affected)
Table 'EMPLOYEES'. Scan count 1, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

• Câu lệnh tìm kiếm với điều kiện Female có chiến lược thực thi khác hoàn toàn so với câu lệnh tìm kiếm Male
• Đo chỉ số I/O của 2 câu lệnh thì thấy câu lệnh tìm kiếm Female có lượng Logical Reads lớn hơn 3.3 lần so với câu lệnh tìm kiếm Male.
• Nhân tiện đây, nếu các anh em nào muốn biết chi tiết các kỹ thuật tối ưu SQL và tối ưu Database thì tôi có một khoá học có thể giúp anh em đạt được kết quả này vô cùng ĐƠN GIẢN và DỄ DÀNG. Các anh em có thể đăng ký trải nghiệm (buổi trải nghiệm này miễn phí) khoá học của tôi bằng cách điền thông tin tại đây.

2.2. Thực hiện tìm kiếm dựa trên Procedure và hiện tượng câu lệnh SQL lúc nhanh lúc chậm gặp phải như thế nào?

CREATE PROCEDURE FindGender
@gender VARCHAR(50)
AS
SELECT * FROM EMPLOYEES WHERE gender=@gender;
GO

EXEC Findgender @gender = 'Female';

EXEC Findgender @gender = 'Male';

• Người dùng thứ nhất: thực hiện tìm kiếm những EMPLOYEE có điều kiện gender = ‘Female’
• Người dùng thứ hai: thực hiện sau người dùng thứ nhất (ví dụ: thực hiện sau 5 phút), điều kiện tìm kiếm những EMPLOYEE có điều kiện gender=’Male’

EXEC Findgender @gender = 'Female';

StmtText
EXEC Findgender @gender = 'Female';
CREATE PROCEDURE FindGender @gender VARCHAR(50) AS SELECT * FROM EMPLOYEES WHERE gender=@gender;

StmtText
|--Table Scan(OBJECT:([MYDB].[dbo].[EMPLOYEES]), WHERE:([MYDB].[dbo].[EMPLOYEES].[gender]=[@gender]))
(999 rows affected)
Table 'EMPLOYEES'. Scan count 1, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

• SQL Server đã sử dụng giải thuật quét toàn bộ dữ liệu của bảng EMPLOYEES (TABLE SCAN).
• Câu lệnh trên tiêu tốn LOGICAL READS = 10 để trả ra 999 bản ghi
• Các thông số này hoàn toàn giống với những gì chúng ta mong đợi (giống với việc đã phân tích bên trên: quét full table vì có hơn 99.9% số lượng bản ghi thoả mãn điều kiện tìm kiếm)

Bây giờ kiểm tra điều gì sẽ xảy ra khi người thứ hai thực hiện trên SQL Server nhé

EXEC Findgender @gender = 'Male';

Chiến lược thực thi và các thông số về hiệu năng của câu lệnh này như sau

StmtText
EXEC Findgender @gender = 'Male';
CREATE PROCEDURE FindGender @gender VARCHAR(50) AS SELECT * FROM EMPLOYEES WHERE gender=@gender;

StmtText
|--Table Scan(OBJECT:([MYDB].[dbo].[EMPLOYEES]), WHERE:([MYDB].[dbo].[EMPLOYEES].[gender]=[@gender]))
(1 row affected)
Table 'EMPLOYEES'. Scan count 1, logical reads 10, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

• SQL Server đã lực chọn việc sử dụng giải thuật quét toàn bộ Table (TABLE SCAN) chỉ để lấy ra 1 row thoả mãn
• Việc sử dụng giải thuật này khiến hệ thống tiêu tốn LOGICAL READS  = 10
• Như vậy, chúng ta thấy một điều ở đây: thay vì sử dụng INDEX để tìm kiếm (mang lại hiệu quả rất nhanh chóng, tiêu tốn ít tài nguyên hơn, tối ưu hơn), SQL Server đã lựa chọn một phương án thực thi “TỒI”. Việc này dẫn tới câu lệnh tìm kiếm CHẬM HƠN HẲN so với KỲ VỌNG

3. Tại sao lại có hiện tượng câu lệnh SQL lúc nhanh lúc chậm này?

Hãy để ý ở trường hợp chúng ta sử dụng PROCEDURE để tìm kiếm bên trên.

Chúng ta sẽ thực hiện tìm kiếm dựa trên việc gán các giá trị cần tìm cho một biến @gender.

Khi thực hiện câu lệnh đầu tiên, SQL Server nhận được giá trị của biến @gender = ‘Female’.

SQL Server sẽ phân tích giá trị này và thấy rằng:

• Giá trị Female chiếm đại đa số giá trị trong bảng EMPLOYEE, do đó database sẽ lựa chọn giải thuật là quét toàn bộ dữ liệu của bảng (TABLE SCAN)
• Chiến lược thực thi này được mã hoá và lưu lại trong database. Mục đích là nếu có câu lệnh tương tự thì sẽ lấy chiến lược thực thi ra sử dụng luôn, thay vì phải phân tích lại từ đầu.
• Ghi chú: Nếu bạn chưa biết về cách mà Database phân tích một câu lệnh SQL thì bạn có thể xem Video tôi đã phân tích chi tiết vấn đề này tại đây.

Bây giờ người dùng thứ hai thực hiện câu lệnh tìm kiếm với GENDER =’Male’.

Lúc này về mặt TEXT thì câu lệnh của người dùng thứ hai HOÀN TOÀN GIỐNG với câu lệnh của người dùng thứ nhất, do đó SQL Server thực hiện lấy luôn chiến lược thực thi đã lưu để áp dụng cho câu lệnh,

Chính vì vậy, câu lệnh thứ hai sử dụng luôn giải thuật là FULL TABLE SCAN (mặc dù nếu sử dụng Index thì sẽ tốt hơn rất nhiều lần).

Đây là một trong các trường hợp dẫn tới hiện tượng câu lệnh SQL lúc nhanh, lúc chậm.

Nếu các anh em muốn biết thêm các Case study và giải pháp tối ưu cơ sở dữ liệu trong thực tế, các anh em có thể đăng ký khoá học Từ điển tối ưu 100x hiệu năng của tôi. Khoá học này anh em có thể đăng ký 01 buổi trải nghiệm miễn phí. Anh em điền vào link sau để đăng ký trải nghiệm nhé:  Link đăng ký trải nghiệm khoá học.

4. Chốt lại điều gì ở đây?

Thứ nhất: khi gặp trường hợp câu lệnh SQL lúc nhanh lúc chậm thì hãy nghĩ ngay tới trường hợp Database đã lựa chọn các chiến lược thực thi khác nhau

Thứ hai: Cần phải hiểu rõ về cách thức hoạt động của một câu lệnh SQL khi gửi lên Database, như thế thì chúng ta mới có thể tối ưu trong các tình huống thực tế.

5. Một số bài viết về tối ưu khác có liên quan dành cho anh em

• Đọc cách thức tối ưu SQL giảm hơn 97% chỉ bằng một dấu chậm: Đọc bài viết tại đây
• Sử dụng JSON thiết kế trên các cơ sở dữ liệu Oracle, SQL Server, PostgreSQL, MySQL: Đọc bài viết tại đây
• Chiến lược thực thi của câu lệnh SQL: Đọc bài viết tại đây

6. Nếu bạn muốn liên hệ với tôi

Tác giả: Trần Quốc Huy – Founder & CEO Wecommit.

Các bạn có thể liên hệ, thảo luận các kiến thức về tối ưu cùng tôi qua kênh FB cá nhân của tôi

Facebook: https://www.facebook.com/tranquochuy.toiuu/