Uso de SQL

Consulta de SQL de banco de dados

• Otimize as instruções ORDER BY ... LIMIT por índices para melhorar a eficiência da execução.
• Se as instruções contiverem ORDER BY, GROUP BY ou DISTINCT, verifique se o conjunto de resultados filtrado pela condição WHERE contém no máximo 1.000 linhas. Caso contrário, as instruções SQL serão executadas lentamente.
• Para as instruções ORDER BY, GROUP BY e DISTINCT, use índices para recuperar dados classificados diretamente. Por exemplo, use key(a,b) em where a=1 order by b.
• Ao usar JOIN, use índices na mesma tabela na condição WHERE.

  Exemplo:

  select t1.a, t2.b from t1,t2 where t1.a=t2.a and t1.b=123 and t2.c= 4

  Se os campos t1.c e t2.c tiverem o mesmo valor, apenas b no índice (b,c) em t1 será usado.

  Se você alterar t2.c=4 na condição WHERE para t1.c=4, poderá usar o índice completo. Isso pode ocorrer durante o projeto de redundância de campo (desnormalização).

• Se a desduplicação não for necessária, use UNION ALL em vez de UNION.

  Como o UNION ALL não desduplica e classifica os dados, ele é executado mais rápido do que o UNION. Se a desduplicação não for necessária, use UNION ALL preferencialmente.

• Para implementar a consulta de paginação no código, especifique que, se count for definido como 0, as instruções de paginação subsequentes não serão executadas.
• Não execute COUNT com frequência em uma tabela. Demora muito tempo para executar CONTAR em uma tabela com uma grande quantidade de dados. Geralmente, a velocidade de resposta é em segundos. Se você precisar executar frequentemente a operação CONTAR em uma tabela, introduza uma tabela de contagem especial.
• Se apenas um registro for retornado, use LIMIT 1. Se os dados estiverem corretos e o número de registros retornados no conjunto de resultados puder ser determinado, use LIMIT o mais rápido possível.
• Ao avaliar a eficiência das instruções DELETE e UPDATE, altere as instruções para SELECT e execute EXPLAIN. Um grande número de instruções SELECT reduzirá a velocidade do banco de dados, e as operações de gravação bloquearão as tabelas.
• TRUNCATE TABLE é mais rápido e usa menos recursos de sistema e log do que DELETE. Se a tabela a ser excluída não tiver um gatilho e toda a tabela precisar ser excluída, TRUNCATE TABLE é recomendado.
  • TRUNCATE TABLE não grava dados excluídos em arquivos de log.
  • Uma instrução TRUNCATE TABLE tem a mesma função que uma instrução DELETE sem uma cláusula WHERE.
  • Instruções TRUNCATE TABLE não podem ser gravadas com outras instruções DML na mesma transação.
• Não use consultas negativas para evitar a verificação completa da tabela. Consultas negativas indicam que os seguintes operadores negativos são usados: NOT, !=, <>, NOT EXISTS, NOT IN e NOT LIKE.

  Se uma consulta negativa for usada, a estrutura de índice não poderá ser usada para pesquisa binária. Em vez disso, a tabela inteira precisa ser verificada.

• Evite usar JOIN para participar de mais de três tabelas. Os tipos de dados dos campos a serem unidos devem ser os mesmos.
• Durante a consulta de join de várias tabelas, certifique-se de que os campos associados tenham índices. Ao unir várias tabelas, selecione a tabela com um conjunto de resultados menor como tabela de controle para unir outras tabelas. Preste atenção aos índices de tabelas e ao desempenho de SQL, mesmo que duas tabelas sejam unidas.
• Para consultar tabelas ultragrandes, você também precisa cumprir as seguintes regras:
  • Para localizar instruções SQL lentas, ative logs de consulta lenta.
  • Não realize operações de coluna, por exemplo, SELECT id WHERE age+1=10. Qualquer operação em uma coluna, incluindo funções de tutorial de banco de dados e expressões de cálculo, causará varreduras de tabela. Mova as operações para a direita do sinal de igual (=) durante a consulta.
  • Divida instruções maiores em instruções menores e mais simples para reduzir o tempo de bloqueio e evitar o bloqueio de todo o banco de dados.
  • Não use SELECT*.
  • Altere OR para IN. A eficiência de OR está no nível n, enquanto a eficiência de IN está no nível log(n). Tente manter o número de INs abaixo de 200.
  • Evite usar procedimentos e gatilhos armazenados em aplicações.
  • Evite usar consultas no formato %xxx.
  • Evite usar JOIN e tente consultar uma única tabela sempre que possível.
  • Use o mesmo tipo para comparação, por exemplo, '123' a '123' ou 123 a 123.
  • Evite usar os operadores != or <> na cláusula WHERE. Caso contrário, o mecanismo não usará índices e, em vez disso, verificará a tabela completa.
  • Para valores consecutivos, use BETWEEN em vez de IN: SELECT id FROM t WHERE num BETWEEN1AND5.

