Posted on: March 14, 2022 12:15 PM

Posted by: Renato

Categories: Laravel PHP mysql postgresql database 100DaysOfCode

Views: 4714

18 dicas para otimizar consultas de banco de dados laravel

Se seu aplicativo estiver lento ou fazendo muitas consultas ao banco de dados, siga as dicas de otimização de desempenho abaixo para melhorar o tempo de carregamento do aplicativo.

Eu compartilhei dicas para otimizar suas consultas de banco de dados mysql, eloquent e raw.

1. Recuperando grandes conjuntos de dados

Esta dica se concentra principalmente em melhorar o uso de memória do seu aplicativo ao lidar com grandes conjuntos de dados.

Se seu aplicativo precisar processar um grande conjunto de registros, em vez de recuperar todos de uma vez, você poderá recuperar um subconjunto de resultados e processá-los em grupos.

Para recuperar um grande conjunto de resultados de uma tabela chamada posts, normalmente faríamos como abaixo.

$posts = Post::all(); // when using eloquent
$posts = DB::table('posts')->get(); // when using query builder

foreach ($posts as $post){
 // Process posts
}

Os exemplos acima recuperarão todos os registros da tabela de postagens e os processarão. E se esta tabela tiver 1 milhão de linhas? Ficaremos facilmente sem memória.

Para evitar problemas ao lidar com grandes conjuntos de dados, podemos recuperar um subconjunto de resultados e processá-los conforme abaixo.

opção 1: usando pedaço

// when using eloquent
$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

