Базы данных MySQL и PostgreSQL

Разработка баз данных Mysql, Postgresql

При сравнении MySQL и PostgreSQL исторически сформировался следующий подход:

 

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

Сегодня мы рассмотрим два наиболее популярных сервера баз данных, основанных на open-source. Вначале поговорим об истории развития и архитектуре программных продуктов, а затем перейдем к сравнительному анализу. Автор не ставит своей целью написать подробный учебник по MySQL и PostgreSQL – в рамках короткого цикла статей это невозможно. Скорее здесь идет речь о небольшом вводном курсе для тех, кто еще не определился с выбором ПО. В следующих статьях мы будем поочередно рассматривать различные аспекты использования MySQL и PostgreSQL.

 

1. История развития MySQL и PostgreSQL

 

История MySQL начинается в 1979 г., у ее истоков стояла небольшая компания во главе с Monty Widenius. В 1996 г. появился первый релиз 3.11 под солярис с публичной лицензией. Затем MySQL была портирована под другие операционные системы, появилась специальная коммерческая лицензия. В 2000 г., после добавления интерфейса, аналогичного Berkeley DB, база стала транзакционной. Примерно тогда же была добавлена репликация. В 2001 г. в версии 4.0 был добавлен движок InnoDB к уже имеющемуся MyISAM, в результате чего появилось кеширование и возросла производительность. В 2004 г. вышла версия 4.1, в которой появились подзапросы, парциальная индексация для MyISAM, юникод. В версии 5.0 в 2005 г. появились хранимые процедуры, курсоры, триггеры, представления (views). В MySQL развиваются коммерческие тенденции: в 2009 г. MySQL стала торговой маркой компании Oracle.

История постгрес началась в 1977 г. с базы данных Ingress.

В 1986 г. в университете Беркли, Калифорния, она была переименована в PostgreSQL.

В 1995 г. постгрес стала открытой базой данных. Появился интерактивный psql.

В 1996 г. Postgres95 была переименована в PostgreSQL версии 6.0.

У постгреса несколько сотен разработчиков по всему миру.

2. Архитектура MySQL и PostgreSQL

PostgreSQL – унифицированный сервер баз данных, имеющий единый движок – storage engine. Постгрес использует клиент-серверную модель.

Для каждого клиента на сервере создается новый процесс (не поток !). Для работы с такими клиентскими процессами сервер использует семафоры.

Клиентский запрос проходит следующие стадии.

1. Коннект.
2. Парсинг: проверяется корректность запроса и создается дерево запроса (query tree). В основу парсера положены базовые юниксовые утилиты yacc и lex.
3. Rewrite: берется дерево запросов и проверяется наличие в нем правил (rules), которые лежат в системных каталогах. Всякий раз пользовательский запрос переписывается на запрос, получающий доступ к таблицам базы данных.
4. Оптимизатор: на каждый запрос создается план запроса – query plan, который передается исполнителю – executor. Смысл плана в том, что в нем перебираются все возможные варианты получения результата (использовать ли индексы, джойны и т.д.), и выбирается самый быстрый вариант.
5. Выполнение запроса: исполнитель рекурсивно проходит по дереву и получает результат, используя при этом сортировку, джойны и т.д., и возвращает строки. Постгрес – обьектно-реляционная база данных, каждая таблица в ней представляет класс, между таблицами реализовано наследование. Реализованы стандарты SQL92 и SQL99.

Транзакционная модель построена на основе так называемого multi-version concurrency control (MVCC), что дает максимальную производительность. Ссылочная целостность обеспечена наличием первичных и вторичных ключей.

MySQL имеет два слоя – внешний слой sql и внутренний набор движков, из которых наиболее часто используется движок InnoDb, как наиболее полно поддерживающий ACID.

Реализован стандарт SQL92.

С модульной точки зрения код MySQL можно разделить на следующие модули.

1. Инициализация сервера.
2. Менеджер коннектов.
3. Менеджер потоков.
4. Обработчик команд.
5. Аутентификация.
6. Парсер.
7. Кеш.
8. Оптимизатор.
9. Табличный менеджер.
10. Движки (MyISAM, InnoDB, MEMORY, Berkeley DB).
11. Логирование.
12. Репликация.
13. Сетевое API.
14. API ядра.

Порядок работы модулей следующий: сначала загружается первый модуль, который читает опции командной строки, конфиг-файлы, выделяет память, инициализирует глобальные структуры, загружает системные таблицы и передает управление менеджеру коннектов.

