Сравнение MySQL и PostgreSQL с точки зрения разработчика

Аннотация

В статье представлен сравнительный анализ двух бесплатных свободных систем управления базами данных (СУБД): MySQL и PostgreSQL. Анализ ведётся с точки зрения использования этих СУБД в мало- и средненагруженных приложениях. Не рассматриваются вопросы масштабирования и оптимизации под проекты с многомиллионными аудиториями. Не приводятся данные сравнения производительности. Рассматриваются MySQL 5.1 и PostgreSQL 8.3.

Типы данных

Первое с чем приходится сталкиваться разработчику - это доступные типы данных. Проведём сравнение доступных типов данных.

Целые числа и числа с плавающей точкой

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

.                       |MySQL                | PostgreSQL        
TINYINT                 |1 байт               |                   
SMALLINT                |2 байта              | 2 байта           
MEDIUMINT               |3 байта              |                   
INTEGER, INT            |4 байта              | 4 байта           
BIGINT                  |8 байт               | 8 байт            
FLOAT, DOUBLE, REAL     |4 или 8 байт         | 4 или 8 байт      
DECIMAL, NUMERIC        |65 десятичных знаков | без ограничений   
SERIAL, BIGSERIAL       |8 байт               | 4 или 8 байт
BIT                     |он есть              | он есть

Примечание:
1) В MySQL есть поддержка UNSIGNED типов, однако это не входит в стандарт SQL.
2) Для типов с плавающей точкой PostgreSQL использует формат стандарта IEEE 754, поэтому можно хранить значения +Inf, -Inf и NaN, однако использовать эти значения в математических операциях не выйдет.


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

Строки и данные

Размеры указываются в байтах. Не забывайте, что для одного символа UTF-8 может использоваться от 1 до 4 байт.

.                       | MySQL           | PostgreSQL
BINARY, CHAR            | 255             | 10485760
VARCHAR, VARBINARY      | 65535*          | 10485760
TINYBLOB,TINYTEXT       | 2^8             | 
BLOB, TEXT, bytea       | 2^16            | не ограничен
MEDIUMBLOB              | 2^24            | 
LONGBLOB                | 2^32            |

* ограничение вызвано максимальной длиной строки, равной 65535 байтам, но в реальности максимальная длинна гораздо меньше.


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

Чтобы не быть голословным приведу пример — хранение данных пользователя. Предположим, что нам потребовалось хранить о пользователе не только его имя, фамилию и отчество, но адрес и телефоны, да мало ли что мы можем предложить хранить пользователю в своём профиле. С точки зрения SQL для этого должны использоваться типы CHAR и VARCHAR. И вот тут в MySQL приходится решать какая максимальная длинна у фамилии, какая максимальная длинна у имени, какая максимальная длинна у адреса, ибо на всё про всё дано 65535 байт. В то же время в PostgreSQL мы просто указываем в качестве типа для всех столбцов таблицы VARCHAR, куда мы в случае необходимости можем уложить гораздо больше данных, чем нам позволяет MySQL. (Попрошу не предлагать использовать TEXT в MySQL для этих целей.)

Дата и время

Типы даты и времени для двух баз практически идентичны и проблем как правило не вызывают.

Нестандартные типы

Для желающих PostgreSQL предлагает целую группу типов данных для работы, которые напрочь отстутствуют в MySQL: массивы, структуры, типы для хранения IP и MAC адресов, и даже типы для хранения параметров геометрических фигур. Желающие могут самостоятельно ознакомиться с типами данных PostgreSQL.

Выводы по типам данных

1) Типы данных, предлагаемые двумя СУБД, с функциональной точки зрения идентичны.
2) При помощи этих типов можно хранить данные в любой из СУБД, однако в MySQL разработчик вынужден на самом начальном этапе проектирования искуственно ограничивать длинну строковых данных, что не сказывается положительно на удобстве пользования системой.
3) Использование нестандартных типов в PostgreSQL позволяет довольно сильно упростить разработку, однако усложнит переход на другую СУБД.
4) MySQL позволяет точно контролировать структуру хранимых данных, однако при этом жертвуется удобством разработчика.

Возможности управления данными

Здесь я хочу сравнить две СУБД с точки зрения дополнительного функционала предлагаемого разработчику. Часть этого функционала включена в стандарт SQL.

Хранимые процедуры

Начнём с простейшего — хранимые процедуры. Грубо говоря, в MySQL вообще нет функционала хранимых процедур. Если выражаться более точно, то вообще они есть, но довольно условны. Так, например, при включённой репликации хранимые процедуры могут быть только readonly. Так что довольно популярная схема ограничения прав пользователя через хранимые процедуры вовсе не реализуема на MySQL.

Индексы и ключи

На этом фронте MySQL тоже не блещет своими возможностями. Ограничение в 1000 байт на размер ключа — куда это годится? Допустим, я разрешаю своим пользователям создавать учётные записи на любом языке (UTF-8). В качестве максимальной длинны логина я выбираю 512 символов. Так как логины должны быть уникальными, прихожу к выводу, что нужно наложить уникальный ключ на столбец, и, как выясняется, не могу, ибо ключ не вписывается в 1000 байт. Приходится идти на уступки и делать уникальность только по первым 333 символам. Кто не верит, может самостоятельно посмотреть на результат create table t( t varchar(512), key(t)) character set = utf8.
PostgreSQL такими комплексами не страдает, а просто делает уникальный ключ необходимого размера.

Проверка данных на этапе добавления

В стандарте SQL была предусмотрена инструкция CHECK, которая задаёт выражение, которому должны удовлетворять данные добавляемые в таблицу. В руководстве MySQL об этой инструкции сказано предельно просто «The CHECK clause is parsed but ignored by all storage engines.» Больше добавить мне к этому нечего, в постгре, как и ожидалось, всё в порядке.

Транзакции и внешние ключи

По умолчанию в MySQL для таблиц используется движок MyISAM, который не поддерживает ни транзакций ни внешних ключей. Это сложилось исторически и у такого подхода есть оправдание, если рассматривать работу с БД с точки производительности. Можно просто заметить, что, если вам нужны транзакции и внешние ключи, то использование storage engine InnoDB обязательно. Естественно в PostgreSQL транзакции и вешние ключи полностью функциональны.

Выводы

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

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


_________
Текст подготовлен в ХабраРедакторе