Оптимизация работы с SQLite под iOS



    “Think of SQLite not as a replacement for Oracle but as a replacement for fopen()”
    — About SQLite


    А также, скорее всего, под Android, BlackBerry и в сэнд-боксе браузеров для веб-приложений, но я не проверял.

    Почему может быть нужно работать с SQLite напрямую?

    Любой опытный iOS девелопер тут же упрекнёт меня за использование SQLite напрямую (вернее не напрямую, а через FmDb, но это почти всё равно что напрямую). Он скажет, что нужно использовать CoreData, т.к. оно много всяких ништяков делает автоматически, типа Undo и Redo. И в нём можно рисовать красивые схемки, которые потом приятно показывать заказчику. А в андроиде, например, есть OrmLite.

    И я соглашусь – но до той поры, пока у вас база не перевалила, скажем, за 10 таблиц по 500,000 записей в каждой. А если таблиц 52, и есть особо жирные таблицы по миллиону и больше? И базу нужно синхронизировать с сервером через третий формат, к тому же заказчику критично, будет синхронизация длиться час или пять? Если вы встречали задачи такого объёма, добро пожаловать под кат! Если не встречали – то тоже, ведь никто не застрахован от проектов с большими данными, пускай даже таких мобильных и меньше.

    Очерёдность операторов запроса

    Дело в том, что, когда нас учат работать с базой данных, нас в большинстве случаев учат работать с лучшими из энтерпрайз решений. Меня, например, в институте учили на Oracle, кого-то – на MS SQL. Но SQLite в разы проще – это, например, следует из эпиграфа к статье, взятого с официального сайта SQLite.

    Совершенно случайно я обратил внимание, что

    SELECT * FROM tablename WHERE col1 LIKE ‘%string%’ AND col2 = 123456

    работает в 3-4 раза медленнее чем

    SELECT * FROM tablename WHERE col2 = 123456 AND col1 LIKE ‘%string%’

    на таблице из, скажем, 300,000 записей. Мы просто поменяли операторы местами, а как изменился результат!

    В учебниках по базам данных, скорее всего, даже не акцентируют внимания на таких особенностях, и правильно делают – во всех энтерпрайз решениях есть оптимизаторы запросов. К примеру, на MS SQL Server 2008 Web Edition на тех же данных и тех же запросах разницы никакой.

    Но на SQLite есть. Это нужно помнить. В мире SQLite более “простые” операции всегда должны идти левее более “сложных”.

    Базу SQLite тоже можно и нужно индексировать


    Думая об SQLite как об альтернативе fopen, базе без хранимых процедур, семафоров и юзеров, забываешь, что она, как и любая нормальная база, поддерживает индексы. Про них столько написано, что не стоит заострять внимания на синтаксических особенностях – просто запомните, что как только размер базы превысит 50,000 строк – её нужно проиндексировать. А при использовании сложных запросов – и раньше.

    Позволю себе только маленькое замечание – индексирование лучше всего проводить после того, как написаны основные запросы, на основе их анализа. При проектировании БД, когда девелопер ещё не знает назубок всю бизнес-логику приложения, он может заблуждаться относительно того, по какому полю будет чаще всего происходить поиск/выборка. Однако имея SQL-запросы перед глазами, написать правильный индекс ничего не стоит.

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

    На одном из проектов нужно было каждый раз на старте приложения просить пользователя выбрать автомобиль, с которым он хочет работать. Для того, чтобы составить полное описание машины, нужно было обратиться к нескольким таблицам:
    • Год выпуска
    • Производитель
    • Модель
    • Комплектация
    • Некоторые иные технические характеристики, которые заказчик пожелал показывать при выборе машины. Например, MPG (miles per gallon, аналог наших километров на литр).

    Делая запрос по пяти таблицам и составляя список машин, мы затормозили айфон до отклика 6-8 секунд на каждом пикере. Выхода два – можно при первом запуске (у нас – при синхронизации) либо составлять представление со всеми возможными данными, либо, если удобнее, сохранять объекты с данными прямо на жёсткий диск. Тоже один раз, в удобный момент, когда пользователь знает о задержке и готов подождать.

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

    SQLite – однопоточная база

    Не связано напрямую с оптимизацией, но об этом также не стоит забывать. Обращение к SQLite базе из двух потоков одновременно неизбежно вызовет краш. Выхода два:
    • Синхронизируйте обращения при помощи директивы @synchronized. Это если поздно менять архитектуру, как было у меня;
    • Если задача закладывается на этапе проектирования, завести менеджер запросов на основе NSOperationQueue. Он страхует от ошибок автоматически, а то, что делается автоматически, часто делается без ошибок.
    Вообще, обращаться к базе всегда лучше из второстепенных потоков, даже если UI-ный поток ничего не делает, кроме прокрутки индикатора ожидания. Время на написание методов делегата и асинхронизацию бизнес-логики с лихвой окупится положительным user experience.

    Вывод

    Всё, что я перечислил выше, было лишь частными случаями. Нету универсального таска оптимизации, который можно заэстимировать в 1.5 дней или 10% от общего времени проекта. Этим приходится заниматься по мере необходимости. Главное правило, которое всегда помогало мне правильно выбрать путь оптимизации – время, затраченное на выборку, не должно увеличиваться с ростом размера базы кроме особых случаев. Оно должно оставаться примерно одинаковым как на ста записях, так и на ста тысячах.

    И ещё одно правило, любимая фраза моего начальника – не оптимизируйте то, что не нуждается в оптимизации. Зачастую юзеру не важно, будет ваш код выполняться одну миллисекунду или сто, а заказчику важно, задержали вы проект на день или сдали вовремя.

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 34

      +6
      >> В мире SQLite более “простые” операции всегда должны идти левее более “сложных”.
      Спасибо. Это небольшое, но очень важное знание на будущее. Там, где теория совсем расходится с конкретной реализацией. А за всем и не уследишь.
        +2
        Я думаю было бы полезнее, если бы автор привел больше примеров оптимизации взаимодействия с SQLite. Не жадничайте опытом — больше примеров!
          +4
          Я вот, например, знаю про команду VACUUM, которая тоже бывает полезна при работе с SQLite.
            0
            Sqlite в Chrome не поддерживает VACUUM.
            0
            Мне совсем не сложно, но остальные примеры более относятся ко всем базам данных, нежели исключительно к SQLite. Вот, например, два:

            1) При большой количестве инсертов (когда речь идёт на тысячи и десятки тысяч) используйте «BEGIN TRANSACTION» и «COMMIT». Оптимальную периодичность открытия-закрытия транзакции трудно посчитать, но мы эмпирически вывели число 100, т.е. как-то так:

            if(!(insert_counter % 100))
            {
            sqlite3_exec(db, "COMMIT", NULL, NULL, &errmsg);
            sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &errmsg);
            }


            2) Как ни смешно это звучит, но большинство непроизводительного кода часто бывает по той причине, что для выполнения одной операции использовалось несколько обращений к базе.

            Например, нужно вывести картинку машины заданного цвета. Первый запрос ищет машину, второй — её ID в таблице цветов, и третий непосредственно ссылку на PNG-картинку. Объединив все запросы с один, мы повышаем производительность в десятки раз.

            Не хочется смотреть по Blame кто это написал, т.к. у самого бывали подобные косяки — зачастую в 9-м часу вечера, когда релиз должен был быть вчера, не так хорошо соображаешь.

            Но опять же, это относится ко всем базам данных.
            +1
            Это правило, кстати, хорошо работает и в языках программирования при ряде OR условий.
              0
              Да, я в курсе + есть шанс вывалиться из короткозамкнутого условия по простой проверке, не выполняя сложных операций.
            +1
            работать с SQLite напрямую бывает еще удобно когда одно и тоже приложение пишется под две и более платформы (андроид и iOS например). тогда можно использовать одни и теже запросы к базе.

            еще SQLite не работает корректно с юникод строками (в частности, русскими символами) и тогда его приходится патчить ICU расширением.
              +1
              Спасибо, теперь понятно почему оно меня так сегодня ругало при записи в базу.
                0
                все запросы перевожу в utf8 — всё корректно.
                  0
                  А у меня помню были проблемы с немецким языком. Только, к сожалению, не помню как решили, было больше двух лет назад…
                    0
                    добавление выполняется без проблем.
                    у нас были некорректно выполнялся select like. регистронезависимый поиск не работал с русским языком. тогда и пришлось патчить
                  +3
                  Кстати, как и что оптимизировать в SQLite становится понятно, покопавшись у него «внутрях», благо код очень компактный и понятный. Например, я, в свое время, узнал, что SQL-запрос компилируется в обыкновенную процедурную программу, которая потом исполняется на простенькой виртуальной машине. А изучение работы механизма пейджинга тоже дает пару идей, где можно искать проблемы с производительностью.
                    +3
                    Вообще основания для использования FmDb вместо Core Data, которые привел автор, ясно дают понять, что автор практически не знаком с принципами работы и методами работы с Core Data с большими данными.
                    Часто, простота освоения и использования Core Data останавливают многих на более глубокое изучение этой технологии. Как следсвите, при возникновении не очень тривиальных задач,
                    эти многие начинают искать «костыли» или альтернативные пути, вместо того чтоб более детально ознакомится с CoreData.

                    • UFO just landed and posted this here
                      0
                      Полностью поддерживаю! Первое время сам удивлялся и почему c CoreData все так тормозит, причем даже на незначительных объемах данных. Как только разобрался все встало на свои места.
                        0
                        Может и не знаком так хорошо как Вы, но в любом случае, согласитесь, CoreData будет работать медленнее по определению ORM. Не в разы, не на порядки, но медленнее.

                        К тому же, как правильно заметил ara89 выше, есть ещё один сильный довод — кросс-платформенность.
                          +1
                          В некоторых случаях не медленее, а даже быстрее, благодаря fault'ам. И намного безопасней и экономичней с точки зрения управления памятью, можно держать объекты из разных мест, благодаря тому-же faulting и uniquing. Например вы сделали два запроса к пересекающимся данным из разных мест — держите двойное раходование памяти, в коре-дата естественно этого нет, и более того, часть данных уже загуржена и доступна (которая находится в пересечении), остальные же можно догрузить лениво, если нужно.
                          Конечно это можно и напрямую сделать, но получится намного сложнее, да и в итоге получится тот-же Core Data. Уж в очень экзотических случаях можно использовать гибридный подход: выполнять SQL запросы к хранилищу Core Data.

                          какая-же кросс-платформенность, если будет использоваться тот-же FmDb. Кросс-платформенность в данном случае относительная, да и смысл ее, если писать нативное приложение.
                          Я бы сказал что в некоторых случаях для обеспечения одной модели для мака и iOS лучше использовать Core Data для обеспечения той-же кросс-платформенности.
                            0
                            >> Уж в очень экзотических случаях можно использовать гибридный подход: выполнять SQL запросы к хранилищу Core Data.

                            Вот этого, на сколько я знаю, не получится сделать, если речь идет не об использовании предикатов.
                              0
                              это как одно другому то мешать может? у sql свои предикаты, а у core data свои. Они даже при желании не пересекаются.
                                0
                                Имел ввиду особый формат хранения данных CoreData в SQLLite базе. Технически запрос конечно можно выполнить.
                        +1
                        >> Обращение к SQLite базе из двух потоков одновременно неизбежно вызовет краш

                        На самом деле это не так. При открытии базы возможно использовать флаг SQLITE_OPEN_FULLMUTEX, при условии, что данная функциональность не была отключена на этапе компиляции.
                        www.sqlite.org/c3ref/open.html
                          0
                          Спасибо, ценное замечание! Почитал документацию — оказывается, SQLite может работать в трёх режимах

                          Но всё же на stackoverflow более популярны советы по NSOperationQueue и @synchronized, т.е. организовать строго последовательный доступ средствами языка, а не параллельный — базой, т.к. человек пишет, что при FULLMUTEX и большой количестве параллельных инсертов у него пропадают записи.
                          0
                          Сравните еще поиск по %string и gnirts%
                            +1
                            Не знание CoreData на должном уровне, вовсе не говорит о том что этой очередной костыль системы, что выходит из вашей статьи. Ваши выводы не объективны и однобоки, и несут лишь синтетическую нагрузку. Не буду повторяться, скажу лишь что я полностью согласен с «rule» комментарии которого выше.

                            На мой взгляд, отказ от использования CoreData может быть обусловлен только нехваткой времени на его изучение.
                              0
                              Ааа, знакомый тролль :) А я милого узнаю по походке…
                              0
                              В свое время начали использовать fmdb, тысячу раз пожалели, проблем с ним набролись достаточно.
                                0
                                например?
                                –1
                                Ваша статья вызывает недоумение.

                                Sqlite замечательно использует индексы. Отсутствие хранимых процедур не влияет на их работу.

                                Условие LIKE '%foo%' не индексируется ни в одной БД, включая Oracle. Поэтому оно не может и не должно влиять на подбор индекса.

                                Работать из двух потоков можно, используя два соединения к БД. Использовать одно соединение можно при условии, то sqlite работает в режиме сериализации.

                                Кэширование данных можно выполнять по-разному. Можно и БД в памяти использовать.

                                Вообщем, не очень понятно, что хотели сказать.
                                  0
                                  Столько времени прошло… но отвечу

                                  — SQLite нормально использует индексы, я с этим не спорю;
                                  — с какого перепуга LIKE вообще может индексироваться, это бред, и я такого не писал. Я писал о том, что в SQLite нет оптимизатора запросов, в отличие от Oracle и SQL Server, поэтому нужно думать, в каком порядке ставить условия;
                                  — в настройках по умолчанию SQLite однопоточна, мьютексы и тем более Serialized ведут к очень существенному провису пефоманса.
                                  — про какое кеширование идет речь? Если вам нужно просто закешировать данные, так понятно что тут вообще плоскопараллельно, как и что делать. В какой памяти хранить, если речь шла о сотнях мегабайт базы?

                                  В общем, непонятно что вы хотели сказать.
                                  0
                                  Не обижайтесь, расшифрую подробнее.

                                  Изначально делается посылка, что sqlite это «недобаза» из-за отсутствия встроенного языка, прав доступа, и пр. Этот вывод расширяется на «плохую» работу оптимизатора запросов. В sqlite безусловно имеется оптимизатор запросов, не такой мощный как в Oracle, но вполне приличный. В каждой новой версии sqlite он улучшается.

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

                                  Оператор LIKE может быть использован в индексе при записи операнда в виде 'ABC%'.

                                  Насчет однопоточности: не знаю каким образом встроена sqlite на вашей платформе, но обычно всегда можно открыть N независимых соединений к БД. Блокировка БД будет возникать только при обновлении (т.е. sqlite работает по принципу «многие читают-один пишет») и ее можно обойти. Короче, просто открывайте дополнительные соединения к БД.

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

                                    0
                                    Спасибо за развернутый ответ, изменил свое мнение о вашей карме :)

                                    Про первое отвечу — я люблю SQLite, но оптимизатора запросов там либо нет, либо он слишком простой. В любом случае, лучше знать эту особенность чем не знать.

                                    Про второе — спасибо за пример, но все же при настройках по умолчанию в iOS нельзя одновременно даже одновременно читать. По крайней мере так было в 2011 году, на момент написании статьи (ибо именно из-за этого команда убила пол-вечера).

                                    Ну и про БД в памяти — БД занимала около 700 мегабайт (энтерпрайз приложение), ее невозможно было хранить в памяти.

                                    Но все равно отдельное уважение за развернутый комментарий!
                                      0
                                      Работа оптимизатора описана здесь.

                                      Если под iOS нельзя читать двоим это странно. Зачем такое ограничение…

                                  Only users with full accounts can post comments. Log in, please.