SQLite vs MySQL vs PostgreSQL: сравнение систем управления базами данных

Система управления базами данных

Базы данных — это специально разработанное хранилище для различных типов данных. Каждая база данных, имеет определённую модель (реляционная, документно-ориентированная), которая обеспечивает удобный доступ к данным. Системы управления базами данных (СУБД) — специальные приложения (или библиотеки) для управления базами данных различных размеров и форм.

Реляционная система управления базами данных (РСУБД)

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

Типы данных и отношений между ними

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

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

Заметка: если вам требуется использовать неструктурированные данные (т.е. записи в одной и той же таблице могут иметь разную структуру), то следует рассмотреть использование NoSQL (безсхемная СУБД).

SQLite

Легко встраиваемая в приложения база данных. Так как это система базируется на файлах, то она предоставляет довольно широкий набор инструментов для работы с ней, по сравнению с сетевыми СУБД. При работе с этой СУБД обращения происходят напрямую к файлам (в эти файлах хранятся данные), вместо портов и сокетов в сетевых СУБД. Именно поэтому SQLite очень быстая, а также мощная благодаря технологиям обслуживающих библиотек.

Типы данных SQLite

NULL — значение NULL
INTEGER — знаковое целочисленное значение, использует 1, 2, 3, 4, 6, или 8 байт в зависимости от порядка числа
REAL — число с плавающей точкой, занимает 8 байт для хранения числа в формате IEEE
TEXT — текстовая строка, при хранении используются кодировки UTF-8, UTF-16BE или UTF-16LE
BLOB — тип данных BLOB, массив двоичных данных (предназначенный, в первую очередь, для хранения изображений, аудио и видео).

Заметка: более подробно о типах данных SQLite в официальной документации{:target=»_blank»}.

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

Файловая структура — вся база данных состоит из одного файла, поэтому её очень легко переносить на разные машины
Используемые стандарты — хотя может показаться, что эта СУБД примитивная, но она использует SQL. Некоторые особенности опущенны (RIGHT OUTER JOIN или FOR EACH STATEMENT), но основные все-таки поддерживаются
Отличная при разработке и тестировании — в процессе разработки приложений часто появляется необходимость масштабирования. SQLite предлагает всё что необходимо для этих целей, так как состоит всего из одного файла и библиотеки написанной на языке C.

Недостатки SQLite

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

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

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

Когда отказаться от SQLite

Многопользовательские приложения — если вам необходимо обеспечить доступ к данным для нескольких пользователей, да и к тому же различать их по правам доступа, то, наверное, полноценная СУБД (например: MySQL) будет более логичным выбором
Запись больших объемов данных — одно из ограничений SQLite это операции записи. Разрешен только один процесс записи в промежуток времени, что сильно ограничивает производительность.

MySQL

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

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

Несмотря на то, что в ней не реализован весь SQL функционал, MySQL предлагает довольно много инструментов для разработки приложений. Так как это серверная СУБД, приложения для доступа к данным, в отличии от SQLite работают со службами MySQL.

Типы данных MySQL

TINYINT — очень малые целочисленные значения
SMALLINT — малые целочисленные значения
MEDIUMINT — средние целочисленные значения
INT или INTEGER — стандартные целочисленные значения
BIGINT — большие целочисленные значения
FLOAT — маленькие значения с плавающей точкой (точность до одного значения после точки). Всегда знаковые значения
DOUBLE, BOUBLE PRECISION, REAL — Стандартные значения с плавающей точкой. Всегда знаковые
DECIMAL, NUMERIC — распакованное значение с плавающей точкой, всегда знаковое.
DATE — дата
DATETIME — дата и время в одном значении
TIMESTAMP — временная отметка timestamp
TIME — время
YEAR — год, 2 или 4 числа (4 — по-умолчанию)
CHAR — строковое значение фиксированной длины, справа всегда добавляются пробелы до указанной длины при сортировке
VARCHAR — строковое значение переменной длины
TINYBLOB, TINYTEXT — значение типа BLOB или TEXT, 255 (2^8 — 1) символов — максимальныя длина
BLOB, TEXT — значение типа BLOB или TEXT, 65535 (2^16 — 1) символов — максимальныя длина
MEDIUMBLOB, MEDIUMTEXT — значение типа BLOB или TEXT, 16777215 (2^24 — 1) символов — максимальныя длина
LONGBLOB, LONGTEXT — значение типа BLOB или TEXT, 4294967296 (2^32 — 1) символов — максимальныя длина
ENUM — перечисление
SET — множество

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

Простота в работе — установить MySQL довольно просто. Дополнительные приложения, например GUI, позволяет довольно легко работать с БД
Богатый функционал — MySQL поддерживает большинство функционала SQL.
Безопасность — большое количество функций обеспечивающих безопасность, которые поддерживается по умолчанию
Масштабируемость — MySQL легко работает с большими объемами данных и легко масштабируется
Скорость — упрощение некоторых стандартов позволяет MySQL значительно увеличить производительность.

Недостатки MySQL

