Quando sua tabela tem 30 milhões de registros no MySQL

Imagine que você é um padeiro que precisa fatiar os pães produzidos. Agora multiplique isso por 3.000. É assim que se sente quando sua tabela MySQL cresce para 30 milhões de linhas. De repente, aquela consulta rápida vira uma espera eterna, como esperar o pão crescer em pleno inverno.

Por que 30 milhões de registros são diferentes?

Uma tabela pequena é como sua lista de compras – você encontra tudo rapidamente. Contudo, 30 milhões de registros são como a biblioteca de Alexandria: encontrar um livro específico exige organização e estratégia. O MySQL precisa de ajustes especiais para não ficar sobrecarregado, similar a como um padeiro profissional precisa de equipamentos industriais para produzir milhares de pães diariamente.

Preparando o terreno: configurando o MySQL para grandes volumes

Antes de mergulhar nos dados, precisamos ajustar nosso “forno” para assar pães em larga escala. As configurações padrão do MySQL são como um forno doméstico – perfeito para um bolo, mas insuficiente para uma padaria industrial.

Passos:

Feche o MySQL
abra o CMD como ADMINISTRADOR
Parando o MySQL pelo CMD

net stop MySQL80

1	net stop MySQL80

Navegar para a pasta de configuração onde está o arquivo my.ini

# Possível local
cd C:\ProgramData\MySQL\MySQL Server 8.0

1 2	# Possível local cd C:\ProgramData\MySQL\MySQL Server 8.0

faça um Backup do my.ini original

# Abra o arquivo de configuração e 
notepad my.ini
# salve em outro local com nome backup_my_ini.txt ANTES DE CONTINUAR

# Abra o arquivo de configuração e

notepad my.ini

# salve em outro local com nome backup_my_ini.txt ANTES DE CONTINUAR

Acessando my.ini em modeo edição

# Editar o arquivo de configuração
notepad my.ini

1 2	# Editar o arquivo de configuração notepad my.ini

Minha receita para a minha situação

Assim como diferentes tipos de pão exigem temperaturas e tempos específicos, diferentes cargas de trabalho precisam de configurações personalizadas. Principalmente, focaremos no innodb_buffer_pool_size – a memória que o MySQL usa para armazenar dados frequentemente acessados.

No meu caso tenho uma tabela vendas_medicamentos com 30468924 de linhas que preciso fazer o group by por ANO_VENDA, MUNICIPIO_VENDA, PRINCIPIO_ATIVO.

O meu my.ini ficou desta forma:

[client]
default-character-set = utf8mb4
port=3306

[mysql]
no-beep

[mysqld]

# ======================
# CONFIGURAÇÕES OTIMIZADAS PARA 41M+ LINHAS
# ======================

# Buffer Pool (CRÍTICO - use 2GB para segurança)
innodb_buffer_pool_size = 2G

# Logs para grandes transações
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M

# Performance vs Durability (importante para ETL)
innodb_flush_log_at_trx_commit = 2

# Memória para operações GROUP BY
tmp_table_size = 256M
max_heap_table_size = 256M

# Buffers de ordenação
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
join_buffer_size = 4M

# Timeouts maiores para queries longas
net_read_timeout = 1800
net_write_timeout = 1800
wait_timeout = 28800
interactive_timeout = 28800

# Otimizações para queries grandes
max_allowed_packet = 256M

# Máximo de conexões
max_connections = 100

# ======================
# CONFIGURAÇÕES GERAIS
# ======================
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data
character-set-server=utf8mb4
collation-server = utf8mb4_unicode_ci
authentication_policy=*,,
default-storage-engine=INNODB
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="TORRECOUGAR-AMJ.log"
slow-query-log=1
slow_query_log_file="TORRECOUGAR-AMJ-slow.log"
long_query_time=10
log-error="TORRECOUGAR-AMJ.err"
log-bin="TORRECOUGAR-AMJ-bin"
server-id=1
lower_case_table_names=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"
max_connections=151
table_open_cache=4000
temptable_max_ram=1G
internal_tmp_mem_storage_engine=TempTable
myisam_max_sort_file_size=2146435072
myisam_sort_buffer_size=153M
key_buffer_size=8M

# ⚠️ CONFIGURAÇÕES COMENTADAS - JÁ DEFINIDAS ACIMA
# read_buffer_size=128K
# read_rnd_buffer_size=256K
# innodb_flush_log_at_trx_commit=1
# innodb_log_buffer_size=16M
# innodb_buffer_pool_size=128M

