AskOverflow.Dev

AskOverflow.Dev Logo AskOverflow.Dev Logo

AskOverflow.Dev Navigation

  • Início
  • system&network
  • Ubuntu
  • Unix
  • DBA
  • Computer
  • Coding
  • LangChain

Mobile menu

Close
  • Início
  • system&network
    • Recentes
    • Highest score
    • tags
  • Ubuntu
    • Recentes
    • Highest score
    • tags
  • Unix
    • Recentes
    • tags
  • DBA
    • Recentes
    • tags
  • Computer
    • Recentes
    • tags
  • Coding
    • Recentes
    • tags
Início / dba / Perguntas / 317863
Accepted
Ross Bush
Ross Bush
Asked: 2022-10-06 05:46:46 +0800 CST2022-10-06 05:46:46 +0800 CST 2022-10-06 05:46:46 +0800 CST

Um índice clusterizado baseado em um campo-chave pode ter melhor desempenho do que um índice de cobertura não clusterizado para uma tabela grande?

  • 772

Estou pesquisando uma tabela de relatórios DW que crescerá muito. Para simplificar, vou mostrar a tabela da seguinte forma:

BigTable
--------
TableID INT IDENTITY NOT NULL,
CompanyName NVARCHAR(100) NOT NULL

Cada consulta usará o nome da empresa para consultar em uma partição de dados (não em uma partição física).

Como essa tabela pode conter mais de um bilhão de linhas e cada empresa terá uma distribuição de dados bastante uniforme, as consultas por empresa devem ser o mais rápidas possível. Estou na fase de configurar alguns testes, mas antes de fazê-lo pensei em perguntar e ver se seria uma perda de tempo.

Minha ideia era determinar que, se a partição de dados de cada empresa fosse colocada lado a lado no disco por meio de um índice clusterizado, a recuperação de dados seria mais rápida do que apenas usar um índice não clusterizado para cobrir CompanyName.

