Laravel 13 数据库优化完全指南

数据库优化是提升应用性能的关键环节。本文将深入探讨 Laravel 13 中数据库优化的各种技巧和最佳实践。

查询优化

选择必要字段

1
2
3
4
5
6
7
8
// 不推荐
$users = User::all();

// 推荐
$users = User::select(['id', 'name', 'email'])->get();

// 动态选择
$users = User::select($request->get('fields', ['id', 'name']))->get();

避免 N+1 问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// N+1 问题
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name; // 每次循环都查询
}

// 预加载解决
$posts = Post::with('user')->get();
foreach ($posts as $post) {
    echo $post->user->name; // 不额外查询
}

// 嵌套预加载
$posts = Post::with('user.profile', 'comments.user')->get();

// 条件预加载
$posts = Post::with(['comments' => function ($query) {
    $query->where('approved', true);
}])->get();

// 延迟预加载
$posts = Post::all();
$posts->load('user', 'comments');

智能预加载

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $with = ['user'];

    protected $withCount = ['comments'];

    public function scopeWithMinimal($query)
    {
        return $query->select(['id', 'title', 'user_id'])
            ->with('user:id,name');
    }

    public function scopeWithFull($query)
    {
        return $query->with(['user.profile', 'comments.user', 'tags'])
            ->withCount(['comments', 'likes']);
    }
}

索引优化

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
// 迁移文件
Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email')->unique();
    $table->string('name');
    $table->string('status');
    $table->timestamp('created_at');

    // 单列索引
    $table->index('status');
    
    // 复合索引
    $table->index(['status', 'created_at']);
    
    // 全文索引
    $table->fullText('description');
});

// 后期添加索引
Schema::table('users', function (Blueprint $table) {
    $table->index(['status', 'created_at'], 'users_status_created_at_index');
});

索引使用分析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
use Illuminate\Support\Facades\DB;

// 分析查询
$users = DB::table('users')
    ->select(['id', 'name', 'email'])
    ->where('status', 'active')
    ->where('created_at', '>', now()->subMonth())
    ->get();

// 获取执行计划
$plan = DB::table('users')
    ->where('status', 'active')
    ->explain()
    ->get();

// 分析慢查询
DB::listen(function ($query) {
    if ($query->time > 100) {
        Log::channel('slow-query')->info('Slow query', [
            'sql' => $query->sql,
            'bindings' => $query->bindings,
            'time' => $query->time,
        ]);
    }
});

分页优化

基础分页

1
2
3
4
5
6
7
8
// 简单分页（无总数）
$users = User::simplePaginate(15);

// 标准分页
$users = User::paginate(15);

// 游标分页（大数据量推荐）
$users = User::orderBy('id')->cursorPaginate(15);

高效分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
<?php

namespace App\Services;

use Illuminate\Contracts\Pagination\LengthAwarePaginator;

class OptimizedPagination
{
    public function paginate($query, int $perPage = 15, int $page = 1): LengthAwarePaginator
    {
        $total = $this->getEstimatedCount($query);
        
        $items = $query->clone()
            ->forPage($page, $perPage)
            ->get();

        return new \Illuminate\Pagination\LengthAwarePaginator(
            $items,
            $total,
            $perPage,
            $page
        );
    }

    protected function getEstimatedCount($query): int
    {
        $sql = $query->toSql();
        $bindings = $query->getBindings();

        $countSql = "EXPLAIN SELECT COUNT(*) FROM ({$sql}) as subquery";
        $result = DB::select($countSql, $bindings);

        return $result[0]->rows ?? 0;
    }
}

无限滚动分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// API 响应
public function index(Request $request)
{
    $users = User::orderBy('id')
        ->cursorPaginate($request->get('per_page', 15));

    return response()->json([
        'data' => UserResource::collection($users),
        'meta' => [
            'has_more' => $users->hasMorePages(),
            'next_cursor' => $users->nextCursor()?->encode(),
        ],
    ]);
}

// 请求下一页
$users = User::orderBy('id')
    ->cursorPaginate(15, ['*'], 'cursor', $request->cursor);

批量操作

批量插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 单条插入（慢）
foreach ($users as $userData) {
    User::create($userData);
}

// 批量插入（快）
User::insert($users);

// 批量插入并获取 ID
$users = User::insertAndReturnIds($users);

// 分块批量插入
collect($users)->chunk(1000)->each(function ($chunk) {
    User::insert($chunk->toArray());
});

批量更新

1
2
3
4
5
6
7
8
9
10
11
12
13
// 使用 upsert
User::upsert([
    ['id' => 1, 'name' => 'John', 'email' => 'john@example.com'],
    ['id' => 2, 'name' => 'Jane', 'email' => 'jane@example.com'],
], ['id'], ['name', 'email']);

// 条件批量更新
User::where('status', 'pending')
    ->update(['status' => 'processed']);

// 批量更新关联
DB::table('users')
    ->upsert($data, ['email'], ['name', 'updated_at']);

