แนวทางปฏิบัติแนะนำสำหรับประสิทธิภาพของ SQLite

Android มีการรองรับ SQLite ในตัว ซึ่งเป็นฐานข้อมูล SQL ที่มีประสิทธิภาพ ทำตามแนวทางปฏิบัติแนะนำเหล่านี้เพื่อเพิ่มประสิทธิภาพของแอป เพื่อให้มั่นใจว่าแอปจะยังคงทำงานได้อย่างรวดเร็วและคาดการณ์ได้เมื่อข้อมูลเพิ่มขึ้น การใช้แนวทางปฏิบัติแนะนำเหล่านี้ยังช่วยลดโอกาสที่จะพบปัญหาด้านประสิทธิภาพที่จำลองและแก้ปัญหาได้ยากด้วย

โปรดทำตามหลักการด้านประสิทธิภาพต่อไปนี้เพื่อให้ได้ประสิทธิภาพที่เร็วขึ้น

  • อ่านแถวและคอลัมน์น้อยลง: เพิ่มประสิทธิภาพการค้นหาเพื่อดึงเฉพาะข้อมูลที่จำเป็น ลดปริมาณข้อมูลที่อ่านจากฐานข้อมูล เนื่องจาก การดึงข้อมูลมากเกินไปอาจส่งผลต่อประสิทธิภาพ

  • พุชงานไปยังเครื่องมือ SQLite: ดำเนินการคำนวณ การกรอง และการจัดเรียงภายในคำค้นหา SQL การใช้เครื่องมือค้นหาของ SQLite จะช่วยปรับปรุงประสิทธิภาพได้อย่างมาก

  • แก้ไขสคีมาฐานข้อมูล: ออกแบบสคีมาฐานข้อมูลเพื่อช่วยให้ SQLite สร้างแผนการค้นหาและการแสดงข้อมูลที่มีประสิทธิภาพ จัดทำดัชนีตารางอย่างเหมาะสมและเพิ่มประสิทธิภาพโครงสร้างตารางเพื่อปรับปรุงประสิทธิภาพ

นอกจากนี้ คุณยังใช้เครื่องมือแก้ปัญหาที่มีอยู่เพื่อวัดประสิทธิภาพของฐานข้อมูล SQLite เพื่อช่วยระบุส่วนที่ต้องเพิ่มประสิทธิภาพได้ด้วย

เราขอแนะนำให้ใช้ไลบรารี Jetpack Room

กำหนดค่าฐานข้อมูลเพื่อประสิทธิภาพ

ทำตามขั้นตอนในส่วนนี้เพื่อกำหนดค่าฐานข้อมูลให้มีประสิทธิภาพสูงสุดใน SQLite

เปิดใช้การบันทึกแบบ Write-Ahead

SQLite จะใช้การกลายพันธุ์โดยการเพิ่มลงในบันทึก ซึ่งบางครั้งจะบีบอัดลงในฐานข้อมูล ซึ่งเรียกว่า Write-Ahead Logging (WAL)

เปิดใช้ WAL เว้นแต่คุณจะใช้ ATTACH DATABASE

เปิดใช้ WAL ผ่านเฟรมเวิร์กแทนการเรียกใช้ PRAGMA journal_mode=WAL ด้วยตนเอง Room จะเปิดใช้ WAL โดยค่าเริ่มต้นในอุปกรณ์ที่ไม่ใช่อุปกรณ์ที่มี RAM ต่ำ หากไม่มี Room ให้ใช้ตัวเลือกใดตัวเลือกหนึ่งต่อไปนี้

เมื่อเฟรมเวิร์กทราบว่าฐานข้อมูลใช้ WAL เฟรมเวิร์กจะเปิดกลุ่มการเชื่อมต่อเพื่อให้การอ่านทำงานพร้อมกันกับการเขียนได้ และใช้โหมดซิงโครนัส WAL ที่อธิบายไว้ในส่วนถัดไป หากคุณเรียกใช้ PRAGMA journal_mode=WAL ด้วยตนเอง เฟรมเวิร์กจะยังคงถือว่าฐานข้อมูลเป็นฐานข้อมูล Rollback Journal โดยจะใช้การเชื่อมต่อเดียว และการคอมมิตจะคงโหมดซิงโครนัส FULL เริ่มต้นไว้

ผ่อนคลายโหมดการซิงค์ข้อมูล

SQLite มีตัวเลือกในการควบคุมโหมดซิงโครนัส ในโหมด FULL ทุกๆ การคอมมิตจะออก fsync เพื่อช่วยให้มั่นใจว่าข้อมูลจะไปถึงดิสก์ ซึ่งจะช่วยปรับปรุงความคงทนของข้อมูล แต่จะทำให้การคอมมิตช้าลง เมื่อใช้ WAL โหมด NORMAL จะไม่ซิงค์ในทุกคอมมิตและยังคงปกป้องฐานข้อมูลจากความเสียหาย

เมื่อคุณเปิดใช้ WAL ผ่านเฟรมเวิร์ก Android จะใช้โหมดซิงโครนัส WAL เริ่มต้นของอุปกรณ์ ซึ่งก็คือ NORMAL ใน AOSP สำหรับ Android 9 ขึ้นไป ผู้ผลิตอุปกรณ์สามารถเปลี่ยนค่าเริ่มต้นนี้ได้ และฐานข้อมูลที่ไม่ได้ใช้ WAL จะมีค่าเริ่มต้นเป็น FULL หากต้องการตรวจสอบว่าฐานข้อมูลใช้ NORMAL ให้ตั้งค่าเมื่อคุณเปิดฐานข้อมูล ดังนี้

// When opening the database
val params = SQLiteDatabase.OpenParams.Builder()
    .addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)
    // SYNC_MODE_NORMAL is available in API level 33 and higher.
    // On API levels 28 to 32, pass "NORMAL" instead.
    .setSynchronousMode(SQLiteDatabase.SYNC_MODE_NORMAL)
    .build()
// With SQLiteOpenHelper, call setOpenParams(params) in your constructor.

// Or: after having opened the database
db.execSQL("PRAGMA synchronous = NORMAL")

ในการตั้งค่านี้ ระบบจะแสดงผลการคอมมิตก่อนที่จะจัดเก็บข้อมูลในดิสก์ หากอุปกรณ์ปิดเครื่อง เช่น ไฟดับหรือเคอร์เนลแพนิก ข้อมูลที่คอมมิตไว้อาจสูญหาย อย่างไรก็ตาม เนื่องจากมีการบันทึก ฐานข้อมูลของคุณจึงไม่เสียหาย

หากมีเพียงแอปของคุณที่ขัดข้อง ข้อมูลจะยังคงไปถึงดิสก์ สำหรับแอปส่วนใหญ่ การตั้งค่านี้จะช่วยปรับปรุงประสิทธิภาพโดยไม่มีค่าใช้จ่ายที่สำคัญ

จัดการการเชื่อมต่อและความทรงจำ

