Detailed Explanation of New Features of Relational Database in MySQL 8.0

Preface

The latest version of MySQL 8.0 is 8.0. 4 rc, and it is estimated that the official version will come out soon. This article introduces several major new features of 8.0 in relational databases.

You may already know that MySQL has provided NoSQL storage since version 5.7, and this part of the function has been improved in 8.0. However, since this is rarely used in practice and I have not used it, this article will not introduce this aspect, but focus on its relational database aspect.

1. Hide the index

The hidden index feature is very useful for performance debugging. In 8.0, indexes can be "hidden" and "displayed". When an index is hidden, it will not be used by the query optimizer.

In other words, we can hide an index and observe the impact on the database. If the database performance declines, it shows that this index is useful, so it can be "restored and displayed"; If there is no change in database performance, this index is redundant and can be deleted.

The syntax for hiding an index is:

ALTER TABLE t ALTER INDEX i INVISIBLE;

The syntax for restoring display of the index is:

ALTER TABLE t ALTER INDEX i VISIBLE;

When an index is hidden, we can see from the output of the show index command that the index's Visible property has a value of NO.

Note: When the index is hidden, its contents are still updated in real-time like normal index 1. This feature itself is specially used for optimal debugging. If you hide an index for a long time, you might as well delete it simply, because the existence of an index will affect the performance of insertion, update and deletion.

2. Set up persistence

The settings of MySQL can be changed at run time with the SET GLOBAL command, but this change will only take effect temporarily, and the database will be read from the configuration file at the next startup.

MySQL 8 adds the SET PERSIST command, for example:

SET PERSIST max_connections = 500;

MySQL saves the configuration of the command to the mysqld-auto. cnf file in the data directory, which is read at the next startup, overwriting the default configuration file with the configuration in it.

3. UTF-8 coding

Beginning with MySQL 8, the default encoding of the database will be changed to utf8mb4, which includes all emoji characters. For many years, we have to be careful in coding when using MySQL, for fear of forgetting to change the default latin and causing garbled codes. Don't worry about it from now on.

4. General Table Expression (Common Table Expressions)

Complex queries use embedded tables, such as:

SELECT t1.*, t2.* FROM 
 (SELECT col1 FROM table1) t1,
 (SELECT col2 FROM table2) t2;

And with CTE, we can write this:

WITH
 t1 AS (SELECT col1 FROM table1),
 t2 AS (SELECT col2 FROM table2)
SELECT t1.*, t2.* 
FROM t1, t2;

In this way, it seems that the levels and areas are more distinct, and it is clearer to know which part to change.

For a more detailed introduction to CTE, please refer to the official documents.

5. Window function (Window Functions)

One of the most spit-out features of MySQL is the lack of rank () function. When you need to rank in a query, you must hand-write the @ variable. But since 8.0, MySQL has added a new concept called window function, which can be used to implement several new query methods.

Window functions are a bit like aggregate functions like SUM (), COUNT (), but instead of merging multiple lines of query results into one line, they put the results back into multiple lines. That is, the window function does not need GROUP BY.

Suppose we have a table of "class size":

mysql> select * from classes;
+--------+-----------+
| name | stu_count |
+--------+-----------+
| class1 |  41 |
| class2 |  43 |
| class3 |  57 |
| class4 |  57 |
| class5 |  37 |
+--------+-----------+
5 rows in set (0.00 sec)

If I want to rank class sizes from small to large, I can use window functions like this:

mysql> select *, rank() over w as `rank` from classes
 -> window w as (order by stu_count);
+--------+-----------+------+
| name | stu_count | rank |
+--------+-----------+------+
| class5 |  37 | 1 |
| class1 |  41 | 2 |
| class2 |  43 | 3 |
| class3 |  57 | 4 |
| class4 |  57 | 4 |
+--------+-----------+------+
5 rows in set (0.00 sec)

Here we create an window named w, specifying that it sorts the stu_count field, then executes the rank () method on w in the select clause, and outputs the result as the rank field.

In fact, the creation of window is optional. For example, if I want to add the total number of students to every 1 line, I can do this:

mysql> select *, sum(stu_count) over() as total_count
 -> from classes;
+--------+-----------+-------------+
| name | stu_count | total_count |
+--------+-----------+-------------+
| class1 |  41 |   235 |
| class2 |  43 |   235 |
| class3 |  57 |   235 |
| class4 |  57 |   235 |
| class5 |  37 |   235 |
+--------+-----------+-------------+
5 rows in set (0.00 sec)

What's the use of doing this? In this way, we can find out the proportion of students in each class once:

mysql> select *,
 -> (stu_count)/(sum(stu_count) over()) as rate
 -> from classes;
+--------+-----------+--------+
| name | stu_count | rate |
+--------+-----------+--------+
| class1 |  41 | 0.1745 |
| class2 |  43 | 0.1830 |
| class3 |  57 | 0.2426 |
| class4 |  57 | 0.2426 |
| class5 |  37 | 0.1574 |
+--------+-----------+--------+
5 rows in set (0.00 sec)

In the past, it was necessary to write a large section of obscure sentences to achieve it! More information about window functions is here.

Well, after reading the above introduction, do you have more expectations for MySQL 8.0?

Summarize