โปรโฮสต์ > บล็อก > การบริหาร > PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"

ผู้จัดการหลายพันคนจากสำนักงานขายทั่วประเทศเป็นประวัติการณ์ ระบบ CRM ของเรา ผู้ติดต่อนับหมื่นรายต่อวัน — ข้อเท็จจริงของการสื่อสารกับลูกค้าที่มีศักยภาพหรือลูกค้าปัจจุบัน และสำหรับสิ่งนี้ คุณต้องค้นหาลูกค้าก่อน และควรรวดเร็วมาก และสิ่งนี้มักเกิดขึ้นตามชื่อ

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

นอกจากนี้ การสอบสวนเพิ่มเติมยังพบตัวอย่างที่น่าสนใจอีกด้วย การเพิ่มประสิทธิภาพขั้นแรก จากนั้นจึงลดประสิทธิภาพลง ร้องขอด้วยการปรับแต่งตามลำดับโดยหลายทีมซึ่งแต่ละทีมทำหน้าที่ด้วยความตั้งใจที่ดีที่สุดเท่านั้น

0: ผู้ใช้ต้องการอะไร?

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"[เคดีพีวี ด้วยเหตุนี้]

ผู้ใช้มักจะหมายถึงอะไรเมื่อพูดถึงการค้นหาชื่อแบบ "รวดเร็ว" แทบจะไม่เคยกลายเป็นการค้นหาสตริงย่อยที่ "ซื่อสัตย์" เลย ... LIKE '%роза%' - เพราะแล้วผลที่ได้ก็ไม่ใช่แค่เท่านั้น 'Розалия' и 'Магазин Роза'แต่ 'Гроза' และแม้กระทั่ง 'Дом Деда Мороза'.

ผู้ใช้ถือว่าในระดับรายวันที่คุณจะมอบให้เขา ค้นหาตามจุดเริ่มต้นของคำ ในชื่อและทำให้มีความเกี่ยวข้องมากขึ้น เริ่มต้นด้วย เข้ามา และคุณจะทำมัน เกือบจะในทันที - สำหรับการป้อนข้อมูลแบบอินเตอร์ลิเนียร์

1: จำกัดงาน

และยิ่งไปกว่านั้นบุคคลจะไม่เข้าไปโดยเฉพาะ 'роз магаз'เพื่อให้คุณต้องค้นหาแต่ละคำตามคำนำหน้า ไม่ ผู้ใช้จะตอบสนองต่อคำใบ้สั้นๆ สำหรับคำสุดท้ายได้ง่ายกว่าการตั้งใจ "ระบุน้อยเกินไป" คำก่อนหน้า - ดูว่าเครื่องมือค้นหาต่างๆ จัดการเรื่องนี้อย่างไร

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

นักพัฒนานามธรรมทำอะไร?

1.0: เครื่องมือค้นหาภายนอก

โอ้การค้นหายากฉันไม่อยากทำอะไรเลย - ปล่อยให้มันกับ devops กันเถอะ! ให้พวกเขาปรับใช้เครื่องมือค้นหาภายนอกฐานข้อมูล: Sphinx, ElasticSearch,...

ตัวเลือกการทำงานแม้ว่าจะต้องใช้แรงงานมากในแง่ของการซิงโครไนซ์และความเร็วของการเปลี่ยนแปลง แต่ไม่ใช่ในกรณีของเรา เนื่องจากการค้นหาจะดำเนินการสำหรับลูกค้าแต่ละรายภายในกรอบข้อมูลบัญชีของเขาเท่านั้น และข้อมูลมีความแปรปรวนค่อนข้างสูง - และหากผู้จัดการได้ป้อนการ์ดแล้ว 'Магазин Роза'จากนั้นหลังจากผ่านไป 5-10 วินาที เขาอาจจำได้ว่าเขาลืมระบุอีเมลของเขาที่นั่นและต้องการค้นหาและแก้ไขให้ถูกต้อง

