Сравнение реляционных систем управления базами данных SQLite, MySQL и PostgreSQL

Введение

Реляционная модель данных, которая организует данные в таблицы строк и столбцов, преобладает в средствах управления базами данных. Сегодня существуют и другие модели данных, включая NoSQL и NewSQL, но реляционные системы управления базами данных (RDBMSs) остаются доминирующими для хранения и управления данными по всему миру.

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

A Bit About Database Management Systems

Базы данных – это логически моделированные кластеры информации, или данные. Система управления базами данных (DBMS), с другой стороны, является компьютерной программой, которая взаимодействует с базой данных. DBMS позволяет контролировать доступ к базе данных, записывать данные, выполнять запросы и выполнять любые другие задачи, связанные с управлением базами данных.

Хотя системы управления базами данных часто называются “базами данных”, эти два термина не являются взаимозаменяемыми. Базой данных может быть любой набор данных, а не только тот, который хранится на компьютере. В отличие от этого, СУБД специально относится к программному обеспечению, позволяющему вам взаимодействовать с базой данных.

Все системы управления базами данных имеют базовую модель, которая структурирует способ хранения и доступа к данным. Система управления реляционными базами данных – это СУБД, которая использует реляционную модель данных. В этой реляционной модели данные организованы в таблицы. Таблицы в контексте СУБД формально называются отношениями. Отношение – это набор кортежей, которые являются строками в таблице, и каждый кортеж имеет набор атрибутов, которые являются столбцами в таблице:

Большинство реляционных баз данных используют язык структурированных запросов (SQL) для управления и запросов данных. Однако многие СУБД используют свой собственный диалект SQL, который может иметь определенные ограничения или расширения. Эти расширения обычно включают дополнительные функции, которые позволяют пользователям выполнять более сложные операции, чем они могли бы сделать с обычным SQL.

Типы данных и ограничения

Каждому столбцу назначается тип данных, который определяет, какие записи разрешены в этом столбце. Различные СУБД реализуют разные типы данных, которые не всегда могут быть непосредственно взаимозаменяемы. Некоторые распространенные типы данных включают даты, строки, целые числа и логические значения.

Хранение целых чисел в базе данных более сложно, чем простое размещение чисел в таблице. Числовые типы данных могут быть как знаковыми, что означает, что они могут представлять как положительные, так и отрицательные числа, так и беззнаковыми, что означает, что они могут представлять только положительные числа. Например, тип данных tinyint в MySQL может содержать 8 бит данных, что равно 256 возможным значениям. Диапазон значений этого типа данных для знаковых чисел составляет от -128 до 127, в то время как для беззнаковых чисел диапазон составляет от 0 до 255.

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

• УНИКАЛЬНЫЙ: Применение этого ограничения к столбцу гарантирует, что ни одна из двух записей в этом столбце не будет идентичной.
• NOT NULL: Это ограничение гарантирует, что в столбце нет ни одной записи с NULL.
• PRIMARY KEY: Комбинация UNIQUE и NOT NULL, ограничение PRIMARY KEY гарантирует, что ни одна запись в столбце не является NULL, и что каждая запись уникальна.
• FOREIGN KEY: FOREIGN KEY – это столбец в одной таблице, который ссылается на PRIMARY KEY другой таблицы. Это ограничение используется для связи двух таблиц. Записи в столбце FOREIGN KEY должны уже существовать в родительском столбце PRIMARY KEY, чтобы процесс записи был успешным.
• CHECK: Это ограничение ограничивает диапазон значений, которые могут быть введены в столбец. Например, если ваше приложение предназначено только для жителей Аляски, вы можете добавить ограничение CHECK на столбец ZIP-код, чтобы разрешить только записи между 99501 и 99950.

Если вы хотите узнать больше о системах управления базами данных, ознакомьтесь с нашей статьей Сравнение систем управления базами данных и моделей NoSQL.

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

SQLite

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

