目录

lumen提供了三种数据库使用方式：Raw SQL、Query Builder、Eloquent ORM。

1、Raw SQL

Database: Getting Started - Laravel - The PHP Framework For Web Artisans

注：面向DB的编码

好处：

  • 简单
  • 适用于写原生sql
  • 支持select、selectOne、insert、update、delete、scalar（大多基于statement、affectingStatement）
  • 支持绑定占位符,比如:id、?
  • lumen的事务，简单明确，必须使用闭包DB::transaction(function(){}) 或者 手动DB::beginTransaction()、DB::rollBack()、DB::commit()
  • 容易跟踪sql语句

常用实践：

return DB::connection("dbMaster")->select("select * from test_account limit 2");
return DB::connection("dbMaster")->select("select * from test_account where id=:id", ["id"=>320]);
return DB::connection("dbMaster")->select("select * from test_account where id=?", [320]);

其他方法类推（含事务）

2、Query Builder

Database: Query Builder - Laravel - The PHP Framework For Web Artisans

查询构造器，是很多框架都会提供的常用方式。相比较于Raw SQL的方式，封装了更多方法用于构造查询，比如where子句、join、union、order、groupBy/having、limit等

注：面向table的编码

常用最佳实践：

private function getDbConn($dbKey = ''){
    if(empty($dbKey)){
        $dbKey = "dbMaster";
    }
    return  DB::connection($dbKey);
}    
return $dbConn->table("test_account")->whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->orderBy("id", "asc")->get(); //[{"id":315,"account_id":191551561},{"id":320,"account_id":191551615}]
return $dbConn->table("test_account")->whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->first(); //[{"id":315,"account_id":191551561}]
return $dbConn->table("test_account")->whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->first();//{"id":315,"account_id":191551561}

return $dbConn->table("test_account")->insert([["account_id"=>10000],["account_id"=>10001]]);//支持一维数组，二维数组
return $dbConn->table("test_account")->insertGetId([["account_id"=>10000],["account_id"=>10001]]);//如果需要返回自增id

//chunk 方法，每次插入一个数组，提高性能。有时我们会一次性查出大量数据，但这样很占用内存，所以我们可以使用chunk方法。chunk是拿时间换空间，当然性价比高，再当然，如果可以的话，我们建议自己实现通过limit、offset与每次查询出的最大值id作为下次查询的范围条件，性价比更高。
$dbConn->table('test_account')->orderBy('id')->chunk(100, function ($users) {
foreach ($users as $user) {
        //
    }
});

//upsert 新增如果已经存在则更新； 新增（第一个参数为数组，为将新增的数据记录），如果存在departure与destination栏位相同的记录（相当于where条件，第二个参数-数组），则更新price（第三个参数）
DB::table('flights')->upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);    

//更新，除了update字句外，其他的参考where查询构造器
$affected = DB::table('users')
            ->where('id', 1)
            ->update(['votes' => 1]);
//updateOrInsert 更新如果不存在则新增； 第一个参数是更新条件，第二参数是更新数据；如果不存则新增时，会合并两个参数并新增
DB::table('users')
->updateOrInsert(
    ['email' => 'john@example.com', 'name' => 'John'],
    ['votes' => '2']
);

//delete 构造查询
$deleted = DB::table('users')->where('votes', '>', 100)->delete();

//支持悲观锁
DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();

注：以上两种方式Raw SQL 和 Query Builder，其实跟有没有model是没有关系，只要是数据库操作，都可以使用查询构造器。而且IDE不需要额外的安装插件就能做到自动补全。

不建议：

  • 不建议使用随机排序
  • 不建议使用latest这种高度依赖框架约定的date栏位

3、 Eloquent ORM

注：面向model编码

