теперь, когдаMySQL 8 и PostgreSQL 10Теперь, когда он выпущен, пришло время рассмотреть, как две большие реляционные базы данных с открытым исходным кодом конкурируют друг с другом.
До этих выпусков обычно считалось, что Postgres имеет лучший набор функций и хвалили за его «академический» стиль, а MySQL лучше справлялся с массовым одновременным чтением/записью.
Но с выпуском их последней версии разрыв между ними значительно сократился.
Сравнение функций
Давайте взглянем на «причудливые» функции, о которых мы все любим говорить.
| характеристика | MySQL 8 | PostgreSQL 10 |
|---|---|---|
| Запрос и анализ | ||
| Общие табличные выражения (CTE) | ✔ Новое | ✔ |
| оконная функция | ✔ Новое | ✔ |
| тип данных | ||
| поддержка JSON | ✔ Улучшено | ✔ |
| GIS / SRS | ✔ Улучшено | ✔ |
| Полнотекстовый поиск | ✔ | ✔ |
| Масштабируемость | ||
| логическая репликация | ✔ | ✔ Новое |
| полусинхронная репликация | ✔ | ✔ Новое |
| Декларативное разбиение | ✔ | ✔ Новое |
Раньше часто говорили, что MySQL лучше всего подходит для онлайн-транзакций, а PostgreSQL лучше всего подходит для аналитических процессов. Но не больше.
Общие табличные выражения (CTE) и оконные функции являются основными причинами выбора PostgreSQL. Но теперь рекурсивный обход таблицы сотрудников путем ссылки на boss_id в той же таблице или нахождение медианы (или 50%) в отсортированном результате больше не является проблемой для MySQL.
Репликации в PostgreSQL не хватает гибкости конфигурации, поэтомуUber переходит на MySQLпричина. Но теперь, благодаря функции логической репликации, можно добиться обновления с нулевым временем простоя, создав новую версию Postgres и переключившись на нее. Также намного проще урезать устаревший раздел в огромной таблице событий временных рядов.
С точки зрения функций обе базы данных теперь согласованы.
Каковы различия?
Теперь у нас остался только один вопрос — так в чем причина выбора одного над другим?
экосистемаявляется одним из факторов. MySQL имеет динамичную экосистему, включающую MariaDB, Percona, Galera и т. д., а также механизмы хранения, отличные от InnoDB, но это также может сбивать с толку. Postgres имеет ограниченные возможности высокого класса, но это изменится с новыми функциями, представленными в последней версии.
Управлениеявляется еще одним фактором. Когда Oracle (или прежде всего SUN) купила MySQL, все боялись, что они уничтожат продукт, но в последнее десятилетие этого не произошло. На самом деле развитие ускорилось после приобретения. А у Postgres большой опыт в управлении заданиями и совместных сообществах.
инфраструктураОн не будет меняться очень часто, и хотя подробного обсуждения этого в последнее время не проводилось, его также стоит рассмотреть еще раз.
Давайте рассмотрим:
| характеристика | MySQL 8 | PostgreSQL 10 |
|---|---|---|
| Архитектура | один процесс | мультипрогресс |
| параллелизм | Многопоточность | fork(2) |
| Структура таблицы | кластеризованный индекс | куча |
| сжатие страницы | Transparent | TOAST |
| возобновить | In-Place / Rollback Segments | Append Only / HOT |
| вывоз мусора | очистить нить | Автоматически очистить процесс |
| Журнал транзакций | REDO Log (WAL) | WAL |
| Копировать журнал | Separate (Binlog) | WAL |
процесс против потока
когда Postgres разветвляет дочерний процессустановить соединение,Каждое соединение может занимать до 10 МБ.. По сравнению с моделью соединения потоков MySQL, которая более требовательна к памяти, на 64-разрядных платформах размер стека по умолчанию для потоков составляет 256 КБ. (Конечно, локальные буферы сортировки потока и т. д. делают эти накладные расходы менее значительными, даже если ими можно пренебречь.)
несмотря на то что"копирование при записи«Сохраняет некоторое неизменяемое состояние памяти, совместно используемое с родительским процессом, но базовые накладные расходы архитектуры, основанной на процессах, обременительны, когда у вас есть тысячи одновременных подключений, и это, вероятно, самый важный фактор при планировании емкости.
То есть, если вы запускаете приложение Rails на 30 серверах, каждый из которых имеет 16 ядер ЦП и 32 потока, то у вас будет 960 подключений. Вероятно, менее 0,1% приложений выйдут за пределы этого диапазона, но об этом следует помнить.
Кластеризованный индекс против таблицы кучи
кластеризованный индекспредставляет собой табличную структуру, в которой строки непосредственно встроены в структуру b-дерева ее первичного ключа. Куча (некластеризованная) — это обычная структура таблицы, которая заполняется строками данных отдельно от индексов.
С кластеризованным индексом, когда вы ищете запись по первичному ключу, вы можете получить всю строку за один ввод-вывод, тогда как некластеризованный индекс всегда требует поиска ссылки, что требует как минимум двух операций ввода-вывода. Поскольку ссылки на внешние ключи и соединения JOIN инициируют поиск первичного ключа, влияние может быть очень большим, что приведет к большому количеству запросов.
Один из теоретических недостатков кластеризованного индекса заключается в том, что при запросе с использованием вторичного индекса необходимо пройти в два раза больше узлов дерева, сначала просмотреть вторичный индекс, а затем просмотреть кластеризованный индекс, который также является деревом.
Однако, согласно современнымправила оформления таблиц, с автоматически увеличивающимся целым числом в качестве первичного ключа[1]--Это называетсяСуррогатный ключ- тогда естьКластерные индексы почти всегда желательны. Что еще более важно, я думаю, что это работает хорошо, если вы выполняете много ORDER BY id для получения самых последних (или самых старых) N записей.
Postgres не поддерживает кластерные индексы, а MySQL (InnoDB) не поддерживает кучи. Но в любом случае, если у вас много памяти, разница должна быть небольшой.
Структура страницы и сжатие
И Postgres, и MySQL имеют физическое хранилище на основе страниц. (8 КБ против 16 КБ)
Введение в физическое хранилище PostgreSQL
Структура страницы выглядит как карта справа. Он содержит некоторые записи, которые мы не собираемся здесь обсуждать, но они содержат метаданные о странице. Элемент, следующий за записью, представляет собой идентификатор массива, состоящий из пар (смещение, длина), указывающих на кортеж или строку данных. Таким образом, в Postgres несколько версий одной и той же записи могут храниться на одной странице.
Структура табличного пространства MySQL аналогична структуре Oracle в том, что она имеет несколько уровней, включая уровни, экстенты, страницы и уровни строк.
Кроме того, у него есть отдельный сегмент для отмены, называемый «сегментом отката». В отличие от Postgres, MySQL хранит несколько версий одной и той же записи в отдельной области.
Если есть одна страница, на которой должна поместиться строка в обеих базах данных, это означает, что строка должна быть меньше 8 КБ. (На странице MySQL должно поместиться не менее 2 строк, что составляет 16 КБ/2 = 8 КБ)
Так что же происходит, когда у вас есть большой объект JSON в столбце?
Постгрес используетTOAST, который является выделенным хранилищем теневых таблиц. Большие объекты вытягиваются при выборе строк и столбцов. Другими словами, множество черных ящиков не загрязнят ваш драгоценный кэш. Он также поддерживает сжатие объектов TOAST.
MySQL имеет более сложную функцию, называемуюПрозрачное сжатие страниц, благодаря производителям высококачественных SSD-накопителейFusio-ioвклад. Он предназначен для более эффективного использования твердотельных накопителей, где количество операций записи напрямую связано со сроком службы устройства.
Сжатие для MySQL применяется не только к большим объектам вне страницы, но и ко всем страницам. это черезразреженный файлЭто достигается с помощью пробивки отверстий, т.ext4 или btrfsи т.д. поддерживаются современными файловыми системами.
Подробнее см.:Значительный прирост производительности благодаря новому сжатию страниц MariaDB на FusionIO.
стоимость обновления
Еще одна функция, которую часто упускают из виду, но которая оказывает большое влияние на производительность и, вероятно, является самой спорной темой, — это обновления.
Это еще одна причина, по которой Uber отказался от Postgres, что спровоцировало многих сторонников Postgres на возражение.
-
MySQL против Убервозможныйподходит, но может не подойти вам
Обе являются базами данных MVCC, которые изолируют несколько версий данных.
Для этого Postgres хранит старые данные в куче до тех пор, пока она не будет очищена, а MySQL перемещает старые данные в отдельную область, называемую сегментами отката.
В Postgres при попытке обновления приходится копировать всю строку, а также запись индекса, указывающую на нее. Отчасти это связано с тем, что Postgres не поддерживает кластеризованные индексы, поэтому физическое расположение строки, на которую ссылается индекс, не абстрагируется логическим ключом.
Для решения этой проблемы Postgres используеткортеж в куче(HOT), не обновляйте индекс, когда это возможно. Однако, если обновления происходят достаточно часто (или если кортеж относительно большой), история кортежа может легко превысить размер страницы в 8 КБ, занимая несколько страниц и ограничивая эффективность этой функции. Время обрезки и/или дефрагментации зависит от эвристического решения. Также установите не более 100Параметры заполненияСнижает эффективность использования пространства — компромисс, который трудно учитывать при создании таблиц.
Это ограничение идет глубже; поскольку индексные кортежи не содержат никакой информации о транзакциях, оно не поддерживалось до версии 9.2.только сканирование индекса. Это один из старейших и наиболее важных методов оптимизации, поддерживаемый всеми основными базами данных, включая MySQL, Oracle, IBM DB2 и Microsoft SQL Server. Но даже с последней версией, когда много ОБНОВЛЕНИЙ вОтображение видимостиPostgres также не полностью поддерживает сканирование только индекса, когда в нем установлен грязный бит, и часто выбирает сканирование Seq, когда оно нам не нужно.
В MySQL обновления происходят на месте, а данные старых строк запечатываются в отдельной области, называемой сегментом отката. В результате вам не нужен VACUUM, а коммиты выполняются очень быстро, а откаты относительно медленны, что является желательным компромиссом для большинства случаев использования.
Это такжедостаточно умен, очистить историю как можно скорее. Если уровень изоляции транзакции установлен наREAD-COMMITTEDили ниже, история очищается после завершения оператора.
Размер записи транзакции не влияет на главную страницу. Фрагментация — ложное утверждение. Таким образом, общая производительность MySQL лучше и более предсказуема.
Вывоз мусора
В Postgres VACUUM очень дорог, потому что он в основном работает в области кучи, вызывая прямую конкуренцию за ресурсы. Это похоже на сборку мусора в языке программирования — она мешает и останавливает вас в любой момент.
Настройка для таблиц с миллиардами записейautovacuumВсе еще вызов.
Purge также может быть довольно тяжелым для MySQL, но, поскольку он выполняется в отдельном сегменте отката с выделенным потоком, он никак не влияет на параллелизм чтения. даже использоватьраспределение по умолчанию, вероятность того, что раздутый сегмент отката замедлит вас, невелика.
Занятые таблицы с миллиардами записей не раздувают исторические данные в MySQL, такие вещи, как размер файла в хранилище и производительность запросов, почти предсказуемы и стабильны.