Сравнение производительности MySQL vs PostgreSQL

Материал из Национальной библиотеки им. Н. Э. Баумана
Последнее изменение этой страницы: 18:34, 12 сентября 2018.

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

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

Основные характеристики баз данных

MySQL — свободная реляционная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems.[1]

PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (ORDBMS) (по-русски ОРСУБД или просто СУБД) основанная на POSTGRES, версии 4.2, которая была разработана в Научном Компьютерном Департаменте Беркли Калифорнийского Университета.[2]

Сравнение MySQL и PostgreSQL[3]
Параметры MySQL PostgreSQL
Краткое описание Широко используемая свободная реляционная система управления базами данных Широко используемая свободная реляционная система управления базами данных
Основная модель хранения данных Реляционная база данных Реляционная база данных
Дополнительная модель хранения данных База данных типа Key/Value, документно-ориентированная база данных База данных типа Key/Value, документно-ориентированная база данных
Вебсайт www.mysql.com www.postgresql.org
Документация dev.mysql.com/­doc www.postgresql.org/­docs/­manuals
Разработчик Oracle PostgreSQL Global Development Group
Дата релиза 1995 1996
Текущая версия 8.0.12, Июль 2018 10.5, Август 2018
Лицензия Открытое программное обеспечение Открытое программное обеспечение
Облачное Нет Нет
Язык реализации С++, C C
Поддерживаемые операционные системы сервера FreeBSD, Linux, Solaris, OS X, Windows FreeBSD, Linux, Solaris, OS X, Windows, NetBSD, OpenBSD, HP-UX, Unix
Схема данных Да Да
Типизация Да Да
Поддержка XML Да Да
Поддержка вторичных индексов Да Да
SQL Да Да
API и другие методы доступа Проприентарное нативное API, ADO.NET, JDBC, ODBC Нативная С библиотека, потоковое API для больших объектов, ADO.NET, JDBC, ODBC
Поддерживаемые языки программирования Ada, C, C#, С++, D, Delphi, Eiffel, Erlang, Haskell, Java, JavaScript (Node.js), Objective-C, OCaml, Perl, PHP, Python, Ruby, Scheme, Tcl .Net, C, С++, Delphi, Java, Perl, PHP, Python, Tcl
Язык написания скриптов на стороне сервера Да Функции определенные пользователем
Триггеры Да Да
Методы разбиения Горизонтальное разбиение, шардинг с MySQL Cluster или MySQL Fabric декларативное разбиение (по диапазону или списку) начиная с PostgerSQL 10.0
Методы репликаций Master-Master, Master-Slave Master-Slave
MapReduce Нет Нет
Концепции согласования Немедленное согласование Немедленное согласованиее
Параллелизм Да Да
Возможность хранения только в памяти Да Нет
Контроль доступа пользователей Концепт пользователей с детальной авторизацией Детальные права доступа в соответствии с SQL стандартом

Критерии сравнения

Мы сравним скорость выборки из одной таблицы, обновления одной таблицы, скорость сортировки (ORDER BY) и группировки (GROUP BY) при выборке данных из одной таблицы, а также скорость вставки в таблицу и внутреннего объединения двух таблиц.

Выбор структуры таблиц и обоснование

Мы создадим две таблицы: first_table(rand_num int, some_data varchar(40)) и second_table(rand_num int, rand_group int, some_data varchar(40)). Атрибут some_data нам нужен просто для того, чтобы увеличить объем записи на диск, так что в него можно записывать любой текст. Атрибуты rand_num обеих таблиц будут содержать в себе случайные значения, так же как и атрибут rand_group второй таблицы. Тесты будут проводиться следующим образом:

1) сначала мы будем просто добавлять строки (до 400000 строк) в первую таблицу и замерять время добавления.

 :
 1 firstTableRowCount = len(firstTableRandNums)	#firstTableRandNums - случайные числа для заполнения атрибута rand_num первой таблицы
 2     rowsCountPerQuery = firstTableRowCount / 10		#rowsCountPerQuery - количество добавляемых в таблицу строк за один запрос
 3     insertQuery = "insert into first_table values"	#начинаем формировать запрос на добавление
 4     totalInsertTime = 0					#totalInsertTime - суммарное время выполнения запросов на добавление строк
 5     for i in range(firstTableRowCount):
 6         insertQuery += ("(%d, 'first_table some_data')" % firstTableRandNums[i])
 7         if (i + 1) % rowsCountPerQuery:
 8             insertQuery += ", "
 9         else:
10             insertQuery += ";"
11             t = time.time()
12             cursor.execute(insertQuery)			#выполняем запрос на добавление строк
13             totalInsertTime += time.time() - t  
14             print("%d : %s" % ((i + 1), totalInsertTime))
15             insertQuery = "insert into first_table values"	#начинаем формировать следующий запрос


Когда же мы будем заполнять вторую таблицу (до 400000 строк), то каждый раз после добавления фиксированного числа строк (40000) мы будем проводить следующие измерения: 1) скорость внутреннего объединения первой и второй таблицы по атрибуту rand_num

1 cursor.execute("select * from first_table" 
2                 " inner join second_table on first_table.rand_num = second_table.rand_num;")
3             selectedList = list(cursor)		#это нужно, чтобы извлечь данные из курсора, иначе результат измерения скорости будет некорректным