Когда клиент подсоединяется к базе, управление передается менеджеру потоков, который создает поток (не процесс!) для клиента, и проверяется его аутентификация.

Клиентские запросы в зависимости от их типа на верхнем уровне обрабатываются четвертым модулем (dispatcher). Запросы будут залогированы 11-м модулем. Команда передается парсеру, проверяется кеш. Далее запрос может попасть в оптимизатор, табличный модуль, модуль репликации, и т.д. В результате данные возвращаются клиенту через менеджер потоков.

Наиболее важный код находится в файле sql/mysqld.cc. В нем находятся базовые функции, которые не меняются со времен версии 3.22:

init_common_variables()
init_thread_environment()
init_server_components()
grant_init() // sql/sql_acl.cc
init_slave() // sql/slave.cc
get_options()
handle_connections_sockets()
create_new_thread()
handle_one_connection()
check_connection()
acl_check_host() // sql/sql_acl.cc
create_random_string() // sql/password.cc
check_user() // sql/sql_parse.cc
mysql_parse() // sql/sql_parse.cc
dispatch_command()
Query_cache::store_query() // sql/sql_cache.cc
JOIN::optimize() // sql/sql_select.cc
open_table() // sql/sql_base.cc
mysql_update() // sql/sql_update.cc
mysql_check_table() // sql/sql_table.cc

В хидере sql/sql_class.h определяются базовые классы: Query_arena, Statement, Security_context, Open_tables_state classes, THD. Обьект класса THD представляет собой дескриптор потока и является аргументом большого количества функций.

В начало

3. Сравнение MySQL и PostgreSQL: сходство и различия

ACID-стандарт

Стандарт ACID базируется на атомарности, целостности, изоляции и надежности. Эта модель используется для гарантии целостности данных. Реализуется это на основе транзакций. PostgreSQL полностью соответствует стандарту ACID. Для полной поддержки ACID в MySQL в конфиге нужно установить default-storage-engine=innodb.

Производительность (performance)

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

• парциальные индексы;
• компрессия данных;
• выделение памяти;
• улучшенный кеш.

MySQL имеет частичную поддержку парциальных индексов в InnoDB. Если взять MySQL-ский движок ISAM, он оказывается быстрее на плоских запросах, при этом нет блокировок на инсерты, нет поддержки транзакций, foreign key.

Компрессия

PostgreSQL лучше сжимает и разжимает данные, позволяя сохранить больше данных на дисковом пространстве. При этом компрессионные данные читаются быстрее с диска.

MySQL-компрессия для разных движков частично поддерживается, частично нет, и это зависит от конкретной версии конкретного движка.

SMP

На мульти-процессорности PostgreSQL имеет преимущество над MySQL. Даже сами разработчики MySQL признают, что их движок в этом плане не так хорош.

Типы данных

MySQL: для хранения бинарных данных использует типы TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, которые отличаются размером (до 4 ГБ).

Character: четыре типа – TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT.

PostgreSQL: поддерживает механизм пользовательских данных с помощью команды CREATE TYPE, тип BOOLEAN, геометрические типы.

Character: TEXT (ограничение – max row size).

Для хранения бинарных данных есть тип BLOB, который хранится в файловой системе. Столбцы таблицы могут быть определены как многомерный массив переменной длины. Обьектно-реляционное расширение: структура таблицы может быть унаследована от другой таблицы.

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

И PostgreSQL , и MySQL поддерживают хранимые процедуры. PostgreSQL придерживается стандарта Oracle PL/SQL, MySQL – IBM DB2. MySQL поддерживает extend SQL для написания функций на языке C/C++ с версии 5.1. PostgreSQL: PL/PGSQL, PL/TCL, PL/Perl, SQL, C для написания хранимых процедур.

Ключи

И PostgreSQL , и MySQL поддерживают уникальность Primary Key и Foreign Key. MySQL не поддерживает check constraint плюс вторичные ключи реализованы частично. PostgreSQL: полная реализация плюс поддержка ON DELETE CASCADE и ON UPDATE CASCADE.

Триггеры

MySQL: рудиментарная поддержка. PostgreSQL: декларативные триггеры: SELECT, INSERT, DELETE, UPDATE, INSTEAD OF; процедурные триггеры: CONSTRAINT TRIGGER. События: BEFORE или AFTER на INSERT, DELETE , UPDATE.

Автоинкремент

MySQL: в таблице может быть только один такой столбец, который должен быть проиндексирован. PostgreSQL: SERIAL data type.

