Сводные таблицы хранилища данных

Введение

В данном документе рассматривается создание и ведение "Сводных таблиц". Он является дополнением к документу по Методам хранения данных в хранилище.

Основные термины ("Таблица фактов", "Нормализация" и т.д.) освещаются в этом документе.

Сводные таблицы для отчетов хранилища данных

Сводные таблицы необходимы для повышения производительности больших таблиц. MariaDB и MySQL не предоставляют автоматического способа их создания, поэтому здесь представлены методы.

(Другие поставщики предлагают что-то подобное с помощью "материализованных представлений".)

Когда у вас миллионы или миллиарды строк, сводка данных для отображения подсчетов, итогов, средних значений и т.д. в удобном для восприятия человеком формате занимает много времени. Вычисляя и сохраняя промежуточные итоги по мере поступления данных, можно значительно ускорить работу с "отчетами". (Я видел ускорение в 10-1000 раз.) Промежуточные итоги записываются в "сводную таблицу". Этот документ поможет вам повысить эффективность как при создании, так и при использовании таких таблиц.

Общая структура сводной таблицы

Сводная таблица включает два набора столбцов:

• Основной ключ: дата + некоторые измерения
• Промежуточные итоги: COUNT(*), SUM(...), ...; но не AVG()

«Дата» может быть типом данных DATE (3-байтовый родной тип данных) или часом, или каким-либо другим временным интервалом. 3-байтовый MEDIUMINT UNSIGNED 'час' может быть получен из DATETIME или TIMESTAMP с помощью

   FLOOR(UNIX_TIMESTAMP(dt) / 3600)
   FROM_UNIXTIME(hour * 3600)

«Измерения» (термин DW) — это некоторые столбцы таблицы «Факты». Примеры: Страна, Производитель, Товар, Категория, Хост. Примеры неизмерений: Продажи, Количество, ВремяЗатраченное

Обычно существует один или несколько индексов, обычно начинающихся с некоторых измерений и заканчивающихся полем даты. Заканчивая полем даты, можно эффективно получить диапазон дней/недель и т.д., даже если каждая строка суммирует только один день.

Как правило, существует «несколько» сводных таблиц. Часто одной сводной таблицы достаточно для эффективного решения нескольких задач.

Как правило, сводная таблица будет иметь в десять раз меньше строк, чем таблица фактов. (Это очень приблизительное значение.)

Пример

Давайте поговорим о большой сети автосалонов. Таблица фактов содержит все продажи со столбцами, такими как дата и время, id продавца, город, цена, id клиента, марка, модель, год модели. Одна сводная таблица может быть сфокусирована на продажах:

   PRIMARY KEY(city, datetime),
   Aggregations: ct, sum_price
   
   # Core of INSERT..SELECT:
   DATE(datetime) AS date, city, COUNT(*) AS ct, SUM(price) AS sum_price
   
   # Reporting average price for last month, broken down by city:
   SELECT city,
          SUM(sum_price) / SUM(ct) AS 'AveragePrice'
      FROM SalesSummary
      WHERE datetime BETWEEN ...
      GROUP BY city;
   
   # Monthly sales, nationwide, from same summary table:
   SELECT MONTH(datetime) AS 'Month',
          SUM(ct)         AS 'TotalSalesCount'
          SUM(sum_price)  AS 'TotalDollars'
      FROM SalesSummary
      WHERE datetime BETWEEN ...
      GROUP BY MONTH(datetime);
   # This might benefit from a secondary INDEX(datetime)

Когда следует дополнять сводную таблицу(ы)?

«Дополнить» в данном разделе означает добавить новые строки в сводную таблицу или увеличить счетчики в существующих строках.

Вариант А: «Во время вставки» строк в таблицу фактов дополняйте сводную таблицу(ы). Это просто и работает для баз данных DW меньшего размера (менее 10 строк таблицы фактов в секунду). Для баз данных DW большего размера вариант А, вероятно, слишком дорогостоящий, чтобы быть практичным.

