Desculpe pelo longo post!

Eu tenho um banco de dados contendo ~ 30 tabelas (mecanismo InnoDB). Apenas duas dessas tabelas, a saber, "transação" e "turno" são muito grandes (a primeira possui 1,5 milhão de linhas e o turno tem 23 mil linhas). Agora tudo funciona bem e não tenho problemas com o tamanho atual do banco de dados.

No entanto, teremos um banco de dados semelhante (mesmos tipos de dados, design, etc.), mas muito maior, por exemplo, a tabela "transação" terá cerca de1 bilhão de registros (cerca de 2,3 milhões de transações por dia) e estamos pensando em como devemos lidar com esse volume de dados no MySQL? (é leitura e gravação intensiva). Li várias postagens relacionadas para ver se o Mysql (e mais especificamente o mecanismo InnoDB) pode funcionar bem com bilhões de registros, mas ainda tenho algumas perguntas. Algumas dessas postagens relacionadas que li são as seguintes:

O MySQL pode razoavelmente executar consultas em bilhões de linhas?O InnoDB (MySQL 5.5.8) é a escolha certa para bilhões de linhas?Melhor armazenamento de dados para bilhões de linhasQual o tamanho de um banco de dados MySQL antes que o desempenho comece a diminuirPor que o MySQL pode ser lento com tabelas grandes?O Mysql pode lidar com tabelas que conterão cerca de 300 milhões de registros?

O que eu entendi até agora para melhorar o desempenho de tabelas muito grandes:

(para tabelas innoDB, que é o meu caso) aumentando oinnodb_buffer_pool_size (por exemplo, até 80% da RAM). Além disso, encontrei outras configurações de ajuste de desempenho do MySQLaqui no blog perconater índices adequados na tabela (usando EXPLAN em consultas)particionando a tabelaSharding ou clustering do MySQL

Aqui estão minhas perguntas / confusões:

Sobre o particionamento, tenho algumas dúvidas se devemos usá-lo ou não. Por um lado, muitas pessoas sugeriram melhorar o desempenho quando a tabela é muito grande. Por outro lado, eu li muitas postagens dizendo que isso não melhora o desempenho da consulta e não faz com que as consultas sejam executadas mais rapidamente (por exemplo,aqui eaqui) Além disso, eu li emManual de Referência do MySQL esteChaves estrangeiras do InnoDB e particionamento do MySQL não são compatíveis (temos chaves estrangeiras).

Em relação aos índices, no momento eles têm um bom desempenho, mas até onde eu entendi, a indexação de tabelas muito grandes é mais restritiva (como Kevin Bedell mencionou em sua respostaaqui) Além disso, os índices aceleram as leituras enquanto diminuem a gravação (inserção / atualização). Portanto, para o novo projeto semelhante que teremos esse grande banco de dados, devemos primeiro inserir / carregar todos os dados e depois criar índices? (para acelerar a inserção)

Se não podemos usar o particionamento para nossa grande tabela (tabela "transação"), qual é uma opção alternativa para melhorar o desempenho? (exceto configurações variáveis do MySQl, comoinnodb_buffer_pool_size) Devemos usar clusters Mysql? (também temos muitas junções)

EDITAR

Isto é oshow create table declaração para a nossa maior tabela denominada "transação":

  CREATE TABLE `transaction` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `terminal_transaction_id` int(11) NOT NULL,
 `fuel_terminal_id` int(11) NOT NULL,
 `fuel_terminal_serial` int(11) NOT NULL,
 `xboard_id` int(11) NOT NULL,
 `gas_station_id` int(11) NOT NULL,
 `operator_id` text NOT NULL,
 `shift_id` int(11) NOT NULL,
 `xboard_total_counter` int(11) NOT NULL,
 `fuel_type` int(11) NOT NULL,
 `start_fuel_time` int(11) NOT NULL,
 `end_fuel_time` int(11) DEFAULT NULL,
 `preset_amount` int(11) NOT NULL,
 `actual_amount` int(11) DEFAULT NULL,
 `fuel_cost` int(11) DEFAULT NULL,
 `payment_cost` int(11) DEFAULT NULL,
 `purchase_type` int(11) NOT NULL,
 `payment_ref_id` text,
 `unit_fuel_price` int(11) NOT NULL,
 `fuel_status_id` int(11) DEFAULT NULL,
 `fuel_mode_id` int(11) NOT NULL,
 `payment_result` int(11) NOT NULL,
 `card_pan` text,
 `state` int(11) DEFAULT NULL,
 `totalizer` int(11) NOT NULL DEFAULT '0',
 `shift_start_time` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `terminal_transaction_id` (`terminal_transaction_id`,`fuel_terminal_id`,`start_fuel_time`) USING BTREE,
 KEY `start_fuel_time_idx` (`start_fuel_time`),
 KEY `fuel_terminal_idx` (`fuel_terminal_id`),
 KEY `xboard_idx` (`xboard_id`),
 KEY `gas_station_id` (`gas_station_id`) USING BTREE,
 KEY `purchase_type` (`purchase_type`) USING BTREE,
 KEY `shift_start_time` (`shift_start_time`) USING BTREE,
 KEY `fuel_type` (`fuel_type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1665335 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

Obrigado pelo seu tempo,