Menu

MySQL 8.0 RC1 – Highlights

The long awaited first release candidate of MySQL 8.0 is now available. The theme of this release is “making MySQL better for modern apps”. What does that mean exactly?

A modern application is mobile first. Mobile-first is not just a theme applied to an existing app, it is about using context about the user (such as their location) and reducing the clicks required for a transaction.

Unicode (or more specifically UTF-8 encoding) has become universal even in English speaking markets. A key driver is mobile applications, where emojis are frequently used as character input. To support modern applications, it is important to have first-class support for UTF-8 out of the box.

Modern applications are developed with a premium on time-to-market. You don’t always know how what you developed will be used, and want to be able to respond fast to the directions the market takes you. It is important to have flexibility, and a large part of this story is schemaless JSON support.

“8.0” also marks a departure from the 5.x numbering series, which we have used since 2005. We felt that using a fresh number was important to communicate the depth of some of the core changes. Skipping 6.x and 7.x was intentional to avoid confusion with a previous alpha release and MySQL Cluster.

Highlights

Improved JSON Support

JSON support was perhaps the most well received feature of MySQL 5.7, and with MySQL 8.0 we have expanded it with a number of useful additions. In terms of SQL Functions, we have added functions to check the on-disk size of the native JSON type, array and object aggregate functions, and a pretty function for formatting.

JSON path expressions now support ranges, which means that you can now extract the top n or last item from an array:

Document Store

The MySQL document store allows you to treat MySQL like a document database, with a set of NoSQL CRUD APIs to access your data. First introduced in MySQL 5.7, the document store now provides you with consistent reads-and-writes. This gives it a distinct advantage over other NoSQL databases, in that developers don’t need to give up transactional semantics. Data created in the Document Store can also be accessed via the regular MySQL SQL protocol.

In addition to that MySQL document store has expanded its indexing capabilities. To enhance spatial searches, we have added support for spatial indexing of GeoJSON data within MySQL JSON documents, enabling efficient spatial searches of documents in the MySQL Doc Store. On the full text search side of things we now allow full text indexing of entire or part of a JSON document. With this, users can more flexibility search all or part of the data within your JSON documents within MySQL.

If you are not familiar with Window Functions, the easiest way to describe them is like a GROUP BY, but not affecting the results. You can picture this as peeking through a window to look at the other rows, such as looking at the aggregate sales performance of an employee, while enumerating through a list of sales:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

SELECT

employee,

date,

sale,

SUM(sale)OVER(PARTITION BY employee)ASsum

FROM sales;

+----------+------------+------+------+

|employee|date|sale|sum|

+----------+------------+------+------+

|odin|2017-03-01|200|900|

|odin|2017-04-01|300|900|

|odin|2017-05-01|400|900|

|thor|2017-03-01|400|1200|

|thor|2017-04-01|300|1200|

|thor|2017-05-01|500|1200|

+----------+------------+------+------+

Window Functions are extremely powerful, and reduce code complexity significantly. Two of my favourite examples are LAG() and LEAD(), which can be used to access the previous or next row in a set.

Better Handling of Hot Rows

We have added support for the lock modifiers SKIP LOCKED and NOWAIT. These two features allow you to better manage situations where you have tables with hot row contention – such as in the cases of a hot SKU in an commerce application or a set of worker threads that are all reading from the same table trying to find new rows to process.

When encountering locked rows, the default behaviour is to queue waiting for up to 50 seconds for the lock holder to release. You now have the option to error immediately (NOWAIT) or non-deterministically skip past locked rows (SKIP LOCKED). You can even use both at the same time:

1

2

3

4

5

6

SELECT seat_no

FROM seats JOIN seat_rows USING(row_no)

WHERE seat_no IN(3,4)ANDseat_rows.row_no IN(12)

ANDbooked='NO'

FORUPDATE OF seats SKIP LOCKED

FORSHARE OF seat_rows NOWAIT;

Unicode 9.0

We’ve upgraded our Unicode support to the latest 9.0 standard, and switched from latin1 to utf8mb4 for the default character set. This means that you can now have bacon out of the box:

On a more serious note, Unicode (or more specifically UTF-8 encoding) has become universal even in English speaking markets. A key driver is mobile applications, where emojis are frequently used as character input.

The challenge with variable length character encodings has always been performance, which the team has worked hard on improving during the development of 8.0. Current users of MySQL 5.7 should notice a substantial performance increase by upgrading to one of the new collations in MySQL 8.0, and we have also deprecated the earlier utf8mb3 character set.

Improvements to Query Consistency

With SQL being a declarative language, a query is similar to what a street address is to GPS navigation. That is to say it conveys a final destination, and not a set of directions on how to get there. The optimizer is the part of the database system which “picks” the best execution plan (to extend the street analogy, is Google Maps). MySQL 8.0 contains several improvements designed to help picking the best plan more consistently.

