تصميم المخطط

تحسين مخطط ClickHouse لأداء الاستعلامات

يُعد فهم التصميم الفعّال للمخطط أمرًا أساسيًا لتحسين أداء ClickHouse، ويتضمن اختيارات تنطوي غالبًا على مفاضلات، إذ يعتمد النهج الأمثل على الاستعلامات التي تتم خدمتها، إلى جانب عوامل مثل معدل تحديث البيانات، ومتطلبات الكمون، وحجم البيانات. يقدّم هذا الدليل لمحة عامة عن أفضل ممارسات تصميم المخطط وتقنيات نمذجة البيانات لتحسين أداء ClickHouse.

​

مجموعة بيانات Stack Overflow

في الأمثلة الواردة في هذا الدليل، نستخدم مجموعة فرعية من مجموعة بيانات Stack Overflow. تضم هذه المجموعة كل منشور وتصويت ومستخدم وتعليق وشارة ظهرت على Stack Overflow من عام 2008 حتى أبريل 2024. وهذه البيانات متاحة بتنسيق Parquet باستخدام المخططات أدناه ضمن حاوية S3 ‏s3://datasets-documentation/stackoverflow/parquet/:

المفاتيح الأساسية والعلاقات الموضحة ليست مفروضة من خلال القيود (إذ إن Parquet تنسيق ملفات وليس تنسيق جداول)، وإنما تشير فقط إلى كيفية ارتباط البيانات والمفاتيح الفريدة التي تتضمنها.

تحتوي مجموعة بيانات Stack Overflow على عدد من الجداول المترابطة. في أي مهمة لنمذجة البيانات، نوصي المستخدمين بالتركيز أولًا على تحميل جدولهم الأساسي. وليس بالضرورة أن يكون هذا أكبر جدول، بل الجدول الذي تتوقع أن تَرِد عليه معظم الاستعلامات التحليلية. يتيح لك ذلك التعرّف على مفاهيم ClickHouse الأساسية وأنواعها، وهو أمر مهم خاصةً إذا كنت قادمًا من خلفية يغلب عليها OLTP. وقد يتطلب هذا الجدول إعادة نمذجة مع إضافة جداول أخرى للاستفادة الكاملة من ميزات ClickHouse وتحقيق أفضل أداء.

المخطط أعلاه غير مثالي عمدًا لأغراض هذا الدليل.

​

إنشاء المخطط الأولي

نظرًا لأن جدول posts سيكون الهدف لمعظم استعلامات التحليلات، فإننا نركز على إنشاء مخطط لهذا الجدول. هذه البيانات متاحة في حاوية S3 العامة s3://datasets-documentation/stackoverflow/parquet/posts/*.parquet، مع ملف لكل سنة.

يمثّل تحميل البيانات من S3 بتنسيق Parquet الطريقة الأكثر شيوعًا والمفضلة لتحميل البيانات إلى ClickHouse. وقد صُمّم ClickHouse لمعالجة Parquet بكفاءة، ويمكنه نظريًا قراءة وإدراج عشرات الملايين من الصفوف من S3 في الثانية الواحدة.

يوفّر ClickHouse إمكانية استدلال المخطط لتحديد الأنواع تلقائيًا لمجموعة بيانات. وهذا مدعوم لجميع تنسيقات البيانات، بما في ذلك Parquet. ويمكننا الاستفادة من هذه الميزة لتحديد أنواع ClickHouse للبيانات عبر دالة الجدول S3 والأمر DESCRIBE. لاحظ أدناه أننا نستخدم نمط glob ‏*.parquet لقراءة جميع الملفات في المجلد stackoverflow/parquet/posts.

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')
SETTINGS describe_compact_output = 1