Desenvolvimento da instrução SQL

• Divida instruções SQL simples.

  Por exemplo, na condição OR f_phone='10000' or f_mobile='10000', os dois campos têm seus próprios índices, mas apenas um deles pode ser usado.

  Você pode dividir a instrução em duas instruções SQL ou usar UNION ALL.

• Se possível, execute o cálculo complexo de SQL ou a lógica de serviço na camada de serviço.
• Use um método de paginação adequado para melhorar a eficiência da paginação. Ignorar a paginação não é recomendado para páginas grandes.

  • Exemplo negativo: SELECT * FROM table1 ORDER BY ftime DESC LIMIT 10000,10;

    Ele causa um grande número de operações de I/O porque o MySQL usa a política de leitura antecipada.

  • Exemplo positivo: SELECT * FROM table1 WHERE ftime < last_time ORDER BY ftime DESC LIMIT 10;

    Método de paginação recomendado: transfira o valor limite da última paginação.

• Execute instruções UPDATE em transações com base em chaves primárias ou chaves exclusivas. Caso contrário, um bloqueio de lacuna será gerado e o intervalo de dados bloqueado será expandido. Como resultado, o desempenho do sistema se deteriora e ocorre um deadlock.
• Não use chaves estrangeiras e operações em cascata. Os problemas de chaves estrangeiras podem ser resolvidos na camada de aplicação.

  Exemplo:

  Se student_id for uma chave primária na tabela de alunos, student_id será uma chave estrangeira na tabela de pontuação. Se student_id for atualizado na tabela de alunos, student_id na tabela de pontuação também será atualizado. Esta é uma atualização em cascata.

  • Chaves estrangeiras e atualizações em cascata são adequadas para clusters de nó único com baixa simultaneidade e não são adequadas para cluster distribuído com alta simultaneidade.
  • As atualizações em cascata podem fazer com que blocos fortes e chaves estrangeiras afetem as operações de INSERT.
• Se possível, não use IN. Se for necessário, certifique-se de que o número de elementos definidos após IN seja de no máximo 500.
• Para reduzir as interações com o banco de dados, utilize lotes de instruções SQL, por exemplo, INSERT INTO … VALUES (*),(*),(*)....(*);. Tente manter o número de itens * abaixo de 100.
• Não use procedimentos armazenados, que são difíceis de depurar, estender e transplantar.
• Não use gatilhos, agendadores de eventos ou exibições para a lógica do serviço. A lógica de serviço deve ser processada na camada de serviço para evitar dependência lógica no banco de dados.
• Não use conversão de tipo implícita.
  

  As regras de conversão são as seguintes:

  1. Se pelo menos um dos dois parâmetros for NULL, o resultado da comparação também será NULL. Entretanto, quando <=> é usado para comparar dois valores de NULL, 1 é retornado.
  2. Se ambos os parâmetros forem cadeias de caracteres, eles serão comparados como cadeias de caracteres.
  3. Se ambos os parâmetros forem inteiros, eles serão comparados como inteiros.
  4. Quando um parâmetro é um valor hexadecimal e o outro parâmetro é um valor não numérico, eles são comparados como cadeias binárias.
  5. Se um parâmetro for um valor de TIMESTAMP ou DATETIME e o outro parâmetro for um valor de CONSTANT, eles serão comparados como valores de TIMESTAMP.
  6. Se um parâmetro for um valor de DECIMAL e o outro for um valor de DECIMAL ou INTEGER, eles serão comparados como valores de DECIMAL. Se o outro argumento for um valor de FLOATING POINT, eles serão comparados como valores de FLOATING POINT.
  7. Em outros casos, ambos os parâmetros são comparados como valores de FLOATING POINT.
  8. Se um parâmetro for uma cadeia e o outro for um valor de INT, eles serão comparados como valores de FLOATING POINT (referindo-se ao item 7)

     Por exemplo, o tipo de f_phone é varchar. Se f_phone in (098890) for usado na condição WHERE, dois parâmetros serão comparados como valores de FLOATING POINT. Nesse caso, o índice não pode ser usado, afetando o desempenho do banco de dados.

     Se f_user_id = '1234567', o número é comparado diretamente como uma cadeia de caracteres. Para mais detalhes, consulte o item 2.

