Posted on: December 14, 2021 01:07 PM
Posted by: Renato
Views: 1517
Como posso melhorar desempenho da consulta
# como-posso-melhorar-desempenho-da-consulta
# 10 dicas simples para otimizar seu site
Quanto mais veloz for seu site, mais satisfeito seu usuário ficará – e pode acabar se tornando um fiel cliente.
1. Modele corretamente o banco de dados
Esta primeira dica parece bem óbvia, mas o fato é que a maioria dos problemas com desempenho são devido à modelagem errada dos bancos de dados.
Um simples exemplo, que vejo com muita frequência, é salvar diversas informações, como dados pessoais e financeiros, na mesma coluna de uma tabela. Outro exemplo é salvar em um único campo os valores selecionados em checkboxes. Alguns programadores pegam todos os valores e os armazenam separados por vírgula em um único campo. Quando o usuário decide atualizar ou remover uma opção, o programador deve escrever linhas e mais linhas para separar os valores, comparar e depois unir de novo.
Modelagem errada prejudica o banco de dados, o programador, que precisará escrever mais códigos, e, principalmente, o desempenho da aplicação, que precisará processar todo esse código em excesso.
Modelagem de dados é a primeira fase do desenvolvimento. Estruture bem seu banco de dados, antes mesmo de começar a escrever seus códigos.
2. Não execute consultas dentro de loops
O velho problema do N + 1.
Imagine esta situação: um site de artigos, com uma tabela de usuários e outra de artigos, que possui um campo que relaciona o artigo com seu autor.
Se precisar listar os autores e os títulos de seus respectivos artigos, alguns programadores fariam o seguinte:
Selecionar todos os usuários: SELECT id, nome FROM usuarios;
Para cada usuário, buscar seus posts: SELECT titulo FROM posts WHERE user_id = id_do_usuario;
A segunda consulta seria executada N vezes, send N o número total de usuários. Ou seja, total de consultas seria N + 1 (N consultas de posts mais uma consulta para a lista de usuários).
Porém, bastariam duas consultas ou mesmo um simples JOIN para resolver o problema, o que pode trazer um ganho de desempenho de até 13 vezes.
Explico o Problema do N + 1 em mais detalhes neste artigo.
3. Selecione apenas os campos que realmente vai usar
É muito comum vermos consultas como SELECT * FROM tabela. O asterisco (*) vai trazer todas as colunas da tabela. Mas nem sempre precisamos de todas elas.
Muitas vezes, temos uma tabela com 20, 50 ou até 100 ou mais colunas. Serão raros os casos em que você precisará de todas elas.
Por isso sempre defina quais campos quer selecionar.
4. Faça cache das consultas
As consultas mais rápidas são aquelas não executadas. Explico: sempre que você executa uma consulta SQL, uma determinada quantidade de recursos do servidor é usada, além do tempo gasto, é claro.
Por isso, uma ótima opção para sites grandes e com muito tráfego é criar cache de dados, evitando que algumas consultas SQL frequentes sejam executadas pelo SGBD.
Há diversas ferramentas para isso, como, por exemplo, AdoDB e Memcached.
5. Use a cláusula LIMIT
Outra vez, parece uma dica óbvia – até demais. Porém, por incrível que pareça, tem muita gente por aí que não faz isso.
Imagine um site ou blog que exibe apenas 10 registros por página. O correto é usar LIMIT para trazer apenas esses 10.
Já vi códigos sem esse LIMIT, que buscam diversos registros e pegam, via programação, apenas os 10 primeiros.
6. Use JOINs em vez de sub-consultas
Quando programamos, usar sub-consultas é algo simples, lógico e funcional, como neste exemplo:
SELECT usuarios.id,
(
SELECT MAX(data_criacao)
FROM posts
WHERE usuario_id = usuarios.id
) AS ultimo_post
FROM usuarios u
Embora sub-consultas sejam úteis, usar JOIN é igualmente funcional e mais rápido.
A consulta anterior pode ser transformada nesta:
SELECT a.id, MAX(p.data_criacao) AS ultimo_post
FROM usuarios u
INNER JOIN posts p ON (u.id = p.usuario_id)
GROUP BY u.id
7. Use UNIONs em vez de ORs
A seguinte consulta utiliza a cláusula OR para filtrar os registros:
SELECT *
FROM tabela1, tabela2
WHERE tabela1.p = tabela2.q
OR tabela1.x = tabela2.y;
A cláusula UNION permite combinar os resultados de dois ou mais SELECTs.
A seguinte consulta, usando UNION, vai trazer os mesmos resultados que a consulta anterior, sendo mais rápida:
SELECT *
FROM tabela1, tabela2
WHERE tabela1.p = tabela2.q
UNION
SELECT *
FROM tabela1, tabela2
WHERE tabela1.x = tabela2.y
8. Utilize índices
Índices de banco de dados são como índices de bibliotecas. Eles permitem ao banco de dados encontrar os resultados sem perder tempo olhando registro por registro. Assim como um leitor olha o índice da biblioteca e encontra um livro com mais facilidade e rapidez.
Sempre crie índices em suas tabelas, indexando os campos que são frequentemente consultados.
9. Otimize as tabelas do banco de dados
Alguns SGBDs, como o MySQL, possuem ferramentas nativas para otimização de seus dados.
Se suas tabelas são frequentemente modificadas, recebendo novos dados e removendo outros, pode ser bom rodar uma otimização com certa frequência, para tornar as tabelas menores e mais rápidas.
A otimização toma um certo tempo para ser executada. Por isso é recomendável agendá-la para madrugadas ou para horários quando seu sistema tem pouco uso.
Para o MySQL, um simples exemplo de otimização pode ser feito com esta consulta:
OPTIMIZE TABLE nome_da_tabela;
10. Use um servidor de busca
Todas as dicas anteriores são válidas e trazem um certo ganho de desempenho. Mas já imaginou conseguir ganhos de desempenho de até 278 vezes?! Esse é o ganho que podemos conseguir com um Servidor de Busca como o ElasticSearch, que permite armazenar dados de forma otimizada para buscas extremamente rápidas.
Se o seu sistema faz muitos SELECTs, o ElasticSearch pode trazer ganhos fenomenais em desempenho.
|----------------------------------------------------------------------------------------------------------------------|
A importância dessa otimização vai aumentando enormemente na medida em que os acessos ao seu site (e consequentemente ao seu banco de dados) crescem. E quem já teve a oportunidade de trabalhar desenvolvendo um site com muitos acessos sabe que o acesso ao banco de dados pode ser um dos maiores gargalos (pontos potenciais de atrasos na exibição do conteúdo) em seu site.
Ele pode trabalhar de 3 maneiras diferentes, no nosso my.ini dentro do grupo [mysqld]:
```
query_cache_type = 0
Desligado
query_cache_type = 1
Ligado para todas as query’s
query_cache_type = 2
```
Ligado sobre demanda
A maneira mais indicada de você utilizar o SQL_CACHE é fazer um mapeamento da frequência de atualização das tabelas e utilizar o cache para as que não tenham uma frequência grande de atualização/inserção, para que assim, o cache lhe dê um ganho de performance, e não fique sobrecarregando o servidor tendo que invalidar muitas querys.
Para quem utiliza o MySQL temos a opção de cachear algumas consultas, usando a palavra chave "SQL_CACHE" após o 'SELECT', ou seja, guardar em memória consultas que são utilizadas com frequência e com a mesma sintaxe.
Quando falo mesma sintaxe incluo até diferenças entre letras maiúsculas e minúsculas, exemplo:
```
SELECT SQL_CACHE * FROM Produtos
Não é a mesma coisa que ...
select SQL_CACHE * from Produtos
```
Essas 2 instruções SQL são iguais e retornam os mesmo registros, mas, serão interpretadas pelo servidor MySQL como consultas distintas, ou seja, serão executadas 2 vezes simplesmente por causa da diferença entre as letras.
Dica: Pessoalmente prefiro escrever minhas instruções SQL sempre com as palavras reservadas em letra maiúscula (SELECT, FROM, INNER JOIN, WHERE e etc...), ajuda a evitar esses problemas com a interpretação da instrução além do código ficar mais bonito (legível).
Caso você faça a análise e veja que a maioria das suas tabelas devem ser cacheadas, você pode usar o processo inverso, ou seja, setar a variavel query_cache_type = 1, isso fará com que todas as consultas sejam cacheadas e especificar nos selects as que não devem cachear com SELECT SQL_NO_CACHE * FROM...
## Utilizar qualquer uma das funções abaixo inutiliza o cache em sua consulta:
```
BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME() DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() SLEEP() SYSDATE() UNIX_TIMESTAMP( USER() UUID() UUID_SHORT()
```
# Uma maneira seria usar o cache em conjunto com o mysqli_fetch_all.
## O PHP seria assim:
$limite = filter_var($_GET['limit'], FILTER_VALIDATE_INT) && $_GET['limit'] !== 0 ? $limite : 2147483647;
$query = "SELECT SQL_CACHE tb_time.id as id_time, tb_time.nome_time, tb_campeonato.nome_camp
FROM tb_campeonato, tb_time
WHERE tb_time.tb_campeonato_id = tb_campeonato.id
LIMIT ".$limite;
$result = mysqli_query($link, $query);
echo json_encode(mysqli_fetch_all($result));
Dessa forma o mysqli_fetch_all irá fazer um loop (não explicito) de todos os dados e então o json_encode irá entregar os dados como estão, assim quem deverá tratar os dados é o cliente.
O JQuery seria:
$.getJSON('seu_arquivo_do_php.php?limit=0',
function(json){
$.each(json, function(indice, data){
$('select').append(new Option(data[1], data[0]));
});
}
);
O JQuery por sua vez poderá apenas adicionar o option ao select. Também poderá limitar a quantidade a ser exibida. Você poderá então colocar uma opção para carregar mais do que X, por exemplo.
Pode ocorrer, no servidor, alto uso de memoria RAM!
Otimização do MYSQL (acredito que mais eficaz)!
Use CACHE:
Altere o my.cnf:
query_cache_type = 2
Execute como demonstrado:
SELECT SQL_CACHE ...
Use INDEX:
Execute:
ALTER TABLE tb_time
ADD INDEX tb_campeonato_id
(tb_campeonato_id);
ALTER TABLE tb_campeonato
ADD INDEX id
(id);
```php
private function itemprecofinalizado(){
if($this->get_request_method() != "GET"){
$this->response('',406);
}
$id =(int)$this->_request['id'];
//inicio versao em PDO
if($id > 0){
define( 'MYSQL_HOST', 'localhost' );
define( 'MYSQL_USER', 'root' );
define( 'MYSQL_PASSWORD', 'root' );
define( 'MYSQL_DB_NAME', 'banco' );
//melhor jeito, retorna o erro
try
{
$PDO = new PDO( 'mysql:host=' . MYSQL_HOST . ';dbname=' . MYSQL_DB_NAME, MYSQL_USER, MYSQL_PASSWORD );
}
catch ( PDOException $e )
{
echo 'Erro ao conectar com o MySQL: ' . $e->getMessage();
}
$sqlProduto = "SELECT * FROM item_pedido i INNER JOIN produto p WHERE i.id_pedido = '".$id."' AND i.id_produto = p.prod_id ORDER BY p.prod_descricao";
$stmt = $PDO->prepare( $sqlProduto );
$result1 = $stmt->execute();
$rowItems = $stmt->fetchAll( PDO::FETCH_ASSOC );
$quantProduto = $stmt->rowCount();
if($quantProduto > 0 ){
foreach ( $rowItems as $rowItem ){
$duplicidade = 0;//verifica preco em duplicidade
$id_pedido = $rowItem['id_pedido'];
$id_prod = $rowItem['id_produto'];
$prod_qtd = $rowItem['item_qtd'];
$prod_descricao = $rowItem['prod_descricao'];
$prod_unidade = $rowItem['prod_unidade'];
$prod_embalagem = $rowItem['prod_embalagem'];
//INICIO essa parte so pega o menor preco de cada item na tabela item_pedido_preco
$sqlMenor = "SELECT min(item_vl_imposto) FROM item_pedido_preco r WHERE r.id_pedido ='".$id."' AND r.id_produto ='".$id_prod."' and r.item_vl_imposto != 0";
$stmt = $PDO->prepare( $sqlMenor );
$result2 = $stmt->execute();
$rowMenors = $stmt->fetchAll( PDO::FETCH_ASSOC );
foreach ( $rowMenors as $rowMenor){
$menor = str_replace('.',',', $rowMenor['min(item_vl_imposto)']);
//echo 'helio'.$menor;
}
//FIM MENOR VALOR
//INICIO BUSCA TODOS OS USUARIOS DA TABELA E EM CADA UM DELES COLOCA TODOS OS ITENS, MESMO OS QUE ELES NAO COTAO
$sqlUsuario = "SELECT * FROM usuarios WHERE usu_nivel = 1 and usu_status = 1 ORDER BY usu_id";
$stmt = $PDO->prepare( $sqlUsuario );
$result3 = $stmt->execute();
$rowUsus = $stmt->fetchAll( PDO::FETCH_ASSOC );
$quantUsuario = $stmt->rowCount();
if($quantUsuario > 0){
foreach ( $rowUsus as $rowUsu){
$usu_id = $rowUsu['usu_id'];
//INICIO PRODUTO, USUARIO, E PEGAR OS PRECOS DE TODOS OS ITENS CONFORME INFORMACAO ACIMA
$sqlPreco = "SELECT * FROM item_pedido_preco WHERE id_pedido ='".$id."' AND id_produto ='".$id_prod."' AND id_usuario= '".$usu_id."' ORDER BY id_usuario";
$stmt = $PDO->prepare( $sqlPreco );
$result4 = $stmt->execute();
$rowPrecos = $stmt->fetchAll( PDO::FETCH_ASSOC );
$quantPreco = $stmt->rowCount();
if($quantPreco > 0){
foreach ( $rowPrecos as $rowPreco){
$id_usuario = $rowUsu['usu_id'];
$item_vl_anterior = str_replace('.',',',$rowPreco['item_vl_imposto']);
$item_vl_imposto = str_replace('.',',',$rowPreco['item_vl_imposto']);
$usu_titulo = $rowUsu['usu_titulo'];
$usu_imposto = $rowUsu['usu_imposto'];
if($rowPreco['item_vl_unit'] == 0 or $rowPreco['item_vl_unit'] == NULL):
$resultproduto[] = array(
'id_usuario' => $id_usuario,
'item_vl_imposto' => "-",
'item_vl_anterior' => "-",
'usu_titulo' => $usu_titulo,
'usu_imposto' => $usu_imposto
);
else:
$resultproduto[] = array(
'id_usuario' => $id_usuario,
'item_vl_imposto' => $item_vl_imposto,
'item_vl_anterior' => $item_vl_anterior,
'usu_titulo' => $usu_titulo,
'usu_imposto' => $usu_imposto
);
if($item_vl_imposto == $menor):
$duplicidade = $duplicidade+1;
endif;
endif;
}//foreach item_preco
}else{//se nao tiver no item_preco
$id_usuario = $usu_id;
$item_vl_anterior = "NULL";
$item_vl_imposto = "NULL";
$usu_titulo = $rowUsu['usu_titulo'];
$usu_imposto = $rowUsu['usu_imposto'];
$resultproduto[] = array(
'id_usuario' => $id_usuario,
'item_vl_imposto' => $item_vl_imposto,
'item_vl_anterior' => $item_vl_anterior,
'usu_titulo' => $usu_titulo,
'usu_imposto' => $usu_imposto
);
}//FIM
}//foreach usuario
}
if($duplicidade > 1)://informa qual item esta com preco em duplicidade
$prod_repeat = '*';
else:
$prod_repeat = '';
endif;
$result[] = array(
'id_pedido' => $id_pedido,
'id_produto' => $id_prod,
'prod_qtd' => $prod_qtd,
'prod_descricao' => $prod_descricao,
'prod_unidade' => $prod_unidade,
'prod_embalagem' => $prod_embalagem,
'item_vl_unit' => $resultproduto,
'itemmenorpreco' => $menor,
'prod_repeat' => $prod_repeat
);
$resultproduto = "";
}//foreach item_pedido
$this->response($this->json($result), 200);
}
$this->response('',204);
}
}
```
|----------------------------------------------------------------------------------------------------------------------|
Uma maneira seria usar o cache em conjunto com o mysqli_fetch_all.
O PHP seria assim:
$limite = filter_var($_GET['limit'], FILTER_VALIDATE_INT) && $_GET['limit'] !== 0 ? $limite : 2147483647;
$query = "SELECT SQL_CACHE tb_time.id as id_time, tb_time.nome_time, tb_campeonato.nome_camp
FROM tb_campeonato, tb_time
WHERE tb_time.tb_campeonato_id = tb_campeonato.id
LIMIT ".$limite;
$result = mysqli_query($link, $query);
echo json_encode(mysqli_fetch_all($result));
## Otimizar consultas com leftJoin Laravel?
Possuo tabelas vinculadas e quando necessito acessar as informações eu recrio a query:
DB::table('paciente AS pac')
->leftJoin('pessoa AS p', 'p.id', '=', 'pac.id_pessoa')
->select('p.nome, pac.ficha')
->where('pac.id', '=', $id_paciente)
->orderby('ag_prof.hora_inicial')
->get();
Como posso otimizar, para evitar a repetição desse trecho no Controller, devo criar na model ou em qual pasta?
Se pode utilizar Local Scopes que é um método feito no seu model eloquent para facilitar na programação, não seria uma otimização de banco, mas, sim um codificação limpa, exemplo:
use Illuminate\Database\Eloquent\Model;
class Paciente extends Model
{
public function scopeJoinPessoa($query, $id_paciente)
{
return $query->leftJoin('pessoa', 'pessoa.id','=', 'paciente.id_pessoa')
->select('pessoa.nome, paciente.ficha')
->where('paciente.id', '=', $id_paciente);
}
}
e utilizar no seu código assim:
Paciente::joinPessoa($id_paciente)->get();
Observação: no seu exemplo tem um orderBy, que não existe essa relação, se por ventura esqueceu é só adicionar mais um join no método de scopes.
Esse seria um forma de resolver esse problema.
|----------------------------------------------------------------------------------------------------------------------|
(copiado de laracasts) Isso provavelmente também ajudará:
DB::connection()->disableQueryLog();
"Por padrão, Laravel mantém um log na memória de todas as consultas que foram executadas para a solicitação atual. No entanto, em alguns casos, como ao inserir um grande número de linhas, isso pode causar o aplicativo para usar excesso de memória ".
|----------------------------------------------------------------------------------------------------------------------|
## Cache Queries Results
Caching the results of the queries that are frequently run is a great way of improving Laravel 5.5 performance. For this, I recommend the remember function, that is used as follows:
$posts = Cache::remember('index.posts', 30, function()
{return Post::with('comments', 'tags', 'author', 'seo')->whereHidden(0)->get();});
## Use “Eager Loading” para dados
O Laravel oferece um ótimo ORM para lidar com bancos de dados. Conhecido como Eloquent, ele cria modelos que abstraem as tabelas de banco de dados dos desenvolvedores. Usando estruturas simples, os desenvolvedores podem usar o Eloquent para lidar com todas as operações CRUD em PHP. Quando o Eloquent usa o carregamento antecipado, ele recupera todos os modelos de objetos associados em resposta à consulta inicial. Isso aumenta a resposta do aplicativo. Vamos comparar o carregamento lento e o carregamento rápido:
- A consulta de carregamento lento será semelhante a:
$books = App\Book::all();
foreach ($books as $book) {
echo $book->author->name;}
## Em contraste, a consulta de carregamento antecipado será semelhante a:
$books = App\Book::with('author')->get();
foreach ($books as $book) {
echo $book->author->name;
}
|----------------------------------------------------------------------------------------------------------------------|
- https://duvidas.bem-vindo.net/348/otimizando-consultas-com-o-explain-do-mysql
- https://www.gigasystems.com.br/artigo/74/sql_cache-otimizando-suas-consultas-sql-do-mysql
- https://rubsphp.blogspot.com/2011/02/otimizacao-de-consultas-sql-usando.html
- https://pt.stackoverflow.com/questions/146765/otimizar-carregamento-de-select-phpmysql
- https://imasters.com.br/banco-de-dados/10-dicas-simples-para-otimizar-seu-site
- https://laravel.com/docs/5.4/eloquent
- https://laravel.com/docs/5.4/eloquent#local-scopes
- https://pt.stackoverflow.com/questions/207784/otimizar-consultas-com-leftjoin-laravel
- https://tableless.com.br/10-dicas-simples-para-acelerar-seu-site-ate-278-vezes/
- https://www.cloudways.com/blog/mysql-performance-tuning/
- https://www.cloudways.com/blog/laravel-performance-optimization/
- https://blog.goinfinite.net/2020/09/3-dicas-para-otimizar-sua-base-de-dados-e-acelerar-o-seu-site/
Donate to Site
Renato
Developer