┌─name──────────────────┬─type───────────────────────────┐
│ Id                    │ Nullable(Int64)               │
│ PostTypeId            │ Nullable(Int64)               │
│ AcceptedAnswerId      │ Nullable(Int64)               │
│ CreationDate          │ Nullable(DateTime64(3, 'UTC')) │
│ Score                 │ Nullable(Int64)               │
│ ViewCount             │ Nullable(Int64)               │
│ Body                  │ Nullable(String)              │
│ OwnerUserId           │ Nullable(Int64)               │
│ OwnerDisplayName      │ Nullable(String)              │
│ LastEditorUserId      │ Nullable(Int64)               │
│ LastEditorDisplayName │ Nullable(String)              │
│ LastEditDate          │ Nullable(DateTime64(3, 'UTC')) │
│ LastActivityDate      │ Nullable(DateTime64(3, 'UTC')) │
│ Title                 │ Nullable(String)              │
│ Tags                  │ Nullable(String)              │
│ AnswerCount           │ Nullable(Int64)               │
│ CommentCount          │ Nullable(Int64)               │
│ FavoriteCount         │ Nullable(Int64)               │
│ ContentLicense        │ Nullable(String)              │
│ ParentId              │ Nullable(String)              │
│ CommunityOwnedDate    │ Nullable(DateTime64(3, 'UTC')) │
│ ClosedDate            │ Nullable(DateTime64(3, 'UTC')) │
└───────────────────────┴────────────────────────────────┘

تتيح دالة الجدول S3 الاستعلام عن البيانات الموجودة في S3 مباشرةً من ClickHouse دون الحاجة إلى استيرادها أولًا. هذه الدالة متوافقة مع جميع تنسيقات الملفات التي يدعمها ClickHouse.

يوفّر لنا هذا مخططًا أوليًا غير محسّن. افتراضيًا، يعيّن ClickHouse هذه الأنواع إلى أنواع Nullable المكافئة. يمكننا إنشاء جدول ClickHouse باستخدام هذه الأنواع عبر أمر CREATE EMPTY AS SELECT بسيط.

CREATE TABLE posts
ENGINE = MergeTree
ORDER BY () EMPTY AS
SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

بضع نقاط مهمة:

جدول posts الخاص بنا فارغ بعد تشغيل هذا الأمر. لم يتم تحميل أي بيانات. لقد حددنا MergeTree كمحرك للجدول. ويُعد MergeTree أكثر محركات الجداول شيوعًا في ClickHouse، ومن المرجح أنك ستستخدمه. فهو بمثابة الأداة متعددة الاستخدامات في مجموعة أدوات ClickHouse، إذ يمكنه التعامل مع بيتابايت من البيانات، ويلبّي معظم حالات الاستخدام التحليلية. وتوجد محركات جداول أخرى لحالات استخدام مثل CDC التي تحتاج إلى دعم تحديثات فعّالة.

تعني العبارة ORDER BY () أنه ليس لدينا فهرس، وبشكل أدق لا يوجد أي ترتيب في بياناتنا. سنتناول هذا بمزيد من التفصيل لاحقًا. أما الآن، فيكفي أن تعرف أن جميع الاستعلامات ستتطلب مسحًا خطيًا.

لتأكيد أنه تم إنشاء الجدول:

SHOW CREATE TABLE posts

CREATE TABLE posts
(
        `Id` Nullable(Int64),
        `PostTypeId` Nullable(Int64),
        `AcceptedAnswerId` Nullable(Int64),
        `CreationDate` Nullable(DateTime64(3, 'UTC')),
        `Score` Nullable(Int64),
        `ViewCount` Nullable(Int64),
        `Body` Nullable(String),
        `OwnerUserId` Nullable(Int64),
        `OwnerDisplayName` Nullable(String),
        `LastEditorUserId` Nullable(Int64),
        `LastEditorDisplayName` Nullable(String),
        `LastEditDate` Nullable(DateTime64(3, 'UTC')),
        `LastActivityDate` Nullable(DateTime64(3, 'UTC')),
        `Title` Nullable(String),
        `Tags` Nullable(String),
        `AnswerCount` Nullable(Int64),
        `CommentCount` Nullable(Int64),
        `FavoriteCount` Nullable(Int64),
        `ContentLicense` Nullable(String),
        `ParentId` Nullable(String),
        `CommunityOwnedDate` Nullable(DateTime64(3, 'UTC')),
        `ClosedDate` Nullable(DateTime64(3, 'UTC'))
)
ENGINE = MergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')
ORDER BY tuple()