Вариант B: «Периодически», через cron или событие.

Вариант C: «По мере необходимости». То есть, когда кто-то запрашивает отчет, код сначала обновляет необходимые сводные таблицы.

Вариант D: «Гибридный» вариант B и C. Только вариант C может привести к значительным задержкам при получении отчета. Реализуя оба варианта, эти задержки можно сократить.

Вариант E: (Не рекомендуется.) «Перестроение» всей сводной таблицы на основе всей таблицы фактов. Стоимость этого значительна для больших таблиц. Однако вариант E может потребоваться, если вы решите изменить столбцы сводной таблицы или обнаружите ошибку в расчетах. Чтобы уменьшить влияние полной перестройки, используйте методы разделения на части в Разбиении на части при удалении.

Вариант F: «Таблица-сцена». Это в первую очередь для очень скоростного приема данных. В этом блоге он упомянут кратко, а более подробно рассматривается в сопутствующем блоге: Быстрый прием данных

Сводка данных при вставке одной строки за раз

    INSERT INTO Fact ...;
    INSERT INTO Summary (..., ct, foo, ...) VALUES (..., 1, foo, ...)
        ON DUPLICATE KEY UPDATE ct = ct+1, sum_foo = sum_foo + VALUES(foo), ...;

IODKU (Insert On Duplicate Key Update) обновит существующую строку или создаст новую. Он понимает, что делать, основываясь на первичном ключе сводной таблицы.

Предупреждение: Этот подход дорогостоящий и не масштабируется со скоростью приема более, скажем, 10 строк в секунду (или, возможно, 50/секунду на SSD). Более подробная информация будет позже.

Сводка данных периодически или по мере необходимости

Если ваши отчеты должны быть актуальными в реальном времени, вам нужен вариант «по мере необходимости» или «гибридный». Если ваши отчеты менее срочные (например, еженедельные отчеты, не включающие «сегодня»), то вариант «периодически» может быть лучшим.

Для ежедневных сводок дополнение сводных таблиц можно выполнить сразу после полуночи. Однако будьте осторожны с данными, поступающими «с опозданием».

Для обоих вариантов «периодически» и «по мере необходимости» необходим определенный способ отслеживания точки «прекращения».

Случай 1: Вы сначала вставляете данные в таблицу фактов, и она имеет идентификатор AUTO_INCREMENT: Получите MAX(id) в качестве верхней границы для сводки и поместите его либо в другое безопасное место (дополнительная таблица), либо в строку(и) в сводной таблице при их вставке. (Примечание: идентификаторы AUTO_INCREMENT плохо работают в многомастерных, включая Galera, конфигурациях.)

Случай 2: Если вы используете таблицу «сцена», проблем нет. (Подробности о таблицах-сценах позже.)

Сводка данных при пакетной вставке

Это относится к многострочным (пакетным) операциям INSERT и LOAD DATA.

Таблица фактов должна иметь идентификатор AUTO_INCREMENT, и вам нужно иметь возможность найти точный диапазон вставленных идентификаторов. (Это может быть непрактично в любой многомастерной конфигурации.)

Затем выполните пакетную сводку данных, используя

   FROM Fact
   WHERE id BETWEEN min_id and max_id

Сводка данных при использовании таблицы-сцены

