เรื่องราวของการสืบสวน SQL ครั้งหนึ่ง

เมื่อเดือนธันวาคมปีที่แล้ว ฉันได้รับรายงานข้อผิดพลาดที่น่าสนใจจากทีมสนับสนุน VWO เวลาในการโหลดรายงานการวิเคราะห์รายการใดรายการหนึ่งสำหรับลูกค้าองค์กรขนาดใหญ่ดูเหมือนจะห้ามปราม และเนื่องจากนี่คือพื้นที่รับผิดชอบของฉัน ฉันจึงมุ่งเน้นไปที่การแก้ปัญหาทันที

ประวัติศาสตร์

เพื่อให้ชัดเจนว่าฉันกำลังพูดถึงอะไร ฉันจะบอกคุณเล็กน้อยเกี่ยวกับ VWO นี่คือแพลตฟอร์มที่คุณสามารถเปิดตัวแคมเปญที่กำหนดเป้าหมายได้หลากหลายบนเว็บไซต์ของคุณ: ทำการทดสอบ A/B ติดตามผู้เยี่ยมชมและคอนเวอร์ชั่น วิเคราะห์ช่องทางการขาย แสดงแผนที่ความร้อน และเล่นบันทึกการเยี่ยมชม

แต่สิ่งที่สำคัญที่สุดเกี่ยวกับแพลตฟอร์มก็คือการรายงาน ฟังก์ชั่นข้างต้นทั้งหมดเชื่อมโยงถึงกัน และสำหรับลูกค้าองค์กร ข้อมูลจำนวนมหาศาลคงจะไร้ประโยชน์หากไม่มีแพลตฟอร์มที่มีประสิทธิภาพซึ่งนำเสนอในรูปแบบการวิเคราะห์

เมื่อใช้แพลตฟอร์ม คุณสามารถสร้างแบบสอบถามแบบสุ่มบนชุดข้อมูลขนาดใหญ่ได้ นี่เป็นตัวอย่างง่ายๆ:

แสดงการคลิกทั้งหมดบนหน้า "abc.com" จาก <date d1> ถึง <date d2> สำหรับผู้ที่ใช้ Chrome OR (อยู่ในยุโรปและใช้ 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 สามโต๊ะ:

1. ครั้ง ราคา: เพื่อแสดงข้อมูลเซสชัน: เบราว์เซอร์ ตัวแทนผู้ใช้ ประเทศ และอื่นๆ
2. การบันทึก_ข้อมูล: URL ที่บันทึกไว้, เพจ, ระยะเวลาการเข้าชม
3. 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ปรากฏว่าค่อนข้างเร็ว

การจำกัดวงผู้ต้องสงสัยให้แคบลง

ฉันพร้อมที่จะเริ่มเปลี่ยนแปลงแบบสอบถามเพื่อให้บรรลุการปรับปรุงประสิทธิภาพที่เป็นไปได้ ฉันและทีมงานได้พัฒนาแนวคิดหลัก 2 ประการ:

• ใช้ 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 ทั้งหมด แต่หมายถึงคอลัมน์ "url" ในตาราง 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 ได้โดยตรง เนื่องจากสิ่งเหล่านี้เป็นเพียงรหัสที่อ้างอิงถึง 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 array เพื่อให้คุณสามารถใช้เงื่อนไขได้โดยตรง 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(*) ในการสืบค้นทั้งแบบช้าและเร็วสำหรับชุดข้อมูลที่แตกต่างกันจำนวนมาก จากนั้น สำหรับข้อมูลส่วนเล็กๆ ฉันตรวจสอบด้วยตนเองว่าผลลัพธ์ทั้งหมดถูกต้อง

การทดสอบทั้งหมดให้ผลลัพธ์ที่เป็นบวกอย่างสม่ำเสมอ เราแก้ไขทุกอย่างแล้ว!

บทเรียนที่ได้รับ

มีบทเรียนมากมายที่ต้องเรียนรู้จากเรื่องราวนี้:

1. แผนการสืบค้นไม่ได้บอกเรื่องราวทั้งหมด แต่สามารถให้เบาะแสได้
2. ผู้ต้องสงสัยหลักไม่ใช่ผู้กระทำผิดที่แท้จริงเสมอไป
3. การสืบค้นที่ช้าสามารถแยกย่อยเพื่อแยกปัญหาคอขวดได้
4. การเพิ่มประสิทธิภาพบางอย่างไม่ได้ลดทอนลงแต่อย่างใด
5. ใช้ EXISTหากเป็นไปได้ สามารถนำไปสู่ประสิทธิภาพการผลิตที่เพิ่มขึ้นอย่างมาก

เอาท์พุต

เราเปลี่ยนจากเวลาสืบค้นประมาณ 24 นาทีเหลือ 2 วินาที ซึ่งเพิ่มประสิทธิภาพได้ค่อนข้างมาก! แม้ว่าบทความนี้จะออกมายิ่งใหญ่ แต่การทดลองทั้งหมดที่เราทำเกิดขึ้นในหนึ่งวัน และคาดว่าจะใช้เวลาระหว่าง 1,5 ถึง 2 ชั่วโมงในการเพิ่มประสิทธิภาพและการทดสอบ

SQL เป็นภาษาที่ยอดเยี่ยมถ้าคุณไม่กลัวมัน แต่พยายามเรียนรู้และใช้งานมัน การมีความเข้าใจที่ดีเกี่ยวกับวิธีการดำเนินการสืบค้น SQL วิธีที่ฐานข้อมูลสร้างแผนการสืบค้น วิธีการทำงานของดัชนี และขนาดของข้อมูลที่คุณกำลังจัดการ คุณจะสามารถเพิ่มประสิทธิภาพการสืบค้นได้สำเร็จ อย่างไรก็ตาม การลองใช้แนวทางต่างๆ ต่อไปและค่อยๆ แยกแยะปัญหาโดยค้นหาจุดคอขวดก็มีความสำคัญไม่แพ้กัน

ส่วนที่ดีที่สุดเกี่ยวกับการบรรลุผลลัพธ์เช่นนี้คือการปรับปรุงความเร็วที่เห็นได้ชัดเจน โดยที่รายงานที่ก่อนหน้านี้ไม่สามารถโหลดได้ในขณะนี้จะโหลดได้เกือบจะในทันที

ขอขอบคุณเป็นพิเศษสำหรับ สหายของฉัน ตามคำสั่งของอาทิตยา มิชรา, อาทิตยา เการู и วรุณ มัลโหตรา เพื่อการระดมความคิดและ ดินการ์ ปานดีร์ สำหรับการค้นหาข้อผิดพลาดที่สำคัญในคำขอสุดท้ายของเรา ก่อนที่เราจะกล่าวคำอำลาในที่สุด!

ที่มา: will.com