Согласно веб-сайту проекта SQLite, это «безсерверная» база данных. Большинство реляционных баз данных реализованы как серверный процесс, в котором программы взаимодействуют с хост-сервером через межпроцессное взаимодействие, передающее запросы. В отличие от этого, SQLite позволяет любому процессу, который обращается к базе данных, читать и записывать данные напрямую на диск. Это упрощает процесс настройки SQLite, поскольку это исключает необходимость настройки серверного процесса. Точно так же для программ, которые будут использовать базу данных SQLite, не требуется никакой конфигурации: им просто нужен доступ к диску.

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

Поддерживаемые типы данных в SQLite

SQLite позволяет использовать различные типы данных, организованные в следующие классы хранения:

В контексте SQLite термины “класс хранения” и “тип данных” считаются взаимозаменяемыми. Если вы хотите узнать больше о типах данных SQLite и аффинности типов SQLite, ознакомьтесь с официальной документацией SQLite по этому вопросу.

Преимущества SQLite

• Небольшой размер: Как следует из названия, библиотека SQLite очень легковесна. Хотя количество места, которое она занимает, зависит от системы, на которой она установлена, она может занимать менее 600 Кб. Кроме того, она полностью автономна, что означает отсутствие внешних зависимостей, которые необходимо устанавливать на вашей системе для работы с SQLite.
• Удобный в использовании: SQLite иногда описывается как база данных “без необходимости в конфигурации”, готовая к использованию сразу после установки. SQLite не работает как серверный процесс, что означает, что его никогда не нужно останавливать, запускать или перезапускать, и он не поставляется с какими-либо файлами конфигурации, которые нужно управлять. Эти функции помогают упростить путь от установки SQLite до его интеграции с приложением.
• Портативный: В отличие от других систем управления базами данных, которые обычно хранят данные как большой пакет отдельных файлов, целая база данных SQLite хранится в одном файле. Этот файл может быть размещен в любом месте в иерархии каталогов и может быть общим через съемные носители или протокол передачи файлов.

Недостатки SQLite

• Ограниченная параллельность: Хотя несколько процессов могут одновременно получать доступ к базе данных SQLite и выполнять запросы, только один процесс может вносить изменения в базу данных в любой момент времени. Это означает, что хотя SQLite поддерживает большую параллельность, чем большинство других встроенных систем управления базами данных, он не может поддерживать столько, сколько клиентские/серверные СУБД, такие как MySQL или PostgreSQL.
• Отсутствие управления пользователями: Системы баз данных часто поставляются с поддержкой пользователей или управляемых подключений с предопределенными привилегиями доступа к базе данных и таблицам. Поскольку SQLite читает и записывает непосредственно в обычный файл на диске, единственные применимые разрешения доступа – это типичные разрешения доступа базовой операционной системы. Это делает SQLite плохим выбором для приложений, которым требуется наличие нескольких пользователей с особыми правами доступа.
• Безопасность: СУБД, использующая сервер, в некоторых случаях может обеспечить лучшую защиту от ошибок в клиентском приложении, чем база данных без сервера, такая как SQLite. Например, случайные указатели в клиенте не могут повредить память на сервере. Кроме того, поскольку сервер является единым постоянным процессом, клиент-серверная база данных может контролировать доступ к данным с большей точностью, чем база данных без сервера. Это позволяет более детально настраивать блокировку и обеспечивать лучшую параллельность.

Когда использовать SQLite

• Встроенные приложения: SQLite отличный выбор базы данных для приложений, которым нужна портативность и которым не требуется будущего расширения. Примеры включают однопользовательские локальные приложения, мобильные приложения или игры.
• Замена доступа к диску: В случаях, когда приложению необходимо читать и записывать файлы на диск напрямую, использование SQLite может быть выгодным из-за дополнительных функций и простоты, которые появляются при использовании SQL.
• Тестирование: Для многих приложений может быть излишним проверять их функциональность с помощью СУБД, которая использует дополнительный серверный процесс. В SQLite есть режим в памяти, который можно использовать для быстрого выполнения тестов без накладных расходов реальных операций с базой данных, что делает его идеальным выбором для тестирования.

Когда не использовать SQLite