Загрузите данные (через INSERT или LOAD DATA) в массив в «таблицу-сцену». Затем выполните пакетную сводку данных из таблицы-сцены. И пакетно скопируйте данные из таблицы-сцены в таблицу фактов. Обратите внимание, что таблица-сцена полезна для пакетной «нормализации» при приеме данных. См. также [[data-warehousing-high-speed-ingestion|Быстрый прием данных

Сводная таблица: первичный ключ или нет?

Предположим, ваша сводная таблица содержит дату `dy` и измерение `foo`. Возникает вопрос: (foo, dy) должно быть первичным ключом? Или не уникальным индексом?

Случай 1: Первичный ключ (foo, dy), и сводка данных синхронизирована с, скажем, изменениями в `dy`.

Этот случай прост и понятен — до тех пор, пока вы не дойдете до крайних случаев. Как вы будете обрабатывать случай с «опоздавшими» данными? Возможно, вам нужно будет пересчитать некоторые части данных? Если да, как?

Случай 2: (foo, dy) является не уникальным индексом.

Этот случай прост и понятен, но он может засорять сводную таблицу, так как для одной пары (foo, dy) может быть несколько строк. Отчет всегда должен будет использовать SUM() для значений, так как он не может предположить, что есть только одна строка, даже когда он сообщает об одном `foo` для одной `dy`. Такое принудительное применение SUM() не очень плохо — вы должны это делать в любом случае; таким образом, все ваши отчеты написаны по одному шаблону.

Случай 3: Первичный ключ (foo, dy), и сводка данных может происходить в любое время.

Поскольку вы должны использовать InnoDB, должен быть явный первичный ключ. Один из подходов, когда у вас нет «естественного» первичного ключа, выглядит так:

   id INT UNSIGNED AUTO_INCREMENT NOT NULL,
   ...
   PRIMARY KEY(foo, dy, id),  -- `id` added to make unique
   INDEX(id)                  -- sufficient to keep AUTO_INCREMENT happy

Этот случай переносит сложность на сводку данных, выполняя IODKU.

Совет? Избегайте случая 1; слишком сложно. Случай 2 приемлем, если дополнительные строки не слишком часты. Случай 3, возможно, ближе всего к «размеру, подходящему для всех».

Средние значения и т.д.

При сводках включайте COUNT(*) AS ct and SUM(foo) AS sum_foo. При представлении отчета «среднее значение» вычисляется как SUM(sum_foo) / SUM(ct). Это математически верно.

Исключение... Предположим, вы анализируете температуры. И ваша станция наблюдения периодически, но не надежно, получает температуру. То есть, количество измерений для одного дня варьируется. Кроме того, вы решаете, что самый простой способ компенсировать несоответствия — это что-то вроде: вычислить среднюю температуру для каждого дня, а затем усреднить их по месяцам (или другим временным рамкам).

Формула для стандартного отклонения:

    SQRT( SUM(sum_foo2)/SUM(ct) - POWER(SUM(sum_foo)/SUM(ct), 2) )

Где sum_foo2 — это SUM(foo * foo) из сводной таблицы. sum_foo и sum_foo2 должны быть типа FLOAT. FLOAT обеспечивает около 7 значащих цифр, что более чем достаточно для таких вещей, как среднее значение и стандартное отклонение. FLOAT занимает 4 байта. DOUBLE даст вам большую точность, но занимает 8 байт. INT и BIGINT непрактичны, потому что могут привести к жалобам на переполнение.

Таблица-сцена

Идея заключается в том, чтобы сначала загрузить набор записей фактов в «таблицу-сцену» со следующими характеристиками (по крайней мере):

• Таблица многократно заполняется и обнуляется
• Вставки могут быть индивидуальными или пакетными, и от одного или нескольких клиентов
• SELECT будет выполнять сканирование таблицы, поэтому индексы не нужны
• Вставка будет быстрой (InnoDB может быть самой быстрой)
• Нормализация может быть выполнена в массовом порядке, поэтому эффективно
• Копирование в таблицу фактов будет быстрым
• Сводка данных может быть выполнена в массовом порядке, поэтому эффективно
• «Всплески» приема данных сглаживаются этим процессом
• Переключайте пару таблиц-сцен

Если у вас есть пакетные вставки (пакетный INSERT или LOAD DATA), подумайте о том, чтобы выполнить нормализацию и сводку данных сразу после каждой пакетной вставки.

Дополнительные сведения: Быстрый прием данных

Расширенный дизайн

Вот более сложный способ проектирования системы с целью достижения еще большей масштабируемости:

• Используйте конфигурацию мастер-раб: прием данных на мастер-сервере; получение отчетов на раб-сервере(ах).
• Передача приема данных через таблицу-сцену (как описано выше)
• Единственный источник данных: ENGINE=MEMORY; несколько источников: InnoDB
• binlog_format = ROW
• Используйте binlog_ignore_db, чтобы избежать репликации таблицы-сцены, что требует размещения её в отдельной базе данных.
• Выполните сводку данных из таблицы-сцены
• Загрузка фактов с помощью INSERT INTO Fact ... SELECT FROM Staging ...

Пояснение и комментарии:

• ROW + ignore_db позволяет избежать репликации таблицы-сцены, но при этом реплицирует вставки, основанные на ней. Таким образом, уменьшается нагрузка на запись на сервера-рабы
• Если вы используете MEMORY, помните, что она является непостоянной — восстанавливайтесь после сбоя, запустив прием данных заново.
• Для отладки обнуляйте или пересоздавайте таблицу-сцену в начале следующего цикла.
• Таблица-сцена не требует индексов — все операции считывают все строки из неё.

Статистика системы, из которой был получен этот «расширенный дизайн»: Таблица фактов: 450 ГБ, 100 млн строк/день (пакет 4 млн/час), 60-дневное хранение (60+24 раздела), 75 Б/строка, 7 сводных таблиц, время приема и сводки данных для часового пакета менее 10 минут. INSERT..SELECT обработало более 20 000 строк/с при записи в таблицу фактов. Винчестеры (не SSD) с RAID-10.

"Остатки"

Один из способов заключается в обобщении некоторых данных, а затем записи того, на каком этапе вы остановились, чтобы в следующий раз начать с этого места. Существуют некоторые тонкости с понятием "остановки", которых стоит опасаться.

Если вы используете DATETIME или TIMESTAMP в качестве точки остановки, будьте осторожны с множественными строками с одинаковым значением.

• План A: Используйте составной "маркер остановки" (например, TIMESTAMP + ID). Это сложно, подвержено ошибкам и т. д.
• План B: WHERE ts >= $left_off AND ts < $max_ts — избегает дубликатов, но имеет и другие проблемы (ниже)
• Разные потоки могут COMMIT TIMESTAMP'ы в неправильном порядке.

Если вы используете AUTO_INCREMENT в качестве "маркера остановки", будьте осторожны с:

• В InnoDB разные потоки могут COMMIT идентификаторы в неправильном порядке.
• Многомастерные (включая Galera и InnoDB Cluster) системы могут привести к проблемам с порядком.

Значит, ничего не работает, по крайней мере, не в многопоточной среде?

Если вы можете смириться с случайными сбоями (пропущенные записи), то, возможно, это "не проблема" для вас.

«Схема поэтапного переключения» — это безопасная альтернатива, необязательно в сочетании с «Экстремальным дизайном».

Схема поэтапного переключения

Если у вас много потоков, одновременно выполняющих INSERT в одну таблицу подготовки, то вот эффективный способ обработки большой нагрузки: иметь процесс, который переключает эту таблицу подготовки на другую, идентичную, таблицу подготовки, и выполняет массивную нормализацию, вставку фактов и массированное обобщение.

Шаг переключения использует быструю, атомную операцию переименования.

Вот набросок кода:

    # Prep for flip:
    CREATE TABLE new LIKE Staging;

    # Swap (flip) Staging tables:
    RENAME TABLE Staging TO old, new TO Staging;

    # Normalize new `foo`s:
    # (autocommit = 1)
    INSERT IGNORE INTO Foos SELECT fpp FROM old LEFT JOIN Foos ...

    # Prep for possible deadlocks, etc
    while...
    START TRANSACTION;

    # Add to Fact:
    INSERT INTO Fact ... FROM old JOIN Foos ...

    # Summarize:
    INSERT INTO Summary ... FROM old ... GROUP BY ...

    COMMIT;
    end-while

    # Cleanup:
    DROP TABLE old;

Тем временем, загрузка может продолжать запись в таблицу `Staging`. Операции INSERT загрузки будут конфликтовать с переименованием, но будут разрешены плавно, незаметно и быстро.

Насколько быстро следует выполнять переключение? Вероятно, лучшая схема —

• Иметь задачу, которая выполняет переключение в быстром цикле (без задержки или с небольшой задержкой между итерациями) и
• Иметь CRON, который служит только для поддержания работоспособности, чтобы перезапустить задачу в случае ее сбоя.

Если таблица `Staging` большая, итерация займёт больше времени, но будет работать эффективнее. Таким образом, она саморегулирующаяся.

В среде Galera (или InnoDB Cluster?) каждый узел может получать входные данные. Если вы можете себе позволить потерять несколько строк, пусть `Staging` будет нереплицированной таблицей MEMORY. В противном случае имейте одну таблицу `Staging` на узел и сделайте её InnoDB; это будет более безопасно, но медленнее и не без проблем. В частности, если узел полностью выйдет из строя, вам нужно как-то обработать его таблицу `Staging`.

Несколько таблиц сводки

• Посмотрите на отчеты, которые вам потребуются.
• Проектируйте таблицу сводки для каждого из них.
• Затем посмотрите на таблицы сводки — вы, вероятно, найдете некоторые сходства.
• Объедините похожие.

Чтобы понять, чего требует отчет, посмотрите на предложение WHERE, которое предоставит данные. Вот несколько примеров, предполагая данные о записях обслуживания автомобилей: операция GROUP BY дает подсказку о том, о чем может быть отчет.

1. WHERE make = ? AND model_year = ? GROUP BY service_date, service_type 2. WHERE make = ? AND model = ? GROUP BY service_date, service_type 3. WHERE service_type = ? GROUP BY make, model, service_date 4. WHERE service_date between ? and ? GROUP BY make, model, model_year

Вам нужно разрешить «импровизированные» запросы? Ну, посмотрите на все импровизированные запросы — они все содержат диапазон дат плюс ещё одну или две вещи. (Я редко вижу что-то настолько ужасное, как «%CL%» для уточнения другого измерения.) Итак, начните с того, что подумаете о дате плюс одном или двух других измерениях как «ключе» в новую таблицу сводки. Затем возникает вопрос о том, какие данные могут быть необходимы — подсчёты, суммы и т. д. В конечном итоге у вас будет небольшой набор таблиц сводки. Затем создайте переднюю часть, чтобы позволить им выбирать только из этих вариантов. Она должна поощрять использование существующих таблиц сводки, а не быть по-настоящему «открытой».

Позже может появиться ещё одно «требование». Итак, создайте ещё одну таблицу сводки. Конечно, может потребоваться день, чтобы первоначально заполнить её.

Игры с таблицами сводки

Нужно ли когда-либо сводить таблицу сводки? Да, но только в крайних случаях. Обычно еженедечный отчёт может быть получен из ежедневной таблицы сводки; создание отдельной еженедельной таблицы сводки не стоит усилий.

Когда стоит разделить таблицу сводки на разделы? Да, в крайних случаях, когда таблица большая и

• нужно очистить старые данные (маловероятно), или
• зачастую запрашиваются «недавние» данные, и индекс(ы) не предотвращают сканирования таблицы (редко). («Обрезка разделов» на помощь.)

См. также

Рик Джеймс любезно разрешил нам использовать эту статью в базе знаний.

Сайт Рика Джеймса содержит другие полезные советы, руководства, оптимизации и советы по отладке.

Исходный источник: http://mysql.rjweb.org/doc.php/summarytables

Примеры

• http://dba.stackexchange.com/a/144723/1876
• http://stackoverflow.com/a/39403194/1766831
• http://stackoverflow.com/a/40310314/1766831