Antipattern PostgreSQL: Kisah Penambahbaikan Berulang Carian mengikut Nama, atau "Mengoptimumkan Ke belakang dan ke belakang"

Beribu-ribu pengurus dari pejabat jualan di seluruh negara merekodkan sistem CRM kami puluhan ribu kenalan setiap hari — fakta komunikasi dengan bakal pelanggan atau pelanggan sedia ada. Dan untuk ini, anda mesti mencari pelanggan terlebih dahulu, dan sebaik-baiknya dengan cepat. Dan ini berlaku paling kerap dengan nama.

Oleh itu, tidak menghairankan bahawa, sekali lagi menganalisis pertanyaan "berat" pada salah satu pangkalan data yang paling dimuatkan - kami sendiri akaun korporat VLSI, saya dapati "di bahagian atas" meminta carian "cepat" mengikut nama untuk kad organisasi.

Selain itu, siasatan lanjut mendedahkan contoh yang menarik pengoptimuman pertama dan kemudian kemerosotan prestasi permintaan dengan pemurnian berurutan oleh beberapa pasukan, yang masing-masing bertindak semata-mata dengan niat terbaik.

0: apa yang pengguna mahukan?

[KDPV oleh itu]

Apakah yang biasanya dimaksudkan oleh pengguna apabila mereka bercakap tentang carian "cepat" mengikut nama? Ia hampir tidak pernah menjadi carian "jujur" untuk subrentetan seperti ... LIKE '%роза%' - kerana kemudian hasilnya termasuk bukan sahaja 'Розалия' и 'Магазин Роза'Tetapi 'Гроза' dan juga 'Дом Деда Мороза'.

Pengguna menganggap pada tahap harian yang anda akan berikan kepadanya cari mengikut permulaan perkataan dalam tajuk dan menjadikannya lebih relevan bermula dengan masuk. Dan anda akan melakukannya hampir serta merta - untuk input antara linear.

1: hadkan tugas

Dan lebih-lebih lagi, seseorang tidak akan masuk secara khusus 'роз магаз', supaya anda perlu mencari setiap perkataan mengikut awalan. Tidak, adalah lebih mudah bagi pengguna untuk bertindak balas kepada pembayang pantas untuk perkataan terakhir daripada sengaja "meremehkan" perkataan sebelumnya - lihat cara mana-mana enjin carian mengendalikan perkara ini.

Secara umumnya, betul merumuskan keperluan untuk masalah adalah lebih daripada separuh penyelesaian. Kadangkala analisis kes penggunaan yang teliti boleh mempengaruhi keputusan dengan ketara.

Apakah yang dilakukan oleh pembangun abstrak?

1.0: enjin carian luaran

Oh, pencarian adalah sukar, saya tidak mahu melakukan apa-apa - mari berikan kepada devops! Biarkan mereka menggunakan enjin carian di luar pangkalan data: Sphinx, ElasticSearch,...

Pilihan kerja, walaupun intensif buruh dari segi penyegerakan dan kelajuan perubahan. Tetapi tidak dalam kes kami, kerana carian dijalankan untuk setiap pelanggan hanya dalam rangka kerja data akaunnya. Dan data mempunyai kebolehubahan yang agak tinggi - dan jika pengurus kini telah memasuki kad 'Магазин Роза', kemudian selepas 5-10 saat dia mungkin sudah ingat bahawa dia terlupa untuk menunjukkan e-melnya di sana dan ingin mencari dan membetulkannya.

Oleh itu - mari cari "terus dalam pangkalan data". Nasib baik, PostgreSQL membenarkan kami melakukan ini, dan bukan hanya satu pilihan - kami akan melihatnya.

1.1: subrentetan "jujur".

Kami berpegang kepada perkataan "substring". Tetapi untuk carian indeks mengikut subrentetan (dan juga dengan ungkapan biasa!) terdapat yang sangat baik modul pg_trgm! Hanya selepas itu anda perlu mengisih dengan betul.

Mari cuba ambil plat berikut untuk memudahkan model:

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

Kami memuat naik 7.8 juta rekod organisasi sebenar di sana dan mengindeksnya:

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

Mari cari 10 rekod pertama untuk carian interlinear:

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

[lihat explain.tensor.ru]

Nah, itu... 26ms, 31MB membaca data dan lebih daripada 1.7K rekod yang ditapis - untuk 10 yang dicari. Kos overhed terlalu tinggi, bukankah ada yang lebih cekap?

