วิธีให้งาน sync ใน Laravel เขียนเฉพาะแถวที่ข้อมูลเปลี่ยน

ระบบที่ดึงข้อมูลจาก ERP หรือ API ของระบบอื่นมาเก็บไว้ในฐานข้อมูลของเรา ปกติเรามักเขียนเป็น job ใน scheduler ที่รันทุกนาที ดึงมาทั้งชุดแล้ว upsert() ลงตาราง ไม่ต้องคิดว่าแถวไหนเปลี่ยน แถวไหนไม่เปลี่ยน ฐานข้อมูลก็ไม่ได้โตขึ้น ดูเหมือนไม่มีอะไรเสียหาย

แต่ upsert() ของ Eloquent เขียนทุกแถวที่ส่งไปใหม่ทุกรอบ ถึงค่าจะเหมือนเดิมทุกช่องก็ตาม เพราะ updated_at ได้เวลาใหม่ทุกครั้ง

Eloquent upsert() เติม updated_at ให้แถวเดิมทุกครั้ง#

Model::upsert() เติม created_at และ updated_at ให้ทุกแถว แล้วเพิ่ม updated_at ลงในรายการคอลัมน์ที่อัปเดตเมื่อชนแถวเดิม (addUpdatedAtToUpsertColumns() ใน Illuminate\Database\Eloquent\Builder) บน MySQL ได้ SQL หน้าตาแบบนี้

insert into `order_lines` (`bin`, `created_at`, `line_key`, `qty`, `updated_at`)
values (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), …
on duplicate key update
    `qty` = values(`qty`),
    `bin` = values(`bin`),
    `updated_at` = values(`updated_at`)

MySQL ฉลาดพอจะไม่เขียนแถวที่ค่าใหม่เท่ากับค่าเดิมทุกคอลัมน์ (คู่มือ บอกว่าถ้าตั้งค่าคอลัมน์เป็นค่าที่มีอยู่แล้ว MySQL จะไม่อัปเดต) แต่ values(updated_at) คือเวลาของรอบนี้ ซึ่งไม่เคยเท่ากับของรอบก่อน ทุกแถวที่ส่งไปจึงต่างจากเดิมอย่างน้อยหนึ่งคอลัมน์เสมอ

ลองกับตาราง 1,000 แถวบน MySQL 8.4 ส่งข้อมูลชุดเดิมซ้ำด้วยวิธีเดียวกับ Eloquent ได้ 2,000 rows affected (MySQL นับแถวที่อัปเดตเป็น 2) และ binlog เพิ่มขึ้น 66 KB ต่อรอบ ถ้ารันทุกนาทีทั้งวันก็ราว 95 MB ต่อวันต่อทุก 1,000 แถว ทั้งที่ไม่มีข้อมูลไหนเปลี่ยนจริง

แถวที่ไม่ได้เปลี่ยนจริงก็ยังมีต้นทุน#

• binlog โตตามจำนวนแถวที่ sync ไม่ใช่ตามจำนวนแถวที่เปลี่ยน MySQL เปิด binlog ไว้เป็นค่าเริ่มต้นและเก็บ 30 วัน ระบบคลังสินค้าที่ sync ข้อมูลจาก ERP ทุกนาทีระบบหนึ่ง อัปเดตตาราง order lines ราว 211,000 แถวในเวลาราว 11 นาที ทั้งที่ตารางมีแค่ราว 37,000 แถว binlog โตวันละราว 7 GB สะสมจนเป็น 183 GB เรื่องฝั่งเซิร์ฟเวอร์อยู่ในโพสต์ วิธีตั้งค่า MySQL บนเซิร์ฟเวอร์ production
• ดิสก์ทำงานโดยไม่จำเป็น ทุกแถวที่อัปเดตต้องเขียน redo log และหน้าข้อมูลกลับลงดิสก์ ถ้ามี replica ก็ต้องเล่นซ้ำทุกแถวเหมือนกัน
• updated_at ไม่มีความหมายอีกต่อไป ทุกแถวเพิ่งแก้เมื่อนาทีที่แล้ว จะถามว่า "วันนี้มีอะไรเปลี่ยนบ้าง" หรือส่งออกเฉพาะแถวที่เปลี่ยนไปให้ระบบอื่นก็ทำไม่ได้