การเชื่อมต่อฐานข้อมูลแบบเปิดทุกรายการจะใช้หน่วยความจำ SQLite ได้แก่ แคชหน้าเว็บ, ตัวจัดสรร Lookaside และแคชของคำสั่งที่คอมไพล์แล้ว หน้าไฟล์ฐานข้อมูลจะยังคงอยู่ในแคชหน้าเคอร์เนลด้วย ทำตามแนวทางปฏิบัติในส่วนนี้เพื่อตรวจสอบหน่วยความจำนี้โดยไม่ทำให้การค้นหาช้าลง

เปิดฐานข้อมูลแต่ละรายการ 1 ครั้ง

ใช้ SQLiteOpenHelper หรือ RoomDatabase อินสแตนซ์เดียวต่อไฟล์ฐานข้อมูลสำหรับกระบวนการทั้งหมด เช่น โดยการทำให้เป็น Singleton แต่ละอินสแตนซ์มีพูลการเชื่อมต่อ แคชหน้าเว็บ และแคชคำสั่งของตัวเอง และอินสแตนซ์ 2 รายการที่เปิดไฟล์เดียวกันจะแชร์แคชเหล่านี้ไม่ได้ ในการเปรียบเทียบประสิทธิภาพครั้งหนึ่ง การรวมตัวช่วย 2 ตัวที่เปิดไฟล์เดียวกันไว้ในหน่วยความจำแคชหน้า SQLite หนึ่งหน้าช่วยลดการใช้หน่วยความจำลงเกือบครึ่งหนึ่ง และลดเวลาในการตอบสนองของคำค้นหา p95 ลงมากกว่าครึ่งหนึ่ง

หากต้องการค้นหาอินสแตนซ์ที่ซ้ำกัน ให้ตรวจสอบ dumpsys dbinfo เพื่อหาเส้นทางฐานข้อมูลเดียวกันภายใต้หัวข้อ Connection pool for มากกว่า 1 รายการ

เก็บการเปลี่ยนแปลงสคีมาไว้นอก Callback แบบเปิด

Callback onOpen ของ SQLiteOpenHelper จะทำงานทุกครั้งที่เปิดฐานข้อมูล และ Callback onOpen ของ RoomDatabase.Callback ก็ทำงานเช่นกัน ใส่คำสั่งต่างๆ เช่น CREATE TABLE IF NOT EXISTS และ CREATE INDEX IF NOT EXISTS ใน onCreate และ onUpgrade หรือในการย้ายข้อมูลห้องแชท อย่าเรียกใช้ setVersion ใน onOpen เนื่องจาก SQLiteOpenHelper ตั้งค่าเวอร์ชันหลังจาก onCreate และ onUpgrade ไว้แล้ว แม้ว่าสคีมาจะมีอยู่แล้ว แต่คำสั่งเหล่านี้จะยังคงทำงานทุกครั้งที่เปิด และ setVersion จะเขียนลงในไฟล์ฐานข้อมูล

ปรับขนาดแคชของ Prepared Statement

Android จะแคชคำสั่งที่คอมไพล์แล้วสำหรับการเชื่อมต่อแต่ละรายการ แคชจะเก็บคำสั่งไว้ 25 รายการโดยค่าเริ่มต้น และคุณสามารถเพิ่มได้สูงสุด MAX_SQL_CACHE_SIZE (100) รายการด้วย setMaxSqlCacheSize หากฐานข้อมูลเรียกใช้คำสั่งที่แตกต่างกันมากกว่า 25 รายการในเส้นทางที่ใช้บ่อย แคชจะทำงานหนักเกินไปและ SQLite จะคอมไพล์คำสั่งเดิมซ้ำแล้วซ้ำอีก

ก่อนอื่น ให้กำหนดพารามิเตอร์การค้นหา ไม่มีขนาดแคชใดที่ช่วย SQL ที่สร้างขึ้นด้วยการเชื่อมโยงสตริงได้ เนื่องจากสตริงทุกรายการเป็นคำสั่งใหม่ จากนั้นตรวจสอบการพบและไม่พบแคชของคำสั่งในส่วน POOL STATS ใน dumpsys meminfo ตัวอย่างเช่น ฐานข้อมูล phenotype.db ในเอาต์พุตตัวอย่างของส่วนนั้นมี Hit 147,921 รายการและ Miss 89,616 รายการ ซึ่งเป็นอัตรา Miss 38% การเพิ่มขนาดแคชในฐานข้อมูลเช่นนี้จะช่วยลดการพลาดได้เกือบทั้งหมด คำสั่งที่แคชไว้แต่ละรายการจะใช้หน่วยความจำ 2-3 กิโลไบต์

หากต้องการเพิ่มขนาดแคช ให้ลบล้าง onConfigure ในคลาสย่อย SQLiteOpenHelper ดังนี้

override fun onConfigure(db: SQLiteDatabase) {
    db.setMaxSqlCacheSize(SQLiteDatabase.MAX_SQL_CACHE_SIZE)
}

หากคุณใช้ Room ให้เรียกใช้เมธอดเดียวกันใน SupportSQLiteDatabase ใน Callback onOpen ของ RoomDatabase.Callback

ปิดฐานข้อมูลที่ไม่มีการใช้งาน แต่เปิดฐานข้อมูลที่มีการใช้งานไว้

ฐานข้อมูลที่เปิดอยู่จะเก็บการเชื่อมต่อ แคช และตัวบอกไฟล์ไว้แม้ว่าจะไม่มีการใช้งานก็ตาม ในการเปรียบเทียบหนึ่ง ฐานข้อมูล Room ที่ไม่มีการใช้งานในโหมด WAL ซึ่งเปิดอยู่จะใช้หน่วยความจำแคชหน้า SQLite ประมาณ 678 KB และตัวบอกไฟล์ 5 รายการ หากคุณใช้ฐานข้อมูลเป็นครั้งคราวเท่านั้น เช่น ในระหว่างการซิงค์รายวัน ให้ปิดฐานข้อมูลเมื่อดำเนินการเสร็จแล้ว

หากต้องการปิดฐานข้อมูลที่ไม่มีการใช้งานอย่างปลอดภัย ให้ทำตามหลักเกณฑ์ต่อไปนี้

  • Room สามารถปิดฐานข้อมูลที่ไม่มีการใช้งานให้คุณได้ด้วย setAutoCloseTimeout ซึ่งเป็น API เวอร์ชันทดลอง Room จะเปิดฐานข้อมูลอีกครั้งเมื่อมีการเข้าถึงครั้งถัดไปและเรียกใช้ Callback onOpen อีกครั้ง ดังนั้นให้ใส่การกำหนดค่าการเชื่อมต่อไว้ที่นั่น
  • หลีกเลี่ยงวิธีการ setIdleConnectionTimeout ของ SQLiteOpenHelper และ SQLiteDatabase.OpenParams.Builder เมธอดเหล่านี้เลิกใช้งานแล้วในระดับ API 29 เนื่องจากจะปิดการเชื่อมต่อโดยไม่คืนค่าการตั้งค่าที่คุณใช้กับคำสั่ง PRAGMA
  • อย่าปิดฐานข้อมูลที่คุณค้นหาทุกๆ 2-3 วินาที ระยะหมดเวลาที่สั้นกว่าช่องว่างระหว่างการค้นหาจะทำให้ฐานข้อมูลปิดและเปิดใหม่ซ้ำๆ ซึ่งจะทิ้งแคชหน้าเว็บและแคชคำสั่งทุกครั้ง และทำให้การค้นหาครั้งถัดไปช้าลง