Репликации

Поддерживаются и в MySQL, и в PostgreSQL. PostgreSQL имеет модульную архитектуру, и репликация входит в отдельные модули:

• PGCluster;
• Slony-I – основной механизм репликации в постгресе, производительность падает в квадратичной зависимости от числа серверов;
• DBBalancer;
• pgpool;
• PostgreSQL table comparator;
• SkyTools;
• Sequoia;
• Bucardo.

Репликация в PostgreSQL основана на триггерах и более медленная, чем в MySQL. В ядро репликацию планируется добавить, начиная с версии 8.4.

В MySQL репликация входит в ядро и имеет две разновидности, начиная с версии 5.1:

• SBR – statement based replication;
• RBR – row based replication.

Первый тип основан на логировании записей в бинарный лог, второй – на логировании изменений. Начиная с версии 5.5, в MySQL поддерживается так называемая полусинхронная репликация, при которой основной сервер (master) делает сброс данных на другой сервер (slave) при каждом коммите. Движок NDB делает полную синхронную двухфазную репликацию.

Транзакции

MySQL: только для для InnoDB. Поддержка SAVEPOINT, ROLLBACK TO SAVEPOINT. Уровни блокировки: table level (MyISAM). PostgreSQL: поддерживается плюс read committed и уровни изоляции. Поддержка ROLLBACK, ROLLBACK TO SAVEPOINT. Уровни блокировки: row level, table level.

Уровни привилегий

PostgreSQL: для пользователя или группы пользователей могут быть назначены привилегии.

Экспорт-импорт данных

MySQL: набор утилит для экспорта: mysqldump, mysqlhotcopy, mysqlsnapshot. Импорт из текстовых файлов, html, dbf. PostgreSQL: экспорт – утилита pg_dump. Импорт между базами данных и файловой системой.

Вложенные запросы

Есть и в MySQL, и в PostgreSQL, но в MySQL могут работать непроизводительно.

Индексация

Хэширование индексов: в MySQL– частичное, в PostgreSQL – полное. Полнотекстовый поиск: в MySQL– частичный, в PostgreSQL – полный. Парциальные индексы: в MySQL не поддерживаются, в PostgreSQL поддерживаются. Многостолбцовые индексы: в MySQL ограничение 16 столбцов, в PostgreSQL – 32. Expression-индексы: в MySQL– эмуляция, в PostgreSQL – полное. Неблокирующий create index: в MySQL – частичное, в PostgreSQL – полное.

Партиционирование (Partitioning)

MySQL поддерживает горизонтальное партиционирование: range, list, hash, key, композитное партиционирование. PostgreSQL поддерживает RANGE и LIST. Автоматическое партиционирование для таблиц и индексов.

Автоматическое восстановление после сбоев

MySQL: частичное для InnoDB – нужно вручную сделать backup. PostgreSQL: Write Ahead Logging (WAL).

Data Storage Engines

PostgreSQL поддерживает один движок – Postgres Storage System. В MySQL 5.1 их несколько:

• MyISAM – используется для хранения системных таблиц;
• InnoDB – максимальное соответствие ACID, хранит данные с первичными ключами, кэширует инсерты, поддерживает компрессию, начиная с версии 5.1 – см. атрибут ROW_FORMAT=COMPRESSED;
• NDB Cluster – движок, ориентированный на работу с памятью, кластерная архитектура, использующая синхронную репликацию;
• ARCHIVE – поддерживает компрессию, не использует индексы;
• а также: MERGE, MEMORY (HEAP), CSV.

InnoDB разрабатывается компанией InnoBase, являющейся дочерней компанией Oracle. В 6-й версии должны появиться два движка – Maria и Falcon. Falcon – движок, основанный на ACID-транзакциях.

Лицензирование

PostgreSQL: BSD (Berkeley Software Distribution) open source. MySQL: GPL (Gnu General Public License) или Commercial. MySQL – это open-source продукт. Postgres – это open-source проект.

В начало

Заключение

Подводя итоги, можно сказать следующее: MySQL и PostgreSQL – две наиболее популярные open-source базы данных в мире. Каждая база имеет свои особенности и отличия. Если вам нужно быстрое хранилище для простых запросов с минимальной настройкой, я бы порекомендовал MySQL. Если вам нужно надежное хранилище для большого объема данных с возможностью расширения, репликации, полностью соответствующее современным стандартам языка SQL, я бы предложил использовать PostgreSQL.