Exemplo 1: Aqui está a variação em que a coluna IDENTITY é o PK, mas não CLUSTERED. O CompanayName e TableID se combinam para formar o Índice Clusterizado para que os dados sejam ordenados por empresa no disco.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY NONCLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE UNIQUE CLUSTERED INDEX [CLUSTERED_ByCompanyName_TableID] ON [dbo].[BigTable]
(
    [CompanyName] ASC,
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

E aqui está a maneira tradicional de criar tabelas com índices de cobertura.

CREATE TABLE [dbo].[BigTable](
    [TableID] [int] IDENTITY(1,1) NOT NULL,
    [CompanyName] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_BigTable] PRIMARY KEY CLUSTERED 
(
    [TableID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ByCompanyName] ON [dbo].[BigTable]
(
    [CompanyName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Alguém sabe imediatamente se haveria alguma melhoria de desempenho a ser obtida usando o primeiro exemplo sobre o segundo exemplo?

EDIT: Estou inclinado a usar um índice clusterizado com a empresa. O TableID é apenas um campo de autoincremento para usar como PK se uma linha precisar de uma referência exclusiva. Eu sinto que as buscas/varreduras de índice agrupadas são mais rápidas do que as buscas/busca(s) de índice.

Eu gostaria que você pudesse particionar facilmente ou fragmentar com base em algo como companyid.

Uma consulta básica seria na forma de

SELECT
    SUM(FieldA) OVER (PARTITION BY ...) a,
    COUNT(1) OVER (PARTITION BY...) b
    ...       
FROM
    BigTable 
WHERE
    CompanyName = 'NABISCO'
GROUP BY
   ....
ORDER BY
   ....
sql-server index-tuning
  • 1 1 respostas
  • 57 Views

1 respostas

  • Voted
  1. Best Answer
    Tibor Karaszi
    2022-10-07T00:48:22+08:002022-10-07T00:48:22+08:00

    Aqui está uma tentativa de resposta, com base nos comentários.

    Em resumo, você diz que sempre filtra o CompanyName para suas consultas.

    Ter um índice clusterizado em CompanyName, TableID pode realmente ser benéfico, pois o SQL Server pode navegar nos "dados" para a empresa certa e ler apenas as linhas dessa empresa específica.

    Considere usar a compactação de dados. E certifique-se de avaliar a compactação de linha e página. As pessoas tendem a esquecer a compactação de linha, mas considerando sua sobrecarga quase inexistente, pode ser um tipo de compactação muito atraente em alguns casos.

    Ter um índice columnstore pode ser ainda mais benéfico. Em parte por causa da taxa de compactação ainda maior em comparação com nenhuma, linha ou compactação de página. Mas também porque é mais provável que você veja o modo de lote para seus operadores no plano de execução. Você pode obter o modo de lote sem índices columnstore em 2019, mas requer nível de compatibilidade de banco de dados de 2019 e Enterprise Edition.

    Você deseja cobrir a consulta com o índice columnstore. Ou um não clusterizado que possui todas as colunas de que suas consultas precisam. Ou provavelmente mais atraente no seu caso, um índice columnstore clusterizado - onde agora você também percebe a economia de armazenamento para o índice columnstore.

    Um aspecto será como as linhas são dispostas nos grupos de linhas (um grupo de linhas tem cerca de 1 milhão de linhas, dependendo de como você carrega novos dados, etc.). Você deseja "agrupar" isso com base na empresa. Procure a empresa A e, se as linhas da empresa A estiverem limitadas a um pequeno conjunto de rowgroups, agora você poderá obter uma boa eliminação de rowgroup em tempo de execução (também conhecida como eliminação de segmento). O SQL Server tem metadados para o valor mais baixo e mais alto de cada coluna e cada grupo de linhas. Ao criar o índice, você garantirá que o SQL Server "aconteça" ler as linhas na ordem desejada - tendo um índice agrupado por linha nessa coluna e criando o índice columnstore usando CREATE INDEX ... WITH DROP EXISTING ( basicamente convertendo o índice clusterizado de linha em um índice clusterizado de coluna).

    Existem limitações em relação à eliminação do grupo de linhas e aos tipos de dados. Acredito que você ainda não tenha eliminação para tipos de dados de string. Ou seja, considere cuidadosamente se esta será uma coluna CompanyName ou CompanyID! A próxima versão está planejada para estender o suporte de tipo para eliminação de rowgroup.

    E depois há o aspecto quando você adiciona dados. Adicione um monte de linhas, que provavelmente são para muitos de seus clientes e eles estarão no(s) mesmo(s) grupo(s) de linhas - e esse grupo de linhas agora terá que ser lido para suas próximas consultas. Ou seja, o índice "degradará" ao longo do tempo se você adicionar dados quando se trata de eliminação de rowgroup, deixando você com a decisão de reconstruir o índice (o que novamente é um pouco complicado até agora porque não temos alguma cláusula ORDER) para refazer -agrupar as linhas com base na empresa.

    • 3

relate perguntas

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

  • Preciso de índices separados para cada tipo de consulta ou um índice de várias colunas funcionará?

  • Quando devo usar uma restrição exclusiva em vez de um índice exclusivo?

  • Quais são as principais causas de deadlocks e podem ser evitadas?

  • Como determinar se um Índice é necessário ou necessário

Sidebar

Stats

  • Perguntas 205573
  • respostas 270741
  • best respostas 135370
  • utilizador 68524
  • Highest score
  • respostas
  • Marko Smith

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

    • 12 respostas
  • Marko Smith

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

    • 3 respostas
  • Marko Smith

    Selecione qual tem data máxima ou data mais recente

    • 3 respostas
  • Marko Smith

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

    • 4 respostas
  • Marko Smith

    Listar todas as colunas de uma tabela especificada

    • 5 respostas
  • Marko Smith

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

    • 4 respostas
  • Marko Smith

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

    • 4 respostas
  • Marko Smith

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

    • 10 respostas
  • Marko Smith

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

    • 4 respostas
  • Marko Smith

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

    • 7 respostas
  • Martin Hope
    Jin conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host 2014-12-02 02:54:58 +0800 CST
  • Martin Hope
    Stéphane Como faço para listar todos os esquemas no PostgreSQL? 2013-04-16 11:19:16 +0800 CST
  • Martin Hope
    Mike Walsh Por que o log de transações continua crescendo ou fica sem espaço? 2012-12-05 18:11:22 +0800 CST
  • Martin Hope
    Stephane Rolland Listar todas as colunas de uma tabela especificada 2012-08-14 04:44:44 +0800 CST
  • Martin Hope
    haxney O MySQL pode realizar consultas razoavelmente em bilhões de linhas? 2012-07-03 11:36:13 +0800 CST
  • Martin Hope
    qazwsx Como posso monitorar o andamento de uma importação de um arquivo .sql grande? 2012-05-03 08:54:41 +0800 CST
  • Martin Hope
    markdorison Como você mysqldump tabela (s) específica (s)? 2011-12-17 12:39:37 +0800 CST
  • Martin Hope
    Jonas Como posso cronometrar consultas SQL usando psql? 2011-06-04 02:22:54 +0800 CST
  • Martin Hope
    Jonas Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL? 2011-05-28 00:33:05 +0800 CST
  • Martin Hope
    Jonas Como faço para listar todos os bancos de dados e tabelas usando o psql? 2011-02-18 00:45:49 +0800 CST

Hot tag

sql-server mysql postgresql sql-server-2014 sql-server-2016 oracle sql-server-2008 database-design query-performance sql-server-2017

Explore

  • Início
  • Perguntas
    • Recentes
    • Highest score
  • tag
  • help

Footer

AskOverflow.Dev

About Us

  • About Us
  • Contact Us

Legal Stuff

  • Privacy Policy

Language

  • Pt
  • Server
  • Unix

© 2023 AskOverflow.DEV All Rights Reserve