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

แนวคิดและการติดตั้งใช้งาน Jetpack Compose

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

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

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

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

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

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

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

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

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

ลดโหมดการซิงโครไนซ์

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

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

Kotlin

// When opening the database
val paramsBuilder: SQLiteDatabase.OpenParams.Builder = SQLiteDatabase.OpenParams.Builder()
paramsBuilder.journalMode = SQLiteDatabase.SYNC_MODE_NORMAL

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

Java

// When opening the database
SQLiteDatabase.OpenParams.Builder paramsBuilder = new SQLiteDatabase.OpenParams.Builder();
paramsBuilder.setJournalMode(SQLiteDatabase.SYNC_MODE_NORMAL);

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

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

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

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

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

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

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

Kotlin

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

Java

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

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

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

ในตัวอย่างต่อไปนี้ คุณจะเลือก 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)
        // Further processing
    }
}

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);
    // Further processing
  }
}

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

Kotlin

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

Java

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

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

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

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) ที่แตกต่างกัน

ใช้ 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
        // Process distinct name
    }
}

Java

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

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

ใช้ฟังก์ชันการรวมข้อมูลสำหรับผลลัพธ์รวมโดยไม่มีข้อมูลแถว เช่น โค้ดต่อไปนี้จะตรวจสอบว่ามีแถวที่ตรงกันอย่างน้อย 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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
}

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

หากต้องการดึงข้อมูลเฉพาะแถวแรก คุณสามารถใช้ 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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
    }
}

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

ใช้ฟังก์ชันการรวมข้อมูลของ 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()
    // Use count
}

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

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

Kotlin

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

Java

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

ซ้อนการค้นหาแทนโค้ด

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()) {
                // Process inner cursor data
            }
        }
    }
}

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()) {
          // Process inner cursor data
        }
    }
  }
}

หากต้องการรับผลลัพธ์ในครึ่งหนึ่งของเวลาในตัวอย่างก่อนหน้า ให้ใช้การค้นหา 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()) {
        // Process cursor data
    }
}

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()) {
    // Process cursor data
  }
}

ตรวจสอบความไม่ซ้ำกันใน 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,
    });

คุณสามารถตรวจสอบข้อจำกัดที่ไม่ซ้ำกันใน 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, "customerValue")
        )
    }
} finally {
    db.endTransaction()
}

Java

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