Preview: Top MySQL 8 Features

DZone 's Guide to

Preview: Top MySQL 8 Features

MySQL 8 contains many long-awaited features and bug fixes. There is one thing that we can say for sure: it will almost certainly have been worth the wait!

· Database Zone ·
Free Resource

Although there is no official software release for MySQL 8.0 as of yet, most insiders believe that it’s likely to arrive sometime in 2018. In the meantime, Oracle has officially announced a tantalizing list of over two hundred new features! We recently covered replication performance enhancements. Today’s article will cover some of the other exciting enhancements we can expect when the production release of MySQL 8 hits the market.

New Database Roles

A role is a named collection of privileges that define what a user can and cannot do within a database. Roles play a vital part of database security by limiting who can connect to the server, access the database, or even access individual database objects and data.

Although prior to version 8, MySQL did provide a set of privileges and administrative roles, the upcoming release will also support a set of flexible and properly architected roles, allowing DBAs to:

  • Create and drop roles, grant to roles
  • Grant roles to roles, grant roles to users
  • Limit hosts that can use roles, define default roles
  • Decide what roles are applicable during a session
  • And even visualize roles with SQL function ROLES_GRAPHML() 

Since each role packs multiple privileges, DBAs don’t have to remember exactly which permissions a user requires. Roles are also very easy to set up:

  • Creating a new role:
    CREATE ROLE ‘app_developer’, ‘app_read’, ‘app_write’;
  • Assigning privileges to roles:
    GRANT SELECT ON app_db.* TO ‘app_read’;
  • Assigning the role to a user:
    GRANT ‘app_read’ TO ‘read_user1’@’localhost’, ‘read_user2’@’localhost’;
  • Index Hiding, AKA “Invisible” Indexes

    Hidden indexes are similar to disabled indexes, except that in the case of the former, index information remains fully up to date and maintained by data manipulation language (DML); it’s just invisible to the MySQL Optimizer. This feature is useful in hiding an index you suspect you don’t need without actually dropping it. By marking an index as invisible, the MySQL optimizer will no longer use it. You can then monitor your server and query performance to decide whether to delete it or re-activate it if it turns out that the index does provide improved performance.

    This feature has two main uses.

    Soft Delete

    This was the situation described above where you don’t think an index is utilized anymore. In this case, rendering the index invisible is akin to throwing it in the recycle bin. In that state, it’s still possible to restore it.

    First, you would render the index invisible:


    You can revert it, i.e. make it visible again, if need be:


    If it is safe to drop the index:


    Staged Rollout

    Adding a new index can not only change existing execution plans, like all changes; it also introduces the risk of regression. That’s where your database becomes unstable due to multiple changes and additions that may not have been fully tested as a whole.

    Invisible indexes allow you to stage all changes by putting the database in a “prepared” state.

    You can add an index invisibly at an opportune time:

    ALTER TABLE Country ADD INDEX c (Continent) INVISIBLE;

    Then activate the index after testing the changes to everyone’s satisfaction:


    Improved JSON and Document Support

    MySQL 5.7 introduced JSON support to compete with NoSQL databases that use JSON natively. That included the introduction of a JSON data type, virtual columns, and a set of approximately 20 SQL functions that allow you to manipulate and search JSON data on the server side. MySQL 8 continues to build on 5.7’s foundation by improving performance, as well as by adding:

    • Functions to perform search operations on JSON values to extract data from them report whether data exists at a location within them or report the path to data within them.
    • Aggregation functions that let MySQL-native structured data and semi-structured JSON data be merged in a query.
    • Document-store abilities.

    Searching JSON Data

    Searching through JSON data is now easier thanks to the JSON_EXTRACT() function. It returns data from a JSON document (the first argument) selected from the parts of the document matched by subsequent path arguments. For example, here is a query the fetches the second data element from a JSON-formatted array:

    mysql> SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');
    | JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |
    | 20                                         |

    Aggregation Functions

    The MySQL 8.0 lab release added the JSON_ARRAYAGG() and JSON_OBJECTAGG() aggregation functions that can be utilized to combine data into JSON arrays/objects.

    Consider the following table:

    | key  | group  | val    |
    | key1 | g1     | v1     |
    | key2 | g2     | v1     |
    | key3 | g3     | v2     |

    The following query selects the keys as a JSON array:

    mysql> SELECT JSON_ARRAYAGG(`key`) AS `keys` FROM t1;
    | keys                     |
    | [ "key1",                |
    |   "key2",                |
    |   "key3" ]               | 
    |                          |

    Document-Store Abilities

    Shortly after the JSON data type emerged came the MySQL Document Store feature. It was designed for developers who are not well-versed in SQL but want to enjoy the many benefits that a relational database provides. In MySQL 8, reads and writes to the document store use transactions so that changes to JSON data may be rolled back. Moreover, documents may be stored in open GeoJSON format for geospatial data so that they can be indexed and searched according to proximity.

    In order to function as a document store, MySQL employs the X Plugin and the MySQL Shell interface. It communicates with a MySQL using the X Protocol via the X DevAPI, a modern programming interface that provides support for established industry standard concepts such as CRUD operations. It is implemented in several programming languages, including Java, JavaScript, Node.JS, Python, and C++, with more on the way.

    Say that you added the following JSON data to the document store:

        GNP: .6,
        IndepYear: 1967,
        Name: "Sealand",
        _id: "SEA",
        demographics: {
            LifeExpectancy: 79,
            Population: 27
        geography: {
            Continent: "Europe",|
            Region: "British Islands",
            SurfaceArea: 193

    You could then retrieve the document by ID (the _id field) using the find() method. Here is the call using the JavaScript shell:

    mysql-js> db.countryinfo.find("_id = 'SEA'")
            "GNP": 351182,
                 SurfaceArea: 193

    Configuration Persistence

    Changing configuration during MySQL runtime is commonly done using SET GLOBAL. The disadvantage of this technique is that the changes will not survive a server restart. As of MySQL 8, configuration changes applied via the SET PERSIST command will survive a MySQL server restart. For instance:

    SET PERSIST max_connections = 500;

    SET PERSIST works with any configuration variables, including offline_moderead_only, etc. One of the best things about SET PERSIST is that it does not require filesystem access, making it particularly useful when you don’t have system file access.

    Unicode UTF-8 Encoding

    With the precipitous rise of UTF-8 encoding in recent years, it has emerged as the dominating character encoding for the web and modern applications. UTF-8’s dominance has been partially driven by “adopted words” from foreign languages, but more likely, the main factor has been its support for emojis.

    In a move that will do doubt make life easier for the vast majority of MySQL users, version 8 no longer uses latin1 as the default encoding to discourage new users from choosing a problematic legacy option. The recommended default character set for MySQL 8 is now utf8mb4, which is intended to be faster than the now-deprecated utf8mb3 character set and also to support more flexible collations and case sensitivity.

    A collection of UTF-8 emojis:

    Common Table Expressions

    Derived tables have existed in MySQL for a while now (since version 4.1, in fact). So, what is a derived table, you may ask? A derived table is a subquery in the FROM clause:

    SELECT … FROM (SELECT …) AS derived_table;

    You could think of common table expressions (CTEs) as improved derived tables — at least in their non-recursive form. CTEs can be recursive, as well, but we're getting a bit ahead of ourselves with that.  The purpose of CTEs is to simplify the writing of complex SQL. You can always recognize them by the WITH keyword at the start of the SQL statement. For instance:

    WITH t1 AS (SELECT * FROM tbl_a WHERE a='b')
    SELECT * FROM t1;

    Here’s the same query rewritten using a derived table:

    SELECT *
    FROM (SELECT * FROM tbl_a) AS t1
    WHERE t1.a='b';

    Recursive CTEs

    A recursive CTE is a set of rows that is built iteratively like a programming loop. An initial set of rows is fed into the process, each time producing more rows until the process ceases to produce any additional rows. Syntactically, a recursive CTE refers to itself in a subquery; the “seed” SELECT is executed once to create the initial data subset, then the recursive SELECT is repeatedly executed to return subsets of data until the complete result set is obtained.

    Similar to Oracle’s CONNECT BY, recursive CTEs are useful for digging in hierarchies such as parent/child and part/subpart relationships.

    Recursive CTEs typically take this form:

    WITH RECURSIVE cte_name AS
      SELECT ...      <-- specifies initial set
      SELECT ...      <-- specifies how to derive new rows

    Here’s a simple example that outputs 1 to 10:

    ( SELECT 1 AS a 
      UNION ALL 
      SELECT 1+a FROM qn WHERE a<10
    SELECT * FROM qn;
    | a    |
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    |    6 |
    |    7 |
    |    8 |
    |    9 |
    |   10 |

    CTEs can also be utilized within SELECTINSERTUPDATE, and DELETE statements. For example, taking our 1-to-10 example, we can name the column using the my_cte(n) syntax and use the result of my_cte to create a table called numbers:

    INSERT INTO numbers
    WITH RECURSIVE my_cte(n) AS
      SELECT 1
      SELECT 1+n FROM my_cte WHERE n<10
    SELECT * FROM my_cte;

    Querying the numbers table confirms that it contains numbers from 1 to 10:

    SELECT * FROM numbers;
    | n    |
    |    1 |
    |    2 |
    |    3 |
    |    4 |
    |    5 |
    |    6 |
    |    7 |
    |    8 |
    |    9 |
    |   10 |

    Window Functions

    An extremely useful feature, window functions have enjoyed support on many other database products for some time now. A window function performs a calculation across a set of rows that are related to the current row, similar to an aggregate function. However, unlike aggregate functions, a window function does not cause rows to become grouped into a single output row. This allows you to perform aggregate calculations across multiple rows while still having access to individual rows “in the vicinity” of the current row.

    The currently supported functions include:

    Name Description
    CUME_DIST() Cumulative distribution value.
    Rank of current row within its partition, without gaps.
    Value of argument from the first row of the window frame.
    Value of argument from row lagging current row within the partition.
    Value of argument from the last row of the window frame.
    Value of argument from row leading current row within the partition.
    Value of argument from the Nth row of the window frame.
    Bucket number of the current row within its partition.
    Percentage rank value.
    Rank of current row within its partition, with gaps.
    The number of current row within its partition.

    For example, suppose we have a table that contains sales figures. We can aggregate total sales by country:

    SELECT country, SUM(profit) AS country_profit
    FROM sales
    GROUP BY country
    ORDER BY country;
    | country | country_profit |
    | Finland |           1610 |
    | India   |           1350 |
    | USA     |           4575 |

    By contrast, window operations do not collapse groups of query rows into a single output row. Instead, they produce a result for each row. Like the preceding queries, the following query uses SUM(), but this time as a window function:

    SELECT year, country, product, profit,
           SUM(profit) OVER() AS total_profit,
           SUM(profit) OVER(PARTITION BY country) AS country_profit
    FROM sales
    ORDER BY country, year, product, profit;
    | year | country | product    | profit | total_profit | country_profit |
    | 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
    | 2000 | Finland | Phone      |    100 |         7535 |           1610 |
    | 2001 | Finland | Phone      |     10 |         7535 |           1610 |
    | 2000 | India   | Calculator |     75 |         7535 |           1350 |
    | 2000 | India   | Calculator |     75 |         7535 |           1350 |
    | 2000 | India   | Computer   |   1200 |         7535 |           1350 |
    | 2000 | USA     | Calculator |     75 |         7535 |           4575 |
    | 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
    | 2001 | USA     | Calculator |     50 |         7535 |           4575 |
    | 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
    | 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
    | 2001 | USA     | TV         |    100 |         7535 |           4575 |
    | 2001 | USA     | TV         |    150 |         7535 |           4575 |

    The main part of this query is SUM(profit) OVER (…), which are the window functions. PARTITION BY divides rows into groups while SUM() tallies the sales figures for the specified group (country).


    From new database roles and index hiding to recursive common table expressions and window functions, MySQL 8 contains many long-awaited features and bug fixes. As to when it will be released, we can only guess at this point, since the production release was originally scheduled for October of 2017. There is one thing that we can say for sure: it will almost certainly have been worth the wait!

    database, database roles, indexes, json, mysql, mysql 8

    Published at DZone with permission of Shree Nair . See the original article here.

    Opinions expressed by DZone contributors are their own.

    {{ parent.title || parent.header.title}}

    {{ parent.tldr }}

    {{ parent.urlSource.name }}