Eloquent: Getting Started - Laravel - The PHP Framework For Web Artisans - retrieving-models

  • ORM

    ORM：Object Relational Mapping 对象映射关系，一般是将DB表（结构）映射到对象（类）

    既然映射到代码层面的对象类，那代码层面就应该通过class、property、method来描述，而不是直接使用DB表的结构。

    所以Eloquent ORM的model也是相当于为DB表描述了一个对象类，比如指定connection、table、primaryKey、timestamps（默认使用created_at、updated_at）、dataFormat等等。

    You can think of each Eloquent model as a powerful query builder allowing you to fluently query the database table associated with the model.

    从下面的一段代码，我们可以看出可以将model类作为一个query builder，可以通过where条件等等来查询数据库表，而不是直接使用DB表的结构。

    return TestAccount::whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->orderBy("id", "asc")->get();

    注意：model是代码层面的数据，model数据的改变，并不会直接自动更新到db，所以那些问我们这个问题的人不用太对model数据的改变太过于纠结。

  • 常用实践

    编码自然是一直在IDE中，而不需要频繁切出查看文档，所以IDE的自动补全是很重要的一部分，而大部分php框架都会运用到php的魔术方法，而IDE的自动补全就是为了让大家能够快速的查找到这些魔术方法。

    如何在lumen项目中自动补全model？

    Laravel在IDE的使用中无法自动补全Eloquent链式查询的解决办法_Coder_Russell的博客-CSDN博客

    按照以上artisan的命令，可以自动生成model的一些注释，这些注释就可以让我们在IDE中畅游了，比如一个model叫TestFamily（mysql），ide-helper:models 生成注释是这样的:

    /**
 * Class TestFamily.
 *
 * @package namespace App\Models\Test\Master;
 * @property int $id
 * @property string $name 家庭组织名称
 * @property int $account_id 家庭组织所有者（家长账号id）
 * @property \Illuminate\Support\Carbon $updated_at 更新时间
 * @property \Illuminate\Support\Carbon $created_at 创建时间
 * @property \Illuminate\Support\Carbon|null $deleted_at 软删除时间
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily newModelQuery()
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily newQuery()
 * @method static \Illuminate\Database\Query\Builder|TestFamily onlyTrashed()
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily query()
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily whereAccountId($value)
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily whereCreatedAt($value)
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily whereDeletedAt($value)
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily whereId($value)
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily whereName($value)
 * @method static \Illuminate\Database\Eloquent\Builder|TestFamily whereUpdatedAt($value)
 * @method static \Illuminate\Database\Query\Builder|TestFamily withTrashed()
 * @method static \Illuminate\Database\Query\Builder|TestFamily withoutTrashed()
 * @mixin \Eloquent
 */

    标签@property，声明了属性，而一般model的基类都会有魔术方法__set和__get，这个声明就使得我们可以通过 $model->name 来访问属性name，而不是通过 $model->attributes['name']。

    标签@method，声明了个方法，告诉了我们这个类有哪些魔术方法可以调用。比如query()这个方法，就是我们在model类中定义的一个魔术方法，通过这个调用这个魔术方法，我们可以得到一个Builder，从而可以通过Quer Builder来构建查询。

    注：忽略newQuery、newModelQuery，在更新的ide-helper是不会再自动生成这些方法的，query本身是继承的，newQuery和newModelQuery在laravel6+ 与 php7的版本下通过static方式调用已经存在的非static的方法，是会报错的，目前未知原因。直接php7下模拟类似方式实现是可以正常通过static调用已经存在的非static方法。所以newQuery与newModelQuery可以去掉

    虽说model是超级query builder，具备query builder的链式方法，虽然能用，但IDE上仍然无法自动完成，需用通过以上标签@method的声明一些可返回的Query Builder的魔术方法，否则会大大提高编码人员的心智，降低开发效率

    //这个写法是正确的（框架会自动实例化model并使用Builder），但IDE中不会自动补全，所以这个很让编码头疼
return TestAccount::whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->orderBy("id", "asc")->get();
  
//先调用query是为了自动补全（返回Builder）
return TestFamily::query()->whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->orderBy("id", "asc")->get();

//其实上面调用静态方法query的效果就是：
return (new TestFamily())->newQuery()->whereIn("id",[320,319,315])->where("vip","=",1)->select(["id","account_id"])->orderBy("id", "asc")->get();

//这个Eloquent model提供的自动转换为对应property的查询魔术方法，比如这个方法会自动转换成 `where("account_id","=",191547725)` 查询
return TestFamily::whereAccountId(181546046)->get();

    model还提供了很多高级用法：firstOr、firstOrFail、firstOrCreate、聚合函数、save、updateOrCreate、upsert、destroy等，对于orm的model有两种调用方式，一种是静态调用，一种是实例化对象的调用。

    //通过静态方法调用create创建一个model并插入db，但实际上这个create是Builder的方法，而不是model的方法，只是model的__callStatic魔术方法在自己类中找不到方法时，会去调用Builder的方法，比如find、create等方法
$model = TestFamily::create([
    "account_id"=>123
]);


//更新model并保存到db
$model = new TestFamily();
$model->account_id = 123;
$model->save(); //实例化对象调用save既可以用于insert也可以用于update