بعد تعريف المخطط الأوّلي، يمكننا تعبئة البيانات باستخدام INSERT INTO SELECT، مع قراءة البيانات عبر دالة الجدول S3. يحمّل المثال التالي بيانات posts في نحو دقيقتين على مثيل ClickHouse Cloud مزوّد بثماني نوى.

INSERT INTO posts SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

0 rows in set. Elapsed: 148.140 sec. Processed 59.82 million rows, 38.07 GB (403.80 thousand rows/s., 257.00 MB/s.)

يحمّل الاستعلام أعلاه 60 مليون صف. ومع أن هذا العدد يُعد صغيرًا بالنسبة إلى ClickHouse، فقد يرغب المستخدمون الذين لديهم اتصالات إنترنت أبطأ في تحميل مجموعة فرعية من البيانات. ويمكن تحقيق ذلك ببساطة من خلال تحديد السنوات التي يريدون تحميلها باستخدام نمط glob، مثل https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/2008.parquet أو https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/{2008, 2009}.parquet. راجع هنا لمعرفة كيفية استخدام أنماط glob لاستهداف مجموعات فرعية من الملفات.

​

تحسين الأنواع

أحد أسرار أداء الاستعلامات في ClickHouse هو الضغط.

تقليل حجم البيانات على القرص يعني عمليات I/O أقل، وبالتالي استعلامات وعمليات insert أسرع. وفي معظم الحالات، تفوق الاستفادة من تقليل I/O أي عبء إضافي تفرضه خوارزمية الضغط على CPU. لذلك، ينبغي أن يكون تحسين ضغط البيانات أول ما نركز عليه عند العمل على تسريع استعلامات ClickHouse.

لمعرفة سبب كفاءة ClickHouse العالية في ضغط البيانات، نوصي بقراءة هذه المقالة. باختصار، وبحكم أنه قاعدة بيانات موجَّهة بالأعمدة، تُكتب القيم بترتيب الأعمدة. وإذا كانت هذه القيم مرتبة، فستتجاور القيم المتشابهة. وتستفيد خوارزميات الضغط من الأنماط المتجاورة في البيانات. وإضافةً إلى ذلك، يوفّر ClickHouse codecs وأنواع بيانات دقيقة التحبّب تتيح لك ضبط تقنيات الضغط بمزيد من الدقة.

يتأثر الضغط في ClickHouse بثلاثة عوامل رئيسية: ordering key، وأنواع البيانات، وأي codecs مستخدمة. وكل ذلك يُضبط عبر المخطط.

يمكن تحقيق أكبر تحسن أولي في الضغط وأداء الاستعلامات من خلال عملية بسيطة لتحسين الأنواع. ويمكن تطبيق بعض القواعد البسيطة لتحسين المخطط:

