Оптимизация таблиц журналирования SQL Server с использованием динамических таблиц размерностей

Проблема:

При создании таблиц журналирования или таблиц только для вставки в SQL Server мы часто храним большие строки, такие как URL-адреса, имена хостов или сообщения об ошибках. По мере роста таблицы мы понимаем, что могли бы обработать эти повторяющиеся значения более эффективно. Хранение дублирующихся значений в миллионах строк неэффективно с точки зрения хранения и может привести к медленным запросам поиска.

Решение:

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

Рассмотрим пример таблицы журналирования:

CREATE TABLE dbo.ExceptionLog
(
  LogID         bigint IDENTITY(1,1),
  Hostname      nvarchar(64),
  URI           nvarchar(255),
  ErrorMessage  nvarchar(255),
  EventTime     datetime2(0) NOT NULL DEFAULT sysutcdatetime(),
  CONSTRAINT    PK_ExceptionLog PRIMARY KEY (LogID)
);

По мере роста таблицы журналирования мы видим, что широкие столбцы (Hostname, URI и ErrorMessage) занимают значительное количество места. Чтобы оптимизировать это, мы можем создать отдельные таблицы размерностей для каждого из этих столбцов:

CREATE TABLE dbo.ExceptionHostnames
(
  HostnameID  bigint IDENTITY(1,1),
  Hostname    nvarchar(64),
  CONSTRAINT  PK_ExceptionHostnames PRIMARY KEY (HostnameID)
);

CREATE TABLE dbo.ExceptionURIs
(
  URIID       bigint IDENTITY(1,1),
  URI         nvarchar(255),
  CONSTRAINT  PK_ExceptionURIs PRIMARY KEY (URIID)
);

CREATE TABLE dbo.ExceptionErrorMessages
(
  ErrorMessageID  bigint IDENTITY(1,1),
  ErrorMessage    nvarchar(255),
  CONSTRAINT      PK_ExceptionErrorMessages PRIMARY KEY (ErrorMessageID)
);

Затем мы можем добавить столбцы с возможными внешними ключами в таблицу журналирования, которые ссылаются на таблицы размерностей:

ALTER TABLE dbo.ExceptionLog ADD
  HostnameID     bigint NULL FOREIGN KEY
                 REFERENCES dbo.ExceptionHostnames(HostnameID),
  URIID          bigint NULL FOREIGN KEY
                 REFERENCES dbo.ExceptionURIs(URIID),
  ErrorMessageID bigint NULL FOREIGN KEY
                 REFERENCES dbo.ExceptionErrorMessages(ErrorMessageID);

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

CREATE TRIGGER dbo.ExceptionLog_DivertDimensions
ON dbo.ExceptionLog
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;

  -- генерация новых идентификаторов
  INSERT dbo.ExceptionHostnames(Hostname) 
    SELECT Hostname FROM inserted AS i WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.ExceptionHostnames 
        WHERE Hostname = i.Hostname
    ) GROUP BY Hostname;

  INSERT dbo.ExceptionURIs(URI) 
    SELECT URI FROM inserted AS i WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.ExceptionURIs 
        WHERE URI = i.URI
    ) GROUP BY URI;

  INSERT dbo.ExceptionErrorMessages(ErrorMessage) 
    SELECT ErrorMessage FROM inserted AS i WHERE NOT EXISTS 
    (
      SELECT 1 FROM dbo.ExceptionErrorMessages 
        WHERE ErrorMessage = i.ErrorMessage
    ) GROUP BY ErrorMessage;

  -- обновление идентификаторов
  UPDATE l SET l.HostnameID = h.HostnameID
    FROM dbo.ExceptionLog AS l
    INNER JOIN dbo.ExceptionHostnames AS h
    ON l.Hostname = h.Hostname
    INNER JOIN inserted AS i ON i.LogID = l.LogID
    WHERE l.HostnameID IS NULL;

  UPDATE l SET l.URIID = u.URIID
    FROM dbo.ExceptionLog AS l
    INNER JOIN dbo.ExceptionURIs AS u
    ON l.URI = u.URI
    INNER JOIN inserted AS i ON i.LogID = l.LogID
    WHERE l.URIID IS NULL;

  UPDATE l SET l.ErrorMessageID = m.ErrorMessageID
    FROM dbo.ExceptionLog AS l
    INNER JOIN dbo.ExceptionErrorMessages AS m
    ON l.ErrorMessage = m.ErrorMessage
    INNER JOIN inserted AS i ON i.LogID = l.LogID
    WHERE l.ErrorMessageID IS NULL;
END

После установки триггера мы можем заполнить таблицы размерностей всеми значениями, которые мы видели до сих пор. Мы можем делать это постепенно, используя дружественные к журналу, непрерывные пакеты:

INSERT dbo.ExceptionHostnames(Hostname) 
   SELECT Hostname FROM dbo.ExceptionLog AS src WHERE NOT EXISTS 
   (
     SELECT 1 FROM dbo.ExceptionHostnames 
       WHERE Hostname = src.Hostname
   ) GROUP BY Hostname;

 INSERT dbo.ExceptionURIs(URI) 
   SELECT URI FROM dbo.ExceptionLog AS src WHERE NOT EXISTS 
   (
     SELECT 1 FROM dbo.ExceptionURIs 
      WHERE URI = src.URI
   ) GROUP BY URI;

 INSERT dbo.ExceptionErrorMessages(ErrorMessage) 
   SELECT ErrorMessage FROM dbo.ExceptionLog AS src WHERE NOT EXISTS 
   (
     SELECT 1 FROM dbo.ExceptionErrorMessages 
       WHERE ErrorMessage = src.ErrorMessage
   ) GROUP BY ErrorMessage;

Наконец, мы можем обновить таблицу журналирования сгенерированными идентификаторами:

UPDATE l SET l.HostnameID = h.HostnameID
  FROM dbo.ExceptionLog AS l
  INNER JOIN dbo.ExceptionHostnames AS h
  ON l.Hostname = h.Hostname
  WHERE l.HostnameID IS NULL;

UPDATE l SET l.URIID = u.URIID
  FROM dbo.ExceptionLog AS l
  INNER JOIN dbo.ExceptionURIs AS u
  ON l.URI = u.URI
  WHERE l.URIID IS NULL;

UPDATE l SET l.ErrorMessageID = m.ErrorMessageID
  FROM dbo.ExceptionLog AS l
  INNER JOIN dbo.ExceptionErrorMessages AS m
  ON l.ErrorMessage = m.ErrorMessage
  WHERE l.ErrorMessageID IS NULL;

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

CREATE VIEW dbo.vExceptionLog
AS
  SELECT l.LogID,
         h.Hostname,
         u.URI,
         m.ErrorMessage,
         l.EventTime
  FROM dbo.ExceptionLog AS l
  INNER JOIN dbo.ExceptionHostnames AS h
    ON l.HostnameID = h.HostnameID
  INNER JOIN dbo.ExceptionURIs AS u
    ON l.URIID = u.URIID
  INNER JOIN dbo.ExceptionErrorMessages AS m
    ON l.ErrorMessageID = m.ErrorMessageID;

Если мы не можем немедленно изменить код приложения, мы можем продолжать заполнять широкие столбцы и постепенно обрабатывать изменение. Во второй части мы рассмотрим способы минимизации влияния на запись и защиты пользователей от основного изменения, пока код приложения не будет обновлен.

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

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

Статья обновлена: 2022-04-08

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on

Оптимизация таблиц журналирования SQL Server с использованием динамических таблиц размерностей

Let's work together