Optimizer Histograms (not to be confused with Performance Schema histograms) have been introduced to help with cases where a high amount of data skew would have previously led to a poor execution plan choice.

The cost model has been refined to consider the effect of how much of the table or index is resident in memory. For example: On a given query, it may be preferred to range scan only when a large percentage of the index is in memory. The individual constants used in the cost model are user-configurable, and in MySQL 8.0 the usability has been improved slightly by showing the default values:

Additional query hints have also been added for finer grain control of the optimizer if it makes incorrect choices. Database Administrators can also insert these hints by using the query rewrite feature on the MySQL server side.

GIS

Modern applications are mobile, and (besides using emoji!), one of the characteristics of mobile is that it frequently make use of a user’s location to provide context. In MySQL 5.7, we made the strategic choice to switch from our home-grown GIS functions to using Boost.Geometry as a core library. We have since become regular contributors to Boost, employing two developers to work on it full time.

In MySQL 8.0 GIS is extended to support Geography and Spatial Reference Systems (SRS). We have also implemented standards compliant axis ordering in functions, as well as a ST_SwapXY() helper function to convert incorrectly specified data.

Cloud Friendly

It is becoming more common for MySQL to be deployed in a virtual machine, where the amount of system resources can change as the system is scaled up or down. With the new innodb_dedicated_server option, it’s possible to auto-detect the system memory, and have MySQL adjust appropriately without having to edit configuration files.

It is also more common to have restricted or zero local access (shell or filesystem) to running database servers. This restriction makes it hard for administrators to change MySQL configuration. This was one of the use-cases for the new SET PERSIST feature, which allows you to persist global variables between MySQL restarts:

Native Data Dictionary

Just as you use a database like MySQL to store your application data, MySQL must also store its meta data (schema names, table definitions etc) somewhere. Traditionally this meta data storage has been split between many different locations (.FRM, .PAR, .OPT, .TRN and .TRG files).

While there are a number of reasons why this was an important core-change, the TL;DR is a lower barrier to entry for new features, and improved reliability as we unify meta data management in transactional storage.

Invisible Indexes

Many of today’s applications run 24×7, leaving no clear maintenance windows for software upgrades or database changes. One key piece of feedback we received from our customers is that any change to indexing can have unintended consequences.

Invisible indexes are a new feature in MySQL 8.0 that provide the ability to mark an index as unavailable for use by the Optimizer. This means that the index will still be maintained and kept up-to-date as data is modified, but no queries will be permitted to make use of the index (even if the query uses a FORCE INDEX hint).

One common use case is to find out which indexes are unused via the SYS schema. You can then set them to INVISIBLE, which is the database equivalent to a Recycle Bin:

1

2

3

4

5

6

7

8

9

10

11

mysql>SELECT *FROM sys.schema_unused_indexes;

+---------------+-------------+------------+

|object_schema|object_name|index_name|

+---------------+-------------+------------+

|world|Country|p|

|world|Country|p_c|

+---------------+-------------+------------+

2rows inset(0.01sec)

mysql>ALTER TABLE Country ALTER INDEXpINVISIBLE;

Query OK,0rows affected(0.01sec)

After some time has elapsed, you can then safely DROP the index. The same process can also be used in reverse for a staged-rollout of indexing changes.

Improvements to Defaults

In total we have changed over 20 defaults from MySQL 5.7 to MySQL 8.0, as we pay close attention to improving the out of the box experience. Notable changes include several changes to replication, additional performance schema instruments enabled by default (memory, transactions, mdl), the default character set is now utf8mb4, and InnoDB now assumes SSDs by default.

The default configuration remains targeted for a system with 512M of memory, making it friendly for development environments and small cloud VMs. But in MySQL 8.0 we have made it easier to switch to a dedicated mode, where it will scale to use all system resources.

For a more detailed description of changes, we recommend users read the What Is New in MySQL 8.0 manual page when upgrading.

We are diligent in managing the technical debt of a now 20-year old code base, and with each major release target specific areas for improvement. These targets may be based on analysis of historical areas of complaints (as in the case of the native data dictionary) or intended future work, but we also take a more holistic look at how we can simplify our code base and make better use of modern libraries and C++ features.

Very Useful Article.. For additional MySQL 8.0 documentation, see the MySQL 8.0 Reference Manual, which includes an overview of features added in MySQL 8.0 (What Is New in MySQL 8.0), and discussion of upgrade issues that you may encounter for upgrades from MySQL 5.7 to MySQL 8.0 (Changes Affecting Upgrades to MySQL 8.0).

Updates to these notes occur as new product features are added, so that everybody can follow the development process. If a recent version is listed here that you cannot find on the download page (http://dev.mysql.com/downloads/), the version has not yet been released.