Resumo : neste tutorial, você aprenderá vários mecanismos de armazenamento MySQL . É essencial compreender os recursos de cada mecanismo de armazenamento no MySQL para que você possa usá-los de forma eficaz para maximizar o desempenho de seus bancos de dados.
Introdução aos mecanismos de armazenamento MySQL
No MySQL, um mecanismo de armazenamento é um componente de software responsável por gerenciar como os dados são armazenados, recuperados e manipulados nas tabelas. Um mecanismo de armazenamento também determina a estrutura e os recursos subjacentes das tabelas.
MySQL oferece suporte a vários mecanismos de armazenamento, cada um com seu próprio conjunto de recursos. Para encontrar os mecanismos de armazenamento disponíveis em seu servidor MySQL, você pode usar a seguinte consulta:
SELECT
engine,
support
FROM
information_schema.engines
ORDER BY
engine;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Saída:
+--------------------+---------+
| engine | support |
+--------------------+---------+
| ARCHIVE | YES |
| BLACKHOLE | YES |
| CSV | YES |
| FEDERATED | NO |
| InnoDB | DEFAULT |
| MEMORY | YES |
| MRG_MYISAM | YES |
| MyISAM | YES |
| ndbcluster | NO |
| ndbinfo | NO |
| PERFORMANCE_SCHEMA | YES |
+--------------------+---------+
11 rows in set (0.02 sec)
Linguagem de código: texto simples ( texto simples )A consulta retorna 11 mecanismos de armazenamento na coluna de mecanismo e se há suporte ou não na coluna de suporte.
Se a coluna de suporte for YES, significa que o mecanismo de armazenamento correspondente é compatível ou NOnão.
Se o valor na coluna de suporte for DEFAULT, significa que o mecanismo de armazenamento é suportado e usado como padrão.
Alternativamente, você pode usar a SHOW ENGINESinstrução para listar todos os mecanismos de armazenamento disponíveis:
SHOW ENGINES;Saída:
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)Linguagem de código: PHP ( php )Observe que o MySQL 5.5 ou posterior usa InnoDB como mecanismo de armazenamento padrão.
Para especificar um mecanismo de armazenamento ao criar uma nova tabela, use a ENGINEcláusula na instrução CREATE TABLE :
CREATE TABLE table_name(
column_list
) ENGINE = engine_name;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Se você omitir a cláusula ENGINE, o MySQL usará o mecanismo de armazenamento padrão para criar a tabela.
Recursos do mecanismo de armazenamento MySQL
A tabela a seguir compara os recursos do mecanismo de armazenamento no MySQL:
| Recurso | MeuISAM | MEMÓRIA | CSV | ARQUIVO | BURACO NEGRO | MESCLAR | FEDERADO | InnoDB |
|---|---|---|---|---|---|---|---|---|
| Transacional | Não | Não | Não | Não | Não | Não | Não | Sim |
| Conformidade com ÁCIDO | Não | Não | Não | Não | Não | Não | Não | Sim |
| Bloqueio em nível de tabela | Sim | Não | Sim | Sim | Sim | Não | Sim | Sim |
| Pesquisa de texto completo | Sim | Não | Não | Não | Não | Não | Não | Sim |
| Restrições de chave estrangeira | Não | Não | Não | Não | Não | Não | Não | Sim |
| Recuperação de falhas | Não | Não | Não | Não | Não | Não | Não | Sim |
| Acesso a dados externos | Não | Não | Sim | Sim | Não | Não | Sim | Não |
| Tabelas temporárias | Sim | Sim | Não | Não | Não | Não | Não | Sim |
| Mecanismo de armazenamento padrão | Não | Não | Não | Não | Não | Não | Não | Sim |
InnoDB
As tabelas InnoDB suportam totalmente transações compatíveis com ACID . Eles também são ideais para desempenho. A tabela InnoDB suporta chaves estrangeiras , operações de commit, rollback e rollforward. O tamanho de uma tabela InnoDB pode ser de até 64 TB.
Assim como o MyISAM, as tabelas do InnoDB são portáveis entre diferentes plataformas e sistemas operacionais. O MySQL também verifica e repara tabelas do InnoDB, se necessário, na inicialização.
MeuISAM
O MyISAM estende o antigo mecanismo de armazenamento ISAM. As tabelas MyISAM são otimizadas para compactação e velocidade. As tabelas MyISAM também são portáveis entre plataformas e sistemas operacionais.
O tamanho da tabela MyISAM pode ser de até 256 TB, o que é enorme. Além disso, as tabelas MyISAM podem ser compactadas em tabelas somente leitura para economizar espaço. Na inicialização, o MySQL verifica se há corrupção nas tabelas MyISAM e até as repara em caso de erros. A desvantagem das tabelas MyISAM é que elas não são seguras para transações.
Antes da versão 5.5, o MySQL usava MyISAM como mecanismo de armazenamento padrão. A partir da versão 5.5, o MySQL usa InnoDB como mecanismo de armazenamento padrão.
MESCLAR
Uma tabela MERGE é uma tabela virtual que combina várias tabelas MyISAM que possuem a mesma estrutura de uma tabela. O mecanismo de armazenamento MERGE também é conhecido como MRG_MyISAMmecanismo. As MERGEtabelas não possuem índices. Em vez disso, eles usam índices das tabelas componentes.
Se você usar DROP TABLEuma instrução em uma MERGEtabela, o MySQL removerá apenas a MERGEtabela e não excluirá as tabelas subjacentes.
As tabelas MERGE permitem acelerar o desempenho ao unir múltiplas tabelas . O MySQL permite apenas que você execute operações SELECT, DELETE, UPDATE e INSERT nas MERGEtabelas.
Memória
As tabelas MEMORY armazenam dados inteiramente na memória e usam índices hash para que sejam mais rápidas que as tabelas MyISAM.
A vida útil dos dados das tabelas MEMORY depende do tempo de atividade do servidor de banco de dados. O mecanismo de armazenamento de memória era anteriormente conhecido como HEAP.
Arquivo
O mecanismo de armazenamento ARCHIVE permite armazenar um grande número de registros para fins de arquivamento em um formato compactado para economizar espaço em disco. O mecanismo de armazenamento ARCHIVE compacta um registro quando ele é inserido e descompacta à medida que é lido.
As tabelas ARCHIVE permitem apenas instruções INSERT e SELECT . As ARCHIVEtabelas não suportam índices, portanto é necessária uma varredura completa da tabela para leitura de linhas.
CSV
O mecanismo de armazenamento CSV armazena dados em formato de arquivo de valores separados por vírgula (CSV). Uma tabela CSV oferece uma maneira conveniente de migrar dados para aplicativos não SQL, como software de planilha.
A tabela CSV não oferece suporte ao tipo de dados NULL. Além disso, a operação de leitura requer uma varredura completa da tabela.
BURACO NEGRO
O mecanismo de armazenamento BLACKHOLE não armazena os dados da tabela. Isso significa que os dados que você envia para uma tabela BLACKHOLE são descartados.
Portanto, as tabelas BLACKHOLE podem ser úteis em um cenário de replicação onde você deseja capturar alterações de dados no servidor mestre sem armazenar esses dados no servidor local.
FEDERADO
O mecanismo de armazenamento FEDERATED permite gerenciar dados de um servidor MySQL remoto sem usar cluster ou tecnologia de replicação.
A tabela federada local não armazena dados. Ao consultar dados de uma tabela federada local, os dados são extraídos automaticamente das tabelas federadas remotas.
Resumo
- Um mecanismo de armazenamento determina como o MySQL armazena, recupera e manipula os dados da tabela.
- MySQL usa InnoDB como mecanismo de armazenamento padrão.
- Use a
ENGINEcláusula paraCREATE STATEMENTinstruir explicitamente o MySQL a usar um mecanismo de armazenamento específico diferente do mecanismo de armazenamento padrão. - Cada mecanismo de armazenamento tem seus prós e contras, portanto, escolher o mecanismo de armazenamento certo é fundamental para sua aplicação.
