Рекомендации по повышению производительности SQLite

В Android есть встроенная поддержка SQLite – эффективной базы данных SQL. Следуйте этим рекомендациям, чтобы оптимизировать производительность вашего приложения и обеспечить его быструю и предсказуемую работу по мере роста объема данных. Кроме того, это поможет вам избежать проблем с производительностью, которые сложно воспроизвести и устранить.

Чтобы повысить производительность, следуйте этим принципам:

• Считывайте меньше строк и столбцов. Оптимизируйте запросы, чтобы получать только необходимые данные. Минимизируйте объем данных, считываемых из базы данных, поскольку извлечение избыточных данных может повлиять на производительность.

• Перенос вычислений в ядро SQLite. Выполняйте вычисления, фильтрацию и сортировку в SQL-запросах. Использование механизма запросов SQLite может значительно повысить производительность.

• Измените схему базы данных. Разработайте схему базы данных, которая поможет SQLite создавать эффективные планы запросов и представления данных. Правильно индексируйте таблицы и оптимизируйте их структуру, чтобы повысить производительность.

Кроме того, вы можете использовать доступные инструменты устранения неполадок, чтобы измерить производительность базы данных SQLite и определить области, требующие оптимизации.

Рекомендуем использовать библиотеку Jetpack Room.

Как настроить базу данных для повышения производительности

Чтобы настроить базу данных для оптимальной производительности в SQLite, выполните действия, описанные в этом разделе.

Включить упреждающую запись в журнал

SQLite реализует изменения, добавляя их в журнал, который время от времени сжимается в базу данных. Это называется упреждающей записью в журнал (WAL).

Включите WAL, если вы не используете ATTACH DATABASE.

Включите WAL через фреймворк, а не запускайте PRAGMA journal_mode=WAL самостоятельно. Room по умолчанию включает WAL на устройствах с достаточным объемом ОЗУ. Если у вас нет Room, используйте один из следующих вариантов:

• Метод enableWriteAheadLogging
• Пометка о восстановлении ENABLE_WRITE_AHEAD_LOGGING
• Метод setWriteAheadLoggingEnabled объекта SQLiteOpenHelper

Когда фреймворк знает, что база данных использует WAL, он открывает пул подключений, чтобы операции чтения могли выполняться одновременно с записью, и применяет синхронный режим WAL, описанный в следующем разделе. Если вы самостоятельно выполните команду PRAGMA journal_mode=WAL, фреймворк по-прежнему будет считать базу данных базой данных журнала отката. Используется одно подключение, а фиксации сохраняют режим синхронизации по умолчанию (FULL).

Ослабьте режим синхронизации

В SQLite есть возможность управлять синхронным режимом. В режиме FULL каждая фиксация выдает fsync, чтобы данные были записаны на диск. Это повышает надежность данных, но замедляет фиксацию. При использовании WAL режим NORMAL не синхронизируется при каждой фиксации и по-прежнему защищает базу данных от повреждения.

Когда вы включаете WAL через фреймворк, Android применяет синхронный режим WAL по умолчанию, который в AOSP для Android 9 и более поздних версий равен 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")

При этом фиксация может быть возвращена до того, как данные будут сохранены на диске. Если устройство выключится, например из-за сбоя питания или ядра, зафиксированные данные могут быть потеряны. Однако благодаря ведению журнала база данных не повреждена.

Если произойдет сбой только в приложении, данные все равно будут записаны на диск. Для большинства приложений эта настройка позволяет повысить эффективность без существенных затрат.

Примечание. Если в приложении несколько баз данных, используйте для них одинаковые настройки синхронизации, чтобы избежать проблем с зависимостями данных.

Управление подключениями и памятью

Каждое открытое подключение к базе данных занимает память SQLite: кеш страниц, распределитель памяти и кеш скомпилированных операторов. Страницы файла базы данных также остаются в кэше страниц ядра. Следуйте рекомендациям из этого раздела, чтобы контролировать объем памяти, не замедляя выполнение запросов.

Откройте каждую базу данных один раз.

Используйте один экземпляр SQLiteOpenHelper или RoomDatabase для каждого файла базы данных во всем процессе, например сделав его синглтоном. У каждого экземпляра есть собственный пул подключений, кеш страниц и кеш операторов. Два экземпляра, открывающие один и тот же файл, не могут использовать их совместно. В одном из тестов объединение двух помощников, открывающих один и тот же файл, сократило объем памяти кэша страниц SQLite почти вдвое, а задержку запросов p95 – более чем вдвое.

Чтобы найти дублирующиеся экземпляры, проверьте, не указан ли в файле dumpsys dbinfo один и тот же путь к базе данных в нескольких разделах Connection pool for.

Не вносите изменения в схему в обратном вызове open

