🥇一次SQL調查的故事

去年 XNUMX 月，我收到了來自 VWO 支援團隊的有趣的錯誤報告。大型企業客戶的一份分析報告的載入時間似乎令人望而卻步。而且由於這是我的職責範圍，所以我立即集中精力解決問題。

底

為了清楚說明我在說什麼，我將向您介紹一些關於 VWO 的資訊。透過這個平台，您可以在您的網站上發起各種有針對性的活動：進行 A/B 實驗、追蹤訪客和轉換、分析銷售漏斗、顯示熱圖並播放訪問記錄。

但該平台最重要的是報告。所有上述功能都是相互關聯的。對於企業客戶來說，如果沒有以分析形式呈現的強大平台，大量資訊將毫無用處。

使用該平台，您可以對大型資料集進行隨機查詢。這是一個簡單的例子：

顯示從<日期d1>到<日期d2>期間，使用Chrome瀏覽器或（位於歐洲且）在「abc.com」頁面上的所有點擊記錄。 iPhone)

注意布林運算符。客戶端可以在查詢介面中使用它們進行任意複雜的查詢來取得樣本。

請求緩慢

有問題的客戶正在嘗試做一些直觀上應該快速工作的事情：

顯示訪問 URL 包含「/jobs」的任何頁面的使用者的所有會話記錄

該網站擁有大量流量，我們為此儲存了超過一百萬個唯一 URL。他們希望找到一個與其業務模型相關的相當簡單的 URL 範本。

初步調查

讓我們看一下資料庫中發生了什麼。以下是原始的慢 SQL 查詢：

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 ;

以下是時間安排：

規劃時間：1.480 ms 執行時間：1431924.650 ms

該查詢爬取了 150 萬行。查詢規劃器顯示了一些有趣的細節，但沒有明顯的瓶頸。

讓我們進一步研究該請求。正如你所看到的，他確實 JOIN 三個表：

會議：顯示會話資訊：瀏覽器、使用者代理程式、國家等。
錄音數據：記錄的URL、頁面、訪問時長
網址：為了避免重複非常大的 URL，我們將它們儲存在單獨的表格中。

另請注意，我們所有的表都已按以下方式分區 account_id。這樣就排除了一個特別大的帳戶給其他帳戶帶來問題的情況。

尋找線索

經過仔細檢查，我們發現特定請求有問題。值得仔細看看這一行：

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

第一個想法是，也許是因為 ILIKE 在所有這些長 URL 上（我們有超過 1,4 萬個 獨特的 為此帳戶收集的 URL）效能可能會受到影響。

但不，這不是重點！

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

Time: 5231.765 ms

模板搜尋請求本身只需要 5 秒。在一百萬個唯一 URL 中搜尋模式顯然不是問題。

名單上的下一個嫌疑犯是幾位 JOIN。也許它們的過度使用導致了速度放緩？通常 JOIN是最明顯的性能問題候選人，但我不認為我們的案例是典型的。

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

這也不是我們的情況。 JOIN事實證明速度相當快。

縮小嫌疑範圍

我準備開始更改查詢以實現任何可能的效能改進。我和我的團隊提出了兩個主要想法：

使用 EXISTS 作為子查詢 URL：我們想再次檢查 URL 的子查詢是否有任何問題。實現此目的的一種方法是簡單地使用 EXISTS. EXISTS 可以大大提高了效能，因為一旦找到唯一與條件相符的字串，它就會立即結束。

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

嗯，是。包裹在子查詢中時 EXISTS，讓一切變得超級快。下一個邏輯問題是為什麼請求 JOIN-ami 和子查詢本身單獨運行很快，但一起運行卻非常慢？

將子查詢移至 CTE ：如果查詢本身很快，我們可以簡單地先計算快速結果，然後提供給主查詢

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;

但還是很慢。

尋找罪魁禍首

一直以來，有一件小事在我眼前閃過，但我不斷地把它拋到一邊。但既然已經沒有別的事了，我決定也要去看看她。我說的是 && 操作員。再見 EXISTS 剛剛提高了性能 && 是所有版本的慢查詢中唯一剩下的共同因素。