// when using query builder
$posts = DB::table('posts')->chunk(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

O exemplo acima recupera 100 registros da tabela posts, os processa, recupera outros 100 registros e os processa. Essa iteração continuará até que todos os registros sejam processados.

Essa abordagem tornará mais consultas de banco de dados, mas altamente eficiente em termos de memória. Normalmente, o processamento de grandes conjuntos de dados será feito em segundo plano. Portanto, não há problema em fazer mais consultas ao executar em segundo plano para evitar ficar sem memória ao processar grandes conjuntos de dados.

opção 2: Usando o cursor

// when using eloquent
foreach (Post::cursor() as $post){
   // Process a single post
}

// when using query builder
foreach (DB::table('posts')->cursor() as $post){
   // Process a single post
}

O exemplo acima fará uma única consulta ao banco de dados, recuperará todos os registros da tabela e hidratará os modelos eloquentes um por um. Essa abordagem fará apenas uma consulta ao banco de dados para recuperar todos os posts. Mas usa gerador de php para otimizar o uso de memória.

quando você pode usar isso?

Embora isso otimize bastante o uso de memória no nível do aplicativo, como estamos recuperando todas as entradas de uma tabela, o uso de memória na instância do banco de dados ainda será maior.

É melhor usar o cursor Se seu aplicativo da Web que está executando seu aplicativo tiver menos memória e a instância do banco de dados tiver mais memória. No entanto, se sua instância de banco de dados não tiver memória suficiente, é melhor manter o bloco.

opção 3: usando chunkById

// when using eloquent
$posts = Post::chunkById(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

// when using query builder
$posts = DB::table('posts')->chunkById(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

A principal diferença entre chunke chunkByIdé que o trecho é recuperado com base em offsete limit. Considerando que chunkByIdrecupera os resultados do banco de dados com base em um idcampo. Esse campo de id geralmente é um campo inteiro e, na maioria dos casos, seria um campo de incremento automático.

As consultas feitas por chunke chunkByIdforam as seguintes.

pedaço

select * from posts offset 0 limit 100

select * from posts offset 101 limit 100

chunkById

select * from posts order by id asc limit 100

select * from posts where id > 100 order by id asc limit 100

Geralmente, o uso de limite com deslocamento é mais lento e devemos tentar evitar usá-lo. Este artigo explica em detalhes sobre o problema com o uso de deslocamento.

Como chunkById está usando o campo id que é um inteiro, e a consulta está usando um where clause, a consulta será muito mais rápida.

quando você pode usar chunkById? - Se sua tabela de banco de dados possui uma primary key columncoluna que é um campo de incremento automático.

2. Selecione apenas as colunas que você precisa

Normalmente, para recuperar resultados de uma tabela de banco de dados, faríamos o seguinte.

$posts = Post::find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->first(); //When using query builder

O código acima resultará em uma consulta como abaixo

select * from posts where id = 1 limit 1

Como você pode ver, a consulta está fazendo um select *. Isso significa que ele está recuperando todas as colunas da tabela do banco de dados. Isso é bom se realmente precisarmos de todas as colunas da tabela.

Em vez disso, se precisarmos apenas de colunas específicas (id, title), podemos recuperar apenas essas colunas conforme abaixo.

$posts = Post::select(['id','title'])->find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first(); //When using query builder

O código acima resultará em uma consulta como abaixo

select id,title from posts where id = 1 limit 1

3. Use arrancar quando precisar exatamente de uma ou duas colunas do banco de dados

Esta dica se concentra mais no tempo gasto depois que os resultados são recuperados do banco de dados. Isso não afeta o tempo real da consulta.

Como mencionei acima, para recuperar colunas específicas, faríamos

$posts = Post::select(['title','slug'])->get(); //When using eloquent
$posts = DB::table('posts')->select(['title','slug'])->get(); //When using query builder

Quando o código acima é executado, ele faz o seguinte nos bastidores.

• Executa select title, slug from postsa consulta no banco de dados
• Cria um novo Postobjeto de modelo para cada linha recuperada (para o construtor de consultas, ele cria um objeto padrão PHP)
• Cria uma nova coleção com os Postmodelos
• Devolve a coleção

Agora, para acessar os resultados, faríamos

foreach ($posts as $post){
    // $post is a Post model or php standard object
    $post->title;
    $post->slug;
}

A abordagem acima tem uma sobrecarga adicional de hidratação do Postmodelo para cada linha e criação de uma coleção para esses objetos. Isso seria melhor se você realmente precisasse da Postinstância do modelo em vez dos dados. Mas se tudo o que você precisa são esses dois valores, você pode fazer o seguinte.

$posts = Post::pluck('title', 'slug'); //When using eloquent
$posts = DB::table('posts')->pluck('title','slug'); //When using query builder

Quando o código acima é executado, ele faz o seguinte nos bastidores.

• Executa select title, slug from postsa consulta no banco de dados
• Cria um array com titleas array valuee slugas array key.
• Retorna o array(formato do array: [ slug => title, slug => title ])

Agora, para acessar os resultados, faríamos

foreach ($posts as $slug => $title){
    // $title is the title of a post
    // $slug is the slug of a post
}

Se você quiser recuperar apenas uma coluna, você pode fazer

$posts = Post::pluck('title'); //When using eloquent
$posts = DB::table('posts')->pluck('title'); //When using query builder
foreach ($posts as  $title){
    // $title is the title of a post
}

A abordagem acima elimina a criação de Postobjetos para cada linha. Reduzindo assim o uso de memória e o tempo gasto no processamento dos resultados da consulta.

Eu recomendaria usar a abordagem acima apenas no novo código. Pessoalmente, sinto que voltar e refatorar seu código para seguir a dica acima não é digno do tempo gasto nisso. Refatore o código existente apenas se o seu código estiver processando grandes conjuntos de dados ou se você tiver tempo livre.

4. Conte linhas usando consulta em vez de coleção

Para contar o número total de linhas em uma tabela, normalmente faríamos

$posts = Post::all()->count(); //When using eloquent
$posts = DB::table('posts')->get()->count(); //When using query builder

Isso gerará a seguinte consulta

select * from posts

A abordagem acima irá recuperar todas as linhas da tabela, carregá-las em um collectionobjeto e contar os resultados. Isso funciona bem quando há menos linhas na tabela do banco de dados. Mas rapidamente ficaremos sem memória à medida que a tabela crescer.

Em vez da abordagem acima, podemos contar diretamente o número total de linhas no próprio banco de dados.

$posts = Post::count(); //When using eloquent
$posts = DB::table('posts')->count(); //When using query builder

Isso gerará a seguinte consulta

select count(*) from posts

Contar linhas no sql é um processo lento e tem um desempenho muito ruim quando a tabela do banco de dados tem tantas linhas. É melhor evitar ao máximo a contagem de linhas.

5. Evite consultas N+1 pelo relacionamento de carregamento antecipado

Você já deve ter ouvido falar dessa dica um milhão de vezes. Então vou mantê-lo o mais curto e simples possível. Vamos supor que você tenha o seguinte cenário

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        return view('posts.index', ['posts' => $posts ]);
    }
}

// posts/index.blade.php file

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
    </li>
@endforeach

O código acima está recuperando todas as postagens e exibindo o título da postagem e seu autor na página da web. O código acima pressupõe que você tenha um authorrelacionamento em seu modelo de postagem.

A execução do código acima resultará na execução das seguintes consultas.

select * from posts // Assume this query returned 5 posts
select * from authors where id = { post1.author_id }
select * from authors where id = { post2.author_id }
select * from authors where id = { post3.author_id }
select * from authors where id = { post4.author_id }
select * from authors where id = { post5.author_id }

Como você pode ver, temos uma consulta para recuperar as postagens e 5 consultas para recuperar os autores das postagens (já que assumimos que temos 5 postagens.) Portanto, para cada postagem recuperada, ele está fazendo uma consulta separada para recuperar seu autor.

Portanto, se houver um número N de postagens, ele fará N + 1 consultas (1 consulta para recuperar postagens e N consultas para recuperar o autor de cada postagem). Isso é comumente conhecido como problema de consulta N+1.

Para evitar isso, carregue ansiosamente o relacionamento do autor nas postagens como abaixo.

$posts = Post::all(); // Avoid doing this
$posts = Post::with(['author'])->get(); // Do this instead

A execução do código acima resultará na execução das seguintes consultas.

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

6. Relacionamento aninhado de carga ansiosa

No exemplo acima, considere que o autor pertence a uma equipe e você deseja exibir o nome da equipe também. Portanto, no arquivo blade, você faria como abaixo.

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
        <p>Author's Team: {{ $post->author->team->name }}</p>
    </li>
@endforeach

Agora fazendo o abaixo

$posts = Post::with(['author'])->get();

Resultará nas seguintes consultas

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id = { author1.team_id }
select * from teams where id = { author2.team_id }
select * from teams where id = { author3.team_id }
select * from teams where id = { author4.team_id }
select * from teams where id = { author5.team_id }

Como você pode ver, apesar de estarmos ansiosos para carregar o authorsrelacionamento, ele ainda está fazendo mais consultas. Porque não estamos ansiosos para carregar o teamrelacionamento no authors.

Podemos corrigir isso fazendo o seguinte.

$posts = Post::with(['author.team'])->get();

A execução do código acima resultará na execução das seguintes consultas.

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

Então, ao carregar o relacionamento aninhado rapidamente, reduzimos o número total de consultas de 11 para 3.

7. Não carregue o relacionamento belongsTo se você só precisa de seu id

Imagine que você tem duas tabelas postse authors. A tabela Posts tem uma coluna author_idque representa um relacionamento belongsTo na tabela autores.

Para obter o id do autor de uma postagem, normalmente faríamos

$post = Post::findOrFail(<post id>);
$post->author->id; 

Isso resultaria na execução de duas consultas.

select * from posts where id = <post id> limit 1
select * from authors where id = <post author id> limit 1

Em vez disso, você pode obter diretamente o ID do autor fazendo o seguinte.

$post = Post::findOrFail(<post id>);
$post->author_id; // posts table has a column author_id which stores id of the author

Quando posso usar a abordagem acima?

Você pode usar a abordagem acima quando tiver certeza de que uma linha sempre existirá na tabela de autores se for referenciada na tabela de postagens.

8. Evite consultas desnecessárias

Muitas vezes, fazemos consultas de banco de dados que não são necessárias. Considere o exemplo abaixo.

<?php

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        $private_posts = PrivatePost::all();
        return view('posts.index', ['posts' => $posts, 'private_posts' => $private_posts ]);
    }
}

O código acima está recuperando linhas de duas tabelas diferentes (ex: posts, private_posts) e passando-as para visualização. O arquivo de visualização se parece com o abaixo.

// posts/index.blade.php

@if( request()->user()->isAdmin() )
    <h2>Private Posts</h2>
    <ul>
        @foreach($private_posts as $post)
            <li>
                <h3>{{ $post->title }}</h3>
                <p>Published At: {{ $post->published_at }}</p>
            </li>
        @endforeach
    </ul>
@endif

<h2>Posts</h2>
<ul>
    @foreach($posts as $post)
        <li>
            <h3>{{ $post->title }}</h3>
            <p>Published At: {{ $post->published_at }}</p>
        </li>
    @endforeach
</ul>

Como você pode ver acima, $private_postsé visível apenas para um usuário que é um admin. Resto todos os usuários não podem ver essas postagens.

O problema aqui é, quando estamos fazendo

$posts = Post::all();
$private_posts = PrivatePost::all();

Estamos fazendo duas consultas. Um para obter os registros da poststabela e outro para obter os registros da private_poststabela.

Os registros da private_poststabela são visíveis apenas para o admin user. Mas ainda estamos fazendo a consulta para recuperar esses registros para todos os usuários mesmo que não estejam visíveis.

Podemos modificar nossa lógica abaixo para evitar essa consulta extra.

$posts = Post::all();
$private_posts = collect();
if( request()->user()->isAdmin() ){
    $private_posts = PrivatePost::all();
}

Ao alterar nossa lógica para acima, estamos fazendo duas consultas para o usuário administrador e uma consulta para todos os outros usuários.

9. Mesclar consultas semelhantes

Algumas vezes precisamos fazer consultas para recuperar diferentes tipos de linhas da mesma tabela.

$published_posts = Post::where('status','=','published')->get();
$featured_posts = Post::where('status','=','featured')->get();
$scheduled_posts = Post::where('status','=','scheduled')->get();

O código acima está recuperando linhas com status diferente da mesma tabela. O código resultará em fazer as seguintes consultas.

select * from posts where status = 'published'
select * from posts where status = 'featured'
select * from posts where status = 'scheduled'

Como você pode ver, está fazendo 3 consultas diferentes na mesma tabela para recuperar os registros. Podemos refatorar esse código para fazer apenas uma consulta ao banco de dados.

$posts =  Post::whereIn('status',['published', 'featured', 'scheduled'])->get();
$published_posts = $posts->where('status','=','published');
$featured_posts = $posts->where('status','=','featured');
$scheduled_posts = $posts->where('status','=','scheduled');

select * from posts where status in ( 'published', 'featured', 'scheduled' )

O código acima está fazendo uma consulta para recuperar todas as postagens que tenham qualquer um dos status especificados e criando coleções separadas para cada status, filtrando as postagens retornadas por seu status. Portanto, ainda teremos três variáveis ​​diferentes com seus status e faremos apenas uma consulta.

10. Adicione índice a colunas consultadas com frequência

Se você estiver fazendo consultas adicionando uma wherecondição em um stringbased column, é melhor adicionar um índice à coluna. As consultas são muito mais rápidas ao consultar linhas com uma coluna de índice.

$posts = Post::where('status','=','published')->get();

No exemplo acima, estamos consultando registros adicionando uma condição where à statuscoluna. Podemos melhorar o desempenho da consulta adicionando a seguinte migração de banco de dados.

Schema::table('posts', function (Blueprint $table) {
   $table->index('status');
});

11. Use simplePaginate em vez de Paginate

Ao paginar os resultados, normalmente faríamos

$posts = Post::paginate(20);

Isso fará 2 consultas. 1 para recuperar os resultados paginados e outro para contar o número total de linhas na tabela. Contar linhas em uma tabela é uma operação lenta e afetará negativamente o desempenho da consulta.

Então, por que o laravel conta o número total de linhas?

Para gerar links de paginação, o Laravel conta o número total de linhas. Assim, quando os links de paginação são gerados, você sabe de antemão quantas páginas estarão lá e qual é o número da página anterior. Assim, você pode navegar para qualquer página que desejar facilmente.

Por outro lado, fazer simplePaginatenão contará o número total de linhas e a consulta será muito mais rápida que a paginateabordagem. Mas você perderá a capacidade de saber o número da última página e poderá pular para páginas diferentes.

Se sua tabela de banco de dados tiver tantas linhas, é melhor evitar paginatee fazer isso simplePaginate.

$posts = Post::paginate(20); // Generates pagination links for all the pages
$posts = Post::simplePaginate(20); // Generates only next and previous pagination links

Quando usar paginar vs paginar simples?

Veja a tabela de comparação abaixo e determine se paginar ou paginar simples é ideal para você

12. Evite usar curingas iniciais (palavra-chave LIKE)

Ao tentar consultar resultados que correspondam a um padrão específico, normalmente iríamos com

select * from table_name where column like %keyword%

A consulta acima resultará em uma verificação completa da tabela. Se soubermos que a palavra-chave ocorre no início do valor da coluna, podemos consultar os resultados conforme abaixo.

select * from table_name where column like keyword%

13. evite usar funções SQL na cláusula where

É sempre melhor evitar funções SQL na cláusula where, pois elas resultam em varredura completa da tabela. Vejamos o exemplo abaixo. Para consultar resultados com base em determinada data, normalmente faríamos

    $posts = POST::whereDate('created_at', '>=', now() )->get();

Isso resultará em uma consulta semelhante à abaixo

select * from posts where date(created_at) >= 'timestamp-here'

A consulta acima resultará em uma verificação completa da tabela, porque a condição where não é aplicada até que a datefunção seja avaliada.

Podemos refatorar isso para evitar a datefunção sql como abaixo

    $posts = Post::where('created_at', '>=', now() )->get();

select * from posts where created_at >= 'timestamp-here'

14. evite adicionar muitas colunas a uma tabela

