Перевод статьи разработчика Android Team Криса Крайка (Chris Craik) “Large Database Queries on Android”.

Статья упомянута в офф. документации Google в разделе Pagging Library. И является вполне программной в смысле видения Google дальнейшего развития работы с SQLite в Android. Это новое видение, вполне не ново, т.к. вопрос (скорость и удобство работы с SQLite, загрузка больших порций данных) стоял давно и те или иные сторонние библиотеки решали этот вопрос. Но представив свою концепцию архитектуры приложений Android Architecture Components разработчики Android Team не обошли внимание проблемы SQLite. Статья обзорная, она говорит о проблемах работы с большими выборками данных, при использовании SQLite, и описывает пути решения этих проблем.

Возможности

SQLite - отличный способ хранить данные в Android-системах, но так сложилось, что загрузка этих наборов данных в UI довольно нетривиальна, и может привести к проблемам производительности. Перед запуском новой библиотеки Paging Library мы исследовали существующие подходы к подгрузке данных и особенно потенциальные ловушки использования SQLiteCursor.

В этой статье мы рассмотрим его проблемы и поймем почему мы склоняемся к использованию небольших запросов с помощью Room и Pagging Library в Architecture Android Components.

SQLiteCursor и CursorAdapter

SQLiteCursor это возвращаемый тип при запросе к базе данных SQLite в Android. Он позволяет получить большой объем данных за фиксированную “стоимость”. Первое чтение данных инициализирует CursorWindow, буфер строк с дефолтным размером 2MB, который содержит контент из базы данных. SQLiteCursor обновляет этот буфер полностью каждый раз, когда вы делаете запрос на строку, которая еще не представлена в выборке. Таким образом SQLiteCursor реализует постраничную загрузку данных с фиксированным размером страницы.

CursorAdapter был доступен начиная с API 1, и предоставил простой способ получения данных из курсора (обычно SQLiteCursor) для элементов ListView. Хотя он отлично справляется с этой функцией, он обращается к базе данных в пользовательском потоке всякий раз, когда требуется загрузка новых данных. Это само по себе неприемлемо для современных и отзывчивых приложений. Тогда мы можем спросить, не может ли у нас быть адаптера, основанного на курсоре, но который бы загружал данные в фоновом потоке? В конце концов у SQLiteCursor есть встроенная постраничная загрузка данных.

Проблемы с постраничной загрузкой данных с использованием SQLiteCursor

Большинство проблем с постраничной загрузкой с использованием SQLiteCursor происходит из-за поведения, которое нельзя предсказать, поскольку он использует свое окно (CursorWindow) для разбиения данных на страницы. Ниже приведен список проблем, с которыми мы столкнулись, когда экспериментировали с встроенной постраничной загрузкой через SQLiteCursor, что привело нас к созданию Paging Library:

• SQLiteCursor не поддерживает транзакции базы данных

Когда я начал исследовать постраничную загрузку данных, я был не опытен в SQLite и особенно в Cursor в Android. Я просто предположил, что SQLiteCursor, после загрузки окна данных, мог бы приостановить запрос, до того момента, когда ему понадобится следующее окно. Таким образом, доступ к десятому окну будет таким же эффективным, как и к первому. Но это не так. Каждый раз, когда читается новое окно, запрос перезапускается с начала и пропускает строки, которые не запрашиваются для этого окна. Каждый раз полный проход по данным. Это от того, что SQLiteCursor не может останавливать и возобновлять запросы.

Это похоже на доступ к элементам с 1000-го по 1500-й в связном списке (LinkedList) - вам нужно проходить большое количество элементов, перед тем как загрузить следующую страницу данных. Когда вы загружаете, каждое последующее окно должно пропускать все больше и больше данных, прежде чем начнет загружать необходимые для него. Это замедляет загрузку. Это эквивалентно использованию ключевого слова SQL OFFSET для пропуска данных, которое не является самым эффективным способом заполнения выборки данными, и этого нельзя избежать, опираясь на постраничную загрузку данных с помощью SQLiteCursor. Вы можете посмотреть, как SQLiteCursor подгружает данные в новое окно здесь.

• SQLiteCursor.getCount() обязателен, и проходит по всему запросу

Перед чтением самой первой строки, SQLiteCursor вызыввает getCount() для проверки границ. Поскольку SQLite вынужден сканировать весь результат запроса для его подсчета (опять же, как в связном списке), это может привести к значительным накладным расходам. Если вы постепенно подгружаете большой запрос в UI, в ответ на прокрутку пользователя, вам может не понадобится знать весь его (запроса) размер, поэтому подсчет добавляет ненужную начальную работу.

• SQLiteCursor.getCount() всегда загружает первое окно данных

Как часть подсчета количества, при сканировании полученной выборки, SQLiteCursor заранее заполняет свое окно с позиции 0, в предположении, что первые элементы в запросе будут нужны.