• استخدم أنواعًا صارمة - استخدم المخطط الأولي لدينا Strings في كثير من الأعمدة التي من الواضح أنها رقمية. ويضمن استخدام الأنواع الصحيحة الدلالات المتوقعة عند التصفية والتجميع. وينطبق الأمر نفسه على أنواع التاريخ، التي وُفِّرت بالفعل بشكل صحيح في ملفات Parquet.
• تجنب الأعمدة Nullable - يُفترض افتراضيًا أن الأعمدة أعلاه يمكن أن تكون Null. ويتيح النوع Nullable للاستعلامات التمييز بين القيمة الفارغة وقيمة Null. ويؤدي ذلك إلى إنشاء عمود منفصل من النوع UInt8. ويجب معالجة هذا العمود الإضافي في كل مرة يعمل فيها المستخدم مع عمود Nullable. وهذا يؤدي إلى استهلاك مساحة تخزين إضافية، وغالبًا ما يؤثر سلبًا في أداء الاستعلامات. لا تستخدم Nullable إلا إذا كان هناك فرق فعلي بين القيمة الفارغة الافتراضية لنوع ما وقيمة Null. فعلى سبيل المثال، من المرجح أن تكون القيمة 0 للقيم الفارغة في العمود ViewCount كافية لمعظم الاستعلامات ولن تؤثر في النتائج. وإذا كان ينبغي التعامل مع القيم الفارغة بصورة مختلفة، فيمكن غالبًا استبعادها من الاستعلامات باستخدام filter.
• استخدم أقل precision ممكن للأنواع الرقمية - يوفّر ClickHouse عددًا من الأنواع الرقمية المصممة لنطاقات رقمية ومستويات precision مختلفة. واحرص دائمًا على تقليل عدد bits المستخدمة لتمثيل العمود. وإلى جانب الأعداد الصحيحة ذات الأحجام المختلفة مثل Int16، يوفّر ClickHouse أيضًا متغيرات غير موقعة تكون قيمتها الدنيا 0. ويمكن أن يتيح ذلك استخدام bits أقل للعمود؛ فعلى سبيل المثال، الحد الأقصى لـ UInt16 هو 65535، أي ضعف Int16. ويفضَّل استخدام هذه الأنواع بدلًا من المتغيرات الموقعة الأكبر متى أمكن.
• أقل precision لأنواع التاريخ - يدعم ClickHouse عددًا من أنواع التاريخ والتاريخ والوقت. ويمكن استخدام Date وDate32 لتخزين التواريخ فقط، مع دعم الثاني لنطاق تاريخ أكبر مقابل استخدام bits أكثر. ويوفّر DateTime وDateTime64 دعمًا لقيم date time. ويقتصر DateTime على granularity بالثانية ويستخدم 32 bits. أما DateTime64، وكما يوحي الاسم، فيستخدم 64 bits، لكنه يوفّر دعمًا يصل إلى granularity بالنانوثانية. وكما هو الحال دائمًا، اختر الإصدار الأقل دقة المقبول لاستعلاماتك، مع تقليل عدد bits المطلوبة.
• استخدم LowCardinality - يمكن ترميز الأعمدة من نوع Numbers وStrings وDate أو DateTime التي تحتوي على عدد قليل من القيم الفريدة باستخدام النوع LowCardinality. ويستخدم هذا النوع Dictionary لترميز القيم، مما يقلل الحجم على القرص. فكّر في هذا الخيار للأعمدة التي تحتوي على أقل من 10 آلاف قيمة فريدة.
• استخدم FixedString للحالات الخاصة - يمكن ترميز Strings ذات الطول الثابت بالنوع FixedString، مثل رموز اللغات والعملات. ويكون هذا فعالًا عندما يكون طول البيانات N بايتًا بالضبط. وفي جميع الحالات الأخرى، يُرجَّح أن يقلل الكفاءة، ويُفضَّل LowCardinality.
• استخدم Enums للتحقق من صحة البيانات - يمكن استخدام النوع Enum لترميز الأنواع المعدودة بكفاءة. ويمكن أن تكون Enums بطول 8 أو 16 bits، بحسب عدد القيم الفريدة المطلوب تخزينها. وفكّر في استخدام ذلك إذا كنت تحتاج إلى التحقق المرتبط عند insert time (ستُرفض القيم غير المعلنة) أو إذا كنت ترغب في تنفيذ استعلامات تستفيد من ترتيب طبيعي في قيم Enum، مثل عمود feedback يحتوي على ردود المستخدمين Enum(':(' = 1, ':|' = 2, ':)' = 3).

نصيحة: للعثور على نطاق جميع الأعمدة وعدد القيم المميزة، يمكنك استخدام الاستعلام البسيط SELECT * APPLY min, * APPLY max, * APPLY uniq FROM table FORMAT Vertical. ونوصي بتنفيذ ذلك على subset أصغر من البيانات لأن ذلك قد يكون مكلفًا. ويتطلب هذا الاستعلام أن تكون القيم الرقمية معرّفة على هذا الأساس على الأقل للحصول على نتيجة دقيقة، أي ألا تكون من النوع String.

بتطبيق هذه القواعد البسيطة على posts table لدينا، يمكننا تحديد النوع الأمثل لكل عمود:

