One place for hosting & domains

      consultas

      Como otimizar consultas do MySQL com o cache do ProxySQL no Ubuntu 16.04


      O autor selecionou a Free Software Foundation para receber uma doação como parte do programa Write for DOnations.

      Introdução

      O ProxySQL é um servidor proxy com reconhecimento de SQL que pode ser posicionado entre seu aplicativo e seu banco de dados. Ele oferece muitos recursos, como o de balancear carga entre vários servidores de MySQL e servir como uma camada de cache para consultas. Este tutorial irá se concentrar no recurso de cache do ProxySQL e como ele pode otimizar as consultas para o seu banco de dados do MySQL.

      O cache do MySQL ocorre quando o resultado de uma consulta é armazenado para que, quando essa consulta for repetida, o resultado possa ser retornado sem a necessidade de classificar o banco de dados. Isso pode aumentar significativamente a velocidade das consultas comuns. Em muitos métodos de cache, porém, os desenvolvedores precisam modificar o código do seu aplicativo, o que poderia introduzir um bug na base de códigos. Para evitar essa prática propensa a erros, o ProxySQL permite que você configure o cache transparente.

      No cache transparente, apenas os administradores do banco de dados precisam alterar a configuração do ProxySQL para habilitar o cache para as consultas mais comuns. Tais alterações podem ser feitas através da interface de administrador do ProxySQL. Tudo o que o desenvolvedor precisa fazer é conectar-se ao proxy que reconhece o protocolo. O proxy decidirá se a consulta pode ser atendida a partir do cache sem chegar ao servidor de back-end.

      Neste tutorial, você usará o ProxySQL para configurar o cache transparente de um servidor MySQL no Ubuntu 16.04. Em seguida, você testará seu desempenho usando o mysqlslap – com e sem o cache, no intuito de demonstrar o efeito do cache e quanto tempo ele pode poupar na execução de várias consultas semelhantes.

      Pré-requisitos

      Antes de iniciar este guia, você precisará do seguinte:

      Passo 1 — Instalando e configurando o servidor MySQL

      Primeiro, você instalará o servidor MySQL e o configurará para ser usado pelo ProxySQL como um servidor de back-end para atender consultas de clientes.

      No Ubuntu 16.04, o mysql-server pode ser instalado usando este comando:

      • sudo apt-get install mysql-server

      Pressione Y para confirmar a instalação.

      Em seguida, você será solicitado a digitar sua senha de usuário root do MySQL. Digite uma senha forte e salve-a para usar mais tarde.

      Agora que você tem seu servidor MySQL pronto, irá configurá-lo para que o ProxySQL funcione corretamente. Você precisa adicionar um usuário monitor para o ProxySQL monitorar o servidor MySQL, uma vez que o ProxySQL escuta o servidor de back-end através do protocolo da SQL, em vez de usar uma conexão TCP ou pedidos pelo método GET do HTTP – para garantir que o back-end esteja funcionando. O monitor usará uma conexão SQL fictícia para determinar se o servidor está ativo ou não.

      Primeiro, faça login no shell do MySQL:

      O parâmetro -uroot, conecta você através do usuário root do MySQL e o -p solicita a senha do usuário root. Esse usuário root é diferente do usuário root do seu servidor e a senha é a que você digitou quando instalou o pacote mysql-server.

      Digite a senha do root e pressione ENTER.

      Agora, você criará dois usuários, um chamado monitor para o ProxySQL e outro que você usará para executar consultas de clientes e conceder-lhes os privilégios corretos. Este tutorial nomeará esse usuário como sammy.

      Crie o usuário monitor:

      • CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

      A consulta CREATE USER é usada para criar um novo usuário que pode se conectar a partir de IPs específicos. Usar % denota que o usuário pode se conectar a partir de qualquer endereço IP. IDENTIFIED BY define a senha para o novo usuário; digite qualquer senha que quiser, mas certifique-se de lembrá-la para uso posterior.

      Com o usuário monitor criado, crie o usuário sammy:

      • CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

      Em seguida, conceda privilégios aos seus novos usuários. Execute o seguinte comando para configurar o monitor:

      • GRANT SELECT ON sys.* TO 'monitor'@'%';

      A consulta GRANT é usada para dar privilégios aos usuários. Aqui, você concedeu privilégios somente de SELECT em todas as tabelas no banco de dados sys para o usuário monitor; ele precisa apenas desse privilégio para escutar o servidor de back-end.

      Agora, conceda ao usuário sammy todos os privilégios em relação a todos os bancos de dados:

      • GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

      Isso permitirá que o sammy faça as consultas necessárias para testar seu banco de dados mais tarde.

      Aplique as alterações de privilégios, executando o seguinte:

      Por fim, saia do shell do mysql:

      Agora, você instalou o mysql-server e criou um usuário para ser usado pelo ProxySQL para monitorar seu servidor MySQL e outro para executar consultas de clientes. Em seguida, você instalará e configurará o ProxySQL.

      Passo 2 — Instalando e configurando o servidor ProxySQL

      Agora, você pode instalar o servidor ProxySQL, que será usado como uma camada de cache para as suas consultas. Uma camada de cache existe como uma parada entre os servidores do seu aplicativo e os servidores de back-end do banco de dados; ela é usada para se conectar ao banco de dados e salvar os resultados de algumas consultas em sua memória para acesso rápido mais tarde.

      A página de lançamentos do ProxySQL no Github oferece arquivos de instalação para distribuições comuns do Linux. Para este tutorial, você usará o wget para baixar o arquivo de instalação do ProxySQL versão 2.0.4 do, Debian:

      • wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

      Em seguida, instale o pacote usando o dpkg:

      • sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

      Assim que estiver instalado, inicie o ProxySQL com este comando:

      • sudo systemctl start proxysql

      Verifique se o ProxySQL iniciou corretamente com este comando:

      • sudo systemctl status proxysql

      Você receberá um resultado semelhante a este:

      Output

      root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled) Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago Docs: man:systemd-sysv-generator(8) Tasks: 0 Memory: 0B CPU: 0

      Agora, é hora de conectar o seu servidor ProxySQL ao servidor MySQL. Para tanto, utilize a interface administrativa SQL do ProxySQL, a qual, por padrão, escuta a porta 6032 no localhost e tem admin como seu nome de usuário e senha.

      Conecte-se à interface executando o seguinte:

      • mysql -uadmin -p -h 127.0.0.1 -P6032

      Digite admin quando for solicitado a inserir uma senha.

      -uadmin define o nome de usuário como admin e o sinalizador -h especifica o host como localhost. A porta é 6032, especificada com o sinalizador -P.

      Aqui, você teve que especificar claramente o host e a porta porque, por padrão, o cliente MySQL se conecta usando um arquivo de socket local e a porta 3306.

      Agora que você se conectou ao shell mysql como admin, configure o usuário monitor para que o ProxySQL possa usá-lo. Primeiro, use consultas SQL padrão para definir os valores de duas variáveis globais:

      • UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
      • UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

      A variável mysql-monitor_username especifica o nome de usuário do MySQL que será usado para verificar se o servidor de back-end está ativo ou não. A variável mysql-monitor_password aponta para a senha que será usada ao se conectar ao servidor de back-end. Use a senha que criou para o nome de usuário monitor.

      Toda vez que fizer uma alteração na interface administrativa do ProxySQL, precisará usar o comando LOAD (carregar) correto para aplicar as alterações na instância do ProxySQL em execução. Você alterou variáveis globais do MySQL,assim, carregue-as no RUNTIME para aplicar as alterações:

      • LOAD MYSQL VARIABLES TO RUNTIME;

      Em seguida, SAVE (salve) as alterações no banco de dados em disco para manter as alterações entre as reinicializações. O ProxySQL usa seu próprio banco de dados do SQLite local para armazenar suas próprias tabelas e variáveis:

      • SAVE MYSQL VARIABLES TO DISK;

      Agora, você dirá ao ProxySQL sobre o servidor de back-end. A tabela mysql_servers detém as informações sobre cada servidor de back-end ao qual o ProxySQL pode conectar-se e onde pode executar consultas. Assim, adicione um novo registro usando uma instrução SQL padrão INSERT, com os seguintes valores para hostgroup_id, hostname e port:

      • INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

      Para aplicar as alterações, execute LOAD e SAVE novamente:

      • LOAD MYSQL SERVERS TO RUNTIME;
      • SAVE MYSQL SERVERS TO DISK;

      Por fim, você dirá ao ProxySQL qual usuário se conectará ao servidor de back-end; defina o sammy como o usuário e substitua sammy_password pela senha que criou anteriormente:

      • INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

      A tabela mysql_users contém informações sobre os usuários usados para se conectar aos servidores de back-end; você especificou o username (nome de usuário), password (senha) e default_hostgroup (grupo de host padrão).

      LOAD e SAVE as alterações:

      • LOAD MYSQL USERS TO RUNTIME;
      • SAVE MYSQL USERS TO DISK;

      Então, saia do shell do mysql:

      Para testar se você consegue se conectar ao seu servidor de back-end usando o ProxySQL, execute a seguinte consulta teste:

      • mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

      Nesse comando, você usou o sinalizador -e para executar uma consulta e fechar a conexão. A consulta imprime o nome do host do servidor de back-end.

      Nota: por padrão, o ProxySQL usa a porta 6033 para escutar as conexões de entrada.

      O resultado ficará parecido com este, sendo o your_hostname substituído pelo seu nome de host:

      Output

      +----------------------------+ | hostname | +----------------------------+ | your_hostname | +----------------------------+

      Para aprender mais sobre a configuração do ProxySQL, consulte o Passo 3 sobre Como usar o ProxySQL como um balanceador de carga para o MySQL no Ubuntu 16.04.

      Até aqui, você configurou o ProxySQL para usar seu servidor MySQL como um back-end e se conectou ao back-end usando o ProxySQL. Agora,você está pronto para usar o mysqlslap para comparar o desempenho das consultas sem cache.

      Passo 3 — Testando o uso do mysqlslap sem o cache

      Neste passo, você fará download de um banco de dados de teste para que possa executar consultas nele com o mysqlslap, no intuito de testar a latência sem o armazenamento em cache, definindo um parâmetro de comparação para a velocidade das suas consultas. Você também irá explorar como o ProxySQL mantém os registros das consultas na tabela stats_mysql_query_digest.

      O mysqlslap é um cliente de emulação de carga que é usado como uma ferramenta de teste de carga para o MySQL. Ele pode testar um servidor MySQL com consultas geradas automaticamente ou com algumas consultas personalizadas, executadas em um banco de dados. Ele vem instalado no pacote do cliente MySQL, de modo que não é necessário instalá-lo; em vez disso, você irá baixar um banco de dados apenas para fins de teste, no qual você poderá usar o mysqlslap.

      Neste tutorial, você usará uma amostra de banco de dados de funcionários. Você vai usar essa amostra de banco de dados de funcionários porque ela apresenta um conjunto grande de dados que pode ilustrar as diferenças na otimização das consultas. O banco de dados tem seis tabelas, mas os dados que ele contém têm mais de 300.000 registros de funcionários. Isso ajudará você a emular uma carga de trabalho de produção em grande escala.

      Para baixar o banco de dados, clone primeiro o repositório do Github usando este comando:

      • git clone https://github.com/datacharmer/test_db.git

      Em seguida, acesse o diretório test_db e carregue o banco de dados no servidor MySQL usando estes comandos:

      • cd test_db
      • mysql -uroot -p < employees.sql

      Esse comando usa o redirecionamento da shell para ler as consultas em SQL no arquivo employees.sql e as executa no servidor MySQL para criar a estrutura do banco de dados.

      Você verá um resultado como este:

      Output

      INFO CREATING DATABASE STRUCTURE INFO storage engine: InnoDB INFO LOADING departments INFO LOADING employees INFO LOADING dept_emp INFO LOADING dept_manager INFO LOADING titles INFO LOADING salaries data_load_time_diff 00:00:32

      Assim que o banco de dados for carregado no seu servidor MySQL, teste se o mysqlslap está funcionando com a seguinte consulta:

      • mysqlslap -usammy -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose

      O mysqlslap tem sinalizadores semelhantes aos do cliente mysql; aqui estão os usados neste comando:

      • -u – especifica o usuário usado para se conectar ao servidor.
      • -p – solicita a senha do usuário.
      • -P – conecta-se usando a porta especificada.
      • -h – conecta-se ao host especificado.
      • --auto-generate-sql – permite que o MySQL faça testes de carga, usando suas próprias consultas geradas.
      • --verbose – faz o resultado mostrar mais informações.

      Você irá obter um resultado similar ao seguinte:

      Output

      Benchmark Average number of seconds to run all queries: 0.015 seconds Minimum number of seconds to run all queries: 0.015 seconds Maximum number of seconds to run all queries: 0.015 seconds Number of clients running queries: 1 Average number of queries per client: 0

      Nesse resultado, você pode ver o número médio, mínimo e máximo de segundos gastos para executar todas as consultas. Isso lhe dará uma ideia sobre o tempo necessário para executar as consultas feitas por um certo número de clientes. Nesse resultado, apenas um cliente foi usado para executar consultas.

      Em seguida, descubra quais consultas o mysqlslap executou no último comando, examinando o stats_mysql_query_digest do ProxySQL. Isso nos dará informações como o resumo das consultas, que é uma forma normalizada da instrução em SQL que poderá ser referenciada mais tarde para habilitar o armazenamento em cache.

      Acesse a interface de administração do ProxySQL com este comando:

      • mysql -uadmin -p -h 127.0.0.1 -P6032

      Depois, execute esta consulta para encontrar informações na tabela stats_mysql_query_digest:

      • SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

      Você verá um resultado similar ao seguinte:

      +------------+----------+-----------+--------------------+----------------------------------+
      | count_star | sum_time | hostgroup | digest             | digest_text                      |
      +------------+----------+-----------+--------------------+----------------------------------+
      | 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
      | 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
      +------------+----------+-----------+--------------------+----------------------------------+
      2 rows in set (0.01 sec)
      

      A consulta anterior seleciona os dados da tabela stats_mysql_query_digest, a qual contém informações sobre todas as consultas executadas no ProxySQL. Aqui, você tem cinco colunas selecionadas:

      • count_star: o número de vezes que essa consulta foi executada.
      • sum_time: tempo total em milissegundos que essa consulta levou para executar.
      • hostgroup: o grupo de hosts usado para executar a consulta.
      • digest: um resumo da consulta executada.
      • digest_text: a consulta em si. No exemplo deste tutorial, a segunda consulta é parametrizada usando sinais de ? no lugar de parâmetros de variável. select @@version_comment limit 1 e select @@version_comment limit 2 são, portanto, agrupados juntos como a mesma consulta e com o mesmo resumo.

      Agora que você sabe como verificar os dados de consulta na tabela stats_mysql_query_digest, saia do shell do mysql:

      O banco de dados que baixou contém algumas tabelas com dados de demonstração. Agora, você testará consultas na tabela dept_emp, selecionando quaisquer registros cujo from_date formaior que 2000-04-20 e registrando o tempo médio de execução.

      Use este comando para executar o teste:

      • mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

      Aqui, você está usando alguns sinalizadores novos:

      • --concurrency=100: define o número de usuários a simular, neste caso 100.
      • --iterations=20: faz com que o teste seja executado 20 vezes e calcula os resultados de todos elas.
      • --create-schema=employees: aqui você selecionou o banco de dados employees (funcionários).
      • --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": aqui você especificou a consulta executada no teste.

      O teste levará alguns minutos. Após terminar, você receberá resultados semelhantes ao seguinte:

      Output

      Benchmark Average number of seconds to run all queries: 18.117 seconds Minimum number of seconds to run all queries: 8.726 seconds Maximum number of seconds to run all queries: 22.697 seconds Number of clients running queries: 100 Average number of queries per client: 1

      Seus números podem ser um pouco diferentes. Mantenha esses números em algum lugar para compará-los com os resultados obtidos após habilitar o armazenamento em cache.

      Após testar o ProxySQL sem armazenar em cache, é hora de executar o mesmo teste novamente; mas, desta vez, com o armazenamento em cache habilitado.

      Neste passo, o armazenamento em cache nos ajudará a diminuir a latência ao executar consultas semelhantes. Aqui, você identificará as consultas executadas, pegará seus resumos da tabela stats_mysql_query_digest do ProxySQL e os usará para habilitar o armazenamento em cache. Em seguida, você testará novamente para verificar a diferença.

      Para habilitar o armazenamento em cache, você precisa conhecer os resumos das consultas que serão armazenadas em cache. Faça login na interface de administração do ProxySQL, usando este comando:

      • mysql -uadmin -p -h127.0.0.1 -P6032

      Depois, execute esta consulta novamente para obter uma lista das consultas executadas e seus resumos:

      • SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

      Você receberá um resultado semelhante a este:

      Output

      +------------+-------------+-----------+--------------------+------------------------------------------+ | count_star | sum_time | hostgroup | digest | digest_text | +------------+-------------+-----------+--------------------+------------------------------------------+ | 2000 | 33727110501 | 1 | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? | | 1 | 601 | 1 | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname | | 1 | 0 | 1 | 0x226CD90D52A2BA0B | select @@version_comment limit ? | +------------+-------------+-----------+--------------------+------------------------------------------+ 3 rows in set (0.00 sec)

      Examine a primeira linha. Trata-se de uma consulta que foi executada 2000 vezes. Essa é a consulta executada anteriormente como parâmetro de comparação. Pegue seu resumo e guarde-o para ser usado na adição de uma regra de consulta para o armazenamento em cache.

      As próximas consultas vão adicionar uma nova regra de consulta ao ProxySQL que fará a correspondência entre o resumo da consulta anterior e colocará um valor cache_ttl para ela. O cache_ttl é o número de milissegundos em que o resultado ficará armazenado em memória cache:

      • INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

      Nesse comando, você está adicionando um novo registro à tabela mysql_query_rules; essa tabela contém todas as regras aplicadas antes de executar uma consulta. Nesse exemplo, você está adicionando um valor à coluna cache_ttl, que fará com que a consulta – que foi combinada por determinado resumo – seja armazenada em cache pelo tempo (em milissegundos) especificado nessa coluna. Coloque 1 na coluna de aplicação para garantir que a regra seja aplicada às consultas.

      LOAD e SAVE essas alterações e, em seguida, saia do shell mysql:

      • LOAD MYSQL QUERY RULES TO RUNTIME;
      • SAVE MYSQL QUERY RULES TO DISK;
      • exit;

      Agora que o armazenamento em cache está habilitado, execute novamente o teste para verificar o resultado:

      • mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

      Isso dará um resultado similar ao seguinte:

      Output

      Benchmark Average number of seconds to run all queries: 7.020 seconds Minimum number of seconds to run all queries: 0.274 seconds Maximum number of seconds to run all queries: 23.014 seconds Number of clients running queries: 100 Average number of queries per client: 1

      Aqui, você consegue ver a grande diferença em tempo médio de execução: o tempo baixou de 18.117 segundos para 7.020.

      Conclusão

      Neste artigo, você configurou o armazenamento em cache transparente com o ProxySQL para armazenar em cache os resultados das consultas no banco de dados. Também testou a velocidade de consulta com e sem o armazenamento em cache para ver a diferença que o armazenamento em cache pode fazer.

      Neste tutorial, você usou um nível de armazenamento em cache. Você também poderia tentar fazer o armazenamento em cache baseado na Web, o qual fica na frente de um servidor Web e armazena em cache as respostas a pedidos semelhantes, enviando a resposta de volta para o cliente, sem chegar aos servidores de back-end. É bem parecido com o armazenamento em cache do ProxySQL, porém em um outro nível. Para aprender mais sobre o armazenamento em cache baseado na Web, acesse o artigo Noções básicas de armazenamento em cache baseado na Web: terminologia, cabeçalhos de HTTP e primer de estratégias de armazenamento em cache.

      O servidor MySQL também tem seu próprio cache de consulta; você pode aprender mais sobre isso em nosso tutorial sobre Como otimizar o MySQL com o cache de consulta no Ubuntu 18.04.



      Source link

      Cómo optimizar las consultas de MySql con almacenamiento en caché de ProxySQL en Ubuntu 16.04


      El autor seleccionó la Free Software Foundation para recibir una donación como parte del programa Write for DOnations.

      Introducción

      ProxySQL es un servidor proxy con reconocimiento de SQL que puede posicionarse entre su aplicación y su base de datos. Ofrece muchas funciones, como el equilibrio de carga entre varios servidores MySQL y además sirve como capa de almacenamiento en caché para las consultas. Este tutorial se centrará en la función de almacenamiento en caché de ProxySQL, y en la forma en que puede optimizar las consultas de su base de datos MySQL.

      El almacenamiento en caché de MySQL se produce cuando el resultado de una consulta se almacena de modo que, cuando se repite la consulta, el resultado pueda mostrarse sin necesidad de realizar búsquedas en la base de datos. Esto puede aumentar considerablemente la velocidad de las consultas comunes. Sin embargo, en muchos métodos de almacenamiento en caché, los desarrolladores deben modificar el código de su aplicación, lo que podría introducir un error en la base de código. Para evitar esta práctica propensa a errores, ProxySQL le permite configurar un método de almacenamiento en caché transparente.

      En el almacenamiento en caché transparente, los administradores de la base de datos solo necesitan cambiar la configuración de ProxySQL para permitir el almacenamiento en caché de las consultas más comunes, y estos cambios pueden realizarse a través de la interfaz de administración de ProxySQL. Lo único que el desarrollador debe hacer es establecer conexión con el proxy que reconoce el protocolo. El proxy decidirá si la consulta puede presentarse desde la memoria caché sin alcanzar al servidor de backend.

      En este tutorial, usará ProxySQL para configurar un almacenamiento en caché transparente para un servidor MySQL en Ubuntu 16.04. A continuación, probará su rendimiento usando mysqlslap con y sin almacenamiento en caché para demostrar el efecto del almacenamiento en caché y la cantidad de tiempo que puede ahorrarse con él al ejecutar muchas consultas similares.

      Requisitos previos

      Para completar esta guía, necesitará lo siguiente:

      Paso 1: Instalar y configurar el servidor de MySQL

      Primero, instalará el servidor de MySQL y lo configurará para que lo use ProxySQL como servidor de backend para presentar consultas de clientes.

      En Ubuntu 16.04, puede instalar mysql-server usando este comando:

      • sudo apt-get install mysql-server

      Pulse Y para confirmar la instalación.

      Se solicitará su contraseña de usuario** root** de MySQL. Introduzca una contraseña segura y guárdela para su uso posterior.

      Ahora que tiene su servidor MySQL listo, lo configurará para que ProxySQL funcione correctamente. Debe añadir un usuario monitor para que ProxySQL monitorice el servidor de MySQL, ya que ProxySQL escucha al servidor de backend a través del protocolo SQL en vez de usar una conexión TCP o solicitudes HTTP GET para garantizar que el backend está en ejecución. El *usuario monitor *usará una conexión SQL ficticia para determinar si el servidor está activo o no.

      Primero, inicie sesión en el shell de MySQL:

      -uroot inicia sesión por usted usando el usuario root de MySQL y -p solicita la contraseña del usuario root. Este usuario root es diferente del usuario root de su servidor y la contraseña es la que introdujo cuando instaló el paquete mysql-server.

      Introduzca la contraseña root y pulse ENTER.

      Ahora, creará dos usuarios, uno llamado** monitor** para ProxySQL y otro que usará para ejecutar las consultas de los clientes y otorgarles los privilegios adecuados. En este tutorial, se asignará el nombre sammy a este usuario.

      Cree el usuario monitor:

      • CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor_password';

      La consulta CREATE USER se utiliza para crear un nuevo usuario que pueda conectarse desde IP específicas. Usar % denota que el usuario puede conectarse desde cualquier dirección IP. IDENTIFIED BY establece la contraseña para el nuevo usuario; introduzca la contraseña que desee, pero asegúrese de recordarla para usarla más adelante.

      Una vez creado el usuario monitor, cree el usuario sammy:

      • CREATE USER 'sammy'@'%' IDENTIFIED BY 'sammy_password';

      A continuación, conceda privilegios a sus nuevos usuarios. Ejecute el siguiente comando para configurar el usuario monitor:

      • GRANT SELECT ON sys.* TO 'monitor'@'%';

      La consulta GRANT se utiliza para dar privilegios a los usuarios. Aquí solo concedió SELECT en todas las tablas de la base de datos sys al usuario monitor; solo necesita este privilegio para escuchar al servidor de backend.

      Ahora, conceda todos los privilegios para todas las bases de datos al usuario sammy:

      • GRANT ALL PRIVILEGES on *.* TO 'sammy'@'%';

      Esto permitirá que sammy realice las consultas necesarias para probar su base de datos más tarde.

      Aplique los cambios de privilegios ejecutando lo siguiente:

      Finalmente, salga del shell mysql:

      Con esto, habrá instalado mysql-server y creado un usuario que ProxySQL utilizará para monitorizar su servidor de MySQL, y otro para ejecutar las consultas de clientes. A continuación, instalará y configurará ProxySQL.

      Paso 2: Instalar y configurar el servidor de ProxySQL

      Ahora podrá instalar el servidor de ProxySQL, que se utilizará como una capa de almacenamiento en caché para sus consultas. Una capa de almacenamiento en caché existe como punto de detención entre los servidores de su aplicación y los servidores de backend de la base de datos; se utiliza para conectar con la base de datos y para guardar los resultados de algunas consultas en su memoria para acceder a ellas más rápidamente.

      En la página de Github para versiones de ProxySQL se ofrecen archivos de instalación para distribuciones comunes de Linux. A los efectos de este tutorial, usará wget para descargar el archivo de instalación de Debian para la versión de ProxySQl 2.0.4:

      • wget https://github.com/sysown/proxysql/releases/download/v2.0.4/proxysql_2.0.4-ubuntu16_amd64.deb

      A continuación, instale el paquete usando dpkg:

      • sudo dpkg -i proxysql_2.0.4-ubuntu16_amd64.deb

      Una vez que lo haga, inicie ProxySQL con este comando:

      • sudo systemctl start proxysql

      Puede comprobar si ProxySQL se inició correctamente con este comando:

      • sudo systemctl status proxysql

      Verá un resultado similar a este:

      Output

      root@ubuntu-s-1vcpu-2gb-sgp1-01:~# systemctl status proxysql ● proxysql.service - LSB: High Performance Advanced Proxy for MySQL Loaded: loaded (/etc/init.d/proxysql; bad; vendor preset: enabled) Active: active (exited) since Wed 2019-06-12 21:32:50 UTC; 6 months 7 days ago Docs: man:systemd-sysv-generator(8) Tasks: 0 Memory: 0B CPU: 0

      Ahora es el momento de conectar su servidor de ProxySQL con el servidor de MySQL. Para esto, utilice la interfaz de administración SQL de ProxySQl, que por defecto escucha el puerto 6032 en localhost y tiene admin como nombre de usuario y contraseña.

      Establezca conexión con la interfaz ejecutando lo siguiente:

      • mysql -uadmin -p -h 127.0.0.1 -P6032

      Introduzca admin cuando se le solicite la contraseña.

      -uadmin establece admin como nombre de usuario, y el indicador -h especifica que localhost es el host. El puerto es el 6032, especificado usando el indicador -P.

      Aquí, tuvo que especificar el host y el puerto de forma explícita porque, por defecto, el cliente MySQL se conecta usando un archivo de sockets local y el puerto 3306.

      Ahora que inició sesión en el shell de mysql como admin, configure el usuario monitor de modo que ProxySQL pueda usarlo. Primero, utilice consultas SQL estándares para establecer los valores de dos variables globales:

      • UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
      • UPDATE global_variables SET variable_value='monitor_password' WHERE variable_name='mysql-monitor_password';

      La variable mysql-monitor_username especifica el nombre de usuario de MySQL que se utilizará para comprobar si el servidor de backend está activo o no. La variable mysql-monitor_password apunta a la contraseña que se usará cuando establece conexión con el servidor de backend. Utilice la contraseña que creó para el nombre de usuario monitor.

      Cada vez que realice un cambio en la interfaz de administración de ProxySQL, deberá usar el comando LOAD adecuado para aplicar los cambios a la instancia de ProxySQL en ejecución. Cambió las variables globales de MySQL. Por ello, cárguelas en RUNTIME para aplicar los cambios:

      • LOAD MYSQL VARIABLES TO RUNTIME;

      A continuación, aplique SAVE los cambios a la base de datos en el disco para que los cambios persistan entre reinicios. ProxySQL utiliza su propia base de datos local SQLite para guardar sus tablas y variables:

      • SAVE MYSQL VARIABLES TO DISK;

      Ahora, transmitirá información a ProxySQL sobre el servidor de backend. La tabla mysql_servers contiene información sobre cada servidor de backend en los cuales ProxySQL puede establecer conexión y ejecutar consultas. Por lo tanto, debe añadir un nuevo registro usando una instrucción INSERT de SQL con los siguientes valores para hostgroup_id, hostname y port:

      • INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (1, '127.0.0.1', 3306);

      Para aplicar los cambios, ejecute LOAD y SAVE de nuevo:

      • LOAD MYSQL SERVERS TO RUNTIME;
      • SAVE MYSQL SERVERS TO DISK;

      Finalmente, indicará a ProxySQL el usuario que se conectará con el servidor de backend; establezca sammy como el usuario y sustituya sammy_password por la contraseña que creó antes:

      • INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('sammy', 'sammy_password', 1);

      La tabla mysql_users contiene información sobre los usuarios empleados para establecer conexión con los servidores de backend; especificó username, password y default_hostgroup.

      Aplique LOAD y SAVE a los cambios:

      • LOAD MYSQL USERS TO RUNTIME;
      • SAVE MYSQL USERS TO DISK;

      Finalmente, cierre el shell mysql:

      Para probar que pueda establecer conexión con su servidor de backend usando ProxySQL, ejecute la siguiente consulta de prueba:

      • mysql -usammy -h127.0.0.1 -p -P6033 -e "SELECT @@HOSTNAME as hostname"

      En este comando, usó el indicador -e para ejecutar una consulta y cerrar la conexión. La consulta imprime el nombre de host del servidor de backend.

      Nota: ProxySQL utiliza el puerto 6033 por defeto para escuchar las conexiones entrantes.

      El resultado tendrá este aspecto y your_hostname se sustituirá por su nombre de host:

      Output

      +----------------------------+ | hostname | +----------------------------+ | your_hostname | +----------------------------+

      Para obtener más información sobre la configuración de ProxySQL, consulte el paso 3 de Cómo usar ProxySQL como equilibrador de carga para MySQL en Ubuntu 16.04.

      Hasta ahora, configuró ProxySQL para que utilice su servidor de MySQL como backend y estableció conexión con el este último usando ProxySQL. Ahora, estará listo para usar mysqlslap para hacer referencia al rendimiento de la consulta sin almacenamiento en caché.

      Paso 3: Probar mysqlslap sin almacenamiento en caché

      En este paso, descargará una base de datos de prueba para poder ejecutar consultas en ella con mysqlslap para probar la latencia sin almacenamiento en caché, estableciendo una referencia para la velocidad de sus consultas. También verá la forma en que ProxySQL lleva registros de las consultas en la tabla stats_mysql_query_digest.

      mysqlslap es un cliente de emulación de carga que se usa como herramienta de prueba de carga para MySQL. Puede probar un servidor MySQL con consultas autogeneradas o con algunas consultas personalizadas ejecutadas en una base de datos. Viene instalado con el paquete cliente de MySQL, de modo que no necesita instalarlo. En vez de eso, descargará una base de datos solo para pruebas en la cual puede usar mysqlslap.

      En este tutorial, usará una base de datos de empleados de muestra. Usará esta base de datos de empleados porque cuenta con un gran conjunto de datos que puede ilustrar las diferencias en la optimización de consultas. La base de datos tiene seis tablas, pero los datos que contiene tienen más de 300.000 registros de empleados. Esto le permitirá emular una carga de trabajo de producción a gran escala.

      Para descargar la base de datos, primero clone el repositorio de Github usando este comando:

      • git clone https://github.com/datacharmer/test_db.git

      A continuación, introduzca el directorio test_db y cargue la base de datos en el servidor MySQL usando estos comandos:

      • cd test_db
      • mysql -uroot -p < employees.sql

      Este comando utiliza redireccionamiento de shell para leer las consultas SQL en el archivo employees.sql y las ejecuta en el servidor MySQL para crear la estructura de la base de datos.

      Verá un resultado similar a este:

      Output

      INFO CREATING DATABASE STRUCTURE INFO storage engine: InnoDB INFO LOADING departments INFO LOADING employees INFO LOADING dept_emp INFO LOADING dept_manager INFO LOADING titles INFO LOADING salaries data_load_time_diff 00:00:32

      Una vez que la base de datos se cargue en su servidor MySQL, pruebe que mysqlslap funcione con la siguiente consulta:

      • mysqlslap -usammy -p -P6033 -h127.0.0.1 --auto-generate-sql --verbose

      mysqlslap tiene indicadores similares a los del cliente mysql; aquí están los que se utilizan en este comando:

      • -u especifica el usuario usado para establecer conexión con el servidor.
      • -p pide la contraseña del usuario.
      • -P establece conexión usando el puerto especificado.
      • -h establece conexión con el host especificado.
      • --auto-generate-sql permite que MySQL realice una prueba de carga usando sus propias consultas generadas.
      • --verbose hace que en el resultado se muestre más información.

      Obtendrá un resultado similar al siguiente:

      Output

      Benchmark Average number of seconds to run all queries: 0.015 seconds Minimum number of seconds to run all queries: 0.015 seconds Maximum number of seconds to run all queries: 0.015 seconds Number of clients running queries: 1 Average number of queries per client: 0

      En este resultado, puede ver los números de segundos promedio, mínimo y máximo utilizados para ejecutar todas las consultas. Esto le proporciona una indicación sobre la cantidad de tiempo necesario para ejecutar las consultas por un número de clientes. En este resultado, solo se utilizó un cliente para ejecutar las consultas.

      A continuación, descubra las consultas que mysqlslap ejecutó en el último comando mirando stats_mysql_query_digest de ProxySQL. Esto nos dará información, como el* resumen* de las consultas, que es una forma normalizada de la instrucción de SQL a la que se puede hacer referenciad más tarde para habilitar el almacenamiento en caché.

      Ingrese en la interfaz de administración de ProxySQL con este comando:

      • mysql -uadmin -p -h 127.0.0.1 -P6032

      A continuación, ejecute esta consulta para buscar la información en la tabla stats_mysql_query_digest:

      • SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

      Verá resultados similares al siguiente:

      +------------+----------+-----------+--------------------+----------------------------------+
      | count_star | sum_time | hostgroup | digest             | digest_text                      |
      +------------+----------+-----------+--------------------+----------------------------------+
      | 1          | 598      | 1         | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname    |
      | 1          | 0        | 1         | 0x226CD90D52A2BA0B | select @@version_comment limit ? |
      +------------+----------+-----------+--------------------+----------------------------------+
      2 rows in set (0.01 sec)
      

      Con la consulta previa se seleccionan datos de la tabla stats_mysql_query_digest, que contiene información sobre todas las consultas ejecutadas en ProxySQL. A continuación, se muestran cinco columnas seleccionadas:

      • count_star: número de veces que se ejecutó esta consulta.
      • sum_time: tiempo total en milisegundos que tardó esta consulta en ejecutarse.
      • hotsgroup: hostgroup usado para ejecutar la consulta.
      • digest: resumen de la consulta ejecutada.
      • digest_text: la consulta real. En este ejemplo del tutorial, la segunda consulta se parametriza usando signos ? en lugar de parámetros variables. select @@version_comment limit 1 y select @@version_comment limit 2, por lo tanto, se agrupan como la misma consulta con el mismo resumen.

      Ahora que sabe comprobar los datos de la consulta en la tabla stats_mysql_query_digest, cierre el shell de mysql:

      La base de datos que descargó contiene algunas tablas con datos de demostración. Ahora, probará las consultas en la tabla dept_emp seleccionando cualquier registro cuya from_date sea mayor que 2000-04-20 y registrando el tiempo medio de ejecución.

      Utilice este comando para ejecutar la prueba:

      • mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

      Aquí usa algunos indicadores nuevos:

      • --concurrency=100: esto establece el número de usuarios que se simulará; en este caso, 100.
      • --iterations=20: esto hace que la prueba se ejecute 20 veces y calcule los resultados de todas ellas.
      • --create-schema=employees: aquí seleccionó la base de datos employees.
      • --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'": aquí especificó la consulta ejecutada en la prueba.

      La prueba tardará unos minutos. Una vez que esta se realice, obtendrá resultados similares a los siguientes:

      Output

      Benchmark Average number of seconds to run all queries: 18.117 seconds Minimum number of seconds to run all queries: 8.726 seconds Maximum number of seconds to run all queries: 22.697 seconds Number of clients running queries: 100 Average number of queries per client: 1

      Sus números podrían ser un poco diferentes. Mantenga estos números en algún lugar para compararlos con los resultados obtenidos tras habilitar el almacenamiento en caché.

      Una vez que pruebe ProxySQL sin almacenamiento en caché, será el momento de ejecutar la misma prueba de nuevo, pero esta vez con el almacenamiento en caché habilitado.

      Paso 4: Probar mysqlslap con almacenamiento en caché

      En este paso, el almacenamiento en caché nos ayudará a disminuir la latencia al ejecutar consultas similares. Aquí identificará las consultas ejecutadas, obtendrá sus resúmenes desde la tabla stats:mysql_query_digest de ProxySQL y los usará para habilitar el almacenamiento en caché. A continuación, hará otra prueba para comprobar la diferencia.

      Para habilitar el almacenamiento en caché, deberá conocer los resúmenes de las consultas que se almacenarán en caché. Inicie sesión en la interfaz de administración de ProxySQL usando este comando:

      • mysql -uadmin -p -h127.0.0.1 -P6032

      A continuación, ejecute esta consulta de nuevo para obtener una lista de las consultas ejecutadas y sus resúmenes:

      • SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;

      Verá un resultado similar a este:

      Output

      +------------+-------------+-----------+--------------------+------------------------------------------+ | count_star | sum_time | hostgroup | digest | digest_text | +------------+-------------+-----------+--------------------+------------------------------------------+ | 2000 | 33727110501 | 1 | 0xC5DDECD7E966A6C4 | SELECT * from dept_emp WHERE from_date>? | | 1 | 601 | 1 | 0xF8F780C47A8D1D82 | SELECT @@HOSTNAME as hostname | | 1 | 0 | 1 | 0x226CD90D52A2BA0B | select @@version_comment limit ? | +------------+-------------+-----------+--------------------+------------------------------------------+ 3 rows in set (0.00 sec)

      Observe la primera fila. Se relaciona con una consulta que se ejecutó 2000 veces. Esta es la consulta que se ejecutó previamente y a la que se hizo referencia. Tome su resumen y guárdelo para usarlo a la hora de añadir una regla de consulta para el almacenamiento en caché.

      Con las siguientes consultas se añadirá una nueva regla de consulta a ProxySQL que coincidirá con el resumen de la consulta anterior y pondrá un valor cache_ttl para ella. cache_ttl es el número de milisegundos durante los cuales el resultado está almacenado en la memoria caché:

      • INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0xC5DDECD7E966A6C4',2000,1);

      En este comando, añade un nuevo registro a la tabla mysql_query_rules; esta tabla contiene todas las reglas aplicadas antes de ejecutar una consulta. En este ejemplo, agrega un valor para la columna cache_ttl que hará que la consulta conciliada debido al resumen dado se almacene en caché durante un número de milisegundos especificado en esta columna. Usted dispone 1 en la columna apply para garantizar que la regla se aplique a las consultas.

      Aplique LOAD y SAVE a estos cambios, y luego cierre el shell de mysql:

      • LOAD MYSQL QUERY RULES TO RUNTIME;
      • SAVE MYSQL QUERY RULES TO DISK;
      • exit;

      Ahora que el almacenamiento en caché está habilitado, vuelva a ejecutar la prueba de nuevo para comprobar el resultado:

      • mysqlslap -usammy -P6033 -p -h127.0.0.1 --concurrency=100 --iterations=20 --create-schema=employees --query="SELECT * from dept_emp WHERE from_date>'2000-04-20'" --verbose

      Con esto, se mostrará un resultado similar al siguiente:

      Output

      Benchmark Average number of seconds to run all queries: 7.020 seconds Minimum number of seconds to run all queries: 0.274 seconds Maximum number of seconds to run all queries: 23.014 seconds Number of clients running queries: 100 Average number of queries per client: 1

      Aquí puede ver la gran diferencia en el tiempo de ejecución promedio: se redujo de 18.117 a 7.020 segundos.

      Conclusión

      A través de este artículo, configuró un almacenamiento en caché transparente con ProxySQL para almacenar en caché resultados de consultas a la base de datos. También probó la velocidad de las consultas con y sin almacenamiento en caché para ver la diferencia que este puede suponer.

      Durante este tutorial, usó un nivel de almacenamiento en caché. También podría probar el almacenamiento en caché web, que se dispone delante de un servidor web, almacena las respuestas a solicitudes similares y envía la respuesta de vuelta al cliente sin llegar a los servidores de backend. Esto es muy similar al almacenamiento en caché de ProxySQL, pero a un nivel diferente. Para obtener más información sobre el almacenamiento en caché, consulte nuestro artículo introductorio Principios básicos del almacenamiento en caché web: terminología, encabezados HTTP y estrategias de almacenamiento en caché.

      El servidor MySQL también tiene su propia memoria caché de consultas; puede obtener más información sobre ella en el tutorial Cómo optimizar MySQL con memoria caché de consultas en Ubuntu 18.04.



      Source link

      Uma Introdução às consultas no MySQL


      Introdução

      Bancos de dados são um componente chave em muitos websites e aplicações, e estão no centro de como os dados são armazenados e trocados pela Internet. Um dos aspectos mais importantes do gerenciamento de banco de dados é a prática de recuperar dados de um banco de dados, seja em uma base ad hoc ou parte de um processo codificado em um aplicativo. Existem várias maneiras de recuperar informações de um banco de dados, mas um dos métodos mais utilizados é realizado através do envio de consultas pela linha de comandos.

      Em sistemas de gerenciamento de bancos de dados relacionais, uma consulta é qualquer comando usado para recuperar dados de uma tabela. Na Linguagem de Consulta Estruturada ou Structured Query Language (SQL), consultas são feitas quase sempre usando o comando SELECT.

      Neste guia, discutiremos a sintaxe básica das consultas SQL, bem como algumas das funções e operadores mais comumente empregados. Vamos também praticar a criação de consultas SQL usando alguns dados de amostra em um banco de dados MySQL.

      O MySQL é um sistema de gerenciamento de banco de dados relacional open-source. Sendo um dos bancos de dados SQL mais amplamente implantados, o MySQL prioriza velocidade, confiabilidade e usabilidade. Em geral, ele segue o padrão SQL ANSI, embora haja alguns casos em que o MySQL executa operações de maneira diferente do padrão reconhecido.

      Pré-requisitos

      Em geral, os comandos e conceitos apresentados neste guia podem ser usados em qualquer sistema operacional baseado em Linux executando qualquer software de banco de dados SQL. No entanto, ele foi escrito especificamente com um servidor Ubuntu 18.04 executando o MySQL em mente. Para configurar isso, você precisará do seguinte:

      Com esta configuração pronta, podemos começar o tutorial.

      Criando um Banco de Dados de Amostra

      Antes de começarmos a fazer consultas no SQL, vamos primeiro criar um banco de dados e algumas tabelas, em seguida, preencher essas tabelas com alguns dados de amostra. Isso permitirá que você ganhe experiência prática quando começar a fazer consultas mais tarde.

      Para o banco de dados de amostra que usaremos ao longo deste guia, imagine o seguinte cenário:

      Você e vários de seus amigos celebram seus aniversários juntos. Em cada ocasião, os membros do grupo vão para a pista de boliche local, participam de um torneio amistoso e, em seguida, todos vão para sua casa, onde você prepara a refeição favorita do aniversariante.

      Agora que essa tradição já dura algum tempo, você decidiu começar a acompanhar os registros desses torneios. Além disso, para tornar o planejamento das refeições mais fácil, você decide criar um registro dos aniversários dos seus amigos e de suas entradas, acompanhamentos e sobremesas favoritas. Em vez de manter essas informações em um livro físico, você decide exercitar suas habilidades de banco de dados gravando-as em um banco de dados MySQL.

      Para começar, abra um prompt de MySQL como seu usuário root do MySQL:

      Note: Se você seguiu o pré-requisito do tutorial sobre Como Instalar o MySQL no Ubuntu 18.04, você pode ter configurado seu usuário root para autenticar usando uma senha. Neste caso, você irá se conectar ao prompt do MySQL com o seguinte comando:

      Em seguida, crie o banco de dados executando:

      • CREATE DATABASE `aniversarios`;

      Depois, selecione este banco de dados digitando:

      A seguir, crie duas tabelas dentro desse banco de dados. Vamos utilizar a primeira tabela para acompanhar os registros dos seus amigos na pista de boliche. O seguinte comando criará uma tabela chamada torneios com colunas para o nome de cada um de seus amigos, o número de torneios que eles ganharam (vitorias), a melhor pontuação deles em todo o tempo, e que tamanho de sapato de boliche eles usam (tamanho):

      • CREATE TABLE torneios (
      • nome varchar(30),
      • vitorias real,
      • melhor real,
      • tamanho real
      • );

      Depois de executar o comando CREATE TABLE e preenchê-lo com títulos das colunas, você receberá a seguinte saída:

      Output

      Query OK, 0 rows affected (0.00 sec)

      Preencha a tabela torneios com alguns dados de amostra:

      • INSERT INTO torneios (nome, vitorias, melhor, tamanho)
      • VALUES ('Dolly', '7', '245', '8.5'),
      • ('Etta', '4', '283', '9'),
      • ('Irma', '9', '266', '7'),
      • ('Barbara', '2', '197', '7.5'),
      • ('Gladys', '13', '273', '8');

      Você receberá uma saída como esta:

      Output

      Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0

      Depois disso, crie outra tabela dentro do mesmo banco de dados que usaremos para armazenar informações sobre as refeições de aniversário favoritas dos seus amigos. O seguinte comando cria uma tabela chamada refeicoes com colunas para o nome de cada um dos seus amigos, a data_nascimento, a entrada favorita de cada um, o acompanhamento preferido, e a sobremesa favorita:

      • CREATE TABLE refeicoes (
      • nome varchar(30),
      • data_nascimento date,
      • entrada varchar(30),
      • acompanhamento varchar(30),
      • sobremesa varchar(30)
      • );

      Da mesma forma, para esta tabela, você receberá um feedback confirmando que o comando foi executado com sucesso:

      Output

      Query OK, 0 rows affected (0.01 sec)

      Preencha esta tabela com alguns dados de amostra também:

      • INSERT INTO refeicoes (nome, data_nascimento, entrada, acompanhamento, sobremesa)
      • VALUES ('Dolly', '1946-01-19', 'steak', 'salad', 'cake'),
      • ('Etta', '1938-01-25', 'chicken', 'fries', 'ice cream'),
      • ('Irma', '1941-02-18', 'tofu', 'fries', 'cake'),
      • ('Barbara', '1948-12-25', 'tofu', 'salad', 'ice cream'),
      • ('Gladys', '1944-05-28', 'steak', 'fries', 'ice cream');

      Output

      Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0

      Uma vez que esse comando tenha sido concluído com êxito, você acabou de configurar seu banco de dados. A seguir, vamos falar sobre a estrutura básica de comando das consultas SELECT.

      Entendendo Comandos SELECT

      Conforme mencionado na introdução, consultas SQL quase sempre começam com o comando SELECT. SELECT é usado em consultas para especificar quais colunas de uma tabela devem ser retornadas no conjunto de resultados ou result-set. As consultas também quase sempre incluem FROM, que é usado para especificar qual tabela o comando consultará.

      Geralmente, as consultas SQL seguem essa sintaxe:

      • SELECT coluna_a_selecionar FROM tabela_a_selecionar WHERE certas_condições_a_aplicar;

      A título de exemplo, o seguinte comando retornará a coluna nome inteira da tabela refeicoes:

      • SELECT nome FROM refeicoes;
      [seconday_label Output]
      +---------+
      | nome    |
      +---------+
      | Dolly   |
      | Etta    |
      | Irma    |
      | Barbara |
      | Gladys  |
      +---------+
      5 rows in set (0.00 sec)
      

      Você pode selecionar várias colunas da mesma tabela, separando seus nomes com uma vírgula, desta forma:

      • SELECT nome, data_nascimento FROM refeicoes;

      Output

      +---------+-----------------+ | nome | data_nascimento | +---------+-----------------+ | Dolly | 1946-01-19 | | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Barbara | 1948-12-25 | | Gladys | 1944-05-28 | +---------+-----------------+ 5 rows in set (0.00 sec)

      Em vez de nomear uma coluna específica ou um conjunto de colunas, você pode seguir o operador SELECT com um asterisco (*) que serve como um curinga representando todas as colunas em uma tabela. O seguinte comando retorna todas as colunas da tabela torneios:

      Output

      +---------+----------+--------+---------+ | nome | vitorias | melhor | tamanho | +---------+----------+--------+---------+ | Dolly | 7 | 245 | 8.5 | | Etta | 4 | 283 | 9 | | Irma | 9 | 266 | 7 | | Barbara | 2 | 197 | 7.5 | | Gladys | 13 | 273 | 8 | +---------+----------+--------+---------+ 5 rows in set (0.00 sec)

      WHERE é usado em consultas para filtrar registros que atendem a uma condição especificada, e todas as linhas que não atendem a essa condição são eliminadas do resultado. Uma cláusula WHERE geralmente segue esta sintaxe:

      • . . . WHERE nome_da_coluna operador_de_comparação valor

      O operador de comparação em uma cláusula WHERE define como a coluna especificada deve ser comparada com o valor. Aqui estão alguns operadores comuns de comparação em SQL:

      Operador O que ele faz
      = testa a igualdade
      != testa a desigualdade
      < testa menor que
      > testa maior que
      <= testa menor que ou igual a
      >= testa maior que ou igual a
      BETWEEN testa se um valor está dentro de um determinado intervalo
      IN testa se o valor de uma linha está contido em um conjunto de valores especificados
      EXISTS testa se existem linhas, dadas as condições especificadas
      LIKE testa se um valor corresponde a uma string especificada
      IS NULL testa valores NULL
      IS NOT NULL testa todos os valores que não sejam NULL

      Por exemplo, se você quiser encontrar o tamanho do sapato de Irma, use a seguinte consulta:

      • SELECT tamanho FROM torneios WHERE nome = 'Irma';

      Output

      +---------+ | tamanho | +---------+ | 7 | +---------+ 1 row in set (0.00 sec)

      O SQL permite o uso de caracteres curinga, e eles são especialmente úteis quando usados em cláusulas WHERE. Os sinais de porcentagem (%) representam zero ou mais caracteres desconhecidos, e os sublinhados ou underscores (_) representam um único caractere desconhecido. Eles são úteis se você estiver tentando encontrar uma informação específica em uma tabela, mas não tiver certeza de qual é exatamente essa informação. Para ilustrar, digamos que você tenha esquecido a entrada favorita de alguns de seus amigos, mas você está certo de que este prato principal começa com um "t". Você pode encontrar seu nome executando a seguinte consulta:

      • SELECT entrada FROM refeicoes WHERE entrada LIKE 't%';

      Output

      +---------+ | entrada | +---------+ | tofu | | tofu | +---------+ 2 rows in set (0.00 sec)

      Com base na saída acima, vemos que a entrada que esquecemos é tofu.

      Pode haver momentos em que você está trabalhando com bancos de dados que possuem colunas ou tabelas com nomes relativamente longos ou difíceis de ler. Nesses casos, você pode tornar esses nomes mais legíveis criando um alias ou apelido com a palavra-chave AS. Apelidos criados com AS são temporários e existem apenas durante a consulta para a qual eles foram criados:

      • SELECT nome AS n, data_nascimento AS d, sobremesa AS s FROM refeicoes;

      Output

      +---------+------------+-----------+ | n | d | s | +---------+------------+-----------+ | Dolly | 1946-01-19 | cake | | Etta | 1938-01-25 | ice cream | | Irma | 1941-02-18 | cake | | Barbara | 1948-12-25 | ice cream | | Gladys | 1944-05-28 | ice cream | +---------+------------+-----------+ 5 rows in set (0.00 sec)

      Aqui, dissemos ao SQL para exibir a coluna nome como n, a coluna data_nascimento como d e a coluna sobremesa como s.

      Os exemplos que mostramos até aqui incluem algumas das palavras-chave e cláusulas mais usadas em consultas SQL. Elas são úteis para consultas básicas, mas não são úteis se você estiver tentando realizar um cálculo ou derivar um valor escalar (um valor único, em oposição a um conjunto de vários valores diferentes) com base em seus dados. É aqui que as funções de agregação entram em ação.

      Funções de Agregação

      Muitas vezes, ao trabalhar com dados, você não necessariamente quer ver os dados em si. Em vez disso, você quer informações sobre os dados. A sintaxe SQL inclui várias funções que permitem interpretar ou executar cálculos em seus dados apenas emitindo uma consulta SELECT. Estas são conhecidas como funções de agregação.

      A função COUNT conta e retorna o número de linhas que correspondem a um determinado critério. Por exemplo, se você quiser saber quantos dos seus amigos preferem o tofu para a entrada de aniversário, você pode fazer essa consulta:

      • SELECT COUNT(entrada) FROM refeicoes WHERE entrada = 'tofu';

      Output

      +----------------+ | COUNT(entrada) | +----------------+ | 2 | +----------------+ 1 row in set (0.00 sec)

      A função AVG retorna o valor médio (média) de uma coluna. Usando nossa tabela de exemplo, você pode encontrar a melhor pontuação média entre seus amigos com esta consulta:

      • SELECT AVG(melhor) FROM torneios;

      Output

      +-------------+ | AVG(melhor) | +-------------+ | 252.8 | +-------------+ 1 row in set (0.00 sec)

      SUM é usado para encontrar a soma total de uma determinada coluna. Por exemplo, se você quiser ver quantos jogos você e seus amigos jogaram ao longo dos anos, você pode executar essa consulta:

      • SELECT SUM(vitorias) FROM torneios;

      Output

      +---------------+ | SUM(vitorias) | +---------------+ | 35 | +---------------+ 1 row in set (0.00 sec)

      Observe que as funções AVG eSUM só funcionarão corretamente quando usadas com dados numéricos. Se você tentar usá-los em dados não numéricos, isso resultará em um erro ou apenas 0, dependendo de qual SGBD você está usando:

      • SELECT SUM(entrada) FROM refeicoes;

      Output

      +--------------+ | SUM(entrada) | +--------------+ | 0 | +--------------+ 1 row in set, 5 warnings (0.00 sec)

      MIN é usado para encontrar o menor valor dentro de uma coluna especificada. Você poderia usar essa consulta para ver qual o pior registro geral de boliche até agora (em termos de número de vitórias):

      • SELECT MIN(vitorias) FROM torneios;
      [secondarylabel Output]
      +---------------+
      | MIN(vitorias) |
      +---------------+
      |             2 |
      +---------------+
      1 row in set (0.00 sec)
      

      Da mesma forma, MAX é usado para encontrar o maior valor numérico em uma determinada coluna. A consulta a seguir mostrará o melhor registro geral de boliche:

      • SELECT MAX(vitorias) FROM torneios;

      Output

      +---------------+ | MAX(vitorias) | +---------------+ | 13 | +---------------+ 1 row in set (0.00 sec)

      Ao contrário de SUM eAVG, as funções MIN eMAX podem ser usadas para tipos de dados numéricos e alfabéticos. Quando executado em uma coluna contendo valores de string, a função MIN mostrará o primeiro valor alfabeticamente:

      • SELECT MIN(nome) FROM refeicoes;

      Output

      +-----------+ | MIN(nome) | +-----------+ | Barbara | +-----------+ 1 row in set (0.00 sec)

      Da mesma forma, quando executado em uma coluna contendo valores de string, a função MAX mostrará o último valor em ordem alfabética:

      • SELECT MAX(nome) FROM refeicoes;

      Output

      +-----------+ | MAX(nome) | +-----------+ | Irma | +-----------+ 1 row in set (0.00 sec)

      As funções agregadas têm muitos usos além do que foi descrito nesta seção. Elas são particularmente úteis quando usadas com a cláusula GROUP BY, que é abordada na próxima seção junto com várias outras cláusulas de consulta que afetam como os result-sets são classificados.

      Manipulando Saídas da Consulta

      Além das cláusulas FROM eWHERE, existem várias outras cláusulas que são usadas para manipular os resultados de uma consulta SELECT. Nesta seção, explicaremos e forneceremos exemplos para algumas das cláusulas de consulta mais comumente usadas.

      Uma das cláusulas de consulta mais usadas, além de FROM e WHERE, é a cláusula GROUP BY. Ela é normalmente usada quando você está executando uma função de agregação em uma coluna, mas em relação aos valores correspondentes em outra.

      Por exemplo, digamos que você queria saber quantos de seus amigos preferem cada uma das três entradas que você faz. Você pode encontrar essa informação com a seguinte consulta:

      • SELECT COUNT(nome), entrada FROM refeicoes GROUP BY entrada;

      Output

      +-------------+----------+ | COUNT(nome) | entrada | +-------------+----------+ | 1 | chicken | | 2 | steak | | 2 | tofu | +-------------+----------+ 3 rows in set (0.00 sec)

      A cláusula ORDER BY é usada para classificar os resultados da consulta. Por padrão, os valores numéricos são classificados em ordem crescente e os valores de texto são classificados em ordem alfabética. Para ilustrar, a consulta a seguir lista as colunas nome e data_nascimento, mas classifica os resultados por data_nascimento:

      • SELECT nome, data_nascimento FROM refeicoes ORDER BY data_nascimento;

      Output

      +---------+-----------------+ | nome | data_nascimento | +---------+-----------------+ | Etta | 1938-01-25 | | Irma | 1941-02-18 | | Gladys | 1944-05-28 | | Dolly | 1946-01-19 | | Barbara | 1948-12-25 | +---------+-----------------+ 5 rows in set (0.00 sec)

      Observe que o comportamento padrão de ORDER BY é classificar o result-set em ordem crescente. Para reverter isso e ter o resultado classificado em ordem decrescente, feche a consulta com DESC:

      • SELECT nome, data_nascimento FROM refeicoes ORDER BY data_nascimento DESC;

      Output

      +---------+-----------------+ | nome | data_nascimento | +---------+-----------------+ | Barbara | 1948-12-25 | | Dolly | 1946-01-19 | | Gladys | 1944-05-28 | | Irma | 1941-02-18 | | Etta | 1938-01-25 | +---------+-----------------+ 5 rows in set (0.00 sec)

      Como mencionado anteriormente, a cláusula WHERE é usada para filtrar resultados com base em condições específicas. No entanto, se você usar a cláusula WHERE com uma função de agregação, ela retornará um erro, como é o caso da seguinte tentativa de encontrar quais acompanhamentos são os favoritos de pelo menos três de seus amigos:

      • SELECT COUNT(nome), acompanhamento FROM refeicoes WHERE COUNT(nome) >= 3;

      Output

      ERROR 1111 (HY000): Invalid use of group function

      A cláusula HAVING foi adicionada ao SQL para fornecer funcionalidade semelhante à da cláusula WHERE, além de ser compatível com funções de agregação. É útil pensar na diferença entre essas duas cláusulas como sendo que WHERE se aplica a registros individuais, enquantoHAVING se aplica a grupos de registros. Para este fim, sempre que você emitir uma cláusula HAVING, a cláusula GROUP BY também deve estar presente.

      O exemplo a seguir é outra tentativa de descobrir quais são os acompanhamentos favoritos de pelo menos três de seus amigos, embora este retorne um resultado sem erro:

      • SELECT COUNT(nome), acompanhamento FROM refeicoes GROUP BY acompanhamento HAVING COUNT(nome) >= 3;

      Output

      +-------------+----------------+ | COUNT(nome) | acompanhamento | +-------------+----------------+ | 3 | fries | +-------------+----------------+ 1 row in set (0.00 sec)

      As funções de agregação são úteis para resumir os resultados de uma determinada coluna em uma dada tabela. No entanto, há muitos casos em que é necessário consultar o conteúdo de mais de uma tabela. Na próxima seção analisaremos algumas maneiras de fazer isso.

      Consultando Várias Tabelas

      Mais frequentemente, um banco de dados contém várias tabelas, cada uma contendo diferentes conjuntos de dados. O SQL fornece algumas maneiras diferentes de executar uma única consulta em várias tabelas.

      A cláusula JOIN pode ser usada para combinar linhas de duas ou mais tabelas em um resultado de consulta. Ele faz isso localizando uma coluna relacionada entre as tabelas e classifica os resultados adequadamente na saída.

      Os comandos SELECT que incluem uma cláusula JOIN geralmente seguem esta sintaxe:

      • SELECT tabela1.coluna1, tabela2.coluna2
      • FROM tabela1
      • JOIN tabela2 ON tabela1.coluna_relacionada=tabela2.coluna_relacionada;

      Note que como cláusulas JOIN comparam o conteúdo de mais de uma tabela, o exemplo anterior especifica em qual tabela selecionar cada coluna, precedendo o nome da coluna com o nome da tabela e um ponto. Você pode especificar de qual tabela uma coluna deve ser selecionada para qualquer consulta, embora isso não seja necessário ao selecionar de uma única tabela, como fizemos nas seções anteriores. Vamos examinar um exemplo usando nossos dados de amostra.

      Imagine que você queria comprar para cada um de seus amigos um par de sapatos de boliche como presente de aniversário. Como as informações sobre datas de nascimento e tamanhos de calçados dos seus amigos são mantidas em tabelas separadas, você pode consultar as duas tabelas separadamente e comparar os resultados de cada uma delas. Com uma cláusula JOIN, no entanto, você pode encontrar todas as informações desejadas com uma única consulta:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Output

      +---------+---------+------------------+ | nome | tamanho | data_nascimento | +---------+---------+------------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+---------+------------------+ 5 rows in set (0.00 sec)

      A cláusula JOIN usada neste exemplo, sem nenhum outro argumento, é uma cláusula inner JOIN. Isso significa que ela seleciona todos os registros que possuem valores correspondentes nas duas tabelas e os imprime no result-set, enquanto todos os registros que não tem correspondência são excluídos. Para ilustrar essa ideia, vamos adicionar uma nova linha a cada tabela que não tenha uma entrada correspondente na outra:

      • INSERT INTO torneios (nome, vitorias, melhor, tamanho)
      • VALUES ('Bettye', '0', '193', '9');
      • INSERT INTO refeicoes (nome, data_nascimento, entrada, acompanhamento, sobremesa)
      • VALUES ('Lesley', '1946-05-02', 'steak', 'salad', 'ice cream');

      Então, execute novamente a instrução SELECT anterior com a cláusula JOIN:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Output

      +---------+---------+-----------------+ | nome | tamanho | data_nascimento | +---------+---------+-----------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | +---------+---------+-----------------+ 5 rows in set (0.00 sec)

      Observe que, como a tabela torneios não tem entrada para Lesley e a tabela refeicoes não tem entrada para Bettye, esses registros estão ausentes desta saída.

      É possível, no entanto, retornar todos os registros de uma das tabelas usando uma cláusula outer JOIN. No MySQL, as cláusulas JOIN são escritas como LEFT JOIN ou RIGHT JOIN.

      Uma cláusula LEFT JOIN retorna todos os registros da tabela da "esquerda" e apenas os registros correspondentes da tabela da direita. No contexto de outer joins, a tabela da esquerda é aquela referenciada pela cláusula FROM e a tabela da direita é qualquer outra tabela referenciada após o comando JOIN.

      Execute a consulta anterior novamente, mas desta vez use uma cláusula LEFT JOIN:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • LEFT JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Este comando retornará todos os registros da tabela da esquerda (neste caso, torneios), mesmo que não tenha um registro correspondente na tabela da direita. Toda vez que não houver um registro correspondente da tabela da direita, ele será retornado como NULL ou apenas como um valor em branco, dependendo do seu SGBD:

      Output

      +---------+---------+-----------------+ | nome | tamanho | data_nascimento | +---------+---------+-----------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | Bettye | 9 | NULL | +---------+---------+-----------------+ 6 rows in set (0.00 sec)

      Agora execute a consulta novamente, desta vez com uma cláusula RIGHT JOIN:

      • SELECT torneios.nome, torneios.tamanho, refeicoes.data_nascimento
      • FROM torneios
      • RIGHT JOIN refeicoes ON torneios.nome=refeicoes.nome;

      Isso retornará todos os registros da tabela da direita (refeicoes). Como a data de nascimento de Lesley está registrada na tabela da direita, mas não há uma linha correspondente para ela na tabela da esquerda, as colunas nome e tamanho retornarão como valores NULL nessa linha:

      Output

      +---------+---------+-----------------+ | nome | tamanho | data_nascimento | +---------+---------+-----------------+ | Dolly | 8.5 | 1946-01-19 | | Etta | 9 | 1938-01-25 | | Irma | 7 | 1941-02-18 | | Barbara | 7.5 | 1948-12-25 | | Gladys | 8 | 1944-05-28 | | NULL | NULL | 1946-05-02 | +---------+---------+-----------------+ 6 rows in set (0.00 sec)

      Observe que joins à esquerda e à direita podem ser escritos como LEFT OUTER JOIN ou RIGHT OUTER JOIN, embora a parte OUTER da cláusula esteja implícita. Da mesma forma, especificar INNER JOIN produzirá o mesmo resultado que apenas escrever JOIN.

      Como uma alternativa ao uso de JOIN para consultar registros de várias tabelas, você pode usar a cláusula UNION.

      O operador UNION funciona de forma ligeiramente diferente de uma cláusula JOIN: em vez de imprimir resultados de várias tabelas como colunas únicas usando um único comando SELECT, o UNION combina os resultados de dois comandos SELECT em uma única coluna.

      Para ilustrar, execute a seguinte consulta:

      • SELECT nome FROM torneios UNION SELECT nome FROM refeicoes;

      Esta consulta removerá quaisquer entradas duplicadas, que é o comportamento padrão do operador UNION:

      Output

      +---------+ | nome | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Lesley | +---------+ 7 rows in set (0.00 sec)

      Para retornar todas as entradas (incluindo as duplicadas), use o operador UNION ALL:

      • SELECT nome FROM torneios UNION ALL SELECT nome FROM refeicoes;

      Output

      +---------+ | nome | +---------+ | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Bettye | | Dolly | | Etta | | Irma | | Barbara | | Gladys | | Lesley | +---------+ 12 rows in set (0.00 sec)

      Os nomes e números das colunas na tabela de resultados refletem o nome e o número de colunas consultadas pelo primeiro comando SELECT. Note que ao usar UNION para consultar múltiplas colunas de mais de uma tabela, cada comando SELECT deve consultar o mesmo número de colunas, as respectivas colunas devem ter tipos de dados similares, e as colunas em cada comando SELECT devem estar na mesma ordem. O exemplo a seguir mostra o que pode resultar se você usar uma cláusula UNION em dois comandos SELECT que consultam um número diferente de colunas:

      • SELECT nome FROM refeicoes UNION SELECT nome, vitorias FROM torneios;

      Output

      ERROR 1222 (21000): The used SELECT statements have a different number of columns

      Outra maneira de consultar várias tabelas é através do uso de subconsultas ou subqueries. As subqueries (também conhecidas como consultas internas ou aninhadas) são consultas incluídas em outra consulta. Elas são úteis nos casos em que você está tentando filtrar os resultados de uma consulta com base no resultado de uma função de agregação separada.

      Para ilustrar essa ideia, digamos que você queira saber quais dos seus amigos ganharam mais partidas do que Bárbara. Em vez de consultar quantos jogos Bárbara venceu e, em seguida, executar outra consulta para ver quem ganhou mais jogos do que isso, você pode calcular ambos com uma única consulta:

      • SELECT nome, vitorias FROM torneios
      • WHERE vitorias > (
      • SELECT vitorias FROM torneios WHERE nome = 'Barbara'
      • );

      Output

      +--------+----------+ | nome | vitorias | +--------+----------+ | Dolly | 7 | | Etta | 4 | | Irma | 9 | | Gladys | 13 | +--------+----------+ 4 rows in set (0.00 sec)

      A subquerie nesse comando foi executada apenas uma vez; ele só precisava encontrar o valor da coluna vitorias na mesma linha que Barbara na coluna nome, e os dados retornados pela subquerie e pela consulta externa são independentes um do outro. Existem casos, no entanto, em que a consulta externa deve primeiro ler todas as linhas de uma tabela e comparar esses valores com os dados retornados pela subquerie para retornar os dados desejados. Nesse caso, a subquerie é referida como uma subquerie correlacionada.

      O comando a seguir é um exemplo de uma subquerie correlacionada. Esta consulta procura descobrir quais dos seus amigos ganharam mais jogos do que a média para aqueles com o mesmo tamanho de calçado:

      • SELECT nome, tamanho FROM torneios AS t
      • WHERE vitorias > (
      • SELECT AVG(vitorias) FROM torneios WHERE tamanho = t.tamanho
      • );

      Para que a consulta seja concluída, ela deve primeiro coletar as colunas nome e tamanho da consulta externa. Em seguida, ele compara cada linha desse result-set com os resultados da consulta interna, que determina o número médio de vitórias para indivíduos com tamanhos de sapatos idênticos. Como você só tem dois amigos com o mesmo tamanho de calçado, só pode haver uma linha no result-set:

      Output

      +------+---------+ | nome | tamanho | +------+---------+ | Etta | 9 | +------+---------+ 1 row in set (0.00 sec)

      Conforme mencionado anteriormente, as subquerie podem ser usadas para consultar resultados de várias tabelas. Para ilustrar isso com um exemplo final, digamos que você queria fazer um jantar surpresa para o melhor jogador de todos os tempos do grupo. Você pode encontrar qual dos seus amigos tem o melhor registro de boliche e retornar sua refeição favorita com a seguinte consulta:

      • SELECT nome, entrada, acompanhamento, sobremesa
      • FROM refeicoes
      • WHERE nome = (SELECT nome FROM torneios
      • WHERE vitorias = (SELECT MAX(vitorias) FROM torneios));

      Output

      +--------+---------+-----------------+------------+ | nome | entrada | acompanhamento | sobremesa | +--------+---------+-----------------+------------+ | Gladys | steak | fries | ice cream | +--------+---------+-----------------+------------+ 1 row in set (0.00 sec)

      Observe que esse comando não inclui apenas uma subquerie, mas também contém uma subquerie dentro dessa subquerie.

      Conclusão

      A realização de consultas é uma das tarefas mais comuns no domínio do gerenciamento de banco de dados. Existem várias ferramentas de administração de banco de dados, como phpMyAdmin or pgAdmin, que permitem realizar consultas e visualizar os resultados, mas a execução de comandos SELECT a partir da linha de comando ainda é um fluxo de trabalho amplamente praticado que também pode fornecer maior controle.

      Se você é novato no trabalho com SQL, lhe encorajamos a usar nosso Guia de Consulta Rápida SQL como referência e a revisar a documentação oficial do MySQL. Além disso, se você quiser saber mais sobre bancos de dados relacionais e SQL, os seguintes tutoriais podem ser de seu interesse:

      Por Mark Drake



      Source link