2) скорость выборки всех данных из второй таблицы, сортируя их по атрибуту rand_num

1 cursor.execute("select * from second_table order by rand_num;")
2             selectedList = list(cursor)

3) скорость подсчета числа строк второй таблицы, принадлежащих одной группе (rand_group)

1 cursor.execute("select count(*) from second_table group by rand_group;")
2             selectedList = list(cursor)

4) скорость выборки тех строк из первой таблицы, атрибут rand_num которых совпадает с атрибутом rand_num какой-либо строки второй таблицы

1 cursor.execute("select * from first_table where rand_num in (select rand_num from second_table);")
2             selectedList = list(cursor)

5) Скорость обновления атрибута some_data в строках первой таблицы. Но обновлять мы будем не все строки, а только те, которые удовлетворяют условию, описанному в п.4.

1 cursor.execute("update first_table set some_data = 'second_table has %d rows' where rand_num in (select rand_num from second_table);" % (i + 1))


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

Результаты

С индексацией

Mysql vs postgresql 1.png

Добавление. postgresql оказался быстрее с большим отрывом в 3,46 раза.

Mysql vs postgresql 2.png

Внутреннее объединение. На небольшом количестве строк postgresql отрывается от mysql незначительно (в районе 25 процентов), но с увеличением размера второй таблицы преимущество postgresql становится более очевидным. При достижении второй таблицей максимального размера скорость объединения в postgresql уже в 2.8 раз выше. В среднем postgresql оказалась быстрее в 1,66 раза.

Mysql vs postgresql 3.png

Сортировка. Postgresql быстрее на 60-100 процентов в зависимости от размера второй таблицы. В среднем postgresql быстрее в 2,04 раза.

Mysql vs postgresql 4.png

Группировка. Сначала postgresql была быстрее в 4 раза, но с ростом второй таблицы преимущество сократилось до 1.6 раз. В среднем postgresql оказалась быстрее в 2,32 раза

Mysql vs postgresql 5.png

Выборка. Postgresql справляется в среднем в 1,97 раза лучше.

Mysql vs postgresql 6.png

Обновление. В postgresql время обновления данных растет более-менее линейно, чего совсем не скажешь о mysql, что говорит о разной реализации двух СУБД. При обновлении максимального числа строк postgresql справилась примерно в 2.5 раза лучше, при обновлении меньше числа строк отрыв становится гораздо существеннее. Postgresql справляется в среднем в 5,72 раза лучше.

Без индексации

Mysql vs postgresql 7.png

И у postgresql, и у mysql скорость добавления выросла. Оно и понятно, ведь теперь не нужно строить дополнительные индексы. Тем не менее преимущество сохраняется у postgresql (в среднем в 4,44 раза).

Mysql vs postgresql 8.png

Сортировка. Postgresql быстрее в среднем в 2 раза.

Mysql vs postgresql 9.png

Группировка. Postgresql быстрее в среднем в 2,41 раза. Отмена индексирования практически никак не повлияла на скорость выполнения запросов с группировкой и сортировкой как у postgresql, так и у mysql. Это говорит о том, что алгоритмы сортировки и группировки в обеих СУБД от индексов не зависят.

Mysql vs postgresql 10.png
Mysql vs postgresql 11.png
Mysql vs postgresql 12.png

Самым же неожиданным оказался тот факт, что отмена индексов в postgresql практически не сказалась даже на скорости выполнения объединения таблиц, а также выборки из первой таблицы и ее обновления. Хотя, казалось бы, в этих тестах нам важна скорость поиска по атрибуту rand_num, а индексирование как раз и должно было ускорить этот поиск. Для mysql же результаты тестирования объединения, выборки и обновления мы даже приводить не будем, так как уже при заполнении второй таблицы на 40000 строк на каждый запрос уходило более 400 секунд. Чего, собственно, изначально мы могли ожидать от обеих СУБД, а не только от MySQL.

Демонстрация работы

Установка баз данных

Подробный процесс установки этих баз данных можно увидеть в соответствующих статьях:

Заключение

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

В большинстве случаев для организации работы с базой данных в MySQL используется таблица InnoDB, эта таблица представляет из себя B-дерево с индексами. Индексы позволяют очень быстро получить данные из диска, и для этого будет нужно меньше дисковых операций. Но сканирование дерева требует нахождения двух индексов, а это уже медленно. Все это значит, что MySQL будет быстрее Postgresql только при использовании первичного ключа.

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

В целом PostgreSQL работает быстрее MySQL, как показали тесты, примерно в 2 раза.

Источники

  1. Википедия [Электронный ресурс]: MySQL / Дата обращения: 21.08.2018. — Режим доступа: https://ru.wikipedia.org/wiki/MySQL.
  2. Википедия [Электронный ресурс]:PostgreSQL / Дата обращения: 21.08.2018. — Режим доступа: https://ru.wikipedia.org/wiki/PostgreSQL.
  3. DB-Engines[Электронный ресурс]: System Properties Comparison MySQL vs. PostgreSQL / Дата обращения: 21.08.2018. — Режим доступа: https://db-engines.com/en/system/MySQL%3BPostgreSQL .