Обратный вызов onOpen для SQLiteOpenHelper выполняется каждый раз, когда открывается база данных. То же самое происходит с обратным вызовом onOpen для RoomDatabase.Callback. Поместите операторы, такие как CREATE TABLE IF NOT EXISTS и CREATE INDEX IF NOT EXISTS, в onCreate и onUpgrade или в переносы Room. Не вызывайте setVersion в onOpen, потому что SQLiteOpenHelper уже задает версию после onCreate и onUpgrade. Даже если схема уже существует, эти инструкции все равно выполняются при каждом открытии, и setVersion записывает данные в файл базы данных.

Размер кеша подготовленных инструкций

Android кеширует скомпилированные инструкции для каждого подключения. По умолчанию в кеше хранится 25 операторов, но вы можете увеличить это число до MAX_SQL_CACHE_SIZE (100) с помощью setMaxSqlCacheSize. Если в базе данных выполняется более 25 отдельных операторов в горячих путях, кэш перегружается и SQLite компилирует одни и те же операторы снова и снова.

Во-первых, параметризуйте запросы. Если SQL-запрос создан с помощью объединения строк, размер кеша не имеет значения, поскольку каждая строка представляет собой новую инструкцию. Затем проверьте совпадения и несовпадения в кеше операторов в разделе POOL STATS в dumpsys meminfo. Например, в базе данных phenotype.db в примере выходных данных этого раздела есть 147 921 попадание и 89 616 промахов, что составляет 38 % промахов. Увеличение размера кэша в такой базе данных может устранить почти все промахи. Каждая инструкция в кеше занимает несколько килобайт памяти.

Чтобы увеличить размер кеша, переопределите onConfigure в подклассе SQLiteOpenHelper:

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

Если вы используете Room, вызовите тот же метод для SupportSQLiteDatabase в обратном вызове onOpen класса RoomDatabase.Callback.

Закрывать неиспользуемые базы данных, но оставлять открытыми используемые

Открытая база данных сохраняет свои подключения, кеши и дескрипторы файлов, даже когда она неактивна. В одном из тестов бездействующая база данных Room в режиме WAL, которая оставалась открытой, занимала около 678 КБ памяти кеша страниц SQLite и пять дескрипторов файлов. Если вы используете базу данных лишь время от времени, например для ежедневной синхронизации, закрывайте ее, когда она не нужна.

Чтобы безопасно закрывать неиспользуемые базы данных, следуйте этим рекомендациям:

• Room может закрывать неактивную базу данных с помощью экспериментального API setAutoCloseTimeout. Room повторно открывает базу данных при следующем доступе и снова запускает обратный вызов onOpen, поэтому поместите туда любую конфигурацию подключения.
• Не используйте методы setIdleConnectionTimeout из SQLiteOpenHelper и SQLiteDatabase.OpenParams.Builder. Они не поддерживаются на уровне API 29, поскольку закрывают подключения, не восстанавливая настройки, примененные с помощью операторов PRAGMA.
• Не закрывайте базу данных, к которой вы обращаетесь каждые несколько секунд. Если время ожидания меньше, чем интервал между запросами, база данных будет постоянно закрываться и открываться заново. При этом каждый раз будут сбрасываться кеш страниц и кеш операторов, что замедлит выполнение следующего запроса.

Учет памяти SQLite

Память SQLite бывает трудно обнаружить. Кеш страниц и кеш операторов SQLite – это нативные выделения памяти в куче. Однако в Android по умолчанию не включен ввод-вывод с отображением в памяти для SQLite, поэтому SQLite считывает файлы базы данных как обычные файлы, а содержимое файлов, которое кеширует ядро, – это страницы файлов без отображения. Они не учитываются в оценке безопасности приложения в 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	Джон Леннон	Ливерпуль, Англия
2	123	Майкл Джексон	Гэри (Индиана)
3	789	Долли Партон	Округ Севир (Теннесси)

Столбец rowid – это индекс, который сохраняет порядок вставки. Запросы, отфильтрованные по rowid, выполняются как быстрый поиск по B-дереву, а запросы, отфильтрованные по id, – как медленное сканирование таблицы.

Если вы планируете выполнять поиск по id, вы можете не хранить столбец rowid, чтобы уменьшить объем данных в хранилище и ускорить работу базы данных:

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

Теперь таблица выглядит следующим образом:

id	название	город
123	Майкл Джексон	Гэри (Индиана)
456	Джон Леннон	Ливерпуль, Англия
789	Долли Партон	Округ Севир (Теннесси)

Поскольку вам не нужно хранить столбец 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 теперь удваивается, поскольку он присутствует как в исходной таблице, так и в индексе. Поскольку вы используете индекс, стоимость дополнительного хранилища оправдана преимуществом более быстрых запросов. Однако не поддерживайте индекс, который вы не используете, чтобы не платить за хранение без повышения производительности запросов.

Как создать индексы с несколькими столбцами

