Uma vez detectadas as consultas lentas é preciso avaliar como o MySQL está executando estes comandos.
Para isto faz-se uso do comando EXPLAIN, que deve ser colocado antes do comando SELECT a ser estudado.
Este comando irá exibir o plano de execução escolhido pelo otimizador.
Vejamos dois exemplos:
EXEMPLO 1: Consulta Simples:
Conectamos no MySQL e vamos utilizar o banco DB_TESTE que temos criado para este exemplo e que não tem nenhuma tabela criada:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | DB_TESTE | | mysql | | performance_schema | | sys | | test_replication | +--------------------+ 6 rows in set (0.00 sec) mysql> use DB_TESTE; Database changed mysql> show tables; Empty set (0.00 sec)
Vamos agora criar uma tabela que armazenará o nome dos pais e já vamos inserir alguns registros:
mysql> CREATE TABLE tb_pai (id_pai int not null auto_increment, nome_pai varchar(255), primary key(id_pai)) Engine=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql> insert into tb_pai (nome_pai) values ('Antonio da Silva');
Query OK, 1 row affected (0.02 sec)
mysql> insert into tb_pai (nome_pai) values ('Jose Pereira');
Query OK, 1 row affected (0.07 sec)
mysql> insert into tb_pai (nome_pai) values ('Joao Antunes');
Query OK, 1 row affected (0.03 sec)
mysql> select * from tb_pai;
+--------+------------------+
| id_pai | nome_pai |
+--------+------------------+
| 1 | Antonio da Silva |
| 2 | Jose Pereira |
| 3 | Joao Antunes |
+--------+------------------+
3 rows in set (0.00 sec)
mysql>
Certo agora vamos realizar duas consultas.
Vamos consultar o pai com ID igual a 1, registro que pertence ao pai Antonio da Silva e depois vamos filtrar o pai pelo nome exato: Jose Pereira:
mysql> select * from tb_pai where id_pai = 1; +--------+------------------+ | id_pai | nome_pai | +--------+------------------+ | 1 | Antonio da Silva | +--------+------------------+ 1 row in set (0.00 sec) mysql> select * from tb_pai where nome_pai = 'Jose Pereira'; +--------+--------------+ | id_pai | nome_pai | +--------+--------------+ | 2 | Jose Pereira | +--------+--------------+ 1 row in set (0.00 sec)
Tudo certo. Mas como o MYSQL trata isso internamente? Qual plano de execução? Index são utilizados?
Para obter estas informações vamos utilizar o comando: EXPLAIN, como já mencionado no início do artigo.
Vamos lá, vamos obter o plano de execução da primeira consulta pelo ID do pai:
mysql> explain select * from tb_pai where id_pai = 1\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb_pai partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)
Agora o plano de execução da segunda consulta, pelo nome exato do pai:
mysql> explain select * from tb_pai where nome_pai = 'Jose Pereira'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tb_pai partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 filtered: 33.33 Extra: Using where 1 row in set, 1 warning (0.00 sec)
Certo. Então vamos ver algumas informações importantes através de algumas colunas que apareceram após a emissão do EXPLAIN:
Colunas:
- select_type: SIMPLE: O resultado desta coluna mostra o tipo de consulta que está sendo processada. Estas podem ser consultas simples ou sem sub-consultas (SIMPLE) e SUB_QUERY ou UNION para comando que possuem consultas aninhadas.
- type: ALL: A coluna TYPE exibe o tipo de acesso ou algoritmo de busca utilizado para a leitura dos dados, um ALL por exemplo, representa um full table scan e um const representa uma busca pela chave primária. Obs. No final do artigo tem a imagem de uma "Tabela TYPE" que apresenta alguns dos valores possíveis para esta coluna, indo do melhor para o pior tipo.
- possible_keys: PRIMARY: O EXPLAIN fornece quais os índices estão disponíveis ou são possíveis para a utilização na execução do comando.
- key: PRIMARY: A coluna Key mostra o índice que está sendo realmente utilizado para a leitura do dados,(NULL, caso não esteja fazendo uso de índices).
- ref: const: A coluna REF indica a coluna utilizada para referenciar tabelas em JOIN ou comparada com indices.
- rows: 1: A coluna ROWS fornece o número estimado de linhas lidas pelo MySQL para buscar o resultado.
- Extra: O EXTRA fornece informações adicionais sobre a execução, tais como, o uso de tabelas temporárias, ordenação, dentre outros.
Sendo assim, pelo plano de execução podemos ver que a consulta está utilizando o index existente da chave primária para consulta pelo ID do pai, como podemos ver no plano:
mysql> show index from tb_pai; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tb_pai | 0 | PRIMARY | 1 | id_pai | A | 2 | NULL | NULL | | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
O que não acontece na consulta pelo nome exato do pai = 'Jose Pereira' que está fazendo full na tabela, como dito pelo EXPLAIN.
Informações sobre os TYPEs de acessos: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
Informações sobre o output do explain: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-output-columns
EXEMPLO 2: JOIN entre duas tabelas:
Agora vamos criar outra tabela para um join, a tabela de filhos e inserir alguns registros:
mysql> CREATE TABLE tb_filho (id_pai int not null, id_filho int not null, nome_filho varchar(255), primary key(id_pai,id_filho)) Engine=InnoDB; Query OK, 0 rows affected (0.09 sec) mysql> ALTER TABLE tb_filho ADD CONSTRAINT fk_tb_filho FOREIGN KEY ( id_pai ) REFERENCES tb_pai ( id_pai ); Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into tb_filho (id_pai, id_filho, nome_filho) values (1,1,'Ana'); Query OK, 1 row affected (0.35 sec) mysql> insert into tb_filho (id_pai, id_filho, nome_filho) values (1,2,'Lucas'); Query OK, 1 row affected (0.04 sec) mysql> select * from tb_filho; +--------+----------+------------+ | id_pai | id_filho | nome_filho | +--------+----------+------------+ | 1 | 1 | Ana | | 1 | 2 | Lucas | +--------+----------+------------+ 2 rows in set (0.01 sec)
Vamos ver o plano de execução da query com join entre duas tabelas, a tabela pai e a tabela filho, abaixo:
mysql> select p.nome_pai, f.id_filho, f.nome_filho from tb_filho f, tb_pai p where p.id_pai = f.id_pai; +------------------+----------+------------+ | nome_pai | id_filho | nome_filho | +------------------+----------+------------+ | Antonio da Silva | 1 | Ana | | Antonio da Silva | 2 | Lucas | +------------------+----------+------------+ 2 rows in set (0.00 sec) mysql> explain select p.nome_pai, f.id_filho, f.nome_filho from tb_filho f, tb_pai p where p.id_pai = f.id_pai\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: f partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 3 filtered: 50.00 Extra: Using where; Using join buffer (Block Nested Loop) 2 rows in set, 1 warning (0.00 sec)
Perceba que embora existir a chave primária, foi feito um "Full" nas duas tabelas.
Vamos ver agora o plano se filtrar-mos pelo ID do pai:
mysql> explain select p.nome_pai, f.id_filho, f.nome_filho from tb_filho f, tb_pai p where p.id_pai = f.id_pai and p.id_pai = 1\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: p partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: f partitions: NULL type: ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 2 filtered: 100.00 Extra: NULL 2 rows in set, 1 warning (0.00 sec)
Repare agora que está sendo utilizado uma consulta via Chave primária pela tabela pai, e através da chave é pesquisado os registros na tabela filho.
Tabela Type:
Por hoje é só pessoal,
Ronaldo.
Nenhum comentário:
Postar um comentário