ให้ MySQL เทียบเองว่าแถวไหนเปลี่ยน#

แทนที่จะอัปเดต updated_at ทุกครั้ง ให้ MySQL เลือกเองว่าจะใช้เวลาใหม่หรือเก็บเวลาเดิม โดยดูจากคอลัมน์ข้อมูลทุกตัวที่ sync

on duplicate key update
    `updated_at` = if(
        not (`qty` <=> values(`qty`)) or not (`bin` <=> values(`bin`)),
        values(`updated_at`),
        `updated_at`
    ),
    `qty` = values(`qty`),
    `bin` = values(`bin`)

• <=> คือเครื่องหมายเท่ากับที่รองรับ NULL NULL <=> NULL ได้ 1 ส่วน = ธรรมดาได้ NULL ซึ่งจะทำให้แถวที่มีค่าว่างนับว่าไม่เปลี่ยนตลอด
• ถ้าทุกคอลัมน์เท่าเดิม updated_at ก็ได้ค่าเดิม ทั้งแถวเท่าเดิม MySQL จึงข้ามแถวนั้นไป

ลำดับสำคัญ updated_at ต้องอยู่บรรทัดแรก MySQL ประเมินค่าที่ตั้งจากซ้ายไปขวา คอลัมน์ทางขวาเห็นค่าที่คอลัมน์ทางซ้ายเพิ่งตั้งไป ถ้าวาง updated_at ไว้ท้ายสุด ตอน IF เทียบ qty ก็เป็นค่าใหม่ไปแล้ว ผลคือ updated_at ไม่ขยับเลยแม้ข้อมูลจะเปลี่ยนจริง ลองสลับลำดับดู แถวที่ qty เปลี่ยนจาก 5 เป็น 55 ยังได้ updated_at เดิม

ผลบนตารางเดียวกัน 1,000 แถว

PostgreSQL ไม่มีเรื่องลำดับ เพราะทุกคอลัมน์ใน SET เห็นค่าเดิมของแถว และ ON CONFLICT DO UPDATE ใส่ WHERE ได้ เช่น where (order_lines.qty, order_lines.bin) is distinct from (excluded.qty, excluded.bin) PostgreSQL จะข้ามแถวที่ไม่ผ่านเงื่อนไขไปเลย แต่ upsert() ของ Laravel ไม่มีที่ให้ใส่ WHERE ต้องเขียน SQL เอง

ห่อเป็น helper ให้ทุก job ใช้#

เขียนครั้งเดียวแล้วให้ทุก job ที่ sync เรียกตัวเดียวกัน helper นี้ข้ามส่วนของ Eloquent ไปเรียก upsert() ของ query builder ตรง ๆ เติม timestamps เอง แล้วสร้าง IF จากรายการคอลัมน์ที่อัปเดต

<?php

namespace App\Support;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\DB;

class QuietUpsert
{
    public static function run(Builder $query, array $values, array $uniqueBy, ?array $update = null): int
    {
        if ($values === []) {
            return 0;
        }

        $model = $query->getModel();
        $grammar = $query->getQuery()->getGrammar();
        $now = $model->freshTimestampString();
        $createdAt = $model->getCreatedAtColumn();
        $updatedAt = $model->getUpdatedAtColumn();

        $columns = array_values(array_diff($update ?? array_keys(Arr::first($values)), [$createdAt, $updatedAt]));

        $changed = collect($columns)
            ->map(fn (string $column) => sprintf('not (%1$s <=> values(%1$s))', $grammar->wrap($column)))
            ->implode(' or ') ?: 'false';

        return $query->toBase()->upsert(
            array_map(fn (array $row) => [...$row, $createdAt => $now, $updatedAt => $now], $values),
            $uniqueBy,
            [
                // updated_at ต้องมาก่อน MySQL จะได้เทียบกับค่าเดิมของแถว
                $updatedAt => DB::raw(sprintf('if(%s, values(%2$s), %2$s)', $changed, $grammar->wrap($updatedAt))),
                ...$columns,
            ],
        );
    }
}

QuietUpsert::run(OrderLine::query(), $rows, uniqueBy: ['line_key'], update: ['qty', 'bin']);

