Muốn tối ưu SQL, các bạn nên bắt đầu từ đâu?

Tối ưu SQL không chỉ là câu chuyện viết lại câu lệnh cho ngắn hơn hay thêm một vài Index. Đây là một quá trình cần hiểu bản chất của hệ quản trị cơ sở dữ liệu, cách mà SQL được phân tích và thực hiện ra sao.

Trước khi học bất kỳ những kỹ năng nâng cao nào, tôi muốn anh em nhận ra sai lầm kinh điển nhất, thứ mà nhiều người cứ nghĩ là đang học và làm tối ưu SQL, nhưng bản chất chỉ nói về "cái vỏ", và vì thế đôi khi mất 5, 7 năm cũng chẳng thể thật sự làm được trong dự án.

1. Sai lầm phổ biến khi bắt đầu tối ưu SQL

Rất nhiều người nghĩ rằng tối ưu SQL là:

• Dùng Select đúng cột mình cần thay vì Select *
• Sử dụng Index (đôi khi nghĩ đơn giản là cứ chỗ nào Where thì đánh Index)
• Viết lại câu lệnh cho nó “ngắn” hơn
• Xóa bớt dữ liệu đi vì nhiều người tin rằng ít bản ghi sẽ giúp câu lệnh SQL nhanh hơn.
• Anh em tin rằng “lọc dữ bớt dữ liệu” trong câu lệnh thì sẽ khiến tối ưu SQL.
• Đổi chỗ thứ tự viết lệnh trong mệnh đề JOIN có thể khiến câu lệnh SQL nhanh hơn
• Hoặc một số anh em lại coi rằng cứ câu lệnh chậm thì nghĩ tới nâng cấp phần cứng.

Tất cả những thứ kể trên giống như anh em đang tìm cách tối ưu “theo từ khóa”, ai đó nói một từ khóa tối ưu và bạn sẽ đi thử.

Nếu đi theo kiểu lần mò ấy, thì có khi mãi cũng chẳng thể nào làm được việc.

Ngay cách tiếp cận ấy đã là không đi vào bản chất rồi, mà đi không vào bản chất thì làm sao mà biết thật sự giải pháp là gì.

Đôi khi chẳng may làm câu lệnh SQL chạy nhanh hơn, nhưng cũng không thể giải thích được vì sao nhanh, và cũng không lặp lại được thành công ấy trong lần khác.

Tôi sẽ demo cụ thể một số trường hợp để anh em mường tượng cụ thể hơn, những gì “lời đồn tối ưu” nó khác biệt với thực tế thế nào nhé.

1.1. Liệu đổi thứ tự câu lệnh trong JOIN hoặc WHERE sẽ tối ưu hiệu năng SQL

Để tôi giúp anh em hiểu rõ tư duy, từ đó về sau, anh em sẽ tự mình lập luận được khi đứng trước các vấn đề như kiểu đổi chỗ thứ tự thì câu lệnh SQL có nhanh hơn không.

Ghi chú là: Đổi chỗ nhưng logic thì không đổi nhé, chứ đổi chỗ mà đổi cả logic thì không nói đến làm gì.

Hỏi: Bây giờ câu nhanh nếu nhanh hơn thì vì cái gi ?

Đáp: Nó nhanh hơn vì đã thực hiện một cách thức nào đó lấy dữ liệu tốt hơn. Cái đấy gọi là chiến lược thực thi của câu lệnh SQL hay thuật ngữ trong tài liệu anh em sẽ thấy là SQL Execution Plan.

Một câu lệnh muốn thực hiện được, Database nó phải biết cần làm các bước gì, các bước ấy Input là gì, Output là gì, sau đó các bước nó có kết hợp với nhau không. Tất cả những thứ đó gọi là chiến lược thực thi.

Hỏi: Vậy làm sao để biết được nếu đổi chỗ thứ tứ câu lệnh trong JOIN hoặc WHERE thì câu lệnh SQL có tối ưu hơn không ?

Đáp: Cứ đổi chỗ rồi kiểm tra chiến lược thực thi. Nếu chiến lược thực thi không thay đổi thì nghĩa là hiệu năng không đổi. Thế thôi.

Tôi sẽ kiểm thử cho anh em tự tin.

Thực hiện trên Database Oracle, tôi sẽ viết 1 câu lệnh SQL, sau đó đổi thứ tự xuất hiện trong JOIN và WHERE để xem chiến lược thực thi của câu lệnh có thay đổi hay không.