العمود	هل هو رقمي	الحد الأدنى، الحد الأقصى	القيم الفريدة	القيم الخالية	التعليق	النوع المُحسَّن
PostTypeId	نعم	1, 8	8	لا		Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8)
AcceptedAnswerId	نعم	0, 78285170	12282094	نعم	فرّق بين NULL والقيمة 0	UInt32
CreationDate	لا	2008-07-31 21:42:52.667000000, 2024-03-31 23:59:17.697000000	*	لا	لا حاجة إلى دقة الملّي ثانية، استخدم DateTime	DateTime
Score	نعم	-217, 34970	3236	لا		Int32
ViewCount	نعم	2, 13962748	170867	لا		UInt32
المحتوى	لا	-	*	لا		String
OwnerUserId	نعم	-1, 4056915	6256237	نعم		Int32
OwnerDisplayName	لا	-	181251	نعم	اعتبار NULL كسلسلة نصية فارغة	String
LastEditorUserId	نعم	-1, 9999993	1104694	نعم	0 قيمة غير مستخدمة ويمكن استخدامها مع قيم NULL	Int32
LastEditorDisplayName	لا	*	70952	نعم	اعتبر القيمة Null سلسلةً فارغة. تم اختبار LowCardinality ولم تظهر أي فائدة	String
LastEditDate	لا	2008-08-01 13:24:35.051000000, 2024-04-06 21:01:22.697000000	-	لا	لا حاجة إلى دقة المللي ثانية، استخدم DateTime	DateTime
LastActivityDate	لا	2008-08-01 12:19:17.417000000, 2024-04-06 21:01:22.697000000	*	لا	لا حاجة إلى دقة المللي ثانية، استخدم DateTime	DateTime
Title	لا	-	*	لا	اعتبر NULL سلسلة نصية فارغة	String
Tags	لا	-	*	لا	اعتبر NULL سلسلة نصية فارغة	String
AnswerCount	نعم	0, 518	216	لا	اعتبر NULL و0 متماثلين	UInt16
CommentCount	نعم	0, 135	100	لا	اعتبر NULL و0 متساويين	UInt8
FavoriteCount	نعم	0, 225	6	نعم	اعتبار NULL و0 متماثلين	UInt8
ContentLicense	لا	-	3	لا	يقدّم LowCardinality أداءً أفضل من FixedString	LowCardinality(String)
ParentId	لا	*	20696028	نعم	تعامل مع Null كسلسلة فارغة	String
CommunityOwnedDate	لا	2008-08-12 04:59:35.017000000, 2024-04-01 05:36:41.380000000	-	نعم	يُفضَّل استخدام القيمة الافتراضية 1970-01-01 للقيم NULL. لا حاجة إلى دقة المللي ثانية، استخدم DateTime	DateTime
ClosedDate	لا	2008-09-04 20:56:44, 2024-04-06 18:49:25.393000000	*	نعم	يُفضَّل استخدام القيمة الافتراضية 1970-01-01 لقيم NULL. لا حاجة إلى دقة المللي ثانية، استخدم DateTime	DateTime

ينتج عن ذلك المخطّط التالي:

CREATE TABLE posts_v2
(
   `Id` Int32,
   `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
   `AcceptedAnswerId` UInt32,
   `CreationDate` DateTime,
   `Score` Int32,
   `ViewCount` UInt32,
   `Body` String,
   `OwnerUserId` Int32,
   `OwnerDisplayName` String,
   `LastEditorUserId` Int32,
   `LastEditorDisplayName` String,
   `LastEditDate` DateTime,
   `LastActivityDate` DateTime,
   `Title` String,
   `Tags` String,
   `AnswerCount` UInt16,
   `CommentCount` UInt8,
   `FavoriteCount` UInt8,
   `ContentLicense`LowCardinality(String),
   `ParentId` String,
   `CommunityOwnedDate` DateTime,
   `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'

يمكننا تعبئة هذا باستخدام INSERT INTO SELECT بسيط، عبر قراءة البيانات من الجدول السابق وإدراجها في هذا الجدول:

INSERT INTO posts_v2 SELECT * FROM posts

0 rows in set. Elapsed: 146.471 sec. Processed 59.82 million rows, 83.82 GB (408.40 thousand rows/s., 572.25 MB/s.)

نحن لا نحتفظ بأي قيم NULL في المخطط الجديد. ويحوّل أمر insert أعلاه هذه القيم ضمنيًا إلى القيم الافتراضية لأنواعها المقابلة: 0 للأعداد الصحيحة وقيمة فارغة للسلاسل النصية. كما يحوّل ClickHouse تلقائيًا أي قيم رقمية إلى الدقة المستهدفة لها. المفاتيح الأساسية (مفاتيح الترتيب) في ClickHouse غالبًا ما يبحث المستخدمون القادمون من قواعد بيانات OLTP عن مفهوم مكافئ لذلك في ClickHouse.

​

اختيار مفتاح الترتيب

عند الأحجام التي يُستخدم فيها ClickHouse غالبًا، تكون كفاءة الذاكرة والقرص بالغة الأهمية. تُكتب البيانات في جداول ClickHouse على شكل دفعات تُعرف باسم الأجزاء، مع تطبيق قواعد لدمج هذه الأجزاء في الخلفية. وفي ClickHouse، لكل جزء فهرسه الأساسي الخاص. وعند دمج الأجزاء، تُدمج أيضًا الفهارس الأساسية للجزء الناتج. ويحتوي الفهرس الأساسي للجزء على إدخال فهرس واحد لكل مجموعة من الصفوف — وتُعرف هذه التقنية باسم الفهرسة المتفرقة.

لا يحدد المفتاح المختار في ClickHouse الفهرس فقط، بل يحدد أيضًا الترتيب الذي تُكتب به البيانات على القرص. ولهذا قد يؤثر بشكل كبير في مستويات الضغط، مما قد ينعكس بدوره على أداء الاستعلامات. فمفتاح الترتيب الذي يجعل قيم معظم الأعمدة تُكتب بترتيب متجاور يتيح لخوارزمية الضغط المختارة (والـ codecs) ضغط البيانات بكفاءة أكبر.

ستُرتَّب جميع الأعمدة في الجدول استنادًا إلى قيمة مفتاح الترتيب المحدد، سواء كانت مُدرجة في المفتاح نفسه أم لا. على سبيل المثال، إذا استُخدم CreationDate كمفتاح، فسيطابق ترتيب القيم في جميع الأعمدة الأخرى ترتيب القيم في العمود CreationDate. ويمكن تحديد عدة مفاتيح ترتيب — وسيجري الترتيب هنا وفق الدلالات نفسها لعبارة ORDER BY في استعلام SELECT.

يمكن تطبيق بعض القواعد البسيطة للمساعدة في اختيار مفتاح ترتيب. وقد تتعارض النقاط التالية أحيانًا، لذا يُستحسن النظر فيها بهذا الترتيب. ويمكنك من خلال هذه العملية تحديد عدد من المفاتيح، ويكون 4-5 منها كافيًا عادةً:

• اختر الأعمدة التي تتوافق مع عوامل التصفية الأكثر شيوعًا لديك. فإذا كان أحد الأعمدة يُستخدم كثيرًا في عبارات WHERE، فأعطه أولوية عند تضمينه في المفتاح مقارنةً بالأعمدة الأقل استخدامًا. وفضّل الأعمدة التي تساعد، عند التصفية، على استبعاد نسبة كبيرة من إجمالي الصفوف، مما يقلل كمية البيانات التي يلزم قراءتها.
• فضّل الأعمدة التي يُرجح أن تكون شديدة الارتباط بأعمدة أخرى في الجدول. فهذا يساعد على ضمان تخزين هذه القيم أيضًا بشكل متجاور، مما يحسّن الضغط. كما يمكن جعل عمليات GROUP BY وORDER BY للأعمدة الموجودة في مفتاح الترتيب أكثر كفاءة من حيث الذاكرة.

عند تحديد المجموعة الفرعية من الأعمدة لمفتاح الترتيب، عرّف الأعمدة بترتيب محدد. ويمكن أن يؤثر هذا الترتيب بشكل كبير في كلٍّ من كفاءة التصفية على أعمدة المفتاح الثانوية في الاستعلامات، ونسبة الضغط في ملفات بيانات الجدول. وبوجه عام، من الأفضل ترتيب المفاتيح تصاعديًا حسب الكاردينالية. لكن ينبغي موازنة ذلك مع حقيقة أن التصفية على الأعمدة التي تظهر لاحقًا في مفتاح الترتيب ستكون أقل كفاءة من التصفية على الأعمدة التي تظهر مبكرًا في الـ Tuple. وازن بين هذين الجانبين وراعِ أنماط الوصول لديك (والأهم من ذلك: اختبر المتغيرات).

​

مثال

بتطبيق الإرشادات أعلاه على جدول posts، فلنفترض أن المستخدمين يريدون إجراء تحليلات تتضمن التصفية حسب التاريخ ونوع المنشور، على سبيل المثال:

“ما الأسئلة التي حصدت أكبر عدد من التعليقات خلال الأشهر الثلاثة الماضية؟”

الاستعلام الخاص بهذا السؤال باستخدام جدول posts_v2 السابق ذي الأنواع المحسّنة ولكن من دون مفتاح ترتيب:

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v2
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

┌───────Id─┬─Title─────────────────────────────────────────────────────────────┬─CommentCount─┐
│ 78203063 │ How to avoid default initialization of objects in std::vector?     │               74 │
│ 78183948 │ About memory barrier                                               │               52 │
│ 77900279 │ Speed Test for Buffer Alignment: IBM's PowerPC results vs. my CPU │        49 │
└──────────┴───────────────────────────────────────────────────────────────────┴──────────────

10 rows in set. Elapsed: 0.070 sec. Processed 59.82 million rows, 569.21 MB (852.55 million rows/s., 8.11 GB/s.)
Peak memory usage: 429.38 MiB.

الاستعلام هنا سريع جدًا رغم أنه جرى فحص جميع الصفوف البالغ عددها 60 مليون صف فحصًا خطيًا — ClickHouse سريع فحسب :) سيتعين عليك أن تثق بنا بأن مفاتيح الترتيب تستحق ذلك عند العمل على نطاقات TB وPB!

