1. Selecione os atributos de campo mais aplicáveis.
O MySQL pode suportar o acesso de grandes quantidades de dados, mas de modo geral, quanto menor a tabela no banco de dados, mais rápidas serão as consultas executadas nela. Portanto, ao criar uma tabela, para obter melhor desempenho, podemos definir a largura dos campos da tabela o menor possível. Por exemplo, ao definir o campo de código postal, se você defini-lo como CHAR(255), obviamente adicionará espaço desnecessário ao banco de dados. Mesmo usar o tipo VARCHAR é redundante, porque CHAR(6) é uma missão cumprida. Da mesma forma, se possível, devemos usar MEDIUMINT em vez de BIGIN para definir campos inteiros.
Outra forma de melhorar a eficiência é definir os campos como NOT NULL quando possível, para que o banco de dados não precise comparar valores NULL ao executar consultas no futuro.
Para alguns campos de texto, como “província” ou “gênero”, podemos defini-los como tipos ENUM. Porque no MySQL, o tipo ENUM é tratado como dados numéricos e os dados numéricos são processados muito mais rápido do que os tipos de texto. Desta forma, podemos melhorar o desempenho do banco de dados.
2. Use junções (JOIN) em vez de subconsultas (Subconsultas).
O MySQL suporta subconsultas SQL a partir de 4.1. Essa técnica permite usar uma instrução SELECT para criar uma única coluna de resultados de consulta e, em seguida, usar esse resultado como condição de filtro em outra consulta. Por exemplo, se quisermos excluir clientes que não possuem pedidos na tabela de informações básicas do cliente, podemos usar uma subconsulta para primeiro recuperar os IDs de todos os clientes que emitiram pedidos na tabela de informações de vendas e, em seguida, passar os resultados para a consulta principal, conforme mostrado abaixo:
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
O uso de subconsultas pode concluir muitas operações SQL que logicamente exigem a conclusão de várias etapas ao mesmo tempo. Também pode evitar bloqueios de transações ou tabelas e também é fácil de escrever. No entanto, em alguns casos, as subconsultas podem ser substituídas por junções mais eficientes (JOIN). Por exemplo, suponha que queiramos recuperar todos os usuários que não possuem registros de pedidos, podemos usar a seguinte consulta:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
Se você usar conexão (JOIN).. para completar esta consulta, a velocidade será muito mais rápida. Principalmente se houver um índice em CustomerID na tabela salesinfo, o desempenho será melhor. A consulta é a seguinte:
SELECT * FROM customerinfo.
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
ID do cliente
WHERE salesinfo.CustomerID IS NULL
connection (JOIN).. A razão pela qual é mais eficiente é que o MySQL não precisa criar uma tabela temporária na memória para completar esta consulta lógica em duas etapas.
3. Use união (UNION) para substituir tabelas temporárias criadas manualmente
O MySQL suporta consultas UNION a partir da versão 4.0, que pode mesclar duas ou mais consultas SELECT que requerem o uso de tabelas temporárias em uma consulta. Quando a sessão de consulta do cliente terminar, a tabela temporária será excluída automaticamente para garantir que o banco de dados esteja organizado e eficiente. Ao usar UNION para criar uma consulta, precisamos apenas usar UNION como palavra-chave para conectar várias instruções SELECT. Deve-se observar que o número de campos em todas as instruções SELECT deve ser o mesmo. O exemplo a seguir demonstra uma consulta usando UNION.
SELECIONE Nome, Telefone DO cliente
UNIÃO
SELECIONE Nome, Data de Nascimento DO autor
UNIÃO
SELECT Nome, Fornecedor FROM produto
4. Transações
Embora possamos usar subconsultas, conexões (JOIN) e uniões (UNION) para criar uma variedade de consultas, nem todas as operações de banco de dados podem ser realizadas com apenas uma. algumas instruções SQL. Mais frequentemente, uma série de declarações são necessárias para completar um determinado tipo de trabalho. Mas neste caso, quando uma determinada instrução neste bloco de instruções for executada incorretamente, a operação de todo o bloco de instruções se tornará incerta. Imagine que você deseja inserir determinados dados em duas tabelas relacionadas ao mesmo tempo. Isso pode acontecer: após a atualização da primeira tabela com sucesso, ocorre uma situação inesperada no banco de dados, fazendo com que a operação na segunda tabela não seja concluída. Desta forma, os dados ficarão incompletos e até mesmo os dados do banco de dados serão destruídos. Para evitar esta situação, você deve usar transações. Sua função é: todas as instruções no bloco de instruções são bem-sucedidas ou falham. Em outras palavras, a consistência e a integridade dos dados no banco de dados podem ser mantidas. As coisas começam com a palavra-chave BEGIN e terminam com a palavra-chave COMMIT. Se uma operação SQL falhar durante esse período, o comando ROLLBACK poderá restaurar o banco de dados para o estado anterior ao início de BEGIN.
COMEÇAR;
INSERT INTO salesinfo SET CustomerID=14;
ATUALIZAR inventário SET Quantidade = 11
WHERE item='livro';
COMMIT;
Outra função importante das transações é que quando vários usuários usam a mesma fonte de dados ao mesmo tempo, ele pode usar o método de bloqueio do banco de dados para fornecer aos usuários um método de acesso seguro, que pode garantir que as operações do usuário não sejam bloqueadas. por outros usuários.
5. Bloqueio de tabelas
Embora as transações sejam uma ótima maneira de manter a integridade do banco de dados, devido à sua exclusividade, às vezes isso afeta o desempenho do banco de dados, especialmente em grandes sistemas de aplicativos. Como o banco de dados será bloqueado durante a execução da transação, outras solicitações do usuário só poderão esperar até que a transação termine. Se um sistema de banco de dados for usado por apenas alguns usuários, o impacto das transações não se tornará um grande problema, mas se milhares de usuários acessarem um sistema de banco de dados ao mesmo tempo, como acessar um site de comércio eletrônico, isso causará sérios problemas; atraso de resposta.
Na verdade, em alguns casos podemos obter melhor desempenho bloqueando a tabela. O exemplo a seguir usa o método de tabela de bloqueio para concluir a função de transação do exemplo anterior.
LOCK TABLE inventário WRITE
SELECIONE Quantidade DO inventário
WHEREItem='livro';
...
ATUALIZAR inventário SET Quantidade = 11
WHEREItem='livro';
UNLOCK TABLES
Aqui usamos uma instrução SELECT para recuperar os dados iniciais e, por meio de alguns cálculos, usamos uma instrução UPDATE para atualizar os novos valores na tabela. A instrução LOCK TABLE contendo a palavra-chave WRITE garante que não haverá outro acesso ao inventário para inserir, atualizar ou excluir antes que o comando UNLOCK TABLES seja executado.
6.
O método de utilização de chaves estrangeiras para bloquear a tabela pode manter a integridade dos dados, mas não pode garantir a relevância dos dados. Neste momento podemos usar chaves estrangeiras. Por exemplo, uma chave estrangeira pode garantir que cada registro de vendas aponte para um cliente existente. Aqui, a chave estrangeira pode mapear o CustomerID na tabela customerinfo para o CustomerID na tabela salesinfo. Qualquer registro sem um CustomerID válido não será atualizado ou inserido no salesinfo.
CRIAR TABELA informações do cliente
(
ID do Cliente INT NÃO NULO,
CHAVE PRIMÁRIA (CustomerID)
) TIPO = INNODB;
CRIAR TABELA informações de vendas
(
SalesID INT NÃO NULO,
ID do Cliente INT NÃO NULO,
CHAVE PRIMÁRIA (CustomerID, SalesID),
CHAVE ESTRANGEIRA (CustomerID) REFERÊNCIAS customerinfo
(CustomerID) EM DELETECASCADE
) TYPE = INNODB;
Observe o parâmetro "ON DELETE CASCADE" no exemplo. Este parâmetro garante que quando um registro de cliente na tabela customerinfo for excluído, todos os registros relacionados ao cliente na tabela salesinfo também serão excluídos automaticamente. Se você quiser usar chaves estrangeiras no MySQL, lembre-se de definir o tipo de tabela como um tipo InnoDB seguro para transações ao criar a tabela. Este tipo não é o tipo padrão para tabelas MySQL. O método definido é adicionar TYPE=INNODB à instrução CREATE TABLE. Como mostrado no exemplo.
7. O uso de índices
é um método comum para melhorar o desempenho do banco de dados. Ele permite que o servidor de banco de dados recupere linhas específicas muito mais rápido do que sem um índice, especialmente se a instrução de consulta contiver MAX(), MIN() e ORDERBY. a melhoria do desempenho é mais óbvia. Então, quais campos devem ser indexados? De modo geral, os índices devem ser construídos em campos que serão usados para julgamento de JOIN, WHERE e classificação ORDER BY. Tente não indexar um campo do banco de dados que contenha um grande número de valores duplicados. Para um campo do tipo ENUM, é muito possível ter um grande número de valores duplicados, como o campo "província".. em customerinfo. Construir um índice em tal campo não será útil, pelo contrário, é possível; Reduza o desempenho do banco de dados. Podemos criar índices apropriados ao mesmo tempo ao criar a tabela, ou podemos usar ALTER TABLE ou CREATE INDEX para criar índices posteriormente. Além disso, o MySQL suporta indexação e pesquisa de texto completo a partir da versão 3.23.23. O índice de texto completo é um índice do tipo FULLTEXT no MySQL, mas só pode ser usado para tabelas do tipo MyISAM. Para um banco de dados grande, será muito rápido carregar os dados em uma tabela sem um índice FULLTEXT e então usar ALTER TABLE ou CREATE INDEX para criar o índice. Mas se você carregar dados em uma tabela que já possui um índice FULLTEXT, o processo de execução será muito lento.
8. Instruções de consulta otimizadas
Na maioria dos casos, o uso de índices pode melhorar a velocidade da consulta, mas se as instruções SQL forem usadas incorretamente, o índice não será capaz de desempenhar o devido papel. A seguir estão vários aspectos aos quais devemos prestar atenção. Primeiro, é melhor realizar operações de comparação entre campos do mesmo tipo. Antes da versão 3.23 do MySQL, esta era até uma condição obrigatória. Por exemplo, um campo INT indexado não pode ser comparado com um campo BIGINT, mas como um caso especial, quando um campo do tipo CHAR e um campo do tipo VARCHAR têm o mesmo tamanho, eles podem ser comparados. Em segundo lugar, tente não utilizar funções para operar em campos indexados.
Por exemplo, ao usar a função YEAE() em um campo do tipo DATE, o índice não funcionará como deveria. Portanto, embora as duas consultas seguintes retornem os mesmos resultados, a última é muito mais rápida que a primeira.
SELECT * FROM order WHERE YEAR(OrderDate)＜2001;
SELECT
*FROM
order WHERE OrderDate＜"2001-01-01"
;
* FROM inventário WHERE Amount<24*7;
As duas consultas acima também retornam os mesmos resultados, mas a última consulta será muito mais rápida que a anterior. Terceiro, ao pesquisar campos de caracteres, às vezes usamos palavras-chave LIKE e curingas. Embora essa abordagem seja simples, ela também prejudica o desempenho do sistema. Por exemplo, a consulta a seguir comparará todos os registros da tabela.
SELECIONE * DOS livros
WHERE nome como "MySQL%"
Mas se você usar a seguinte consulta, os resultados retornados serão os mesmos, mas a velocidade será muito mais rápida:
SELECT * FROM books
WHERE name>="MySQL" e name<"MySQM"
Finalmente, você deve ter cuidado para evitar que o MySQL execute conversão automática de tipo na consulta, pois o processo de conversão também tornará o índice ineficaz.