Câu lệnh SQL ban đầu

SELECT 
    p.Id AS PostId,
    p.Title,
    u.DisplayName,
    u.Reputation,
    p.Score
FROM Posts p
JOIN Users u ON p.OwnerUserId = u.Id
WHERE 
    u.Reputation > 100000
    AND p.Score > 10
    AND p.PostTypeId = 1;

‍

Chiến lược thực thi của câu lệnh này như sau:

-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                        |  4144 | 21007   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                 |  4144 | 21007   (1)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                 | 26640 | 21007   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| USERS           |  2220 |  1051   (1)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IDX_REPUTATION  |  2220 |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | IDX_OWNERUSERID |    12 |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID         | POSTS           |     2 |    14   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

‍

Cái bên trường chính là chiến lược thực thi của câu lệnh SQL (hay gọi là SQL Execution Plan).

Nếu anh em lần đầu nhìn thấy thứ này, hãy tập trung vào mấy thông số sau cho nó dễ tiếp cận

• Cái phải xem đầu tiên là COST. Đây chính là trọng số mà Database ước tính chiến lược thực thi này sẽ tiêu tốn tài nguyên thế nào. Thường thì khi một câu lệnh SQL có nhiều chiến lược thực thi, Database sẽ hướng tới chọn cái có COST bé nhất. Trong trường hợp đang xét COST của câu lệnh là 21007.
• Xem số bước mà câu lệnh thực hiện, để chút nữa so sánh xem câu lệnh có bị thêm hay bớt bước nào không. Cái này xem ở cột ID ở ngoài cùng bên trái trong cái bảng chiến lược thực thi bên trên.

Bây giờ tôi sẽ đổi chỗ giữa bảng Posts  và bảng Users trong phần Join, đồng thời đổi luôn cả thứ tự trong phần Where

Câu lệnh SQL lúc này như sau:

SELECT p.Id     AS PostId,
       p.Title,
       u.DisplayName,
       u.Reputation,
       p.Score
  FROM Users u JOIN Posts p ON p.OwnerUserId = u.Id
 WHERE p.Score > 10 
 AND u.Reputation > 100000 
 AND p.PostTypeId = 1;

‍

Chiến lược thực thi của câu lệnh mới:

-------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                        |  4144 | 21007   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                         |                 |  4144 | 21007   (1)| 00:00:01 |
|   2 |   NESTED LOOPS                        |                 | 26640 | 21007   (1)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| USERS           |  2220 |  1051   (1)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | IDX_REPUTATION  |  2220 |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | IDX_OWNERUSERID |    12 |     2   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID         | POSTS           |     2 |    14   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

‍

Anh em có thể thấy, câu lệnh có chiến lược thực thi không có bất kỳ thay đổi gì hết

• Cost giữ nguyên
• Các bước thực thi giữ nguyên

Chốt hạ là: thay đổi thứ tự viết lệnh chẳng ảnh hưởng gì tới hiệu năng của câu lệnh SQL anh em nhé. Các ông Database không “ngu ngơ” thế đâu.

Điều này cũng hoàn toàn đúng với các cơ sở dữ liệu khác như Oracle, PostgreSQL, MySQL anh em nhé

‍

1.2. Hiểu lầm phổ biến: Cứ dùng Index là sẽ tăng hiệu năng câu lệnh SQL

Cứ câu lệnh chậm là anh em lại nghĩ tới Index đi, và nhiều người còn nhầm tưởng là cứ Index thì sẽ nhanh.

Nếu tối ưu mà cứng nhắc theo kiểu “khẩu quyết” thế này thì toang anh em ạ.

Tôi sẽ demo ngay lập tức cho anh em để thấy rằng, có những trường hợp database nhận Index thì hiệu năng câu lệnh SQL sẽ cực kỳ chậm luôn.

Ở bên trên tôi đã demo dùng Database Oracle rồi, phần này tôi sẽ demo với Database SQL Server để anh em tự tin rằng những chia sẻ này áp dụng thoải mái, không phụ thuộc loại Database nào hết.

Tôi sẽ thực hiện tạo Index trên cột Views của bảng Users cho demo này.

create index idx_views on Users(views)

‍

Bảng Users có hơn 2.4 triệu bản ghi.