• Se possível, certifique-se de que o número de instruções SQL em uma transação seja o menor possível, não mais do que 5. Transações longas bloquearão dados por muito tempo, gerarão muitos caches no MySQL e ocuparão muitas conexões.
• Não use NATURAL JOIN.

  NATURAL JOIN é usado para unir colunas implicitamente, o que é difícil de entender e pode causar problemas. A instrução NATURAL JOIN não pode ser transplantada.

• Para tabelas com dezenas de milhões ou centenas de milhões de registros de dados, é aconselhável usar os seguintes métodos para melhorar a eficiência de gravação de dados:
  1. Exclua índices desnecessários.

     Quando os dados são atualizados, os dados de índice também são atualizados. Para tabelas com grandes quantidades de dados, evite criar muitos índices, pois isso pode atrasar o processo de atualização. Exclua índices desnecessários.

  2. Insira vários registros de dados em lotes.

     Isso ocorre porque a inserção em lote requer apenas uma única solicitação remota ao banco de dados.

     Exemplo:

     insert into tb1 values(1,'value1');
     insert into tb2 values(2,'value2');
     insert into tb3 values(3,'value3');

     Após a otimização:

     insert into tb values(1,'value1'),(2,'value2'),(3,'value3');

  3. Ao inserir vários registros de dados, controle manualmente as transações.

     Ao controlar manualmente a transação, várias unidades de execução podem ser mescladas em uma única transação, evitando a sobrecarga de várias transações e garantindo a integridade e a consistência dos dados.

     Exemplo:

     insert into table1 values(1,'value1'),(2,'value2'),(3,'value3');
     insert into table2 values(4,'value1'),(5,'value2'),(6,'value3');
     insert into table3 values(7,'value1'),(8,'value2'),(9,'value3');

     Após a otimização:

     start transaction;
     insert into table1 values(1,'value1'),(2,'value2'),(3,'value3');
     insert into table2 values(4,'value1'),(5,'value2'),(6,'value3');
     insert into table3 values(7,'value1'),(8,'value2'),(9,'value3');
     commit;

     

     Ter muitas instruções mescladas pode levar a grandes transações, o que bloqueará a tabela por muito tempo. Avalie as necessidades de serviço e controle o número de instruções em uma transação de acordo.

  4. Ao inserir dados com chaves primárias, tente inseri-los na ordem sequencial das chaves primárias. Você pode usar AUTO_INCREMENT.

     A inserção de dados em uma ordem aleatória das chaves primárias pode causar divisão de página, o que pode afetar negativamente o desempenho.

     Exemplo:

     Inserção de dados em uma ordem aleatória de chaves primárias: 6 2 9 7 2

     Inserção de dados em uma ordem sequencial de chaves primárias: 1 2 4 6 8

  5. Evite usar UUIDs ou outras chaves naturais, como números de carteira de identidade, como chaves primárias.

     Os UUIDs gerados a cada vez não são ordenados e inseri-los como chaves primárias pode causar divisão de página, o que pode afetar negativamente o desempenho.

  6. Evite modificar chaves primárias durante as operações de serviço.

     Modificar chaves primárias requer a modificação da estrutura do índice, o que pode ser caro.

  7. Reduza o comprimento das chaves primárias o máximo possível.
  8. Não use chaves estrangeiras para manter relacionamentos de chaves estrangeiras. Em vez disso, use programas.
  9. Separe as operações de leitura e gravação. Solicitações de leitura diretas para ler réplicas para evitar inserção lenta causada por I/Os.