لنحدّد العمودين PostTypeId وCreationDate كمفاتيح الترتيب لدينا.

ربما نتوقع في حالتنا أن يطبّق المستخدمون دائمًا عامل تصفية على PostTypeId. يبلغ عدد القيم المميّزة هنا 8، ما يجعله الخيار المنطقي للعنصر الأول في مفتاح الترتيب لدينا. ونظرًا إلى أن التصفية بدقة التاريخ يُرجّح أن تكون كافية (مع أنها ستظل مفيدة أيضًا لعوامل تصفية التاريخ والوقت)، فإننا نستخدم toDate(CreationDate) بوصفه المكوّن الثاني من مفتاحنا. وسيؤدي هذا أيضًا إلى إنشاء فهرس أصغر، لأن التاريخ يمكن تمثيله باستخدام 16، مما يسرّع التصفية. أما العنصر الأخير في مفتاحنا فهو CommentCount للمساعدة في العثور على المنشورات الأكثر احتواءً على التعليقات (الفرز النهائي).

CREATE TABLE posts_v3
(
        `Id` Int32,
        `PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime,
        `Score` Int32,
        `ViewCount` UInt32,
        `Body` String,
        `OwnerUserId` Int32,
        `OwnerDisplayName` String,
        `LastEditorUserId` Int32,
        `LastEditorDisplayName` String,
        `LastEditDate` DateTime,
        `LastActivityDate` DateTime,
        `Title` String,
        `Tags` String,
        `AnswerCount` UInt16,
        `CommentCount` UInt8,
        `FavoriteCount` UInt8,
        `ContentLicense` LowCardinality(String),
        `ParentId` String,
        `CommunityOwnedDate` DateTime,
        `ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)
COMMENT 'Ordering Key'

--populate table from existing table

INSERT INTO posts_v3 SELECT * FROM posts_v2

0 rows in set. Elapsed: 158.074 sec. Processed 59.82 million rows, 76.21 GB (378.42 thousand rows/s., 482.14 MB/s.)
Peak memory usage: 6.41 GiB.

يحسّن الاستعلام السابق زمن الاستجابة بأكثر من 3 أضعاف:

SELECT
    Id,
    Title,
    CommentCount
FROM posts_v3
WHERE (CreationDate >= '2024-01-01') AND (PostTypeId = 'Question')
ORDER BY CommentCount DESC
LIMIT 3

10 rows in set. Elapsed: 0.020 sec. Processed 290.09 thousand rows, 21.03 MB (14.65 million rows/s., 1.06 GB/s.)

