PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್ಸ್: ಹೆಸರಿನ ಮೂಲಕ ಹುಡುಕಾಟದ ಪುನರಾವರ್ತಿತ ಪರಿಷ್ಕರಣೆಯ ಕಥೆ, ಅಥವಾ "ಹಿಂದೆ ಮತ್ತು ಮುಂದಕ್ಕೆ ಆಪ್ಟಿಮೈಜ್ ಮಾಡುವುದು"

ದೇಶದಾದ್ಯಂತ ಮಾರಾಟ ಕಚೇರಿಗಳಿಂದ ಸಾವಿರಾರು ನಿರ್ವಾಹಕರು ದಾಖಲೆ ಮಾಡಿದ್ದಾರೆ ನಮ್ಮ CRM ವ್ಯವಸ್ಥೆ ಪ್ರತಿದಿನ ಹತ್ತು ಸಾವಿರ ಸಂಪರ್ಕಗಳು - ಸಂಭಾವ್ಯ ಅಥವಾ ಅಸ್ತಿತ್ವದಲ್ಲಿರುವ ಗ್ರಾಹಕರೊಂದಿಗೆ ಸಂವಹನದ ಸಂಗತಿಗಳು. ಮತ್ತು ಇದಕ್ಕಾಗಿ, ನೀವು ಮೊದಲು ಕ್ಲೈಂಟ್ ಅನ್ನು ಕಂಡುಹಿಡಿಯಬೇಕು ಮತ್ತು ಮೇಲಾಗಿ ಬೇಗನೆ. ಮತ್ತು ಇದು ಹೆಸರಿನಿಂದ ಹೆಚ್ಚಾಗಿ ಸಂಭವಿಸುತ್ತದೆ.

ಆದ್ದರಿಂದ, ಹೆಚ್ಚು ಲೋಡ್ ಮಾಡಲಾದ ಡೇಟಾಬೇಸ್‌ಗಳಲ್ಲಿ ಒಂದಾದ ನಮ್ಮದೇ ಆದ “ಭಾರೀ” ಪ್ರಶ್ನೆಗಳನ್ನು ಮತ್ತೊಮ್ಮೆ ವಿಶ್ಲೇಷಿಸುವುದರಲ್ಲಿ ಆಶ್ಚರ್ಯವೇನಿಲ್ಲ. VLSI ಕಾರ್ಪೊರೇಟ್ ಖಾತೆ, ನಾನು "ಮೇಲ್ಭಾಗದಲ್ಲಿ" ಕಂಡುಬಂದಿದ್ದೇನೆ ಹೆಸರಿನ ಮೂಲಕ "ತ್ವರಿತ" ಹುಡುಕಾಟಕ್ಕಾಗಿ ವಿನಂತಿಸಿ ಸಂಸ್ಥೆಯ ಕಾರ್ಡ್‌ಗಳಿಗಾಗಿ.

ಇದಲ್ಲದೆ, ಹೆಚ್ಚಿನ ತನಿಖೆಯು ಆಸಕ್ತಿದಾಯಕ ಉದಾಹರಣೆಯನ್ನು ಬಹಿರಂಗಪಡಿಸಿತು ಮೊದಲ ಆಪ್ಟಿಮೈಸೇಶನ್ ಮತ್ತು ನಂತರ ಕಾರ್ಯಕ್ಷಮತೆಯ ಅವನತಿ ಹಲವಾರು ತಂಡಗಳಿಂದ ಅದರ ಅನುಕ್ರಮ ಪರಿಷ್ಕರಣೆಯೊಂದಿಗೆ ವಿನಂತಿಸಿ, ಪ್ರತಿಯೊಂದೂ ಉತ್ತಮ ಉದ್ದೇಶಗಳೊಂದಿಗೆ ಮಾತ್ರ ಕಾರ್ಯನಿರ್ವಹಿಸುತ್ತದೆ.

0: ಬಳಕೆದಾರರಿಗೆ ಏನು ಬೇಕು?