ดังนั้น - มาเลย ค้นหา "โดยตรงในฐานข้อมูล". โชคดีที่ PostgreSQL ช่วยให้เราทำเช่นนี้ได้ ไม่ใช่แค่ตัวเลือกเดียว เราจะมาดูกัน

1.1: สตริงย่อย "ซื่อสัตย์"

เรายึดติดกับคำว่า "สตริงย่อย" แต่สำหรับการค้นหาดัชนีด้วยสตริงย่อย (และแม้แต่นิพจน์ทั่วไป!) ก็ถือว่ายอดเยี่ยม โมดูล pg_trgm! จากนั้นจึงจำเป็นต้องเรียงลำดับให้ถูกต้อง

ลองใช้เพลตต่อไปนี้เพื่อทำให้โมเดลง่ายขึ้น:

CREATE TABLE firms(
  id
    serial
      PRIMARY KEY
, name
    text
);

เราอัปโหลดบันทึกขององค์กรจริงจำนวน 7.8 ล้านรายการและจัดทำดัชนี:

CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);

มาดู 10 บันทึกแรกสำหรับการค้นหาแบบอินไลน์:

SELECT
  *
FROM
  firms
WHERE
  lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"
[ดูที่ expand.tensor.ru]

นั่นคือ... 26 มิลลิวินาที, 31 เมกะไบต์ อ่านข้อมูลและบันทึกที่กรองมากกว่า 1.7K - สำหรับการค้นหา 10 รายการ ต้นทุนค่าโสหุ้ยสูงเกินไป ไม่มีอะไรมีประสิทธิภาพไปมากกว่านี้อีกแล้วเหรอ?

1.2: ค้นหาด้วยข้อความ? มันคือเอฟทีเอส!

แท้จริงแล้ว PostgreSQL มอบบริการที่ทรงพลังมาก เครื่องมือค้นหาข้อความแบบเต็ม (Full Text Search) รวมถึงความสามารถในการค้นหาคำนำหน้า ตัวเลือกที่ยอดเยี่ยม คุณไม่จำเป็นต้องติดตั้งส่วนขยายด้วยซ้ำ! มาลองกัน:

CREATE INDEX ON firms USING gin(to_tsvector('simple'::regconfig, lower(name)));

SELECT
  *
FROM
  firms
WHERE
  to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*')
ORDER BY
  lower(name) ~ ('^' || 'роза') DESC
, lower(name)
LIMIT 10;

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"
[ดูที่ expand.tensor.ru]

การดำเนินการค้นหาแบบขนานที่นี่ช่วยเราได้เล็กน้อย โดยลดเวลาลงครึ่งหนึ่ง 11ms. และเราต้องอ่านน้อยลง 1.5 เท่า - โดยรวมแล้ว 20MB. แต่ที่นี่ ยิ่งน้อยก็ยิ่งดี เพราะยิ่งเราอ่านข้อมูลได้มากเท่าใด โอกาสที่จะพลาดแคชก็จะยิ่งมากขึ้นเท่านั้น และข้อมูลทุกหน้าพิเศษที่อ่านจากดิสก์ก็อาจเป็น “เบรก” สำหรับคำขอได้

1.3: ยังชอบอยู่หรือเปล่า?

คำขอก่อนหน้านี้ดีสำหรับทุกคนแต่ขอแค่ดึงวันละแสนครั้งเท่านั้นมันก็มา 2TB อ่านข้อมูล ในกรณีที่ดีที่สุด จากหน่วยความจำ แต่ถ้าคุณโชคไม่ดี ก็มาจากดิสก์ เรามาลองทำให้มันเล็กลงกันดีกว่า

จำไว้ว่าผู้ใช้ต้องการเห็นอะไร ครั้งแรก “ซึ่งขึ้นต้นด้วย...”. นี่จึงเป็นรูปแบบที่บริสุทธิ์ที่สุด ค้นหาคำนำหน้า ด้วย text_pattern_ops! และเฉพาะในกรณีที่เรา "มีไม่เพียงพอ" ถึง 10 รายการที่เรากำลังมองหา เราจะต้องอ่านให้เสร็จสิ้นโดยใช้การค้นหา FTS:

CREATE INDEX ON firms(lower(name) text_pattern_ops);

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
LIMIT 10;

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"
[ดูที่ expand.tensor.ru]

ประสิทธิภาพดีเยี่ยม-โดยรวม 0.05ms และมากกว่า 100KB เล็กน้อย อ่าน! เพียงแต่เราลืมไป เรียงตามชื่อเพื่อให้ผู้ใช้ไม่หลงทางในผลลัพธ์:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name)
LIMIT 10;

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"
[ดูที่ expand.tensor.ru]

โอ้ บางสิ่งบางอย่างไม่สวยงามอีกต่อไป - ดูเหมือนว่าจะมีดัชนีอยู่ แต่การเรียงลำดับบินผ่านมันไป... แน่นอนว่ามันมีประสิทธิภาพมากกว่าตัวเลือกก่อนหน้าหลายเท่าอยู่แล้ว แต่...

1.4: “จบด้วยไฟล์”

แต่มีดัชนีที่ให้คุณค้นหาตามช่วงและยังคงใช้การเรียงลำดับตามปกติ - ปกติ!

CREATE INDEX ON firms(lower(name));

เฉพาะคำขอเท่านั้นที่จะต้อง "รวบรวมด้วยตนเอง":

SELECT
  *
FROM
  firms
WHERE
  lower(name) >= 'роза' AND
  lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
   lower(name)
LIMIT 10;

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"
[ดูที่ expand.tensor.ru]

ยอดเยี่ยม - งานคัดแยกและการใช้ทรัพยากรยังคงเป็น "กล้องจุลทรรศน์" มีประสิทธิภาพมากกว่า FTS ที่ "บริสุทธิ์" หลายพันเท่า! สิ่งที่เหลืออยู่คือการรวมเข้าด้วยกันเป็นคำขอเดียว:

(
  SELECT
    *
  FROM
    firms
  WHERE
    lower(name) >= 'роза' AND
    lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых кодировок - chr(255)
  ORDER BY
     lower(name)
  LIMIT 10
)
UNION ALL
(
  SELECT
    *
  FROM
    firms
  WHERE
    to_tsvector('simple'::regconfig, lower(name)) @@ to_tsquery('simple', 'роза:*') AND
    lower(name) NOT LIKE ('роза' || '%') -- "начинающиеся на" мы уже нашли выше
  ORDER BY
    lower(name) ~ ('^' || 'роза') DESC -- используем ту же сортировку, чтобы НЕ пойти по btree-индексу
  , lower(name)
  LIMIT 10
)
LIMIT 10;

โปรดทราบว่าแบบสอบถามย่อยที่สองจะถูกดำเนินการ เฉพาะในกรณีที่ตัวแรกกลับมาน้อยกว่าที่คาดไว้ สุดท้าย LIMIT จำนวนบรรทัด ฉันกำลังพูดถึงวิธีการเพิ่มประสิทธิภาพแบบสอบถามนี้ เขียนไปแล้วก่อนหน้านี้.

ใช่แล้ว ตอนนี้เรามีทั้ง btree และ gin อยู่บนโต๊ะแล้ว แต่ตามสถิติปรากฎว่าเป็นเช่นนั้น คำขอน้อยกว่า 10% ไปถึงการดำเนินการของบล็อกที่สอง. นั่นคือด้วยข้อจำกัดทั่วไปที่ทราบล่วงหน้าสำหรับงาน เราสามารถลดการใช้ทรัพยากรเซิร์ฟเวอร์ทั้งหมดได้เกือบพันเท่า!

1.5*: เราสามารถทำได้โดยไม่ต้องใช้ไฟล์

สูงกว่า LIKE เราถูกป้องกันไม่ให้ใช้การเรียงลำดับที่ไม่ถูกต้อง แต่สามารถ “ตั้งค่าบนเส้นทางที่ถูกต้อง” ได้โดยระบุตัวดำเนินการ USING:

โดยค่าเริ่มต้นจะถือว่า ASC. นอกจากนี้ คุณยังสามารถระบุชื่อของตัวดำเนินการเรียงลำดับเฉพาะในส่วนคำสั่งได้ USING. ตัวดำเนินการเรียงลำดับจะต้องเป็นสมาชิกของตัวดำเนินการ B-tree ที่น้อยกว่าหรือมากกว่าบางตระกูล ASC มักจะเทียบเท่า USING < и DESC มักจะเทียบเท่า USING >.

ในกรณีของเรา "น้อยกว่า" คือ ~<~:

SELECT
  *
FROM
  firms
WHERE
  lower(name) LIKE ('роза' || '%')
ORDER BY
  lower(name) USING ~<~
LIMIT 10;

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"
[ดูที่ expand.tensor.ru]

2: คำขอเปลี่ยนไปอย่างไร

ตอนนี้เราปล่อยให้คำขอของเรา "เคี่ยว" เป็นเวลาหกเดือนหรือหนึ่งปีและเราก็ต้องประหลาดใจที่พบว่า "ที่ด้านบน" อีกครั้งพร้อมตัวบ่งชี้ "การปั๊ม" ของหน่วยความจำรายวันทั้งหมด (บัฟเฟอร์ที่ใช้ร่วมกัน Hit) ใน 5.5TB - นั่นคือมากกว่าเดิมด้วยซ้ำ

ไม่ แน่นอนว่าธุรกิจของเราเติบโตขึ้นและภาระงานก็เพิ่มขึ้น แต่ก็ไม่เท่ากัน! ซึ่งหมายความว่ามีบางอย่างคาวที่นี่ - ลองคิดดูสิ

2.1: การกำเนิดของเพจ

เมื่อถึงจุดหนึ่ง ทีมพัฒนาอื่นต้องการให้สามารถ "ข้าม" จากการค้นหาตัวห้อยอย่างรวดเร็วไปยังรีจิสทรีด้วยผลลัพธ์ที่เหมือนกัน แต่มีการขยายออกไป รีจิสทรีที่ไม่มีการนำทางหน้าคืออะไร มาทำให้มันพังกันเถอะ!

( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;

ตอนนี้คุณสามารถแสดงรีจิสทรีของผลการค้นหาด้วยการโหลด "ทีละหน้า" โดยไม่ต้องเครียดสำหรับนักพัฒนา

แน่นอนว่าในความเป็นจริงแล้ว สำหรับแต่ละหน้าถัดไปของข้อมูลจะมีการอ่านมากขึ้นเรื่อยๆ (ทั้งหมดจากครั้งก่อนซึ่งเราจะทิ้งไปบวกกับ "หาง") - นั่นคือนี่คือการต่อต้านรูปแบบที่ชัดเจน แต่จะเป็นการถูกต้องมากกว่าที่จะเริ่มการค้นหาในการวนซ้ำครั้งถัดไปจากคีย์ที่เก็บไว้ในอินเทอร์เฟซ แต่เกี่ยวกับเรื่องนั้นในครั้งต่อไป

2.2: ฉันต้องการบางสิ่งที่แปลกใหม่

เมื่อถึงจุดหนึ่งผู้พัฒนาต้องการ กระจายตัวอย่างผลลัพธ์ด้วยข้อมูล จากตารางอื่น ซึ่งคำขอก่อนหน้าทั้งหมดถูกส่งไปยัง CTE:

WITH q AS (
  ...
  LIMIT <N> + 10
)
SELECT
  *
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
  q
LIMIT 10 OFFSET <N>;

และถึงกระนั้นก็ตาม มันก็ไม่แย่นัก เนื่องจากแบบสอบถามย่อยจะได้รับการประเมินเพียง 10 ระเบียนที่ส่งคืน หากไม่ใช่ ...

2.3: DISTINCT ไร้สติและไร้ความปรานี

ที่ไหนสักแห่งในกระบวนการวิวัฒนาการดังกล่าวจากแบบสอบถามย่อยที่ 2 ได้หายไป NOT LIKE สภาพ. เป็นที่ชัดเจนว่าหลังจากนี้ UNION ALL เริ่มกลับมา บางรายการสองครั้ง - พบครั้งแรกที่จุดเริ่มต้นของบรรทัด และอีกครั้ง - ที่จุดเริ่มต้นของคำแรกของบรรทัดนี้ ในขีดจำกัด ระเบียนทั้งหมดของแบบสอบถามย่อยที่ 2 สามารถตรงกับระเบียนของแบบสอบถามแรกได้

Developer ทำอะไรแทนที่จะมองหาสาเหตุ?.. ไม่มีคำถาม!

  • เพิ่มขนาดเป็นสองเท่า ตัวอย่างต้นฉบับ
  • ใช้ความแตกต่างเพื่อรับเพียงอินสแตนซ์เดียวของแต่ละบรรทัด

WITH q AS (
  ( ... LIMIT <2 * N> + 10)
  UNION ALL
  ( ... LIMIT <2 * N> + 10)
  LIMIT <2 * N> + 10
)
SELECT DISTINCT
  *
, (SELECT ...) sub_query
FROM
  q
LIMIT 10 OFFSET <N>;

นั่นคือเป็นที่ชัดเจนว่าผลลัพธ์ในท้ายที่สุดก็เหมือนกันทุกประการ แต่โอกาสในการ "บิน" ไปยังแบบสอบถามย่อย CTE ที่ 2 นั้นสูงขึ้นมากและถึงแม้จะไม่มีสิ่งนี้ก็ตาม อ่านได้ชัดเจนยิ่งขึ้น.

แต่นี่ไม่ใช่สิ่งที่เศร้าที่สุด เนื่องจากผู้พัฒนาขอเลือก DISTINCT ไม่ใช่เฉพาะเจาะจง แต่สำหรับทุกสาขาในคราวเดียว บันทึก จากนั้นฟิลด์ sub_query ซึ่งเป็นผลลัพธ์ของแบบสอบถามย่อย จะถูกรวมไว้ที่นั่นโดยอัตโนมัติ ตอนนี้เพื่อดำเนินการ DISTINCTฐานข้อมูลต้องดำเนินการเรียบร้อยแล้ว ไม่ใช่ 10 แบบสอบถามย่อย แต่ทั้งหมด <2 * N> + 10!

2.4: ความร่วมมือเหนือสิ่งอื่นใด!

ดังนั้นนักพัฒนาจึงอาศัยอยู่ - พวกเขาไม่สนใจเพราะเห็นได้ชัดว่าผู้ใช้ไม่มีความอดทนเพียงพอที่จะ "ปรับ" รีจิสทรีให้เป็นค่า N ที่มีนัยสำคัญโดยมีการชะลอตัวเรื้อรังในการรับ "เพจ" แต่ละหน้าตามมา

จนนักพัฒนาจากแผนกอื่นเข้ามาหาและต้องการใช้วิธีที่สะดวกเช่นนี้ เพื่อการค้นหาซ้ำ - นั่นคือเรานำชิ้นส่วนจากตัวอย่างบางส่วน กรองตามเงื่อนไขเพิ่มเติม วาดผลลัพธ์ จากนั้นชิ้นถัดไป (ซึ่งในกรณีของเราทำได้โดยการเพิ่ม N) และต่อไปเรื่อย ๆ จนกว่าเราจะเต็มหน้าจอ

โดยทั่วไปแล้วในตัวอย่างที่จับได้ N ถึงค่าเกือบ 17Kและในเวลาเพียงวันเดียว คำขอดังกล่าวอย่างน้อย 4 รายการได้รับการดำเนินการ "ตามสายโซ่" คนสุดท้ายถูกสแกนอย่างกล้าหาญ หน่วยความจำ 1GB ต่อการวนซ้ำ...

เบ็ดเสร็จ

PostgreSQL Antipatterns: เรื่องราวของการปรับแต่งการค้นหาซ้ำตามชื่อ หรือ "การเพิ่มประสิทธิภาพไปมา"

ที่มา: will.com

เพิ่มความคิดเห็น