بالنسبة إلى المستخدمين المهتمين بتحسينات الضغط التي يمكن تحقيقها باستخدام أنواع محددة ومفاتيح ترتيب مناسبة، راجع الضغط في ClickHouse. وإذا كنت بحاجة إلى تحسين الضغط بدرجة أكبر، فنوصي أيضًا بقراءة قسم اختيار خوارزمية ترميز ضغط العمود المناسبة.

​

التالي: تقنيات نمذجة البيانات

حتى الآن، لم نرحّل سوى جدول واحد. ومع أن ذلك أتاح لنا تقديم بعض مفاهيم ClickHouse الأساسية، فإن معظم المخططات ليست بهذه البساطة للأسف.

في الأدلة الأخرى المدرجة أدناه، سنستعرض عددًا من التقنيات لإعادة هيكلة مخططنا الأوسع بما يحقق أفضل أداء للاستعلامات في ClickHouse. وخلال هذه العملية، نهدف إلى أن يظل Posts جدولنا المركزي الذي تُجرى من خلاله معظم الاستعلامات التحليلية. ومع أنه لا يزال بالإمكان الاستعلام عن الجداول الأخرى بشكل مستقل، فإننا نفترض أن معظم أعمال التحليلات تُجرى في سياق posts.

في هذا القسم، نستخدم إصدارات محسّنة من جداولنا الأخرى. ومع أننا نوفر مخططاتها، فإننا نغفل القرارات المتخذة فيها اختصارًا. تستند هذه القرارات إلى القواعد الموضحة سابقًا، ونترك للقارئ استنتاجها.

تهدف جميع الأساليب التالية إلى تقليل الحاجة إلى استخدام JOINs من أجل تحسين عمليات القراءة وتعزيز أداء الاستعلامات. ومع أن JOINs مدعومة بالكامل في ClickHouse، فإننا نوصي باستخدامها بحذر (فلا بأس بجدولين إلى ثلاثة جداول في استعلام JOIN) لتحقيق أفضل أداء.

لا يدعم ClickHouse مفهوم المفاتيح الخارجية. وهذا لا يمنع عمليات الربط، لكنه يعني أن إدارة التكامل المرجعي تُترك للمستخدم على مستوى التطبيق. في أنظمة OLAP مثل ClickHouse، غالبًا ما تُدار سلامة البيانات على مستوى التطبيق أو أثناء عملية إدخال البيانات، بدلًا من فرضها من قِبل قاعدة البيانات نفسها، لأن ذلك يضيف حملًا إضافيًا كبيرًا. يتيح هذا النهج مرونة أكبر وإدخالًا أسرع للبيانات. وهذا ينسجم مع تركيز ClickHouse على السرعة وقابلية التوسع في استعلامات القراءة والإدراج على مجموعات البيانات الكبيرة جدًا.

لتقليل استخدام Joins في وقت الاستعلام، تتوفر للمستخدمين عدة أدوات/أساليب:

• إزالة تطبيع البيانات - أزِل تطبيع البيانات عبر دمج الجداول واستخدام الأنواع المعقدة للعلاقات التي ليست 1:1. وغالبًا ما يتضمن ذلك نقل أي عمليات ربط من وقت الاستعلام إلى وقت الإدراج.
• Dictionaries - ميزة خاصة في ClickHouse للتعامل مع direct joins وعمليات البحث عن المفتاح والقيمة.
• Incremental Materialized Views - ميزة في ClickHouse لنقل كلفة عملية حسابية من وقت الاستعلام إلى وقت الإدراج، بما في ذلك القدرة على حساب القيم التجميعية تدريجيًا.
• Refreshable Materialized Views - على غرار materialized views المستخدمة في منتجات قواعد البيانات الأخرى، يتيح ذلك حساب نتائج الاستعلام دوريًا وتخزين النتيجة مؤقتًا.

سنستعرض كل واحد من هذه الأساليب في دليل مستقل، مع توضيح الحالات المناسبة لكل منها من خلال مثال يبين كيفية تطبيقه للإجابة عن أسئلة تتعلق بـ مجموعة بيانات Stack Overflow.