[ಕೆಡಿಪಿವಿ ಇಲ್ಲಿಂದ]

ಅವರು ಹೆಸರಿನಿಂದ "ತ್ವರಿತ" ಹುಡುಕಾಟದ ಕುರಿತು ಮಾತನಾಡುವಾಗ ಬಳಕೆದಾರರು ಸಾಮಾನ್ಯವಾಗಿ ಏನು ಅರ್ಥೈಸುತ್ತಾರೆ? ಇದು ಯಾವುದೇ ರೀತಿಯ ಸಬ್‌ಸ್ಟ್ರಿಂಗ್‌ಗಾಗಿ "ಪ್ರಾಮಾಣಿಕ" ಹುಡುಕಾಟವಾಗಿ ಹೊರಹೊಮ್ಮುವುದಿಲ್ಲ ... LIKE '%роза%' - ಏಕೆಂದರೆ ಫಲಿತಾಂಶವು ಮಾತ್ರವಲ್ಲದೆ ಒಳಗೊಂಡಿರುತ್ತದೆ 'Розалия' и 'Магазин Роза'ಆದರೆ 'Гроза' ಮತ್ತು ಸಹ 'Дом Деда Мороза'.

ನೀವು ಅವನಿಗೆ ಒದಗಿಸುವ ದೈನಂದಿನ ಮಟ್ಟದಲ್ಲಿ ಬಳಕೆದಾರನು ಊಹಿಸುತ್ತಾನೆ ಪದದ ಆರಂಭದ ಮೂಲಕ ಹುಡುಕಿ ಶೀರ್ಷಿಕೆಯಲ್ಲಿ ಮತ್ತು ಅದನ್ನು ಹೆಚ್ಚು ಪ್ರಸ್ತುತಪಡಿಸಿ ನೊಂದಿಗೆ ಪ್ರಾರಂಭವಾಗುತ್ತದೆ ಪ್ರವೇಶಿಸಿದೆ. ಮತ್ತು ನೀವು ಅದನ್ನು ಮಾಡುತ್ತೀರಿ ಬಹುತೇಕ ತಕ್ಷಣ - ಇಂಟರ್ಲೀನಿಯರ್ ಇನ್ಪುಟ್ಗಾಗಿ.

1: ಕಾರ್ಯವನ್ನು ಮಿತಿಗೊಳಿಸಿ

ಮತ್ತು ಇನ್ನೂ ಹೆಚ್ಚಾಗಿ, ಒಬ್ಬ ವ್ಯಕ್ತಿಯು ನಿರ್ದಿಷ್ಟವಾಗಿ ಪ್ರವೇಶಿಸುವುದಿಲ್ಲ 'роз магаз', ಆದ್ದರಿಂದ ನೀವು ಪ್ರತಿ ಪದವನ್ನು ಪೂರ್ವಪ್ರತ್ಯಯದ ಮೂಲಕ ಹುಡುಕಬೇಕು. ಇಲ್ಲ, ಹಿಂದಿನ ಪದಗಳನ್ನು ಉದ್ದೇಶಪೂರ್ವಕವಾಗಿ "ಅಂಡರ್‌ಸ್ಪೆಸಿಫೈ" ಮಾಡುವುದಕ್ಕಿಂತ ಕೊನೆಯ ಪದಕ್ಕಾಗಿ ತ್ವರಿತ ಸುಳಿವುಗೆ ಪ್ರತಿಕ್ರಿಯಿಸಲು ಬಳಕೆದಾರರಿಗೆ ತುಂಬಾ ಸುಲಭ - ಯಾವುದೇ ಹುಡುಕಾಟ ಎಂಜಿನ್ ಇದನ್ನು ಹೇಗೆ ನಿರ್ವಹಿಸುತ್ತದೆ ಎಂಬುದನ್ನು ನೋಡಿ.