Он предварительно загружает эти элементы, чтобы он мог знать заранее, сколько приблизительно строк попадает в окно (подробнее об этом ниже). Эта рискованная загрузка имеет смысл, если данные предоставляются с начала запроса, но позиция, восстанавливаемая из сохраненного состояния, может быть намного дальше по списку, где начальное окно нерелевантно. Если вы хотите предоставить данные из третьего окна, вы вынуждены сначала загрузить и выбросить 2 MB данных первого окна. Код такого поведения смотрите здесь.

• SQLiteCursor может загружать данные, которые вы не запрашивали

Cursor.moveToPosition() гарантирует, что запрашиваемая строка находится в окне, но SQLiteCursor не начинает наполняться данными, начиная с запрошенной строки. Поскольку SQLiteCursor не предполагает, что приложение читает наперед, он начинает заполнять окно, когда находится на расстоянии около одной трети (⅓) от целевой позиции. Это означает, что CursorAdapter перескакивает через несколько строк после того, как загрузка окна не вызывает загрузку следующего окна. Это также означает, что каждым 2 MB загруженных данных предшествует загрузка 650 KB или более данных, которые уже были получены. Смотрите код и объяснения такого поведения здесь.

• Позиция с которой SQLiteCursor начинает загрузку может быть непредсказуема

Когда SQLiteCursor пытается загрузить целевую позицию, он пытается начать загрузку с ⅓ от размера окна от требуемой позиции. Это означает то, что он должен предполагать какое количество строк помещается в окно. Для этого используется число строк первого загруженного окна. К сожалению это означает, что если ваши строки имеют неодинаковый размер (например, если в базе данных хранятся комментарии пользователей в виде String произвольной длины), его предположение может оказаться неверным. SQLiteCursor может недооценить расстояние до целевой позиции, заполняя окно данными, и заполнить его еще до достижения требуемой строки, - затем, отменить все, и начать заполнять снова. Если, например, вы двигаетесь через большой запрос и получаете строку, которая требует перезапустить окно, тогда возможно загрузка захватит лишь незначительное число новых строк. Код очищения окна и его перезапуска здесь.

• Курсоры требуют закрытия

Курсор должен быть закрыт с помощью метода close(). Поэтому, где бы ни хранились курсоры, всегда должен быть метод, который закрывает их, когда они больше не нужны. CursorAdapter особенно не помогает в этом, перекладывая ответственность на разработчика. Для хранения и переиспользования курсора разработчику приходится писать код для обработки таких событий, как, например, перезапуск Activity.

• SQLiteCursor не знает, что данные изменились

SQLiteCursor не отслеживает, что данные базы данных изменились после того как первое окно прочитано (и произведен первый подсчет). Это означает, что если некоторые элементы базы были удалены, или новые добавлены, то размер кэша SQLiteCursor будет неверен - проблема как для проверки размеров запроса, так и для того, чтобы в итоге загруженные данные были согласованы. И мы можем получить исключение, если было обращение к строке, которая не существует, или получить непоследовательные данные в некоторых случаях. Например, если вы уже загрузили строку N, а новый элемент вставлен в позицию 0, и затем вы пытаетесь загрузить строку N+1, вы загрузите строку N второй раз.

Избегая проблем

Вышеперечисленные проблемы говорят нам, что SQLiteCursor не масштабируется для запросов с тысячами результатов. К счастью, у всех этих проблем есть одно простейшее решение: небольшие запросы. Запросы, которые вписываются в один CursorWindow, избегают всех проблем, поэтому мы так тщательно поддерживаем их в Paging Library и Room. Обычно размер страницы настраивается от десяти до двадцати элементов и запрашивается сразу несколько элементов.

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

Если вы полагаетесь на внутреннюю постраничную загрузку через SQLiteCursor и ленивую загрузку гораздо больших наборов результатов, мы рекомендуем вам использовать другой подход. Тогда используйте Paging Library совместно с Room Persistence Library, или используйте кастомную реализацию, где вы самостоятельно организуете постраничную загрузку. Но тогда следите за тем, чтобы результаты ваших запросов были достаточно малы, чтобы вписаться в CursorWindow.

Чтобы организовать постраничную загрузку большого SQL-запроса небольшими запросами в Room вы можете использовать новую Paging Library. Вам надо изменить:

@Dao
interface UserDao {
    // обычный запрос списка — падает, если результат очень большой
    @Query(“SELECT * FROM user ORDER BY mAge DESC”)
    LiveData<List<User>> loadUsersByAgeDesc();
}

на

@Dao
interface UserDao {
    // запрос с постраничной загрузкой — обрабатывает большие запросы
    @Query(“SELECT * FROM user ORDER BY mAge DESC”)
    LivePagedListProvider<Integer, User> loadUsersByAgeDesc();
}

Затем предоставляются параметры постраничной загрузки, чтобы получить объект LiveData<PagedList>, который содержит результаты больших выборок:

LiveData<PagedList<User>> users = userDao.usersByLastName()
        .create(/*начальная позиция загрузки*/ 0, /*размер страницы*/ 20);

В приведенном выше коде, мы использовали LiveData-версию результатов запросов с постраничной загрузкой, которая также будет обновлять любые Observers (наблюдатели), подписанные на нее, когда меняются данные в базе данных. Чтобы узнать больше о постраничной загрузке из SQLite с использованием architecture components, см. обзор Paging Library и пример её использования на GitHub.

Платформа Android

Сегодня мы работаем над улучшением поведения SQLiteCursor в будущих версиях Android. Например, вероятно, что размер CursorWindow и размер ⅓ от окна должны регулироваться, поскольку современные шаблоны проектирования библиотек выглядят иначе, чем во времена проектирования SQLiteCursor и CursorAdapter. Когда мы внесем изменения, мы обязательно обновим Room Persistence Library для использования этих новых опций, чтобы максимально отвечать современным запросам Android-сообщества.

end.

Bonus

Комментарий Vasiliy Zukanov к статье:

Я не понял следующее высказывание о результатах запроса помещаемых в окно:

“Запросы, которые вписываются в один CursorWindow, избегают всех проблем…”

Хотя я согласен с тем, что постраничная загрузка запросов это хорошо, но зачем ограничивать их размером окна?

Например (в тексте ниже термин “запрос” относится к запросу к БД, а затем к считыванию набора результатов в память):

Предположим, что учитывая мою схему и контент БД, курсор окна может содержать около 15 результатов. Если я сделаю запрос к БД на 15 результатов (это примерно то число, которое вы предлагаете), тогда для одного пользователя в RecyclerView может потребоваться от 4 или более запросов, которые необходимо выполнить (в зависимости от расположения каждого элемента, и т.д.). (NB от переводчика: скорее всего имеется в виду, что за один раз на экране помещается около 4-х элементов списка RecyclerView и, следовательно, пользователю необходимо 4 раза проскроллить список, чтобы увидеть все элементы)

Теперь, несмотря на то, что запросы выполняются в фоновом потоке, все равно их будет 4, и вполне возможно, что скорость их выполнения будет медленнее, чем скроллинг. В таком случае мы получаем лаги, притормаживание UI?

Эта ситуация может ухудшиться если база данных заблокирована в режиме EXCLUSIVE (который включен для Android по-умолчанию) - потоки могут не получать доступ к чтению из базы данных в течение относительно продолжительного времени.

Я провел серию экспериментов некоторое время назад и результаты показали, что оптимальный размер запроса в 10 и более раз больше, чем помещается элементов на страницу. Таким образом, если 5 элементов помещается на одну страницу, было бы лучше запросить 50 элементов, а затем, когда пользователь прошел 25, еще 50. Эта схема может быть подкорректирована с учетом скорости движения, но в большинстве случаев это будет излишним.

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

Учитывая эти наблюдения, ваше предложение делать запрос размером 10-20 элементов приведет к увеличению времени примерно в 4 раза для запроса в 40-80 результатов.

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

У вас есть результаты тестов, которые показывают преимущество размера запроса равного размеру окна курсора?

Ответ автора статьи:

Предложение ограничить запрос размером одного CursorWindow заключается в том, чтобы избежать нескольких точек неэффективности, которые заключаются в последовательном чтении. Например, это предвыборка данных в размере 1/3 окна, или необходимость обязательного подсчета запроса.

Если вы запустили запрос размером четыре CursorWindow, реализация SQLiteCursor будет эффективно выполнять не менее четырех запросов, каждый раз, когда он заполняет свое окно. Но вероятно, вы получите больше запросов, чем могли, т.к. если первое окно загружает 1-15, то второе 10-25 (см. SQLiteCursor может загружать данные, которые вы не запрашивали выше).

Когда вы говорите “в 10 и более раз больше, чем помещается элементов на страницу”, вы имеете в виду помещается в 2 MB СursorWindow? Если так, то я могу попробовать воспроизвести это, но помните, что происходит загрузка 20 MB из базы данных сразу, и (возможно) конвертируется в POJO-объекты.

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

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

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

Ответ Vasiliy Zukanov:

Прошу прощения, я немного ошибся в терминологии: мои эксперименты тогда показали, что оптимальное количество элементов для извлечения из БД в одном запросе составляет не менее чем в 10 раз больше элементов, чем помещается на экране.

Итак, если 10 элементов помещается на экране в RecyclerView, лучше будет загрузить не менее 100 элементов за один запрос. Результат был получен в самостоятельных экспериментах, поэтому результат - всего лишь отражение моего опыта.

Запрос в 20 MB действительно большой, но мне кажется, что лучше сразу прочитать 20 MB из базы данных, чем делать 10 запросов, при каждом прыжке (скроллинге), считывая по 2 MB.

Я постараюсь написать несколько простых тестов, чтобы проверить эту теорию и отчитаться.

см. пост автора коммента в его блоге, где он выполняет обещание.