🥇Câu chuyện về một cuộc điều tra SQL

Tháng XNUMX năm ngoái tôi đã nhận được một báo cáo lỗi thú vị từ nhóm hỗ trợ VWO. Thời gian tải một trong các báo cáo phân tích dành cho khách hàng doanh nghiệp lớn dường như quá cao. Và vì đây là lĩnh vực tôi chịu trách nhiệm nên tôi ngay lập tức tập trung vào việc giải quyết vấn đề.

thời tiền sử

Để làm rõ điều tôi đang nói, tôi sẽ kể cho bạn nghe một chút về VWO. Đây là nền tảng mà bạn có thể khởi chạy các chiến dịch được nhắm mục tiêu khác nhau trên trang web của mình: tiến hành thử nghiệm A/B, theo dõi khách truy cập và chuyển đổi, phân tích kênh bán hàng, hiển thị bản đồ nhiệt và phát bản ghi lượt truy cập.

Nhưng điều quan trọng nhất về nền tảng này là báo cáo. Tất cả các chức năng trên đều được kết nối với nhau. Và đối với các khách hàng doanh nghiệp, một lượng thông tin khổng lồ sẽ trở nên vô dụng nếu không có nền tảng mạnh mẽ trình bày thông tin đó dưới dạng phân tích.

Sử dụng nền tảng này, bạn có thể thực hiện truy vấn ngẫu nhiên trên một tập dữ liệu lớn. Đây là một ví dụ đơn giản:

Hiển thị tất cả các nhấp chuột trên trang "abc.com" TỪ <date d1> ĐẾN <date d2> đối với những người đã sử dụng Chrome HOẶC (nằm ở Châu Âu VÀ đã sử dụng iPhone)

Hãy chú ý đến các toán tử Boolean. Chúng có sẵn cho khách hàng trong giao diện truy vấn để thực hiện các truy vấn phức tạp tùy ý nhằm lấy mẫu.

Yêu cầu chậm

Khách hàng được đề cập đang cố gắng làm điều gì đó có hiệu quả nhanh chóng bằng trực giác:

Hiển thị tất cả bản ghi phiên cho người dùng đã truy cập bất kỳ trang nào có URL chứa "/jobs"

Trang web này có rất nhiều lưu lượng truy cập và chúng tôi đã lưu trữ hơn một triệu URL duy nhất chỉ dành cho nó. Và họ muốn tìm một mẫu URL khá đơn giản có liên quan đến mô hình kinh doanh của họ.

Sự điêu tra sơ bộ

Chúng ta hãy xem những gì đang xảy ra trong cơ sở dữ liệu. Dưới đây là truy vấn SQL chậm ban đầu:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

Và đây là thời gian:

Thời gian dự kiến: 1.480 ms Thời gian thực hiện: 1431924.650 ms

Truy vấn đã thu thập được 150 nghìn hàng. Trình lập kế hoạch truy vấn hiển thị một số chi tiết thú vị nhưng không có điểm nghẽn nào rõ ràng.

Hãy nghiên cứu yêu cầu thêm. Như bạn có thể thấy, anh ấy làm JOIN ba bảng:

phiên: để hiển thị thông tin phiên: trình duyệt, tác nhân người dùng, quốc gia, v.v.
ghi_dữ liệu: URL được ghi lại, trang, thời lượng truy cập
url: Để tránh trùng lặp các URL cực lớn, chúng tôi lưu trữ chúng trong một bảng riêng.

Cũng lưu ý rằng tất cả các bảng của chúng tôi đã được phân vùng bởi account_id. Bằng cách này, tình huống một tài khoản đặc biệt lớn gây ra vấn đề cho người khác sẽ bị loại trừ.

Tìm kiếm manh mối

Khi kiểm tra kỹ hơn, chúng tôi thấy rằng có điều gì đó không ổn với một yêu cầu cụ thể. Thật đáng để xem xét kỹ hơn dòng này:

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

Ý nghĩ đầu tiên là có lẽ vì ILIKE trên tất cả các URL dài này (chúng tôi có hơn 1,4 triệu độc đáo Hiệu suất của các URL được thu thập cho tài khoản này) có thể bị ảnh hưởng.

Nhưng không, đó không phải là vấn đề!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

Bản thân yêu cầu tìm kiếm mẫu chỉ mất 5 giây. Việc tìm kiếm một mẫu trong một triệu URL duy nhất rõ ràng không phải là vấn đề.

Nghi phạm tiếp theo trong danh sách là một số người JOIN. Có lẽ việc sử dụng quá mức của họ đã gây ra sự chậm lại? Thường xuyên JOINlà những ứng cử viên rõ ràng nhất cho các vấn đề về hiệu suất, nhưng tôi không tin rằng trường hợp của chúng tôi là điển hình.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

Và đây cũng không phải là trường hợp của chúng tôi. JOINhóa ra là khá nhanh.

Thu hẹp phạm vi nghi phạm

Tôi đã sẵn sàng bắt đầu thay đổi truy vấn để đạt được bất kỳ cải tiến hiệu suất nào có thể có. Nhóm của tôi và tôi đã phát triển 2 ý tưởng chính:

Sử dụng EXISTS cho URL truy vấn phụ: Chúng tôi muốn kiểm tra lại xem có bất kỳ vấn đề nào với truy vấn phụ cho các URL hay không. Một cách để đạt được điều này là chỉ cần sử dụng EXISTS. EXISTS có thể cải thiện đáng kể hiệu suất vì nó kết thúc ngay lập tức ngay khi tìm thấy chuỗi duy nhất phù hợp với điều kiện.

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

Vâng, vâng. Truy vấn con khi được gói trong EXISTS, khiến mọi thứ trở nên siêu nhanh. Câu hỏi hợp lý tiếp theo là tại sao yêu cầu với JOIN-ami và truy vấn con riêng lẻ thì nhanh nhưng lại chậm khủng khiếp khi kết hợp với nhau?

Di chuyển truy vấn con sang CTE : Nếu truy vấn nhanh, chúng ta có thể chỉ cần tính kết quả nhanh trước rồi cung cấp cho truy vấn chính

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

Nhưng nó vẫn rất chậm.

Tìm ra thủ phạm

Suốt thời gian qua, một điều nhỏ bé lóe lên trước mắt tôi mà tôi liên tục gạt sang một bên. Nhưng vì không còn gì khác nên tôi quyết định cũng nhìn cô ấy. Tôi đang nói về && nhà điều hành. Tạm biệt EXISTS vừa cải thiện hiệu suất && là yếu tố chung duy nhất còn lại trên tất cả các phiên bản của truy vấn chậm.

Nhìn tài liệu, chúng ta thấy rằng && được sử dụng khi bạn cần tìm các phần tử chung giữa hai mảng.

Trong yêu cầu ban đầu đây là:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

Điều đó có nghĩa là chúng tôi thực hiện tìm kiếm mẫu trên các URL của mình, sau đó tìm phần giao nhau với tất cả các URL có các bài đăng phổ biến. Điều này hơi khó hiểu vì "url" ở đây không đề cập đến bảng chứa tất cả các URL mà đề cập đến cột "url" trong bảng recording_data.

Với những nghi ngờ ngày càng tăng liên quan đến &&, tôi đã cố gắng tìm xác nhận cho chúng trong kế hoạch truy vấn được tạo EXPLAIN ANALYZE (Tôi đã lưu một kế hoạch, nhưng tôi thường cảm thấy thoải mái hơn khi thử nghiệm SQL hơn là cố gắng tìm hiểu mức độ mờ đục của các trình lập kế hoạch truy vấn).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

Có một số dòng bộ lọc chỉ từ &&. Điều đó có nghĩa là hoạt động này không chỉ tốn kém mà còn phải thực hiện nhiều lần.

Tôi đã thử nghiệm điều này bằng cách cô lập điều kiện

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

Truy vấn này chậm. Bởi vì JOIN-s rất nhanh và truy vấn phụ cũng nhanh, điều duy nhất còn lại là && nhà điều hành.

Đây chỉ là một hoạt động quan trọng. Chúng tôi luôn cần tìm kiếm toàn bộ bảng URL cơ bản để tìm kiếm một mẫu và chúng tôi luôn cần tìm các điểm giao nhau. Chúng tôi không thể tìm kiếm trực tiếp theo bản ghi URL vì đây chỉ là ID đề cập đến urls.

Trên đường đến một giải pháp

&& chậm vì cả hai bộ đều rất lớn. Thao tác sẽ tương đối nhanh nếu tôi thay thế urls trên { "http://google.com/", "http://wingify.com/" }.

Tôi bắt đầu tìm cách thiết lập giao lộ trong Postgres mà không cần sử dụng &&, nhưng không có nhiều thành công.

