mysql-optimisation / query-optimisation / sql / sql-optimisation

Faça consultas SQL otimizadas e rápidas

Existem várias regras a seguir para fazer cadernos otimizados. Infelizmente não está escrito em livros de estudo porque este conhecimento não é uma compilação do manual do MySQL, mas é resultado de uma longa experiência e luta contra a lentidão.

Eu não falo sobre índices em campos onde WHERE é usado. E outras coisas assim. Isso não é dica profissional 🙂 É meio regular. Portanto, tentarei ir além das sugestões simples.

  • Pode ser que algumas das minhas explicações não sejam completamente corretas quando tento explicar o processo por trás, mas em geral estamos falando sobre resultados.

Para entender a ideia principal, vamos tomar duas tabelas.

Comercial

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `email` varchar(100) NOT NULL DEFAULT '',
  `password` varchar(100) NOT NULL DEFAULT '',
  `block` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_block` (`block`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` (`name`, `email`, `password`, `block`) VALUES ('jon', 'test@test.com', '123', 0);
INSERT INTO `users` (`name`, `email`, `password`, `block`) VALUES ('Eddy', 'eddy@test.com', '321', 1);

e atributos do usuário

CREATE TABLE `user_profiles` (
  `user_id` int(11) NOT NULL,
  `profile_key` varchar(100) NOT NULL,
  `profile_value` varchar(255) NOT NULL,
  UNIQUE KEY `idx_user_id_profile_key` (`user_id`,`profile_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user_profiles` (`user_id`, `profile_key`, `profile_value`) 
VALUES (1, 'moderator', '1'), (2, 'moderator', '0'), 
(1, 'email_sent', '1'), (2, 'email_sent', '1'), 
(1, 'registered', '2012-01-01 00:00:00'), (2, 'registered', '2012-01-02 00:00:00');

Todos nós sabemos que o pior caso é quando fornecemos o uso de temporário e de classificação de arquivo em uma linha em explain. Todas as consultas parecem estar OK até você começar a usar ORDER BYou / eGROUP BY

Isso é o que vamos resolver. Para reestruturar a consulta para que ela tenha apenas o uso de were .

Agora vamos examinar os erros mais sérios na construção de consultas.

1. Evite o uso LEFT JOINse seus apelidos usados ​​em WHERE. Esta é a lição mais importante que tive que aprender.

SELECT * FROM
    users AS u
        LEFT JOIN user_profiles AS p ON p.user_id = u.id 
WHERE 1
    AND u.block = 1 
    AND (p.profile_key = 'moderator' AND p.profile_value = 1)
ORDER BY u.name

Melhor usar subconsultas neste caso.

SELECT * FROM
    users AS u
WHERE 1
    AND u.block = 1 
    AND u.id IN (SELECT user_id 
        FROM user_profiles AS p 
        WHERE p.user_id = u.id 
        AND p.profile_key = 'moderator' 
        AND p.profile_value = 1)
ORDER BY u.name

Deixe-me explicar mais sobre isso. É como se criássemos uma tabela temporária, inseríssemos todos os moderadores e então a usássemos como uma tabela de índice. E este princípio principal da otimização de consulta. Olhe para as subconsultas.

Esta técnica também permitirá que você evite o uso GROUP BY. Porque você usa mesmo que seja apenas 4 registros serão retornados. E é uma grande dor de cabeça.id IN()id IN(1,2,1,3,4,2,2,2)GROUP BY

dica pro-pro 🙂 Eu descobri que às vezes é mais rápido executar a subconsulta separadamente e formar string como e inseri-la como string, mas não como subconsulta.1,2,8,47,12IN()

2. Evite classificar por campo em JOIN. Como, por exemplo, tentar ordenar por valor de perfil cadastrado.

SELECT * FROM
    users AS u
        LEFT JOIN user_profiles AS p ON p.user_id = u.id AND p.profile_key = 'registered' 
WHERE 1
    AND u.block = 1 
ORDER BY p.profile_value ASC

melhor usar subconsultas.

SELECT u.*, 
    (SELECT profile_value 
        FROM user_profiles 
        WHERE user_id = u.id 
        AND profile_key = 'registered') as reg 
    FROM
    users AS u
WHERE 1
    AND u.block = 1 
ORDER BY reg DESC

Related Posts