在 Laravel 6.0 處理資料庫大量資料查詢

Laravel
, , , ,

建立專案

建立專案。

1
laravel new eloquent-chunking

新增資料庫

為求方便，使用 SQLite 資料庫。

安裝套件

由於需要查看記憶體使用量、執行時間以及資料庫查詢，需要安裝 Telescope 套件。

新增模型

新增 Book 模型，使用 -a 參數同時新增遷移檔、工廠和控制器。

1
php artisan make:model Book -a

在遷移檔新增欄位。

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
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateBooksTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('books', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('title');
            $table->string('author');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('books');
    }
}

資料填充

修改 database/factories/BookFactory.php 檔，使工廠產生不重複的資料。

1
2
3
4
5
6
7
8
9
use App\Book;
use Faker\Generator as Faker;

$factory->define(Book::class, function (Faker $faker) {
    return [
        'title' => sprintf('%s (%u)', $faker->sentence(), rand()),
        'author' => sprintf('%s (%u)', $faker->name(), rand()),
    ];
});

新增 BooksTableSeeder 資料填充。

1
php artisan make:seed BooksTableSeeder

分批插入共 10 萬筆資料。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
use App\Book;
use Illuminate\Database\Seeder;

class BooksTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        for ($i = 1; $i <= 100; $i++) {
            $books = factory(Book::class)->times(1000)->make()->toArray();

            Book::insert($books);
        }
    }
}

在 database/seeds/DatabaseSeeder.php 檔註冊資料填充。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        $this->call(BooksTableSeeder::class);
    }
}

執行資料填充。

1
php artisan migrate --seed

新增路由

在 routes/api.php 檔新增路由。

1
Route::get('books', 'BookController@index');

情境

從 10 萬筆資料中找到一筆已存在的資料：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$max = 100000;

// 將最後一筆資料當作要尋找的資料
$existingBook = Book::find($max);

// 查詢已存在的資料
$books = Book::where('id', '<=', $max)->get();

// 檢驗是否已存在
$isExisting = $books->some(function ($book) use (&$existingBook) {
    return $book->title === $existingBook->title;
});

dump($isExisting);

結果：

1
true

實測 5 次的效能分別如下：

Duration Memory usage queries
1037 ms 119.9 MB 2
1040 ms 117.9 MB 2
1092 ms 113.9 MB 2
1151 ms 111.9 MB 2
1084 ms 103.9 MB 2

優化

分塊

在處理大型結果集時，為了節省記憶體使用量，可以使用 chunk() 方法來查詢 Eloquent 模型中的「分塊」，將它們提供給指定的閉包處理。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$max = 100000;

// 將最後一筆資料當作要尋找的資料
$existingBook = Book::find($max);

$isExisting = false;

// 分 10 次查詢已存在的資料
Book::where('id', '<=', $max)->chunk($max / 10, function ($books) use (&$existingBook, &$isExisting) {
    // 檢驗是否已存在
    $isExisting = $books->some(function ($book) use (&$existingBook) {
        return $book->title === $existingBook->title;
    });

    // 如果存在就停止查詢
    return ! $isExisting;
});

dump($isExisting);

結果：

1
true

實測 5 次的效能分別如下：

Duration Memory usage queries
1053 ms 18 MB 11
1112 ms 18 MB 11
1145 ms 18 MB 11
1114 ms 18 MB 11
1120 ms 18 MB 11

使用 chunkById() 方法可以更快速地查詢資料庫，但僅限用於主鍵為 AUTO_INCREMENT 的資料表，並且在有 join 的情況下可能不能使用。

游標

使用 cursor() 方法可以藉由游標遍歷資料庫，並且只會執行一次的查詢。處理大量的數據時，可以減少記憶體使用量。

1
2
3
4
5
6
7
8
9
$max = 100000;

$existingBook = Book::find($max);

$isExisting = Book::where('id', '<=', $max)->cursor()->some(function ($book) use (&$existingBook) {
    return $book->title === $existingBook->title;
});

dump($isExisting);

結果：

1
true

實測 5 次的效能分別如下：

Duration Memory usage queries
2168 ms 4 MB 2
2166 ms 4 MB 2
2214 ms 4 MB 2
2178 ms 4 MB 2
2165 ms 4 MB 2

其他

如果只是要避免重複插入資料而產生錯誤，可以考慮使用 insertOrIgnore() 方法，但是這個方法只會檢查主鍵而已。

1
2
3
4
5
6
7
8
9
10
11
$max = 100000;

Book::insertOrIgnore([
    [
        "id" => $max,
        "title" => "New Book",
        "author" => "Memo Chou",
    ],
]);

dump(Book::count());

結果：

1
100000

程式碼