Если в запросах объединяются несколько столбцов, вы можете создать многостолбцовые индексы, чтобы полностью ускорить запрос. Вы также можете использовать индекс для внешнего столбца, а для внутреннего столбца использовать линейное сканирование.

Например, рассмотрим следующий запрос:

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.

Предупреждение. Если в таблицах, использующих WITHOUT ROWID, большие первичные ключи, это может привести к снижению производительности. Подробнее о примерах использования…

Хранить малые данные в виде BLOB, а большие – в виде файла

Если вы хотите связать большие данные со строкой, например миниатюру изображения или фотографию для контакта, вы можете сохранить данные либо в столбце BLOB, либо в файле, а затем сохранить путь в столбце.

Размер файлов обычно округляется до 4 КБ. Для очень маленьких файлов, где погрешность округления значительна, эффективнее хранить их в базе данных как BLOB. SQLite минимизирует вызовы файловой системы и в некоторых случаях работает быстрее, чем базовая файловая система.

Примечание. На устройствах Android рекомендуется использовать файл для любых данных, размер которых в несколько раз превышает 4 КБ.

Как улучшить эффективность запросов

Следуйте этим рекомендациям, чтобы повысить производительность запросов в SQLite за счет минимизации времени отклика и максимизации эффективности обработки.

Примечание. Многие примеры на этой странице включают условие LIMIT. Это хорошая практика, поскольку запросы, которые могут возвращать много строк, влияют на производительность при возврате больших наборов данных. Исключение составляют запросы, которые неявно возвращают набор данных ограниченного размера. Например, поиск по полю, определенному как UNIQUE, может вернуть только ноль или одну строку.

Чтение только нужных строк

Фильтры позволяют сузить результаты поиска, указав определенные критерии, такие как диапазон дат, местоположение или название. Ограничения позволяют контролировать количество результатов, которые вы видите:

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-запросах для возврата отдельных результатов. Программный цикл примерно в 1000 раз медленнее, чем один SQL-запрос.

Используйте 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
    }
}

По возможности используйте агрегатные функции

Используйте агрегатные функции, чтобы получить агрегированные результаты без данных строк. Например, следующий код проверяет, есть ли хотя бы одна соответствующая строка:

// 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, если одна или несколько строк соответствуют условию:

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
}

Вложенные запросы вместо кода

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

Примечание. Для username создается таблица индексов, которая занимает дополнительное место в хранилище. Подробнее о том, как ускорить выполнение запросов с помощью индексов…

Теперь вы можете вставить строку и позволить SQLite проверить ограничение:

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

Примечание. Если вы зададите INTEGER PRIMARY KEY, то к этому столбцу будет применено ограничение уникальности и дополнительная таблица индексов использоваться не будет.

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. Пакетная обработка оплачивает эту стоимость один раз, что может сделать пакет операций записи на порядок или более быстрее.

Примечание. Одновременно может выполняться только одна транзакция записи. Используйте MoreExecutors.newSequentialExecutor(Executor), чтобы сериализовать записи.

Как использовать инструменты устранения неполадок

В 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

Вы также можете установить инструмент с графическим интерфейсом пользователя 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 добавьте категорию atrace database, чтобы включить операции 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 есть собственный асинхронный трек в процессе приложения, а транзакции не отслеживаются.

Нумерация подключений начинается с нуля для каждой базы данных, поэтому основные подключения всех баз данных в процессе используют дорожку SQLiteConnection-0. Если приложение использует несколько баз данных одновременно, измеряйте длительность с помощью событий START- и END- на дорожках потоков.

Рисунок 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;

Рисунок 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 – размер одной страницы базы данных в килобайтах.
• dbsz – размер всего файла базы данных в килобайтах.
• 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–16 QPR1 это значение представляет собой сумму попаданий и промахов и не отражает размер кеша.

Используйте эту статистику, чтобы решить, что нужно исправить:

• Высокий коэффициент промахов означает, что SQLite компилирует одни и те же инструкции повторно. Сначала найдите SQL-код, созданный с помощью объединения строк, и параметризуйте его. Если запросы уже параметризованы, а значение cache size для каждого подключения достигло предела в 25, увеличьте размер кеша.
• Если путь к базе данных появляется несколько раз без суффикса в стиле (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 и более поздних версиях. Они показывают, сколько операций и транзакций заняли больше двух секунд, и перечисляют последние 10 из них.
• Acquired connections и Connection waiters – используемые подключения и потоки, ожидающие подключения. Ожидающие указывают на конфликт, например на длительную транзакцию записи, которая блокирует другие записи.
• Prepared statement cache: напечатано с помощью -v. Многие записи, которые отличаются только литеральными значениями, указывают на SQL, созданный с помощью объединения строк.
• Если один и тот же путь указан под несколькими заголовками Connection pool for, значит приложение открывало один и тот же файл дважды.

Дополнительные ресурсы

Просмотр контента

• Рекомендации по повышению производительности SQLite (представления)