บัญชีสำหรับหน่วยความจำ SQLite

หน่วยความจำ SQLite อาจสังเกตได้ยาก แคชหน้า SQLite และแคชคำสั่งคือการจัดสรรฮีปแบบเนทีฟ แต่ Android ไม่ได้เปิดใช้ I/O ที่แมปหน่วยความจำของ SQLite โดยค่าเริ่มต้น ดังนั้น SQLite จึงอ่านไฟล์ฐานข้อมูลด้วยการอ่านไฟล์ปกติ และเนื้อหาไฟล์ที่เคอร์เนลแคชไว้คือหน้าไฟล์ที่ไม่ได้แมป โดยจะไม่นับรวมใน PSS ของแอปใน dumpsys meminfo ในอุปกรณ์ที่ใช้การบันทึกการใช้งานหน่วยความจำ cgroup v2 เคอร์เนลจะยังคงเรียกเก็บเงินหน้าเหล่านี้ไปยัง cgroup หน่วยความจำของแอป หากต้องการลดการใช้หน่วยความจำที่ซ่อนอยู่นี้ ให้อ่านหน้าเว็บน้อยลงโดยใช้ดัชนีและอ่านเฉพาะแถวและคอลัมน์ที่ต้องการ การปิดฐานข้อมูลที่ไม่จำเป็น ยังช่วยเพิ่มพื้นที่ว่างในแคช SQLite ด้วย

กำหนดสคีมาตารางที่มีประสิทธิภาพ

กำหนดสคีมาตารางที่มีประสิทธิภาพเพื่อเพิ่มประสิทธิภาพและลดการใช้ข้อมูล SQLite สร้างแผนการค้นหาและข้อมูลที่มีประสิทธิภาพ ซึ่งช่วยให้ดึงข้อมูลได้เร็วขึ้น ส่วนนี้จะแสดงแนวทางปฏิบัติแนะนำในการสร้างสคีมาตาราง

โปรดพิจารณา INTEGER PRIMARY KEY

สำหรับตัวอย่างนี้ ให้กำหนดและป้อนข้อมูลในตารางดังนี้

CREATE TABLE Customers(
  id INTEGER,
  name TEXT,
  city TEXT
);
INSERT INTO Customers Values(456, 'John Lennon', 'Liverpool, England');
INSERT INTO Customers Values(123, 'Michael Jackson', 'Gary, IN');
INSERT INTO Customers Values(789, 'Dolly Parton', 'Sevier County, TN');

เอาต์พุตของตารางจะเป็นดังนี้

rowid id ชื่อ เมือง
1 456 John Lennon ลิเวอร์พูล อังกฤษ
2 123 Michael Jackson แกรี รัฐอินดีแอนา
3 789 Dolly Parton เซเวียร์เคาน์ตี รัฐเทนเนสซี

คอลัมน์ rowid เป็นดัชนีที่คงลำดับการแทรกไว้ การค้นหาที่กรองตาม rowid จะใช้การค้นหา B-tree ที่รวดเร็ว แต่การค้นหาที่กรองตาม id จะเป็นการสแกนตารางที่ช้า

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

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT
);

ตอนนี้ตารางของคุณจะมีลักษณะดังนี้

id ชื่อ เมือง
123 Michael Jackson แกรี รัฐอินดีแอนา
456 John Lennon ลิเวอร์พูล อังกฤษ
789 Dolly Parton เซเวียร์เคาน์ตี รัฐเทนเนสซี

เนื่องจากคุณไม่จำเป็นต้องจัดเก็บคอลัมน์ rowid การค้นหา id จึงทำได้อย่างรวดเร็ว โปรดทราบว่าตอนนี้ตารางจะจัดเรียงตามidแทนที่จะเป็นใบสั่งซื้อการใส่โฆษณา

เร่งการค้นหาด้วยดัชนี

SQLite ใช้ดัชนีเพื่อเร่งการค้นหา เมื่อกรอง (WHERE) จัดเรียง (ORDER BY) หรือรวม (GROUP BY) คอลัมน์ หากตารางมีดัชนีสำหรับคอลัมน์นั้น การค้นหาจะเร็วขึ้น

ในตัวอย่างก่อนหน้านี้ การกรองตาม city ต้องสแกนทั้งตาราง ดังนี้

SELECT id, name
WHERE city = 'London, England';

สำหรับแอปที่มีการค้นหาเมืองจำนวนมาก คุณสามารถเร่งการค้นหาเหล่านั้นด้วยดัชนีได้ดังนี้

CREATE INDEX city_index ON Customers(city);

ดัชนีจะใช้เป็นตารางเพิ่มเติม โดยจัดเรียงตามคอลัมน์ดัชนีและแมปกับ rowid ดังนี้

เมือง rowid
แกรี รัฐอินดีแอนา 2
ลิเวอร์พูล อังกฤษ 1
เซเวียร์เคาน์ตี รัฐเทนเนสซี 3

โปรดทราบว่าตอนนี้ค่าใช้จ่ายในการจัดเก็บคอลัมน์ city เพิ่มขึ้นเป็น 2 เท่า เนื่องจากตอนนี้คอลัมน์ดังกล่าวอยู่ในทั้งตารางเดิมและดัชนี เนื่องจากคุณใช้ดัชนี ค่าใช้จ่ายของพื้นที่เก็บข้อมูลที่เพิ่มเข้ามาจึงคุ้มค่ากับประโยชน์ของการค้นหาที่รวดเร็วขึ้น อย่างไรก็ตาม อย่าเก็บรักษาดัชนีที่ไม่ได้ใช้เพื่อหลีกเลี่ยงการจ่ายค่าพื้นที่เก็บข้อมูลโดยไม่ได้รับประสิทธิภาพการค้นหาเพิ่มขึ้น

สร้างดัชนีหลายคอลัมน์

หากการค้นหารวมหลายคอลัมน์ คุณสามารถสร้างดัชนีหลายคอลัมน์เพื่อเร่งการค้นหาได้อย่างเต็มที่ คุณยังสามารถใช้ดัชนีในคอลัมน์ด้านนอก และให้การค้นหาภายในทำเป็นการสแกนตามลำดับได้ด้วย

ตัวอย่างเช่น หากมีคำค้นหาต่อไปนี้

SELECT id, name
WHERE city = 'London, England'
ORDER BY city, name

คุณสามารถเร่งการค้นหาด้วยดัชนีแบบหลายคอลัมน์ตามลำดับเดียวกับที่ระบุไว้ในการค้นหาได้ ดังนี้

CREATE INDEX city_name_index ON Customers(city, name);

อย่างไรก็ตาม หากคุณมีดัชนีเฉพาะใน city การจัดลำดับภายนอกจะยังคงได้รับการเร่งความเร็ว ในขณะที่การจัดลำดับภายในต้องใช้การสแกนตามลำดับ

