TOP 10 MySQL 8.0 features for developers

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.

TOP 10

1. MySQL Document Store
2. Default to utf8mb4
3. JSON enhancements
4. CTEs
5. Window Functions
6. Descending Indexes
7. Better Optimizer Cost Model
8. MySQL Server Components
9. Improvement in GIS
10. 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, …

JSON enhancements

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 JSON_UNQUOTE(JSON_EXTRACT())
• new aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG()
• addition of JSON_PRETTY()
• new JSON utility fonctions like JSON_STORAGE_SIZE(), JSON_STORAGE_FREE()

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.

Window Functions

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:

Descending Indexes

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.

Some examples:

• 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 NOWAIT and SKIP
LOCKED options with SELECT ... FOR
SHARE and SELECT ... FOR
UPDATE 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.