ಸಾಮಾನ್ಯವಾಗಿ, ಸರಿ ಸಮಸ್ಯೆಯ ಅವಶ್ಯಕತೆಗಳನ್ನು ರೂಪಿಸುವುದು ಅರ್ಧಕ್ಕಿಂತ ಹೆಚ್ಚು ಪರಿಹಾರವಾಗಿದೆ. ಕೆಲವೊಮ್ಮೆ ಎಚ್ಚರಿಕೆಯಿಂದ ಬಳಕೆಯ ಕೇಸ್ ವಿಶ್ಲೇಷಣೆ ಫಲಿತಾಂಶವನ್ನು ಗಮನಾರ್ಹವಾಗಿ ಪ್ರಭಾವಿಸಬಹುದು.

ಅಮೂರ್ತ ಡೆವಲಪರ್ ಏನು ಮಾಡುತ್ತಾನೆ?

1.0: ಬಾಹ್ಯ ಹುಡುಕಾಟ ಎಂಜಿನ್

ಓಹ್, ಹುಡುಕಾಟ ಕಷ್ಟ, ನಾನು ಏನನ್ನೂ ಮಾಡಲು ಬಯಸುವುದಿಲ್ಲ - ಅದನ್ನು ಡೆವೊಪ್ಸ್‌ಗೆ ನೀಡೋಣ! ಡೇಟಾಬೇಸ್‌ಗೆ ಬಾಹ್ಯ ಹುಡುಕಾಟ ಎಂಜಿನ್ ಅನ್ನು ನಿಯೋಜಿಸಲು ಅವರಿಗೆ ಅವಕಾಶ ಮಾಡಿಕೊಡಿ: ಸಿಂಹನಾರಿ, ಸ್ಥಿತಿಸ್ಥಾಪಕ ಹುಡುಕಾಟ,...

ಸಿಂಕ್ರೊನೈಸೇಶನ್ ಮತ್ತು ಬದಲಾವಣೆಗಳ ವೇಗದ ವಿಷಯದಲ್ಲಿ ಕಾರ್ಮಿಕ-ತೀವ್ರವಾಗಿದ್ದರೂ ಕೆಲಸ ಮಾಡುವ ಆಯ್ಕೆ. ಆದರೆ ನಮ್ಮ ವಿಷಯದಲ್ಲಿ ಅಲ್ಲ, ಏಕೆಂದರೆ ಪ್ರತಿ ಕ್ಲೈಂಟ್‌ಗೆ ಅವರ ಖಾತೆ ಡೇಟಾದ ಚೌಕಟ್ಟಿನೊಳಗೆ ಮಾತ್ರ ಹುಡುಕಾಟವನ್ನು ನಡೆಸಲಾಗುತ್ತದೆ. ಮತ್ತು ಡೇಟಾವು ಸಾಕಷ್ಟು ಹೆಚ್ಚಿನ ವ್ಯತ್ಯಾಸವನ್ನು ಹೊಂದಿದೆ - ಮತ್ತು ಮ್ಯಾನೇಜರ್ ಈಗ ಕಾರ್ಡ್ ಅನ್ನು ನಮೂದಿಸಿದ್ದರೆ 'Магазин Роза', ನಂತರ 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;

[explain.tensor.ru ನೋಡಿ]

ಸರಿ, ಅದು... 26ms, 31MB ರೀಡ್ ಡೇಟಾ ಮತ್ತು 1.7K ಗಿಂತ ಹೆಚ್ಚು ಫಿಲ್ಟರ್ ಮಾಡಿದ ದಾಖಲೆಗಳು - 10 ಹುಡುಕಿದ ದಾಖಲೆಗಳಿಗಾಗಿ. ಓವರ್ಹೆಡ್ ವೆಚ್ಚಗಳು ತುಂಬಾ ಹೆಚ್ಚಿವೆ, ಹೆಚ್ಚು ಪರಿಣಾಮಕಾರಿ ಏನಾದರೂ ಇಲ್ಲವೇ?

