MySQL 8.0 features explained

MySQL 8.0 was released back in 2018 with security and performance improvements over the previous version, MySQL 5.7. You might be wondering what’s the story behind jumping from version 5.7 to 8.0, skipping versions 6 and 7. Well, MySQL AB was the company that originally owned MySQL. They were working on version 6.0 when Sun Microsystems acquired them. After this, Sun was acquired by Oracle. The company then explained, “Due to the many new and important features we were introducing in this MySQL version, we decided to start a fresh new series.” That’s how MySQL 8.0 was born. In this article, we go over some of these new features in MySQL 8.0, how to use them, and whether you should consider upgrading from an older MySQL version.

Improved performance and scalability

There are several improvements to the InnoDB storage engine, resulting in better performance and scalability in MySQL 8.0. Some of these improvements include support for native partitioning, improvements to the InnoDB buffer pool, better isolation of transactions, better concurrency control, and much more. These improvements better position MySQL 8.0 as a database to handle large and distributed databases while still being scalable and performant.

Transactional data dictionary

In earlier versions of MySQL, a file-based system, along with non-transactional tables, were used to store metadata. In MySQL 8.0, a new transactional data dictionary is used. Metadata is information about database objects that can include columns, indexes, foreign keys, etc. The data dictionary is a collection of all of these metadata fields displayed using the INFORMATION_SCHEMA or SHOW commands. Rather than using a file-based system, all metadata is now stored in transactional InnoDB tables. These InnoDB tables are more reliable and offer better performance in comparison to the previous implementation.

Window functions

You might have used the GROUP BY statement in SQL that aggregates results for the columns you are grouping by. One thing you might have noticed is that the individual rows are consolidated together to produce the aggregate result. It means that multiple rows with similar values in the grouping column(s) are combined, and only the resulting aggregate value for each group is displayed. With window functions in MySQL 8.0, you get the aggregate function computed for each row without removing any of them. Use the SQL statements below to create a grades table for students.

CREATE TABLE grades (
  id int NOT NULL PRIMARY KEY,
  student_id int NOT NULL,
  department varchar(255) NOT NULL,
  grade int NOT NULL
);

Insert some data using the command below:

INSERT INTO grades (id, student_id, department, grade) VALUES (1, 1341, "Math", 90), (2, 1341, "Science", 85), (3, 1342, "Math", 95), (4, 1342, "Science", 80), (5, 1343, "Math", 100), (6, 1343, "Science", 100);

If you use the usual GROUP BY statement to retrieve the average mark of each student, you’ll do something like this: Notice how there’re only 3 rows in the result set above. Let’s do the same example using a window function.

SELECT STUDENT_ID, AVG(GRADE) OVER() AS AVG_GRADE FROM grades;

This returns a result set containing all rows of the grades table with the average computed for each row.

STUDENT_ID  AVG_GRADE
1341        91.6667
1341        91.6667
1342        91.6667
1342        91.6667
1343        91.6667
1343        91.6667

Inside the OVER() clause, you can use the ORDER BY clause or the PARTITION BY clause.

Common table expressions (CTEs)

A common table expression, sometimes known as a WITH query, is a result set that exists temporarily while executing a statement that can be used later in the same statement. In essence, it helps in creating more complex queries with the help of simpler blocks that use temporary tables. Let’s take a look at an example of this MySQL 8.0 feature. Create a table using the DDL command below:

CREATE TABLE student(
  id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
  first_name varchar(255),
  last_name varchar(255),
  age int,
  date_of_birth date,
  enrollment_date date,
  email varchar(255)
);

Now, insert some data:

INSERT INTO STUDENT (id, first_name, last_name, age, date_of_birth, enrollment_date, email)
VALUES
(1341, "Leo", "Collins", 17, "2006-04-12", "2023-05-01", "leo@hotmail.com"),
(1342, "Cindy", "Collins", 17, "2005-11-03", "2023-05-01", "cindy25@gmail.com"),
(1343, "Leo", "Griffin", 17, "2006-09-09", "2023-05-01", "leo.griffin@gmail.com");