Известные ограничения — по задумке в MySQL заложены некоторые ограничения функционала, которые иногда необходимы в особо требовательных приложениях.
Проблемы с надежностью — из-за некоторых способов обработки данных MySQL (связи, транзакции, аудиты) иногда уступает другим СУБД по надежности.
Медленная разработка — Хотя MySQL технически открытое ПО, существуют жалобы на процесс разработки. Стоит заметить, что существуют другие довольно успешные СУБД созданные на базе MySQL, например MariaDB.

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

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

Когда лучше отказаться от MySQL

Соответствие стандартам -Так как MySQL не ставит для себя целью — полностью соответствовать стандартам SQL, то эта СУБД не полностью поддерживает SQL. Если в будущем вы планируете перейти на подобную систему, то MySQL — не лучший выбор.
Многопоточность — хотя некоторые движки БД довольно легко выполняют параллельное чтение, параллельные операции чтения-записи могут создать проблемы
Недостаток функционала — некоторые движки MySQL, например, не поддерживают полнотекстовый поиск.

PostgreSQL

PostgreSQL является самым профессиональным из всех трех рассмотренных нами СУБД. Она свободно распространяемая и максимально соответствует стандартам SQL. PostgreSQL или Postgres стараются полностью применять ANSI/ISO SQL стандарты своевременно с выходом новых версий.

От других СУБД PostgreSQL отличается поддержкой востребованного объектно-ориентированного и/или реляционного подхода к базам данных. Например, полная поддержка надежных транзакций, т.е. атомарность, последовательность, изоляционность, прочность (Atomicity, Consistency, Isolation, Durability (ACID).) Благодаря мощным технологиям Postgre очень производительна. Параллельность достигнута не за счет блокировки операций чтения, а благодаря реализации управления многовариантным параллелизмом (MVCC), что также обеспечивает соответствие ACID. PostgreSQL очень легко расширять своими процедурами, которые называются хранимые процедуры. Эти функции упрощают использование постоянно повторяемых операций.

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

Типы данных PostgreSQL

bigint — знаковое 8-ми битное целочисленное значение
bigserial — автоматически инкрементируемое 8-ми битное целочисленное значение
bit[(n)] — строка постоянной длины
bit varying [(n)] — строка переменной длины
boolean — булево значение (true/false)
box — прямоугольник на плоскости
bytea — бинарные данные (массив байтов)
character varying [(n)] — строковое значение переменной длины
character [(n)] — строковое значение постоянной длины
cidr — IPv4/IPv6 сетевой адрес
circle — круг на плоскости
date — календарная дата (год, месяц, день)
double precision — число с плавающей точкой двойной точности (8 байт)
inet — IPv4/IPv6 адрес хоста
integer — знаковое 4-ех байтовое целочисленное значение
interval [fields][(p)] — отрезок времени
line — бесконечная прямая на плоскости
lseg — отрезок на плоскости
macaddr — MAC адрес
mony — валютное значение
numeric [(p, s)] — точное численное значение с выбранной точностью
path — геометрическая кривая на плоскости
point — геометрическая точка на плоскости
polygony — многоугольник на плоскости
real — число с плавающей точкой одинарной точности (4 байта)
smallint — знаковое целочисленное значение (4 байта)
serial — автоматические инкрементируемое целочисленное значение (4 байта)
text — строковое значение переменной длины
time [(p)] [without time zone] — время суток (без часового пояса)
time [(p)] with time zone — время суток (включая часовой пояс)
timestamp [(p)] [without time zone] — дата и время (без часового пояса)
timestamp [(p)] with time zone — дата и время (с часовым поясом)
tsquery — текстовый поисковый запрос
tsvector — документ текстового поиска
txid_snapshot — пользовательский снимок транзакции с ID
uuid — универсальный уникальный идентификатор
xml — XML данные

Достоинства PostgreSQL

Открытое ПО соответствующее стандарту SQL — PostgreSQL — бесплатное ПО с открытым исходным кодом. Эта СУБД является очень мощной системой.
Большое сообщество — существует довольно большое сообщество в котором вы запросто найдёте ответы на свои вопросы
Большое количество дополнений — несмотря на огромное количество встроенных функций, существует очень много дополнений, позволяющих разрабатывать данные для этой СУБД и управлять ими.
Расширения — существует возможность расширения функционала за счет сохранения своих процедур.
Объектность — PostrgreSQL это не только реляционная СУБД, но также и объектно-ориентированная с поддержкой наследования и много другого

Недостатки PostgreSQL

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

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

Целостность данных — когда надежность и целостность данных — ваши требования, PostgreSQL будет, пожалуй, лучшим выбором
Сложные пользовательские процедуры — если вам необходимо использовать пользовательские процедуры, то PostgreSQL имеет встроенную поддержку для них
Интеграция — если в будущем вы планируете переход на платные СУБД, например Oracle, то сделать это с PostgreSQL будет довольно просто по сравнению с другими бесплатными СУБД
Сложная структура данных — по сравнению с другими открытими СУБД PostgreSQL предоставляет больше возможностей для создания сложных структур данных без необходимости жертовать какими либо аспектами.

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

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