Este artigo explora ideias para melhorar o desempenho do banco de dados MySQL e fornece soluções específicas em 8 aspectos.
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
Defina o caminho dos dados e coloque os arquivos de dados do banco de dados no diretório NFS compartilhado (servidor NAS).
Os arquivos PID e innioDB devem ser colocados no diretório local do servidor para iniciar e parar o serviço normalmente:
1125. vi /etc/ my.cnf
[mysqld]
#Onde instalar um banco de dados
datadir=/data/mysqldata
#Onde instalar um mecanismo innoDB
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
Compilação relacionada itens a partir de 222 As duas linhas de , coloque o arquivo PID no diretório local do servidor:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid
instala o banco de dados básico do MySQL:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
if Se normal, você pode ver que o mysql inicia normalmente
1146 /etc/rc.d/init.d/mysql.server start
1146 /etc/rc.d/init.d/mysql.server; pare de
configurar alta disponibilidade de HA, não configure a montagem NFS e o serviço mysql será executado automaticamente ao iniciar o servidor
5. Bloqueio de tabelas
Embora as transações sejam uma boa maneira de manter a integridade do banco de dados, devido à sua exclusividade, às vezes afeta o; desempenho do banco de dados, especialmente em períodos de tempo muito longos 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';
DESBLOQUEAR TABELAS
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
) TIPO = 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, MySQL
A indexação e a pesquisa de texto completo são suportadas 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 pedido WHERE ANO(DataPedido)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
A mesma situação também ocorrerá no cálculo de campos numéricos:
SELECT * FROM inventário WHERE Amount/7<24;
SELECT * 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 são os mesmos, mas a velocidade é 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.