• ใช้ได้กับ model ที่มี timestamps เท่านั้น ตารางที่ไม่มี updated_at ใช้ upsert() ของ query builder ได้เลย เพราะไม่มีคอลัมน์ไหนเปลี่ยนเองอยู่แล้ว
• ถ้าเปิด use_upsert_alias ใน config/database.php ไว้ (อธิบายในโพสต์ upsert) ให้เปลี่ยน values(col) เป็น laravel_upsert_alias.col
• test ที่ควรมีคือส่งข้อมูลชุดเดิมซ้ำแล้ว updated_at ต้องไม่ขยับ กับแถวที่ค่าเปลี่ยนต้องได้ updated_at ใหม่ ข้อหลังจะจับได้ทันทีถ้ามีคนย้ายลำดับคอลัมน์

งาน sync ยังเขียนซ้ำได้จากอีกสองทาง#

ลบทิ้งแล้ว insert ใหม่ทั้งชุด#

วิธีที่เขียนง่ายที่สุดสำหรับข้อมูลลูก เช่นรายการ lot ของสินค้าในเอกสาร คือลบของเดิมทั้งหมดแล้ว insert ชุดที่ดึงมาใหม่ ผลเหมือนกันทุกรอบ แต่ทุกรอบคือ DELETE ทุกแถวกับ INSERT ทุกแถว binlog ได้ทั้งสองอย่าง และ id ของแถวเปลี่ยนไปเรื่อย ๆ

ให้เทียบก่อนแทน สร้าง key ของแต่ละแถวจากคอลัมน์ที่บอกว่าเป็นแถวเดียวกัน (เช่น รหัสสินค้า เลข lot จำนวน หน่วย) แล้วลบเฉพาะแถวที่มีในฐานข้อมูลแต่ไม่มีในชุดใหม่ และ insert เฉพาะแถวที่มีในชุดใหม่แต่ยังไม่มีในฐานข้อมูล ถ้า key ซ้ำกันได้ ให้นับจำนวนต่อ key ด้วย รอบที่ไม่มีอะไรเปลี่ยนก็จะไม่มีคำสั่งเขียนเลยสักคำสั่ง

ตัวเลขที่มาเป็น string ทำให้ Laravel นึกว่า model เปลี่ยน#

ERP บางตัวส่งจำนวนมาเป็น "5.000000" ส่วนฐานข้อมูลเก็บเป็น integer 5 พอ updateOrCreate() เอาค่าใหม่ใส่ model แล้วเช็คว่าคอลัมน์ไหนเปลี่ยน Laravel จะเทียบตัวเลขที่ไม่มี cast แบบ string (ดู originalIsEquivalent() ใน HasAttributes) "5.000000" กับ "5" ไม่ตรงกัน model จึง UPDATE ทุกรอบพร้อม updated_at ใหม่

ใส่ cast ให้คอลัมน์ตัวเลข Laravel จะแปลงทั้งสองฝั่งเป็น integer ก่อนเทียบ

protected function casts(): array
{
    return [
        'qty' => 'integer',
    ];
}

เช็กว่างาน sync เขียนเกินจำเป็นหรือเปล่า#

นับว่าในสิบนาทีล่าสุดมีกี่แถวที่ updated_at ขยับ เทียบกับจำนวนแถวทั้งตาราง

SELECT COUNT(*) FROM order_lines WHERE updated_at >= NOW() - INTERVAL 10 MINUTE;

ถ้าตัวเลขใกล้จำนวนแถวทั้งตาราง ทั้งที่ช่วงนั้นไม่มีใครแก้ข้อมูลเยอะขนาดนั้น ก็น่าสงสัยว่ามี job เขียนซ้ำอยู่ ถ้า MySQL เปิด binlog ไว้ นับ row event แยกตามตารางได้ตามวิธีในโพสต์ binlog จะเห็นชัดกว่าว่าตารางไหนเขียนเยอะที่สุด

บางคอลัมน์ตั้งใจให้ขยับทุกรอบจริง เช่น checked_at ที่หน้าจอแสดงว่า "ข้อมูลเมื่อ" กี่โมง แบบนั้นเก็บไว้ได้ แค่รู้ไว้ว่าทุกคอลัมน์แบบนี้คือการเขียนหนึ่งครั้งต่อแถวต่อรอบ และใช้เฉพาะตารางที่ผู้ใช้ต้องเห็นเวลานั้นจริง