思考：如何提高Model层查询DB的效率？如何精简代码？
- 经过一番调研之后发现了一个堪称神器的扩展：laravel-upsert
- 这个 Laravel 扩展为查询构建器和 Eloquent 添加了对 INSERT & UPDATE (UPSERT) 和 INSERT IGNORE 的支持
先简单说明一下业务场景：
- 首先表结构设计是：互相喜欢和添加联系人都是双向关系，即入库A B,B A这样成对的双向数据
- 触发互相喜欢，插入2条双向数据，插入之前校验是否存在，存在不重复添加
- 如果互相喜欢，则添加双向联系人关系，插入之前校验是否存在，存在则更新type等字段，不存在则插入双向数据
我们通过这个场景能非常好的体会laravel-upsert的强大，不仅减少了代码量，也减少了sql 查询次数，提升了性能。
优化代码前
//校验是否存储 public static function checkExist($userid, $otherUserid) { return self::query() ->where('userid', $userid) ->where('otherUserid', $otherUserid) ->exists(); } //添加双向好友关系 public static function addBoth($userid, $otherUserid) { if (!self::checkExist($userid, $otherUserid)) { UserRelationBoth::insert([ [ 'userid' => $userid, 'otherUserid' => $otherUserid, 'createtime' => time(), 'updatetime' => time(), ], [ 'userid' => $otherUserid, 'otherUserid' => $userid, 'createtime' => time(), 'updatetime' => time(), ] ]); //互相喜欢，添加好友关系 AppointmentContacts::saveContacts($userid, $otherUserid, AppointmentContacts::TYPE_RELATION_LIKE_EACHOTHER); } } //保存双向联系人 public static function saveContacts($userid, $otherUserid, $type, $appointmentPrepareId = 0) { if (!self::checkExist($userid, $otherUserid)) { AppointmentContacts::insert([ [ 'userid' => $userid, 'otherUserid' => $otherUserid, 'appointmentPrepareId' => 0, 'type' => $type, 'createtime' => time(), 'updatetime' => time(), ], [ 'userid' => $otherUserid, 'otherUserid' => $userid, 'appointmentPrepareId' => 0, 'type' => $type, 'createtime' => time(), 'updatetime' => time(), ] ]); } else { //存在更新状态 self::updateContactsType($userid, $otherUserid, $type, $appointmentPrepareId); } } //更新最新关系类型 public static function updateContactsType($userid, $otherUserid, $type, $appointmentPrepareId = 0) { self::query() ->where('userid', $userid) ->where('otherUserid', $otherUserid) ->update([ 'type' => $type, 'appointmentPrepareId' => $appointmentPrepareId, 'updatetime' => time(), ]); self::query() ->where('userid', $otherUserid) ->where('otherUserid', $userid) ->update([ 'type' => $type, 'appointmentPrepareId' => $appointmentPrepareId, 'updatetime' => time(), ]); }
代码优化后
public static function addBoth($userid, $otherUserid) { self::query()->insertIgnore([ ["userid" => $userid, "otherUserid" => $otherUserid], ["userid" => $otherUserid, "otherUserid" => $userid] ]); //互相喜欢，添加好友关系 AppointmentContacts::saveContacts($userid, $otherUserid, AppointmentContacts::TYPE_RELATION_LIKE_EACHOTHER); } //保存双向联系人 public static function saveContacts($userid, $otherUserid, $type, $appointmentPrepareId = 0) { //没有添加 有则更新 self::upsert([ ["userid" => $userid, "otherUserid" => $otherUserid, 'type' => $type, "appointmentPrepareId" => $appointmentPrepareId], ["userid" => $otherUserid, "otherUserid" => $userid, 'type' => $type, "appointmentPrepareId" => $appointmentPrepareId] ], ['userid', 'otherUserid'], ['type' => $type, "appointmentPrepareId" => $appointmentPrepareId] ); }
优化效果
代码量：优化前82行代码，优化后22行代码，代码行数少了3倍+
查询sql的条数：优化前5条sql，优化后2条sql
laravel-upsert 扩展的特性
安装
composer require staudenmeir/laravel-upsert:"^1.0"
用法
插入和更新 (UPSERT)
考虑这个users具有唯一username列的表：
Schema :: create ( 'users' , function ( Blueprint $ table ) { $ table -> increments ( 'id' ); $ table -> string ( 'username' )-> unique (); $ table -> boolean ( ' active' ); $ table ->时间戳(); });
使用upsert()插入一个新的用户或更新现有的一个。在此示例中，将重新激活非活动用户并updated_at更新时间戳：
DB :: table ( 'users' )-> upsert ( [ 'username' => 'foo' , 'active' => true , 'created_at' => now (), 'updated_at' => now ()], 'username' , [ '活动'，'updated_at' ] );
提供要作为第一个参数插入的值。这可以是单个记录或多个记录。
第二个参数是唯一标识记录的列。除 SQL Server 外的所有数据库都要求这些列具有PRIMARY或UNIQUE索引。
提供要更新的列作为第三个参数（可选）。默认情况下，将更新所有列。您可以提供带有文字或原始表达式的列名和键值对（见下文）。
作为使用复合键和原始表达式的示例，请考虑以下表，该表计算每个帖子和每天的访问者：
Schema :: create ( 'stats' , function ( Blueprint $ table ) { $ table -> unsignedInteger ( 'post_id' ); $ table -> date ( 'date' ); $ table -> unsigned Integer ( 'views' ); $表->主要（[ 'post_id'，'date' ]）； });
使用upsert()登录访问。该查询将为每个帖子和日期创建一个新记录或增加现有的查看计数器：
DB :: table ( 'stats' )-> upsert ( [ [ 'post_id' => 1 , 'date' => now ()-> toDateString (), 'views' => 1 ], [ 'post_id' => 2 , 'date' => now ()-> toDateString (), 'views' => 1 ], ], [ 'post_id'，'日期' ]， [ 'views' => DB :: raw ( 'stats.views + 1' )] );
插入忽略
您还可以在忽略重复键错误的同时插入记录：
Schema :: create ( 'users' , function ( Blueprint $ table ) { $ table -> increments ( 'id' ); $ table -> string ( 'username' )-> unique (); $ table -> timestamps () ; }); DB :: table ( 'users' )-> insertIgnore ([ [ 'username' => 'foo' , 'created_at' => now (), 'updated_at' => now ()], [ 'username' => 'bar' , 'created_at' => now (), 'updated_at' => now ()], ]);
SQL Server 需要带有唯一标识记录的列的第二个参数：
DB :: table ( 'users' )-> insertIgnore ( [ 'username' => 'foo' , 'created_at' => now (), 'updated_at' => now ()], 'username' );
Eloquent
你可以在 Eloquent 模型中使用 UPSERT 和 INSERT IGNORE 查询。
在 Laravel 5.5-5.7 中，这需要HasUpsertQueriestrait：
class User extends Model { use \Staudenmeir\LaravelUpsert\Eloquent\HasUpsertQueries; } User::upsert(['username' => 'foo', 'active' => true], 'username', ['active']); User::insertIgnore(['username' => 'foo']);
如果模型使用时间戳，upsert()并且insertIgnore()会自动为插入的值添加时间戳。upsert()还将添加updated_at到更新的列中。
Lumen
如果您使用 Lumen，则必须手动实例化查询构建器：
$builder = new \Staudenmeir\LaravelUpsert\Query\Builder(app('db')->connection()); $builder->from(...)->upsert(...);
在 Eloquent 中，所有版本的 LumenHasUpsertQueries都需要该特性。
注意的问题
- 要根据需求添加唯一索引
- 根据官方文档中的说明，我们的model中必须添加这行代码，才能以Eloquent的方式用
use \Staudenmeir\LaravelUpsert\Eloquent\HasUpsertQueries;
- 因为我们数据库的时间是int类型，不是laravel默认的时间格式，并且我们的插入时间和更新时间也不是laravel默认的字段，我们需要做如下定义：
//时间戳类型 public $timestamps = true; //重写插入和修改时间的字段名 const CREATED_AT = 'createtime'; const UPDATED_AT = 'updatetime'; //设置日期格式为时间戳 protected $dateFormat = 'U'; //如果取值有使用toArray()转成数组的话还需要下方的配置 //获得创建时间 protected function getCreatetimeAttribute($value) { return intval($value); } //获得修改时间 protected function getUpdatetimeAttribute($value) { return intval($value); }
总结
- 代码优化是一个不断优化的过程，好久没有更新文章啦，最近会更新一波代码简洁之道和性能优化的文章，包括代码方面的抽象设计、结构方面的、优秀的第三方扩展等。
