Với một số lượng lớn record trong DB trong các phần mềm liên quan đến nhiều nghiệp vụ thì phương pháp cần thiết để tốc độ của hệ thống được đảm bảo là phải tối ưu hóa từng dòng lệnh SQL.
Sau đây mình xin giới thiệu một vài thủ thuật để tối ưu câu lệnh SQL
1. Thử không sử dụng select * để truy vấn SQL, nhưng ta sẽ dùng select các trường cụ thể
Thông thường
1 2 | <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee<span class="token punctuation">;</span> |
Cách tốt hơn
1 2 | <span class="token keyword">select</span> id，name <span class="token keyword">from</span> employee<span class="token punctuation">;</span> |
Bằng cách chỉ sử dụng các trường bắt buộc, chúng ta có thể tiết kiệm tài nguyên của mình
2. Nếu bạn biết rằng chỉ có một kết quả truy vấn, nên sử dụng limit 1
Thông thường
1 2 | <span class="token keyword">select</span> id，name <span class="token keyword">from</span> employee <span class="token keyword">where</span> name<span class="token operator">=</span>'jay |
Cách tốt hơn
1 2 | <span class="token keyword">select</span> id，name <span class="token keyword">from</span> employee <span class="token keyword">where</span> name<span class="token operator">=</span><span class="token string">'jay'</span> <span class="token keyword">limit</span> <span class="token number">1</span><span class="token punctuation">;</span> |
Sau khi thêm limit 1, khi một bản ghi tương ứng được tìm thấy, nó sẽ không tiếp tục quét xuống, và hiệu quả sẽ được cải thiện rất nhiều
3. Hạn chế sử dụng or trong mệnh đề where truy vấn có điều kiện
Thông thường
1 2 | <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> userid <span class="token operator">=</span> <span class="token number">1</span> <span class="token operator">or</span> age <span class="token operator">=</span> <span class="token number">18</span> |
Cách tốt hơn
1 2 3 4 5 | <span class="token comment">//sử dụng union all</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> userid<span class="token operator">=</span><span class="token number">1</span> <span class="token keyword">union</span> <span class="token keyword">all</span> <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> age <span class="token operator">=</span> <span class="token number">18</span> |
Việc sử dụng or có thể làm mất hiệu lực index và do đó nó yêu cầu quét toàn bộ bảng.
4, Tối ưu limit với chỉ số offset
Thông thường
1 2 3 | <span class="token keyword">select</span> id，name，age <span class="token keyword">from</span> employee <span class="token keyword">limit</span> <span class="token number">10000</span>，<span class="token number">10</span><span class="token punctuation">;</span> lấy từ thứ tự thứ <span class="token number">10000</span> của tập kết quả <span class="token punctuation">,</span> lấy <span class="token number">10</span> phần tử |
Cách tốt hơn
1 2 3 4 5 6 | <span class="token comment">//Cách 1:</span> <span class="token keyword">select</span> id，name <span class="token keyword">from</span> employee <span class="token keyword">where</span> id<span class="token operator">></span><span class="token number">10000</span> <span class="token keyword">limit</span> <span class="token number">10.</span> <span class="token comment">//Cách 2: order by + index</span> <span class="token keyword">select</span> id，name <span class="token keyword">from</span> employee <span class="token keyword">order</span> <span class="token keyword">by</span> id <span class="token keyword">limit</span> <span class="token number">10000</span>，<span class="token number">10</span> |
Nếu bạn sử dụng giải pháp1, nó sẽ trả về bản ghi truy vấn cuối cùng (offset), để bạn có thể bỏ qua offset, hiệu quả đã được cải thiện rất nhiều.
Tùy chọn hai sử dụng thứ tự theo + index, cũng có thể cải thiện hiệu quả truy vấn.
5 Tối ưu lệnh like
Thông thường
1 2 | <span class="token keyword">select</span> userId，name <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> userId <span class="token operator">like</span> <span class="token string">'%123'</span><span class="token punctuation">;</span> |
Cách tốt hơn
1 2 | <span class="token keyword">select</span> userId，name <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> userId <span class="token operator">like</span> <span class="token string">'123%'</span><span class="token punctuation">;</span> |
Trường hợp %123 sẽ quét toàn bộ bảng trong trường hợp 123%, trình tối ưu hóa đang sử dụng chỉ mục chúng ta đã tạo và thực hiện tìm kiếm thay vì quét
6. Sử dụng điều kiện where để giới hạn dữ liệu được truy vấn để tránh trả lại các row thừa
Thông thường
1 2 | List<span class="token operator"><</span>Long<span class="token operator">></span> userIds <span class="token operator">=</span> sqlMap<span class="token punctuation">.</span>queryList<span class="token punctuation">(</span><span class="token string">"select userId from user where isVip=1"</span><span class="token punctuation">)</span><span class="token punctuation">;</span><span class="token keyword">boolean</span> isVip <span class="token operator">=</span> userIds<span class="token punctuation">.</span><span class="token keyword">contains</span><span class="token punctuation">(</span>userId<span class="token punctuation">)</span><span class="token punctuation">;</span> |
Cách tốt hơn
1 2 | Long userId <span class="token operator">=</span> sqlMap<span class="token punctuation">.</span>queryObject<span class="token punctuation">(</span><span class="token string">"select userId from user where userId='userId' and isVip='1' "</span><span class="token punctuation">)</span><span class="token keyword">boolean</span> isVip <span class="token operator">=</span> userId！<span class="token operator">=</span><span class="token boolean">null</span><span class="token punctuation">;</span> |
Kiểm tra dữ liệu nào là cần thiết, tránh trả lại dữ liệu không cần thiết và tiết kiệm chi phí
7, Bạn nên tránh sử dụng toán tử! = Hoặc <> trong mệnh đề where càng nhiều càng tốt
Thông thường
1 2 | <span class="token keyword">select</span> age<span class="token punctuation">,</span>name <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> age <span class="token operator"><></span><span class="token number">18</span><span class="token punctuation">;</span> |
Cách tốt hơn
1 2 3 4 | <span class="token comment">//Bạn có thể sử dụng hai câu sql</span> <span class="token keyword">select</span> age<span class="token punctuation">,</span>name <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> age <span class="token operator"><</span><span class="token number">18</span><span class="token punctuation">;</span> <span class="token keyword">select</span> age<span class="token punctuation">,</span>name <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> age <span class="token operator">></span><span class="token number">18</span><span class="token punctuation">;</span> |
Sử dụng! = Và <> có khả năng làm mất hiệu lực của index
8. Sử dụng distinct một cách cẩn trọng
Từ khóa distinct thường được sử dụng để lọc các bản ghi trùng lặp để trả về các bản ghi duy nhất. Khi được sử dụng trong trường hợp truy vấn một trường hoặc một vài trường, nó mang lại hiệu quả tối ưu hóa cho truy vấn.
Tuy nhiên, khi có nhiều trường, nó sẽ làm giảm đáng kể hiệu quả truy vấn
Thông thường
1 2 | <span class="token keyword">SELECT</span> <span class="token keyword">DISTINCT</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span><span class="token punctuation">;</span> |
Cách tốt hơn
1 2 | <span class="token keyword">select</span> <span class="token keyword">DISTINCT</span> name <span class="token keyword">from</span> <span class="token keyword">user</span><span class="token punctuation">;</span> |
Thời gian CPU và thời gian chiếm dụng của câu lệnh với distinct cao hơn câu lệnh không distinct
9. Loại bỏ index dư thừa và trùng lặp
Thông thường
1 2 3 | <span class="token keyword">KEY</span> <span class="token punctuation">`</span>idx_userId<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>userId<span class="token punctuation">`</span><span class="token punctuation">)</span> <span class="token keyword">KEY</span> <span class="token punctuation">`</span>idx_userId_age<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>userId<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>age<span class="token punctuation">`</span><span class="token punctuation">)</span> |
Cách tốt hơn
1 2 3 | <span class="token comment">// Xóa index userId, vì index kết hợp (A, B) tương đương với việc tạo các index(A) và (A, B) </span> <span class="token keyword">KEY</span> <span class="token punctuation">`</span>idx_userId_age<span class="token punctuation">`</span> <span class="token punctuation">(</span><span class="token punctuation">`</span>userId<span class="token punctuation">`</span><span class="token punctuation">,</span><span class="token punctuation">`</span>age<span class="token punctuation">`</span><span class="token punctuation">)</span> |
Các index trùng lặp cần được duy trì và trình tối ưu hóa cũng cần xem xét từng cái một khi tối ưu hóa các truy vấn, điều này sẽ ảnh hưởng đến hiệu suất
10 Xem xét sử dụng các giá trị mặc định thay vì null trong mệnh đề where
Thông thường
1 2 | <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> age <span class="token operator">is</span> <span class="token operator">not</span> <span class="token boolean">null</span><span class="token punctuation">;</span> |
Cách tốt hơn
1 2 | <span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> <span class="token keyword">user</span> <span class="token keyword">where</span> age<span class="token operator">></span><span class="token number">0</span><span class="token punctuation">;</span> <span class="token comment">//Set 0 as default</span> |
Trên đây là một vài cách để bạn có thể tối ưu truy vấn của mình, các bạn có thể có thêm nhiều ý tưởng khác việc tối ưu hóa cũng như tăng tốc độ truy vấn SQL của mình.
Nguồn tham khảo:
https://towardsdatascience.com/how-to-optimize-sql-queries-742177cd5cc6?gi=a4b2e25af429