1.2: cari melalui teks? Ia FTS!

Sesungguhnya, PostgreSQL menyediakan yang sangat berkuasa enjin carian teks penuh (Carian Teks Penuh), termasuk keupayaan untuk awalan carian. Pilihan yang bagus, anda tidak perlu memasang sambungan! Mari kita cuba:

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;

[lihat explain.tensor.ru]

Di sini penyelarasan pelaksanaan pertanyaan membantu kami sedikit, mengurangkan masa separuh kepada 11ms. Dan kami terpaksa membaca 1.5 kali lebih sedikit - secara keseluruhan 20MB. Tetapi di sini, semakin kurang, semakin baik, kerana semakin besar volum yang kita baca, semakin tinggi peluang untuk kehilangan cache, dan setiap halaman tambahan data yang dibaca daripada cakera adalah "brek" yang berpotensi untuk permintaan itu.

1.3: masih SUKA?

Permintaan sebelumnya adalah baik untuk semua orang, tetapi hanya jika anda menariknya seratus ribu kali sehari, ia akan datang 2TB membaca data. Dalam kes terbaik, dari ingatan, tetapi jika anda tidak bernasib baik, maka dari cakera. Jadi mari kita cuba untuk menjadikannya lebih kecil.

Mari kita ingat apa yang pengguna mahu lihat pertama "yang bermula dengan...". Jadi ini adalah dalam bentuk yang paling tulen carian awalan melalui text_pattern_ops! Dan hanya jika kami "tidak mempunyai cukup" sehingga 10 rekod yang kami cari, maka kami perlu menyelesaikan membacanya menggunakan carian FTS:

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

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

[lihat explain.tensor.ru]

Prestasi cemerlang - jumlah 0.05ms dan lebih sedikit daripada 100KB bacalah! Hanya kita yang lupa susun mengikut namasupaya pengguna tidak tersesat dalam keputusan:

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

[lihat explain.tensor.ru]

Oh, sesuatu yang tidak begitu indah lagi - nampaknya seperti terdapat indeks, tetapi pengisihan melepasinya... Sudah tentu, ia sudah berkali-kali lebih berkesan daripada pilihan sebelumnya, tetapi...

1.4: "selesaikan dengan fail"

Tetapi terdapat indeks yang membolehkan anda mencari mengikut julat dan masih menggunakan pengisihan seperti biasa - btree biasa!

CREATE INDEX ON firms(lower(name));

Hanya permintaan untuk itu perlu "dikumpul secara manual":

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

[lihat explain.tensor.ru]

Cemerlang - pengisihan berfungsi, dan penggunaan sumber kekal "mikroskopik", beribu kali lebih berkesan daripada FTS "tulen".! Apa yang tinggal adalah untuk menggabungkannya menjadi satu permintaan:

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

Perhatikan bahawa subquery kedua dilaksanakan hanya jika yang pertama kembali kurang daripada yang dijangkakan yang terakhir LIMIT bilangan baris. Saya bercakap tentang kaedah pengoptimuman pertanyaan ini sudah menulis sebelum ini.

Jadi ya, kami kini mempunyai btree dan gin di atas meja, tetapi secara statistik ternyata begitu kurang daripada 10% permintaan mencapai pelaksanaan blok kedua. Iaitu, dengan batasan tipikal yang diketahui lebih awal untuk tugas itu, kami dapat mengurangkan jumlah penggunaan sumber pelayan hampir seribu kali ganda!

1.5*: kita boleh lakukan tanpa fail

Di atas LIKE Kami telah dihalang daripada menggunakan pengisihan yang salah. Tetapi ia boleh "ditetapkan pada laluan yang betul" dengan menyatakan operator USING:

Secara lalai ia diandaikan ASC. Selain itu, anda boleh menentukan nama pengendali isihan tertentu dalam klausa USING. Pengendali isihan mestilah ahli yang kurang daripada atau lebih besar daripada beberapa keluarga pengendali B-tree. ASC biasanya setara USING < и DESC biasanya setara USING >.

Dalam kes kami, "kurang" adalah ~<~:

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

[lihat explain.tensor.ru]

2: bagaimana permintaan berubah menjadi masam

