Стандарт SQL
Независимо от используемой системы управления базами данных, SQL — это стандартизированный язык выполнения запросов. И он поддерживается всеми решениями, даже MySQL или Postgresql. Стандарт SQL был разработан в 1986 году и за это время уже вышло нескольких версий.
MySQL
MySQL поддерживает далеко не все новые возможности стандарта SQL. Разработчики выбрали именно этот путь развития, чтобы сохранить MySQL простым для использования. Компания пытается соответствовать стандартам, но не в ущерб простоте. Если какая-то возможность может улучшить удобство, то разработчики могут реализовать ее в виде своего расширения не обращая внимания на стандарт.
Postgresql
Postgresql — это проект с открытым исходным кодом, он разрабатывается командой энтузиастов, и разработчики пытаются максимально соответствовать стандарту SQL и реализуют все самые новые стандарты. Но все это приводит к ущербу простоты. Postgresql очень сложный и из-за этого он не настолько популярен как MySQL.
A Bit About Database Management Systems
Databases are logically modelled clusters of information, or data. A database management system (DBMS), on the other hand, is a computer program that interacts with a database. A DBMS allows you to control access to a database, write data, run queries, and perform any other tasks related to database management. Although database management systems are often referred to as “databases,” the two terms are not interchangeable. A database can be any collection of data, not just one stored on a computer, while a DBMS is the software that allows you to interact with a database.
All database management systems have an underlying model that structures how data is stored and accessed. A relational database management system is a DBMS that employs the relational data model. In this model, data are organized into tables, which in the context of RDBMSs are more formally referred to as relations. A relation is a set of tuples, or rows in a table, with each tuple sharing a set of attributes, or columns:
Most relational databases use structured query language (SQL) to manage and query data. However, many RDBMSs use their own particular dialect of SQL, which may have certain limitations or extensions. These extensions typically include extra features that allow users to perform more complex operations than they otherwise could with standard SQL.
Note: The term “standard SQL” comes up several times throughout this guide. SQL standards are jointly maintained by the American National Standards Institute (ANSI), the International Organization for Standardization (ISO), and the International Electrotechnical Commission (IEC). Whenever this article mentions “standard SQL” or “the SQL standard,” it’s referring to the current version of the SQL standard published by these bodies.
It should be noted that the full SQL standard is large and complex: full core SQL:2011 compliance requires 179 features. Because of this, most RDBMSs don’t support the entire standard, although some do come closer to full compliance than others.
Each column is assigned a data type which dictates what kind of entries are allowed in that column. Different RDBMSs implement different data types, which aren’t always directly interchangeable. Some common data types include dates, strings, integers, and Booleans.
Numeric data types can either be signed, meaning they can represent both positive and negative numbers, or unsigned, which means they can only represent positive numbers. For example, MySQL’s data type can hold 8 bits of data, which equates to 256 possible values. The signed range of this data type is from -128 to 127, while the unsigned range is from 0 to 255.
Sometimes, a database administrator will impose a constraint on a table to limit what values can be entered into it. A constraint typically applies to one particular column, but some constraints can also apply to an entire table. Here are some constraints that are commonly used in SQL:
- : Applying this constraint to a column ensures that no two entries in that column are identical.
- : This constraint ensures that a column doesn’t have any entries.
- : A combination of and , the constraint ensures that no entry in the column is and that every entry is distinct.
- : A is a column in one table that refers to the of another table. This constraint is used to link two tables together: entries to the column must already exist in the parent column for the write process to succeed.
- : This constraint limits the range of values that can be entered into a column. For example, if your application is intended only for residents of Alaska, you could add a constraint on a ZIP code column to only allow entries between 99501 and 99950.
If you’d like to learn more about database management systems, check out our article on Understanding SQL and NoSQL Databases and Different Database Models.
Now that we’ve covered relational database management systems generally, let’s move onto the first of the three open-source relational databases this article will cover: SQLite.
СУБД типа ключ-значение
Наверное один из самых простых типов СУБД. В упрощенном виде, это некая таблица с уникальным ключом и собственно связанным с ним значением, в котором может быть что угодно. Чаще всего такие СУБД используют для кэширования, т.к. они очень быстро работают, а это и не сложно, когда есть уникальный ключ, и запрос возвращает только одно значение. У некоторых представителей данных СУБД есть возможность работать полностью в памяти, а так же есть возможность задавать срок жизни записи, после истечения которого, записи будут автоматически удаляться.
Наиболее известные СУБД такого типа — Redis и Memcached.
Когда выбирать СУБД ключ-значение
Если СУБД будет использоваться для кэширования данных или для брокеров сообщений, то это очень подходящий тип. Так же, такая СУБД хорошо подходит для баз где нужно хранить достаточно простые структуры, и иметь к ним очень быстрый доступ.
Когда не выбирать СУБД ключ-значение
Если вы предполагаете хранить в базе данных много сущностей (таблиц), а у сущностей будут сложные структуры с разными типами данных. Так же, если вы предполагаете делать из этой таблицы сложные запросы которые возвращают множества строк.
MariaDB
MariaDB – это по-настоящему открытый дистрибутив MySQL (выпущен под GNU GPLv2). Он был создан после приобретения Oracle MySQL, когда некоторые из основных разработчиков MySQL были обеспокоены тем, что Oracle подорвет его философию открытого исходного кода.
MariaDB был разработан, чтобы быть максимально совместимым с MySQL при замене нескольких ключевых компонентов. Он использует механизм хранения Aria, который функционирует как транзакционный, так и нетранзакционный механизм. Некоторые даже предполагали, что Aria станет стандартным движком для MySQL в будущих выпусках, прежде чем MariaDB станет независимым проектом.
Преимущества
- Многие пользователи выбирают MariaDB вместо MySQL из-за частых обновлений систем безопасности MariaDB. Хотя это не обязательно означает, что MariaDB более безопасна, это означает, что сообщество разработчиков серьезно относится к безопасности;
- Другие говорят, что основные преимущества MariaDB в том, что она почти наверняка останется с открытым исходным кодом и будет полностью совместима с MySQL. Это означает, что миграция из одной системы в другую происходит очень быстро;
- Благодаря этой совместимости MariaDB также хорошо работает со многими другими языками, которые обычно используются в MySQL. Это означает, что на изучение и отладку кода тратится меньше времени;
- Вы можете установить и запустить WordPress с MariaDB вместо MySQL для лучшей производительности и более богатого набора функций. WordPress является самой популярной CMS от markethare, которая обеспечивает почти половину контента в интернете, и имеет активное сообщество разработчиков с открытым исходным кодом. Сторонние темы и плагины работают так, как задумано, когда WordPress установлен с MariaDB.
Недостатки
- MariaDB несколько подвержен «вздутию живота». Его центральный файл журнала IDX, в частности, имеет тенденцию становиться очень большим после длительного использования, что в конечном итоге снижает производительность;
- Кэширование – это еще одна область, в которой MariaDB могла бы усовершенствоваться. Этот процесс не такой быстрый, каким мог бы быть, что немного расстраивает;
- Несмотря на все первоначальные обещания, MariaDB больше не полностью совместима с MySQL. Если вы переходите с MySQL, вам нужно будет выполнить перекодировку.
PVS-Studio
В качестве средства поиска ошибок был использован статический анализатор кода PVS-Studio. PVS-Studio — анализатор исходного кода для языков программирования C, C++, C#, помогающий сократить расходы на разработку ПО за счёт раннего обнаружения ошибок, дефектов и потенциальных уязвимостей в программном коде. Работает в средах Windows и Linux.
Ссылки на загрузку:
- Windows-версия.
- Linux-версия.
Ввиду того, что все 3 проекта достаточно просто собираются и содержат .sln файлы (сразу, или после генерации через CMake), задача самого анализа становится совсем тривиальной — достаточно запустить проверку через интерфейс плагина PVS-Studio, встраиваемый в IDE Visual Studio.
Возможности обработки
Из предыдущего пункта выплывают и другие отличия postgresql от mysql, это возможности обработки данных и ограничения. Естественно, соответствие более новым стандартам дает более новые возможности.
MySQL
При выполнении запроса MySQL загружает весь ответ сервера в память клиента, при больших объемах данных это может быть не совсем удобно. В основном по функциям Postgresql превосходит Mysql, дальше рассмотрим в каких именно.
Postgresql
Postgresql поддерживает использование курсоров для перемещения по полученным данным. Вы получаете только указатель, весь ответ хранится в памяти сервера баз данных. Этот указатель можно сохранять между сеансами. Здесь поддерживается построение индексов сразу для нескольких столбцов таблицы. Кроме того, индексы могут быть различных типов, кроме hash и b-tree доступны GiST и SP-GiST для работы с городами, GIN для поиска по тексту, BRIN и Bloom.
Postgresql поддерживает регулярные выражения в запросах, рекурсивных запросов и наследования таблиц. Но тут есть несколько ограничений, например, вы можете добавить новое поле только в конец таблицы.
History of PostgreSQL
- INGRES was developed-1977
- Michael Stonebraker and his colleagues developed Postgres- 1986
- Support for real ACID and PL/pgSQL – 1990
- Released as Postgres95 in -1995
- Re-released Postgres95 as PostgreSQL 6.0 – 1996
- MVCC, GUC, Join syntax Controls and Procedural Language Loader added- 1998-2001
- Version 7.2 to 8.2: Included features like Schema support, Nonblocking VACUUM, Roles and dblink – 2002-2006
- PostgreSQL 8.4 released in 2009
- PostgreSQL 9.0 released in 2010
- NYCPUG (New York City PostgreSQL User Group) joins PgUS (United States PostgreSQL association)- 2013
- PGconf organised-2014
Основные характеристики баз данных
MySQL — свободная реляционная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems.
PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (ORDBMS) (по-русски ОРСУБД или просто СУБД) основанная на POSTGRES, версии 4.2, которая была разработана в Научном Компьютерном Департаменте Беркли Калифорнийского Университета.
| Параметры | MySQL | PostgreSQL |
|---|---|---|
| Краткое описание | Широко используемая свободная реляционная система управления базами данных | Широко используемая свободная реляционная система управления базами данных |
| Основная модель хранения данных | Реляционная база данных | Реляционная база данных |
| Дополнительная модель хранения данных | База данных типа Key/Value, документно-ориентированная база данных | База данных типа Key/Value, документно-ориентированная база данных |
| Вебсайт | ||
| Документация | ||
| Разработчик | 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 стандартом |
Performance
By running some benchmarks you can get a rough idea of how these databases compare to each other in terms of performance.
For this article, we going to use the HammerDB benchmarking tool.
This tool implements a standardized performance test called TPC-C.
Here’s the description:
Here’s the setup:
- Ubuntu 18.04
- 1 GB RAM
- 1 CPU
- MySQL 5.7.23
- MariaDB 10.1
- PostgreSQL 10.5
- Default settings
We ran the tests for 5 minutes for each database.
Here are the results:
- PostgreSQL 18534 TPM, 7972 NOPM
- MySQL 14051 TPM, 4659 NOPM
- MariaDB 21441 TPM, 7172 NOPM
The meaning of these two values (TMP & NOPM) is described like this:
Like any benchmark the results depend on the specific scenario the tests are run in.
This particular benchmark tries to reproduce the workload of any industry that must manage, sell or distribute a product or service.
Knowing that, we can conclude that both PostgreSQL & MariaDB are faster than MySQL in this particular scenario, with PostgreSQL coming on top of the raw performance game.
Constraints
Помимо стандартных UNIQUE и NOT NULL, в базе можно делать еще и другие проверки целостности. В доменном типе можно прописать check:
который проверяет, что в колонку типа us_postal_code попадут только 5 цифр или 5 цифр, дефис и 4 цифры. Разумеется, сюда можно писать не только регулярки, но и любые другие условия.
Также check можно прописать в таблице:
Т.е. в имени должен быть хотя бы один символ, и не больше 300.
Вообще говоря, сами типы являются также и неким ограничением, дополнительной проверкой, которую делает база. Например, если у вас есть тип complex (смотри выше), состоящий, по сути, из двух чисел, то вы не вставите туда случайно строку:
Таким образом, иногда композитный тип может быть предпочтительнее, чем jsonb, потому что в json вы можете напихать что угодно вообще.
Открытый исходный код
В мире современных корпоративных технологий программное обеспечение с открытым исходным кодом прочно зарекомендовало себя как одна из самых больших сил, с которой приходится считаться. В конце концов, некоторые из самых больших технологических разработок появились из-за движения открытого исходного кода.
Нетрудно понять, почему: даже несмотря на то, что сетевые стандарты с открытым исходным кодом на основе Linux не так популярны, как проприетарные опции, они являются причиной, по которой интеллектуальные устройства разных производителей могут общаться друг с другом. Кроме того, многие утверждают, что разработка с открытым исходным кодом создает приложения, которые превосходят их собственные аналоги. Это одна из причин, почему велика вероятность того, что ваши любимые инструменты (будь то с открытым исходным кодом или проприетарные) были разработаны с использованием баз данных с открытым исходным кодом.
Как и любая другая категория программного обеспечения, функциональные возможности и возможности систем управления базами данных с открытым исходным кодом могут существенно отличаться. Проще говоря, не все системы управления базами данных с открытым исходным кодом одинаковы
Если вы выбираете базу данных с открытым исходным кодом для своей организации, важно выбрать ту, которая удобна для пользователя и может расти вместе с вашей организацией, а также предлагает более чем адекватные функции безопасности
С тех пор как MySQL был приобретен компанией Oracle, последняя уничтожила природу открытого исходного кода для этого проекта, который был базой данных с открытым исходным кодом в течение десятилетий. Однако это открыло пространство для других действительно открытых решений для баз данных, чтобы бросить ему вызов. Вот три из них, достойные вашего внимания.
SQLite
SQLite, пожалуй, является наиболее используемым механизмом баз данных в мире благодаря его внедрению во многих популярных веб-браузерах, операционных системах и мобильных телефонах. Первоначально разработанный как облегченный форк MySQL, в отличие от многих других баз данных, он не является клиент-серверным механизмом; скорее, его полное программное обеспечение встроено в каждую реализацию.
Это создает главное преимущество SQLite: во встраиваемых или распределенных системах каждая машина несет полную реализацию базы данных. Это может значительно повысить производительность баз данных, поскольку снижает потребность в межсистемных вызовах.
Преимущества
Если вы хотите построить и внедрить небольшую базу данных, то SQLite возможно, – лучший для этого путь. Он чрезвычайно мал, поэтому может быть реализован в широком спектре встроенных систем без трудоемких обходных путей;
Его небольшой размер делает систему чрезвычайно быстрой. В то время как некоторые более продвинутые базы данных используют сложные способы обеспечения эффективности, SQLite использует гораздо более простой подход: уменьшая размер вашей базы данных и связанного с ней программного обеспечения для обработки, вы просто получаете меньше данных для работы;
Его широкое распространение также означает, что SQLite, вероятно, является наиболее совместимой базой данных
Это особенно важно, если вам нужно или вы планируете интегрировать вашу систему со смартфонами: система встроена в iOS и будет служить в ней до тех пор, пока существуют сторонние приложения. Кроме того, она безупречно работает в широком диапазоне сред.
Недостатки
- Крошечный размер SQLite означает, что ему не хватает некоторых функций, встречающихся в больших базах данных. Например, в нем отсутствует встроенное шифрование данных, что стало стандартом для предотвращения наиболее распространенных хакерских атак в интернете;
- Широкое внедрение и общедоступный код упрощают работу с SQLite, но также увеличивают площадь его атаки. Это его наиболее часто упоминаемый недостаток. В SQLite часто обнаруживаются новые критические уязвимости, такие как недавний вектор удаленной атаки под названием Magellan;
- Хотя однофайловый подход SQLite создает преимущества в скорости, нет простого способа реализовать многопользовательскую среду с помощью этой системы.
Основные характеристики баз данных
MySQL — свободная реляционная система управления базами данных. Разработку и поддержку MySQL осуществляет корпорация Oracle, получившая права на торговую марку вместе с поглощённой Sun Microsystems.
PostgreSQL (произносится «Пост-Грэс-Кью-Эл») — свободная объектно-реляционная система управления базами данных (ORDBMS) (по-русски ОРСУБД или просто СУБД) основанная на POSTGRES, версии 4.2, которая была разработана в Научном Компьютерном Департаменте Беркли Калифорнийского Университета.
| Параметры | MySQL | PostgreSQL |
|---|---|---|
| Краткое описание | Широко используемая свободная реляционная система управления базами данных | Широко используемая свободная реляционная система управления базами данных |
| Основная модель хранения данных | Реляционная база данных | Реляционная база данных |
| Дополнительная модель хранения данных | База данных типа Key/Value, документно-ориентированная база данных | База данных типа Key/Value, документно-ориентированная база данных |
| Вебсайт | ||
| Документация | ||
| Разработчик | 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 стандартом |
Заключение
Базы данных должны обязательно быть оптимизированы для окружения, в котором они будут использоваться. Исторически так сложилось, что MySQL ориентировалась на максимальную производительность, а Postgresql разрабатывалась как база данных с большим количеством настроек и максимально соответствующую стандарту. Но со временем Postgresql получила много улучшений и оптимизаций.
В большинстве случаев для организации работы с базой данных в MySQL используется таблица InnoDB, эта таблица представляет из себя B-дерево с индексами. Индексы позволяют очень быстро получить данные из диска, и для этого будет нужно меньше дисковых операций. Но сканирование дерева требует нахождения двух индексов, а это уже медленно. Все это значит, что MySQL будет быстрее Postgresql только при использовании первичного ключа.
Вся заголовочная информация таблиц Postgresql находится в оперативной памяти. Вы не можете создать таблицу, которая будет не в памяти. Записи таблицы сортируются по индексу, а поэтому их можно очень быстро извлечь. Для большего удобства можно применять несколько индексов к одной таблице.
В целом PostgreSQL работает быстрее MySQL, как показали тесты, примерно в 2 раза.
Итоги
Важное замечание – не пытайтесь сразу все задачи решить в рамках одной СУБД. Это более чем нормально иметь несколько разных типов СУБД
Так же, не пытайтесь сразу определиться с производителем СУБД, или связать свою жизнь с одним конкретным брендом.
При выборе типа СУБД следует, прежде всего, исходить из типа решаемых задач, типов обрабатываемых данных, перспектив роста и масштабирования.
Обращайте так же внимание на популярность и наличие широкого круга разработчиков и средств разработки – это даст вам возможность, при необходимости, найти ответ на возникший вопрос быстро. В данной статье я намеренно не делаю акцент на выбор между облачными и on-premise решениями — эта тема одной из следующих статей
В данной статье я намеренно не делаю акцент на выбор между облачными и on-premise решениями — эта тема одной из следующих статей.
Итак, в таблице представленной ниже, кратко собрано то, что описано выше в статье.
|
Тип СУБД |
Когда выбирать |
Примеры популярных СУБД |
|
Реляционные |
Нужна транзакционность; высокая нормализация; большая доля операций на вставку |
Oracle, MySQL, Microsoft SQL Server, PostgreSQL |
|
Ключ-значение |
Задачи кэширования и брокеры сообщений |
Redis, Memcached |
|
Документные |
Для хранения объектов в одной сущности, но с разной структурой; хранение структур на основе JSON |
CouchDB, MongoDB, Amazon DocumentDB |
|
Графовые |
Задачи подобные социальным сетям; системы оценок и рекомендаций |
Neo4j, Amazon Neptune, InfiniteGraph, InfoGrid |
|
Колоночные |
Хранилища данных; выборки со сложными аналитическими вычислениями; количество строк в таблице превышает сотни миллионов |
Vertica, ClickHouse, Google BigTable, Sybase \ SAP IQ, InfoBright, Cassandra |
Надеюсь данная статья оказалась полезной.
В следующих статьях посмотрим на выбор между облачными и on-premise СУБД, платными и бесплатными, и многое другое.