批量删除

1
2
3
4
5
6
7
8
9
10
11
12
// 批量删除
User::whereIn('id', $ids)->delete();

// 软删除批量恢复
User::withTrashed()
    ->whereIn('id', $ids)
    ->restore();

// 分块删除（大数据量）
User::chunk(1000, function ($users) {
    $users->each->delete();
});

查询构建优化

子查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
// 使用子查询
$users = User::addSelect([
    'last_post_title' => Post::select('title')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1),
])->get();

// 使用 join 替代子查询
$users = User::join('posts', function ($join) {
    $join->on('users.id', '=', 'posts.user_id')
        ->where('posts.is_latest', true);
})->select('users.*', 'posts.title as last_post_title')->get();

条件查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
// 使用 when 简化条件
$users = User::query()
    ->when($request->search, function ($query, $search) {
        $query->where('name', 'like', "%{$search}%");
    })
    ->when($request->status, function ($query, $status) {
        $query->where('status', $status);
    })
    ->when($request->role, function ($query, $role) {
        $query->whereHas('roles', fn($q) => $q->where('name', $role));
    })
    ->get();

// 使用 whereHas 优化
$users = User::whereHas('posts', function ($query) {
    $query->where('published', true);
}, '>=', 5)->get();

// 使用 whereExists 替代 whereHas
$users = User::whereExists(function ($query) {
    $query->select(DB::raw(1))
        ->from('posts')
        ->whereColumn('posts.user_id', 'users.id')
        ->where('posts.published', true);
})->get();

聚合查询优化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// 使用 withCount
$users = User::withCount(['posts', 'comments'])->get();

// 条件聚合
$users = User::withCount([
    'posts as published_posts_count' => fn($q) => $q->where('published', true),
    'comments as approved_comments_count' => fn($q) => $q->where('approved', true),
])->get();

// 使用聚合缓存
$stats = Cache::remember('user_stats', 3600, function () {
    return [
        'total' => User::count(),
        'active' => User::where('active', true)->count(),
        'new_this_month' => User::whereMonth('created_at', now()->month)->count(),
    ];
});

连接池与读写分离

配置读写分离

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// config/database.php
'mysql' => [
    'read' => [
        'host' => ['read1.example.com', 'read2.example.com'],
    ],
    'write' => [
        'host' => 'write.example.com',
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

强制使用主库

1
2
3
4
5
// 强制从主库读取
$user = User::writeConnection()->find($id);

// 或使用 onWriteConnection
$user = User::onWriteConnection()->find($id);

数据库连接优化

连接池配置

1
2
3
4
5
6
7
8
9
// config/database.php
'mysql' => [
    'driver' => 'mysql',
    'options' => [
        PDO::ATTR_PERSISTENT => true,
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_STRINGIFY_FETCHES => false,
    ],
],

连接复用

1
2
3
4
5
// 多数据库操作时复用连接
DB::connection('mysql')->transaction(function () {
    DB::connection('mysql')->table('users')->insert([...]);
    DB::connection('mysql')->table('profiles')->insert([...]);
});

慢查询分析

查询日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// 启用查询日志
DB::enableQueryLog();

// 执行查询
$users = User::with('posts')->get();

// 获取日志
$queries = DB::getQueryLog();

// 分析日志
foreach ($queries as $query) {
    if ($query['time'] > 100) {
        Log::warning('Slow query detected', $query);
    }
}

查询分析器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
<?php

namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Collection;

class QueryAnalyzer
{
    protected Collection $queries;

    public function __construct()
    {
        $this->queries = collect();
    }

    public function start(): void
    {
        DB::listen(function ($query) {
            $this->queries->push([
                'sql' => $query->sql,
                'bindings' => $query->bindings,
                'time' => $query->time,
                'backtrace' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 10),
            ]);
        });
    }

    public function getReport(): array
    {
        return [
            'total_queries' => $this->queries->count(),
            'total_time' => $this->queries->sum('time'),
            'slow_queries' => $this->queries->where('time', '>', 100)->values(),
            'duplicate_queries' => $this->findDuplicates(),
            'n_plus_one' => $this->detectNPlusOne(),
        ];
    }

    protected function findDuplicates(): Collection
    {
        return $this->queries->groupBy('sql')
            ->filter(fn($group) => $group->count() > 1)
            ->map(fn($group) => [
                'sql' => $group->first()['sql'],
                'count' => $group->count(),
                'total_time' => $group->sum('time'),
            ]);
    }

    protected function detectNPlusOne(): Collection
    {
        return $this->queries->groupBy('sql')
            ->filter(fn($group) => $group->count() > 5)
            ->keys();
    }
}

总结

Laravel 13 的数据库优化包括：

  • 查询字段优化和预加载
  • 索引设计与分析
  • 高效分页策略
  • 批量操作优化
  • 读写分离配置
  • 慢查询监控分析

持续优化数据库是保证应用高性能的关键。