1.2: ಪಠ್ಯದ ಮೂಲಕ ಹುಡುಕುವುದೇ? ಇದು FTS!

ವಾಸ್ತವವಾಗಿ, PostgreSQL ಅತ್ಯಂತ ಶಕ್ತಿಶಾಲಿ ಒದಗಿಸುತ್ತದೆ ಪೂರ್ಣ ಪಠ್ಯ ಹುಡುಕಾಟ ಎಂಜಿನ್ (ಪೂರ್ಣ ಪಠ್ಯ ಹುಡುಕಾಟ), ಪೂರ್ವಪ್ರತ್ಯಯ ಹುಡುಕಾಟ ಸಾಮರ್ಥ್ಯ ಸೇರಿದಂತೆ. ಅತ್ಯುತ್ತಮ ಆಯ್ಕೆ, ನೀವು ವಿಸ್ತರಣೆಗಳನ್ನು ಸ್ಥಾಪಿಸುವ ಅಗತ್ಯವಿಲ್ಲ! ಪ್ರಯತ್ನಿಸೋಣ:

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;

[explain.tensor.ru ನೋಡಿ]

ಇಲ್ಲಿ ಪ್ರಶ್ನೆಯ ಕಾರ್ಯಗತಗೊಳಿಸುವಿಕೆಯ ಸಮಾನಾಂತರತೆಯು ನಮಗೆ ಸ್ವಲ್ಪ ಸಹಾಯ ಮಾಡಿತು, ಸಮಯವನ್ನು ಅರ್ಧಕ್ಕೆ ಕಡಿತಗೊಳಿಸಿತು 11 ಮಿ. ಮತ್ತು ನಾವು 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;

[explain.tensor.ru ನೋಡಿ]

ಅತ್ಯುತ್ತಮ ಪ್ರದರ್ಶನ - ಒಟ್ಟು 0.05ms ಮತ್ತು 100KB ಗಿಂತ ಸ್ವಲ್ಪ ಹೆಚ್ಚು ಓದು! ನಾವು ಮಾತ್ರ ಮರೆತಿದ್ದೇವೆ ಹೆಸರಿನಿಂದ ವಿಂಗಡಿಸಿಆದ್ದರಿಂದ ಬಳಕೆದಾರರು ಫಲಿತಾಂಶಗಳಲ್ಲಿ ಕಳೆದುಹೋಗುವುದಿಲ್ಲ:

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

