Главная | Контакты | Настройки

СМЕНИТЬ ПАЛИТРУ:

MySQL vs PostgreSQL

Выбор между MySQL и PostgreSQL - это решение, которое должен принять каждый разработчик веб-приложений, который выбирает между различными Open-Source СУБД. Оба решения проверены временем и оба заслуживают пристального внимания. СУБД MySQL задумывалась как более быстрая но менее функциональная, в то время как разработчики PostgreSQL сосредоточились на большем количестве функционально полезных примочек чтобы как можно ближе соотвтетствовать стандартам Oracle. MySQL очень популярен среди Web разработчиков по причине его высокой скорости и простоты использования. PosgreSQL менее популярен, так как удобен и просто только для тех разработчиков, которые ранее использовали Oracle или другие похожие базы данных. Но все скоростные характеристики MySQL по сравнению с PostgreSQL постепенно тают, так как с каждым обновлением PostgreSQL заметно прибавляет в скорости. Теперь рассмотрим эти две СУБД более пристально.

Архитектура

PostgreSQL уникальная СУБД с единым сервером хранения данных (СХД). MySQL имеет два слоя в своей архитектуре. Первый слой - это набор серверов хранения данных. По этому при сравнении обычно указывают какой именно сервер хранения данных MySQL использовался. Каждый сервер отличают и параметры быстродейстсвия и его функциональные возможности. Самый распространенный из них - InnoDB. Он отличается от остальных полной поддержкой модели ACID и высокой производительностью. Прямым конкурентом InnoDB считается MyISAM, который годится для обработки и хранения сравнительно небольших объемов данных, которые приемущественно считываются из БД, а не записываются. А также когда не критично отсутствие ACID. Приложения, которые работают с MySQL, могут одновременно использовать несколько СХД для обеспечения наилучшего набора функциональности и производительности.

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

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

Ориентированность СУБД

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

Скоростные характеристики

PostgreSQL

PostgreSQL предоставляет функционал, который может увеличить производительность для некоторых видов SQL запросов. Из этих функций стоит выделить:
- частичное индексирование;
- сжатие данных;
- объем данных, хранимых в оперативной памяти;
- кэш запросов;

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

PostgreSQL поддерживат только один единственный СХД. Он входит в стандартную поставку PostgreSQL.

MySQL:ядро

MySQL 5.1 поддерживает 9 различных СХД:

1. MyISAM
2. InnoDB
3. NDB Cluster
4. MERGE
5. MEMORY (HEAP)
6. FEDERATED
7. ARCHIVE
8. CSV
9. BLACKHOLE

• SolidDB
• NitroEDB
• BrightHouse

Мультпроцессность

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

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

Ассинхронный Ввод/Вывод

PostgreSQL полностью поддерживает асинхронный API для использования в клиентских приложениях. Данная возможность позволяет увеличить производительность в некоторых случаях до 40%. В MySQL отсутствует поддержка ассинхронного режима. Но существует несколько драйверов, которые предоставляют эту возможность. Например MySQL драйверы для perl и ruby.
COUNT(*)

Транзакционные версионные СУБД, которые построенны на модели MVCC, например такие как PostgreSQL и InnoDB, выполняют COUNT(*) очень медленно в сравнении с не транзакционными СХД или транзакционными не версионными СХД, например такими как MyISAM. MyISAM в MySQL использует сканирование индексов для обработки COUNT(*) и также кэширует полученный результат, данный подход гараздо эффективнее. PostgreSQL и InnoDB требуют полного сканирования таблицы на предмет учета всех видимых полей. MVCC-совместимые СХД выполняют операцию COUNT(*) данным способом потому, что MVCC сохраняет информацию о видимости или скрытости транзакции в данных самой записи (ROW). Во всех MVCC-совместимых СХД кэширование результатов операции COUNT(*) приведет к возврату некоректных данных. PostgreSQL Count() работает медленнее чем COUNT(*) в InnoDB.
Тесты производительности

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

Модель ACID

ACID следует понимать как Atomicity, Consistency, Isolation и Durability (Валентность, Последовательность, Изоляция и Длительность). Данная модель используется для обеспечения целостности данных средствами СУБД. Многие СУБД достигают соглашений ACID путем использования транзакций.

PostgreSQL и MySQL использующий InnoDB, Cluster и Falcon СХД полностью соответствуют всем принципам и канонам модели ACID.
Возможности

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

Простота использования

