Composite primary key
3,147 views
Skip to first unread message
Yegor Andreenko
Aug 30, 2016, 8:01:59 AM8/30/16
to ClickHouse
Можно больше документации по устройству primary keys.
Например:
1. сколько колонок может входить в ключ и как данные располагаются при этом? есть ли теоретический/практический предел? Вроде как структура сегментов не поменялась на диске.
2. могут ли входить колонки, отсутствующие в в некоторых записях? По тестам могут.
Например:
1. сколько колонок может входить в ключ и как данные располагаются при этом? есть ли теоретический/практический предел? Вроде как структура сегментов не поменялась на диске.
2. могут ли входить колонки, отсутствующие в в некоторых записях? По тестам могут.
man...@gmail.com
Aug 30, 2016, 10:00:57 PM8/30/16
to ClickHouse
Данные в таблице типа MergeTree хранятся в виде множества "кусков" (parts).
В среднем, поддерживается небольшое множество кусков (единицы-десятки в месяц).
В каждом куске, данные хранятся лексикографически упорядоченными по первичному ключу.
Для примера, если наш первичный ключ - (CounterID, Date),
то данные в куске будут лежать в порядке CounterID; а для каждого CounterID - в порядке Date.
Структура данных, которую образует первичный ключ, представляет собой массив "засечек"
- так называются значения первичного ключа каждые index_granularity строк.
index_granularity - параметр движка MergeTree, обычно 8192.
Мы говорим, что первичный ключ - разреженный индекс в сортированных данных.
Нарисуем это на примере одного куска:
Данные: [-------------------------------------------------------------------------]
CounterID: [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date: [1111111222222233331233211111222222333211111112122222223111112223311122333]
Засечки: | | | | | | | | | | |
a,1 a,2 a,3 b,3 e,2 e,3 g,1 h,2 i,1 i,3 l,3
Номер засечки: 0 1 2 3 4 5 6 7 8 9 10
Засечки удобно представлять себе как сантиметровые засечки на линейке.
Первичный ключ позволяет эффективно читать данные по диапазонам.
Для запроса, ClickHouse выбирает множество диапазонов засечек, которые могут содержать искомые данные.
Так,
Если в запросе написано CounterID IN ('a', 'h')
то сервер будет читать данные в диапазонах засечек [0, 3) и [6, 8).
Если в запросе написано CounterID IN ('a', 'h') AND Date = 3
то сервер будет читать данные в диапазонах засечек [1, 3) и [7, 8).
Иногда первичный ключ работает даже если указано условие только на второй столбец первичного ключа:
Если в запросе написано Date = 3
то сервер будет читать данные в диапазонах засечек [1, 10).
(в данном примере это все засечки кроме засечки 0 - то есть, 90% данных;
то есть, в данном примере индекс не эффективен, хотя и позволяет всё-таки пропустить часть данных)
С другой стороны, если бы было ещё больше данных для одного CounterID, то индекс позволил бы пропустить более крупные диапазоны Date в них.
В любом случае, использование индекса никогда не может быть менее эффективно, чем full scan.
Разреженный индекс допускает чтение лишних строк: при чтении одного диапазона первичного ключа, может быть прочитано до index_granularity * 2 лишних строк в каждом куске. Бояться этого и уменьшать index_granularity не стоит. ClickHouse ориентирован на то, чтобы эффективно обрабатывать данные большими пачками строк, поэтому обработать немножечко лишних строк не помешает. index_granularity = 8192 - хорошее значение для большинства случаев.
Разреженность индекса позволяет работать даже при очень большом количестве строк в таблицах. Такой индекс всегда помещается в оперативку.
Первичный ключ не является уникальным. Можно вставить много строк с одним значением первичного ключа.
В первичный ключ могут входить также и функциональные выражения. Пример: (CounterID, EventDate, intHash32(UserID))
Это используется для того, чтобы для каждого CounterID, EventDate случайно перемешать данные по UserID, что в свою очередь, используется для сэмплирования.
Рассмотрим в итоге, на что влияет выбор первичного ключа.
1. Самое важное и очевидное: первичный ключ позволяет читать меньше данных при запросах.
Как было видно из примеров выше, указывать много элементов первичного ключа обычно не имеет смысла для этой цели.
Пусть у вас есть первичный ключ (a, b). Тогда добавление ещё одного столбца c: (a, b, c) имеет смысл только тогда, когда выполнены оба условия:
- когда у вас есть запросы с условием на этот столбец;
- когда часто встречаются достаточно длинные (в несколько раз больше index_granularity) диапазоны данных с одинаковыми значениями (a, b).
Иначе говоря, когда добавление ещё одного столбца, позволит пропускать достаточно длинные диапазоны в данных.
2. Данные упорядочены по первичному ключу, из-за чего они лучше сжимаются. Изредка бывает, что при добавлении ещё одного поля в первичный ключ, данные будут чуть лучше сжиматься.
3. При использовании вариантов MergeTree с дополнительной логикой при слиянии: CollapsingMergeTree, SummingMergeTree и т. п., первичный ключ влияет на слияние данных. Из-за этого может потребоваться иметь много полей в первичном ключе, даже когда они не нужны для пункта 1.
Количество столбцов в первичном ключе явно не ограничено. Длинный первичный ключ обычно бесполезен.
На практике максимум, который я видел - около 20 столбцов (для SummingMergeTree), но я такой вариант не рекомендую.
Длинный первичный ключ будет негативно влиять на производительность вставки и потребление памяти.
На производительность SELECT-ов негативного влияния от лишних столбцов в первичном ключе нет.
При вставке, отсутствующие значения столбцов (в том числе, входящих в первичный ключ), заменяются на значения по-умолчанию и пишутся в таблицу.
По ним же строится первичный ключ, как обычно. За счёт того, что он не уникален, в этом нет ничего особенного.
В среднем, поддерживается небольшое множество кусков (единицы-десятки в месяц).
В каждом куске, данные хранятся лексикографически упорядоченными по первичному ключу.
Для примера, если наш первичный ключ - (CounterID, Date),
то данные в куске будут лежать в порядке CounterID; а для каждого CounterID - в порядке Date.
Структура данных, которую образует первичный ключ, представляет собой массив "засечек"
- так называются значения первичного ключа каждые index_granularity строк.
index_granularity - параметр движка MergeTree, обычно 8192.
Мы говорим, что первичный ключ - разреженный индекс в сортированных данных.
Нарисуем это на примере одного куска:
Данные: [-------------------------------------------------------------------------]
CounterID: [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date: [1111111222222233331233211111222222333211111112122222223111112223311122333]
Засечки: | | | | | | | | | | |
a,1 a,2 a,3 b,3 e,2 e,3 g,1 h,2 i,1 i,3 l,3
Номер засечки: 0 1 2 3 4 5 6 7 8 9 10
Засечки удобно представлять себе как сантиметровые засечки на линейке.
Первичный ключ позволяет эффективно читать данные по диапазонам.
Для запроса, ClickHouse выбирает множество диапазонов засечек, которые могут содержать искомые данные.
Так,
Если в запросе написано CounterID IN ('a', 'h')
то сервер будет читать данные в диапазонах засечек [0, 3) и [6, 8).
Если в запросе написано CounterID IN ('a', 'h') AND Date = 3
то сервер будет читать данные в диапазонах засечек [1, 3) и [7, 8).
Иногда первичный ключ работает даже если указано условие только на второй столбец первичного ключа:
Если в запросе написано Date = 3
то сервер будет читать данные в диапазонах засечек [1, 10).
(в данном примере это все засечки кроме засечки 0 - то есть, 90% данных;
то есть, в данном примере индекс не эффективен, хотя и позволяет всё-таки пропустить часть данных)
С другой стороны, если бы было ещё больше данных для одного CounterID, то индекс позволил бы пропустить более крупные диапазоны Date в них.
В любом случае, использование индекса никогда не может быть менее эффективно, чем full scan.
Разреженный индекс допускает чтение лишних строк: при чтении одного диапазона первичного ключа, может быть прочитано до index_granularity * 2 лишних строк в каждом куске. Бояться этого и уменьшать index_granularity не стоит. ClickHouse ориентирован на то, чтобы эффективно обрабатывать данные большими пачками строк, поэтому обработать немножечко лишних строк не помешает. index_granularity = 8192 - хорошее значение для большинства случаев.
Разреженность индекса позволяет работать даже при очень большом количестве строк в таблицах. Такой индекс всегда помещается в оперативку.
Первичный ключ не является уникальным. Можно вставить много строк с одним значением первичного ключа.
В первичный ключ могут входить также и функциональные выражения. Пример: (CounterID, EventDate, intHash32(UserID))
Это используется для того, чтобы для каждого CounterID, EventDate случайно перемешать данные по UserID, что в свою очередь, используется для сэмплирования.
Рассмотрим в итоге, на что влияет выбор первичного ключа.
1. Самое важное и очевидное: первичный ключ позволяет читать меньше данных при запросах.
Как было видно из примеров выше, указывать много элементов первичного ключа обычно не имеет смысла для этой цели.
Пусть у вас есть первичный ключ (a, b). Тогда добавление ещё одного столбца c: (a, b, c) имеет смысл только тогда, когда выполнены оба условия:
- когда у вас есть запросы с условием на этот столбец;
- когда часто встречаются достаточно длинные (в несколько раз больше index_granularity) диапазоны данных с одинаковыми значениями (a, b).
Иначе говоря, когда добавление ещё одного столбца, позволит пропускать достаточно длинные диапазоны в данных.
2. Данные упорядочены по первичному ключу, из-за чего они лучше сжимаются. Изредка бывает, что при добавлении ещё одного поля в первичный ключ, данные будут чуть лучше сжиматься.
3. При использовании вариантов MergeTree с дополнительной логикой при слиянии: CollapsingMergeTree, SummingMergeTree и т. п., первичный ключ влияет на слияние данных. Из-за этого может потребоваться иметь много полей в первичном ключе, даже когда они не нужны для пункта 1.
Количество столбцов в первичном ключе явно не ограничено. Длинный первичный ключ обычно бесполезен.
На практике максимум, который я видел - около 20 столбцов (для SummingMergeTree), но я такой вариант не рекомендую.
Длинный первичный ключ будет негативно влиять на производительность вставки и потребление памяти.
На производительность SELECT-ов негативного влияния от лишних столбцов в первичном ключе нет.
При вставке, отсутствующие значения столбцов (в том числе, входящих в первичный ключ), заменяются на значения по-умолчанию и пишутся в таблицу.
По ним же строится первичный ключ, как обычно. За счёт того, что он не уникален, в этом нет ничего особенного.
Yegor Andreenko
Sep 4, 2016, 9:42:38 AM9/4/16
to ClickHouse
Спасибо за описание.
Yegor Andreenko
Feb 14, 2017, 5:49:06 PM2/14/17
to ClickHouse
Translated answer to English and published https://medium.com/@f1yegor/clickhouse-primary-keys-2cf2a45d7324#.by9bxn4yj
Reply all
Reply to author
Forward
0 new messages