• Работа с большим объемом данных: SQLite технически может поддерживать базу данных размером до 140 ТБ, при условии, что дисковый накопитель и файловая система также поддерживают требования к размеру базы данных. Однако веб-сайт SQLite рекомендует, чтобы любая база данных, приближающаяся к 1 ТБ, размещалась на централизованной клиент-серверной базе данных, так как база данных SQLite такого размера или большего будет сложно управлять.
• Высокие объемы записей: SQLite позволяет выполнять только одну операцию записи в любой момент времени, что значительно ограничивает пропускную способность. Если ваше приложение требует большого количества операций записи или нескольких одновременных писателей, SQLite может быть недостаточным для ваших потребностей.
• Требуется сетевой доступ: Поскольку SQLite является базой данных без сервера, он не предоставляет прямого сетевого доступа к своим данным. Этот доступ встроен в приложение. Если данные в SQLite расположены на отдельном компьютере от приложения, потребуется высокоскоростное соединение между движком и диском через сеть. Это дорогостоящее и неэффективное решение, и в таких случаях лучше выбрать клиент-серверную СУБД.

MySQL

Согласно рейтингу DB-Engines, MySQL является самой популярной открытой РСУБД с момента начала отслеживания популярности баз данных на сайте в 2012 году. Это богатый функционалом продукт, который приводит в движение множество крупнейших веб-сайтов и приложений в мире, включая Twitter, Facebook, Netflix и Spotify. Начать работу с MySQL довольно просто, в основном благодаря его исчерпывающей документации и большому сообществу разработчиков, а также изобилию ресурсов, посвященных MySQL в Интернете.

MySQL был разработан для скорости и надежности, за счет полного соответствия стандарту SQL. Разработчики MySQL постоянно работают над более тесным соответствием стандарту SQL, но он все еще отстает от других реализаций SQL. Однако он поставляется с различными режимами и расширениями SQL, которые приближают его к соответствию.

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

MySQL вдохновил множество сторонних приложений, инструментов и интегрированных библиотек, которые расширяют его функциональность и помогают сделать работу с ним более удобной. Некоторые из наиболее широко используемых из этих инструментов сторонних разработчиков – это phpMyAdmin, DBeaver и HeidiSQL.

Поддерживаемые типы данных MySQL

Типы данных MySQL можно разделить на три широкие категории: числовые типы, типы даты и времени, и строковые типы.

Числовые типы:

Типы даты и времени:

Строковые типы:

Преимущества MySQL

• Популярность и простота использования: Как одна из самых популярных систем баз данных в мире, MySQL имеет множество администраторов баз данных, имеющих опыт работы с ней. Также существует обилие документации в печатном и онлайн форматах о том, как установить и управлять базой данных MySQL. Сюда входит ряд сторонних инструментов, таких как phpMyAdmin, которые направлены на упрощение процесса начала работы с базой данных.
• Безопасность: MySQL поставляется с установленным скриптом, который помогает улучшить безопасность вашей базы данных путем установки уровня безопасности пароля, определения пароля для пользователя root, удаления анонимных учетных записей и удаления тестовых баз данных, которые по умолчанию доступны всем пользователям. Кроме того, в отличие от SQLite, MySQL поддерживает управление пользователями и позволяет назначать привилегии доступа на индивидуальной основе для каждого пользователя.
• Скорость: Отказавшись от реализации определенных функций SQL, разработчики MySQL смогли приоритизировать скорость. Хотя более новые тесты производительности показывают, что другие системы управления реляционными базами данных, такие как PostgreSQL, могут сравниться или хотя бы приблизиться к MySQL по скорости, MySQL все равно обладает репутацией чрезвычайно быстрого решения для баз данных.
• Репликация: MySQL поддерживает ряд различных типов репликации, которая представляет собой практику обмена информацией между двумя или более хостами для повышения надежности, доступности и устойчивости к сбоям. Это полезно для настройки резервного копирования базы данных или горизонтального масштабирования базы данных.

Недостатки MySQL