select count(*) from Users
2465713

‍

Tôi sẽ thực hiện một câu lệnh Select trên bảng Users, với điều kiện lọc là cột Views đã có Index.

select * from Users where Views = 0

‍

Nếu anh em nghĩ rằng cột Views đã có Index, Database SQL Server chắc hẳn phải sử dụng nó.

Nhưng sự thật lại không phải thế.

Đây là chiến lược thực thi của câu lệnh SQL trên.

‍

Câu lệnh này sử dụng giải thuật là Clustered Index Scan (Clustered), và tên của Index được sử dụng ở đây là PK_Users_ID.

Không hề sử dụng bất kỳ Index nào trên cột Views cả.

Ghi chú: Trong SQL Server, nếu anh em thấy chiến lược thực thi có cụm từ Clustered Index Scan thì bản chất hệ thống đang quét toàn bộ bảng anh em nhé (Tức là nếu Table của anh em có dữ liệu chiếm 10GB trên ổ cứng, nó sẽ quét toàn bộ 10GB ấy để tìm ra thứ mà câu lệnh SQL đang yêu cầu).

Tôi sẽ đi sâu hơn ở chỗ này, kiểm thử thực tế thì câu lệnh SQL bên trên sẽ chiếm tài nguyên như thế nào trong trường hợp:

• Trường hợp 1: Sử dụng theo chiến lược thực thi của SQL Server đang chọn bên trên (quét toàn bộ Table Users chứ không sử dụng Index IDX_VIEWS trên cột Views).
• Trường hợp 2: Tôi sẽ ép chiến lược thực thi của SQL Server phải sử dụng Index IDX_VIEWS mà tôi tạo ra, để xem có tối ưu hơn không.

Tôi thường sử dụng lệnh sau khi tối ưu SQL và so sánh sự thay đổi hiệu năng (dạng trước và sau tối ưu ) trong dự án

SET STATISTICS IO ON

‍

Đánh giá thông số hiệu năng khi Database không sử dụng Index

select * from Users where Views = 0

(697138 rows affected)
Table 'Users'. Scan count 1, logical reads 44530, 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.

‍

Thông số trên nghĩa là:

• Câu lệnh có 697.138 bản ghi thỏa mãn điều kiện.
• Để tìm được 697.138 bản ghi, SQL Server phải thực hiện quét qua 44.530 pages dữ liệu. (Một page mặc định là 8KB).

Bây giờ tôi sẽ bắt Database SQL Server phải sử dụng Index IDX_VIEWS trong chiến lược thực thi, và xem thông số bây giờ thay đổi ra sao.

select * from Users with(index(idx_views)) where Views = 0 

(697138 rows affected)
Table 'Users'. Scan count 1, logical reads 2136206, physical reads 0, page server reads 0, read-ahead reads 7, 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.

‍

Thông số khi này đã có sự thay đổi rõ rệt:

• Bây giờ để tìm ra cùng số lượng bản ghi (697.138), SQL Server đã phải làm việc với 2.136.206 pages (nhiều hơn ~48 lần so với trước đây).

Anh em thấy đấy, không phải lúc nào sử dụng Index thì cũng nhanh đâu.

Chính vì SQL Server biết rõ điều này, nên trong trường hợp chúng ta kiểm tra mặc định lúc đầu, hệ thống đã ra quyết định là thà quét toàn bộ bảng còn hơn là phải dùng Index trên cột Views.

‍

2. Những nội dung mà bạn có thể bắt đầu khi học về tối ưu SQL

Cách tiếp cận sẽ giúp anh em tiết kiểm rất nhiều thời gian là học từ kiến trúc.

Thật sự hiểu được Database nó hoạt động thế nào.

Chúng ta gửi 1 câu lệnh thì Database sẽ xử lý ra sao.

Nếu nhiều câu lệnh cùng chạy thì những thứ gì sẽ xảy ra (dữ liệu được lấy từ đâu, memory xử lý gì, các tài nguyên xung đột nào diễn ra bên trong Database …)

Đi theo cách này, anh em vừa mang lại kết quả rất rõ trong công việc, lại vừa tự tin khi đi phỏng vấn, vì đã làm được thì lý không ai bắt bẻ được.

Tôi đã từng chia sẻ một số nguồn video mà anh em có thể bắt đầu với tối ưu SQL, anh em có thể xem ở đây:

2.1. Ưu điểm khi học tối ưu SQL từ kiến trúc

Những ưu điểm vượt trội khi bạn học tối ưu SQL theo cách thức này:

• Cực kỳ tự tin: Thay vì khoanh vùng sự cố theo kinh nghiệm, theo “thần linh mách bảo” thì chúng ta có những lập luận cụ thể, giải thích rõ ràng. Khi có năng lực này rồi thì ở tình huống nào, trong dự án nào anh em cũng thấy rất tự tin.
• Lợi thế cạnh tranh: Theo những hiểu biết và các dự án mà tôi từng được làm, số người thật sự hiểu về tối ưu SQL, tối ưu Database từ kiến trúc chiếm một phần rất nhỏ. Thường những người này sẽ có lợi thế rất lớn trong dự án, họ sẽ có xu hướng được giao những nhiệm vụ quan trọng, những dự án quan trọng. Mà càng được giao dự án trọng điểm, họ lại càng có cơ hội giỏi hơn. Mọi thứ tạo nên một lợi thế cạnh tranh bền vững.
• Học nhanh: khi đã hiểu rõ kiến trúc một loại cơ sở dữ liệu (ví dụ: SQL Server), anh em có thể học và tiếp cận sang loại cơ sở dữ liệu khác rất nhanh chóng (Oracle, MySQL, PostgreSQL, MariaDB…). Về bản chất lúc đầu học chúng ta có thể mất thời gian, nhưng về đường dài nó lại là cách học tiết kiệm thời gian nhất.

2.2. Nhược điểm khi học tối ưu SQL từ kiến trúc

Một số điều anh em phải cân nhắc khi quyết định học tối ưu SQL theo cách tiếp cận từ kiến trúc

• Bắt đầu khó hơn: Những bài blog, bài viết chia sẻ về Tips, Tricks thì anh em có thể tìm được ở bất kỳ đâu. Nhưng việc có ai đó chia sẻ cách tối ưu từ kiến trúc thì ít hơn hẳn, điều này một phần do số người thật sự đang làm được nó trong thực tế là không nhiều.
• Tài liệu trừu tượng: Anh em  có thể bắt đầu học từ các kiến tức được mô tả trong Documents của các hãng như Oracle, PostgreSQL, nhưng khá là khó hiểu và lắp ghép những kiến thức về kiến trúc ấy thành một thứ bạn có thể hiểu ngay lập tức. Chúng ta sẽ cần thời gian suy ngẫm lâu hơn, và thật sự tập trung khi học nó.
• Ít người dẫn dắt: Dù có nỗ lực tới đâu, nhưng chắc chắn sẽ có lúc anh em gặp vướng mắc, đặc biệt là khi áp dụng cách thức tối ưu SQL từ kiến trúc vào một bài toán thực tế. Trong những giai đoạn này, anh em cần có người dẫn dắt. Họ vừa là người hiểu rõ kiến trúc Database, vừa phải là người có năng lực tối ưu SQL, đồng thời có khả năng truyền đạt những kinh nghiệm ấy cho bạn. Số lượng người như vậy là ít.

3. Hành trình học tối ưu SQL của chính tôi

Tôi từng chia sẻ lại hành trình mà tôi đã trải qua, từ giai đoạn trang giấy trắng đến thời điểm có thể tự tin đối diện với nhiều dự án tối ưu SQL, tối ưu Database.

Anh em có thể xem hành trình ấy tại đây

4. Kết luận

Cách tối ưu SQL hiệu quả, bài bản nhất là đi theo kiến trúc. Điều quan trọng là chúng ta không chỉ muốn làm một câu lệnh SQL nhanh hơn, hay một chức năng chạy, cái thật sự có giá trị với anh em là

• Lặp lại được thành công ấy nhiều lần, trong nhiều dự án, để những kết quả đó tạo ra sự nghiệp tốt hơn cho anh em.
• Thật sự hiểu và giải thích được những đề xuất tối ưu của mình cho bất kỳ ai. Điều này tạo ra uy tín, sự tin tưởng của sếp, đồng nghiệp và khách hàng.

Với cách thức tối ưu SQL từ kiến trúc này, nó cũng đã thay đổi rất nhiều sự nghiệp của tôi.

Và tôi rất hi vọng, những thứ tuyệt vời ấy sẽ tới với anh em.

‍