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

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

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

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

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

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

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

เราขอแนะนำให้ใช้คลัง Jetpack Room

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

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

เปิดใช้การบันทึกการเขียนล่วงหน้า

SQLite ใช้การเปลี่ยนแปลงโดยการต่อท้ายการเปลี่ยนแปลงเหล่านั้นลงในบันทึก ซึ่งจะ บีบอัดลงในฐานข้อมูลเป็นครั้งคราว ซึ่งเรียกว่าการบันทึกการเขียนล่วงหน้า (WAL)

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

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

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

SQLite มีตัวเลือกในการควบคุมโหมด พร้อมกัน หาก เปิดใช้ WAL ให้ตั้งค่าโหมดพร้อมกันเป็น NORMAL

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

db.execSQL("PRAGMA synchronous = NORMAL");

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

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

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

กำหนดสคีมาตารางที่มีประสิทธิภาพเพื่อเพิ่มประสิทธิภาพและลดการใช้อินเทอร์เน็ต 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 Sevier County, เทนเนสซี

คอลัมน์ 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 Sevier County, เทนเนสซี

เนื่องจากคุณไม่จำเป็นต้องจัดเก็บคอลัมน์ 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
Sevier County, เทนเนสซี 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 โดยการลดเวลาตอบสนองและเพิ่มประสิทธิภาพการประมวลผล

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

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

Kotlin

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

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    ...
  }
}

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

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

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

Kotlin

// 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)
        // ...
    }
}

Java

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

try (Cursor cursor = db.rawQuery("""
    SELECT id, name, phone
    FROM customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(1);
    ...
  }
}

แต่คุณต้องมีคอลัมน์ name เท่านั้น

Kotlin

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

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    ...
  }
}

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

สตริงคำค้นหาอาจมีพารามิเตอร์ที่รู้จักเฉพาะในเวลาเรียกใช้ เช่น

Kotlin

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
        }
    }
}

Java

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery(
      "SELECT name FROM customers WHERE id=" + id, null)) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

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

Kotlin

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
        }
    }
}

Java

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery("""
          SELECT name
          FROM customers
          WHERE id=?
      """, new String[] {String.valueOf(id)})) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

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

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

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

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

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

Kotlin

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

Java

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    ...
  }
}

ใช้ฟังก์ชันการรวมข้อมูลทุกครั้งที่เป็นไปได้

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

Kotlin

// 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
        ...
    } else {
        // No customers from Paris
        ...
}

Java

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

try (Cursor cursor = db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """, null)) {
  if (cursor.moveToFirst()) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

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

Kotlin

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
        ...
    } else {
        // No customers from Paris
        ...
    }
}

Java

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

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

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

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

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

Kotlin

// 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()
}

Java

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

try (Cursor cursor = db.rawQuery("""
    SELECT id
    FROM Customers;
    """, null)) {
  int count = cursor.getCount();
  ...
}

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

Kotlin

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

Java

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  ...
}

คำค้นหา Nest แทนโค้ด

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

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

Kotlin

// 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()) {
                ...
            }
        }
    }
}

Java

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

try (Cursor cursor = db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """, null)) {
  if (cursor.moveToFirst()) {
    String topCity = cursor.getString(0);
    try (Cursor innerCursor = db.rawQuery("""
        SELECT name, city
        FROM Customers
        WHERE city = ?;
        """, new String[] {topCity})) {
        while (innerCursor.moveToNext()) {
          ...
        }
    }
  }
}

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

Kotlin

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()) {
        ...
    }
}

Java

try (Cursor cursor = db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
      SELECT city
      FROM Customers
      GROUP BY city
      ORDER BY COUNT(*) DESC
      LIMIT 1
    );
    """, null)) {
  while(cursor.moveToNext()) {
    ...
  }
}

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

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

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

Kotlin

// 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
    )
)

Java

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

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM customers
      WHERE username = ?
    );
    """, new String[] { customer.username })) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    throw new AddCustomerException(customer);
  }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    new String[] {
      String.valueOf(customer.id),
      customer.name,
      customer.username,
    });

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

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

SQLite จะทำงานเช่นเดียวกับคำสั่งต่อไปนี้

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

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

Kotlin

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

Java

try {
  db.execSQL(
      "INSERT INTO Customers VALUES (?, ?, ?)",
      new String[] {
        String.valueOf(customer.id),
        customer.name,
        customer.username,
      });
} catch (SQLiteConstraintException e) {
  throw new AddCustomerException(customer, e);
}

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

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

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

แทรกหลายรายการพร้อมกันในธุรกรรมเดียว

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

Kotlin

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

Java

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ...)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          ...
        });
  }
} finally {
  db.endTransaction()
}

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

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

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

การติดตาม Perfetto

เมื่อกำหนดค่า Perfetto คุณอาจ เพิ่มรายการต่อไปนี้เพื่อรวมแทร็กสำหรับคำค้นหาแต่ละรายการ

data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      atrace_categories: "database"
    }
  }
}

dumpsys meminfo

adb shell dumpsys meminfo <package-name> จะพิมพ์สถิติที่เกี่ยวข้องกับการใช้หน่วยความจำของแอป รวมถึงรายละเอียดบางอย่างเกี่ยวกับหน่วยความจำ SQLite ตัวอย่างเช่น ข้อมูลนี้ ได้จากเอาต์พุตของ 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

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

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

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

  • cache hits: SQLite จะแคชคำสั่งที่เตรียมไว้และพยายามนำกลับมาใช้ใหม่ เมื่อเรียกใช้การค้นหา เพื่อประหยัดความพยายามและหน่วยความจำในการคอมไพล์คำสั่ง SQL นี่คือจำนวนการเข้าชมแคชของคำสั่ง (จำนวน)
  • cache misses: จำนวนแคชคำสั่งที่พลาด (จำนวน)
  • cache size: เริ่มต้นด้วย Android 17 รายการนี้จะแสดงจำนวนคำสั่งที่เตรียมไว้ทั้งหมดในแคช ในเวอร์ชันก่อนหน้า ค่านี้จะเท่ากับผลรวมของการเข้าชมและการไม่พบที่ระบุไว้ในอีก 2 คอลัมน์ และไม่ได้แสดงถึงขนาดแคช