laravel 框架 一些性能的小技巧

laravel 框架 一些性能的小技巧

1.upsert
例子：
Role::query()->upsert(['name' => 'Founder_12', 'guard_name' => 'web_12','id'=>15], 'id');

首先会查询id = 15 的 记录是否存在？存在更新，不存在新增；

ps:这里相信大家会说 这和 updateOrCreate 方式一样；(我们 打印下 原生 sql 语句)

DB::connection()->enableQueryLog();
Role::query()->upsert(['name' => 'Founder_12', 'guard_name' => 'web_12','id'=>15], 'id');

dd(DB::getQueryLog());//sql-1


DB::connection()->enableQueryLog();
Role::query()->updateOrCreate(['name'=>'Founder_5','guard_name'=>'web_1'], ['name'=>'Founder_1','guard_name'=>'web_1']);

dd(DB::getQueryLog());//sql-2

sql-1

sql-1
array:1 [▼
  0 => array:3 [▼
    "query" => "insert into `roles` (`created_at`, `guard_name`, `id`, `name`, `updated_at`) values (?, ?, ?, ?, ?) on duplicate key update `name` = values(`name`), `guard_name` = values(`guard_name`), `id` = values(`id`), `updated_at` = values(`updated_at`) ◀"
    "bindings" => array:5 [▼
      0 => "2021-07-19 05:31:20"
      1 => "web_12"
      2 => 15
      3 => "Founder_12"
      4 => "2021-07-19 05:31:20"
    ]
    "time" => 5.54
  ]
]

sql-2

array:2 [▼
  0 => array:3 [▼
    "query" => "select * from `roles` where (`name` = ? and `guard_name` = ?) limit 1"
    "bindings" => array:2 [▼
      0 => "Founder_5"
      1 => "web_1"
    ]
    "time" => 11.2
  ]
  1 => array:3 [▼
    "query" => "insert into `roles` (`name`, `guard_name`, `updated_at`, `created_at`) values (?, ?, ?, ?)"
    "bindings" => array:4 [▼
      0 => "Founder_1"
      1 => "web_1"
      2 => "2021-07-19 06:41:41"
      3 => "2021-07-19 06:41:41"
    ]
    "time" => 2.19
  ]
]

---

这里我们可以看到  updateOrCreate 生成了两条sql 语句，upsert 生成了 一条语句
当我们数据量大的时候，性能差距还是蛮明显的