ซึ่งใช้ได้กับการสอบถามคำนำหน้าด้วย ตัวอย่างเช่น ดัชนี ON Customers (city, name) ยังช่วยเร่งการกรอง การจัดลำดับ และการจัดกลุ่มตาม city ด้วย เนื่องจากตารางดัชนีสำหรับดัชนีหลายคอลัมน์จะจัดลำดับตามดัชนีที่ระบุในลำดับที่กำหนด

โปรดพิจารณา WITHOUT ROWID

โดยค่าเริ่มต้น SQLite จะสร้างคอลัมน์ rowid สำหรับตารางของคุณ โดยที่ rowid เป็น INTEGER PRIMARY KEY AUTOINCREMENT โดยนัย หากคุณมีคอลัมน์ที่เป็น INTEGER PRIMARY KEY อยู่แล้ว คอลัมน์นี้จะกลายเป็นชื่อแทนของ rowid

สำหรับตารางที่มีคีย์หลักอื่นนอกเหนือจาก INTEGER หรือคอลัมน์แบบผสม ให้พิจารณาใช้ WITHOUT ROWID

จัดเก็บข้อมูลขนาดเล็กเป็น BLOB และข้อมูลขนาดใหญ่เป็นไฟล์

หากต้องการเชื่อมโยงข้อมูลขนาดใหญ่กับแถว เช่น ภาพขนาดย่อของรูปภาพหรือรูปภาพสำหรับรายชื่อติดต่อ คุณสามารถจัดเก็บข้อมูลในคอลัมน์ BLOB หรือในไฟล์ แล้วจัดเก็บเส้นทางในคอลัมน์ได้

โดยทั่วไปแล้ว ระบบจะปัดเศษไฟล์ขึ้นเป็นจำนวนที่เพิ่มขึ้นทีละ 4 KB สำหรับไฟล์ขนาดเล็กมากซึ่งข้อผิดพลาดในการปัดเศษมีนัยสำคัญ การจัดเก็บไฟล์ในฐานข้อมูลเป็น BLOB จะมีประสิทธิภาพมากกว่า SQLite ลดการเรียกใช้ระบบไฟล์และเร็วกว่าระบบไฟล์ที่สำคัญในบางกรณี

ปรับปรุงประสิทธิภาพการค้นหา

ทำตามแนวทางปฏิบัติแนะนำต่อไปนี้เพื่อปรับปรุงประสิทธิภาพการค้นหาใน SQLite โดยลดเวลาในการตอบสนองและเพิ่มประสิทธิภาพการประมวลผลให้สูงสุด

อ่านเฉพาะแถวที่ต้องการ

ตัวกรองช่วยให้คุณจำกัดผลการค้นหาให้แคบลงได้โดยระบุเกณฑ์บางอย่าง เช่น ช่วงวันที่ สถานที่ หรือชื่อ ขีดจำกัดช่วยให้คุณควบคุมจำนวนผลลัพธ์ที่เห็นได้ ดังนี้

db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Process cursor data
    }
}

อ่านเฉพาะคอลัมน์ที่ต้องการ

หลีกเลี่ยงการเลือกคอลัมน์ที่ไม่จำเป็น ซึ่งอาจทำให้การค้นหาช้าลงและสิ้นเปลืองทรัพยากร แต่ให้เลือกเฉพาะคอลัมน์ที่ใช้เท่านั้น

ในตัวอย่างต่อไปนี้ คุณเลือก id, name และ phone

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT id, name, phone
    FROM customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(1)
        // Further processing
    }
}

อย่างไรก็ตาม คุณต้องมีคอลัมน์ name เท่านั้น

db.rawQuery("""
    SELECT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(0)
        // Further processing
    }
}

กำหนดพารามิเตอร์ให้คำค้นหา

สตริงคำค้นหาอาจมีพารามิเตอร์ที่ทราบเฉพาะที่รันไทม์ เช่น

fun getNameById(id: Long): String?
    db.rawQuery(
        "SELECT name FROM customers WHERE id=$id", null
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

ในโค้ดก่อนหน้า ทุกการค้นหาจะสร้างสตริงที่แตกต่างกัน จึงไม่ได้รับประโยชน์จากแคชคำสั่ง การเรียกแต่ละครั้งต้องใช้ SQLite เพื่อคอมไพล์ก่อนจึงจะดำเนินการได้ แต่คุณสามารถแทนที่อาร์กิวเมนต์ id ด้วยพารามิเตอร์และเชื่อมโยงค่ากับ selectionArgs แทนได้ ดังนี้

fun getNameById(id: Long): String? {
    db.rawQuery(
        """
          SELECT name
          FROM customers
          WHERE id=?
        """.trimIndent(), arrayOf(id.toString())
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

ตอนนี้คุณสามารถคอมไพล์การค้นหาเพียงครั้งเดียวและแคชไว้ได้ ระบบจะนำการค้นหาที่คอมไพล์แล้วกลับมาใช้ซ้ำระหว่างการเรียกใช้ getNameById(long) ที่แตกต่างกัน

ทำซ้ำใน SQL ไม่ใช่ในโค้ด

ใช้การค้นหาเดียวที่แสดงผลลัพธ์เป้าหมายทั้งหมด แทนการวนซ้ำแบบเป็นโปรแกรมในการค้นหา SQL เพื่อแสดงผลลัพธ์แต่ละรายการ ลูปแบบเป็นโปรแกรมจะช้ากว่าการค้นหา SQL รายการเดียวประมาณ 1,000 เท่า

ใช้ DISTINCT สำหรับค่าที่ไม่ซ้ำกัน

การใช้คีย์เวิร์ด DISTINCT จะช่วยปรับปรุงประสิทธิภาพการค้นหาได้โดยการลดจำนวนข้อมูลที่ต้องประมวลผล เช่น หากต้องการแสดงเฉพาะค่าที่ไม่ซ้ำกันจากคอลัมน์ ให้ใช้ DISTINCT:

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        // Process distinct name
    }
}

ใช้ฟังก์ชันรวมข้อมูลเมื่อเป็นไปได้

ใช้ฟังก์ชันรวมข้อมูลสำหรับผลลัพธ์รวมโดยไม่มีข้อมูลแถว ตัวอย่างเช่น โค้ดต่อไปนี้จะตรวจสอบว่ามีแถวที่ตรงกันอย่างน้อย 1 แถวหรือไม่

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        // At least one customer from Paris
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
}

หากต้องการดึงข้อมูลเฉพาะแถวแรก คุณสามารถใช้ EXISTS() เพื่อแสดงผล 0 หากไม่มีแถวที่ตรงกัน และ 1 หากมีแถวที่ตรงกันอย่างน้อย 1 แถว ดังนี้

db.rawQuery("""
    SELECT EXISTS (
        SELECT null
        FROM Customers
        WHERE city = 'Paris';
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        // At least one customer from Paris
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
    }
}

ใช้ฟังก์ชันรวมข้อมูล SQLite ในโค้ดของแอป

  • COUNT: นับจำนวนแถวในคอลัมน์
  • SUM: เพิ่มค่าตัวเลขทั้งหมดในคอลัมน์
  • MIN หรือ MAX: กำหนดค่าต่ำสุดหรือสูงสุด ใช้ได้กับคอลัมน์ตัวเลข ประเภท DATE และประเภทข้อความ
  • AVG: ค้นหาค่าตัวเลขเฉลี่ย
  • GROUP_CONCAT: เชื่อมโยงสตริงกับตัวคั่นที่ไม่บังคับ

