Usei o guia de particionamento e indexação de Rick James para criar isso alguns anos atrás, mas agora, depois de ler o tutorial novamente, não tenho tanta certeza se entendi corretamente e se a tabela abaixo é a ideal. ( referência do guia )
Acho que um dos índices normais é redundante e não tenho certeza se devo deixar de fora a coluna de chave de particionamento (data/hora) ou alterar um dos índices para ter a coluna de chave de particionamento (data/hora) por último. Tê-lo como parte do índice ajudará no desempenho ao selecionar um intervalo de data/hora específico dentro de uma partição?
Também seria melhor ter a chave primária (deviceService, datetime) e ter um índice no incremento automático (id)?
A tabela baseada em séries temporais abaixo tem cerca de 80+ milhões de linhas por partição mensal. Ela é quase sempre consultada para um intervalo de data/hora específico e para um subconjunto de deviceServiceId's.
O particionamento é puramente para arquivamento e manutenção de tabelas e não para desempenho.
CREATE TABLE `serviceResultLinkState`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`deviceServiceId` int(10) unsigned NOT NULL,
`dateTime` datetime NOT NULL DEFAULT current_timestamp(),
`priority` tinyint(1) unsigned NOT NULL,
`alias` varchar(20) NOT NULL,
`active` tinyint(3) unsigned DEFAULT NULL,
`stable` tinyint(3) unsigned DEFAULT NULL,
`attempts` int(5) unsigned DEFAULT NULL,
`retries` int(5) unsigned DEFAULT NULL,
`resultState` tinyint(3) unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (`id`,`dateTime`),
KEY `dt_ds_idx` (`deviceServiceId`),
KEY `datetime_idx` (`dateTime`,`deviceServiceId`)
) ENGINE=InnoDB AUTO_INCREMENT=24814201874 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
PARTITION BY RANGE COLUMNS(`dateTime`)
(PARTITION `p_202405` VALUES LESS THAN ('2024-06-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202406` VALUES LESS THAN ('2024-07-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202407` VALUES LESS THAN ('2024-08-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202408` VALUES LESS THAN ('2024-09-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202409` VALUES LESS THAN ('2024-10-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202410` VALUES LESS THAN ('2024-11-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_202411` VALUES LESS THAN ('2024-12-01 00:00:00') ENGINE = InnoDB,
PARTITION `p_max` VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB);
idpode não ser necessário se você tiver o PK acima. (Novamente, depende das consultas.) Seidfor necessário, então,INDEX(id)além do PK acima, é suficiente paraAUTO_INCREMENT.WHERE x IN (...) AND y BETWEEN...é difícil de otimizar. O particionamento ajudará em alguns casos, não em outros.EXPLAIN SELECT ...mostrará quais partições estão sendo usadas para a consulta, mas não dará muitas pistas de quão bem oINouBETWEENestá sendo manipulado dentro de cada partição que ele usa.Mais
deviceServiceId,datetimemelhoraria o tempo de pesquisa de seleção." -- Sim. Veja minha discussão aqui: https://mysql.rjweb.org/doc.php/mysql_sensoridaté mesmo para operações de "manutenção". Em várias situações,idleva a overhead desnecessário.WHERE x IN (...) AND y BETWEEN...pode ser substituível por umUNION ALL. Depende.Ainda mais
"não tenho certeza se a tabela é ótima" Ótima para quê? Velocidade? Espaço? Inserções? Atualizações? Seleções? A tabela parece que vai "funcionar".
(A velocidade da seleção é mais uma preocupação, pois é usada principalmente para traçar um gráfico de série temporal de métricas para um deviceServiceId específico. Então, eu estava pensando se ter um PK por deviceServiceId,datetime melhoraria o tempo de pesquisa de seleção. Veja a consulta principal mais abaixo)
"A definição da tabela sugere que ela pode ser purgada após cerca de 6 meses. Se for assim, o que você tem parece bom." (Correto)
"acho que um dos índices normais é redundante" -- Os 3 (incluindo o PK) que você tem não são redundantes. Alguns podem não ser usados, essa é uma pergunta diferente. Sem ver as consultas principais, não posso responder a essa pergunta.
(Este é o tipo mais comum de consulta executada nesta tabela que tem dados por minuto de intervalo. Pode ter uma variação de data e hora dependendo do período em que os dados são consultados:
select sr.dateTime, sr.alias, sr.resultState from serviceResultLinkState sr where sr.deviceServiceId = 567403 and dateTime >= curdate() - interval 24 hour; )
"chave primária (deviceService, datetime)" — Se esse par for necessariamente "único", então pode ser uma PK melhor.
(Acho que será único, pois as métricas são persistidas a cada minuto para um deviceServiceId, então o registro de data e hora deve ser único)
"id pode não ser necessário se você tiver o PK acima. (Novamente, depende das consultas.) Se id for necessário, então INDEX(id) além do PK acima é suficiente para AUTO_INCREMENT."
(Indexar em id foi benéfico no passado, pois permite abordar um bloco específico de dados para operações de tipo de manutenção usando intervalo de id de forma mais rápida, mas talvez um pk modificado tenha o mesmo benefício?)
"Tê-lo como parte do índice ajudará no desempenho ao selecionar um intervalo de data/hora específico dentro de uma partição?" Talvez. Deixe-me ver a consulta. A questão se resume a "Este índice composto é útil para esta consulta?". Eu discuto "qual é o melhor índice composto para esta consulta" em um Index Cookbook complementar "consultado para um intervalo de data/hora específico e para um subconjunto de deviceServiceId's" -- WHERE x IN (...) AND y BETWEEN... é difícil de otimizar. O particionamento ajudará em alguns casos, não em outros. EXPLAIN SELECT ... mostrará quais partições estão sendo usadas para a consulta, mas não dará muitas pistas de quão bem o IN ou BETWEEN está sendo tratado dentro de cada partição que ele usa.
(O IN não é, na verdade, comum para acessar esta tabela - para processos de resumo de rollup, eu abordaria cada devicveServiceId individualmente ou juntaria em outra tabela filtrada para a lista de deviceServiceId's em que estamos interessados)
"O particionamento é puramente para arquivamento e manutenção de tabelas, e não para desempenho." -- Isso parece anular o restante de suas perguntas.
(Quase sempre consultaríamos esta tabela para dados recentes, então a única vez que ela talvez abrangeria duas partições seria, por exemplo, olhando os dados dos últimos 7 dias, que poderiam ter alguns dias na partição anterior)
Esses são dados "sênior"? Se sim, tenho mais comentários.
(Não tenho certeza do que você quer dizer com isso?)