По Digoal
Если вы являетесь администратором баз данных в ИТ-индустрии, возможно, вы работаете в компании по анализу данных для бизнеса (2B) и, возможно, разработали таблицу (включая идентификаторы пользователей и несколько хорошо отсортированных значений свойств) или собрали некоторые пользовательские данные и необходимо предоставлять отчеты клиентам. Возможно, вы также запрашивали случайные комбинации значений свойств, и вам нужно было быстро вернуть результаты клиенту.
Все это общие требования компаний, занимающихся платформами данных 2B. Часто вы не можете удовлетворить требования с помощью моделирования, потому что требования B-end непредсказуемы, а любая комбинация запросов требует ответа в реальном времени.
У вас могут быть миллиарды записей данных о клиентах, и каждая из них может иметь сотни свойств. Пользователям могут потребоваться результаты запроса для любой комбинации свойств.
Что касается быстрого ответа, задумывались ли вы о создании индексов для условий запроса?
Например,
where col1=? and col2=? and col3<>? or col4=?;Каков ваш план для такого рода SQL? Создание индекса для (col1, col2) и другого для col4?
Возможно, что в следующий раз пользователи могут изменить свои условия запроса.
where col3=1 or col100=?Creating indexes for col3, col100?
Вы можете обнаружить, что вообще не можете оптимизировать это, потому что могут быть тысячи соответствующих комбинаций индексов запросов.
Метод 1: составные индексы GIN
Создайте составные индексы GIN для полей, которые будут использоваться в запросах.
Этот метод работает для случаев со случайными комбинациями полей. Для множественных условий запроса PostgreSQL использует index + bitmapAnd или bitmapOr для внутренней фильтрации блоков, чтобы получить промежуточные результаты.
+---------------------------------------------+|100000000001000000010000000000000111100000000| bitmap 1|000001000001000100010000000001000010000000010| bitmap 2&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&|000000000001000000010000000000000010000000000| Combined bitmap+-----------+-------+--------------+----------+| | |v v vUsed to scan the heap only for matching pages:+---------------------------------------------+|___________X_______X______________X__________|+---------------------------------------------+
Но что делает этот метод быстрым?
Потому что GIN-индексы реализуют bitmapAnd или bitmapOr внутри, что в основном аналогично созданию отдельного индекса B-Tree для каждого поля (PostgreSQL также поддерживает слияние bitmapAnd и bitmapOr для нескольких индексов B-Tree).
Метод составного индекса GIN может удовлетворить вышеуказанные потребности, но когда объем данных или количество столбцов становится очень большим, размер индексов GIN также будет большим.
Хитрости оптимизации
Мы рекомендуем разделить индексы GIN на несколько таблиц (например, произвольное разделение или разделение на основе обязательных условий). Это не только уменьшает размер индексов GIN, но также позволяет использовать многотабличный параллелизм PostgreSQL 10 для повышения производительности запросов.
Параллельные вычисления PostgreSQL
PostgreSQL поддерживает как однотабличные многоядерные параллельные запросы, так и многотабличные параллельные запросы.
Однотабличный параллелизм означает, что один оператор SQL при обработке данных в одной таблице может использовать для вычислений несколько процессоров.
Многотабличный параллелизм означает, что когда оператор SQL включает обработку нескольких таблиц (например, APPEND SCAN), он может выполнять SCAN нескольких таблиц параллельно.
Параллелизм с несколькими таблицами был впервые включен в PG 10 для поддержки параллелизма сканирования с добавлением в PostgreSQL 10.
Способ 2: полнотекстовый поиск на уровне строк
Этот метод преобразует всю строку записей в большую строку, а затем создает для этой строки полнотекстовый индекс (PostgreSQL имеет встроенную функцию полнотекстового индексирования), который охватывает случаи, связанные с любыми комбинациями полей.
Этот метод работает для случаев без указанных столбцов, но с заданными критериями запроса.
Например, при поиске «Духи Dior» этот термин может встречаться в любых полях таблицы (таких как названия магазинов, названия продуктов и имена пользователей).
Метод 3: Фильтр Блума
Метод фильтра Блума имеет ограниченные эффекты и в настоящее время является функцией предварительного просмотра. Мы рекомендуем использовать его с осторожностью.
Способ 4: Массивы
Каждому пользователю соответствует несколько тегов, и продавцы могут фильтровать группы пользователей по комбинациям тегов. Это обычная практика для рекламных компаний.
Это в основном использует типы массивов и инвертированные индексы PostgreSQL и обеспечивает отличную производительность.
Но что делает этот метод быстрым?
Элементы ARRAY индексируются инвертированным образом. При запросе он выполняет фильтрацию BITMAP на уровне блоков по условиям запроса. Отфильтрованные данные попадают в небольшое количество блоков данных, которые перепроверяются для получения конечного результата.
Мультиполе, случайные комбинации критериев, тегирование людей за миллисекунды
На самом деле случай, упомянутый в начале этой статьи, очень похож на случай пометки людей в индустрии электронной коммерции. Таким образом, мы можем использовать этот метод в этом случае.
Что мы можем сделать?
1. Преобразование нескольких полей в массив
Сначала преобразуйте несколько полей в поле массива.
Например:
create table test(uid int8 primary key, tag1 int, tag2 text, tag3 int, tag4 text, tag5 timestamp, tag6 date, ...);is converted tocreate table test(uid int8 primary key, tag text[]);
Пример
1, 1, 'man', 1023, 'football', '2017-01-01 10:00:00', '1989-09-01'is converted to1, array['tag1:1', 'tag2:man', 'tag3:1023', 'tag4:football', 'tag5:...', tag6:...']
2. Каскадные значения (необязательно)
Если есть запросы, отличные от =, ‹ и › (для таких полей, как возраст, продажи и доход, может быть больше или меньше требований запроса диапазона), то нам нужно каскадировать значения соответствующих тегов.
3. Разделить таблицы (необязательно)
Таблицы разделены для обеспечения параллелизма и обеспечения надлежащего размера каждой таблицы.
Существует множество методов разделения таблиц, таких как случайное разделение и хеширование по UID.
После разделения таблиц просмотрите все таблицы разделов и агрегируйте результаты.
Разделение таблиц может выполняться локально или между разными базами данных. После разделения локальных таблиц мы получаем таблицы разделов. Разделение таблиц по разным базам данных включает в себя распределение и агрегирование данных.
Существует также множество методов распределения и агрегации данных между базами данных, таких как postgres_fdw + pg_pathman, plproxy и программная реализация.
4. Создание индексов GIN для массивов
Создайте индексы GIN для полей массива. Индексы GIN эквивалентны инвертированным индексам B-дерева, используя элементы массива в качестве ключей и номера строк в качестве значений.
Например, при поиске пользователя с определенным тегом мы можем получить номер строки таблицы HEAP из индекса GIN, а затем получить запись. Это очень быстро.
Если запрос использует комбинацию нескольких тегов, BITMAP и/или слияние выполняется внутри, условия будут отфильтрованы до уровня блока данных, записи будут получены из блоков данных, а окончательные результаты будут получены путем запроса условие ФИЛЬТР. Это тоже очень быстро.
5. Отметка людей ‹=› комбинированный запрос массива
После преобразования нескольких полей в массивы пометка людей упрощается до операций с массивами. Например
where tag1=? and tag2=? or tag3=?Процедура преобразования их в массивы выглядит следующим образом:
where arraycol @> array[tag1:?, tag2:?] or arraycol && [tag3:?]Запрос массива использует сканирование индекса GIN, что невероятно быстро.
Способ 5: растровое изображение
При этом используется метод BIT. Когда можно перечислить все значения свойств, скажем, можно перечислить все 1 миллион или любое другое количество значений, мы можем использовать этот метод для оптимизации приложения тегов.
Метод BIT требует в 25 раз меньше места, чем метод массива, сохраняя при этом стабильную производительность.
Однако метод BIT требует, чтобы данные записывались в объединенной форме, предпочтительно с использованием UDF. Фактический случай выглядит следующим образом (включая демонстрационный код для слияния данных).
Метод varbitx точно такой же, как и для растровой базы данных pilosa. Однако рекомендуется использовать PG, так как он имеет более мощные функции.
Метод 6: независимые индексы
Действие объединения PostgreSQL BitmapAnd и BitmapOr также может запускаться, когда мы используем независимые индексы для нескольких полей. Таким образом, запрос по-прежнему очень эффективен.
Например, у нас есть 31 поле и 100 миллионов записей. Поля вставляются случайным образом, а значения полей постепенно варьируются от 1000 до 1 миллиона.
postgres=# create table test(id serial8 primary key,c1 int, c2 int, c3 int, c4 int, c5 int,c6 int, c7 int, c8 int, c9 int, c10 int,c11 int, c12 int, c13 int, c14 int, c15 int,c16 int, c17 int, c18 int, c19 int, c20 int,c21 int, c22 int, c23 int, c24 int, c25 int,c26 int, c27 int, c28 int, c29 int, c30 int);create index idx_test_1 on test(c1);create index idx_test_2 on test(c2);create index idx_test_3 on test(c3);create index idx_test_4 on test(c4);create index idx_test_5 on test(c5);create index idx_test_6 on test(c6);create index idx_test_7 on test(c7);create index idx_test_8 on test(c8);create index idx_test_9 on test(c9);create index idx_test_10 on test(c10);create index idx_test_11 on test(c11);create index idx_test_12 on test(c12);create index idx_test_13 on test(c13);create index idx_test_14 on test(c14);create index idx_test_15 on test(c15);create index idx_test_16 on test(c16);create index idx_test_17 on test(c17);create index idx_test_18 on test(c18);create index idx_test_19 on test(c19);create index idx_test_20 on test(c20);create index idx_test_21 on test(c21);create index idx_test_22 on test(c22);create index idx_test_23 on test(c23);create index idx_test_24 on test(c24);create index idx_test_25 on test(c25);create index idx_test_26 on test(c26);create index idx_test_27 on test(c27);create index idx_test_28 on test(c28);create index idx_test_29 on test(c29);create index idx_test_30 on test(c30);postgres=# alter sequence test_id_seq cache 10000;Write 100 million test data recordsvi ins.sqlinsert into test (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30) select random()*1000,random()*2000,random()*3000,random()*4000,random()*5000,random()*6000,random()*7000,random()*8000,random()*9000,random()*10000,random()*10000,random()*20000,random()*30000,random()*40000,random()*50000,random()*60000,random()*70000,random()*80000,random()*90000,random()*100000,random()*100000,random()*200000,random()*300000,random()*400000,random()*500000,random()*600000,random()*700000,random()*800000,random()*900000,random()*1000000 from generate_series(1,1000);pgbench -M prepared -n -r -P 1 -f ./ins.sql -c 50 -j 50 -t 2000postgres=# select count(*) from test;count-----------100000000(1 row)While testing queries of random field combinations, the query conditions were merged internally using bitmapAnd/bitmapOr, delivering outstanding performance.postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test where c1=1 and c2=1 and c3=1 or (c10=1 and c11=1 or c12=1) and c14 between 1 and 1000000;QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------------------------------------------Bitmap Heap Scan on public.test (cost=1238.80..8607.84 rows=4887 width=128) (actual time=21.869..30.420 rows=4906 loops=1)Output: id, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30Recheck Cond: (((test.c3 = 1) AND (test.c2 = 1)) OR (((test.c10 = 1) AND (test.c11 = 1)) OR (test.c12 = 1)))Filter: (((test.c1 = 1) AND (test.c2 = 1) AND (test.c3 = 1)) OR ((((test.c10 = 1) AND (test.c11 = 1)) OR (test.c12 = 1)) AND (test.c14 >= 1) AND (test.c14 <= 1000000)))Rows Removed by Filter: 16Heap Blocks: exact=4915Buffers: shared hit=5230-> BitmapOr (cost=1238.80..1238.80 rows=4903 width=0) (actual time=20.931..20.931 rows=0 loops=1)Buffers: shared hit=315-> BitmapAnd (cost=947.23..947.23 rows=16 width=0) (actual time=17.602..17.602 rows=0 loops=1)Buffers: shared hit=235-> Bitmap Index Scan on idx_test_3 (cost=0.00..379.09 rows=32470 width=0) (actual time=7.965..7.965 rows=33036 loops=1)Index Cond: (test.c3 = 1)Buffers: shared hit=94-> Bitmap Index Scan on idx_test_2 (cost=0.00..565.45 rows=48517 width=0) (actual time=7.826..7.826 rows=50054 loops=1)Index Cond: (test.c2 = 1)Buffers: shared hit=141-> BitmapOr (cost=291.32..291.32 rows=4887 width=0) (actual time=3.076..3.076 rows=0 loops=1)Buffers: shared hit=80-> BitmapAnd (cost=231.88..231.88 rows=1 width=0) (actual time=2.769..2.769 rows=0 loops=1)Buffers: shared hit=62-> Bitmap Index Scan on idx_test_10 (cost=0.00..114.46 rows=9786 width=0) (actual time=1.104..1.104 rows=10085 loops=1)Index Cond: (test.c10 = 1)Buffers: shared hit=31-> Bitmap Index Scan on idx_test_11 (cost=0.00..114.72 rows=9821 width=0) (actual time=1.178..1.178 rows=9883 loops=1)Index Cond: (test.c11 = 1)Buffers: shared hit=31-> Bitmap Index Scan on idx_test_12 (cost=0.00..58.22 rows=4887 width=0) (actual time=0.307..0.307 rows=4904 loops=1)Index Cond: (test.c12 = 1)Buffers: shared hit=18Planning time: 0.460 msExecution time: 31.546 ms(32 rows)
Если у вас есть указанный фильтр, такой как идентификатор корпорации, вы можете разбить таблицу на основе идентификатора корпорации с помощью хеширования. Это позволяет выполнять точные запросы к соответствующему разделу и позволяет избежать запросов ко всем разделам.
Например:
create table tbl ( -- Master table...);create table tbl_0 ( -- Partition tablecrop_id int, -- Partition mod(corp_id, 128)=0....);....alter table tbl_0 inherit tbl; -- Sets the table inheritance relationship
Создание независимых индексов для каждого столбца увеличивает размер таблицы, которая может увеличиться до трех раз по сравнению с исходной отдельной таблицей (пространство занято значениями полей и номерами строк).
Он не подходит для случаев, требующих многопараллельной и частой записи, обновления и удаления больших объемов данных в режиме реального времени (подходит для случаев, когда записываются, обновляются и удаляются небольшие объемы данных). (Индексы вносят дополнительное потребление, что приводит к снижению производительности). (В данном случае у нас 31 поле и 31 индекс, производительность может упасть до 20 000 записей/с.)
Метод 7: метод удаления массива — оптимизация JOIN
Если некоторые из наших таблиц участвуют в коррелированных запросах 1-к-N, мы можем добавить поле массива в несколько таблиц, чтобы сохранить их корреляцию и избежать объединения.
Предположим, что в таблице A хранится информация о корпорациях и пользователях корпораций, а в таблице B хранится личная информация о пользователях; Таблица A и таблица B участвуют в корреляции 1-к-N через идентификатор пользователя (пользователь может существовать в нескольких корпорациях одновременно).
Возможно, нам потребуется найти некоторых пользователей (конкретные условия можно найти в таблице Б) в указанной корпорации. Для этого требуется соединение 1-к-N.
Пример
create table a(corp_id int, uid int, ....);create table b(uid int, ....);select b.* from a join b on (a.uid=b.uid and a.corp_id=? and b.xxx=xxx);
Этот тип запроса, кажется, в порядке, не так ли? Но что мы можем сделать, если мы используем секционированное хранилище или в случаях, когда существует ограничение на объединение таблицы A и таблицы B?
В большинстве случаев нам приходится извлекать все записи указанной корпорации из таблицы А, а затем импортировать UID в таблицу А для фильтрации.
-- Step 1select * from a where corp_id=?-- Step 2select * from b where b.xxx=xxx and b.uid in (.......)
Это утомительно. PostgreSQL может легко решить эту проблему.
Способ 1. Сам PostgreSQL не ограничивает объединение между базами данных с точки зрения сегментирования. Однако мы рекомендуем разбить таблицу на основе поля JOIN. (Если сегментирование не основано на поле JOIN, PG протолкнет условия, извлечет данные и JOIN, что прозрачно для бизнеса).
Метод 2: добавьте новое поле массива в таблицу B для хранения corp_id, и это позволит избежать присоединения.
create table a(corp_id int, uid int, ....);create table b(uid int, corp_id int[], ....); -- Adds the crop_id int[] array field, and maintains relationship between users and corporations-- Create GIN indexes on arrayscreate idx on b using gin (crop_id);-- Use the array intersection method to search for users that meet certain conditions within a corporationselect * from b where corp_id && array[?] and xxx=xxx;
Резюме
Благодаря мощным функциям PostgreSQL существует множество методов оптимизации для бизнес-сценария с тегами. Подытожим четыре основных метода:
- Составные индексы GIN. Мы можем просто создать составные индексы GIN для столбцов, которые необходимо запрашивать. PostgreSQL внутренне объединит несколько условий GIN, используя bitmapAnd и bitmapOr.
Это самый простой метод. Однако при большом объеме данных или большом количестве столбцов индексы GIN также будут большими. Индексы GIN большого размера могут привести к медленному созданию индексов, а также к медленному обслуживанию индексов в будущем.
Если вы используете этот метод, мы рекомендуем вам разбивать таблицу локально или между разными базами данных, чтобы уменьшить размер данных. каждой отдельной таблицы. (Мы рекомендуем вам контролировать количество записей в нескольких развернутых столбцах (например, 10 столбцов) примерно до 100 миллионов и контролировать количество записей в каждой отдельной таблице примерно до 10 миллионов) (эти числа даны на основе нашего опыта , и в будущем более совершенные аппаратные средства могут поддерживать большие числа)
Кроме того, мы предлагаем вам использовать функции быстрого обновления и отложенного слияния для GIN, чтобы ускорить операции вставки, удаления и обновления. - Независимые индексы B-дерева. Нам нужно создать отдельные индексы B-дерева (или другие индексы соответствующего типа, такие как BRIN, GIN, GIST, SP-GIST и HASH) для каждого из столбцов, которые необходимо запрашивать. PostgreSQL использует bitmapAnd и bitmapOr для внутреннего слияния результатов запроса для нескольких индексов.
Этот метод также очень прост. Если вы используете этот метод, мы рекомендуем разделить таблицу локально или между разными базами данных, чтобы уменьшить размер данных каждой отдельной таблицы. Мы рекомендуем вам контролировать количество записей примерно до 100 миллионов (это число дано на основе нашего опыта, и большее количество может поддерживаться более совершенным оборудованием в будущем). - Массив + ГИН. Это похоже на сценарий тегирования в индустрии электронной коммерции. Нам нужно использовать операторы массива CONTAINS и INTERSECT при выполнении запросов для реализации поиска по индексу.
Этот метод особенно подходит для случаев с созданными тегами (с использованием массивов PostgreSQL), где мы можем добиться тегирования, напрямую используя индексы массива и массив операции. - БИТ метод. Когда все теги могут быть пронумерованы, мы можем использовать теги в качестве ключей, а USERID — в качестве битов для хранения. Метод BIT требует в 25 раз меньше места, чем метод массива, сохраняя при этом стабильную эффективность.
Этот метод инвертирует пользователей и теги. Преимущество в том, что он поддерживает эффективную маркировку любых комбинаций. Однако это очень сложно и требует большой работы по разработке (для этого у нас есть демонстрация UDF).
Рекомендации
- Если вам нужно экономичное и эффективное решение, и вы не против вложить усилия в его реализацию, выберите метод BIT.
- Если у вас есть деньги и вы готовы вложить усилия в эффективное решение, выберите метод Array + GIN.
- Если у вас есть деньги и вы хотите эффективное решение, но не готовы вкладывать много усилий в его реализацию, выберите либо независимые индексы B-Tree, либо составные индексы GIN.
Ссылка:
