Perguntas[index](dba)

Vemos em um de nossos serviços que certas consultas sempre resultam em um COLLSCAN, em vez do índice apropriado ser usado. Os dados têm o formato

{_id: DocumentID, "field": {"child": "Cross-App-ID"}}

com um Índice {"field.child":1}.

Executar a consulta para "field.child", ou seja, db.collection.find({"field.child":"CA-ID"})funciona como esperado com o índice sendo usado. No entanto, por algum motivo, o aplicativo que acessa o banco de dados cria as consultas como db.collection.find({"field":{"child":"CA-ID"}}), o que resulta em um COLLSCANem vez de um IXSCAN.

2 perguntas para mim a partir disto:

• Essas consultas não deveriam ser equivalentes e, portanto, tratadas da mesma forma?
• Como posso criar um índice para o segundo caso de uso? Todos os exemplos que encontrei criam o índice como já fizemos.

Temos uma coleção em nosso MongoDB onde, devido a uma alteração no caso de uso, uma consulta até agora pequena agora está muito lenta. O agregado em questão é mais ou menos assim:

db.collection.aggregate([
  {
    $match: {
      "_id": {
        $in: [ list of 1000 ids ]
      }
    }
  },
  {
    $unwind: "array_field"
  },
  {
    $match: {
      "array_field.sub_field": 123456789
    }
  }
])

Minha pergunta agora é: um índice como esse seria {"_id": 1, "array_field.sub_field": 1}levado em consideração aqui ou é necessário fazer com que os desenvolvedores movam o segundo $matchpara o primeiro?

Infelizmente, um retrabalho do modelo de dados não estará disponível em um futuro próximo.

Como criar um índice composto exclusivo que não diferencia maiúsculas de minúsculas no MongoDB?

Corpo da pergunta: Estou tentando criar um índice de texto exclusivo composto no MongoDB com os seguintes campos:

tenant_id: 1 seller_id: 1 seller_sku_code: text. No entanto, estou encontrando um erro ao tentar criar o índice:

Falha na criação do índice: 4df81a22-042f-4e51-bbec-b0f8a7dafe2e:
Coleção product_db.skus ( 83cc8978-b0fe-4c4b-ae58-691e32db7f95 )
:: causado por :: E11000
coleção de erro de chave duplicada: índice product_db.skus: locatário_id_1_seller_id_1_ chave duplicada seller_sku_code_text: {locatário_id: "1", vendedor_id: "113", _fts: "acc", _ftsx: 0,75 }

Preciso garantir que o seller_sku_code seja exclusivo no banco de dados e não faça distinção entre maiúsculas e minúsculas. Por exemplo:

“ABC” e “abc” não devem ser permitidos simultaneamente. "ACC-2001" e "ACC-2000" deveriam poder coexistir.

Como posso criar esse índice exclusivo, que não diferencia maiúsculas de minúsculas, em seller_sku_code e, ao mesmo tempo, garantir a exclusividade dos campos tenant_id e seller_id?

Nossa equipe desenvolveu um aplicativo híbrido baseado no IndexedDBsuporte a plataformas cruzadas. Este aplicativo agora possui um recurso de bate-papo e queremos armazenar bate-papos para legibilidade offline. Mas, como regra geral, podemos armazenar metadados de bate-papos em texto simples, mas não o conteúdo real do texto da mensagem de bate-papo. Então, decidimos criptografá-los.

Problema: Agora o problema surge quando o usuário deseja pesquisar uma mensagem que contenha alguma palavra específica, pode ser qualquer idioma ou texto.

Tentamos pesquisar o que Whatsappimplementa essa funcionalidade e encontramos vários artigos como https://www.group-ib.com/blog/whatsapp-forensic-artifacts/ e alguns outros, mas mostra para aplicativos nativos, sqlitebancos de dados (msgstore.db , wa.db etc) na pasta raiz não são criptografados nem mesmo um único campo. Embora existam alguns bancos de dados criptografados msgstore.db.cryptxx, a maioria deles são backups.

Questões

1. Devemos presumir que o Whatsapp apenas criptografa mensagens enquanto envia e recebe, não as armazena?

2. O WhatsApp armazena mensagens em texto simples na IndexedDBweb? Não encontramos nenhum, exceto metadados .

3. Se o Whatsapp criptografa todas as mensagens, seja sqliteIndexedDB para web, como ele realiza a busca por mensagens?

4. Que opções temos? Como devemos armazenar mensagens no IndexedDB que podem ser criptografadas e ainda pesquisáveis?

Há uma pergunta relacionada a isso , mas não tem a resposta adequada nem é perguntada sobre o IndexedDB, que pode ser facilmente visualizado pelos usuários finais.

Se bem entendi esta resposta, a diferença entre with Indexon Is Unqiuee a Unique Keyé principalmente cosmética.

Na interface MSSQL, quando mudo para uma chave exclusiva, ela Ignore Duplicate Keysé desativada.

Estou entendendo algo errado? Eu gostaria de usar Ignore Duplicate Keysneste caso. Quais são as consequências quando faço um Indexwith Is Uniqueversus a Unique Key.

Digite Indexcom Is Unique(menu suspenso ativado):

Tipo Unique Key(menu suspenso desativado):

Estou no Azure SQL, adicionando particionamento mensal a algumas tabelas de arquivo histórico (criando uma segunda tabela com esquema/função de particionamento e despejando as linhas do original nela). Eu estava usando uma consulta para visualizar todos os números de partição e intervalos de datas e consegui ver tudo depois de inserir os dados na nova tabela, mas depois de adicionar a restrição PK, minha consulta parou de retornar linhas ou se eu comentar as junções na tabela sys.index e abaixo vejo apenas uma única partição. Não tenho certeza de quais modificações precisaria fazer na consulta abaixo.

DDL:

CREATE PARTITION FUNCTION [PF_YearMonthBiWeekly] (datetime)
AS RANGE RIGHT FOR VALUES 
(
    '2024-01-01', '2024-01-16', '2024-02-01', '2024-02-16', '2024-03-01', '2024-03-16', '2024-04-01', '2024-04-16', '2024-05-01', '2024-05-16', '2024-06-01', '2024-06-16', '2024-07-01', '2024-07-16', '2024-08-01', '2024-08-16', '2024-09-01', '2024-09-16', '2024-10-01', '2024-10-16', '2024-11-01', '2024-11-16', '2024-12-01','2024-12-16',
    '2025-01-01'
);
GO

CREATE PARTITION SCHEME PS_YearMonthBiWeekly
AS PARTITION [PF_YearMonthBiWeekly]
ALL TO ([PRIMARY])
GO

CREATE TABLE [dbo].[Table1]
(
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Test_ID] [int] NULL,
    [Metric_ID] [int] NULL,
    [Metric_Desc] [nvarchar](300) NULL  
    [Result_On] [datetime] NULL,
) ON [PS_YearMonthBiWeekly] ([Result_On]);

Insira dados, consulte funcionando e adicione PK:

ALTER TABLE [dbo].[Table1] 
ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ( [ID] ASC )
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

Consulta de visualização de partição:

SELECT
    OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
    ,OBJECT_NAME(pstats.object_id) AS TableName
    ,pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
    ,c.name AS PartitionKey
    ,CASE 
        WHEN pf.boundary_value_on_right = 0 
        THEN c.name + ' > ' + CAST(ISNULL(LAG(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100)) + ' and ' + c.name + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
        ELSE c.name + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100))  + ' and ' + c.name + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER(PARTITION BY pstats.object_id ORDER BY pstats.object_id, pstats.partition_number), 'Infinity') AS VARCHAR(100))
    END AS PartitionRange
    ,pf.name AS PartitionFunctionName
    ,ps.name AS PartitionSchemeName
    ,ds.name AS PartitionFilegroupName
    ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Range Left' ELSE 'Range Right' END AS PartitionFunctionRange
    ,CASE pf.boundary_value_on_right WHEN 0 THEN 'Upper Boundary' ELSE 'Lower Boundary' END AS PartitionBoundary
    ,prv.value AS PartitionBoundaryValue
    ,p.data_compression_desc AS DataCompression
    ,case 
        when i.[type] = 0 then 'Nonclustered Heap Index'
        when i.[type] = 1 then 'Clustered Index'
        when i.[type] = 2 then 'Nonclustered Unique Index'
        when i.[type] = 3 then 'XML Index'
        when i.[type] = 4 then 'Spatial Index'
        when i.[type] = 5 then 'Clustered Columnstore Index'
        when i.[type] = 6 then 'Nonclustered Columnstore Index'
        when i.[type] = 7 then 'Nonclustered Hash Index'
        else NULL
    end as index_type
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p 
    ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds 
    ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds 
    ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps 
    ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf 
    ON ps.function_id = pf.function_id
LEFT OUTER JOIN sys.indexes AS i 
    ON pstats.object_id = i.object_id 
    AND pstats.index_id = i.index_id AND dds.partition_scheme_id = i.data_space_id 
    --AND i.type <= 1 /* Heap or Clustered Index */
INNER JOIN sys.index_columns AS ic 
    ON i.index_id = ic.index_id 
    AND i.object_id = ic.object_id 
    AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c 
    ON pstats.object_id = c.object_id 
    AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv 
    ON pf.function_id = prv.function_id 
    AND pstats.partition_number = (CASE pf.boundary_value_on_right WHEN 0 THEN prv.boundary_id ELSE (prv.boundary_id+1) END)
--WHERE pstats.object_id = OBJECT_ID('Table1')
--ORDER BY TableName, PartitionNumber;
GO 

Usamos manutenção de índice ollas com algumas alterações aqui e ali feitas por nossa equipe.

geralmente temos um problema com um banco de dados enorme onde seu índice Alter bloqueia alguns processos. Normalmente é uma reorganização, mesmo que bloqueie algumas tarefas, como bulks por exemplo.

o que podemos fazer para minimizar isso? nob funciona por mais de 24 horas todos os domingos.

