Хранение документов JSON в SQL Server или базе данных SQL
SQL Server и база данных SQL Azure имеют собственные функции JSON, позволяющие анализировать документы JSON с помощью стандартного языка SQL. Вы можете хранить документы JSON в SQL Server или Базе данных SQL и запрашивать данные JSON так же, как в базе данных NoSQL. Эта статья описывает возможности хранения документов JSON в SQL Server или базе данных SQL.
Формат хранения JSON
При проектировании хранилища прежде всего нужно решить, как хранить документы JSON в таблицах. Доступны два варианта:
- Хранилище LOB позволяет хранить документы JSON без преобразования в столбцах NVARCHAR . Это лучший способ быстрой загрузки и приема данных, так как скорость загрузки соответствует скорости загрузки строковых столбцов. Этот подход может привести к дополнительным штрафам производительности во время запроса или анализа, если индексирование значений JSON не выполняется, так как необработанные документы JSON должны быть проанализированы во время выполнения запросов.
- Реляционное хранилище позволяет с помощью функций OPENJSON , JSON_VALUE или JSON_QUERY анализировать документы JSON во время их вставки в таблицу. Фрагменты входных документов JSON могут храниться в столбцах типа данных SQL или в столбцах NVARCHAR, содержащих вложенные элементы JSON. Этот подход увеличивает время загрузки, так как анализ JSON выполняется во время загрузки; однако запросы соответствуют производительности классических запросов реляционных данных.
Классические таблицы
Для хранения документов JSON в SQL Server или базе данных SQL проще всего создать обычную таблицу с двумя столбцами, содержащую идентификатор и содержимое документа. Например:
create table WebSite.Logs ( _id bigint primary key identity, log nvarchar(max) ); Эта структура эквивалентна коллекциям из классических баз данных документов. Первичный ключ _id — это значение с автоматическим приращением, представляющее собой уникальный идентификатор каждого документа для быстрого поиска. Такая структура хорошо подходит для классических сценариев NoSQL, когда получение или изменение хранимого документа осуществляются по идентификатору.
Тип данных nvarchar(max) позволяет хранить документы JSON размером до 2 ГБ. Если вы уверены, что размер документов JSON не превышает 8 КБ, рекомендуем вместо NVARCHAR(max) использовать NVARCHAR(4000) для большей производительности.
В образце таблицы, созданном в предыдущем примере, предполагается, что в столбце log хранятся допустимые документы JSON. Если вы хотите убедиться, что в столбце log хранятся допустимые документы JSON, добавьте для столбца ограничение CHECK. Например:
ALTER TABLE WebSite.Logs ADD CONSTRAINT [Log record should be formatted as JSON] CHECK (ISJSON(log)=1) Каждый раз, когда кто-то вставляет или изменяет документ в таблице, это ограничение проверяет, что документ JSON имеет правильный формат. Без этого ограничения таблица будет оптимизирована для операций вставки, так как любой документ JSON добавляется в столбец напрямую, без обработки.
При хранении в таблице документов JSON вы можете выполнять запросы к документам с помощью стандартного языка Transact-SQL. Например:
SELECT TOP 100 JSON_VALUE(log, '$.severity'), AVG( CAST( JSON_VALUE(log,'$.duration') as float)) FROM WebSite.Logs WHERE CAST( JSON_VALUE(log,'$.date') as datetime) > @datetime GROUP BY JSON_VALUE(log, '$.severity') HAVING AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) > 100 ORDER BY AVG( CAST( JSON_VALUE(log,'$.duration') as float) ) DESC Возможность использования любой функции и предложения запроса T-SQL для запросов к документам JSON является существенным преимуществом. SQL Server и база данных SQL не вводят никаких ограничений к запросам, используемым для анализа документов JSON. Вы можете извлекать из документа JSON значения с помощью функции JSON_VALUE и использовать их в запросе, как любые другие значения.
Наличие полнофункционального синтаксиса запросов T-SQL является ключевым отличием SQL Server и базы данных SQL от классических баз данных NoSQL: в Transact-SQL вам, скорее всего, доступны все необходимые функции для обработки данных JSON.
Индексы
Если окажется, что ваши запросы часто выполняют в документах поиск по определенному свойству (например, по свойству severity в документе JSON), добавьте к свойству классический индекс NONCLUSTERED, чтобы ускорить обработку запросов.
Вы можете создать вычисляемый столбец, который предоставляет значения JSON из столбцов JSON по заданному пути (то есть, по пути $.severity ), и создать стандартный индекс в этом столбце. Например:
create table WebSite.Logs ( _id bigint primary key identity, log nvarchar(max), severity AS JSON_VALUE(log, '$.severity'), index ix_severity (severity) ); Используемый в этом примере вычисляемый столбец является несохраняемым или виртуальным столбцом, который не добавляет дополнительное место в таблицу. Индекс ix_severity использует его для повышения производительности запросов, как показано в следующем примере.
SELECT log FROM Website.Logs WHERE JSON_VALUE(log, '$.severity') = 'P4' Важное свойство этого индекса — учет параметров сортировки. Если исходный столбец NVARCHAR имеет свойство COLLATION (например, для учета регистра или японского языка), индекс расставляется в соответствии с правилами языка или правилами учета регистра, связанными со столбцом NVARCHAR. Такой учет параметров сортировки может оказаться важным при разработке приложений для международного рынка, в которых нужно использовать особые языковые правила при обработке JSON-документов.
Формат columnstore больших таблиц &
Если в вашей коллекции планируется большое количество документов JSON, рекомендуем добавить в нее индекс CLUSTERED COLUMNSTORE, как показано в следующем примере.
create sequence WebSite.LogID as bigint; go create table WebSite.Logs ( _id bigint default(next value for WebSite.LogID), log nvarchar(max), INDEX cci CLUSTERED COLUMNSTORE ); Индекс CLUSTERED COLUMNSTORE обеспечивает высокую степень сжатия данных (максимум в 25 раз), которая позволит значительно снизить требования к дисковому пространству, сократить расходы на хранение и повысить производительность операций ввода-вывода рабочей нагрузки. Кроме того, индексы CLUSTERED COLUMNSTORE оптимизированы для сканирования таблиц и анализа документов JSON, поэтому они могут быть наилучшим выбором для аналитики журналов.
В примере выше для присвоения значений столбцу _id используется объект последовательности. И последовательности, и идентификаторы являются приемлемыми вариантами для этого столбца.
Часто изменяющие таблицы, оптимизированные для памяти документов &
Если в коллекциях ожидается множество операций изменения, вставки и удаления, вы можете хранить документы JSON в таблицах, оптимизированных для памяти. Оптимизированные для памяти коллекции JSON всегда хранят данные в памяти, что позволяет избежать нагрузки, связанной с операциями ввода-вывода в хранилище. Кроме того, эти коллекции абсолютно неблокируемые, то есть действия с документами не препятствуют никаким другим операциям.
Единственное, что вам нужно сделать для преобразования классической коллекции в коллекцию, оптимизированную для памяти, — это указать параметр with (memory_optimized=on) после определения таблицы, как показано в следующем примере. После этого вы получите оптимизированную для памяти версию коллекции JSON.
create table WebSite.Logs ( _id bigint identity primary key nonclustered, log nvarchar(4000) ) with (memory_optimized=on) Таблица, оптимизированная для памяти, — лучший вариант для часто изменяемых документов. При их внедрении также учитывайте производительность. Если возможно, используйте в своих оптимизированных для памяти коллекциях NVARCHAR(4000) вместо NVARCHAR(max) для документов JSON, так как это может значительно увеличить производительность.
Как и с классическими таблицами, вы можете добавлять индексы в поля, предоставляемые в оптимизированных для памяти таблицах, с помощью вычисляемых столбцов. Например:
create table WebSite.Logs ( _id bigint identity primary key nonclustered, log nvarchar(4000), severity AS cast(JSON_VALUE(log, '$.severity') as tinyint) persisted, index ix_severity (severity) ) with (memory_optimized=on) Чтобы обеспечить максимальную производительность, приведите значение JSON к наименьшему возможному типу, который можно использовать для хранения значения свойства. В примере выше используется tinyint.
Вы также можете поместить запросы SQL, которые изменяют документы JSON, в хранимые процедуры, чтобы воспользоваться преимуществами компиляции в машинный код. Например:
CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100)) WITH SCHEMABINDING, NATIVE_COMPILATION AS BEGIN ATOMIC WITH (transaction isolation level = snapshot, language = N'English') UPDATE WebSite.Logs SET log = JSON_MODIFY(log, @Property, @Value) WHERE _id = @Id; END Эта скомпилированная в машинный код процедура принимает запрос и создает .DLL-код, который выполняет запрос. Она является самым быстрым способом для создания запросов и изменения данных.
Заключение
Собственные функции JSON в SQL Server и базе данных SQL позволяют работать с документами JSON так же, как в базах данных NoSQL. Каждая база данных (реляционная или NoSQL) обладает рядом преимуществ и недостатков в обработке данных JSON. Основное преимущество хранения документов JSON в SQL Server или базе данных SQL — это полная поддержка языка SQL. Вы можете использовать широкие возможности языка Transact-SQL для обработки данных и настройки множества параметров хранения (от индексов columnstore для высокой степени сжатия и быстрого анализа до оптимизированных для памяти таблиц, обеспечивающих обработку без блокирования). Кроме того, вам доступны обширные возможности обеспечения безопасности и оптимизации под различные рынки, которые можно легко переносить в сценарии NoSQL. Изложенные выше причины являются веским доводом в пользу хранения документов JSON в SQL Server или базе данных SQL.
Дополнительные сведения о JSON в SQL Server и базе данных SQL Azure
Видео Майкрософт
Некоторые ссылки на видео в этом разделе могут не работать в данный момент. Корпорация Майкрософт переносит содержимое, которое ранее транслировалось канале Channel 9, на новую платформу. Мы будем обновлять ссылки по мере переноса видео на новую платформу.
Наглядные инструкции по встроенной поддержке JSON в SQL Server и базе данных SQL Azure см. в следующих видео.
- JSON as a bridge between NoSQL and relational worlds (JSON как мост между NoSQL и реляционными решениями)
Руководство по архитектуре страниц и экстентов
Страница является основной единицей хранилища данных в SQL Server. Экстент представляет собой коллекцию из восьми физически смежных страниц. Экстенты помогают эффективно управлять страницами. В этом руководстве описаны структуры данных, используемые для управления страницами и экстентами во всех версиях SQL Server. Понимание архитектуры страниц и экстентов важно для проектирования и разработки эффективных баз данных.
Страницы и экстенты
Основная единица хранения данных в SQL Server — это страница. Пространство на диске, выделенное файлу данных (MDF или NDF) в базе данных, логически делится на страницы, нумеруемые последовательно от 0 до n. Дисковые операции ввода-вывода выполняются на уровне страницы. Это означает, что SQL Server считывает или записывает целые страницы данных.
Экстент — это коллекция, состоящая из восьми физически непрерывных страниц; они используются для эффективного управления страницами. Все страницы организуются в экстенты.
Страницы
В обычной книге все содержимое написано на страницах. Как и в книге, SQL Server записывает все строки данных на страницах, и все страницы данных имеют одинаковый размер: 8 КБ. В книге большинство страниц содержат данные — основное содержимое книги, а некоторые страницы содержат метаданные о содержимом (например, оглавление и индекс). Опять же, SQL Server не отличается: большинство страниц содержат фактические строки данных, хранящиеся пользователями; они называются страницами данных и текстовыми и изображениями (для особых случаев). Страницы индекса содержат ссылки на индексы о том, где находится данные. Наконец, есть системные страницы , которые хранят различные метаданные о организации данных.
Каждая страница начинается с 96-байтового заголовка, который используется для хранения системных данных о странице. Эти данные включают номер страницы, тип страницы, объем свободного места на странице и идентификатор единицы распределения объекта, которому принадлежит страница.
В следующей таблице представлены типы страниц, используемые в файлах данных базы данных SQL Server.
Файлы журналов не содержат страниц. Они содержат ряд записей журнала, которые не имеют фиксированного размера.
Строки данных хранятся на странице последовательно, начиная с заголовка. Таблица смещения строк начинается в конце страницы; каждая таблица смещения строк содержит одну запись для каждой строки на странице. Каждая запись смещения строки сохраняет байт первой байт строки с начала страницы. Таким образом, функция таблицы смещения строк заключается в том, чтобы SQL Server быстро найти строки на странице. Записи в таблице смещения строк находятся в обратном порядке относительно последовательности строк на странице.
Поддержка больших строк
Строки не могут охватывать страницы; однако части строки могут быть перемещены со страницы строки, поэтому строка может быть очень большой. Максимальный объем данных и затрат, содержащихся в одной строке на странице, составляет 8 060 байт. Это не включает данные, хранящиеся в типе страницы текста или изображения.
Это ограничение непринуждается для таблиц, содержащих столбцы varchar, nvarchar, varbinary или sql_variant столбцов. Если общий размер строки всех фиксированных и переменных столбцов таблицы превышает ограничение на 8 060 байтов, SQL Server динамически перемещает один или несколько столбцов переменной длины на страницы в единице выделения ROW_OVERFLOW_DATA, начиная с столбца с наибольшей шириной.
Это действие выполняется всегда, когда в результате операций вставки или обновления общий размер строки выходит за предел в 8060 байт. Когда происходит перемещение столбца на страницу в единице распределения ROW_OVERFLOW_DATA, 24-байтовый указатель на исходной странице в единице распределения IN_ROW_DATA сохраняется. Если при последующей выполняемой операции размер строк уменьшается, SQL Server динамически перемещает столбцы обратно на исходную страницу данных.
Рекомендации по переполнению строк
Строка не может находиться на нескольких страницах и может переполнение, если совокупный размер полей типа данных переменной длины превышает ограничение в 8060 байтов. Чтобы проиллюстрировать, можно создать таблицу с двумя столбцами: один varchar(7000) и другой varchar (2000). По отдельности ни один столбец не превышает 8060 байт, но в сочетании они могли бы сделать это, если ширина каждого столбца заполнена. SQL Server может динамически переместить столбец переменной varchar(7000) на страницы в единице выделения ROW_OVERFLOW_DATA. При объединении столбцов типа varchar, nvarchar, varbinary или sql_variant или CLR, превышающих 8 060 байт на строку, рассмотрим следующее:
- Перемещение больших записей на другую страницу осуществляется динамически, по мере удлинения записей при операциях обновления. Операции обновления, которые укорачивают записи, могут привести к возвращению записей на исходную страницу в единице распределения IN_ROW_DATA. Выполнение запросов и других операций выборки, например сортировки и соединения, в отношении больших записей с превышающими размер страницы данными строки, увеличивает время обработки, поскольку эти записи обрабатываются синхронно, а не асинхронно. Поэтому при разработке таблицы с несколькими столбцами типа varchar, nvarchar,varbinary или sql_variant или CLR, определяемых пользователем, учитывайте процент строк, которые, скорее всего, будут передаваться и частоту, с которой эти данные переполнения, скорее всего, будут запрашиваться. Если ожидаются частые запросы по многим превышающим размер страницы данным строки, рекомендуется нормализовать таблицу таким образом, чтобы некоторые столбцы переместились в другую таблицу. После этого запросы по таблице можно будет выполнять с помощью асинхронной операции JOIN.
- Длина отдельных столбцов по-прежнему должна находиться в пределах 8 000 байт для столбцов типа varchar, nvarchar , varbinary или sql_variant и clR, определяемых пользователем. И только общая их длина может выходить за предел в 8 060 байт на строку таблицы.
- Сумма других столбцов типа данных, включая данные char и nchar , должна соответствовать ограничению строки 8060 байтов. Данные больших объектов также могут выходить за предел в 8 060 байт на строку.
- Ключ кластеризованного индекса не может включать в себя столбцы varchar, для которых существуют данные в единице размещения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается для столбца типа varchar и существующие данные располагаются в единице размещения IN_ROW_DATA, то все последующие операции вставки или обновления для данного столбца, выталкивающие данные за пределы строки, будут завершаться ошибкой. Дополнительные сведения об единицах распределения см . в руководстве по архитектуре и проектированию индексов.
- Пользователь может включить столбцы, которые содержат превышающие размер страницы данные строки, в качестве ключевых или неключевых столбцов некластеризованного индекса.
- Максимальный размер записи в таблицах, в которых используются разреженные столбцы, составляет 8 018 байт. Если суммарная величина преобразуемых данных и существующих данных записи превышает 8 018 байт, то возвращается ошибка MSSQLSERVER ERROR 576. При преобразовании столбцов между разреженными и непарспарными типами ядро СУБД сохраняет копию текущих данных записи. В связи с этим удваивается количества места, которое требуется для хранения записи.
- Для получения сведений о таблицах или индексах, которые могут содержать превышающие размер страницы данные строки, используется функция динамического управления sys.dm_db_index_physical_stats.
Экстенты
Экстенты являются основными единицами организации пространства. Экстент состоит из восьми непрерывных страниц или 64 КБ. Это означает, что базы данных SQL Server имеют 16 экстентов на мегабайт.
В SQL Server есть два типа экстентов.
- Однородные экстенты принадлежат одному объекту, и все восемь страниц экстента может использовать только этот владеющий объект.
- Смешанные экстенты могут находиться в общем пользовании максимум у восьми объектов. Каждая из восьми страниц в экстенте может находиться во владении разных объектов.
Вплоть до SQL Server 2014 (12.x), ядро СУБД не выделяет целые экстенты в таблицах с небольшими объемами данных. Новая таблица или индекс обычно выделяет страницы из смешанных экстентов. При увеличении размера таблицы или индекса до восьми страниц эти таблица или индекс переходят на использование однородных экстентов для последовательных единиц распределения. При создании индекса для существующей таблицы, в которой содержится достаточно строк, чтобы сформировать восемь страниц в индексе, все единицы распределения для индекса находятся в однородных экстентах.
Начиная с SQL Server 2016 (13.x), по умолчанию для большинства выделений в пользовательской базе данных и tempdb используется единообразные экстенты, за исключением выделения, принадлежащие первым восьми страницам цепочки IAM. Выделения для master баз msdb model данных и баз данных по-прежнему сохраняют предыдущее поведение.
В SQL Server вплоть до SQL Server 2014 (12.x) можно использовать флаг трассировки (TF) 1118, чтобы изменить выделение по умолчанию, чтобы всегда использовать универсальные экстенты. Дополнительные сведения об этом флаге трассировки см. в статье DBCC TRACEON — флаги трассировки (Transact-SQL).
Начиная с SQL Server 2016 (13.x), функции, предоставляемые TF 1118, автоматически включены для tempdb всех пользовательских баз данных. Для пользовательских баз данных это поведение управляется SET MIXED_PAGE_ALLOCATION ALTER DATABASE параметром , при этом значение по умолчанию имеет значение OFF, а TF 1118 не влияет. Дополнительные сведения см. в статье Параметры ALTER DATABASE SET (Transact-SQL).
Начиная с SQL Server 2012 (11.x), системная sys.dm_db_database_page_allocations функция может сообщать сведения о выделении страниц для базы данных, таблицы, индекса и секции.
Системная функция sys.dm_db_database_page_allocations не задокументирована и может быть изменена. Совместимость не гарантируется.
Начиная с SQL Server 2019 (15.x), системная функция sys.dm_db_page_info доступна и возвращает сведения о странице в базе данных. Функция возвращает одну строку, содержащую сведения о заголовке со страницы, включая object_id , index_id и partition_id . В большинстве случаев эта функция заменяет потребность в использовании DBCC PAGE .
Управление выделением экстентов и свободным пространством
Структуры данных SQL Server, управляющие размещением экстентов и отслеживанием свободного места, имеют сравнительно простую организацию. Учтите следующие преимущества:
- Сведения о свободном месте плотно упакованы, поэтому эти данные содержат относительно небольшое количество страниц. Это повышает скорость, уменьшая количество операций чтения дисков, необходимых для получения сведений о выделении. Также увеличивается вероятность того, что страницы размещения будут оставаться в памяти и повторных операций чтения не потребуется.
- Большая часть информации о выделении не объединяется. Это упрощает управление сведениями о размещении. Каждое действие по размещению или освобождению страницы может выполняться быстро. Это сокращает состязание между одновременными задачами размещения и освобождения страниц.
Управление выделением экстентов
SQL Server использует два типа карт размещения для записи размещения экстентов:
- Глобальная карта распределения (GAM) На GAM-страницах записано, какие экстенты были размещены. В каждой карте GAM содержится 64 000 экстентов или почти 4 ГБ данных. GAM имеет 1 бит для каждого экстента в интервале, который он охватывает. Если бит имеет 1 значение, степень свободна; если бит имеет значение 0 , то выделяется экстент.
- Общая глобальная карта распределения (SGAM) На SGAM-страницах записано, какие экстенты в текущий момент используются в качестве смешанных экстентов и имеют как минимум одну неиспользуемую страницу. В каждой карте SGAM содержится 64 000 экстентов или почти 4 ГБ данных. SGAM имеет 1 бит для каждого экстента в интервале, который он охватывает. Если бит имеет значение 1 , экстент используется в качестве смешанной экстенты и имеет бесплатную страницу. Если бит имеет 0 значение, экстент не используется в качестве смешанной экстенты или используется смешанный экстент и все его страницы.
Каждый экстент обладает следующими наборами битовых шаблонов в картах GAM и SGAM, основанными на его текущем использовании.
| Текущее использование экстента | Настройка битов карты GAM | Настройка битов карты SGAM |
|---|---|---|
| Свободно, в текущий момент не используется | 1 | 0 |
| Однородный экстент или заполненный смешанный экстент | 0 | 0 |
| Смешанный экстент со свободными страницами | 0 | 1 |
Это дает простые алгоритмы управления экстентами страниц.
- Чтобы выделить единую степень, ядро СУБД выполняет поиск GAM для бита 1 и задает для него значение 0 .
- Чтобы найти смешанный экстент с бесплатными страницами, ядро СУБД выполняет поиск SGAM немного 1 .
- Чтобы выделить смешанную степень, ядро СУБД выполняет поиск GAM для 1 бита, задает для него 0 значение, а затем задает соответствующий бит в SGAM 1 .
- Чтобы освободить степень, ядро СУБД гарантирует, что для бита GAM задано значение , а для бита SGAM задано 1 значение 0 .
Алгоритмы, которые используются внутри ядра СУБД, более сложны, чем описано в этой статье, так как ядро СУБД равномерно распределяет данные в базе данных. Однако даже настоящие алгоритмы упрощаются из-за того, что отпадает необходимость управления цепочками сведений о размещении экстентов.
Отслеживание свободного места
Страницы Page Free Space (PFS) записывают состояние размещения каждой страницы, информацию о том, была ли размещена конкретная страница, а также количество свободного места на каждой странице. PFS имеет 1 байт для каждой страницы, записывая, выделяется ли страница, и если да, будь то пустая, от 1 до 50 процентов полной, 51 до 80 процентов полной, 81 до 95 процентов полной или 96 до 100 процентов полной.
После размещения экстента на объект ядро СУБД использует PFS-страницы для записи информации о том, какие страницы в экстенте размещены, а какие свободны. Эти сведения используются ядром СУБД при размещении новой страницы. Объем свободного места на странице сохраняется только для кучи и страниц текста или изображения. Это используется при поиске ядром СУБД страницы, имеющей свободное место, которого достаточно для сохранения в ней новой добавляемой строки. Индексы не требуют отслеживания свободного места на странице, так как точка вставки новой строки устанавливается значениями ключа индекса.
В файл данных добавляется новая страница PFS, GAM или SGAM для каждого дополнительного диапазона, который отслеживается. Таким образом, после первой PFS-страницы находится новая PFS-страница с 8088 страницами, а также дополнительные PFS-страницы с последующими интервалами в 8088 страниц. Допустим, страница с идентификатором 1 является PFS-страницей, страница с идентификатором 8088 является PFS-страницей, страница с идентификатором 16176 является PFS-страницей и т. д.
После первой GAM-страницы имеется новая GAM-страница с 64 000 экстентов, которая отслеживает 64 000 экстентов за ней. Последовательность продолжится с интервалом в 64 000. Аналогичным образом после первой SGAM-страницы стоит новая SGAM-страница с 64 000 экстентов, и SGAM-страницы добавляются каждые 64 000 экстентов.
На иллюстрации ниже показана последовательность страниц, используемая ядром СУБД для выделения экстентов и управления ими.
Управление пространством, используемым объектами
Страница карты распределения индекса (Index Allocation Map, IAM) сопоставляет экстенты в 4-гигабайтном фрагменте файла базы данных с единицей размещения, использующей этот фрагмент. Единица распределения может иметь один из трех типов.
- IN_ROW_DATA Содержит секцию кучи или индекса.
- LOB_DATA Содержит типы данных больших объектов (LOB), такие как xml, varbinary(max), и varchar(max).
- ROW_OVERFLOW_DATA Содержит данные переменной длины, хранящиеся в varchar, nvarchar, varbinary или sql_variant столбцах, превышающих ограничение размера строки в 8 060 байтов.
В каждой секции кучи или индекса содержится по крайней мере одна единица распределения IN_ROW_DATA. Кроме того, в зависимости от схемы кучи или индекса, там могут содержаться единицы распределения LOB_DATA или ROW_OVERFLOW_DATA.
IAM-страница охватывает в файле диапазон 4 ГБ, то есть столько же, сколько и GAM- или SGAM-страница. Если в единице распределения содержатся экстенты из более чем одного файла или фрагмент файла размером более 4 ГБ, то несколько IAM-страниц будут объединены в IAM-цепочку. Таким образом, каждая единица распределения содержит как минимум одну IAM-страницу для каждого из файлов, в которых содержатся ее экстенты. Для файла может существовать несколько IAM-страниц, если размер экстентов файла, назначенного единице распределения, превышает объем, который может быть записан в одной IAM-странице.
IAM-страницы для каждой единицы распределения выделяются по необходимости и располагаются в файле в случайном порядке. Системное представление sys.system_internals_allocation_units указывает на первую страницу IAM единицы размещения. Все страницы IAM, относящиеся к одной единице размещения, объединяются в цепочку IAM.
Системное представление sys.system_internals_allocation_units предназначено только для внутреннего использования и может быть изменено. Совместимость не гарантируется. Это представление недоступно в Базе данных SQL Azure.
Страница IAM содержит заголовок, указывающий начальную степень диапазона экстентов, сопоставленных страницей IAM. IAM-страница также имеет большую битовую карту, в которой каждый бит представляет экстент. Первый бит схемы представляет первый экстент диапазона, второй бит — второй экстент и т. д. Если это немного 0 , то степень, которую она представляет, не выделяется единицам выделения, принадлежащим IAM. Если бит имеет значение 1 , то степень, которую она представляет, выделяется единицам выделения, принадлежащим странице IAM.
Когда ядро СУБД должно вставить новую строку и нет свободного места на текущей странице, она использует страницы IAM и PFS для поиска страницы выделения или для кучи или страницы текста или изображения, страницы с достаточным пространством для хранения строки. Ядро СУБД использует IAM-страницы для поиска экстентов, привязанных к единице распределения. Для каждого экстента ядро СУБД просматривает PFS-страницы, чтобы определить наличие страниц, которые можно использовать. Каждая страница IAM и PFS охватывает множество страниц данных, поэтому в базе данных есть несколько страниц IAM и PFS. Это означает, что IAM- и PFS-страницы обычно находятся в памяти буферного пула SQL Server и поиск в них осуществляется очень быстро. Для индексов точка вставки новой строки определяется ключом индекса, но если нужна новая страница, происходит описанный выше процесс.
Ядро СУБД выделяет новую степень единицы выделения только в том случае, если она не может быстро найти страницу в существующем экстенте с достаточным пространством для вставки строки.
Пропорциональное выделение заливки
Ядро СУБД выделяет экстенты из доступных в файловой группе с помощью алгоритма распределения пропорциональной заливки. В одной файловой группе с двумя файлами, если один файл имеет двойное свободное пространство, так как другой, два страницы будут выделены из файла с доступным пространством для каждой страницы, выделенной из другого файла. Это означает, что процент заполнения для каждого из файлов в группе будет примерно одинаковым.
Отслеживание измененных экстентов
SQL Server использует две внутренние структуры данных для отслеживания экстентов, измененных операциями массового копирования, и экстентов, измененных с момента последнего полного резервного копирования. Эти структуры данных существенно ускоряют разностные резервные копии. Они также ускоряют операции записи в журнал массового копирования, если база данных использует модель восстановления с неполным протоколированием. Как и страницы GAM и SGAM, эти структуры представляют собой растровые изображения, в которых каждый бит представляет один экстент.
- Схема разностных изменений (Differential Changed Map, DCM) Эта схема отслеживает экстенты, которые были изменены со времени последнего выполнения инструкции BACKUP DATABASE . Если бит для экстента является 1 , то степень была изменена с момента последнего BACKUP DATABASE оператора. Если бит имеет значение 0 , экстент не был изменен. Чтобы определить, какие экстенты были изменены, разностные резервные копии считывают только страницы DCM. Это существенно сокращает количество страниц, которые должна просмотреть разностная резервная копия. Продолжительность выполнения разностной резервной копии пропорциональна количеству экстентов, измененных с момента последнего BACKUP DATABASE оператора, а не общему размеру базы данных.
- Схема массовых изменений (Bulk Changed Map, BCM) Это отслеживает экстенты, которые были изменены операциями массового ведения журнала с момента последнего BACKUP LOG оператора. Если бит для экстента 1 является, то степень была изменена операцией массового ведения журнала после последней BACKUP LOG инструкции. Если бит имеет значение 0 , экстент не был изменен операциями с массовым ведением журнала. Несмотря на то, что страницы BCM существуют во всех базах данных, они соответствуют только в том случае, если база данных использует модель восстановления с неполным протоколированием. В этой модели восстановления при выполнении инструкции BACKUP LOG процесс резервного копирования проверяет схемы BCM на наличие измененных экстентов. Затем она включает в себя экстенты из резервной копии журнала. Это восстанавливает операции массового ведения журнала, если база данных восстанавливается из резервной копии базы данных и последовательности резервных копий журналов транзакций. Страницы BCM не относятся к базе данных, используюшей простую модель восстановления, так как операции массового ведения журнала не регистрируются. Они не относятся к базе данных, которая использует модель полного восстановления, так как эта модель восстановления обрабатывает операции массового ведения журнала как полностью зарегистрированные операции.
Интервал между DCM- и BCM-страницами равен интервалу между GAM- и SGAM-страницами — 64 000 экстентов. Страницы DCM и BCM находятся за страницами GAM и SGAM в физическом файле следующим образом:
См. также
- sys.allocation_units (Transact-SQL)
- Кучи (таблицы без кластеризованных индексов)
- sys.dm_db_page_info
- Считывание страниц
- Запись страниц
Какие существуют способы хранения файлов в sql базах данных?
Как лучше хранить файлы в sql базе данных? Хранить сами файлы(картинки, текстовые файлы, аудио файлы) или хранить в базе сслыку на эти файлы в системе? Каким способом лучше реализовать тот или иной способ?
Отслеживать
81.2k 7 7 золотых знаков 72 72 серебряных знака 153 153 бронзовых знака
задан 13 авг 2020 в 23:04
Denver Toha Denver Toha
2,561 1 1 золотой знак 11 11 серебряных знаков 29 29 бронзовых знаков
Зависит от конкретной СУБД. Например, в Sql Server есть и третий способ: FILESTREAM — сочетает в себе преимущества обоих.
14 авг 2020 в 0:25
скорее всего лусше ссылку.
18 авг 2020 в 7:36
4 ответа 4
Сортировка: Сброс на вариант по умолчанию
Не касаясь вопроса а_на_фига_это_вообще_надо отвечу на прямой вопрос:
Какие существуют способы хранения файлов в sql базах данных?
За все способы не скажу, но я лично использовал такой способ:
- Заголовочная таблица с метаданными файла, поля типа:
- Идентификатор файла
- Название файла
- mime тип файла
- размер файла
- timestamp’ы lastmodified/created
- checksum файла
- список тегов
- Ссылка 1 ко многим на таблицу с контентом файла с полями
- Первичный ключ
- Идентификатор файла
- порядковый номер куска/chunk’а
- BLOB поле
Обращаю внимание, что поле BLOB является стандартным типом поддерживаемым практически любой SQL СУБД.
Работает это так:
- Берем файл
- Определяем его метаданные и пишем в заголовочную таблицу
- Открываем файл делим его на куски и куски пишем в список BLOB полей
P.S. Для любителей говорить о том, что типа страдает скорость приведу маленькую справочку — файл в файловой системе любой ОС организован как БД. То есть заголовочек и есть списочек контента файла на которые хранятся ссылки
Отслеживать
ответ дан 14 авг 2020 в 11:28
81.2k 7 7 золотых знаков 72 72 серебряных знака 153 153 бронзовых знака
порядок кусков еще?
14 авг 2020 в 12:04
Конечно, порядок тоже нужен
14 авг 2020 в 12:32
Да если данные нужны в базе данных то храните в блоб. Если нет то чем плох вариант не хранить их вовсе?
16 авг 2020 в 18:13
Давай попробую ответить.
Смысл хранить данные в базе имеется только если с ними работать на прямую.
Например: у вас есть модуль/функция который который может найти, определить, сгруппировать файлы по содержимому по типу плагият или нет. Или какой нибудь по типу google картинок который находит схожие изображения. Короче говоря вы будите использовать содержимое.
Если нет то есть смысл хранить только ссылки, поскольку разрастание базы не ведёт к улучшению производительности. Сохранение и считывание таких данных будет отнимать всегда лишнее время, когда как ссылки могут спокойно существовать и без самих файлов, при обращении к которым будет вежливо указывается идти лесом.
Структуры хранения данных в SQL Server 7.0
Все страницы имеют заголовок размером 96 байтов. В заголовке хранится общая информация, используемая ядром СУБД для работы со страницами. На странице в отличие от блока хранится однородная информация. Поэтому среди параметров страницы задаются:
- номер страницы в формате ;
- идентификатор объекта, которому принадлежит страница;
- номер индекса, которому принадлежит страница;
- уровень внутри индексного дерева, которому принадлежит страница;
- количество отведенных строк на странице, количество заполненных слотов;
- общий объем свободного пространства на странице;
- указатель на расположение свободного пространства после последней строки на странице;
- минимальная длина строки на странице;
- объем зарезервированного пространства.
После заголовка следует информация о статусе страницы в картах распределения блоков и карте свободного пространства.
Новыми в архитектуре дисковой памяти являются страницы размещения. В этих страницах хранятся сведения о размещении данных. SQL Server 7.0 использует три типа страниц размещения: карты распределения блоков, карты свободного пространства, индексные карты размещения. SQL Server 7.0 хранит информацию размещения на разных уровнях: на уровне блоков, на уровне страниц, на уровне объектов. Такой разносторонний мониторинг помогает СУБД оптимизировать работу в соответствии с требованиями конкретного запроса.
Карты распределения блоков
В данных картах хранится информация о распределении блоков. Карта распределения блоков состоит из стандартного заголовка и одного битового массива в 64 000 битов. Каждый бит характеризует один блок. Поэтому одна страница карты распределения описывает пространство в 64 000 блоков или 4 Гбайт данных.
Карты распределения блоков делятся на два типа:
- Глобальная карта распределения (Global allocation map, GAM) хранит информацию об использовании блоков. Если бит установлен в 0, то блок занят данными, если в 1 — то блок свободен.
- Вторичная глобальная карта распределения (Secondary global allocation map, SGAM) хранит информацию о типе блоков. Если бит установлен в 1, то блок смешанный и минимум одна страница в нем свободна, в остальных случаях (блок свободен, блок смешанный, но свободных страниц нет, блок однородный) бит равен 0.
При отведении пространства сервер использует обе карты распределения.
Карты свободного пространства
Степень заполнения страниц в SQL 7.0 отслеживает специальный механизм — карты свободного пространства (Page free space page, PFS). Каждая PFS-страни-ца хранит информацию о 8000 страниц, по 1 байту на страницу. Каждый байт представляет собой битовую карту, которая сообщает о степени занятости страницы и о том, принадлежит ли она объекту.
Первые страницы файла БД всегда используются под карты распределения. Страница № 1 состоит из двух частей. После стандартного заголовка страницы следует заголовок файла, содержащий его описание, затем размешается блок PFS. Страницы PFS повторяются через каждые 8000 страниц, если размер файла
превосходит один блок. Страница № 2 — это GAM, страница № 3 — это SGAM. Карты распределения блоков повторяются через каждые 512 000 страниц. Кроме того, каждая девятая страница первичного файла — это загрузочная страница БД (database boot page), содержащая описание БД и параметры конфигурации.
Для организации связи между блоками и расположенными на них объектами используются индексные карты размещения (Index Allocation Map, IАМ). Каждая таблица или индекс имеют одну или более страниц IАМ. В каждом файле, в котором размещаются таблица или индекс, существует минимум одна карта размещения для этой таблицы или индекса. Страницы IАМ размещаются про,-извольно внутри файла и отводятся по мере необходимости. IAM объединены друг с другом в цепочку двунаправленными ссылками. Указатель на первую карту размещения содержится в поле FirstIAM системной таблицы Sysindex.
Каждая IAM описывает некоторый диапазон блоков и представляет собой битовую карту: если бит установлен в 1, то в данном блоке есть страницы, принадлежащие данному объекту, если в 0 — то нет.
Все страницы размещения не связаны напрямую с некоторым объектом БД, они соответствуют некоторой системной информации, поэтому параметр «идентификатор объекта» для всех этих страниц одинаков и равен 99.
На самом общем уровне здесь, так же как и в предыдущей версии, страница данных делится на 3 зоны: заголовок, область данных и таблицу смещений, но размер страницы увеличен, размер заголовка также и некоторые отличия существуют и в структуре остальных зон.
Прежде всего стоит отметить, что в отличие от SQL Server 6.5 в новой версии страницы данных не связаны друг с другом в цепочки. За связь между страницами и объектами отвечает новая специальная структура — карты размещения.
Кроме того, ранее данные на странице хранились непрерывно. При удалении строки данные внутри страницы перемещались так, чтобы не оставалось пустот. Однако такой подход затруднял строчные блокировки. И в новой версии данные на странице не обязательно хранятся непрерывно. Здесь допустимы пропуски. При удалении строки пустое пространство помечается и потом его может занять новая строка, но перемещения строк не происходит.
В SQL Server 7.0 теперь поддерживается классический термин слот (Slot), и это место размещения строки на странице. Если таблица не имеет кластеризованного индекса, то номер слота является идентификатором строки и не меняется, пока не будет удалена соответствующая строка. Если же таблица имеет кластеризованный индекс, то слоты располагаются в порядке, задаваемом индексом.
Строки данных претерпели существенное изменение. Отметим наиболее важные моменты.
- Номера строки больше нет — строка идентифицируется номером слота, который ее определяет, либо значением кластерного ключа.
- В версии 6.5 поля, допускающие NULL, хранятся точно так же, как поля переменной длины. В версии 7.0 поля фиксированной длины всегда занимают свою полную длину, значение NULL задается специальным флагом. Это облегчает замену неопределенного значения на некоторое конкретное без перемещения строк на странице.
- Фиксированные поля вместе с описателями хранятся до полей переменной длины, так же как и в 6.5.
- В каждой строке хранится общая длина строки и текущие длины полей переменной длины. Отсутствуют таблицы смещений и подстройки смещений. Данные считываются последовательно с начального адреса.
- Максимальное количество полей в строке 1024, в версии 6.5 только 256.
В версии 7.0 изменены принципы хранения текстовых полей. Строки данных по-прежнему содержат 16-байтные указатели на текстовые данные. Однако хранение самих текстовых данных производится иначе.
Текстовая страница теперь может содержать несколько текстовых полей. Собственно данные хранятся в виде сбалансированного дерева (B-tree). Строка данных содержит указатель на корневую структуру (Root structure) размером 84 байта.
Данные длиной менее 64 байт хранятся в корневой структуре. Для данных до 32 Кбайт корневая структура (Root structure) может адресовать 4 блока данных (это не блоки страниц) до 8 Кбайт каждый. Блоки наращиваются до 8 Кбайт (реально на одной текстовой странице может быть размещено 8080 байт). Например, если первая порция данных составляет 4 Кбайта, то отводится один блок. Если в дальнейшем данные увеличиваются до 6 Кбайт, то первый блок увеличивается до 6 Кбайт, а второй блок имеет размер всего 2 Кбайта.
Если же длина текстового поля более 32 Кбайт, то строятся промежуточные узлы.
В версии 7.0 текстовая страница может содержать данные нескольких текстовых полей (рис. 9.18).
Страницы журнала транзакций
В отличие от версии 6.5 в новой версии под журнал транзакций отводится всегда отдельный файл. В предыдущей версии журнал транзакций являлся системной таблицей и хранился в системном сегменте LOG. Несмотря на настоятельные рекомендации располагать журнал транзакций и файлы базы данных на разных физических устройствах, СУБД автоматически не контролировала этот факт. В новой версии журнал транзакций всегда хранится в отдельном файле.
Рис. 9.18. Пример хранения текстовых данных на одной странице
Знаете ли Вы, что cогласно релятивистской мифологии «гравитационное линзирование — это физическое явление, связанное с отклонением лучей света в поле тяжести. Гравитационные линзы обясняют образование кратных изображений одного и того же астрономического объекта (квазаров, галактик), когда на луч зрения от источника к наблюдателю попадает другая галактика или скопление галактик (собственно линза). В некоторых изображениях происходит усиление яркости оригинального источника.» (Релятивисты приводят примеры искажения изображений галактик в качестве подтверждения ОТО — воздействия гравитации на свет)
При этом они забывают, что поле действия эффекта ОТО — это малые углы вблизи поверхности звезд, где на самом деле этот эффект не наблюдается (затменные двойные). Разница в шкалах явлений реального искажения изображений галактик и мифического отклонения вблизи звезд — 10 11 раз. Приведу аналогию. Можно говорить о воздействии поверхностного натяжения на форму капель, но нельзя серьезно говорить о силе поверхностного натяжения, как о причине океанских приливов.
Эфирная физика находит ответ на наблюдаемое явление искажения изображений галактик. Это результат нагрева эфира вблизи галактик, изменения его плотности и, следовательно, изменения скорости света на галактических расстояниях вследствие преломления света в эфире различной плотности. Подтверждением термической природы искажения изображений галактик является прямая связь этого искажения с радиоизлучением пространства, то есть эфира в этом месте, смещение спектра CMB (космическое микроволновое излучение) в данном направлении в высокочастотную область. Подробнее читайте в FAQ по эфирной физике.