ใช้ COUNT() แทน Cursor.getCount()

ในตัวอย่างต่อไปนี้ ฟังก์ชัน Cursor.getCount() จะอ่านแถวทั้งหมดจากฐานข้อมูลและแสดงค่าแถวทั้งหมด

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    val count = cursor.getCount()
    // Use count
}

อย่างไรก็ตาม เมื่อใช้ COUNT() ฐานข้อมูลจะแสดงเฉพาะจำนวนเท่านั้น

db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    cursor.moveToFirst()
    val count = cursor.getInt(0)
    // Use count
}

การค้นหา Nest แทนโค้ด

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

ในตัวอย่างต่อไปนี้ คุณสามารถเรียกใช้การค้นหาเพื่อดูว่าเมืองใดมีลูกค้ามากที่สุด จากนั้นใช้ผลลัพธ์ในการค้นหาอื่นเพื่อค้นหาลูกค้าทั้งหมดจากเมืองนั้น

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        val topCity = cursor.getString(0)
        db.rawQuery("""
            SELECT name, city
            FROM Customers
            WHERE city = ?;
        """.trimIndent(),
        arrayOf(topCity)).use { innerCursor ->
            while (innerCursor.moveToNext()) {
                // Process inner cursor data
            }
        }
    }
}

หากต้องการได้ผลลัพธ์โดยใช้เวลาเพียงครึ่งหนึ่งของตัวอย่างก่อนหน้านี้ ให้ใช้การค้นหา SQL รายการเดียวที่มีคำสั่งที่ซ้อนกัน ดังนี้

db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
        SELECT city
        FROM Customers
        GROUP BY city
        ORDER BY COUNT (*) DESC
        LIMIT 1;
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToNext()) {
        // Process cursor data
    }
}

ตรวจสอบความไม่ซ้ำกันใน SQL

หากต้องไม่แทรกแถวเว้นแต่ค่าคอลัมน์หนึ่งๆ จะไม่ซ้ำกันในตาราง การบังคับใช้ความไม่ซ้ำกันนั้นเป็นข้อจำกัดของคอลัมน์อาจมีประสิทธิภาพมากกว่า

