SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

James

Asked: 2023-11-09 08:13:42 +0800 CST2023-11-09 08:13:42 +0800 CST 2023-11-09 08:13:42 +0800 CST

Precisa de ajuda na compactação de dados do SQL Server, espero compactar mais em uma tabela grande

772

Fizemos um bom progresso após nossa pergunta anterior sobre compactação de dados do SQL Server . Para uma das tabelas, compactamos de 20 GB para 2 GB, o que prova que a melhor taxa de compactação pode chegar a 10 vezes.

O tamanho de outra mesa, medido como o espaço reservado da mesa, foi reduzido de 72,55 GB para 67,06 GB. Portanto, seu benefício de compressão não é suficiente se comparado ao melhor caso acima.

Assim como na pergunta anterior, estamos trabalhando em um data warehouse para uma aplicação Magento-v2 e o nome da tabela é msab_magento.sales_order_item.

Esta tabela também contém um grande número de nullvalores em várias colunas.
No entanto, esta tabela contém uma product_optioncoluna em type nvarchar(max)correspondente à mesma coluna na fonte MySQL em type text. E esta coluna armazena dados de texto no formato JSON. Por linha, o comprimento médio da string nesta coluna é 4923, ou 9 KBcomo cada caractere recebe dois bypes nvarchar.

Em relação à coluna JSON, nossas ideias provisórias são:

Como a compactação em nível de página do SQL Server funciona em páginas individuais de tamanho 8 KBcada. Portanto, o tamanho da página é menor que o tamanho da coluna. Então, isso pode afetar a compressão.
Os documentos JSON vêm dos templates na configuração do produto, preenchidos com informações específicas por transação. Caso as transações consecutivas não sejam para o mesmo produto, seus campos JSON serão gerados a partir de templates diferentes e terão chaves diferentes. Isso dificulta a aplicação do algoritmo de compactação do Dicionário.
No entanto, no conjunto de dados global, cada produto terá um grande número de pedidos, portanto as chaves JSON ainda se repetem muito, talvez em intervalos maiores do que o 8 KBtamanho de uma página ( ).

Nossa pergunta:

Queremos tornar a compactação de dados ainda melhor e precisamos de orientações. Lembre-nos também se o gargalo estiver em outra coisa que não seja a coluna JSON.
Achamos que a sales_order_itemtabela corresponde a esse modelo no magento2repositório do GitHub, certo? Também precisamos de dicas sobre como navegar no código-fonte do Magento, se possível.

Consulte também o DDL da tabela em nosso data warehouse nos detalhes abaixo e me avise em caso de dúvidas.

Agradecemos muito quaisquer dicas e sugestões.

Detalhes:

/****** Object:  Table [msab_magento].[sales_order_item]    Script Date: 11/8/2023 5:08:01 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [msab_magento].[sales_order_item](
    [item_id] [bigint] IDENTITY(3233417,1) NOT NULL,
    [order_id] [bigint] NOT NULL,
    [parent_item_id] [bigint] NULL,
    [quote_item_id] [bigint] NULL,
    [store_id] [int] NULL,
    [created_at] [datetime] NOT NULL,
    [updated_at] [datetime] NOT NULL,
    [product_id] [bigint] NULL,
    [product_type] [nvarchar](255) NULL,
    [product_options] [nvarchar](max) NULL,
    [weight] [decimal](12, 4) NULL,
    [is_virtual] [int] NULL,
    [sku] [nvarchar](255) NULL,
    [name] [nvarchar](255) NULL,
    [description] [nvarchar](max) NULL,
    [applied_rule_ids] [nvarchar](max) NULL,
    [additional_data] [nvarchar](max) NULL,
    [is_qty_decimal] [int] NULL,
    [no_discount] [int] NOT NULL,
    [qty_backordered] [decimal](12, 4) NULL,
    [qty_canceled] [decimal](12, 4) NULL,
    [qty_invoiced] [decimal](12, 4) NULL,
    [qty_ordered] [decimal](12, 4) NULL,
    [qty_refunded] [decimal](12, 4) NULL,
    [qty_shipped] [decimal](12, 4) NULL,
    [base_cost] [decimal](12, 4) NULL,
    [price] [decimal](12, 4) NOT NULL,
    [base_price] [decimal](12, 4) NOT NULL,
    [original_price] [decimal](12, 4) NULL,
    [base_original_price] [decimal](12, 4) NULL,
    [tax_percent] [decimal](12, 4) NULL,
    [tax_amount] [decimal](20, 4) NULL,
    [base_tax_amount] [decimal](20, 4) NULL,
    [tax_invoiced] [decimal](20, 4) NULL,
    [base_tax_invoiced] [decimal](20, 4) NULL,
    [discount_percent] [decimal](12, 4) NULL,
    [discount_amount] [decimal](20, 4) NULL,
    [base_discount_amount] [decimal](20, 4) NULL,
    [discount_invoiced] [decimal](20, 4) NULL,
    [base_discount_invoiced] [decimal](20, 4) NULL,
    [amount_refunded] [decimal](20, 4) NULL,
    [base_amount_refunded] [decimal](20, 4) NULL,
    [row_total] [decimal](20, 4) NOT NULL,
    [base_row_total] [decimal](20, 4) NOT NULL,
    [row_invoiced] [decimal](20, 4) NOT NULL,
    [base_row_invoiced] [decimal](20, 4) NOT NULL,
    [row_weight] [decimal](12, 4) NULL,
    [base_tax_before_discount] [decimal](20, 4) NULL,
    [tax_before_discount] [decimal](20, 4) NULL,
    [ext_order_item_id] [nvarchar](255) NULL,
    [locked_do_invoice] [int] NULL,
    [locked_do_ship] [int] NULL,
    [price_incl_tax] [decimal](20, 4) NULL,
    [base_price_incl_tax] [decimal](20, 4) NULL,
    [row_total_incl_tax] [decimal](20, 4) NULL,
    [base_row_total_incl_tax] [decimal](20, 4) NULL,
    [discount_tax_compensation_amount] [decimal](20, 4) NULL,
    [base_discount_tax_compensation_amount] [decimal](20, 4) NULL,
    [discount_tax_compensation_invoiced] [decimal](20, 4) NULL,
    [base_discount_tax_compensation_invoiced] [decimal](20, 4) NULL,
    [discount_tax_compensation_refunded] [decimal](20, 4) NULL,
    [base_discount_tax_compensation_refunded] [decimal](20, 4) NULL,
    [tax_canceled] [decimal](12, 4) NULL,
    [discount_tax_compensation_canceled] [decimal](20, 4) NULL,
    [tax_refunded] [decimal](20, 4) NULL,
    [base_tax_refunded] [decimal](20, 4) NULL,
    [discount_refunded] [decimal](20, 4) NULL,
    [base_discount_refunded] [decimal](20, 4) NULL,
    [free_shipping] [int] NOT NULL,
    [qty_returned] [decimal](12, 4) NOT NULL,
    [gift_message_id] [int] NULL,
    [gift_message_available] [int] NULL,
    [weee_tax_applied] [nvarchar](max) NULL,
    [weee_tax_applied_amount] [decimal](12, 4) NULL,
    [weee_tax_applied_row_amount] [decimal](12, 4) NULL,
    [weee_tax_disposition] [decimal](12, 4) NULL,
    [weee_tax_row_disposition] [decimal](12, 4) NULL,
    [base_weee_tax_applied_amount] [decimal](12, 4) NULL,
    [base_weee_tax_applied_row_amnt] [decimal](12, 4) NULL,
    [base_weee_tax_disposition] [decimal](12, 4) NULL,
    [base_weee_tax_row_disposition] [decimal](12, 4) NULL,
    [gw_id] [int] NULL,
    [gw_base_price] [decimal](12, 4) NULL,
    [gw_price] [decimal](12, 4) NULL,
    [gw_base_tax_amount] [decimal](12, 4) NULL,
    [gw_tax_amount] [decimal](12, 4) NULL,
    [gw_base_price_invoiced] [decimal](12, 4) NULL,
    [gw_price_invoiced] [decimal](12, 4) NULL,
    [gw_base_tax_amount_invoiced] [decimal](12, 4) NULL,
    [gw_tax_amount_invoiced] [decimal](12, 4) NULL,
    [gw_base_price_refunded] [decimal](12, 4) NULL,
    [gw_price_refunded] [decimal](12, 4) NULL,
    [gw_base_tax_amount_refunded] [decimal](12, 4) NULL,
    [gw_tax_amount_refunded] [decimal](12, 4) NULL,
    [event_id] [int] NULL,
    [giftregistry_item_id] [int] NULL,
    [business_area] [nvarchar](max) NULL,
    [custom_options_has_private_data] [smallint] NULL,
    [auto_ship] [smallint] NULL,
 CONSTRAINT [PK_sales_order_item_item_id] PRIMARY KEY CLUSTERED 
(
    [item_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, DATA_COMPRESSION = PAGE) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [order_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [parent_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [quote_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [store_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (getdate()) FOR [created_at]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (getdate()) FOR [updated_at]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [product_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [product_type]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [weight]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [is_virtual]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [sku]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [name]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [is_qty_decimal]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [no_discount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_backordered]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_canceled]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_ordered]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_shipped]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_cost]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [original_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_original_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [tax_percent]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [tax_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_tax_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [discount_percent]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [discount_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_discount_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [discount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_discount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [row_total]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_row_total]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [row_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [base_row_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [row_weight]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_tax_before_discount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [tax_before_discount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [ext_order_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [locked_do_invoice]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [locked_do_ship]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [price_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_price_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [row_total_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_row_total_incl_tax]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_tax_compensation_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_tax_compensation_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_tax_compensation_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [tax_canceled]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_tax_compensation_canceled]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [tax_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_tax_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [discount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_discount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [free_shipping]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0.0000)) FOR [qty_returned]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gift_message_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gift_message_available]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_applied_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_applied_row_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [weee_tax_row_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_applied_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_applied_row_amnt]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [base_weee_tax_row_disposition]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_price]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_tax_amount]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_price_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_price_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_tax_amount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_tax_amount_invoiced]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_price_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_price_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_base_tax_amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [gw_tax_amount_refunded]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [event_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [giftregistry_item_id]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT (NULL) FOR [custom_options_has_private_data]
GO

ALTER TABLE [msab_magento].[sales_order_item] ADD  DEFAULT ((0)) FOR [auto_ship]
GO

EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE', @value=N'msab_magento.sales_order_item' , @level0type=N'SCHEMA',@level0name=N'msab_magento', @level1type=N'TABLE',@level1name=N'sales_order_item'
GO

1 respostas

Voted

LoztInSpace · Answer 1 · 2023-11-09T13:48:27+08:00

Best Answer

LoztInSpace

2023-11-09T13:48:27+08:002023-11-09T13:48:27+08:00

Tenha em mente que seu varchar(max) é armazenado fora da tabela e não será compactado com os modos de compactação "apertar o botão".

Considerar

a) Retirar o JSON e armazená-lo como dados relacionais, se possível

b) alterar sua coluna para varbinary(max) e adicionar uma etapa de compactação no nível do seu código, como gatilho ou SP. https://learn.microsoft.com/en-us/sql/t-sql/functions/compress-transact-sql?view=sql-server-ver16

To be honest, a 20GB database does not sound worth compressing.

1

Precisa de ajuda na compactação de dados do SQL Server, espero compactar mais em uma tabela grande

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Precisa de ajuda na compactação de dados do SQL Server, espero compactar mais em uma tabela grande

1 respostas

relate perguntas