Là một lập trình viên, chắc chắn chắn bạn sẽ phải thao tác làm việc với Database. Mặc dùng những framework ORM (Object Relation Mapping) bây chừ cũng cấp tương đối nhiều hàm tiến hành việc thao tác với DB. Tuy vậy không cần lúc làm sao nó cũng khá được tối ưu. Và điều ấy vẫn dựa vào vào chủ yếu cách sử dụng của doanh nghiệp nữa.
Bạn đang xem: Cách tối ưu hóa truy vấn sql
Bài bây giờ của mình sẽ cho chính mình 5 mẹo để tối ưu câu truy nã vấn, giúp nâng cấp performace, cải thiện hiệu quả của ứng dụng
1. Học bí quyết đánh Index hợp lýHọc cách làm thế nào để đánh index cho phù hợp là các tốt nhất có thể là nhằm tăng perfomance của câu truy vấn vấn. Index chất nhận được truy cập một bí quyết nhanh hơn tới db một trong những trường hòa hợp điển hình. Những người dân mới thao tác làm việc với db hay cảm thấy việc đánh index này cao thâm hoặc vô cùng khó. Họ hay là chẳng đánh index cho bất kể cái gì, hoặc là nỗ lực để tiến công index cho phần đa thứ. Dĩ nhiên, không có phuơng án nào trong số đó là đúng cả. Với việc không tấn công index cho bất cứ thứ gì, câu truy hỏi vấn của bạn sẽ có tài năng bị chậm. Còn nếu đánh index cho tất cả mọi thứ, việc update xuất xắc insert có khả năng sẽ bị chậm lại.
Nếu chúng ta không chắc chắn rằng mình đủ gọi về index, hãy tìm hiểu nhiều hơn về nó. Chú ý vào việc xem xét xem ngôi trường nào cần đánh index, trường như thế nào không, với đánh index theo kiểu nào, B-tree giỏi Bit
Map .v.v.v
Một giữa những sai lầm mà lại mình thấy nhiều người mới hay gặp mặt phaỉ, đó là lúc nào thì cũng sử dụng SELECT * mặc dầu các không ít trường (cột) chúng ta không cần tin tức của chúng.
Nếu bảng nhỏ, việc lấy thêm những trường này không gây ra rất nhiều khác biệt. Tuy nhiên với tập tài liệu lớn hơn, bài toán chỉ định rất nhiều cột mà bạn muốn lấy sẽ giảm rất nhiều thời gian truy vấn vấn.
Vì thế, ví dụ trường hợp bạn chỉ việc lấy đông đảo thông tin: tên, ngày sinh, giới tính của những USER, thay do sử dụng
SELECT * FROM USER ...thì hãy sử dụng
SELECT NAME, DATE_OF_BIRTH, GENDER FROM USER ...để câu truy vấn vấn của chính mình có giỏi độ hài lòng hơn nhé
Cũng tương tự như như thế, nếu bạn chỉ việc một con số gới hạn các record trả về, thì hãy sử dụng LIMIT (hay tuơng tự cầm ở những DB khác).
SELECT * FROM USER;Vis dụ, nếu như bạn chỉ cần hiển thị 10 record trước tiên trong 50.000 record của bảng USER, thì câu truy nã vấn này đang báo với DB của người tiêu dùng để hạn chế lượng dữ liệu cần tra cứu hiếu. Bằng phương pháp này, DB sẽ dừng lại việc kiếm tìm kiếm sau thời điểm đã kiếm được 10 record thay vì chưng sẽ phải scan toàn cục bảng USER
SELECT * FROM USER LIMIT 10;3. Tránh sử dụng function mặt tay toán tử đối chiếu (hay đúng hơn là tránh sử dụng hàm đối với cột của bảng)Function là 1 cách thuận lợi để xử lý các task phúc tạp mà chúng ta có thể sử dụng nó cả sinh sống mệnh đề SELECT xuất xắc WHERE. Mặc dù nhiên,ứng dụng chúng trong mệnh đề WHERE hoàn toàn có thể gây ra những vụ việc ko giỏi về performance. Hãy ngó qua ví dụ bên dưới đây
4. Xem xét việc sa thải các câu truy nã vấn bé tuơng quanCâu truy tìm vấn nhỏ tuơng quan là đầy đủ câu truy nã vấn con phục thuộc vào câu truy vấn ngoài. Nó sử dụng tài liệu lấy được từ bỏ câu truy hỏi vấn quanh đó trong mệnh đề WHERE. Ví dụ bạn muốn lấy danh sách toàn bộ những us cơ mà đã ủng hộ. Chúng ta có thể sử dụng câu tróc nã vấn
SELECT user_id, last_name FROM users WHERE EXISTS (SELECT * FROM donationuser WHERE donationuser.user_id = users.user_id);Trong trường vừa lòng này, câu tầm nã vấn con sẽ chạy 1 lần với từng record của câu truy nã vấn chính. Ví dụ là các lần xét cho tới 1 record của câu truy nã vấn chính, câu tầm nã vấn nhỏ lại được chạy với điều kiện WHERE trong các số ấy là cực hiếm user_id của record đang được xét của câu truy hỏi vấn chính. Cho nên vì vậy nó câu tầm nã vấn chưa hiệu quả
Thay vào đó, ta có thể sử dụng
SELECT DISTINCT users.user_id FROM users INNER JOIN donationuser ON users.user_id = donationuser.user_id;Nếu bao gồm hàng triệu user vào DB, câu lệnh với câu truy nã vấn nhỏ tuơng quan vẫn hầu như rất có thể gây kém kết quả hơn thực hiện INNER JOIN chính vì ta nên chạy câu truy vấn vấn con hàng triệu lần. Nhưng nếu bạn đang search kiếm những người donations được tạo vày 1 user chỉ định, thì thực hiện nó lại là một trong những ý tưởng k tồi. Theo quy tắc chung, nếu như khách hàng tìm kiếm nhiều hoặc đa số các hàng, hãy nỗ lực tránh sử dụng những truy vấn con tương quan. Mặc dù nhiên, hãy lưu giữ rằng vấn đề sử dụng những truy vấn bé tương quan có thể là chẳng thể tránh ngoài trong một số trường hợp.
Xem thêm: Hướng dẫn cách xóa khung text box trong word 2010, 2013, 2016, 2019, 2021
5. Tránh sử dụng ký trường đoản cú %, _ sinh sống đầu quý hiếm tìm tìm LIKEBất cứ bao giờ có thể, né sử dụng lượt thích theo cách này
SELECT * FROM users WHERE name like "%bar%";Sử dụng % sinh hoạt đầu sẽ ngăn vấn đề db áp dụng index (nếu có) của cột name. Vì thế sẽ làm giảm tốc độ truuy vấn. Vày thế, nếu có thể (spec cho phép), hãy tránh
SELECT * FROM users WHERE name like "bar%";Bài viết của chính bản thân mình được dịch từ bỏ nguồnhttp://www.vertabelo.com/blog/technical-articles/5-tips-to-optimize-your-sql-queries
những lệnh SQL rất có thể dùng để truy xuất tài liệu từ bất cứ cơ sở tài liệu nào. Nếu như bạn đã từng truy hỏi xuất tin tức từ cơ sở dữ liệu, rất có thể bạn đã gặp gỡ những lệnh truy nã vấn chậm. Để có hiệu năng tốt hơn, chúng ta cần các câu lệnh về tối ưu, nhanh hơn, và hiệu quả hơn. Bài viết này trình bày cách tối ưu hóa những lệnh truy vấn vấn SQL để có công dụng tốt hơn. Chủ đề buổi tối ưu hóa truy hỏi vấn là rất lớn nhưng công ty chúng tôi sẽ nỗ lực đề cập đến những điểm quan trọng nhất. Trong bài viết này, cachseo.com sẽ không còn đi sâu vào phân tích cơ sở dữ liệu, mà lại là phần nhiều thủ thuật đơn giản dễ dàng khi các bạn tự học SQL rất có thể điều chỉnh tầm nã vấn, có thể được vận dụng nhằm nâng cấp hiệu suất tức thì lập tức.Giới thiệu về tối ưu hóa
Tối ưu hóa truy vấn vấn là 1 trong kĩ năng quan trọng đặc biệt đối với các Data Analyst, nguời khai thác dữ liệu từ database với tần suất cao. Để nâng cấp hiệu quả của những truy vấn, các lập trình viên với Data Analyst cần hiểu được hình thức tối ưu hóa tầm nã vấn cùng và giải pháp công cụ này lựa chọn một lối truy cập và sẵn sàng kế hoạch triển khai truy vấn. Việc kiểm soát và điều chỉnh truy vấn có liên quan tới những kỹ năng về những kĩ thuật như phương tiện tối ưu theo chi phí và theo khiếp nghiệm, cùng với những chính sách giúp so với kế hoạch thực thi truy vấn (execution plan) bởi vì CSDL cung cấp. Cách tốt nhất để kiểm soát và điều chỉnh hiệu suất là tìm giải pháp viết những truy vấn theo rất nhiều cách thức khác nhau, tự đó so sánh kế hoạch thực thi của từng truy vấn.Các tuyệt kỹ chung để tối ưu hóa tầm nã vấn lúc tự học tập SQL
Mỗi tip dưới đây đã được kiểm tra bằng cách chạy cả nhì truy vấn cội và truy vấn vấn đã sửa đổi khi truy nã vấn tài liệu từ cơ sở dữ liệu 11g Oracle mẫu, đặc biệt quan trọng trên lược đồ vật Sales (Sales Schema). Tôi đã khắc ghi thời gian trung bình của mỗi truy vấn để mô tả sự tối ưu khi xây dựng những truy vấn tác dụng hơn.Lưu ý:Khi tự học SQL, những kỹ thuật tối ưu truy tìm vấn tất cả sự khác biệt nhau giữa các hệ quản lí trị cơ sở dữ liệu khác nhau, nhờ vào vào Database Engine của từng hệ quản ngại trị CSDL. Độc giả nên lưu tâm điều này.Tip #1: sử dụng Tên Cột thay vì chưng * vào lệnh SELECT – Tips cần chăm chú khi tự học tập SQL
Nếu bạn chỉ đang chọn một số ít cột trường đoản cú bảng, không cần sử dụng lệnh SELECT *. Dù bí quyết viết này dễ, tuy thế lại gây tốn nhiều thời gian hơn để cơ sở dữ liệu có thể ngừng truy vấn. Bằng cách chỉ chọn đầy đủ cột phải thiết, các bạn đang giảm bớt kích độ lớn của bảng kết quả, bớt lưu lượng mạng, và có tác dụng tăng tính năng của truy nã vấn.Ví dụ:Truy vấn gốc:SELECT * FROM SH.Sales;Truy vấn đã có cải thiện:SELECT s.prod_id FROM SH.sales s;
Tip #2: Tránh gửi mệnh đề HAVING vào lệnh SELECT
Tip trang bị hai trong lúc tự học SQL về tối ưu hóa lệnh truy tìm vấn là mệnh đề HAVING có chức năng lọc sau thời điểm các hàng vẫn được lựa chọn và được áp dụng như một filter. Mệnh đề này không có nhiều tính năng với lệnh SELECT. Cách hoạt động của nó là quét qua bảng hiệu quả cuối cùng để đối chiếu và bỏ những sản phẩm không đáp ứng nhu cầu điều kiện của mệnh đề HAVING.Ví dụ:Truy vấn gốc:SELECT s.cust_id,count(s.cust_id)FROM SH.sales sGROUP BY s.cust_id
HAVING s.cust_id != "1660" và s.cust_id != "2";Truy vấn đã được cải thiện:SELECT s.cust_id,count(cust_id)FROM SH.sales s
WHERE s.cust_id != "1660"AND s.cust_id !="2"GROUP BY s.cust_id;
Tip #3: vứt bỏ các mệnh đề DISTINCT không cần thiết – Tự học tập SQL
Khi xem xét ví dụ bên dưới đây, từ khóa DISTINCT trong truy hỏi vấn nơi bắt đầu là không quan trọng vì thương hiệu bảng đã đựng p.ID là khóa bao gồm của bảng – đây là một phần của kết quả. Vấn đề đưa mệnh dề DISTINCT sẽ khiến CSDL tiến hành thêm một phép so sánh để vứt bỏ các loại trùng nhau trong tập trung quả.Ví dụ:Truy vấn gốc:SELECT DISTINCT * FROM SH.sales sJOIN SH.customers c
ON s.cust_id= c.cust_id
WHERE c.cust_marital_status = "single";Truy vấn đã có cải thiện:SELECT * FROM SH.sales s JOINSH.customers c
ON s.cust_id = c.cust_id
WHERE c.cust_marital_status="single";
Tip #4: Un-nest các truy vấn nội cỗ (Sub-query)
Viết lại những sub-query để giúp truy vấn chạy hiệu quả và buổi tối ưu hơn. Chú ý chung, câu hỏi unnest các sub-query luôn luôn được triển khai với sub-query tác động với buổi tối đa một bảng từ bỏ mệnh đề FROM, được sử dụng trong số mệnh đề ANY, ALL, và EXISTS. Một sub-query ko tương quan, hay một sub-query với nhiều hơn thế một bảng trường đoản cú mệnh đề FROM, sẽ được thiết kế phẳng nếu dựa vào ngữ cảnh của tầm nã vấn đó.Ví dụ:Truy vấn gốc:SELECT *FROM SH.products pWHERE p.prod_id =(SELECT s.prod_id
FROM SH.sales s
WHERE s.cust_id = 100996AND s.quantity_sold = 1 );Truy vấn đã có được cải thiện:SELECT p.*FROM SH.products p, sales s
WHERE p.prod_id = s.prod_id
AND s.cust_id = 100996AND s.quantity_sold = 1;
Tip #5:Cân nhắc sử dụng mệnh đề IN lúc truy vấn một cột đã có được đánh index
Mệnh đề IN hoàn toàn có thể được khai quật cho những lệnh truy nã vấn sử dụng bảng đã được đánh index, và đồng thời, luật pháp tối ưu hóa rất có thể sắp xếp danh sách IN để khớp với thứ tự phân nhiều loại của chỉ số, mang đến lệnh truy vấn vấn công dụng hơn. Cho nên vì thế khi trong quy trình thực hành cùng tự học tập SQL, cần chăm chú rằng list IN chỉ được chứa những hàm, hoặc những giá trị là hằng số trong việc thực hiện khối truy vấn, lấy ví dụ như như các tham chiếu ngoài.Ví dụ:Truy vấn gốc:SELECT s.*FROM SH.sales sWHERE s.prod_id = 14OR s.prod_id = 17;Truy vấn đã cải thiện:SELECT s.*FROM SH.sales s
WHERE s.prod_id IN (14, 17)
Tip #6:Sử dụng EXISTS núm cho DISTINCT khi kết nối các bảng bao gồm một hoặc nhiều liên kết.
Từ khóa DISTINCT có chức năng chọn toàn bộ cột vào bảng, phân tích cùng lọc ra các cột bị trùng lặp. Cố vào đó, nếu bạn sử dụng sub-query với từ bỏ khóa EXISTS, chúng ta cũng có thể tránh bài toán phải trả lại tổng thể một bảng.Ví dụ:Truy vấn gốc:SELECT DISTINCT c.country_id, c.country_nameFROM SH.countries c,SH.customers e
WHERE e.country_id = c.country_id;Truy vấn đã có được cải thiện:SELECT c.country_id, c.country_name
FROM SH.countries c
WHERE EXISTS (SELECT "X" FROM SH.customers e
WHERE e.country_id = c.country_id);
Tip #7: Sử dụng UNION ALL cụ cho UNION
Mệnh đề UNION ALL cấp tốc hơn UNION bỏi vì mệnh đề UNION ALL quanh đó tới những điểm trùng lập với mệnh đề UNION lại tìm các điểm giống nhau trong bảng khi lựa chọn hàng, dù có điểm trùng lặp hay là không hãy ưu tiên áp dụng UNION ALL khi họ biết chắc chắn rằng mỗi chiếc trong kết quả sẽ là tuyệt nhất hoặc tất cả thể gật đầu việc trùng lặp.Ví dụ:Truy vấn gốc:SELECT cust_idFROM SH.sales
UNIONSELECT cust_id
FROM customers;Truy vấn sẽ cải thiện:SELECT cust_id
FROM SH.sales
UNION ALLSELECT cust_id
FROM customers;
Tip #8:Tránh sử dụng OR trong số mệnh đề vừa lòng nhiều điều kiện
Trong trường hợp khi bạn tự học SQL, mỗi khi chúng ta đặt ‘OR’ vào condition kết nối, truy vấn sẽ đủng đỉnh đi ít nhất một hoặc nhị thừa sốVí dụ:Truy vấn gốcSELECT *FROM SH.costs c
INNER JOIN SH.products phường ON c.unit_price =p.prod_min_price OR c.unit_price = p.prod_list_price;Truy vấn đang cải thiênSELECT *FROM SH.costs c
INNER JOIN SH.products p ON c.unit_price =p.prod_min_price
UNION ALLSELECT *FROM SH.costs c
INNER JOIN SH.products p. ON c.unit_price =p.prod_list_price;
Tip #9:Tránh để hàm số bên đề nghị của toán tử so sánh
Các hàm số hay phương pháp rất thường được áp dụng với truy hỏi vấn SQL tương ứng. Viết lại truy vấn vấn bằng cách bỏ những hàm tổng hợp sẽ tăng công suất đáng kế, đặc biệt quan trọng với các cột đã có được đánh index (chỉ mục).Ví dụ:Truy vấn gốcSELECT *FROM SH.salesWHERE EXTRACT (YEAR FROM TO_DATE (time_id, ‘DDMON-RR’)) = 2001 and EXTRACT (MONTH FROMTO_DATE (time_id, ‘DD-MON-RR’)) =12;Truy vấn đang cải thiện:SELECT * FROM SH.sales
Tip #10:Loại bỏ những phép toán thừa
Khi làm hoặc tự học tập SQL, sẽ có lúc bạn triển khai các phép toán vào một statement của SQL. Những phép toán có thể làm giảm hiệu suất đáng nhắc nếu phương pháp viết không phù hợp. Những lần truy vấn search thấy một hàng, nó sẽ tiến hành lại phép toán. Vì chưng vậy, việc sa thải phép toán không cần thiết sẽ giúp truy vấn chạy cấp tốc hơn hết sức nhiều.Ví dụ:Truy vấn gốc:SELECT *FROM SH.sales sWHERE s.cust_id + 10000 < 35000;Truy vấn đang cải thiện:SELECT *FROM SH.sales s
WHERE s.cust_id < 25000;
Bạn cũng có thểThích