innodb_redo_log_capacity=100M
innodb_thread_concurrency=9
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
flush_time=0

# CONFIGURAÇÕES ESSENCIAIS PARA QUERIES GRANDES
max_execution_time = 0
innodb_buffer_pool_chunk_size = 128M

# ⚠️ CONFIGURAÇÕES COMENTADAS - JÁ DEFINIDAS ACIMA  
# join_buffer_size=256K

max_allowed_packet=64M
max_connect_errors=100
open_files_limit=8161

# ⚠️ CONFIGURAÇÃO COMENTADA - JÁ DEFINIDA ACIMA
# sort_buffer_size=256K

binlog_row_event_max_size=8K
sync_source_info=10000
sync_relay_log=10000
mysqlx_port=33060

100

101

102

103

104

105

106

107

108

109

110

[client]

default-character-set = utf8mb4

port=3306

[mysql]

no-beep

[mysqld]

# ======================

# CONFIGURAÇÕES OTIMIZADAS PARA 41M+ LINHAS

# ======================

# Buffer Pool (CRÍTICO - use 2GB para segurança)

innodb_buffer_pool_size = 2G

# Logs para grandes transações

innodb_log_file_size = 256M

innodb_log_buffer_size = 32M

# Performance vs Durability (importante para ETL)

innodb_flush_log_at_trx_commit = 2

# Memória para operações GROUP BY

tmp_table_size = 256M

max_heap_table_size = 256M

# Buffers de ordenação

sort_buffer_size = 4M

read_buffer_size = 2M

read_rnd_buffer_size = 2M

join_buffer_size = 4M

# Timeouts maiores para queries longas

net_read_timeout = 1800

net_write_timeout = 1800

wait_timeout = 28800

interactive_timeout = 28800

# Otimizações para queries grandes

max_allowed_packet = 256M

# Máximo de conexões

max_connections = 100

# ======================

# CONFIGURAÇÕES GERAIS

# ======================

port=3306

datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data

character-set-server=utf8mb4

collation-server = utf8mb4_unicode_ci

authentication_policy=*,,

default-storage-engine=INNODB

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

log-output=FILE

general-log=0

general_log_file="TORRECOUGAR-AMJ.log"

slow-query-log=1

slow_query_log_file="TORRECOUGAR-AMJ-slow.log"

long_query_time=10

log-error="TORRECOUGAR-AMJ.err"

log-bin="TORRECOUGAR-AMJ-bin"

server-id=1

lower_case_table_names=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

max_connections=151

table_open_cache=4000

temptable_max_ram=1G

internal_tmp_mem_storage_engine=TempTable

myisam_max_sort_file_size=2146435072

myisam_sort_buffer_size=153M

key_buffer_size=8M

# ⚠️ CONFIGURAÇÕES COMENTADAS - JÁ DEFINIDAS ACIMA

# read_buffer_size=128K

# read_rnd_buffer_size=256K

# innodb_flush_log_at_trx_commit=1

# innodb_log_buffer_size=16M

# innodb_buffer_pool_size=128M

innodb_redo_log_capacity=100M

innodb_thread_concurrency=9

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

flush_time=0

# CONFIGURAÇÕES ESSENCIAIS PARA QUERIES GRANDES

max_execution_time = 0

innodb_buffer_pool_chunk_size = 128M

# ⚠️ CONFIGURAÇÕES COMENTADAS - JÁ DEFINIDAS ACIMA

# join_buffer_size=256K

max_allowed_packet=64M

max_connect_errors=100

open_files_limit=8161

# ⚠️ CONFIGURAÇÃO COMENTADA - JÁ DEFINIDA ACIMA

# sort_buffer_size=256K

binlog_row_event_max_size=8K

sync_source_info=10000

sync_relay_log=10000

mysqlx_port=33060

Passo 8 -Reinicie o servidor de BD MySQL no CMD

# Iniciar MySQL
net start MySQL80

1 2	# Iniciar MySQL net start MySQL80

No MySQL execute:

SET SESSION net_read_timeout = 1800;

SET SESSION net_write_timeout = 1800;

SET GLOBAL max_allowed_packet = 268435456;

Estratégias inteligentes para trabalhar com dados massivos

