База SQLite и основы SQL. Часть 1
08-11-2021Время чтения ~ 7 мин.SQL 2953
Поговорим немного о базах данных. Зачем вообще они нужны и какие у них задачи?
Очевидно, что база — это хранилище данных. Например компьютерная программа может сохранять свои настройки в простом ini-файле. Но, что делать, если нужно иметь доступ к большим данным? Например тексты, или справочную информацию (например список городов и улиц)? Программисты часто изобретают свой «велосипед» — например используют какой-то свой формат файла. Сложность здесь в том, что такой подход хорошо работает для однопользовательского приложения и когда данные уже готовы для использования, но не для их изменения.
То есть когда стоит задача обеспечить одновременный доступ для нескольких пользователей или когда нужно делать сложные манипуляции, то намного эффективней использовать именно базы данных.
Баз данных довольно много. Если говорить о самых известных (бесплатных и открытых), то стоит отметить MySQL (и клон MariaDB), PostgreSQL и SQLite. MySQL наиболее распространена в вебе, поскольку почти каждая CMS работает именно на ней. PostgreSQL чаще используется для больших и сложных проектов. А вот SQLite не так часто используется для сайтов, но зато повсеместно используется в ПО: начиная от компьютерных программ до смартфонов.
Главное отличие SQLite от других баз (точнее СУБД — система управления базой данных) в том, что для неё не требуется отдельный сервер, а сама база представляет собой всего лишь один файл.
Чтобы использовать MySQL или PostgreSQL (и другие) вначале нужно установить управляющую программу, потом запустить сервер, потом в режиме администрирования создать непосредственно базу и только после этого можно с ней что-то делать. Это достаточно сложно для обычного пользователя, не говоря уже о том, что такие СУБД занимают немало места на диске.
Возможно вы заметили, что некоторые хостинги ограничивают максимальное количество баз MySQL. Это не просто так.
В этом плане SQLite разительно отличается. SQLite — это встраиваемая в приложение СУБД. По сути это всего один файл dll-библиотеки размером около 1Мб. То есть база встраивается прямо в приложение и не зависит ни от чего другого. Что касается Интернет-серверов, то SQLite давно уже входит в комплект PHP и обычно работает из «коробки», где баз может быть сколько угодно.
Ещё одной особенностью SQLite является то, что она не требует настроек. То есть не нужно задавать права доступа или выполнять какие-то специфичные настройки, как это часто происходит в других СУБД. Здесь же Zero-Configuration.
При всей своей компактности SQLite имеет впечатляющие возможности. В первую очередь, конечно же, это полная поддержка команд SQL. То есть работать с SQLite можно как с любой другой реляционной базой данных. Синтаксис SQL в разных СУБД может отличаться, но в целом SQLite хорошо соответствует стандарту ANSI SQL-92.
Хотя база SQLite представляет собой обычный файл, скорость работы с ним выше, чем обычными функциями чтения и записи файлов. При этом за счёт упаковки, размер файла может быть меньше, чем «сырые» данные. Важно ещё то, что SQLite гарантирует сохранность данных. В этом плане она считается очень надёжной, гарантирующей сохранность данных даже в режиме «катаклизма» — сбой системы или отключения питания. Конечно такой режим работы сказывается на скорости работы с данными. Например вставка или обновление данных потребует переписать файл, а значит когда происходит обновление файла, то база может оказаться недоступна другим пользователям.
Различные тесты показывают, что SQLite достаточно просто справляется с 20-30 одновременными изменениями (INSERT) в секунду. На современных серверах, где используются SSD-диски скорость намного выше — примерно 900..1400 (статья на Хабре). Понятно, что режим чтения (SELECT) работать будет вообще «мгновенно». То есть SQLite показывает результаты как минимум на уровне «больших» баз: MySQL и PostgreSQL.
Возникает вопрос о максимальном размере файла базы. Поскольку это один файл, то получается, что здесь действуют не только ограничения операционной системы, но и то, как SQLite может обрабатывать большие объёмы данных. На сайте SQLite утверждается, что база может быть до 281 Тбайт общего размера и 1 Гб в одной строке. Это лимиты для очень больших данных (big data). Вряд ли в нашей практике потребуется такой объём. Например этот мой сайт имеет примерно 1000 записей и это занимает 5Мб текстового sql-дампа. Даже если предположить, что он будет столько же занимать в базе (на самом деле меньше), то подобный файл с 10-кратным запасом (на 10 тыс. записей) будет занимать меньше 50Мб на диске. Согласитесь, очень скромно. :-)
Где может применяться SQLite ?
Очевидное применение — это компьютерные программы, смартфоны, а также для всех тех, кто работает с большими данными: вместо таблиц Экселя, можно использовать SQLite и получать все необходимые выборки.
Если в плане WEB, то это могут быть обычные сайты/блоги, где предполагается не очень много записей (скажем до 100 тыс. страниц). Здесь вопрос будет скорее в удобстве администрировании: с большим файлом сложнее работать.
Также SQLite может использоваться для хранения небольших настроек сайта. Поскольку здесь не требуется сервер, то базу можно организовать хоть для каждой php-страницы сайта. Лично я с этим столкнулся в Albireo, когда обилие настроек уже сложно хранить в конфигурационных файлах. Изначально Альбирео не имел админки, но после того, как она появилась, стал вопрос не только хранения, но и более дружественного изменения настроек. Поэтому я и добавил API для работы с SQLite (и несколько модулей в админке).
Другой вариант использования, о котором я пока ещё думаю — это кеширование. На примере MaxSite CMS.
Чтобы уменьшить количество запросов к MySQL, используется файловый кэш — по сути серилизованный массив готовых данных. Это позволяет резко сократить количество запросов к базе до единичных. Но есть крупные сайты, где такой файл кэша получается достаточно большим (например много рубрик и их данных). Таким образом работа с файлами напрямую всегда имеет риск повреждения на сервере. Например когда происходит одновременная запись в файл. В MaxSite CMS проверяет корректность кэша, но всё равно это потенциальная проблема.
Похожая ситуация случается с плагином Ушки (пользователи системы знают о чём речь). Там тоже файл и если он становится большим, то есть риск повреждения.
В таких случаях можно попробовать заменить файловый кэш на базу SQLite. В теории это позволит вообще не задумываться о размере кэша, а гарантированная целостность не думать о сбоях. Этот вопрос я ещё изучаю, возможно в будущих версиях MaxSite CMS я их реализую.
Где взять SQLite?
Вообще с официального сайта. Если же работать через PHP, то скорее всего база уже в комплекте. Но, поскольку SQLite не требует установки, то удобней использовать не её командный файл, а стороннюю программу-оболочку. Например я использую SQLiteStudio (можно менять sqlite3.dll на более свежие).
Сейчас мы поговорили об особенностях SQLite, а дальше я расскажу об основах SQL. Поэтому пока будет достаточно той же SQLiteStudio. Об использовании SQLite в PHP речь пойдёт в самом конце.
Наверное стоит отметить, что базами данных профессионально я не занимаюсь уже очень давно. Последние десять лет мне вполне хватало MySQL и простых SQL-запросов в рамках MaxSite CMS (и других систем). Сейчас у меня есть некоторые задумки по Albireo, поэтому я решил освежить знания по SQL и разобраться в тонкостях SQLite. Так и появился этот цикл статей.
Но с базами данных я работаю очень давно. Первая разработка сайта (форум) была примерно двадцать лет назад. Тогда не каждый хостинг даже предлагал возможность работы с MySQL. Но профессионально работать с базами я стал через несколько лет, когда появился заказ на программу, где нужно было хранить данные по улицам, городам и т.д. Писал я тогда на Delphi в базе Paradox и это было классно, поскольку в Delphi всё это было интегрировано, а мастер настроек позволял генерировать готовые формы и таблицы без написания единой строчки кода.
SQL я тогда изучал по книжкам, где он ещё назывался сиквел, а не эс-ку-эль, как это принято сейчас.
Написание и поддержка БД-программ позволило накопить ценный опыт. Например теперь я знаю, что текст запроса лучше держать в отдельном файле, а не жёстко вшивать в программу. :-) Потом были попытки использовать MySQL, правда тогда он жутко тормозил и сервер постоянно «отваливался»: требовалось не сколько программировать, сколько следить за доступностью самой СУБД. Перед тем, как я ушёл во фриланс, часть программ мы переводили в Microsoft Access. Недавно меня попросили помочь с базой Access и как оказалось, принципиально в ней мало что изменилось. :-)
Подытожу. С базами работать не так сложно как это может показаться. Да там есть свои заморочки, но в целом это довольно примитивные sql-запросы. Сложности возникают если попытаться охватить все нюансы и тонкости. Многие учебники по SQL пытаются вывалить на читателя всю возможную информацию и разобраться в ней будет сложно даже опытному специалисту. Возможности SQL достаточно большие, но большая из них часть скорее всего не будет реально востребована. Стоит вспомнить принцип Парето: 80% пользователей будут использовать только 20% возможностей.
Поэтому я постарался оставить только то что, будет реально востребовано и умалчиваю о каких-то «экстра» возможностях (может я и сам их не знаю). Статьи составлены таким образом, чтобы последовательно ознакомить с основами, а потом немного увеличивать сложность.
Для изучающих SQL я рекомендую повторять все примеры запросов в SQLiteStudio, причём не copy/paste, а старайтесь вручную набирать код. Такой способ запоминания намного эффективней. :-)