วิธีใช้ SQLite JSON ให้ได้ความเร็วระดับดัชนีเต็มรูปแบบ: คิวรีความเร็วสูงด้วย Generated Columns

 (dbpro.app)
11 คะแนน โดย GN⁺ 2025-12-13 | 2 ความคิดเห็น | แชร์ทาง WhatsApp
  • แนะนำแนวทางใช้ ความสามารถด้าน JSON ของ SQLite เพื่อเก็บเอกสาร JSON ต้นฉบับไว้ตามเดิม และดึงเฉพาะฟิลด์ที่ต้องใช้มาอินเด็กซ์ด้วย virtual generated columns
  • ใช้ฟังก์ชัน json_extract เพื่อจัดการข้อมูลภายใน JSON เสมือนเป็นคอลัมน์ และคิวรีได้ด้วย ความเร็วระดับดัชนี B-tree
  • เมื่อจำเป็นต้องรองรับรูปแบบการคิวรีใหม่ ก็สามารถขยายได้ด้วยการเพิ่มคอลัมน์และดัชนี โดยไม่ต้องย้ายข้อมูล
  • วิธีนี้ช่วยให้ได้ทั้ง ความยืดหยุ่นของข้อมูลแบบ schema-less และ ประสิทธิภาพของฐานข้อมูลเชิงสัมพันธ์ ไปพร้อมกัน
  • เน้นว่าเป็นแพตเทิร์นที่ใช้งานได้จริงสำหรับนักพัฒนาที่ใช้ SQLite โดยให้ทั้ง โครงสร้างที่เรียบง่ายและประสิทธิภาพสูง

การผสาน SQLite เข้ากับความสามารถด้าน JSON

  • SQLite รองรับ ฟังก์ชันและโอเปอเรเตอร์ JSON ทำให้สามารถจัดเก็บและจัดการข้อมูล JSON ได้โดยตรง
    • เก็บเอกสาร JSON ไว้ทั้งก้อนไว้ในคอลัมน์เดียว แล้วดึงเฉพาะข้อมูลที่ต้องใช้มาเป็นคอลัมน์เสมือน
    • แนวทางนี้ช่วยให้จัดการข้อมูลได้อย่างยืดหยุ่นโดยไม่ต้องกำหนดสคีมาล่วงหน้า
  • ทีม DB Pro ได้ใช้งาน SQLite อย่างจริงจังตลอดช่วงหลายเดือนที่ผ่านมา และพิสูจน์ความสามารถนี้ในงานจริงแล้ว
    • หากตั้งค่าอย่างเหมาะสม SQLite ก็สามารถใช้งานได้อย่างเสถียรใน สภาพแวดล้อมโปรดักชัน

Virtual Generated Columns

  • ใช้ json_extract เพื่อกำหนดค่าบางตัวภายใน JSON ให้เป็น virtual generated columns
    • คอลัมน์นี้จะไม่จัดเก็บข้อมูลจริง แต่จะคำนวณเมื่อมีการคิวรีและพร้อมใช้งานได้ทันที
    • ไม่ต้องมีขั้นตอน backfill หรือคัดลอกข้อมูลแยกต่างหาก
  • ตัวอย่างเช่น สามารถดึงฟิลด์บางตัวจากข้อมูล JSON มาใช้งานเสมือนเป็นคอลัมน์ได้

การเพิ่มดัชนีและการปรับปรุงประสิทธิภาพ

  • หากเพิ่ม ดัชนี ให้กับคอลัมน์เสมือน ข้อมูล JSON ก็จะถูกค้นหาได้ด้วย ความเร็วระดับดัชนี B-tree เช่นเดียวกับคอลัมน์ทั่วไป
    • คอลัมน์เสมือนที่มีดัชนีจะให้ประสิทธิภาพเทียบเท่าคอลัมน์ในฐานข้อมูลเชิงสัมพันธ์
  • แนวทางนี้ช่วยให้ค้นหาข้อมูลได้รวดเร็ว แม้ข้อมูล JSON จะมีขนาดใหญ่ขึ้นก็ตาม

การเพิ่มรูปแบบการคิวรีใหม่

  • หากภายหลังต้องการค้นหาด้วยฟิลด์ใหม่ ก็เพียง เพิ่มคอลัมน์เสมือนและดัชนีใหม่ เท่านั้น
    • ตัวอย่าง: ดึงฟิลด์ user_id ออกมาแล้วสร้างดัชนี
    • ไม่จำเป็นต้องแก้ไขแถวข้อมูลเดิมหรือทำการย้ายข้อมูล
  • ทำให้สามารถ ขยายการคิวรีได้ทันที โดยไม่ต้องเปลี่ยนโครงสร้างข้อมูล