Готча (Gotcha) - это функция или функции которые работают так как описаны но не так как ожидалось (http://sql-info.de/mysql/gotchas.html). Поклонники PostgreSQL настаивают на том, что MySQL имеет больше готчей чем PostgreSQL.

В последних версиях MySQL представила пользователям различные режими работы SQL, которые совместимы с различными стандартами SQL и позволяют легко разобраться в MySQL тем, кто с MySQL никогда не работал.

Insert Ignore / Replace

MySQL поддерживает операторы 'INSERT IGNORE' и 'REPLACE' которые вставляют запись если ее не существует и ничего не делают в другом случае или заменяют текущую запись соответственно.

PostgreSQL не поддерживает ничего из выше перечисленного и советует использовать сохраненные процедуры для достижения такого эффекта. Однако сущесвует одно "НО". В одно и тоже время может быть вставлено только одно значение. Данное условие накладывает серьезные ограничения на производительность и вызывает определенные сложности. INSERT IGNORE и REPLACE обрабатывают вставку нескольких значений и их запись симпотичнее чем процедура.

Еше одна полезная запись в MySQL: INSERT ... ON DUPLICATE UPDATE также напрочь отсутствует в PostgreSQL и для реализации своими руками требует написания сохраненных процедур, которые могут обрабатывать только 1 запись в один момент времени.

Ограничители

Обе СУБД PostgreSQL и MySQL поддерживают ограничители Not-Null, Unique, Primary Key и Foreign Key. Однако MySQL не поддерживает ограничение Check когда PostgreSQL поддерживает его уже довольно продолжительное время.

Таблицы InnoDB поддерживают проверки внешних ключей. Для остальных СХД MySQL разбирает и игнорирует FOREIGN KEY и REFERENCES в директиве CREATE TABLE.

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

Значения по умолчанию

PostgreSQL позволяет для колонок использовать в качестве значения по умолчанию любую функцию помеченную как IMMUTABLE или STABLE. В MySQL, на данный момент, лишь функция Now() может быть использована как значение по умолчанию для колонки.

Сохраненные процедуры

И PostgreSQL и MySQL поддерживают сохраненные процедуры.

Главный язык запросов в PostgreSQL - PL/pgSQL, он похож на PL/SQL в Oracle. PostgreSQL также поддерживает SQL:2003 PSM сохраненные процедуры также как и другие популярные языки программирования Perl (PL/Perl), Python (PL/Python), TCL (PL/Tcl), Java (PL/Java) и даже C (PL/C).

MySQL следует синтаксису SQL:2003 для сохраненных процедур, который также используется в IBM DB2.

MySQL поддерживает другие языки программирования сохраненный процедур с помощью плагинов. Из самых популярных стоит отметить Java, Perl, XML-RPC.

Триггеры

И PostgreSQL и MySQL поддерживают триггеры. Триггеры в PostgreSQL могут выполнять любые пользовательские функции из любого процедурного языка за исключением PL/pgsql.

Триггеры в MySQL активируются только SQL запросами. Они не активируются изменениями в таблицах, которые делаются при помощи MySQL API и не выполняют никаких SQL запросов.

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

Синтакс для определения триггеров в PostgreSQL не так силен как в MySQL. PostgreSQL требует раздельного определения функции с специальным типом возвращаемого значения.

Репликация и Высокая Доступность

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

PostgreSQL

PostgreSQL поддерживает репликацию на уровне подключаемых модулей. Существует несколько модулей, которые позволяют осуществлять репликацию данных СУБД PostgreSQL:
- PGCluster
- Slony-I
- DBBalancer
- pgpool
- PostgreSQL table comparator
- SkyTools
- Sequoia
- Bucardo
- Mammoth Replicator
- Cubercluster
- GridSQL (shared-nothing)

Существует заблуждение о том, что эти модули третих производителей как-то плохо интегрируются и выполняют свою работу не надлежащим образом. Например Slony, был спроектирован и разработан Жаном Вейком (Jan Weick), членом команды разработчиков PostgreSQL при участии множества людей из сообщества PostgreSQL. Однако, Slony, гараздо медленнее и использует больше ресурсов чем встроенный репликатор в MySQL. Но в версии PostgreSQL 8.4 вышел встроенный репликатор.

Слабые места репликации в PostgreSQL

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

2 сервера: MySQL: 2 = 2 PostgreSQL: 2*2^2 = 8
4 сервера: MySQL: 4 = 4 PostgreSQL: 2*4^2 = 32
12 серверов: MySQL: 12 = 12 PostgreSQL: 2*12^2 = 288.

Пока Slony-I адекватен для высокой доступности с использованием двух серверов. Slony-I очень тяжело администрировать.

Bucardo написан на языке Perl и интенсивно использует триггеры PL/PGSQL и PL/PERLU.

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

MySQL

MySQL поставляется с поддержкой ассинхронной репликации данных. Начиная с версии 5.1, MySQL поддерживает два формата репликации. SBR отслеживает SQL запросы, которые вносят изменения в БД, в специальный бинарный логфайл, на который подписаны все дочерние сервера. Соответственно при изменениях в главной БД, все остальные БД получают копии данных. RBR отслеживает инкрементальные изменения записей и записывает их в бинарный логфайл, из которого получают данные все дочерние БД. RBR используется автоматически, когда выполняется определенный запрос в главной БД. Некоторые СХД как NDB, Falcon и в некоторых случаях InnoDB для репликации используют только RBR.

Начиная с версии 6.0 MySQL поддерживает полусинхронную репликацию в которой мастер подтверждает получение хотя бы одной дочерней БД до возврата из процедуры комита данных. Этот метод позволяет уменьшить до минимума потери данных, особенно в больших кластерах.

Типы данных

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

PostgreSQL позволяет колонкам таблиц быть определенными как мультиразмерный массив с изменямой длинной. Массивы могут быть любого встроенного или пользовательского типа, enum или композитного типа. Но вот массивы доменов пока не поддерживаются.

MySQL не имеет типа данных IP Адрес, который присутствует в PostgreSQL но для преобразований из и в IPV4 используются функции INET_ATON() и INET_NTOA(). В БД хранится как Integer.

Подзапросы

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

Расширенное индексирование

Расширенное индексирование позволяет СУБД выполнять запросы с гараздо более высокой скоростью. И MySQL и PostgreSQL поддерживают расширенное индексирование.

Множественные индексы.

MySQL поддерживает множественные индексы для каждой таблицы в отдельности и может использовать один индекс для одной таблицы. PostgreSQL поддерживает множественнные индексы для каждого запроса в отдельности.

Полнотекстовые индексы.

MySQL идет с поддержкой полнотекстового поиска, но поиск может быть использован только на СХД MyISAM. Также MySQL поддерживает внешние модули для организации полнотекстового поиска - Sphinx Fulltext Search Engine. Данный плагин добавляет поддержку полнотекстового поиска для СХД InnoDB.

PostgreSQL начиная с версии 8.2 имеет в своем распоряжении полнотекстовый поиск в модуле tsearch2. Начиная с версии 8.3 модуль tsearch2 интегрирован в ядро PostgreSQL.

Частичное индексирование

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

Один из важнейших аспектов использования частичных индексов - это избежание индексирования повторяющихся значений. Когда запрос ищет повторяющиеся значения, которые занимают не более нескольких процентов от всей таблицы, индексы не используются. Так что нет нужды хранить эти данные в индексе. Данный подход уменьшает размер индекса и увеличивает скорость обработки запросов. А также увеличивается скорость выполнения других операций над таблицами, например скорость обновления записей.

Порционирование

MySQL поддерживает несколько форм горизонтального порционирования:
- RANGE
- LIST
- HASH
- KEY
- Композитное порционирование используя RANGE или LIST с HASH или KEY

PostgreSQL поддерживает только RANGE и LIST. HASH порционирование поддерживается с помощью внешних функций. Также PostgreSQL поддерживает композитные решения.

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

PostgreSQL распространяется по лицензии BSD, в которой отмечены пункты Free Software Definition и Open Source Definition и стандарт Copyfree.

MySQL доступен по лицензии GNU General Public License, в которой также отмечены пункты Free Software Definition и Open Source Definition но не по стандарту Copyfree. А это значит что если будет издан продукт, в котором используются исходники MySQL вам придется либо заплатить за комерческую копию MySQL AB либо открыть исходные коды для общего пользования.

Разработка

MySQL владеет и спонсирует одна профилирующая компания MySQL AB. Правда теперь она является частью Sun Microsystems, которая в свою очередь была приобретена компанией Oracle. MySQL AB держит копирайты и исходники MySQL.

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

MySQL - это ПРОДУКТ с открытым исходным кодом.
PostgreSQL - это ПРОЕКТ с открытым исходным кодом.

Происхождение названия

Когда был создан стандарт ANSI SQL, его автор сказал, что официально SQL произносится как "ess queue ell". Имена и MySQL и PostgreSQL отражают в своих именах этот стандарт. MySQL официально произносится как "my ess queue ell". Однако многие произносят MySQL как "my sequel".

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

PostgreSQL произносится как "пост гресс ку элл". Название сформировано из старого названия Postgres (так называлась СУБД на базе которой был создан PostgreSQL) и SQL. Некоторые люди называют Постгрескуэл как пиджискуэл (pgsql). Ходят слухи, что PostgreSQL будет переименован назад в Postgres. На данный момент идут активные дебаты по этому поводу.

Популярность

MySQL широко известнет и применяется в большинстве открытых (open-source) разработок по всему миру. Наиболее часто используется СХД MyISAM. И с уверенностью можно сказать, что MySQL самая популярная СУБД для вебразработок по всему миру.

Основаниями для такой популярности служит то, что MySQL легче использовать, чем другие СУБД, в частности PostgreSQL. Это утверждение бытует уже много лет. Но фактически несколько лет назад PostgreSQL произвел серьезные изменения в своей структуре и может по праву считаться более легким в использовании нежели MySQL. Но вопрос по прежнему открыт. Что же выбрать? MySQL или PostgreSQL?