MySQL Version 8 Adds Document Store, Performance and Security Improvements

Image result for MySQL Version 8 Adds Document Store, Performance and Security Improvements

Oracle has released version 8 of their open-source relational database management system MySQL. This major release introduces many improvements, but what will probably gain most attention is the introduction of document based storage, allowing the developer to work with both traditional relational and “NoSQL” document data in the same database. The release also brings performance and security enhancements, and a change in default character-set to promote “mobile-first” development.

MySQL introduced JSON support in MySQL 5.7 and now version 8.0 brings the MySQL Document Store, which allows developers to work with schema-less JSON document collections alongside relational tables. The MySQL Document store is made up of a collection of technologies. There is a new client protocol, the X Protocol, and the X Plugin which enables the MySQL Server to use the X Protocol. The new X DevAPI is an asynchronous developer API for CRUD and SQL operations on top of X Protocol. MySQL Connectors are drivers that allow developers to operate on the API and document store using the main development languages of Java, Python, Node, .Net and C++.  The final piece in the document store is the MySQL Shell which provides an interactive Javascript, Python, or SQL interface to support development and administration for the MySQL Server.

Geir Høydalsvik, who has worked on the mySQL database team since 2008, spoke to InfoQ and stated that:

The combination of Document Store and Relational database is a key feature. Users can now have one rock solid database system serving both SQL and NoSQL, with JSON supporting the DocStore.

MySQL is not the first database in the open source arena to create the ability to store both relational and document-based data. PostgreSQL for example, has had the capability for some time.

Version 8.0 of MySQL also brings significant performance improvements over previous versions. The release notes state that MySQL 8.0 is twice as fast as version 5.7. Benchmark data shows that this is measured in terms of queries per second, with the difference in performance appearing as the number of users rises. The benchmark data also shows that version 8 of MySQL achieves a new record for the platform of up to 1.8 million queries per second. Reliability is improved by the consolidation of data dictionary metadata, previously stored in multiple file formats and locations into a set of SQL tables stored within the database itself stored using the default InnoDB storage engine.

Several enhancements improve security. One of these is the introduction of SQL Roles. In common with most applications, a role in MySQL 8 represents a named collection of privileges. Roles in MySQL can now be created, granted, dropped and applied on a per session basis. The database also has a new SQL function, ROLES_GRAPHML(), that returns a graphml document representing role subgraphs. Another security enhancement is the introduction of restrictions on password reuse. MySQL already supports a password expiration policy which enforces users to change their passwords based on time, but the new version introduces the ability to control what can and cannot be used as a password. This restricts password reuse and thus forces users to supply new strong passwords with each change.

The default character set has been changed from latin-1 to utf8mb4. Driven by the mobile world, where emojis and a variety of charsets need to coexist, Unicode/ UTF-8 encoding has become ubiquitous, hence the switch. Due to performance reasons, the previous utf8mb3 has been deprecated.

One of the other characteristics of mobile applications is their use of a user’s locations to provide content. MySQL 8.0 builds on the existing GIS support to add geography and spatial reference systems (SRS).

Version 8.0 also brings several SQL enhancements. One of these is the introduction of common table expressions (CTE), part of the ANSI SQL 99 (aka “SQL 3”) specification. These are named temporary result sets that exists within the scope of a single statement which can be referred to later within the statement.  A number of window functions have also been added. Window functions perform calculations using rows related to each row from a query and are a powerful tool for reducing code complexity.

Full details of the release can be found in the release whitepaper.