أداء الاستعلام - السلاسل الزمنية

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

حسّن مفاتيح `ORDER BY`

قبل تجربة أي تحسينات أخرى، ينبغي أولًا تحسين مفاتيح الترتيب لضمان أن يقدّم ClickHouse أسرع النتائج الممكنة. ويعتمد اختيار المفتاح المناسب إلى حد كبير على الاستعلامات التي ستجريها. لنفترض أن معظم استعلاماتنا تُصفّي حسب العمودين project وsubproject. في هذه الحالة، من الجيد إضافتهما إلى مفتاح الترتيب، وكذلك العمود time لأننا نستعلم حسب الوقت أيضًا. لننشئ نسخة أخرى من الجدول لها أنواع الأعمدة نفسها في wikistat، لكنها مرتبة حسب (project, subproject, time).

CREATE TABLE wikistat_project_subproject
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (project, subproject, time);

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

الاستعلام	`(time)`	`(project, subproject, time)`
`SELECT project, sum(hits) AS h FROM wikistat GROUP BY project ORDER BY h DESC LIMIT 10;`	2.381 ثانية	1.660 ثانية
`SELECT subproject, sum(hits) AS h FROM wikistat WHERE project = 'it' GROUP BY subproject ORDER BY h DESC LIMIT 10;`	2.148 ثانية	0.058 ثانية
`SELECT toStartOfMonth(time) AS m, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY m ORDER BY m DESC LIMIT 10;`	2.192 ثانية	0.012 ثانية
`SELECT path, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY path ORDER BY h DESC LIMIT 10;`	2.968 ثانية	0.010 ثانية

العروض المُجسَّدة

خيار آخر هو استخدام العروض المُجسَّدة لتجميع نتائج الاستعلامات الشائعة وتخزينها. ويمكن الاستعلام عن هذه النتائج بدلًا من الجدول الأصلي. لنفترض أن الاستعلام التالي يُنفَّذ كثيرًا في حالتنا:

SELECT path, SUM(hits) AS v
FROM wikistat
WHERE toStartOfMonth(time) = '2015-05-01'
GROUP BY path
ORDER BY v DESC
LIMIT 10

┌─path──────────────────┬────────v─┐
│ -                     │ 89650862 │
│ Angelsberg            │ 19165753 │
│ Ana_Sayfa             │  6368793 │
│ Academy_Awards        │  4901276 │
│ Accueil_(homonymie)   │  3805097 │
│ Adolf_Hitler          │  2549835 │
│ 2015_in_spaceflight   │  2077164 │
│ Albert_Einstein       │  1619320 │
│ 19_Kids_and_Counting  │  1430968 │
│ 2015_Nepal_earthquake │  1406422 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 2.285 sec. Processed 231.41 million rows, 9.22 GB (101.26 million rows/s., 4.03 GB/s.)
Peak memory usage: 1.50 GiB.

إنشاء عرض مُجسَّد

يمكننا إنشاء العرض المُجسَّد التالي:

CREATE TABLE wikistat_top
(
    `path` String,
    `month` Date,
    hits UInt64
)
ENGINE = SummingMergeTree
ORDER BY (month, hits);

CREATE MATERIALIZED VIEW wikistat_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

تعبئة جدول الوجهة بالبيانات التاريخية

لن تتم تعبئة جدول الوجهة هذا إلا عند إدراج سجلات جديدة في جدول wikistat، لذا نحتاج إلى إجراء تعبئة للبيانات التاريخية. أسهل طريقة لتنفيذ ذلك هي استخدام تعليمة INSERT INTO SELECT للإدراج مباشرةً في الجدول الهدف الخاص بالعرض المُجسَّد باستخدام استعلام SELECT الخاص بالعرض (التحويل):

INSERT INTO wikistat_top
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat
GROUP BY path, month;

اعتمادًا على كاردينالية مجموعة البيانات الخام (لدينا مليار صف!)، قد يكون هذا النهج كثيف الاستهلاك للذاكرة. بدلًا من ذلك، يمكنك استخدام طريقة بديلة تتطلب حدًا أدنى من الذاكرة:

إنشاء جدول مؤقت باستخدام محرك جدول Null
ربط نسخة من العرض المُجسَّد المستخدم عادةً بذلك الجدول المؤقت
باستخدام استعلام INSERT INTO SELECT، نسخ جميع البيانات من مجموعة البيانات الخام إلى ذلك الجدول المؤقت
حذف الجدول المؤقت والعرض المُجسَّد المؤقت.

باستخدام هذا النهج، تُنسخ الصفوف من مجموعة البيانات الخام على مستوى الكتل إلى الجدول المؤقت (الذي لا يخزّن أيًا من هذه الصفوف)، ولكل كتلة من الصفوف، تُحتسب حالة جزئية وتُكتب في الجدول الهدف، حيث تُدمج هذه الحالات تدريجيًا في الخلفية.

CREATE TABLE wikistat_backfill
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = Null;

بعد ذلك، سننشئ عرضًا مُجسَّدًا لقراءة البيانات من wikistat_backfill وكتابتها في wikistat_top

CREATE MATERIALIZED VIEW wikistat_backfill_top_mv 
TO wikistat_top
AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM wikistat_backfill
GROUP BY path, month;

وأخيرًا، سنعبّئ wikistat_backfill انطلاقًا من جدول wikistat الأوّلي:

INSERT INTO wikistat_backfill
SELECT * 
FROM wikistat;

بمجرد اكتمال هذا الاستعلام، يمكننا حذف جدول تعبئة البيانات التاريخية والعرض المُجسَّد:

DROP VIEW wikistat_backfill_top_mv;
DROP TABLE wikistat_backfill;

يمكننا الآن الاستعلام من العرض المُجسَّد بدلًا من الجدول الأصلي:

SELECT path, sum(hits) AS hits
FROM wikistat_top
WHERE month = '2015-05-01'
GROUP BY ALL
ORDER BY hits DESC
LIMIT 10;

┌─path──────────────────┬─────hits─┐
│ -                     │ 89543168 │
│ Angelsberg            │  7047863 │
│ Ana_Sayfa             │  5923985 │
│ Academy_Awards        │  4497264 │
│ Accueil_(homonymie)   │  2522074 │
│ 2015_in_spaceflight   │  2050098 │
│ Adolf_Hitler          │  1559520 │
│ 19_Kids_and_Counting  │   813275 │
│ Andrzej_Duda          │   796156 │
│ 2015_Nepal_earthquake │   726327 │
└───────────────────────┴──────────┘

10 rows in set. Elapsed: 0.004 sec.

التحسّن في الأداء هنا هائل. فبعد أن كان يستغرق ما يزيد قليلًا على ثانيتين لحساب إجابة هذا الاستعلام، أصبح الآن لا يستغرق سوى 4 ميلي ثانية.

​حسّن مفاتيح ORDER BY

​العروض المُجسَّدة

​إنشاء عرض مُجسَّد

​تعبئة جدول الوجهة بالبيانات التاريخية

حسّن مفاتيح `ORDER BY`

العروض المُجسَّدة

إنشاء عرض مُجسَّد

تعبئة جدول الوجهة بالبيانات التاريخية