روال‌های مطلوب برای عملکرد SQLite (بازدیدها)

مفاهیم و پیاده‌سازی Jetpack Compose

Android پشتیبانی داخلی برای SQLite، یک پایگاه داده SQL کارآمد، ارائه می‌دهد. این روال‌های مطلوب را دنبال کنید تا عملکرد برنامه‌تان را بهینه‌سازی کنید و مطمئن شوید که با افزایش داده‌هایتان، سریع و به‌طور قابل‌پیش‌بینی سریع باقی می‌ماند. بااستفاده از این روال‌های مطلوب، احتمال مواجه شدن با مشکلات عملکردی را که بازتولید و عیب‌یابی آن‌ها دشوار است نیز کاهش می‌دهید.

برای دستیابی به عملکرد سریع‌تر، این اصول عملکرد را دنبال کنید:

  • خواندن ردیف‌ها و ستون‌های کمتر: پُرسمان‌هایتان را بهینه‌سازی کنید تا فقط داده‌های ضروری را بازیابی کنید. مقدار داده‌های خوانده‌شده از پایگاه داده را به‌حداقل برسانید، زیرا بازیابی داده‌های اضافی می‌تواند بر عملکرد تأثیر بگذارد.

  • انتقال کار به موتور SQLite: عملیات محاسباتی، فیلتر کردن، و مرتب‌سازی را در پُرسمان‌های SQL انجام دهید. استفاده از موتور پرسمان SQLite می‌تواند عملکرد را به‌طور قابل‌توجهی بهبود بخشد.

  • اصلاح طرح‌واره پایگاه داده: طرح‌واره پایگاه داده‌تان را به‌گونه‌ای طراحی کنید که به SQLite کمک کند طرح‌های پُرسمان و نمایش‌های داده کارآمدی بسازد. نمایه‌سازی صحیح جدول‌ها و بهینه‌سازی ساختار جدول‌ها برای بهبود عملکرد.

علاوه‌براین، می‌توانید از ابزارهای عیب‌یابی موجود برای اندازه‌گیری عملکرد پایگاه داده SQLite خود استفاده کنید تا به شناسایی بخش‌هایی که نیاز به بهینه‌سازی دارند کمک کنید.

توصیه می‌کنیم از کتابخانه Jetpack Room استفاده کنید.

پیکربندی پایگاه داده برای عملکرد

مراحل این بخش را دنبال کنید تا پایگاه داده‌تان را برای عملکرد بهینه در SQLite پیکربندی کنید.

آسان‌تر کردن حالت همگام‌سازی

SQLite گزینه‌ای برای کنترل حالت همگام دارد. در حالت FULL، هر تعهد یک fsync صادر می‌کند تا اطمینان حاصل شود که داده‌ها به دیسک می‌رسند. این کار دوام داده‌ها را بهبود می‌بخشد اما سرعت ثبت را کاهش می‌دهد. با WAL، حالت NORMAL در هر ثبت همگام‌سازی نمی‌شود و همچنان از پایگاه داده دربرابر خرابی محافظت می‌کند.

وقتی WAL را ازطریق چارچوب فعال می‌کنید، Android حالت همگام WAL پیش‌فرض دستگاه را اعمال می‌کند که در AOSP برای Android نسخه ۹ و بالاتر NORMAL است. سازندگان دستگاه می‌توانند این پیش‌فرض را تغییر دهند و پایگاه‌های داده‌ای که از 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")

جاوا

// When opening the database
SQLiteDatabase.OpenParams params = new 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");

در این تنظیم، پیش‌از ذخیره شدن داده‌ها در دیسک، ممکن است یک commit برگردانده شود. اگر دستگاه خاموش شود، مثلاً به‌دلیل قطع برق یا کرنل پنیک، ممکن است داده‌های ثبت‌شده ازدست برود. بااین‌حال، به‌دلیل ثبت گزارش، پایگاه داده شما خراب نشده است.

اگر فقط برنامه‌تان ازکار بیفتد، داده‌هایتان همچنان به دیسک می‌رسد. برای اکثر برنامه‌ها، این تنظیمات باعث بهبود عملکرد بدون هزینه مادی می‌شود.

بهبود عملکرد پُرسمان

این روال‌های مطلوب را دنبال کنید تا عملکرد پُرسمان را در SQLite با به‌حداقل رساندن زمان پاسخ و به‌حداکثر رساندن کارایی پردازش بهبود دهید.

فقط ردیف‌های موردنیازتان را بخوانید

فیلترها به شما امکان می‌دهند نتایج را با مشخص کردن معیارهای خاصی، مانند محدوده تاریخ، مکان، یا نام، محدود کنید. محدودیت‌ها به شما امکان می‌دهد تعداد نتایجی را که می‌بینید کنترل کنید:

کاتلین

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

جاوا

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  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
    }
}

جاوا

// 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 نیاز دارید:

کاتلین

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

جاوا

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String 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
        }
    }
}

جاوا

@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 مقید کنید:

کاتلین

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

جاوا

@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 استفاده کنید:

کاتلین

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

جاوا

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

هرزمان ممکن بود از توابع تجمیعی استفاده کنید

برای نتایج تجمیعی بدون داده‌های ردیف، از توابع تجمیعی استفاده کنید. برای مثال، کد زیر بررسی می‌کند که آیا حداقل یک ردیف منطبق وجود دارد یا نه:

کاتلین

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

جاوا

// 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 درصورت وجود یک یا چند ردیف منطبق استفاده کنید:

کاتلین

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

جاوا

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() همه ردیف‌ها را از پایگاه داده می‌خواند و همه مقادیر ردیف را برمی‌گرداند:

کاتلین

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

جاوا

// 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()، پایگاه داده فقط تعداد را برمی‌گرداند:

کاتلین

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

جاوا

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

پرس‌وجوهای تودرتو به‌جای کد

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

جاوا

// 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 با عبارت‌های تودرتو استفاده کنید:

کاتلین

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

جاوا

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

اگر ردیفی نباید درج شود مگر اینکه مقدار ستون خاصی در جدول یکتا باشد، ممکن است اعمال آن یکتایی به‌عنوان محدودیت ستون کارآمدتر باشد.

در مثال زیر، یک پُرسمان برای اعتبارسنجی ردیفی که باید درج شود اجرا می‌شود و پُرسمان دیگری برای درج واقعی:

کاتلین

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

جاوا

// 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 اجازه دهید محدودیت را بررسی کند:

کاتلین

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

جاوا

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 استفاده کنید.

دسته‌بندی چند درج در یک تراکنش

تراکنش چندین عملیات را انجام می‌دهد که نه‌تنها کارایی بلکه درستی را نیز بهبود می‌بخشد. برای بهبود سازگاری داده‌ها و تسریع عملکرد، می‌توانید درج‌ها را دسته‌بندی کنید:

کاتلین

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

جاوا

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