• Известные ограничения: Поскольку MySQL был разработан для скорости и простоты использования, а не для полного соответствия SQL, он имеет определенные функциональные ограничения. Например, он не поддерживает использование оператора FULL JOIN.
• Лицензирование и проприетарные функции: MySQL представляет собой программное обеспечение с двойной лицензией, с бесплатной и открытой версией для сообщества, лицензированной в соответствии с GPLv2, а также несколькими коммерческими версиями с закрытыми лицензиями. Из-за этого некоторые функции и плагины доступны только для проприетарных версий.
• Замедленное развитие: С момента приобретения проекта MySQL корпорацией Sun Microsystems в 2008 году, а затем корпорацией Oracle в 2009 году, появились жалобы от пользователей о значительном замедлении процесса разработки СУБД, поскольку сообщество больше не имеет возможности быстро реагировать на проблемы и внедрять изменения.

Когда использовать MySQL

• Распределенные операции: Поддержка репликации MySQL делает ее отличным выбором для распределенных баз данных, таких как архитектуры основной-вторичной или основной-основной.
• Веб-сайты и веб-приложения: MySQL приводит в движение множество веб-сайтов и приложений по всему интернету. Это в значительной степени обусловлено тем, насколько легко установить и настроить базу данных MySQL, а также ее общей скоростью и масштабируемостью в долгосрочной перспективе.
• Ожидаемый будущий рост: Поддержка репликации MySQL может помочь облегчить горизонтальное масштабирование. Кроме того, это относительно простой процесс обновления до коммерческого продукта MySQL, такого как MySQL Cluster, который поддерживает автоматическое расслоение, еще один процесс горизонтального масштабирования.

Когда не использовать MySQL

• Необходимо соответствие SQL: Поскольку MySQL не стремится полностью реализовать стандарт SQL, эта система не является полностью совместимой с SQL. Если полное или даже близкое к полному соответствие SQL необходимо для вашего случая использования, вам может потребоваться использовать более полносовместимую СУБД.
• Конкурентность и большие объемы данных: Хотя MySQL в целом хорошо справляется с операциями чтения, параллельные операции чтения и записи могут вызывать проблемы. Если ваше приложение будет иметь много пользователей, одновременно записывающих данные в него, другая СУБД, такая как PostgreSQL, может быть лучшим выбором для базы данных.

PostgreSQL

PostgreSQL, также известный как Postgres, называет себя “самой передовой открытой реляционной базой данных в мире”. Он был создан с целью быть высокорасширяемым и соответствующим стандартам. PostgreSQL является объектно-реляционной базой данных, что означает, что хотя он в основном является реляционной базой данных, он также включает функции, такие как наследование таблиц и перегрузка функций, которые чаще связывают с объектными базами данных.

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

PostgreSQL не так широко используется, как MySQL, но все равно существует ряд сторонних инструментов и библиотек, предназначенных для упрощения работы с PostgreSQL, включая pgAdmin и Postbird.

Поддерживаемые типы данных PostgreSQL

PostgreSQL поддерживает числовые, строковые, даты и временные типы данных, так же как MySQL. Кроме того, он поддерживает типы данных для геометрических форм, сетевых адресов, битовых строк, текстовых поисков и записей JSON, а также несколько идиосинкратических типов данных.

Числовые типы:

Типы символов:

Типы даты и времени:

Геометрические типы:

Типы сетевых адресов:

Типы битовых строк:

Типы текстового поиска:

Типы JSON:

Другие типы данных:

Преимущества PostgreSQL

• Соответствие SQL: В большей степени, чем SQLite или MySQL, PostgreSQL стремится тесно придерживаться стандартов SQL. Согласно официальной документации PostgreSQL, PostgreSQL поддерживает 160 из 179 функций, необходимых для полного соответствия SQL:2011, в дополнение к длинному списку опциональных функций.
• Открытый и сообществом разрабатываемый: Полностью открытый проект, исходный код PostgreSQL разрабатывается большим и преданным сообществом. Точно так же сообщество Postgres поддерживает и вносит вклад в многочисленные онлайн-ресурсы, описывающие работу с СУБД, включая официальную документацию, вики PostgreSQL и различные онлайн-форумы.
• Расширяемость: Пользователи могут расширять PostgreSQL программно и на лету с помощью каталогово-ориентированной операции и использования динамической загрузки. Можно назначить файл объектного кода, такой как разделяемую библиотеку, и PostgreSQL загрузит его по необходимости.

Недостатки PostgreSQL

• Производительность памяти: Для каждого нового подключения клиента PostgreSQL создает новый процесс. Каждому новому процессу выделяется около 10 МБ памяти, что может быстро накапливаться для баз данных с большим количеством подключений. Соответственно, для простых операций с большим объемом чтения PostgreSQL обычно менее производителен, чем другие СУБД, такие как MySQL.
• Популярность: Хотя PostgreSQL стал более широко используемым в последние годы, исторически он отстает от MySQL по популярности. Одним из следствий этого является то, что все еще существует меньше сторонних инструментов, которые могли бы помочь управлять базой данных PostgreSQL. Точно так же, количество администраторов баз данных с опытом управления базой данных Postgres меньше, чем у тех, у кого есть опыт работы с MySQL.

Когда использовать PostgreSQL

• Целостность данных важна: PostgreSQL полностью соответствует стандарту ACID с 2001 года и реализует многоверсионный контроль валюты, чтобы гарантировать согласованность данных, что делает его сильным выбором СУБД, когда целостность данных критична.
• Интеграция с другими инструментами: PostgreSQL совместим с широким спектром языков программирования и платформ. Это означает, что если вам когда-либо понадобится перенести вашу базу данных на другую операционную систему или интегрировать ее с конкретным инструментом, это вероятно будет проще с базой данных PostgreSQL, чем с другой СУБД.
• Сложные операции: Postgres поддерживает планы запросов, которые могут использовать несколько ЦП для ответа на запросы с большей скоростью. Это, в сочетании с его сильной поддержкой множественных одновременных писателей, делает его отличным выбором для выполнения сложных операций, таких как хранилище данных и онлайн-обработка транзакций.

Когда не использовать PostgreSQL

• Скорость императивна: В ущерб скорости PostgreSQL был разработан с учетом расширяемости и совместимости. Если ваш проект требует самых быстрых операций чтения, возможно, PostgreSQL не является лучшим выбором СУБД.
• Простые настройки: Из-за его большого набора функций и сильной приверженности стандартному SQL Postgres может быть излишним для простых настроек базы данных. Для операций с большим количеством чтения, где требуется скорость, обычно более практичным выбором является MySQL.
• Сложная репликация: Хотя PostgreSQL действительно обеспечивает крепкую поддержку для репликации, это все еще относительно новая функция. Некоторые конфигурации, например, архитектура primary-primary, возможны только с помощью расширений. Репликация является более зрелой функцией в MySQL, и многие пользователи считают репликацию в MySQL более простой в реализации, особенно для тех, у кого недостаточно опыта в администрировании баз данных и системы.

Вывод

Сегодня SQLite, MySQL и PostgreSQL – три самые популярные открытые системы управления реляционными базами данных в мире. Каждая из них имеет свои уникальные особенности и ограничения, и преуспевает в конкретных сценариях. При выборе РСУБД играет много факторов, и выбор редко ограничивается просто выбором самой быстрой или функциональной системы. В следующий раз, когда вам понадобится решение для реляционной базы данных, обязательно проведите глубокое исследование этих и других инструментов, чтобы найти то, которое лучше всего соответствует вашим потребностям.

Если вы хотите узнать больше о SQL и как его использовать для управления реляционной базой данных, мы рекомендуем обратиться к нашему шпаргалке Как управлять базой данных SQL. С другой стороны, если вы хотите узнать о нереляционных (или NoSQL) базах данных, ознакомьтесь с нашим Сравнением систем управления базами данных NoSQL.

Ссылки

• Рейтинги DB-Engines
• Официальная документация SQLite
• SQLite не требует сервера
• Подходящие случаи использования SQLite
• Официальная документация MySQL
• Сравнение репликации MySQL и Postgres 9.0
• Официальная документация PostgreSQL
• Пришло ли время для PostgreSQL?