//静态调用
Flight::destroy(1);

//实例化对象
$flight = Flight::find(1);
$flight->delete();

    更多方法详见文档Eloquent: Getting Started - Laravel - The PHP Framework For Web Artisans - retrieving-models

    小结：model的使用，很容易使得model出于贫血状态，而model具体查询或更新等方法会散落在repo或service层，model层不再集中管理，所以追溯跟踪的时候，不容易找到触发查询或更新统一的入口，只能找到service层的调用地方。但同时减少了model层的编码，提高了编码效率，编码人员更多的在service、controller层进行编码。

善用与慎用chunk和cursors处理大量数据model

chunk最大的目标是节省空间（拿查询时间换内存空间），cursors目标也是节省空间，但cursors仍然保留和get一样只进行一次连接查询，所以相对来说chunk最节省空间，cursors和get最节省时间，cursors在节省空间上仅次于chunk。

  • chunk

    可以用于更有效地处理大数据量的model。

    $res = [];
$model = new TestDeviceLocationHistoryLog();
$model->newQuery()->orderBy("id",'desc')->chunk(100,function($list) use(&$res){
    foreach ($list as $one){
        $res[] = $one->id;
    }
});
return $res;

    从以下sql执行来看，chunk是每取100条数据执行一次sql查询：

    sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 2900  | time: 29.5ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 3000  | time: 11.85ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 3100  | time: 42.83ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 3200  | time: 29.81ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 3300  | time: 32.94ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 3400  | time: 48.15ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 100 offset 3500  | time: 119.48ms
.
.
.

    前面我们也提到了chunk还有提升的空间，也就是chunkById()，具体就是利用mysql的B+树索引快速缩小查询范围，当然虽然叫chunkById，但也可以在第三个参数指定使用其他索引（未指定将用默认主键，比如id）：

    $res = [];
$model = new TestDeviceLocationHistoryLog();
$model->setTable("test_device_location_history_log");

$model->newQuery()->orderBy("id",'desc')->chunkById(100,function($list) use(&$res){
    foreach ($list as $one){
        $res[] = $one->id;
    }
},"id");
return $res;

    很明显后续的每次查询都比chunk方法的查询更快：

    sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` asc limit 100  | time: 191.94ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` where `id` > 100 order by `id` asc limit 100  | time: 49.02ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` where `id` > 200 order by `id` asc limit 100  | time: 2.23ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` where `id` > 300 order by `id` asc limit 100  | time: 12.59ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` where `id` > 400 order by `id` asc limit 100  | time: 14.43ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` where `id` > 500 order by `id` asc limit 100  | time: 12.72ms
sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` where `id` > 600 order by `id` asc limit 100  | time: 12.06ms
.
.
.

  • cursor

    cursor通过迭代生成器实现的，与lazy方法类似，游标方法可用于在迭代数万条 Eloquent 模型记录时显着减少应用程序的内存消耗：

    $res = [];
$model = new TestDeviceLocationHistoryLog();
foreach(($model->newQuery()->orderBy("id","desc")->limit(58)->cursor()) as $oneLocation){
    //return $oneLocation;
    $res[] = $oneLocation->id;
}
return $res;

    cursor只执行了一次sql查询，但通过php的迭代器实现了每次只从底层套接字中取出一条数据并处理，所以其实虽然是58条的数据，实际上在php业务服务器一直都只占用1条数据的内存：

    sql | "test\/test\/testindex" | query: select * from `test_device_location_history_log` order by `id` desc limit 58  | time: 226.38ms

  • 建议

    在读取大量记录并遍历的使用场景中：依据 数据量 大小可以选择不同的方式:

    • 超过10万级别的数据：chunk、offset最稳健；
    • 小于10万级别的数据：cursor效率最高；

    注：这里10万级别数据差不多是300MB左右的数据，根据项目实际情况具体分析。

    建议来源：Laravel Get、Cursor、Chunk、Offset的比较

建议

建议简单明了所见即所得，了解每条sql的执行逻辑和原理，由于Eloquent会隐式实现很多细节，容易发生阴差阳错、误打误撞的情况，也为了方便后续阅读与追溯，建议尽量使用原生sql，使用Raw SQL的原生sql的方式，要注意参数预编译避免注入。

凡是两面性，以上部分调用方式，并非完全拒绝，再需要制造大量轮子来实现的时候，可以考虑使用，比如chunkById、cursor，大家都知道原理，就可以自己封装，也可以直接使用。

参考

Database - Lumen - PHP Micro-Framework By Laravel