Trabalhar com 30 milhões de registros exige a mesma paciência e estratégia que um alpinista precisa para escalar o Everest. Você não sobe de uma vez – divide em acampamentos-base.

Atualizações em lotes: dividir para conquistar

Atualizar 30 milhões de registros de uma vez é como tentar assinar 10.000 pães no mesmo forno. Eventualmente, algo queima. Por isso, dividimos em lotes menores:

"""
Atualização em lotes - assinando pães em fornadas menores
"""
UPDATE trampo.VENDAS_MEDICAMENTOS 
SET principio_ativo = 'SEM INFORMAÇÃO' 
WHERE principio_ativo = '' 
LIMIT 1000;  # Apenas 1000 por vez

# Execute repetidamente até terminar
# Verifique quantos faltam com:
SELECT COUNT(*) FROM trampo.VENDAS_MEDICAMENTOS 
WHERE principio_ativo = '';

"""

Atualização em lotes - assinando pães em fornadas menores

"""

UPDATE trampo.VENDAS_MEDICAMENTOS

SET principio_ativo = 'SEM INFORMAÇÃO'

WHERE principio_ativo = ''

LIMIT 1000; # Apenas 1000 por vez

# Execute repetidamente até terminar

# Verifique quantos faltam com:

SELECT COUNT(*) FROM trampo.VENDAS_MEDICAMENTOS

WHERE principio_ativo = '';

Consultas filtradas: buscando agulhas no palheiro

Encontrar dados específicos em 30 milhões de registros exige filtros inteligentes, similar a como um matemático usa equações para resolver problemas complexos:

"""
SEMPRE use WHERE para filtrar - não tente fazer tudo de uma vez
"""
SELECT 
    ANO_VENDA, 
    MUNICIPIO_VENDA, 
    PRINCIPIO_ATIVO,
    SUM(QTD_VENDIDA) as TOTAL_VENDIDO
FROM trampo.vendas_medicamentos  
WHERE MUNICIPIO_VENDA = 'Niterói'
GROUP BY ANO_VENDA, MUNICIPIO_VENDA, PRINCIPIO_ATIVO;

"""

SEMPRE use WHERE para filtrar - não tente fazer tudo de uma vez

"""

SELECT

ANO_VENDA,

MUNICIPIO_VENDA,

PRINCIPIO_ATIVO,

SUM(QTD_VENDIDA) as TOTAL_VENDIDO

FROM trampo.vendas_medicamentos

WHERE MUNICIPIO_VENDA = 'Niterói'

GROUP BY ANO_VENDA, MUNICIPIO_VENDA, PRINCIPIO_ATIVO;

Os detalhes que fazem diferença

Assim como a qualidade da farinha afeta o pão, pequenos detalhes na configuração impactam drasticamente o desempenho. Inegavelmente, o innodb_buffer_pool_size é o mais importante – ele determina quantos dados ficam na memória RAM, que é milhões de vezes mais rápida que o disco.

innodb_buffer_pool_size: Use 50-80% da RAM disponível
innodb_flush_log_at_trx_commit = 2: Acelera escrita mas reduz segurança em caso de queda de energia
Índices: Como índice de livro – aceleram buscas mas desaceleram inserções

Perguntas que os iniciantes fazem

Você deve estar se perguntando: “Por que não uso configurações gigantescas desde o início?” Analogamente a como um padeiro não usa fermento em excesso, configurações muito grandes podem travar seu servidor. Comece conservador e ajuste conforme necessário.

Uma confusão comum é pensar que mais RAM sempre resolve tudo. Surpreendentemente, sem os índices corretos, é como ter uma Ferrari em um congestionamento – o poder está lá, mas você não consegue usar.

Para onde ir agora?

Comece aplicando as configurações básicas e testando com consultas pequenas. Posteriormente, monitore o desempenho usando o slow query log para identificar gargalos. Lembre-se: otimização de banco de dados é uma jornada, não um destino.

Assuntos relacionados

Complexidade algorítmica e notação Big O
Estatística descritiva para análise de dados
Probabilidade e distribuições de dados
Otimização matemática e trade-offs
Álgebra relacional e teoria de conjuntos

Referências que valem a pena

Dominar grandes volumes de dados é como dominar a arte da panificação: requer prática, paciência e os ingredientes certos. Agora você tem a receita – mãos à obra!

Indice