看著文件，我們看到 && 當您需要尋找兩個陣列之間的公共元素時使用。

在原始請求中，這是：

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

這意味著我們對 URL 進行模式搜索，然後找到具有常見帖子的所有 URL 的交集。這有點令人困惑，因為這裡的「urls」並不是指包含所有 URL 的表，而是指表中的「urls」列 recording_data.

隨著越來越多的懷疑 &&，我試圖在生成的查詢計劃中找到它們的確認 EXPLAIN ANALYZE （我已經保存了一個計劃，但我通常更喜歡在 SQL 中進行實驗，而不是嘗試了解查詢計劃程序的不透明性）。

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

有幾行過濾器僅來自 &&。這意味著這個手術不僅成本高昂，而且還要進行多次。

我通過隔離條件對此進行了測試

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[]

這個查詢很慢。因為 JOIN-s 很快，子查詢也很快，唯一剩下的就是 && 操作員。

這只是一個關鍵操作。我們總是需要搜尋整個底層 URL 表格來搜尋模式，並且總是需要找到交集。我們不能直接透過URL記錄來搜索，因為這些只是指涉的ID urls.

在尋求解決方案的路上

&& 慢是因為兩組都很大。如果更換的話操作會比較快 urls 上 { "http://google.com/", "http://wingify.com/" }.

我開始尋找一種在 Postgres 中設定交集而不使用的方法 &&，但沒有太大的成功。

最後，我們決定單獨解決問題：給我一切 urls URL 與模式相符的行。如果沒有附加條件，它將是 -

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%'

而不是 JOIN 語法我只是使用了子查詢並擴展了 recording_data.urls 數組，以便您可以直接套用條件 WHERE.

這裡最重要的是 && 用於檢查給定條目是否包含符合的 URL。如果你稍微瞇起眼睛，你可以看到這個操作遍歷數組的元素（或表格的行），並在滿足條件（匹配）時停止。沒有提醒你什麼嗎？是的， EXISTS.

從此 recording_data.urls 可以從子查詢上下文外部引用，當發生這種情況時，我們可以依靠我們的老朋友 EXISTS 並用它包裝子查詢。

將所有內容放在一起，我們得到最終的最佳化查詢：

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%'
    );

以及最終交貨時間 Time: 1898.717 ms 是時候慶祝一下了？！？

沒那麼快！首先您需要檢查正確性。我非常懷疑 EXISTS 最佳化，因為它改變了提前終止的邏輯。我們需要確保沒有向請求添加不明顯的錯誤。

一個簡單的測試是運行 count(*) 對大量不同資料集的慢速和快速查詢。然後，對於一小部分數據，我手動驗證所有結果是否正確。

所有測試均給出一致的陽性結果。我們修好了一切！

得到教訓

從這個故事中我們可以得到很多教訓：

查詢計劃並不能說明全部情況，但它們可以提供線索
主要嫌疑人並不總是真正的罪魁禍首
可以分解慢查詢以隔離瓶頸
並非所有優化本質上都是還原性的
使用 EXIST在可能的情況下，可以顯著提高生產力

產量

我們的查詢時間從約 24 分鐘縮短到 2 秒——效能顯著提升！雖然這篇文章寫得很大，但我們所做的所有實驗都是在一天內完成的，估計優化和測試需要 1,5 到 2 個小時。

如果您不害怕 SQL，而是嘗試學習和使用它，那麼 SQL 是一門很棒的語言。透過充分了解 SQL 查詢的執行方式、資料庫如何產生查詢計劃、索引如何運作以及正在處理的資料大小，您可以非常成功地最佳化查詢。但同樣重要的是，繼續嘗試不同的方法，慢慢分解問題，找出瓶頸。

實現這樣的結果的最佳部分是顯著、可見的速度改進 - 以前甚至無法加載的報告現在幾乎可以立即加載。

特別感謝 我的戰友們 在阿迪亞·米甚拉的指揮下, 阿迪亞·高魯 и 瓦倫·馬爾霍特拉 用於腦力激盪和 丁卡·潘迪爾 在我們最終告別它之前，在我們的最終請求中發現了一個重要錯誤！

來源： www.habr.com

一項 SQL 調查的故事

底