Hàng ngàn nhà quản lý từ các văn phòng bán hàng trên toàn quốc đã lập kỷ lục hàng chục ngàn liên hệ mỗi ngày - thông tin thực tế về việc trao đổi thông tin với khách hàng tiềm năng hoặc khách hàng hiện tại. Và để làm được điều này, trước tiên bạn phải tìm được khách hàng và tốt nhất là phải tìm được thật nhanh chóng. Và điều này xảy ra thường xuyên nhất theo tên.
Vì vậy, không có gì đáng ngạc nhiên khi một lần nữa phân tích các truy vấn “nặng” trên một trong những cơ sở dữ liệu được tải nhiều nhất - của chúng tôi , tôi tìm thấy "ở trên cùng" yêu cầu tìm kiếm “nhanh” theo tên đối với thẻ tổ chức.
Hơn nữa, cuộc điều tra sâu hơn đã tiết lộ một ví dụ thú vị tối ưu hóa đầu tiên và sau đó là suy giảm hiệu suất yêu cầu được sàng lọc tuần tự bởi một số nhóm, mỗi nhóm chỉ hành động với mục đích tốt nhất.
0: người dùng muốn gì?
[KDPV ]
Người dùng thường có ý gì khi nói về tìm kiếm “nhanh” theo tên? Nó hầu như không bao giờ trở thành một cuộc tìm kiếm “trung thực” cho một chuỗi con như ... LIKE '%роза%' - bởi vì khi đó kết quả không chỉ bao gồm 'Розалия' и 'Магазин Роза'Nhưng 'Гроза' và thậm chí 'Дом Деда Мороза'.
Người dùng giả định ở cấp độ hàng ngày rằng bạn sẽ cung cấp cho anh ta tìm kiếm theo đầu từ trong tiêu đề và làm cho nó phù hợp hơn bắt đầu với đã vào. Và bạn sẽ làm được điều đó gần như ngay lập tức - cho đầu vào liên tuyến.
1: giới hạn nhiệm vụ
Và hơn thế nữa, một người sẽ không cụ thể nhập 'роз магаз', do đó bạn phải tìm kiếm từng từ theo tiền tố. Không, người dùng sẽ dễ dàng phản hồi gợi ý nhanh cho từ cuối cùng hơn là cố tình “xác định thiếu” những từ trước đó - hãy xem cách bất kỳ công cụ tìm kiếm nào xử lý việc này.
Nói chung, một cách chính xác việc xây dựng các yêu cầu cho vấn đề là hơn một nửa giải pháp. Đôi khi phân tích trường hợp sử dụng cẩn thận .
Một nhà phát triển trừu tượng làm gì?
1.0: công cụ tìm kiếm bên ngoài
Ồ, tìm kiếm thật khó khăn, tôi không muốn làm gì cả - hãy giao việc đó cho các nhà phát triển! Hãy để họ triển khai một công cụ tìm kiếm bên ngoài cơ sở dữ liệu: Sphinx, ElasticSearch,...
Một lựa chọn làm việc, mặc dù tốn nhiều công sức về mặt đồng bộ hóa và tốc độ thay đổi. Nhưng không phải trong trường hợp của chúng tôi, vì việc tìm kiếm chỉ được thực hiện đối với từng khách hàng trong khuôn khổ dữ liệu tài khoản của họ. Và dữ liệu có độ biến thiên khá cao - và nếu người quản lý hiện đã nhập thẻ 'Магазин Роза', thì sau 5-10 giây, anh ta có thể nhớ ra rằng mình đã quên cho biết email của mình ở đó và muốn tìm lại và sửa lại.
Vì vậy - chúng ta hãy tìm kiếm "trực tiếp trong cơ sở dữ liệu". May mắn thay, PostgreSQL cho phép chúng tôi thực hiện việc này chứ không chỉ có một tùy chọn - chúng tôi sẽ xem xét chúng.
1.1: chuỗi con "trung thực"
Chúng ta bám vào từ “chuỗi con”. Nhưng để tìm kiếm chỉ mục theo chuỗi con (và thậm chí theo biểu thức chính quy!) thì có một cách tuyệt vời ! Chỉ khi đó mới cần phải sắp xếp chính xác.
Hãy thử lấy tấm sau để đơn giản hóa mô hình:
CREATE TABLE firms(
id
serial
PRIMARY KEY
, name
text
);Chúng tôi tải lên 7.8 triệu hồ sơ của các tổ chức thực sự ở đó và lập chỉ mục cho chúng:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON firms USING gin(lower(name) gin_trgm_ops);Hãy tìm 10 bản ghi đầu tiên cho tìm kiếm xen kẽ:
SELECT
*
FROM
firms
WHERE
lower(name) ~ ('(^|s)' || 'роза')
ORDER BY
lower(name) ~ ('^' || 'роза') DESC -- сначала "начинающиеся на"
, lower(name) -- остальное по алфавиту
LIMIT 10;
Chà, như vậy ... 26 mili giây, 31 MB đọc dữ liệu và hơn 1.7K bản ghi được lọc - cho 10 bản ghi được tìm kiếm. Chi phí chung quá cao, không có cách nào hiệu quả hơn sao?
1.2: tìm kiếm bằng văn bản? Đó là FTS!
Quả thực, PostgreSQL cung cấp một công cụ rất mạnh mẽ (Tìm kiếm toàn văn), bao gồm khả năng tìm kiếm tiền tố. Một lựa chọn tuyệt vời, bạn thậm chí không cần phải cài đặt tiện ích mở rộng! Hãy thử:
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;
Ở đây, việc thực hiện truy vấn song song đã giúp chúng tôi một chút, giảm thời gian xuống còn một nửa 11ms. Và chúng tôi phải đọc ít hơn 1.5 lần - tổng cộng 20MB. Nhưng ở đây, càng ít thì càng tốt, vì khối lượng chúng ta đọc càng lớn thì khả năng bị thiếu bộ nhớ đệm càng cao và mỗi trang dữ liệu bổ sung được đọc từ đĩa đều là một “cú phanh” tiềm năng cho yêu cầu.
1.3: vẫn THÍCH?
Yêu cầu trước đó tốt cho tất cả mọi người, nhưng chỉ khi bạn thực hiện nó một trăm nghìn lần một ngày thì nó sẽ đến 2TB đọc dữ liệu. Trong trường hợp tốt nhất, từ bộ nhớ, nhưng nếu bạn không may mắn, thì từ đĩa. Vì vậy, hãy cố gắng làm cho nó nhỏ hơn.
Hãy nhớ những gì người dùng muốn xem đầu tiên “bắt đầu bằng…”. Vậy đây là dạng tinh khiết nhất thông qua text_pattern_ops! Và chỉ khi chúng tôi “không có đủ” tối đa 10 bản ghi mà chúng tôi đang tìm kiếm, thì chúng tôi sẽ phải đọc xong chúng bằng tìm kiếm FTS:
CREATE INDEX ON firms(lower(name) text_pattern_ops);SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
LIMIT 10;
Hiệu suất tuyệt vời - tổng thể 0.05ms và hơn 100KB một chút đọc! Chỉ có chúng ta đã quên sắp xếp theo tênđể người dùng không bị lạc trong kết quả:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name)
LIMIT 10;
Ồ, có thứ gì đó không còn đẹp nữa - có vẻ như có một chỉ mục, nhưng việc sắp xếp lại trôi qua nó... Tất nhiên, nó đã hiệu quả hơn nhiều lần so với tùy chọn trước đó, nhưng...
1.4: “kết thúc bằng một tập tin”
Nhưng có một chỉ mục cho phép bạn tìm kiếm theo phạm vi mà vẫn sử dụng cách sắp xếp bình thường - btree thường xuyên!
CREATE INDEX ON firms(lower(name));Chỉ yêu cầu về nó mới phải được “thu thập thủ công”:
SELECT
*
FROM
firms
WHERE
lower(name) >= 'роза' AND
lower(name) <= ('роза' || chr(65535)) -- для UTF8, для однобайтовых - chr(255)
ORDER BY
lower(name)
LIMIT 10;
Tuyệt vời - công việc phân loại và mức tiêu thụ tài nguyên vẫn ở mức "vi mô", hiệu quả gấp hàng nghìn lần so với FTS “thuần túy”! Tất cả những gì còn lại là tập hợp nó lại thành một yêu cầu duy nhất:
(
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;
Lưu ý rằng truy vấn con thứ hai được thực thi chỉ khi cái đầu tiên trả về ít hơn mong đợi cuối cùng LIMIT số dòng. Tôi đang nói về phương pháp tối ưu hóa truy vấn này .
Vâng, bây giờ chúng ta có cả btree và gin trên bàn, nhưng theo thống kê thì hóa ra là ít hơn 10% yêu cầu đạt được việc thực thi khối thứ hai. Nghĩa là, với những hạn chế điển hình được biết trước cho tác vụ, chúng tôi đã có thể giảm tổng mức tiêu thụ tài nguyên máy chủ gần một nghìn lần!
1.5*: chúng tôi có thể thực hiện mà không cần tệp
Cao hơn LIKE Chúng tôi đã bị ngăn cản việc sử dụng cách sắp xếp không chính xác. Nhưng nó có thể được “đặt đi đúng hướng” bằng cách chỉ định toán tử USING:
Theo mặc định, nó được giả định
ASC. Ngoài ra, bạn có thể chỉ định tên của toán tử sắp xếp cụ thể trong mệnh đềUSING. Toán tử sắp xếp phải là thành viên của toán tử nhỏ hơn hoặc lớn hơn của một số họ toán tử cây B.ASCthường tương đươngUSING <иDESCthường tương đươngUSING >.
Trong trường hợp của chúng tôi, “ít hơn” là ~<~:
SELECT
*
FROM
firms
WHERE
lower(name) LIKE ('роза' || '%')
ORDER BY
lower(name) USING ~<~
LIMIT 10;
2: các yêu cầu trở nên chua chát như thế nào
Bây giờ chúng tôi để yêu cầu của mình ở chế độ “đun sôi” trong sáu tháng hoặc một năm, và chúng tôi rất ngạc nhiên khi thấy nó lại “ở trên cùng” với các chỉ số về tổng lượng “bơm” bộ nhớ hàng ngày (lượt truy cập được chia sẻ vào bộ đệm) Trong 5.5TB - nghĩa là, thậm chí còn nhiều hơn ban đầu.
Không, tất nhiên, hoạt động kinh doanh của chúng tôi đã phát triển và khối lượng công việc của chúng tôi tăng lên, nhưng không nhiều như vậy! Điều này có nghĩa là có điều gì đó đáng nghi ở đây - hãy cùng tìm hiểu.
2.1: sự ra đời của phân trang
Tại một thời điểm nào đó, một nhóm phát triển khác muốn có thể “chuyển” từ tìm kiếm chỉ số nhanh sang sổ đăng ký với kết quả tương tự nhưng được mở rộng. Sổ đăng ký không có điều hướng trang là gì? Hãy làm hỏng nó đi!
( ... LIMIT <N> + 10)
UNION ALL
( ... LIMIT <N> + 10)
LIMIT 10 OFFSET <N>;Giờ đây, người ta có thể hiển thị sổ đăng ký kết quả tìm kiếm bằng cách tải “từng trang” mà không gây bất kỳ căng thẳng nào cho nhà phát triển.
Tất nhiên, trên thực tế, đối với mỗi trang dữ liệu tiếp theo ngày càng được đọc nhiều hơn (tất cả từ lần trước mà chúng tôi sẽ loại bỏ, cộng với "đuôi" cần thiết) - nghĩa là đây là một mô hình phản mẫu rõ ràng. Nhưng sẽ chính xác hơn nếu bắt đầu tìm kiếm ở lần lặp tiếp theo từ khóa được lưu trong giao diện, nhưng về điều đó vào lúc khác.
2.2: Tôi muốn thứ gì đó kỳ lạ
Tại một số điểm, nhà phát triển muốn đa dạng hóa mẫu kết quả với dữ liệu từ một bảng khác mà toàn bộ yêu cầu trước đó đã được gửi tới CTE:
WITH q AS (
...
LIMIT <N> + 10
)
SELECT
*
, (SELECT ...) sub_query -- какой-то запрос к связанной таблице
FROM
q
LIMIT 10 OFFSET <N>;Và thậm chí như vậy, nó cũng không tệ, vì truy vấn con chỉ được đánh giá cho 10 bản ghi được trả về, nếu không ...
2.3: DISTINCT là vô nghĩa và tàn nhẫn
Ở đâu đó trong quá trình phát triển như vậy từ truy vấn con thứ 2 mất đi NOT LIKE điều kiện. Rõ ràng là sau chuyện này UNION ALL bắt đầu quay trở lại một số mục hai lần - lần đầu tiên được tìm thấy ở đầu dòng, và sau đó một lần nữa - ở đầu từ đầu tiên của dòng này. Trong giới hạn, tất cả các bản ghi của truy vấn con thứ 2 có thể khớp với các bản ghi của truy vấn con thứ nhất.
Nhà phát triển làm gì thay vì tìm kiếm nguyên nhân?.. Không có câu hỏi nào!
- gấp đôi kích thước mẫu ban đầu
- áp dụng DISTINCTđể chỉ nhận được các phiên bản duy nhất của mỗi dòng
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>;Nghĩa là, rõ ràng là kết quả cuối cùng hoàn toàn giống nhau, nhưng cơ hội “bay” vào truy vấn con CTE thứ 2 đã trở nên cao hơn nhiều, và ngay cả khi không có điều này, rõ ràng dễ đọc hơn.
Nhưng đây chưa phải là điều đáng buồn nhất. Vì nhà phát triển đã yêu cầu chọn DISTINCT không dành cho những trường cụ thể mà dành cho tất cả các trường cùng một lúc bản ghi, thì trường sub_query — kết quả của truy vấn con — sẽ tự động được đưa vào đó. Bây giờ, để thực thi DISTINCT, cơ sở dữ liệu đã phải thực thi rồi không phải 10 truy vấn phụ mà là tất cả <2 * N> + 10!
2.4: hợp tác là trên hết!
Vì vậy, các nhà phát triển vẫn tiếp tục - họ không bận tâm, bởi vì người dùng rõ ràng không đủ kiên nhẫn để “điều chỉnh” sổ đăng ký thành các giá trị N đáng kể với tình trạng nhận từng “trang” tiếp theo bị chậm lại thường xuyên.
Cho đến khi các nhà phát triển từ bộ phận khác đến gặp họ và muốn sử dụng một phương pháp tiện lợi như vậy cho tìm kiếm lặp lại - nghĩa là, chúng ta lấy một phần từ một số mẫu, lọc nó theo các điều kiện bổ sung, rút ra kết quả, sau đó là phần tiếp theo (trong trường hợp của chúng ta đạt được bằng cách tăng N), v.v. cho đến khi chúng ta lấp đầy màn hình.
Nhìn chung, trong mẫu vật bắt được N đạt giá trị gần 17Kvà chỉ trong một ngày, ít nhất 4K yêu cầu như vậy đã được thực thi “dọc theo chuỗi”. Cái cuối cùng trong số chúng được quét mạnh mẽ bởi 1GB bộ nhớ cho mỗi lần lặp...
trong tổng số
Nguồn: www.habr.com