MySQL 8.0 RC2 has just been released with a lot of new features compared to MySQL 5.7. In this blog post, I will list the top 10 new features that should excite developers.
These functionalities are presented in descending order of preference of our MySQL Community.
- MySQL Document Store
- Default to utf8mb4
- JSON enhancements
- Window Functions
- Descending Indexes
- Better Optimizer Cost Model
- MySQL Server Components
- Improvement in GIS
- InnoDB NO WAIT & SKIP LOCKED
MySQL Document Store
This is the most expected and liked feature in MySQL 8.0 … and it’s so comprehensible !
I’m myself very excited about MySQL Document Store, I presented it all around the globe for almost one year and I got so many good feedback. Why is MySQL DS so nice ? Because with a single solution you can now handle SQL and NoSQL. You can also mix both world taking advantages of both languages. On the same data you can execute CRUD commands but at the same time you can run more complicated queries in SQL joining multiple tables and/or collections.
Also as the backend is the well known and robust InnoDB engine, MySQL Document Store is fully ACID compliant. And as it’s all inside MySQL, you can also benefit from everything you are familiar with and transpose it on Document Store: replication, performance_schema, …
I’ve presented an overview of MySQL Document Store during pre-FOSDEM MySQL Day on Feb 2nd.
Default to utf8mb4
With MySQL 8.0, we of course focus also on modern web applications… this means mobile ! And who says mobile, says also emoji and a multitude of charsets and collations that need to coexist.
This is why decided to change the default character set from latin-1 to utf8mb4. MySQL supports now the latest Unicode 9.0 with new collations based on DUCET, accent and case sensitive collations, Japanese, Russian, …
MySQL brings new improvements in the collection of JSON functions. This is an overview of some nice improvements:
- ->> has been added, this is the equivalent of
- new aggregation functions
- addition of
- new JSON utility fonctions like
One of the most impressive function we added in MySQL 8.0 for JSON documents is the
JSON_TABLE() function. This function accepts JSON data and returns it as a relational table. Columns and casting can be specified. Also the output of
JSON_TABLE() can be used with SQL as a regular relational table meaning for JOINS, aggregation, … An example can be found on @stoker’s blog and in the manual of course.
And more ! Some stuff don’t only affects developers, but for example, MySQL performs partial, in-place updates of JSON column values, which is more efficient than completely removing an existing JSON value and writing a new one in its place, as it was done previously when updating any JSON column. Replication benefits from this too !
Common Table Expresssions (CTEs)
Common Table Expressions (also known as WITH queries) have been added to MySQL 8.0. A CTE is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statements, recursively or not.
A window function, for each row from a query, performs a calculation using rows related to that row. It’s like a GROUP BY but preserving rows rather than collapsing them.
This is the list of the current implemented Window Functions as in MySQL 8.0.4:
||Cumulative distribution value|
||Rank of current row within its partition, without gaps|
||Value of argument from first row of window frame|
||Value of argument from row lagging current row within partition|
||Value of argument from first row of window frame|
||Value of argument from row leading current row within partition|
||Value of argument from N-th row of window frame|
||Bucket number of current row within its partition.|
||Percentage rank value|
||Rank of current row within its partition, with gaps|
||Number of current row within its partition|
Before MySQL 8.0, when DESC was used in an index definition, this flag was ignored. This is not the case anymore! Now the key values are stored in descending order. Previously, indexes could be scanned in reverse order but at a performance penalty. A descending index can be scanned in forward order, which is more efficient.
Better Optimizer Cost Model
The new Optimizer Cost Model now takes into account how much data is cached (located in memory) and how much data is still on disk. For more information, please check Øystein’s blog post.
MySQL Server Components
This feature is not specific to application developers using MySQL but to developers wanting to extend MySQL server capabilities. This is much more flexible way to add features that is easier to maintain than plugins. For more information, please check the documentation.
Improvement in GIS
With MySQL 8.0, the world is not only flat anymore, but it can be ellipsoidal too ! The GIS support in MySQL 8.0 has been extended a lot to match and exceed the GIS feature set of PostgreSQL.
- axes have units
- geographic coordinate system wrap around
- geographic axes are not orthogonal
- geographic axes order matters
- relevance of axis direction
InnoDB NO WAIT & SKIP LOCKED
InnoDB in MySQL 8.0 does now have a better handling of hot row contention. InnoDB supports
SKIP options with
SELECT ... FOR and
SELECT ... FOR locking read statements.
NOWAIT causes the statement to return immediately if a requested row is locked by another transaction.
SKIP LOCKED removes locked rows from the result set. See Locking Read Concurrency with NOWAIT and SKIP LOCKED.
Of course the list of nice MySQL 8.0 features doesn’t end here, for example the support for Regular Expressions is also something very interesting that just came out with 8.0.4. The new SQL
GROUPING() function, IPV6 and UUID manipulation new operations, more optimizer hints, …
I hope this give you a nice overview of what kind of requests is driven MySQL’s innovation. The next post will be about the MySQL 8.0 features that should make developers happy 😉
!! UPDATE !!
As we keep improving MySQL 8.0 along releases, there is another reason to upgrade to MySQL 8.0, maybe one of the most required features when having to deal with large tables. Developers won’t be afraid to add columns to their schema anymore : Instant ADD Column !
InnoDB INSTANT ADD COLUMN
Thank to the new Data Dictionary it’s now possible to perform a metadata change and add a new column directly at the end of a table without having to rebuild it ! This is the end of ALTER statements running for ever and blocking everything every time a developer wanted to add a column to a table.
And this is only the beginning of the journey to instant DDLs.