Pensei em dividir tabelas, e fazer manutenção de índices em finais de semana separados.

Pensei em um alerta onde isso interromperia o trabalho daquele bloqueio por X minutos (ruim, pois tenho certeza de que nunca será concluído).

Sou um engenheiro de software com cerca de 10 anos de experiência na construção de aplicações web Laravel para pequenas empresas. Estou bastante confortável com o design de banco de dados, mas esse não é meu maior ponto forte.

Um dos aplicativos que desenvolvi é uma ferramenta de terceiros para monitorar aberturas e cliques em boletins informativos por e-mail. Este aplicativo armazena eventos de abertura e clique, e os usuários da ferramenta consultam esses eventos para ver o desempenho de seus boletins informativos por e-mail. A contagem atual de linhas da tabela de eventos analíticos é de cerca de 1,5 bilhão, o que representa cerca de um ano e meio de dados.

Pretendo migrar para um novo design de banco de dados e gostaria de ver se meu entendimento do design de banco de dados está no caminho certo.

Estas são as tabelas do novo banco de dados:

Table contacts {
  id integer
  email_address string
  created_at datetime
  updated_at datetime
  indexes {
    id [pk]
    email_address
    created_at
  }
}

Table contact_opens {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  opened_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    opened_at
  }
}

Table contact_clicks {
  id unsignedBigInteger
  contact_id integer
  sent_at datetime
  clicked_at datetime
  indexes {
    id [pk]
    (contact_id, sent_at)
    sent_at
    clicked_at
  }
}

Pretendo usar o MariaDB em um servidor linode dedicado com 64 GB de RAM, particionar o banco de dados por mês e ajustar o InnoDB da seguinte maneira:

innodb_buffer_pool_size = 48GB
InnoDB File-Per-Table = ON
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 0_DIRECT
innodb_log_file_size = 8GB
table_open_cache = 20

As consultas neste banco de dados segmentarão os Contatos com base em Aberturas e Cliques. Por exemplo:

• Contatos com 5 ou mais aberturas nos últimos 30 dias
• Contatos com pelo menos 1 clique nos últimos 5 dias
• Contatos com 1 clique em cada um dos últimos 30 dias
• etc., consultas padrão de segmentação de boletins informativos por e-mail (como visto no Mailchimp)

As consultas retornarão de 10 mil a 300 mil contatos por vez.

Fiz muitas pesquisas e parece que indexação, particionamento e/ou fragmentação parecem ser a melhor opção para ajudar a acelerar as consultas. Parece que uma combinação de indexação e particionamento seria mais adequada para este caso de uso específico.

Esse uso parece um design de banco de dados sólido, onde a tabela de aberturas cresce cerca de um bilhão de linhas por ano e a tabela de cliques cresce cerca de 300 milhões de linhas por ano?

Atualização 1: o esquema não será alterado. Ele está mudando um pouco em relação à forma como foi projetado anos atrás, mas apenas para acomodar as alterações descritas nesta questão para tornar as consultas mais rápidas.

Atualização 2: As 300 mil consultas de contato são para correspondências em massa (boletins informativos por e-mail). Atualizados os tipos de dados nas tabelas MySQL acima.

esquema:

{
  time_utc: "milliseconds",
  city: "string",
  age: "integer"
}

índice:

{
  time_utc: 1,
  city: 1,
  age: 1
}

consulta de exemplo:

col.aggregate([
  {
    $match: { time_utc: { $exists: true }  city: "new york", age: { $gt: 18 } }
  },
  {
    $sort: { time_utc: -1 }
  }
])

Tenho certeza que o índice composto será utilizado nesta consulta, para que a classificação seja eficiente.

No entanto, a $matchetapa /filtragem de documentos não parece eficiente porque podemos assumir que time_utcé única em toda a coleção.

Suponhamos que haja 1 milhão de documentos no acervo e apenas 10 serão devolvidos. Suponhamos ainda que os 10 documentos estejam localizados na parte inferior da time_utcclassificação.

Neste cenário, a consulta deve varrer todo o valor de 1 milhão do índice para descobrir os 10 documentos. Isso equivale a uma varredura completa da coleção.

Vamos supor o contrário, que existam apenas 2 valores únicos em time_utc. Neste cenário, parece que o índice para os campos citye agepode de fato ser eficaz.

Meu raciocínio está correto?

Na coleção A, cada documento contém até 100 campos. O banco de dados, mongoDB, permite apenas até 64 índices em uma única coleção.

Os índices são necessários para uma consulta nesta coleção. A etapa filtro, ou $match, desta consulta pode ser feita utilizando qualquer combinação dos 100 campos.

Mesmo que sejam criados 64 índices, ele poderá cobrir no máximo 64 campos.

Problema:

Isso significa que, se a consulta for filtrada com base em qualquer um dos 36 campos não indexados, uma varredura de coleta deverá ser feita para concluir a consulta.

Pergunta:

Existem formas alternativas de otimizar essa consulta, impedindo uma varredura de coleção em todas as combinações possíveis dessa consulta?