Mức độ kiến thức cần thiết để hiểu bài viết: hiểu biết chung về cơ sở dữ liệu và SQL, một số kinh nghiệm thực tế với DBMS.
Có lẽ điều quan trọng nhất bạn có thể học để viết các truy vấn SQL hiệu quả là lập chỉ mục. Tuy nhiên, ở vị trí thứ hai, rất gần phía sau, là kiến thức mà nhiều máy khách SQL yêu cầu cơ sở dữ liệu thực hiện nhiều "công việc không cần thiết nhưng cần thiết " . Nhắc lại theo tôi:
Công việc không cần thiết nhưng bắt buộc
“Công việc không cần thiết nhưng bắt buộc” là gì? Như Captain Rõ ràng đã nói với chúng tôi, cô ấy:
không cần thiết
Hãy để ứng dụng khách của chúng tôi cần dữ liệu sau:
Không có gì bất thường. Chúng tôi đang làm việc với cơ sở dữ liệu phim (chẳng hạn như cơ sở dữ liệu Sakila ) và muốn hiển thị tiêu đề cũng như xếp hạng của tất cả phim cho người dùng. Truy vấn sau đây có thể cho kết quả chúng ta cần:
SELECT title, rating
FROM film
Tuy nhiên, ứng dụng của chúng tôi (hoặc ORM của chúng tôi) thay vào đó thực hiện truy vấn này:
SELECT *
FROM film
Kết quả là chúng ta nhận được gì? Đoán. Chúng tôi nhận được rất nhiều thông tin vô ích:
Ở bên phải, bạn thậm chí có thể thấy một số JSON phức tạp đang được tải:
từ đĩa
để bộ nhớ đệm
bằng dây
để tưởng nhớ khách hàng
và cuối cùng bị vứt đi [vì không cần thiết]
Có, chúng tôi loại bỏ hầu hết thông tin này. Mọi hành động được thực hiện để trích xuất thông tin này hóa ra hoàn toàn vô ích. Có đúng không? Có thật không?
Bắt buộc
Và bây giờ - phần tồi tệ nhất. Mặc dù trình tối ưu hóa hiện có thể thực hiện được nhiều việc nhưng những hành động này là bắt buộc đối với cơ sở dữ liệu. Cơ sở dữ liệu không có cách nào để biết rằng ứng dụng khách không cần 95% dữ liệu này. Và đây chỉ là ví dụ đơn giản nhất. Hãy tưởng tượng kết nối nhiều bảng... Vậy bạn nói sao, nhưng cơ sở dữ liệu có nhanh không? Hãy để tôi khai sáng cho bạn một số điều có thể bạn chưa nghĩ tới. Tất nhiên, thời gian thực hiện của một yêu cầu riêng lẻ không thực sự ảnh hưởng gì cả. Được rồi, nó chạy chậm hơn gấp rưỡi, nhưng chúng ta sẽ vượt qua được, phải không? Cho thuận tiện? Đôi khi điều này là đúng. Nhưng nếu bạn luôn hy sinh hiệu suất để có được sự tiện lợi thì những điều nhỏ nhặt này sẽ bắt đầu tích tụ lại. Chúng ta sẽ không nói về hiệu suất (tốc độ thực hiện các yêu cầu riêng lẻ) nữa mà nói về thông lượng (thời gian phản hồi của hệ thống), và khi đó các vấn đề nghiêm trọng không dễ giải quyết sẽ bắt đầu. Đó là khi bạn mất khả năng mở rộng. Chúng ta hãy xem các kế hoạch thực hiện, trong trường hợp này là Oracle DBMS:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|166K||1| TABLE ACCESS FULL| FILM |1000|166K|--------------------------------------------------
so sánh với:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|20000||1| TABLE ACCESS FULL| FILM |1000|20000|--------------------------------------------------
Chạy truy vấn SELECT * thay vì tiêu đề SELECT, xếp hạng sử dụng bộ nhớ nhiều hơn 8 lần trong cơ sở dữ liệu. Không có gì bất ngờ phải không? Chúng tôi biết điều này sẽ xảy ra. Nhưng chúng tôi vẫn đồng ý với điều này đối với nhiều yêu cầu của chúng tôi, trong đó chúng tôi đơn giản là không cần tất cả dữ liệu này. Chúng tôi tạo ra những công việc không cần thiết nhưng bắt buộc đối với cơ sở dữ liệu , công việc này cứ chồng chất lên nhau. Chúng tôi sử dụng bộ nhớ nhiều hơn 8 lần so với mức cần thiết (tất nhiên hệ số nhân sẽ thay đổi). Trong khi đó, ở tất cả các giai đoạn khác (I/O đĩa, truyền dữ liệu qua mạng, mức tiêu thụ bộ nhớ của máy khách), các vấn đề hoàn toàn giống nhau, nhưng tôi sẽ bỏ qua chúng và thay vào đó xem xét...
Sử dụng chỉ mục
Hầu hết các cơ sở dữ liệu ngày nay đã đánh giá cao khái niệm bao phủ các chỉ mục . Bản thân chỉ mục bao trùm không phải là một loại chỉ mục đặc biệt. Nhưng nó có thể trở thành một "chỉ mục đặc biệt" cho một truy vấn cụ thể, "do vô tình" hoặc do nó được dự định như vậy. Hãy xem xét truy vấn sau:
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
Không có gì bất ngờ về việc thực hiện nó. Đây là một yêu cầu đơn giản. Xem phạm vi theo chỉ mục, truy cập vào bảng - và bạn đã hoàn tất:
-------------------------------------------------------------------|Id|Operation|Name|Rows|-------------------------------------------------------------------|0| SELECT STATEMENT ||8||1| TABLE ACCESS BY INDEX ROWID| ACTOR |8||*2| INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME |8|-------------------------------------------------------------------
Kế hoạch tốt phải không? Chà, nếu chúng ta thực sự cần điều này thì không:
Rõ ràng là chúng ta đang lãng phí bộ nhớ, v.v. Hãy xem truy vấn này như một giải pháp thay thế:
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
Kế hoạch của anh ấy là thế này:
----------------------------------------------------|Id|Operation|Name|Rows|----------------------------------------------------|0| SELECT STATEMENT ||8||*1| INDEX RANGE SCAN| IDX_ACTOR_NAMES |8|----------------------------------------------------
Chúng tôi có thể loại bỏ hoàn toàn quyền truy cập vào bảng, nhờ sự hiện diện của một chỉ mục đáp ứng tất cả các nhu cầu truy vấn của chúng tôi... một chỉ mục bao trùm. Nó quan trọng? Và làm thế nào! Cách tiếp cận này cho phép bạn tăng tốc một số truy vấn theo một bậc độ lớn (hoặc làm chậm chúng xuống một bậc độ lớn khi chỉ mục không còn bao phủ sau một số thay đổi). Các chỉ mục bao phủ không phải lúc nào cũng có thể được sử dụng. Bạn phải trả tiền cho các chỉ mục và bạn không nên thêm quá nhiều chỉ mục. Nhưng trong trường hợp này, mọi thứ đều rõ ràng. Hãy đánh giá hiệu suất:
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER :=100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 1 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Улучшенный request, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 2 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Оптимальный request: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 3 : '||(SYSTIMESTAMP - v_ts));
END;/
Kết quả là chúng tôi nhận được:
Toán tử 1: +000000000 00:00:02.479000000
Toán tử 2: +000000000 00:00:02.261000000
Toán tử 3: +000000000 00:00:01.857000000
Lưu ý rằng bảng tác nhân chỉ có 4 cột, do đó sự khác biệt về hiệu suất giữa câu lệnh 1 và 2 không lớn nhưng vẫn đáng kể. Tôi cũng sẽ lưu ý rằng tôi đã sử dụng gợi ý của trình tối ưu hóa Oracle để trình tối ưu hóa chọn một hoặc một chỉ mục cụ thể khác cho truy vấn. Người điều hành 3 là người chiến thắng không thể tranh cãi trong cuộc đua của chúng ta. Hiệu suất của nó tốt hơn nhiều và chúng ta đang nói về một truy vấn cực kỳ đơn giản. Một lần nữa, khi chúng ta viết SELECT *, chúng ta tạo ra những công việc không cần thiết nhưng bắt buộc đối với cơ sở dữ liệu mà nó không thể tối ưu hóa. Cô ấy sẽ không chọn chỉ mục bao phủ vì nó có chi phí cao hơn một chút so với chỉ mục LAST_NAME mà cô ấy đã chọn và, trong số những thứ khác, chẳng hạn như cô ấy vẫn phải truy cập vào bảng để truy xuất cột LAST_UPDATE vô dụng. Nhưng chúng ta càng phân tích sâu hơn SELECT * thì mọi chuyện càng trở nên tồi tệ hơn. Hãy nói về...
CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
Chế độ xem này chỉ đơn giản thực hiện tất cả các phép nối "...-với-một" giữa bảng khách hàng KHÁCH HÀNG và các bảng khác nhau cho các phần địa chỉ của họ. Cảm ơn bạn, bình thường hóa. Hãy tưởng tượng rằng, sau khi làm việc một chút với chế độ xem này, chúng ta đã quen với nó và quên mất các bảng bên dưới nó. Và bây giờ chúng ta thực hiện truy vấn sau:
SELECT *
FROM v_customer
Kết quả là chúng ta có được một kế hoạch rất ấn tượng:
----------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|----------------------------------------------------------------|0| SELECT STATEMENT ||599|47920|14||*1| HASH JOIN ||599|47920|14||2| TABLE ACCESS FULL | COUNTRY |109|1526|2||*3| HASH JOIN ||599|39534|11||4| TABLE ACCESS FULL | CITY |600|10800|3||*5| HASH JOIN ||599|28752|8||6| TABLE ACCESS FULL| CUSTOMER |599|11381|4||7| TABLE ACCESS FULL| ADDRESS |603|17487|3|----------------------------------------------------------------
Tất nhiên. Cơ sở dữ liệu đang thực hiện tất cả các phép nối và quét toàn bộ bảng vì đó là những gì chúng tôi đã yêu cầu nó làm - tìm nạp tất cả dữ liệu này. Bây giờ, một lần nữa, hãy tưởng tượng rằng tất cả những gì chúng ta thực sự cần là thế này:
Cái gì, nghiêm túc đấy, phải không? Bây giờ bạn đang bắt đầu hiểu những gì tôi đang nói về. Nhưng hãy tưởng tượng rằng chúng ta đã học được điều gì đó từ những sai lầm trong quá khứ và thực hiện truy vấn tối ưu hơn này:
Những thay đổi mạnh mẽ để tốt hơn về mặt thực thi. Các phép nối đã bị loại bỏ vì trình tối ưu hóa giờ đây có thể thấy rằng chúng vô dụng và nếu nó có thể thấy điều đó (và bạn chưa bắt buộc thực hiện công việc đó bằng cách chọn *), thì đơn giản là nó không thể thực hiện tất cả công việc đó. Tại sao lại như vậy trong trường hợp này? Khóa ngoại KHÁCH HÀNG.ADDRESS_ID đối với khóa chính ADDRESS.ADDRESS_ID đảm bảo chính xác một giá trị của khóa sau, có nghĩa là thao tác THAM GIA sẽ là phép nối "... với một" không tăng hoặc giảm số lượng hàng . Và vì chúng tôi không chọn hoặc yêu cầu bất kỳ hàng nào cả nên việc tải chúng chẳng ích gì. Việc xóa THAM GIA có thể sẽ không ảnh hưởng đến kết quả của truy vấn. Cơ sở dữ liệu làm điều này mọi lúc. Bạn có thể chạy truy vấn sau trên hầu hết mọi cơ sở dữ liệu:
--Oracle
SELECT CASE WHEN EXISTS (
SELECT 1/0FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (SELECT 1/0)
Trong trường hợp này, bạn có thể mong đợi một ngoại lệ số học được đưa ra, như khi thực hiện truy vấn sau:
SELECT 1/0 FROM dual
Đã xảy ra:
ORA-01476: số chia bằng 0
Nhưng điều này không xảy ra. Trình tối ưu hóa (hoặc thậm chí trình phân tích cú pháp) có thể đảm bảo rằng không có phần tử danh sách chọn nào trong vị từ EXISTS (SELECT ..) sẽ thay đổi kết quả của truy vấn, do đó không cần phải thực thi nó. Như thế này!
Trong khi đó...
Một trong những vấn đề khó chịu nhất với ORM là chúng rất dễ viết các truy vấn SELECT *. Trên thực tế, ví dụ, trong HQL/JPQL chúng thường được sử dụng theo mặc định. Chúng ta có thể bỏ qua mệnh đề SELECT hoàn toàn vì chúng ta sẽ truy xuất toàn bộ thực thể, phải không? Ví dụ:
FROM v_customer
Ví dụ: Vlad Mihalcea, một chuyên gia và người ủng hộ việc phát triển với Hibernate , khuyên bạn hầu như luôn luôn sử dụng các truy vấn [đủ điều kiện] khi bạn chắc chắn rằng mình không muốn lưu bất kỳ thay đổi nào sau khi thanh toán. ORM hỗ trợ rất nhiều cho việc giải quyết vấn đề tồn tại của đồ thị đối tượng. Lưu ý: Kiên trì. Các nhiệm vụ sửa đổi biểu đồ đối tượng thực sự và lưu các thay đổi được liên kết chặt chẽ với nhau. Nhưng nếu bạn không định làm điều đó thì tại sao phải bận tâm đến việc chiết xuất tinh chất? Tại sao không viết một yêu cầu [tinh tế]? Hãy nói rõ: từ quan điểm hiệu suất, việc viết một truy vấn được thiết kế riêng cho trường hợp sử dụng cụ thể của bạn rõ ràng là tốt hơn bất kỳ tùy chọn nào khác. Bạn có thể không quan tâm vì tập dữ liệu của bạn nhỏ và không thành vấn đề. Tuyệt vời. Nhưng cuối cùng, khi bạn cần khả năng mở rộng, việc thiết kế lại ứng dụng của bạn để sử dụng truy vấn thay vì bắt buộc phải duyệt biểu đồ thực thể sẽ khá khó khăn. Và bạn sẽ có việc gì đó để làm nếu không có nó.
Đếm hàng để tìm hiểu xem có thứ gì không
Một trong những sự lãng phí tài nguyên tồi tệ nhất là chạy các truy vấn COUNT(*) chỉ để xem có thứ gì đó trong cơ sở dữ liệu hay không. Ví dụ: chúng ta cần tìm hiểu xem một người dùng cụ thể có đơn đặt hàng nào không. Và chúng tôi thực hiện yêu cầu:
SELECTcount(*)
FROM orders
WHERE user_id =:user_id
Tiểu học. Nếu COUNT = 0 thì không có đơn hàng nào. Nếu không thì có. Hiệu suất sẽ không tệ đến thế vì chúng ta có thể có chỉ mục trên cột ORDERS.USER_ID. Nhưng bạn nghĩ hiệu năng của truy vấn trên sẽ được so sánh với tùy chọn sau như thế nào:
--Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
)
Không cần phải là một nhà khoa học tên lửa mới phát hiện ra rằng một vị từ tồn tại thực sự sẽ ngừng tìm kiếm các chuỗi bổ sung ngay khi nó tìm thấy chuỗi đầu tiên . Vì vậy, nếu kết quả là “không có đơn hàng” thì tốc độ sẽ tương đương. Tuy nhiên, nếu kết quả là “có, có đơn hàng”, thì trong trường hợp không cần đếm chính xác số lượng, câu trả lời sẽ nhận được nhanh hơn nhiều . Rốt cuộc, chúng tôi không quan tâm đến con số chính xác. Tuy nhiên, chúng tôi đã yêu cầu cơ sở dữ liệu tính toán nó ( công việc không cần thiết ) và cơ sở dữ liệu không biết rằng chúng tôi đang bỏ qua tất cả các kết quả lớn hơn 1 ( công việc bắt buộc ). Tất nhiên, sẽ tệ hơn nhiều nếu chúng ta gọi list.size() trên bộ sưu tập được hỗ trợ bởi JPA để đạt được kết quả tương tự. Tôi đã viết về điều này trên blog của mình trước đó và đã tiến hành thử nghiệm so sánh cả hai tùy chọn trên...
Phần kết luận
Bài viết này nêu rõ ràng. Đừng ép cơ sở dữ liệu làm những công việc không cần thiết nhưng bắt buộc . Điều đó là không cần thiết bởi vì, với những yêu cầu đưa ra, bạn biết rằng một số công việc cụ thể không cần phải thực hiện. Tuy nhiên, bạn yêu cầu cơ sở dữ liệu thực hiện việc đó. Nó là cần thiết vì không có cách nào để cơ sở dữ liệu đảm bảo rằng công việc này là không cần thiết . Thông tin này chỉ có sẵn cho máy khách và không có sẵn cho máy chủ. Vì vậy cơ sở dữ liệu phải thực thi nó. Bài viết tập trung vào CHỌN *, chủ yếu vì đây là một đối tượng thuận tiện để xem xét. Nhưng điều này không chỉ áp dụng cho cơ sở dữ liệu. Điều này áp dụng cho tất cả các thuật toán phân tán trong đó máy khách yêu cầu máy chủ thực hiện những công việc không cần thiết nhưng bắt buộc . Có bao nhiêu tác vụ N+1 trong ứng dụng AngularJS trung bình của bạn trong đó giao diện người dùng lặp qua kết quả của dịch vụ A, gọi dịch vụ B nhiều lần, thay vì gói tất cả các lệnh gọi đến B vào một cuộc gọi duy nhất? Đây là một hiện tượng rất phổ biến. Giải pháp luôn giống nhau. Bạn càng cung cấp nhiều thông tin cho thực thể thực thi các lệnh của bạn thì thực thể đó (về mặt lý thuyết) thực thi các lệnh đó càng nhanh. Viết truy vấn tối ưu. Luôn luôn. Toàn bộ hệ thống của bạn sẽ cảm ơn bạn vì điều này. Bài báo gốc
GO TO FULL VERSION