ข้อดีและความหมายของแพตเทิร์นนี้

  • แพตเทิร์นนี้ผสาน ความยืดหยุ่นของการเก็บ JSON แบบ schema-less เข้ากับ ประสิทธิภาพของดัชนีในฐานข้อมูลเชิงสัมพันธ์
    • ไม่จำเป็นต้องตัดสินใจกลยุทธ์การทำดัชนีทั้งหมดตั้งแต่ขั้นตอนออกแบบเริ่มต้น
    • สามารถเพิ่มคอลัมน์และดัชนีเพื่อปรับแต่งประสิทธิภาพได้ตามความต้องการในภายหลัง
  • นำเสนอในฐานะวิธีจัดการข้อมูลที่ เรียบง่ายแต่ทรงพลัง สำหรับนักพัฒนาที่ใช้ SQLite
  • DB Pro ระบุว่าจะมีบทความเพิ่มเติมเกี่ยวกับความสามารถต่าง ๆ ของ SQLite ตามมาอีก

บทความที่เกี่ยวข้อง

2 ความคิดเห็น

 
GN⁺ 2025-12-13
ความคิดเห็นจาก Hacker News

  • สามารถเข้ารหัสเอกสาร JSON โดยตรงเป็น B-tree ที่ซีเรียลไลซ์แล้ว ได้
    แบบนี้จะช่วยให้ดึงข้อมูลฟิลด์ภายในได้ทันทีด้วยความเร็วระดับมีดัชนี และเพราะตัวเอกสารถูกทำดัชนีไว้แล้วจึงไม่ต้อง parse อีก
    ฟอร์แมตนี้เรียกว่า Lite³ เป็นโปรเจ็กต์ที่ฉันกำลังทำอยู่เอง
    ลิงก์ GitHub

    • เจ๋งมาก! ฉันชอบ Rkyv แต่ต้องใช้ Rust เลยรู้สึกหนักไปหน่อยสำหรับโปรเจ็กต์เล็ก ๆ
      ฉันชอบ Lite³ เป็นพิเศษตรงที่รองรับ ข้อมูลไบนารี ผ่าน lite3_val_bytes
    • ฉันสงสัยว่า Lite³ ต่างจาก JSONB ของ PostgreSQL อย่างไร
      JSONB เข้ารหัสทั้งความยาวอาร์เรย์และออฟเซ็ตไว้ด้วยกัน จึงปรับสมดุลระหว่างประสิทธิภาพการบีบอัดกับความเร็วได้
      Lite³ รองรับ การอัปเดตแบบ in-place แต่ต้อง “vacuum” เป็นระยะเพื่อไม่ให้มีข้อมูลอ่อนไหวตกค้าง
      JSONB อัปเดตได้ยากหากไม่เข้ารหัสใหม่ แต่ Lite³ จัดระเบียบได้ง่ายด้วยการไล่โครงสร้างเพียงอย่างเดียว
      ด้านการบีบอัด JSONB น่าจะดีกว่า แต่ฉันคิดว่าการออกแบบของ Lite³ เป็น แนวทางที่ชาญฉลาดมาก
      ฉันเองก็ดูแล ASN.1 compiler อยู่ เลยสนใจฟอร์แมตการซีเรียลไลซ์แบบนี้มาก Lite³ ทำให้ฉันได้ไอเดียใหม่ ๆ
    • ถ้ามี implementation เวอร์ชัน Rust จะดีมาก

  • ฉันชอบ SQLite มาก แต่สำหรับงานวิเคราะห์กลับใช้ DuckDB บ่อยกว่า
    DuckDB ใช้ไฟล์เดี่ยวเหมือน SQLite แต่จัดการชุดข้อมูลขนาดใหญ่ได้ เร็วมากเป็นพิเศษ
    บน MacBook M2 ก็ยังเร็วมากแม้จะจัดการข้อมูล 20 ล้านแถว
    ตัวอย่างเช่น สามารถอ่านไฟล์ JSON ได้โดยตรงด้วยคำสั่งนี้

    SELECT avg(sale_price), count(DISTINCT customer_id)
