แนวทางปฏิบัติแนะนำสำหรับประสิทธิภาพของ 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);
    ...
  }
}

กำหนดพารามิเตอร์การค้นหาด้วยการ์ด SQL ไม่ใช่การต่อสตริง

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

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() จะทำให้ฐานข้อมูลแสดงเฉพาะจํานวน

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 เป็นแบบคอมโพสิทและรองรับการค้นหาย่อย การรวม และการจำกัดคีย์ต่างประเทศ คุณสามารถใช้ผลลัพธ์ของการค้นหารายการหนึ่งในการค้นหารายการอื่นได้โดยไม่ต้องผ่าน App Code วิธีนี้ช่วยลดความจำเป็นในการคัดลอกข้อมูลจาก 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

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

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

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,
    });

แทนที่จะตรวจสอบข้อจำกัดที่ไม่ซ้ำกันใน Kotlin หรือ Java คุณสามารถตรวจสอบได้ใน 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 ตรวจสอบข้อจำกัดได้แล้ว โดยทำดังนี้

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

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

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

เบราว์เซอร์ SQLite

นอกจากนี้ คุณยังติดตั้งเครื่องมือ 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"
    }
  }
}