ในตัวอย่างต่อไปนี้ ระบบจะเรียกใช้การค้นหาหนึ่งรายการเพื่อตรวจสอบแถวที่จะแทรก และอีกรายการหนึ่งเพื่อแทรกจริง

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT EXISTS (
        SELECT null
        FROM customers
        WHERE username = ?
    );
    """.trimIndent(),
    arrayOf(customer.username)
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        throw AddCustomerException(customer)
    }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    arrayOf(
        customer.id.toString(),
        customer.name,
        customer.username
    )
)

แทนที่จะตรวจสอบข้อจำกัดที่ไม่ซ้ำกันใน Kotlin คุณสามารถตรวจสอบใน SQL เมื่อกำหนดตารางได้ ดังนี้

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE
);

SQLite จะทำสิ่งเดียวกันดังนี้

CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);

ตอนนี้คุณสามารถแทรกแถวและให้ SQLite ตรวจสอบข้อจำกัดได้แล้ว

try {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        arrayOf(customer.id.toString(), customer.name, customer.username)
    )
} catch(e: SQLiteConstraintException) {
    throw AddCustomerException(customer, e)
}

SQLite รองรับดัชนีที่ไม่ซ้ำกันซึ่งมีหลายคอลัมน์ ดังนี้

CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);

SQLite ตรวจสอบข้อจำกัดได้เร็วกว่าและมีค่าใช้จ่ายในการดำเนินการน้อยกว่าโค้ด Kotlin แนวทางปฏิบัติแนะนำคือใช้ SQLite แทนโค้ดแอป

จัดกลุ่มการแทรกหลายรายการในธุรกรรมเดียว

ธุรกรรมจะคอมมิตการดำเนินการหลายรายการ ซึ่งไม่เพียงแต่ช่วยเพิ่มประสิทธิภาพเท่านั้น แต่ยังช่วยเพิ่มความถูกต้องด้วย คุณสามารถแทรกเป็นกลุ่มเพื่อปรับปรุงความสอดคล้องของข้อมูลและเร่งประสิทธิภาพได้ ดังนี้

db.beginTransaction()
try {
    customers.forEach { customer ->
        db.execSQL(
            "INSERT INTO Customers VALUES (?, ?, ?)",
            arrayOf(customer.id.toString(), customer.name, "customerValue")
        )
    }
    db.setTransactionSuccessful()
} finally {
    db.endTransaction()
}

นอกธุรกรรมที่ชัดเจน การเขียนแต่ละครั้งจะทำงานในธุรกรรมโดยนัยของตัวเองและจ่ายค่าคอมมิตเต็มจำนวน รวมถึง fsync ในโหมดซิงโครนัส FULL การจัดกลุ่มจะจ่ายค่าใช้จ่ายนั้นเพียงครั้งเดียว ซึ่งจะทำให้การเขียนแบบกลุ่มเร็วขึ้นอย่างมาก

ใช้เครื่องมือแก้ปัญหา

SQLite มีเครื่องมือแก้ปัญหาต่อไปนี้เพื่อช่วยวัดประสิทธิภาพ

ใช้พรอมต์แบบอินเทอร์แอกทีฟของ SQLite

เรียกใช้ SQLite ในเครื่องเพื่อเรียกใช้การค้นหาและเรียนรู้ แพลตฟอร์ม Android เวอร์ชันต่างๆ ใช้ SQLite เวอร์ชันที่แตกต่างกัน หากต้องการใช้เครื่องมือเดียวกับที่อยู่ในอุปกรณ์ที่ทำงานด้วยระบบ Android ให้ใช้ adb shell และเรียกใช้ sqlite3 ในอุปกรณ์เป้าหมาย

คุณขอให้ SQLite จับเวลาการค้นหาได้โดยทำดังนี้

sqlite> .timer on
sqlite> SELECT ...
Run Time: real ... user ... sys ...

EXPLAIN QUERY PLAN

คุณขอให้ SQLite อธิบายวิธีตอบคำถามได้โดยใช้ EXPLAIN QUERY PLAN

sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SCAN Customers

ตัวอย่างก่อนหน้านี้ต้องมีการสแกนตารางทั้งหมดโดยไม่มีดัชนีเพื่อค้นหาลูกค้าทั้งหมดจากปารีส ซึ่งเรียกว่าความซับซ้อนเชิงเส้น SQLite ต้องอ่านแถวทั้งหมดและเก็บเฉพาะแถวที่ตรงกับลูกค้าจากปารีส หากต้องการแก้ไขปัญหานี้ คุณสามารถเพิ่มดัชนีได้โดยทำดังนี้

sqlite> CREATE INDEX Idx1 ON Customers(city);
sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SEARCH test USING INDEX Idx1 (city=?

หากใช้เชลล์แบบอินเทอร์แอกทีฟ คุณสามารถขอให้ SQLite อธิบายแผนการค้นหาเสมอได้โดยทำดังนี้

sqlite> .eqp on

ดูข้อมูลเพิ่มเติมได้ที่การวางแผนการค้นหา

SQLite Analyzer

SQLite มีอินเทอร์เฟซบรรทัดคำสั่ง (CLI) sqlite3_analyzer เพื่อดัมป์ข้อมูลเพิ่มเติมที่ใช้แก้ปัญหาด้านประสิทธิภาพได้ หากต้องการติดตั้ง ให้ไปที่หน้าดาวน์โหลด SQLite

คุณสามารถใช้ adb pull เพื่อดาวน์โหลดไฟล์ฐานข้อมูลจากอุปกรณ์เป้าหมายไปยังเวิร์กสเตชันเพื่อวิเคราะห์ได้ โดยทำดังนี้

adb pull /data/data/<app_package_name>/databases/<db_name>.db

SQLite Browser

นอกจากนี้ คุณยังติดตั้งเครื่องมือ GUI SQLite Browser ได้ในหน้าดาวน์โหลด SQLite

การบันทึกของ Android

Android จะจับเวลาการค้นหา SQLite และบันทึกให้คุณ ดังนี้

# Enable query time logging
$ adb shell setprop log.tag.SQLiteTime VERBOSE
# Disable query time logging
$ adb shell setprop log.tag.SQLiteTime ERROR

ใน Android 13 ขึ้นไป คุณยังบันทึกเฉพาะการดำเนินการที่ช้าได้ด้วย ตั้งค่าเกณฑ์เป็นมิลลิวินาที แล้วรีสตาร์ทแอปเพื่อให้แอปอ่านแท็กบันทึก

$ adb shell setprop log.tag.SQLiteSlowQueries VERBOSE
$ adb shell setprop db.log.slow_query_threshold 100
$ adb shell am force-stop <package-name>

การดำเนินการที่ช้าจะปรากฏใน logcat ภายใต้แท็ก SQLiteConnection พร้อมระยะเวลา, SQL และเส้นทางฐานข้อมูล หากต้องการตั้งค่าเกณฑ์สำหรับแอปเดียว ให้ใช้ db.log.slow_query_threshold.<uid> โดยที่ <uid> คือ UID ของแอป

การติดตาม Perfetto

เมื่อกำหนดค่า Perfetto ให้เพิ่มหมวดหมู่ database atrace เพื่อรวมการดำเนินการ SQLite ในการติดตาม หากต้องการดูว่าแต่ละเทรดทำอะไรขณะเรียกใช้ SQL ให้เพิ่มเหตุการณ์ของเครื่องจัดตารางเวลาด้วย โดยทำดังนี้

data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      ftrace_events: "sched/sched_switch"
      ftrace_events: "sched/sched_waking"
      ftrace_events: "sched/sched_blocked_reason"
      atrace_categories: "database"
    }
  }
}

Android จะติดตามการดำเนินการฐานข้อมูลแต่ละรายการเป็นส่วนที่ตั้งชื่อตามการดำเนินการและ SQL ของการดำเนินการนั้น เช่น executeForCursorWindow SELECT * FROM message WHERE body LIKE ? SQL จะเก็บตัวยึดตำแหน่ง ? ไว้และตัดให้เหลือ 256 อักขระ โดยจะไม่รวมอาร์กิวเมนต์การเชื่อมโยง การดำเนินการต่างๆ ได้แก่ open, close, prepare, execute และตัวแปร executeFor ส่วนแบ่ง prepare ไม่ได้หมายความว่าไม่พบแคชของคำสั่ง

เลย์เอาต์ของแทร็กจะขึ้นอยู่กับเวอร์ชัน Android ดังนี้

  • ใน Android 17 ขึ้นไป การเชื่อมต่อแต่ละรายการจะมีแทร็กแบบไม่พร้อมกันในกระบวนการของแอปชื่อ SQLiteConnection- ตามด้วยหมายเลขการเชื่อมต่อ ธุรกรรมจะปรากฏเป็นส่วน TRANSACTION-EXCLUSIVE, TRANSACTION-IMMEDIATE หรือ TRANSACTION-DEFERRED โดยมีคำสั่งที่ทำงานในธุรกรรมซ้อนอยู่ด้านล่าง เทรดที่เรียกใช้การดำเนินการจะได้รับเหตุการณ์ START- และ END- ทันทีในแทร็กเทรดสำหรับธุรกรรมทุกรายการและคำสั่งทุกรายการนอกธุรกรรม ชื่อของเหตุการณ์เหล่านี้จะลงท้ายด้วยชื่อแทร็กการเชื่อมต่อ เช่น -SQLiteConnection-0
  • ใน Android 16 และต่ำกว่า คำสั่ง SQL ที่แตกต่างกันแต่ละรายการจะมีแทร็กแบบไม่พร้อมกันของตัวเองในกระบวนการของแอป และจะไม่มีการติดตามธุรกรรม

หมายเลขการเชื่อมต่อจะเริ่มต้นใหม่ที่ 0 สำหรับฐานข้อมูลแต่ละรายการ ดังนั้นการเชื่อมต่อหลักของฐานข้อมูลทั้งหมดในกระบวนการจะใช้แทร็ก SQLiteConnection-0 ร่วมกัน หากแอปใช้ฐานข้อมูลมากกว่า 1 ฐานข้อมูลพร้อมกัน ให้วัดระยะเวลาด้วยเหตุการณ์ START- และ END- ในแทร็กชุดข้อความ

เทรดหลักและแทร็ก SQLiteConnection-0 ใน Perfetto ซึ่งแสดงการค้นหา LIKE ที่สแกนทั้งตารางเป็นเวลา 12 มิลลิวินาทีในเทรดหลัก

รูปที่ 1 การสแกนตารางแบบเต็มในเทรดหลักใน Android 17

คุณวิเคราะห์ส่วนเหล่านี้ได้ด้วย PerfettoSQL ใน Perfetto UI หรือด้วย trace_processor การค้นหาต่อไปนี้จะค้นหาการดำเนินการ SQLite ที่พบบ่อยที่สุดในแต่ละกระบวนการ การค้นหานี้ใช้ได้กับ Android ทุกเวอร์ชัน

INCLUDE PERFETTO MODULE slices.with_context;

SELECT process_name, name AS operation, COUNT(*) AS count
FROM process_slice
WHERE name GLOB 'open *' OR name GLOB 'close *'
  OR name GLOB 'prepare *' OR name GLOB 'execute*'
  OR name GLOB 'TRANSACTION-*'
GROUP BY process_name, name
ORDER BY count DESC
LIMIT 10;

การค้นหาต่อไปนี้แสดงรายการการดำเนินการ SQLite ที่ใช้เวลานานกว่า 1 มิลลิวินาทีในเทรดหลักใน Android 17 ขึ้นไป

INCLUDE PERFETTO MODULE slices.with_context;

WITH marks AS (
  SELECT process_name, utid, is_main_thread, ts, name,
    LEAD(ts) OVER (PARTITION BY utid ORDER BY ts) AS end_ts,
    LEAD(name) OVER (PARTITION BY utid ORDER BY ts) AS end_name
  FROM thread_slice
  WHERE name GLOB 'START-*' OR name GLOB 'END-*'
)
SELECT process_name, ts, ROUND((end_ts - ts) / 1e6, 1) AS dur_ms,
  substr(name, 7) AS operation
FROM marks
WHERE name GLOB 'START-*'
  AND end_name = 'END-' || substr(name, 7)
  AND is_main_thread
  AND end_ts - ts > 1e6
ORDER BY dur_ms DESC
LIMIT 10;

ผลการค้นหา Perfetto ที่แสดงการดำเนินการ SQLite ที่ใช้เวลานานกว่า 1 มิลลิวินาทีในเทรดหลักของแอปทดสอบ

รูปที่ 2 การดำเนินการ SQLite ที่ใช้เวลานานกว่า 1 มิลลิวินาทีในเทรดหลัก

การค้นหาต่อไปนี้จะค้นหาชุดข้อความที่เขียนนอกธุรกรรมที่ชัดเจน ซึ่งการเขียนทุกครั้งจะจ่ายสำหรับการคอมมิตของตัวเองใน Android 17 ขึ้นไป

INCLUDE PERFETTO MODULE slices.with_context;

WITH ops AS (
  SELECT process_name, thread_name, utid, name,
    upper(ltrim(substr(name, instr(name, ' ') + 1))) AS sql
  FROM thread_slice
  WHERE name GLOB 'START-*'
)
SELECT process_name, thread_name,
  SUM(name GLOB 'START-TRANSACTION-*') AS transactions,
  SUM(name GLOB 'START-execute*' AND (sql GLOB 'INSERT *'
    OR sql GLOB 'UPDATE *' OR sql GLOB 'DELETE *' OR sql GLOB 'REPLACE *'))
    AS autocommit_writes
FROM ops
GROUP BY utid
HAVING autocommit_writes > 0
ORDER BY autocommit_writes DESC
LIMIT 10;

dumpsys meminfo

adb shell dumpsys meminfo <package-name> จะพิมพ์สถิติที่เกี่ยวข้องกับการใช้งานหน่วยความจำของแอป รวมถึงรายละเอียดเกี่ยวกับ SQLite ในส่วน DATABASES ตัวอย่างเช่น ตัวอย่างนี้มาจากเอาต์พุตของ adb shell dumpsys meminfo com.google.android.gms.persistent ในอุปกรณ์ของนักพัฒนาแอป

DATABASES
      pgsz     dbsz   Lookaside(b) cache hits cache misses cache size  Dbname
PER CONNECTION STATS
         4       52             45     8    41     6  /data/user/10/com.google.android.gms/databases/gaia-discovery
         4        8                    0     0     0    (attached) temp
         4       52             56     5    23     6  /data/user/10/com.google.android.gms/databases/gaia-discovery (1)
         4      252             95   233   124    12  /data/user_de/10/com.google.android.gms/databases/phenotype.db
         4        8                    0     0     0    (attached) temp
         4      252             17     0    17     1  /data/user_de/10/com.google.android.gms/databases/phenotype.db (1)
         4     9280            105 103169 69805    25  /data/user/10/com.google.android.gms/databases/phenotype.db
         4       20                    0     0     0    (attached) temp
         4     9280            108 13877  6394    25  /data/user/10/com.google.android.gms/databases/phenotype.db (2)
         4        8                    0     0     0    (attached) temp
         4     9280            105 12548  5519    25  /data/user/10/com.google.android.gms/databases/phenotype.db (3)
         4        8                    0     0     0    (attached) temp
         4     9280            107 18328  7886    25  /data/user/10/com.google.android.gms/databases/phenotype.db (1)
         4        8                    0     0     0    (attached) temp
         4       36             51   156    29     5  /data/user/10/com.google.android.gms/databases/mobstore_gc_db_v0
         4       36             97    47    27    10  /data/user/10/com.google.android.gms/databases/context_feature_default.db
         4       36             56     3    16     4  /data/user/10/com.google.android.gms/databases/context_feature_default.db (2)
         4      300             40  2111    24     5  /data/user/10/com.google.android.gms/databases/gservices.db
         4      300             39     3    17     4  /data/user/10/com.google.android.gms/databases/gservices.db (1)
         4       20             17     0    14     1  /data/user/10/com.google.android.gms/databases/gms.notifications.db
         4       20             33     1    15     2  /data/user/10/com.google.android.gms/databases/gms.notifications.db (1)
         4      120             40   143   163     4  /data/user/10/com.google.android.gms/databases/android_pay
         4      120            123    86    32    19  /data/user/10/com.google.android.gms/databases/android_pay (1)
         4       28             33     4    17     3  /data/user/10/com.google.android.gms/databases/googlesettings.db
POOL STATS
     cache hits  cache misses    cache size  Dbname
             13            68            81  /data/user/10/com.google.android.gms/databases/gaia-discovery
            233           145           378  /data/user_de/10/com.google.android.gms/databases/phenotype.db
         147921         89616        237537  /data/user/10/com.google.android.gms/databases/phenotype.db
            156            30           186  /data/user/10/com.google.android.gms/databases/mobstore_gc_db_v0
             50            57           107  /data/user/10/com.google.android.gms/databases/context_feature_default.db
           2114            43          2157  /data/user/10/com.google.android.gms/databases/gservices.db
              1            31            32  /data/user/10/com.google.android.gms/databases/gms.notifications.db
            229           197           426  /data/user/10/com.google.android.gms/databases/android_pay
              4            18            22  /data/user/10/com.google.android.gms/databases/googlesettings.db

Android 14 ขึ้นไปจะพิมพ์ตาราง PER CONNECTION STATS และ POOL STATS ที่แสดงในตัวอย่าง Android 13 และต่ำกว่าจะพิมพ์เฉพาะตารางต่อการเชื่อมต่อ โดยรวมสถิติแคชไว้ในคอลัมน์ cache เดียวเป็น hits/misses/size

ในส่วนPER CONNECTION STATS คุณจะเห็นข้อมูลต่อไปนี้

  • pgsz: ขนาดของหน้าฐานข้อมูล 1 หน้า มีหน่วยเป็น KB
  • dbsz: ขนาดของไฟล์ฐานข้อมูลทั้งหมดในหน่วย KB
  • Lookaside(b): จำนวนช่องหน่วยความจำ Lookaside ที่การเชื่อมต่อใช้ แม้ว่าจะมี (b) ในส่วนหัว แต่ค่านี้คือจำนวนช่อง ไม่ใช่จำนวนไบต์
  • cache hits, cache misses และ cache size: สถิติสำหรับแคชคำสั่งที่เตรียมไว้ของการเชื่อมต่อ ค่าเหล่านี้จะนับคำสั่ง SQL ที่คอมไพล์แล้ว ไม่ใช่หน้าฐานข้อมูล
  • Dbname: เส้นทางไปยังไฟล์ฐานข้อมูล การเชื่อมต่อเพิ่มเติมกับฐานข้อมูลเดียวกันจะมีตัวเลขในวงเล็บต่อท้าย เช่น (1) เมื่อใช้ WAL ฐานข้อมูลจะมีการเชื่อมต่อหลายรายการเพื่อให้การอ่านทำงานพร้อมกันได้

ในส่วน POOL STATS คุณจะเห็นสถิติแคชของคำสั่งที่เตรียมไว้สำหรับพูลการเชื่อมต่อแต่ละพูล ดังนี้

  • cache hits: SQLite จะแคชคำสั่งที่เตรียมไว้และพยายามนำกลับมาใช้ซ้ำเมื่อเรียกใช้การค้นหา เพื่อประหยัดเวลาและหน่วยความจำในการคอมไพล์คำสั่ง SQL นี่คือจำนวนครั้งที่พบแคชของคำสั่ง
  • cache misses: จำนวนครั้งที่ไม่พบแคชของคำสั่ง
  • cache size: ใน Android 16 QPR2 ขึ้นไป จำนวนคำสั่งที่เตรียมไว้ในแคช ซึ่งรวมจากการเชื่อมต่อที่ไม่มีการใช้งานของพูล ใน Android 14 ถึง Android 16 QPR1 ค่านี้คือผลรวมของ Hit และ Miss และไม่ได้แสดงถึงขนาดแคช

ใช้สถิติเหล่านี้เพื่อตัดสินใจว่าจะแก้ไขอะไรบ้าง

  • อัตราการไม่พบที่สูงหมายความว่า SQLite คอมไพล์คำสั่งเดียวกันซ้ำๆ ก่อนอื่น ให้มองหา SQL ที่สร้างขึ้นด้วยการเชื่อมโยงสตริงและกำหนดพารามิเตอร์ หากการค้นหาของคุณมีการกำหนดพารามิเตอร์แล้ว และ cache size ต่อการเชื่อมต่อถึงขีดจำกัดที่ 25 ให้เพิ่มขนาดแคช
  • เส้นทางฐานข้อมูลที่ปรากฏมากกว่า 1 ครั้งโดยไม่มีคำต่อท้ายสไตล์ (1) หมายความว่าแอปของคุณเปิดไฟล์เดียวกัน 2 ครั้ง เปิดฐานข้อมูลแต่ละรายการ 1 ครั้ง
  • ฐานข้อมูลที่แอปไม่ค่อยได้ใช้จะยังคงใช้หน่วยความจำขณะที่เปิดอยู่ ปิดเมื่อเสร็จแล้ว

dumpsys dbinfo

adb shell dumpsys dbinfo <package-name> จะพิมพ์สถานะของพูลการเชื่อมต่อทุกพูลในแอปของคุณ เพิ่ม -v เพื่อแสดงรายการคำสั่งในแคชคำสั่งที่เตรียมไว้ของการเชื่อมต่อแต่ละรายการด้วย ตัวอย่างเอาต์พุตต่อไปนี้มาจาก Android 17 โดยมีการย่อเส้นทางและบรรทัดที่ยาวให้สั้นลง

** Database info for pid 2705 [com.example.app] **
Connection pool for /data/user/0/com.example.app/databases/settings.db:
  Open: true
  Max connections: 4
  Total execution time (ms): 4963
  Total statements executed: 231239
  Average time per statement (ms): 0
  Configuration: openFlags=805306368, ..., journalMode=WAL, syncMode=NORMAL
  IsReadOnlyDatabase: false
  Available primary connection:
    <none>
  Available non-primary connections:
    <none>
  Acquired connections:
    <none>
  Connection waiters:
    <none>
Connection pool for /data/user/0/com.example.app/databases/events.db:
  Open: true
  Max connections: 1
  Total execution time (ms): 842
  Total statements executed: 840
  Average time per statement (ms): 1
  Configuration: openFlags=268435456, ..., journalMode=TRUNCATE, syncMode=FULL
  IsReadOnlyDatabase: false
  Available primary connection:
    Connection #0:
      isPrimaryConnection: true
      onlyAllowReadOnlyOperations: false
      totalLongOperations: 0
      Most recently executed operations:
        0: [10-26 04:22:32.133] executeForChangedRowCount took 0ms -
           succeeded, sql="DELETE FROM params WHERE app_id=?", path=...
        1: [10-26 04:22:32.133] prepare took 0ms - succeeded,
           sql="DELETE FROM params WHERE app_id=?", path=...
        ...

โดยให้มองหาสิ่งต่อไปนี้

  • Configuration: โหมดบันทึกและโหมดซิงโครนัสของพูล ใน Android 13 ขึ้นไป บรรทัดนี้จะแสดงโหมดที่เฟรมเวิร์กใช้ รวมถึงค่าเริ่มต้นของแพลตฟอร์ม ในตัวอย่างนี้ events.db ใช้สมุดบันทึกการย้อนกลับที่มีโหมดซิงโครนัส FULL ดังนั้นการคอมมิตทุกครั้งจึงต้องรอการซิงค์แบบเต็ม บรรทัดนี้ไม่แสดงรายการเคลื่อนไหวของ PRAGMA ที่คุณเรียกใช้เอง
  • Max connections: ค่า 1 สำหรับฐานข้อมูลที่เขียนได้หมายความว่าเฟรมเวิร์กไม่ถือว่าฐานข้อมูลอยู่ในโหมด WAL ดังนั้นการอ่านและการเขียนจึงใช้การเชื่อมต่อร่วมกัน
  • Total execution time, Total statements executed และ Average time per statement: พร้อมใช้งานใน Android 13 ขึ้นไป ใช้เพื่อค้นหาฐานข้อมูลที่มีการใช้งานมากที่สุด
  • Most recently executed operations: การดำเนินการ 20 รายการล่าสุดในการเชื่อมต่อแต่ละรายการ พร้อมด้วยระยะเวลาและ SQL มองหารูปแบบต่างๆ เช่น การค้นหาเดียวกันที่ทำงานในลูป หรือการเขียนหลายครั้งติดต่อกันนอกธุรกรรม ใน Android 15 QPR1 ขึ้นไป รายการนี้จะรวมธุรกรรมที่ชัดเจนด้วย
  • totalLongOperations และ Operations exceeding 2000ms: พร้อมใช้งานใน Android 15 QPR1 ขึ้นไป โดยจะแสดงจำนวนการดำเนินการและธุรกรรมที่ใช้เวลานานกว่า 2 วินาที และแสดงรายการ 10 รายการล่าสุด
  • Acquired connections และ Connection waiters: การเชื่อมต่อที่ใช้งานอยู่และชุดข้อความที่กำลังรอการเชื่อมต่อ Waiters บ่งบอกถึงการช่วงชิงทรัพยากร เช่น ธุรกรรมการเขียนที่ใช้เวลานานซึ่งบล็อกการเขียนอื่นๆ
  • Prepared statement cache: พิมพ์ด้วย -v รายการจำนวนมากที่แตกต่างกันเฉพาะในค่าลิเทอรัลจะชี้ไปยัง SQL ที่สร้างขึ้นด้วยการเชื่อมโยงสตริง
  • เส้นทางเดียวกันภายใต้ส่วนหัว Connection pool for มากกว่า 1 รายการหมายความว่าแอปเปิดไฟล์เดียวกัน 2 ครั้ง

แหล่งข้อมูลเพิ่มเติม

ดูเนื้อหา