Фон
Примечание. Эта статья содержит ссылки на китайские статьи в блогах.
Точный маркетинг - горячая тема в рекламной индустрии. В одной из моих предыдущих статей описывается, как использовать массивы и индексы GIN в PostgreSQL, чтобы отмечать людей в реальном времени. (Источник из китайской статьи: Маркетинг триллионам людей за миллисекунды - дизайн базы данных для систем рекомендаций в реальном времени с триллионами пользовательских тегов)
Вышеупомянутые методы требуют определенной корректировки программы (конечно, если у пользователя уже есть стек технологий PostgreSQL, модификации будут минимальными). См. Пример ниже.
Предположим, что пользователь использует несколько столбцов для указания различных атрибутов, и каждый атрибут соответствует диапазону значений определенных тегов.
create table user_tags(uid int8 primary key, lab1 int, lab2 text, lab3 timestamp, lab4 text, lab5 interval, lab6 json);
Исходный тег пользователя и статистический запрос могут выглядеть следующим образом:
select * from user_tags where lab1 ? xxx and lab2 ? xxx or lab4 ? xxx;
select xx, count(*) from user_tags where lab1 ? xxx and lab2 ? xxx or lab4 ? xxx group by xxx;Поскольку диапазон значений может быть непрерывным, для использования метода Прогресс обусловлен ленивым дизайном базы данных для систем рекомендаций в реальном времени с триллионами пользовательских тегов нам необходимо создать библиотеку тегов, каскадировать данные и преобразовать запросы.
Непрерывные запросы, такие как между и, необходимо преобразовать в хеш-запросы. Хотя это усложняет программирование, оно также может обеспечить наиболее оптимальную производительность.
Предоставляет ли PostgreSQL лучший метод?
Ответ положительный. Решения создаются для ленивых, и тем самым ленивый движет вперед!
Вы можете обратиться к следующим статьям, чтобы узнать больше о решениях PostgreSQL.
1. Используйте растровые изображения, растровые изображения и любой индекс, чтобы отмечать людей.
2. Используйте расширение varbitx, чтобы отмечать людей.
Давайте посмотрим, как это работает в сценариях с непрерывными запросами, запросами на равенство и запросами с несколькими комбинациями условий.
Моделирование и тестирование
Создать таблицу тегов
postgres=# create table tbl_label(uid int primary key, c1 int, c2 text, c3 numeric, c4 timestamp, c5 interval, c6 int);
CREATE TABLE
Time: 31.145 msВставить пакет данных
postgres=# insert into tbl_label select id,
random()*10000, md5(random()::text),
10000*random(), clock_timestamp(),
(random()*1000::int||' hour')::interval,
random()*99999
from generate_series(1,10000000) t(id);
INSERT 0 10000000Модель данных
postgres=# select * from tbl_label limit 10;
uid | c1 | c2 | c3 | c4 | c5 | c6
-----+------+----------------------------------+------------------+----------------------------+------------------+-------
1 | 1692 | 505662aa4a6b33e1775cea660063ba58 | 9761.26249413937 | 2017-06-12 18:38:57.515097 | 322:06:55.266882 | 67699
2 | 8611 | a60d564b7f4d58029dfd5e16f0457305 | 1003.07232700288 | 2017-06-12 18:38:57.515282 | 780:59:39.081975 | 89283
3 | 290 | f226358e08372d4b79c8ecdd27172244 | 8240.20517989993 | 2017-06-12 18:38:57.515296 | 261:29:59.658099 | 87975
4 | 7829 | 32bc5d97731ddaf2c1630218e43d1e85 | 9061.87939457595 | 2017-06-12 18:38:57.515303 | 760:47:18.267513 | 76409
5 | 7735 | 3813b4bcdaadc21a55da143f6aceeac9 | 6651.74870751798 | 2017-06-12 18:38:57.515309 | 512:45:50.116217 | 11252
6 | 9945 | ff72917169cdea9225e429e438f22586 | 2114.50539063662 | 2017-06-12 18:38:57.515316 | 63:30:34.15014 | 33288
7 | 9144 | 7cf4067f22c5edbb1fc4e08ecee7242c | 5662.74457611144 | 2017-06-12 18:38:57.515322 | 890:30:28.360096 | 55484
8 | 2433 | 8ac9732bec2b1c175483c16e82467653 | 9184.17258188128 | 2017-06-12 18:38:57.515328 | 343:34:40.88581 | 53265
9 | 8113 | 2dd724e82dc7c2a15dfda45f6a41cd53 | 5094.92502082139 | 2017-06-12 18:38:57.515334 | 635:16:39.096908 | 63410
10 | 3893 | b8abdb00228f09efb04c1e2a8a022c22 | 6397.02362008393 | 2017-06-12 18:38:57.51534 | 295:26:24.752753 | 17894
(10 rows)Анализировать статистику таблицы
postgres=# analyze tbl_label ;
ANALYZEПроверьте степень хеширования для каждого столбца
Description of n_distinct
-1 indicates that each row in the column is unique.
>=1 indicates the number of unique values in the column
<1 indicates the number/total number of unique values in the column
Description of correlation
It indicates the linear correlation between this column and the data stack storage, where 1 indicates perfect positive correlation. As the value gets closer to 0, the data distribution is more discrete. <0 indicates an inverse correlation.
Uid is auto-increment, c4 increases by time, so the correlation is 1, a perfect positive correlation.
postgres=# select tablename,attname,n_distinct,correlation from pg_stats where tablename='tbl_label';
tablename | attname | n_distinct | correlation
-----------+---------+------------+-------------
tbl_label | uid | -1 | 1
tbl_label | c1 | 10018 | 0.00431651
tbl_label | c2 | -0.957505 | -0.00796595
tbl_label | c3 | -1 | 0.00308372
tbl_label | c4 | -1 | 1
tbl_label | c5 | -1 | 0.000382809
tbl_label | c6 | 100688 | 0.00156045
(7 rows)Основываясь на приведенной выше статистической информации, мы должны создать индексы btree для уникального столбца и создать индексы gin (инвертированные индексы) для свободного столбца, чтобы добиться наилучшего эффекта.
Чтобы общие типы поддерживали джин, нам нужно создать расширение btree_gin.
postgres=# create extension btree_gin;
CREATE EXTENSION
Create compound gin indexes for c1 and c6.
postgres=# set maintenance_work_mem ='32GB';
SET
Time: 0.168 ms
postgres=# create index idx_tbl_label_1 on tbl_label using gin(c1,c6);
CREATE INDEX
Time: 1504.542 ms (00:01.505)Тестирование запросов показывает, что запросы с любой комбинацией c1 и c6 очень эффективны.
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_label where c1 between 1 and 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_label (cost=125.76..8759.97 rows=10074 width=80) (actual time=40.856..50.480 rows=9922 loops=1)
Output: uid, c1, c2, c3, c4, c5, c6
Recheck Cond: ((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100))
Heap Blocks: exact=7222
Buffers: shared hit=7982
-> Bitmap Index Scan on idx_tbl_label_1 (cost=0.00..123.24 rows=10074 width=0) (actual time=39.773..39.773 rows=9922 loops=1)
Index Cond: ((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100))
Buffers: shared hit=760
Planning time: 0.105 ms
Execution time: 51.043 ms
(10 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_label where c1 between 1 and 100 or c6=100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_label (cost=134.36..8799.70 rows=10085 width=80) (actual time=41.133..50.187 rows=9932 loops=1)
Output: uid, c1, c2, c3, c4, c5, c6
Recheck Cond: (((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100)) OR (tbl_label.c6 = 100))
Heap Blocks: exact=7228
Buffers: shared hit=7992
-> BitmapOr (cost=134.36..134.36 rows=10085 width=0) (actual time=40.045..40.045 rows=0 loops=1)
Buffers: shared hit=764
-> Bitmap Index Scan on idx_tbl_label_1 (cost=0.00..123.24 rows=10074 width=0) (actual time=40.031..40.031 rows=9922 loops=1)
Index Cond: ((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100))
Buffers: shared hit=760
-> Bitmap Index Scan on idx_tbl_label_1 (cost=0.00..6.08 rows=11 width=0) (actual time=0.012..0.012 rows=10 loops=1)
Index Cond: (tbl_label.c6 = 100)
Buffers: shared hit=4
Planning time: 0.125 ms
Execution time: 50.758 ms
(15 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_label where c1 between 1 and 100 and c6=100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_label (cost=22.50..24.02 rows=1 width=80) (actual time=36.193..36.193 rows=0 loops=1)
Output: uid, c1, c2, c3, c4, c5, c6
Recheck Cond: ((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100) AND (tbl_label.c6 = 100))
Buffers: shared hit=763
-> Bitmap Index Scan on idx_tbl_label_1 (cost=0.00..22.50 rows=1 width=0) (actual time=36.190..36.190 rows=0 loops=1)
Index Cond: ((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100) AND (tbl_label.c6 = 100))
Buffers: shared hit=763
Planning time: 0.115 ms
Execution time: 36.226 ms
(9 rows)Итак, давайте создадим индексы btree для других столбцов. Значения n_distinct в других столбцах указывают на то, что значения в этих столбцах в основном уникальны, поэтому мы собираемся создать индексы btree, которые позволяют нам точно найти нужное значение.
Создайте индексы brin для столбцов с хорошей линейной корреляцией. Принципы и выбор индексов будут подробно описаны позже.
postgres=# create index idx_tbl_label2 on tbl_label using btree(c2);
CREATE INDEX
Time: 1388.756 ms (00:01.389)
postgres=# create index idx_tbl_label3 on tbl_label using btree(c3);
CREATE INDEX
Time: 1028.865 ms (00:01.029)Запросы на комбинации нескольких столбцов очень эффективны.
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_label where c1 between 1 and 100 and c6=100 and c2='abc';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_tbl_label2 on public.tbl_label (cost=0.42..3.45 rows=1 width=80) (actual time=0.032..0.032 rows=0 loops=1)
Output: uid, c1, c2, c3, c4, c5, c6
Index Cond: (tbl_label.c2 = 'abc'::text)
Filter: ((tbl_label.c1 >= 1) AND (tbl_label.c1 <= 100) AND (tbl_label.c6 = 100))
Buffers: shared read=3
Planning time: 0.248 ms
Execution time: 0.056 ms
(7 rows)Производительность запросов при произвольных условиях может быть значительно улучшена за счет фильтрации данных с помощью bitmapAnd и bitmapOr, а также за счет использования нескольких индексов. Принципы работы следующие:
Как выбрать индекс? Это будет подробно описано позже.
Пояснения
В приведенном выше примере я использовал составные индексы GIN с несколькими столбцами, но на самом деле есть другой способ решения проблемы. Мы можем преобразовать несколько столбцов в массив и создать индексы массива (индексы выражений PostgreSQL)
1. Как можно преобразовать несколько столбцов в массив?
postgres=# create or replace function to_array(VARIADIC anyarray) returns anyarray as $$
select $1;
$$ language sql strict immutable;
CREATE FUNCTION
Example
postgres=# select to_array('a'::text,'b','c');
to_array
----------
{a,b,c}
(1 row)
postgres=# select to_array(now(),clock_timestamp());
to_array
-------------------------------------------------------------------
{"2017-06-12 17:50:47.992274+08","2017-06-12 17:50:47.992489+08"}
(1 row)
postgres=# select to_array(1,2,3);
to_array
----------
{1,2,3}
(1 row)2. индексы выражения массива
Пример
create index idx_tbl_label_combin on tbl_label using gin (to_array(c1,c6));
When we have a set of different types of columns, we can convert them into a single uniform type and then create expression indexes. To convert the columns, we may need to use the immutable function. If you don't have the immutable function, you can easily create one.
postgres=# create index idx_tbl_label_combin1 on tbl_label using gin (to_array('c1:'||c1,'c6:'||c6));3.Как попасть в индексы выражений массива?
Когда условие запроса согласуется с выражением индекса, оно выполняется.
Пример
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_label where to_array(c1,c6) && array[1,2];
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_label (cost=840.56..86397.30 rows=99750 width=80) (actual time=0.777..4.030 rows=2254 loops=1)
Output: uid, c1, c2, c3, c4, c5, c6
Recheck Cond: (ARRAY[tbl_label.c1, tbl_label.c6] && '{1,2}'::integer[])
Heap Blocks: exact=2242
Buffers: shared hit=2251
-> Bitmap Index Scan on idx_tbl_label_combin (cost=0.00..815.62 rows=99750 width=0) (actual time=0.465..0.465 rows=2254 loops=1)
Index Cond: (ARRAY[tbl_label.c1, tbl_label.c6] && '{1,2}'::integer[])
Buffers: shared hit=9
Planning time: 0.361 ms
Execution time: 4.176 ms
(10 rows)
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from tbl_label where to_array('c1:'||c1,'c6:'||c6) && array['c1:1'];
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_label (cost=422.00..54015.43 rows=50000 width=80) (actual time=0.331..1.888 rows=1021 loops=1)
Output: uid, c1, c2, c3, c4, c5, c6
Recheck Cond: (ARRAY[('c1:'::text || (tbl_label.c1)::text), ('c6:'::text || (tbl_label.c6)::text)] && '{c1:1}'::text[])
Heap Blocks: exact=1019
Buffers: shared hit=1024
-> Bitmap Index Scan on idx_tbl_label_combin1 (cost=0.00..409.50 rows=50000 width=0) (actual time=0.195..0.195 rows=1021 loops=1)
Index Cond: (ARRAY[('c1:'::text || (tbl_label.c1)::text), ('c6:'::text || (tbl_label.c6)::text)] && '{c1:1}'::text[])
Buffers: shared hit=5
Planning time: 0.173 ms
Execution time: 1.972 ms
(10 rows)Резюме
1.Когда выбрать btree?
Индексы B-дерева подходят для столбцов с лучшими свойствами выбора (значение n_distinct очень велико, или = -1), или имеется высокая доля уникальных значений.
2.Когда выбирать джин?
В отличие от индексов btree, индексы GIN более эффективны, если столбцы имеют плохие свойства выбора.
Кроме того, поскольку индексы GIN являются инвертированными индексами, они очень подходят для запросов по комбинациям многозначных элементов, например массив, полнотекстовый поиск, токен и т. д.
Поскольку интерфейсы для индексов GIN открыты, вы можете настроить индексы GIN в соответствии с индивидуальными характеристиками ваших данных. Он поддерживает больше типов данных, например, сходство между изображениями, схожесть текста и т. Д.
3.Когда выбирать суть?
GIST - это обобщенный индексный интерфейс, принадлежащий PostgreSQL. Он подходит для различных типов данных, особенно для разнородных типов, например индекс геометрии, пространственный индекс, индекс диапазона и т. д.
См. Следующие принципы использования индексов GIST:
4.Когда выбирать хэш?
Хеш-индексы полезны, если вы имеете дело только с запросами на равенство, а длина индексированных столбцов очень велика, более 1/3 длины блока базы данных. Хеш-индексы PostgreSQL 10 будут генерировать WAL. Для обеспечения надежности он также поддерживает потоковую репликацию.
Хеш-индексы не рекомендуются для версий PostgreSQL ранее, чем версия 10, так как после сбоя требуется перестроение, а предыдущие версии не поддерживают потоковую репликацию.
5.Когда выбирать рассол?
Индексы BRIN могут использоваться, когда данные и хранилище стека линейно коррелированы.
Индекс BRIN - это индекс диапазона блоков. Он хранит элементарную информацию (максимальное значение, минимальное значение, среднее значение, нулевое отношение, COUNT и т. Д.) Каждого блока данных (или последовательных блоков данных).
Он особенно подходит для сканирования диапазона.
Какие запросы поддерживаются каждым типом индекса?
1.btree
Поддерживает сортировку, «› = »,« ‹=», «=», «in», «› »,« ‹» и другие запросы.
2.HASH
Поддерживает запросы со знаком «=».
3.GIN
Поддерживает разные запросы запросов на основе разных типов данных.
Например, если данные состоят из пересечения массивов и запросы «Содержит» являются наиболее подходящими.
4.GIST
Поддерживает разные запросы запросов на основе разных типов данных.
Например, если данные пространственные, соответствующие типы запросов включают расстояние, KNN, «Содержит», пересечение, слева и справа.
5.BRIN
Поддерживает запросы диапазона и запросы со знаком «=».
Как оптимизировать эффективность индекса
Выше объясняется, как выбрать тип индекса, но не упоминается, как оптимизировать ваши индексы. В действительности эффективность индекса во многом зависит от распределения данных.
Например
Следовательно, эффективность наших индексных запросов можно значительно повысить, если наши данные будут перераспределены в соответствии с характеристиками сканирования индекса.
Например, сканирование индекса по битовой карте (считываемое в порядке идентификаторов блоков) может использоваться в PostgreSQL для уменьшения дискретного ввода-вывода.
1. оптимизация распределения данных btree
Лучшая линейная корреляция делает сканирование и возврат нескольких записей данных более эффективным.
2.Оптимизация распределения хеш-данных
Лучшая линейная корреляция делает сканирование и возврат нескольких записей данных более эффективным.
3.Оптимизация распределения данных по джину
Для обычных данных лучшая линейная корреляция делает сканирование и возврат нескольких записей данных более эффективным.
Для многозначных данных (например, массив, полнотекстовый поиск, ТОКЕНЫ) запросы тем эффективнее, чем больше сконцентрировано элементов (например, при анализе концентрации элементов, где ось x - это номер строки, а ось y - значение элемента. , тем больше концентрируются данные).
Обычно достичь концентрации элементов непросто, но у нас есть несколько методов для агрегирования данных. 1. Сортировка и реструктуризация данных в зависимости от частоты элементов. Когда пользователи ищут часто встречающиеся термины, нужно искать меньшее количество блоков и сокращается объем ввода-вывода. 2. Сортировка на основе значения (количества найденных элементов x количество совпадений) и агрегирование данных в порядке убывания от элемента, который занимает первое место.
(Вышеупомянутые методы могут быть немного сложными для понимания. Позже я опубликую статью, в которой конкретно рассматривается оптимизация реструктуризации данных GIN.)
4. оптимизация распределения основных данных
Для обычных данных лучшая линейная корреляция делает сканирование и возврат нескольких записей данных более эффективным.
Для пространственных данных запросы тем эффективнее, чем больше сконцентрировано элементов (например, когда данные последовательно распределяются с помощью геохеша).
5.Оптимизация распределения данных по рассолу
Лучшая линейная корреляция делает сканирование и возврат нескольких записей данных более эффективным.
6.Оптимизация распределения данных для составных индексов с несколькими столбцами
Для составных индексов с несколькими столбцами эффективность зависит от типа индекса, и применимы вышеуказанные требования.
Еще одно условие: чем лучше линейная корреляция между несколькими столбцами, тем выше эффективность.
Чтобы узнать, как рассчитать линейную корреляцию между несколькими столбцами, см.
Используйте PostgreSQL для вычисления линейной корреляции между полями любого типа
Распределение данных имеет еще одно преимущество в том, что оно значительно увеличивает степень сжатия для хранения столбцов.
Использованная литература:
Применение одноколоночного индекса концентрации PostgreSQL GIN
PostgreSQL - принципы индексации GIN
Ссылка:
