laravel-soar – A package for easily optimizing sql statements in Laravel

Time:2022-11-24

laravel-soar– An extension package for easily optimizing sql statements in Laravel applications.

laravel-soar - A package for easily optimizing sql statements in Laravel

Function

  • Support statement optimization based on heuristic algorithm
  • Support multi-column index optimization for complex queries (UPDATE, INSERT, DELETE, SELECT)
  • Support EXPLAIN information-rich interpretation
  • Supports SQL fingerprinting, compression and beautification
  • Support for Eloquent query builder methods to generate SQL optimization reports

Source link

related items

Environmental requirements

Install

$ composer require guanguans/laravel-soar --dev -vvv

publish service

$ php artisan vendor:publish --provider="Guanguans\\LaravelSoar\\SoarServiceProvider"

use

Generate sql scoring report example

use App\Models\Member;
    
Member::query()
    ->select([
        'id',
        'nickname',
    ])
    ->where('id', 100)
    // ->toSoarScore()
    // ->dumpSoarScore()
    ->ddSoarScore()
;

laravel-soar - A package for easily optimizing sql statements in Laravel

// example query builder usage
DB::table('yb_member')
    ->select('*')
    ->join('yb_member_account as yb_member_account', 'yb_member_account.member_id', '=', 'yb_member.id')
    ->whereRaw('1 <> 1')
    ->where('yb_member.nickname', 'like', 'admin')
    ->where('yb_member.username', 'like', '%admin%')
    ->whereRaw("substring(yb_member.username, 1, 5) = 'admin'")
    ->whereIn('yb_member.id', [110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120])
    ->orWhereNotNull('yb_member.realname')
    ->groupByRaw("yb_member.status, '100'")
    ->having('yb_member.id', '>', '100')
    ->inRandomOrder()
    // ->toSoarScore() // Generate sql scoring report
    // ->dumpSoarScore() // print sql score report
    ->ddSoarScore() // Print the sql scoring report and exit the application.
;

laravel-soar - A package for easily optimizing sql statements in Laravel

Generate explain Example of information interpretation report

// example query builder usage
DB::table('yb_member')
    ->select('*')
    ->join('yb_member_account as yb_member_account', 'yb_member_account.member_id', '=', 'yb_member.id')
    ->whereRaw('1 <> 1')
    ->where('yb_member.nickname', 'like', 'admin')
    ->where('yb_member.username', 'like', '%admin%')
    ->whereRaw("substring(yb_member.username, 1, 5) = 'admin'")
    ->whereIn('yb_member.id', [110, 120])
    ->orWhereNotNull('yb_member.realname')
    ->groupByRaw("yb_member.status, '100'")
    ->having('yb_member.id', '>', '100')
    ->inRandomOrder()
    // ->toSoarHtmlExplain() // Generate explain information interpretation report
    // ->dumpSoarHtmlExplain() // Print explain information interpretation report
    ->ddSoarHtmlExplain() // Print explain information interpretation report and exit the application.
;

laravel-soar - A package for easily optimizing sql statements in Laravel

Beautify sql statement

// example query builder usage
DB::table('yb_member')
    ->select('*')
    ->join('yb_member_account as yb_member_account', 'yb_member_account.member_id', '=', 'yb_member.id')
    ->whereRaw('1 <> 1')
    ->where('yb_member.nickname', 'like', 'admin')
    ->where('yb_member.username', 'like', '%admin%')
    ->whereRaw("substring(yb_member.username, 1, 5) = 'admin'")
    ->whereIn('yb_member.id', [110, 120])
    ->orWhereNotNull('yb_member.realname')
    ->groupByRaw("yb_member.status, '100'")
    ->having('yb_member.id', '>', '100')
    ->inRandomOrder()
    // ->toSoarPretty() // generate beautified sql
    // ->dumpSoarPretty() // Print the beautified sql
    ->dumpSoarPretty() // Print the beautified sql and exit the application.
;

laravel-soar - A package for easily optimizing sql statements in Laravel

Other usage examples

\Soar::score($sql); // generate sql scoring report
\Soar::mdExplain($sql); // Generate explain information interpretation report in markdown format
\Soar::htmlExplain($sql); // Generate Explain information interpretation report in html format
\Soar::syntaxCheck($sql); // sql syntax check
\Soar::fingerPrint($sql); // generate sql fingerprint
\Soar::pretty($sql); // Beautify sql
\Soar::md2html($sql); // Convert markdown format content to html format content
\Soar::help($sql); // output soar help command content
\Soar::exec($command); // execute any soar command