Sekarang kami meninggalkan permintaan kami untuk "mereneh" selama enam bulan atau setahun, dan kami terkejut apabila mendapatinya sekali lagi "di bahagian atas" dengan penunjuk jumlah "pengepaman" memori harian (penimbal kongsi hit) dalam 5.5TB - iaitu, lebih daripada asalnya.

Tidak, sudah tentu, perniagaan kami telah berkembang dan beban kerja kami telah meningkat, tetapi bukan dengan jumlah yang sama! Ini bermakna ada sesuatu yang mencurigakan di sini - mari kita fikirkan.

2.1: kelahiran paging

Pada satu ketika, pasukan pembangunan lain ingin membolehkan "melompat" daripada carian subskrip pantas ke pendaftaran dengan hasil yang sama, tetapi berkembang. Apakah itu pendaftaran tanpa navigasi halaman? Mari kita kacau!

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

Kini adalah mungkin untuk menunjukkan pendaftaran hasil carian dengan pemuatan "halaman demi halaman" tanpa sebarang tekanan untuk pembangun.

Sudah tentu, sebenarnya, untuk setiap halaman berikutnya data semakin banyak dibaca (semua dari masa sebelumnya, yang akan kami buang, ditambah dengan "ekor") yang diperlukan - iaitu, ini adalah antipattern yang jelas. Tetapi adalah lebih tepat untuk memulakan carian pada lelaran seterusnya daripada kunci yang disimpan dalam antara muka, tetapi kira-kira pada masa yang lain.

2.2: Saya mahukan sesuatu yang eksotik

Pada satu ketika pemaju mahu mempelbagaikan sampel yang terhasil dengan data daripada jadual lain, yang mana keseluruhan permintaan sebelumnya telah dihantar kepada CTE:

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

Dan walaupun begitu, ia tidak buruk, kerana subquery dinilai hanya untuk 10 rekod yang dikembalikan, jika tidak...

2.3: DISTINCT adalah tidak masuk akal dan tanpa belas kasihan

Di suatu tempat dalam proses evolusi sedemikian daripada subkueri ke-2 tersesat NOT LIKE keadaan. Jelasnya selepas ini UNION ALL mula kembali beberapa entri dua kali - mula-mula ditemui pada permulaan baris, dan kemudian sekali lagi - pada permulaan perkataan pertama baris ini. Dalam had, semua rekod subkueri ke-2 boleh sepadan dengan rekod yang pertama.

Apa yang dilakukan oleh pembangun dan bukannya mencari puncanya?.. No question!

• dua kali ganda saiz sampel asal
• memohon DISTINCTuntuk mendapatkan hanya kejadian tunggal bagi setiap baris

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

Iaitu, jelas bahawa hasilnya, pada akhirnya, adalah sama, tetapi peluang untuk "terbang" ke subkueri CTE ke-2 telah menjadi lebih tinggi, dan walaupun tanpa ini, jelas lebih mudah dibaca.

Tetapi ini bukan perkara yang paling menyedihkan. Sejak pemaju meminta untuk memilih DISTINCT bukan untuk yang khusus, tetapi untuk semua bidang sekaligus rekod, kemudian medan sub_query — hasil subquery — dimasukkan secara automatik di sana. Sekarang, untuk melaksanakan DISTINCT, pangkalan data terpaksa dilaksanakan sudah bukan 10 subqueries, tetapi semua <2 * N> + 10!

2.4: kerjasama di atas segalanya!

Jadi, pemaju hidup tanpa mengganggu, kerana pengguna jelas tidak mempunyai kesabaran yang cukup untuk "melaraskan" pendaftaran kepada nilai N yang ketara dengan kelembapan kronik dalam menerima setiap "halaman" berikutnya.

Sehingga pemaju dari jabatan lain datang kepada mereka dan mahu menggunakan kaedah yang begitu mudah untuk carian berulang - iaitu, kami mengambil sekeping dari beberapa sampel, menapisnya dengan syarat tambahan, lukiskan hasilnya, kemudian sekeping seterusnya (yang dalam kes kami dicapai dengan meningkatkan N), dan seterusnya sehingga kami mengisi skrin.

Secara umum, dalam spesimen yang ditangkap N mencapai nilai hampir 17K, dan dalam hanya satu hari sekurang-kurangnya 4K permintaan sedemikian telah dilaksanakan "di sepanjang rantaian". Yang terakhir daripada mereka dengan berani diimbas oleh 1GB memori setiap lelaran...

Dalam jumlah

Sumber: www.habr.com