การสร้าง Video URL Canonicalization Pipeline

ผมได้รันคิวรีบนฐานข้อมูล SQLite ในระบบโปรดักชัน และพบปัญหาใหญ่มาก

ตาราง videos มีข้อมูล 41,283 แถว แต่มีชื่อวิดีโอที่ไม่ซ้ำกันเพียง 29,000 ชื่อ เรากำลังเก็บวิดีโอตัวเดิมซ้ำหลายครั้ง

ทำไมถึงเป็นแบบนี้? วิดีโอ YouTube ตัวเดียวกันถูกส่งมาในรูปแบบ URL ที่ต่างกัน:

• Short links: youtu.be/ID • Desktop links: youtube.com/watch?v=ID • Mobile links: m.youtube.com/watch?v=ID • Shorts: youtube.com/shorts/ID

วิดีโอหนึ่งตัวหมายถึงข้อมูล 4 แถวในฐานข้อมูล และหน้าเว็บที่เกือบจะเหมือนกัน 4 หน้า สิ่งนี้ทำให้เสีย crawl budget และทำให้เกิดข้อผิดพลาดใน Google Search Console

ผมทำ TrendVidStream เราดึงข้อมูลเทรนด์จาก 8 ภูมิภาคที่แตกต่างกัน แต่ละภูมิภาคสามารถแสดงวิดีโอไวรัลตัวเดียวกันโดยใช้รูปแบบ URL ที่ต่างกันได้

ผมจึงสร้าง pipeline ขึ้นมาเพื่อแก้ไขปัญหานี้โดยใช้ PHP 8.4 และ SQLite

นี่คือวิธีการทำงานของ pipeline:

  1. Extract: เปลี่ยน URL รูปแบบใดก็ตามให้เป็น video ID ขนาด 11 ตัวอักษรที่คงที่
  2. Normalize: สร้าง URL มาตรฐาน (canonical URL) เพียงหนึ่งเดียวจาก ID นั้น
  3. Validate: ตรวจสอบเบื้องต้นอย่างรวดเร็วก่อนจะแตะต้องฐานข้อมูล
  4. Upsert: ใช้ SQLite UPSERT เพื่อรวมข้อมูลแทนการเพิ่มแถวใหม่
  5. Emit: เพิ่ม canonical tags และ 301 redirects เพื่อให้ Search Engine พบ URL เดียวกัน

การตัดสินใจทางเทคนิคที่สำคัญ:

• ใช้ host allowlist ที่แน่นอน ห้ามใช้การจับคู่ด้วยส่วนท้าย (suffix matching) เพื่อป้องกันความเสี่ยงด้านความปลอดภัย • ห้ามเปลี่ยน ID เป็นตัวพิมพ์เล็กเด็ดขาด เพราะ YouTube ID เป็นแบบ case-sensitive การเปลี่ยนเป็นตัวพิมพ์เล็กจะทำให้วิดีโอที่ต่างกันถูกรวมเข้าด้วยกัน • ใช้ UNIQUE constraint บน video_id นี่คือเกราะป้องกันที่ดีที่สุดในการป้องกันข้อมูลซ้ำ • ใช้ SQLite WAL mode เพื่อให้การเขียนข้อมูลรวดเร็วและเชื่อถือได้

ผลลัพธ์ที่ได้:

ตารางวิดีโอของเราลดลงจาก 41,283 แถว เหลือ 28,094 แถว โดยไม่เสียวิดีโอไปเลยแม้แต่ตัวเดียว ข้อผิดพลาดใน Google Search Console ลดลงจาก 412 เหลือ 9 ผลการค้นหาดูสะอาดตาขึ้นเพราะเราหยุดแสดงวิดีโอตัวเดิมซ้ำถึงสี่ครั้ง

บทเรียนนี้ง่ายมาก: เมื่อคุณรวบรวมข้อมูลจากหลายแหล่ง ให้สร้างระบบระบุตัวตน (identity) ไว้ในระบบของคุณก่อน ดึง ID ที่คงที่ออกมา บังคับใช้ด้วยข้อกำหนดของฐานข้อมูล และทำให้ทุกการเขียนข้อมูลเป็นแบบ idempotent

คุณไม่จำเป็นต้องใช้เครื่องมือที่ซับซ้อน แค่ PHP และ SQLite ก็เพียงพอแล้ว

Source: https://dev.to/ahmet_gedik778845/building-a-video-url-canonicalization-pipeline-in-php-84-with-sqlite-32ne