We’ll use this along with the grades table from the previous section. Don’t worry too much about the data types used. Let’s try to retrieve the ID, name, and marks in math using a CTE.

WITH MATH_STUDENTS AS (
  SELECT ID, GRADE FROM grades WHERE DEPARTMENT = "Math"
)
SELECT STUDENT.ID, STUDENT.FIRST_NAME, STUDENT.LAST_NAME, MATH_STUDENTS.GRADE FROM STUDENT JOIN MATH_STUDENTS ON STUDENT.ID = MATH_STUDENTS.STUDENT_ID;

The WITH query houses the temporary table containing the IDs and grades from the grades table for the MATHS department. We then join this subquery with the STUDENT table and retrieve the results needed. You can see how we used the common table expression just like a normal table. It has improved readability of the query as well. Limited by the Vitess MySQL implementation, PlanetScale does not support the WITH query yet.

ID    FIRST_NAME  LAST_NAME   GRADE
1341  Leo         Collins     90
1342  Cindy       Collins     95
1343  Leo         Griffin     100

JSON enhancements

You can use the improved JSON functions in MySQL 8.0 to manipulate JSON data directly in the MySQL database. These are some of the new JSON functions:

• JSON_EXTRACT
• JSON_MERGE
• JSON_ARRAY

There is also support for validating the schema of a JSON document stored in the database. This enforces additional data integrity for the JSON data. There are quite a few of these and you can read more about them in the MySQL 8.0 reference manual for JSON functions.

Invisible indexes

Before getting into invisible indexes, let's define indexes. We use indexes to improve the speed with which we retrieve data. Indexes are specialized structures designed to enhance data retrieval speed. They function as auxiliary tables, containing an indexing column along with reference pointers to the corresponding records in the actual table. To create an index in MySQL 8.0 or below, you can use the following command:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_1);

You can use the statement “CREATE UNIQUE INDEX” to create an index where two rows cannot have the same index value and also multiple column names within the parentheses. All regular indexes are used by the MySQL query optimizer to ensure retrieval of only the required rows in the least possible time, using indexes wherever possible. Now, invisible indexes are those that are hidden from the MySQL query optimizer. What does this mean exactly and how is it useful? To create an invisible index, you can use the exact same syntax as above with the addition of the keyword “INVISIBLE.”

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_1) INVISIBLE;

This index gets omitted by the query optimizer. Think of this scenario. You are working with a very large table that has a few indexes. But now, you want to further optimize it by removing some indexes and adding new ones. Removing them on large tables can have repercussions if you anticipate needing them in the future, as rebuilding an index can be a resource-intensive operation. So the best way to overcome this is by using invisible indexes. Just convert an existing index to an invisible index using the following command and it gets omitted by the query optimizer. You can now add your new indexes and try out different scenarios to improve your query lookups.

ALTER TABLE TABLE_NAME ALTER INDEX INDEX_NAME INVISIBLE;

Roles

Roles in MySQL 8.0 are a set of privileges with a pre-defined name. You can assign users roles, create new roles, or delete existing ones. To create a new role, use the following command:

CREATE ROLE 'read_admin';

The role creation follows the same syntax as that of user account creation. The actual syntax is

CREATE ROLE ‘role_name’@’host_name’;

If the “@’host_name’” part is not present, it defaults to “@.” To grant permissions to a role, you can use the following command:

GRANT SELECT ON STUDENT TO 'read_admin';

This grants the ability for the “read_admin” role to perform SELECT queries from the STUDENT table. To drop a role, use the following command:

DROP ROLE 'read_admin';

Conclusion

With that, we have just scratched the surface of all the new features from MySQL 8.0. These features are aimed at improving developer experience and overall performance and scalability. Don’t forget to check out the deprecated and removed features as well before deciding to perform any database upgrades. You can check the compatibility of your app and see whether these new improvements make sense for your organization.