[explain.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;

[explain.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 ಸಾಲುಗಳ ಸಂಖ್ಯೆ. ನಾನು ಈ ಪ್ರಶ್ನೆ ಆಪ್ಟಿಮೈಸೇಶನ್ ವಿಧಾನದ ಬಗ್ಗೆ ಮಾತನಾಡುತ್ತಿದ್ದೇನೆ ಮೊದಲೇ ಬರೆದಿದ್ದಾರೆ.

ಆದ್ದರಿಂದ ಹೌದು, ನಾವು ಈಗ ಮೇಜಿನ ಮೇಲೆ ಬಿಟ್ರೀ ಮತ್ತು ಜಿನ್ ಎರಡನ್ನೂ ಹೊಂದಿದ್ದೇವೆ, ಆದರೆ ಸಂಖ್ಯಾಶಾಸ್ತ್ರೀಯವಾಗಿ ಅದು ತಿರುಗುತ್ತದೆ 10% ಕ್ಕಿಂತ ಕಡಿಮೆ ವಿನಂತಿಗಳು ಎರಡನೇ ಬ್ಲಾಕ್‌ನ ಕಾರ್ಯಗತಗೊಳಿಸುವಿಕೆಯನ್ನು ತಲುಪುತ್ತವೆ. ಅಂದರೆ, ಕಾರ್ಯಕ್ಕಾಗಿ ಮುಂಚಿತವಾಗಿ ತಿಳಿದಿರುವ ಅಂತಹ ವಿಶಿಷ್ಟ ಮಿತಿಗಳೊಂದಿಗೆ, ನಾವು ಸರ್ವರ್ ಸಂಪನ್ಮೂಲಗಳ ಒಟ್ಟು ಬಳಕೆಯನ್ನು ಸುಮಾರು ಸಾವಿರ ಪಟ್ಟು ಕಡಿಮೆ ಮಾಡಲು ಸಾಧ್ಯವಾಯಿತು!

1.5*: ನಾವು ಫೈಲ್ ಇಲ್ಲದೆ ಮಾಡಬಹುದು

ಹೆಚ್ಚಿನ LIKE ತಪ್ಪಾದ ವಿಂಗಡಣೆಯನ್ನು ಬಳಸದಂತೆ ನಮ್ಮನ್ನು ತಡೆಯಲಾಗಿದೆ. ಆದರೆ USING ಆಪರೇಟರ್ ಅನ್ನು ನಿರ್ದಿಷ್ಟಪಡಿಸುವ ಮೂಲಕ ಅದನ್ನು "ಸರಿಯಾದ ಹಾದಿಯಲ್ಲಿ ಹೊಂದಿಸಬಹುದು":

ಪೂರ್ವನಿಯೋಜಿತವಾಗಿ ಇದನ್ನು ಊಹಿಸಲಾಗಿದೆ ASC. ಹೆಚ್ಚುವರಿಯಾಗಿ, ನೀವು ಷರತ್ತುಗಳಲ್ಲಿ ನಿರ್ದಿಷ್ಟ ರೀತಿಯ ಆಪರೇಟರ್‌ನ ಹೆಸರನ್ನು ನಿರ್ದಿಷ್ಟಪಡಿಸಬಹುದು USING. ವಿಂಗಡಣೆಯ ನಿರ್ವಾಹಕರು ಕೆಲವು ಬಿ-ಟ್ರೀ ನಿರ್ವಾಹಕರ ಕುಟುಂಬಕ್ಕಿಂತ ಕಡಿಮೆ ಅಥವಾ ಹೆಚ್ಚಿನ ಸದಸ್ಯರಾಗಿರಬೇಕು. ASC ಸಾಮಾನ್ಯವಾಗಿ ಸಮಾನವಾಗಿರುತ್ತದೆ USING < и DESC ಸಾಮಾನ್ಯವಾಗಿ ಸಮಾನವಾಗಿರುತ್ತದೆ USING >.

ನಮ್ಮ ಸಂದರ್ಭದಲ್ಲಿ, "ಕಡಿಮೆ" ಆಗಿದೆ ~<~:

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

[explain.tensor.ru ನೋಡಿ]

2: ವಿನಂತಿಗಳು ಹೇಗೆ ಹುಳಿಯಾಗುತ್ತವೆ

ಈಗ ನಾವು ನಮ್ಮ ವಿನಂತಿಯನ್ನು ಆರು ತಿಂಗಳು ಅಥವಾ ಒಂದು ವರ್ಷದವರೆಗೆ “ಬೇಯಿಸಲು” ಬಿಡುತ್ತೇವೆ ಮತ್ತು ಮೆಮೊರಿಯ ಒಟ್ಟು ದೈನಂದಿನ “ಪಂಪಿಂಗ್” ಸೂಚಕಗಳೊಂದಿಗೆ ಅದನ್ನು ಮತ್ತೆ “ಮೇಲ್ಭಾಗದಲ್ಲಿ” ಕಂಡು ಆಶ್ಚರ್ಯಪಡುತ್ತೇವೆ (ಬಫರ್ ಹಂಚಿಕೆ ಹಿಟ್) ರಲ್ಲಿ 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: ಡಿಸ್ಟಿಂಕ್ಟ್ ಪ್ರಜ್ಞಾಶೂನ್ಯ ಮತ್ತು ದಯೆಯಿಲ್ಲ

2 ನೇ ಉಪಪ್ರಶ್ನೆಯಿಂದ ಅಂತಹ ವಿಕಾಸದ ಪ್ರಕ್ರಿಯೆಯಲ್ಲಿ ಎಲ್ಲೋ ಕಳೆದುಹೋಯಿತು NOT LIKE ಸ್ಥಿತಿ. ಇದರ ನಂತರ ಎಂಬುದು ಸ್ಪಷ್ಟವಾಗಿದೆ UNION ALL ಹಿಂತಿರುಗಲು ಪ್ರಾರಂಭಿಸಿದರು ಕೆಲವು ನಮೂದುಗಳು ಎರಡು ಬಾರಿ - ಮೊದಲ ಸಾಲಿನ ಆರಂಭದಲ್ಲಿ ಕಂಡುಬರುತ್ತದೆ, ಮತ್ತು ನಂತರ ಮತ್ತೆ - ಈ ಸಾಲಿನ ಮೊದಲ ಪದದ ಆರಂಭದಲ್ಲಿ. ಮಿತಿಯಲ್ಲಿ, 2 ನೇ ಸಬ್ಕ್ವೆರಿಯ ಎಲ್ಲಾ ದಾಖಲೆಗಳು ಮೊದಲನೆಯ ದಾಖಲೆಗಳಿಗೆ ಹೊಂದಿಕೆಯಾಗಬಹುದು.

ಕಾರಣವನ್ನು ಹುಡುಕುವ ಬದಲು ಡೆವಲಪರ್ ಏನು ಮಾಡುತ್ತಾನೆ?.. ಪ್ರಶ್ನೆಯೇ ಇಲ್ಲ!

• ಗಾತ್ರವನ್ನು ದ್ವಿಗುಣಗೊಳಿಸಿ ಮೂಲ ಮಾದರಿಗಳು
• DISTINCT ಅನ್ನು ಅನ್ವಯಿಸಿಪ್ರತಿ ಸಾಲಿನ ಏಕೈಕ ನಿದರ್ಶನಗಳನ್ನು ಪಡೆಯಲು

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>;

ಅಂದರೆ, ಫಲಿತಾಂಶವು ಅಂತಿಮವಾಗಿ ಒಂದೇ ಆಗಿರುತ್ತದೆ ಎಂಬುದು ಸ್ಪಷ್ಟವಾಗಿದೆ, ಆದರೆ 2 ನೇ CTE ಉಪಪ್ರಶ್ನೆಗೆ "ಹಾರುವ" ಅವಕಾಶವು ಹೆಚ್ಚು ಹೆಚ್ಚಾಗಿದೆ ಮತ್ತು ಇದು ಇಲ್ಲದೆಯೂ ಸಹ, ಸ್ಪಷ್ಟವಾಗಿ ಹೆಚ್ಚು ಓದಬಲ್ಲದು.

ಆದರೆ ಇದು ಅತ್ಯಂತ ದುಃಖಕರ ವಿಷಯವಲ್ಲ. ಡೆವಲಪರ್ ಆಯ್ಕೆ ಮಾಡಲು ಕೇಳಿದಾಗಿನಿಂದ DISTINCT ನಿರ್ದಿಷ್ಟವಾದವುಗಳಿಗೆ ಅಲ್ಲ, ಆದರೆ ಎಲ್ಲಾ ಕ್ಷೇತ್ರಗಳಿಗೆ ಏಕಕಾಲದಲ್ಲಿ ದಾಖಲೆಗಳು, ನಂತರ sub_query ಕ್ಷೇತ್ರ - ಉಪಪ್ರಶ್ನೆ ಫಲಿತಾಂಶ - ಸ್ವಯಂಚಾಲಿತವಾಗಿ ಅಲ್ಲಿ ಸೇರಿಸಲಾಯಿತು. ಈಗ, ಕಾರ್ಯಗತಗೊಳಿಸಲು DISTINCT, ಡೇಟಾಬೇಸ್ ಈಗಾಗಲೇ ಕಾರ್ಯಗತಗೊಳಿಸಬೇಕಾಗಿತ್ತು 10 ಉಪಪ್ರಶ್ನೆಗಳಲ್ಲ, ಆದರೆ ಎಲ್ಲಾ <2 * N> + 10!

2.4: ಎಲ್ಲಕ್ಕಿಂತ ಹೆಚ್ಚಾಗಿ ಸಹಕಾರ!

ಆದ್ದರಿಂದ, ಡೆವಲಪರ್‌ಗಳು ವಾಸಿಸುತ್ತಿದ್ದರು - ಅವರು ತಲೆಕೆಡಿಸಿಕೊಳ್ಳಲಿಲ್ಲ, ಏಕೆಂದರೆ ಪ್ರತಿ ನಂತರದ “ಪುಟ” ಸ್ವೀಕರಿಸುವಲ್ಲಿ ದೀರ್ಘಕಾಲದ ನಿಧಾನಗತಿಯೊಂದಿಗೆ ನೋಂದಾವಣೆಯನ್ನು ಗಮನಾರ್ಹ N ಮೌಲ್ಯಗಳಿಗೆ “ಸರಿಹೊಂದಿಸಲು” ಬಳಕೆದಾರರಿಗೆ ಸಾಕಷ್ಟು ತಾಳ್ಮೆ ಇರಲಿಲ್ಲ.

ಮತ್ತೊಂದು ಇಲಾಖೆಯ ಅಭಿವರ್ಧಕರು ಅವರ ಬಳಿಗೆ ಬರುವವರೆಗೆ ಮತ್ತು ಅಂತಹ ಅನುಕೂಲಕರ ವಿಧಾನವನ್ನು ಬಳಸಲು ಬಯಸುತ್ತಾರೆ ಪುನರಾವರ್ತಿತ ಹುಡುಕಾಟಕ್ಕಾಗಿ - ಅಂದರೆ, ನಾವು ಕೆಲವು ಮಾದರಿಯಿಂದ ತುಂಡನ್ನು ತೆಗೆದುಕೊಳ್ಳುತ್ತೇವೆ, ಅದನ್ನು ಹೆಚ್ಚುವರಿ ಷರತ್ತುಗಳಿಂದ ಫಿಲ್ಟರ್ ಮಾಡಿ, ಫಲಿತಾಂಶವನ್ನು ಸೆಳೆಯಿರಿ, ನಂತರ ಮುಂದಿನ ತುಣುಕು (ನಮ್ಮ ಸಂದರ್ಭದಲ್ಲಿ N ಅನ್ನು ಹೆಚ್ಚಿಸುವ ಮೂಲಕ ಸಾಧಿಸಲಾಗುತ್ತದೆ), ಮತ್ತು ಹೀಗೆ ನಾವು ಪರದೆಯನ್ನು ತುಂಬುವವರೆಗೆ.

ಸಾಮಾನ್ಯವಾಗಿ, ಹಿಡಿದ ಮಾದರಿಯಲ್ಲಿ N ಸುಮಾರು 17K ಮೌಲ್ಯಗಳನ್ನು ತಲುಪಿದೆ, ಮತ್ತು ಕೇವಲ ಒಂದು ದಿನದಲ್ಲಿ ಕನಿಷ್ಠ 4K ಅಂತಹ ವಿನಂತಿಗಳನ್ನು "ಸರಪಳಿಯ ಉದ್ದಕ್ಕೂ" ಕಾರ್ಯಗತಗೊಳಿಸಲಾಗಿದೆ. ಅವುಗಳಲ್ಲಿ ಕೊನೆಯದನ್ನು ಧೈರ್ಯದಿಂದ ಸ್ಕ್ಯಾನ್ ಮಾಡಲಾಯಿತು ಪ್ರತಿ ಪುನರಾವರ್ತನೆಗೆ 1GB ಮೆಮೊರಿ...

ಒಟ್ಟು

ಮೂಲ: www.habr.com