Cuối cùng, chúng tôi quyết định chỉ giải quyết vấn đề một cách riêng lẻ: đưa cho tôi mọi thứ urls các dòng mà URL khớp với mẫu. Nếu không có điều kiện bổ sung, nó sẽ là -

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

Thay vì JOIN cú pháp tôi vừa sử dụng truy vấn con và mở rộng recording_data.urls mảng để bạn có thể áp dụng trực tiếp điều kiện trong WHERE.

Điều quan trọng nhất ở đây là && được sử dụng để kiểm tra xem một mục nhất định có chứa URL phù hợp hay không. Nếu để ý một chút, bạn có thể thấy thao tác này di chuyển qua các phần tử của một mảng (hoặc các hàng của bảng) và dừng khi đáp ứng một điều kiện (khớp). Không nhắc nhở bạn về bất cứ điều gì? Vâng, EXISTS.

Kể từ ngày recording_data.urls có thể được tham chiếu từ bên ngoài ngữ cảnh truy vấn phụ, khi điều này xảy ra, chúng ta có thể quay lại với người bạn cũ của mình EXISTS và bọc truy vấn phụ với nó.

Đặt mọi thứ lại với nhau, chúng tôi nhận được truy vấn được tối ưu hóa cuối cùng:

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

Và thời gian dẫn cuối cùng Time: 1898.717 ms Đã đến lúc ăn mừng?!?

Không quá nhanh! Đầu tiên bạn cần kiểm tra tính chính xác. Tôi đã vô cùng nghi ngờ về EXISTS tối ưu hóa vì nó thay đổi logic để hoàn thành sớm hơn. Chúng tôi cần chắc chắn rằng chúng tôi chưa thêm lỗi không rõ ràng vào yêu cầu.

Một thử nghiệm đơn giản là chạy count(*) trên cả truy vấn chậm và nhanh cho một số lượng lớn các tập dữ liệu khác nhau. Sau đó, đối với một tập hợp con nhỏ dữ liệu, tôi đã xác minh thủ công rằng tất cả các kết quả đều chính xác.

Tất cả các xét nghiệm đều cho kết quả tích cực. Chúng tôi đã sửa mọi thứ!

Bài học kinh nghiệm

Có rất nhiều bài học được rút ra từ câu chuyện này:

Kế hoạch truy vấn không kể toàn bộ câu chuyện, nhưng chúng có thể cung cấp manh mối
Nghi phạm chính không phải lúc nào cũng là thủ phạm thực sự
Các truy vấn chậm có thể được chia nhỏ để cô lập các nút thắt cổ chai
Không phải tất cả các tối ưu hóa đều có tính chất rút gọn
Sử dụng EXIST, nếu có thể, có thể dẫn đến tăng năng suất đáng kể

Đầu ra

Chúng tôi đã tăng thời gian truy vấn từ ~24 phút lên 2 giây - hiệu suất tăng khá đáng kể! Mặc dù bài viết này rất hay nhưng tất cả các thử nghiệm chúng tôi thực hiện đều diễn ra trong một ngày và ước tính mất từ 1,5 đến 2 giờ để tối ưu hóa và thử nghiệm.

SQL là một ngôn ngữ tuyệt vời nếu bạn không sợ nó mà hãy cố gắng học và sử dụng nó. Bằng cách hiểu rõ về cách thực thi các truy vấn SQL, cách cơ sở dữ liệu tạo ra các kế hoạch truy vấn, cách hoạt động của các chỉ mục và đơn giản là kích thước của dữ liệu bạn đang xử lý, bạn có thể rất thành công trong việc tối ưu hóa các truy vấn. Tuy nhiên, điều quan trọng không kém là tiếp tục thử các cách tiếp cận khác nhau và từ từ giải quyết vấn đề, tìm ra những điểm nghẽn.

Phần tốt nhất để đạt được những kết quả này là sự cải thiện tốc độ rõ ràng, đáng chú ý - trong đó một báo cáo trước đây thậm chí không tải được bây giờ sẽ tải gần như ngay lập tức.

Trân trọng cảm ơn đồng chí của tôi theo lệnh của Aditya Mishra, Gauru Aditya и Varun Malhotra để động não và Dinkar Pandir vì đã tìm ra một lỗi quan trọng trong yêu cầu cuối cùng của chúng tôi trước khi chúng tôi nói lời tạm biệt với nó!

Nguồn: www.habr.com

Câu chuyện về một cuộc điều tra SQL