FROM '/my-data-lake/sales/2024/*.json';

    นอกจากนี้ยังโหลดคอลัมน์ชนิด JSON และใช้ไวยากรณ์แบบ Postgres อย่าง col->>'$.key' ได้ด้วย

    • ฉันสงสัยว่าคำสั่งแรกนั้นคือการทำดัชนีไฟล์ JSON ในไฟล์ซิสเต็มแบบเฉพาะหน้าหรือเปล่า
    • ถ้าใช้ DuckDB คู่กับเครื่องมือ visualization อย่าง pygwalker ก็วิเคราะห์ข้อมูลระดับหลายล้านแถวได้ภายในไม่กี่วินาที
      แต่ถ้าเอาไปเทียบกับ SQLite ก็อาจไม่ค่อยยุติธรรมนัก SQLite เหมาะกับการสร้างระบบ ส่วน DuckDB เหมาะกับงานวิเคราะห์โดยเฉพาะ
      เวลา deploy ไปหลายแพลตฟอร์ม DuckDB จะยุ่งยากอยู่บ้าง
    • ควรหลีกเลี่ยงการ เก็บชุดข้อมูลแบบไม่บีบอัด อย่างเด็ดขาด DuckDB รองรับฟอร์แมตการบีบอัดหลายแบบ

  • ฉันคิดว่าการใช้ Generated Column เพื่อประสิทธิภาพของ JSON เป็นวิธีที่พบได้บ่อย
    ใน Postgres ฉันก็เคยใช้แบบนี้เพื่อเก็บคีย์ในคอลัมน์ JSON ให้เป็น foreign key มันออกจะเป็นวิธีที่ “ประหลาด” หน่อย แต่ก็ใช้งานได้ดี

    • ถ้าเป็น Postgres ก็สร้างดัชนีให้ฟิลด์ภายใน JSONB โดยตรงได้ไม่ใช่หรือ?
      ตัวอย่างเช่น 
      CREATE INDEX idx_status_gin
ON my_table
USING gin ((data->'status'));
      บล็อกอ้างอิง
    • แต่สุดท้ายวิธีแบบนี้หลายครั้งก็สู้ การแยกออกเป็นตาราง key/value ไม่ได้
      ถ้า schema ของ JSON เปลี่ยน การ parse หรือ migration ก็อาจล้มเหลวได้
    • จริง ๆ ก็ไม่ถึงกับเป็นวิธีที่ “ประหลาด” หรอก ใช้โครงสร้าง relational ที่ normalize แล้วในส่วนที่จำเป็น และใช้ jsonb กับส่วนที่เหลือก็พอ
    • ฉันสงสัยว่าใช้ คอลัมน์ VIRTUAL แทน STORED ได้ไหม ซึ่งตัวอย่างนี้ก็ใช้ VIRTUAL นี่แหละ

  • ฉันเพิ่งรู้จักเทคนิคนี้จากตัวอย่างการปรับแต่งประสิทธิภาพที่ Claude Code เสนอมาเมื่อไม่นานนี้
    เป็นจุดที่พลาดไปเพราะไม่รู้ฟีเจอร์ใหม่ของ SQLite แต่ ประสิทธิภาพที่ดีขึ้น ค่อนข้างมาก
    บทเรียนคือ ต่อให้เป็นเครื่องมือที่คุ้นเคย ก็ควรกลับไปอ่านเอกสารเป็นระยะ ๆ

    • การกลับไปอ่านคู่มือใหม่อีกครั้งเป็นเรื่องที่ ทำให้เกิดความเข้าใจใหม่ ได้อย่างคาดไม่ถึง

  • ฉันได้เขียนบล็อกโพสต์นี้หลังจากเห็นคอมเมนต์ของ bambax บน HN ในปี 2023
    ลิงก์คอมเมนต์ต้นฉบับ

  • คุณสามารถสร้างดัชนีได้แม้ไม่ต้องฉายค่า JSON ออกมาโดยตรง แต่ computed column ทำให้ query เรียบง่ายขึ้น
    ก่อน MS-SQL 2025(v17) การรองรับ JSON ยังจำกัด วิธีนี้จึงแทบเป็นสิ่งจำเป็น

    • ถ้าใช้เฉพาะ computed column โดยไม่ query JSON ตรง ๆ ก็จะไม่เผลอเขียน query ที่ไม่ใช้ดัชนี
    • ฉันเคยได้ยินเรื่องฟีเจอร์นี้ในงานสัมมนา DBA ท้องถิ่น แต่ตอนนั้นไม่ได้รู้สึกว่ามันเปลี่ยนแปลงอะไรใหญ่โต

  • ฉันเปิดบทความจาก HN แล้วพบว่ามีการอ้างถึงคอมเมนต์ของตัวเอง แถมคอมเมนต์นั้นยังเป็นหัวข้อหลักของบทความอีก เป็นประสบการณ์ที่แปลกดี
    พอเห็นประโยค “ขอบคุณนะ, bambax!” ก็อดยิ้มไม่ได้ SQLite เป็นเครื่องมือที่ยอดเยี่ยมจริง ๆ

    • คอมเมนต์ที่เป็นแรงบันดาลใจจริง ๆ อยู่ที่ลิงก์นี้

  • น่าสนใจนะ แต่ใช้ "Index On Expression" ของ SQLite ก็ได้ไม่ใช่หรือ?
    ตัวอย่างเช่น CREATE INDEX idx_events_type ON events(json_extract(data, '$.type'))
    เพียงแต่ถ้าไวยากรณ์ JSON path ต่างกันนิดเดียว ก็อาจทำให้ไม่ใช้ดัชนีได้ ขณะที่ Virtual Generated Column จะรับประกันการใช้ดัชนีเสมอ

    • ถ้าใช้ expression index ร่วมกับ view ก็สามารถรับประกันได้ว่า expression จะตรงกัน
      ตัวอย่าง: โปรเจ็กต์ recordlite
    • ถ้าไวยากรณ์ JSON path ต่างกันแม้เพียงเล็กน้อย ดัชนีก็อาจใช้ไม่ได้
      เช่น json_extract(data, "$.type") กับ data -> '$.type' ถูกมองว่าเป็นคนละแบบ
      กล่าวคือ ถ้า expression ใน WHERE เปลี่ยน ดัชนีก็อาจไร้ประโยชน์ทันที
    • เป็นวิธีแก้ที่เรียบง่ายและเร็ว ความจริงที่ว่าคำสั่ง query กับดัชนีต้องตรงกันนั้นเป็นเรื่องจริงเสมอ
    • ฟีเจอร์ expression index เป็น ฟีเจอร์ที่ค่อนข้างใหม่ ซึ่งถูกเพิ่มเข้ามาตั้งแต่ SQLite 3.9.0 (2015-10-14)

  • ฉันอยากให้เหล่านักพัฒนาหลีกเลี่ยงการใส่ข้อมูลทั้งหมดลงใน คอลัมน์ JSON(B) ทั้งที่จริง ๆ มี schema ที่สม่ำเสมออยู่แล้ว
    เพราะจะทำให้การตั้งดัชนี การจัดการ constraint ยุ่งยากขึ้น และมี overhead สูงขึ้นในการใช้งานจริง

    • คอลัมน์ JSON จะโดดเด่นมากเมื่อใช้กับข้อมูลที่แสดงเป็นตารางได้ยาก เช่นโครงสร้างแบบต้นไม้
      ตัวอย่างเช่น ในสภาพแวดล้อม Haskell+TypeScript การซีเรียลไลซ์โครงสร้างซ้อนที่ซับซ้อนเป็น JSON นั้นสะดวกกว่ามาก
    • JSON(B) มีประโยชน์เมื่อเก็บข้อมูลต่างชนิดไว้ในคอลเล็กชันเดียวกัน
      เช่น เก็บผลลัพธ์จาก payment processor หลายเจ้าไว้ในตารางเดียว หรือจัดการคุณสมบัติที่ต่างกันตามประเภทสินค้าใน เว็บลงประกาศ
      ใน C# หรือ JS/TS ถ้าใช้เครื่องมือตรวจสอบชนิดข้อมูลร่วมด้วย เช่น Zod, OpenAPI ก็จะจัดการได้ง่าย
    • ถ้าเป็น JSON ที่เรียบง่าย การ normalize มักจะดีกว่า แต่ถ้าเป็นการตอบกลับจาก API ที่ซับซ้อน การแตกออกเป็นตารางอาจกลายเป็น นรกของ JOIN
      สุดท้ายก็เป็นเรื่องของการหาสมดุลระหว่างการบำรุงรักษากับประสิทธิภาพ ประเด็นสำคัญของบทความนี้คือ แม้เป็น JSON ก็สร้างดัชนีได้ง่าย
    • ในกรณีอย่างข้อมูลจากเซนเซอร์ที่ต้อง จัดการทั้งโครงสร้างต้นไม้ในคราวเดียว คอลัมน์ JSON จะง่ายกว่ามาก
      ส่วนประสิทธิภาพการอ่านก็ชดเชยได้ด้วยดัชนี
    • การ normalize แบบสมบูรณ์มักไม่มีประสิทธิภาพเสมอไป
      ตัวอย่างเช่น ในระบบกำหนดราคาสินค้า ถ้าต้องเก็บกฎส่วนลดพิเศษเฉพาะลูกค้าในรูป JSON ก็จะยืดหยุ่นกว่ามาก

  • ถ้าใช้ XML แทน JSON นี่ก็คือโมเดลเดียวกับ document store ในยุค 90–00
    คือ parse ตอน insert/update แล้วเวลา query ก็แตะเฉพาะดัชนีเท่านั้น
    น่าสนใจมากที่ SQLite มีความสามารถแบบนี้มาให้ในตัว
 
iolothebard 2025-12-14

ปลายศตวรรษที่ 20… เคยมีสิ่งที่เรียกว่า universal database อยู่… (ตอนนี้อาจจะใช่ แต่ตอนนั้นไม่ใช่.)