É melhor limitar o número total de colunas em uma tabela. Bancos de dados relacionais, como o mysql, podem ser aproveitados para dividir as tabelas com tantas colunas em várias tabelas. Eles podem ser unidos usando suas chaves primárias e estrangeiras.

Adicionar muitas colunas a uma tabela aumentará o tamanho do registro individual e diminuirá a velocidade da verificação da tabela. Quando você está fazendo uma select *consulta, você acabará recuperando um monte de colunas que você realmente não precisa.

15. colunas separadas com tipo de dados de texto em sua própria tabela

Esta dica é de experiência pessoal e não é uma maneira padrão de arquitetar suas tabelas de banco de dados. Eu recomendo seguir esta dica apenas se sua tabela tiver muitos registros ou crescer rapidamente.

Se uma tabela possui colunas que armazenam grandes quantidades de dados (ex: colunas com tipo de dados TEXT), é melhor separá-las em uma tabela própria ou em uma tabela que será solicitada com menos frequência.

Quando a tabela tem colunas com grandes quantidades de dados, o tamanho de um registro individual cresce muito. Eu pessoalmente observei que isso afetou o tempo de consulta em um de nossos projetos.

Considere um caso em que você tem uma tabela chamada postscom uma coluna contentque armazena o conteúdo da postagem do blog. O conteúdo da postagem do blog será realmente enorme e, muitas vezes, você precisará desses dados apenas se uma pessoa estiver visualizando essa postagem específica do blog.

Portanto, separar esta coluna da tabela de postagens melhorará drasticamente o desempenho da consulta quando houver muitas postagens.

16. Melhor maneira de recuperar as últimas linhas de uma tabela

Quando queremos recuperar as últimas linhas de uma tabela, geralmente fazemos

$posts = Post::latest()->get();
// or $posts = Post::orderBy('created_at', 'desc')->get();

A abordagem acima produzirá a seguinte consulta sql.

select * from posts order by created_at desc

A consulta está basicamente ordenando as linhas em ordem decrescente com base na coluna created_at. Como a coluna created_at é uma coluna baseada em string, geralmente é mais lento ordenar os resultados dessa maneira.

Se sua tabela de banco de dados tiver um id de chave primária de incremento automático, na maioria dos casos, a linha mais recente sempre terá o id mais alto. Como o campo id é um campo inteiro e também uma chave primária, é muito mais rápido ordenar os resultados com base nessa chave. Portanto, a melhor maneira de recuperar as últimas linhas é a seguinte.

$posts = Post::latest('id')->get();
// or $posts = Post::orderBy('id', 'desc')->get();

select * from posts order by id desc

17. otimizar inserções do mysql

Até agora, analisamos a otimização selectde consultas para recuperar resultados de um banco de dados. Na maioria dos casos, precisamos apenas otimizar as consultas de leitura. Mas às vezes encontramos a necessidade de otimizar inserte updateconsultas. Encontrei um artigo interessante sobre como otimizar inserções do mysql que ajudará a otimizar inserções e atualizações lentas.

18. Inspecione e otimize as consultas

Não existe uma solução universal ao otimizar consultas em laravel. Só você sabe o que seu aplicativo está fazendo, quantas consultas está fazendo, quantas delas estão realmente em uso. Portanto, inspecionar as consultas feitas pelo seu aplicativo o ajudará a determinar e reduzir o número total de consultas feitas.

Existem certas ferramentas que ajudam você a inspecionar as consultas feitas em cada página.

Observação: é recomendável não executar nenhuma dessas ferramentas em seu ambiente de produção. Executá-los em seus aplicativos de produção degradará o desempenho do aplicativo e, quando comprometidos, usuários não autorizados terão acesso a informações confidenciais.

• Laravel Debugbar - Laravel debugbar tem uma aba chamadadatabaseque irá mostrar todas as consultas executadas quando você visita uma página. Visite todas as páginas do seu aplicativo e veja as consultas executadas em cada página.
• Clockwork - Clockwork é o mesmo que laravel debugbar. Mas, em vez de injetar uma barra de ferramentas em seu site, ela exibirá as informações de depuração emdeveloper tools windowou como uma interface de usuário autônoma visitandoyourappurl/clockwork.
• Telescópio Laravel - O telescópio Laravel é um maravilhoso companheiro de depuração ao desenvolver aplicativos laravel localmente. Depois que o telescópio estiver instalado, você poderá acessar o painel visitandoyourappurl/telescope. No painel do telescópio, vá até aqueriesguia e ele exibirá todas as consultas que estão sendo executadas pelo seu aplicativo.

Gostou de ler o artigo acima? compartilhe seus pensamentos.

Renato

Developer

No comments yet! Be the first to comment