The latest version of MySQL 8.0 is 8.0.4rc, and the official version is expected soon. This article introduces some of the major new features of 8.0 in relational databases.

As you probably know, MySQL has provided NoSQL storage since version 5.7, and some improvements have been made in 8.0, but since it’s rarely used in practice and I haven’t used it yet, this article won’t cover that, and will focus on the relational database aspect.

1. Hide indexes

The hidden index feature is useful for performance debugging. In 8.0, indexes can be “hidden” and “shown.” When an index is hidden, it is not used by the query optimizer.

That is, we can hide an index and see the impact on the database. If the performance of the database deteriorates, the index is useful and can be “restored to display.” If the performance of the database does not change, the 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 the index to display 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 an index is hidden, its contents are updated in real time just like a normal index. This feature itself is used to optimize debugging. If you’ve been hiding an index for a long time, you might as well just delete it, since the presence of an index affects insert, update, and delete performance.

2. Set persistence

MySQL Settings can be changed at run time using the SET GLOBAL command, but this change is only temporary until the database is read from the configuration file at the next startup.

MySQL 8 added the SET PERSIST command, for example:

SET PERSIST max_connections = 500;

MySQL will save the configuration of this command to the mysqld. auto-. CNF file in the data directory. The next startup will read this file and overwrite the default configuration file with the configuration in the file.

3. Utf-8 encoding

Starting with MySQL 8, the default database encoding will be changed to UTF8MB4, which includes all emoji characters. For years we’ve had to be careful with our encoding in case we forgot to change the default Latin and got garbled. No need to worry from now on.

4. Common Table Expressions

Complex queries use embedded tables, such as:

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

With CTE, we can write:

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

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

For a more detailed description of CTE, see:

dev.mysql.com/doc/ref…

5. Window Functions

One of the most teased features of MySQL is the lack of a rank() function. When you want to rank a query, you must write the @ variable. However, since 8.0, MySQL has introduced a new concept called window functions, which can be used to implement several new queries.

The window function is a bit like a collection function like SUM() and COUNT(), but instead of combining the results of a multi-row query into a single row, it puts the results back into multiple rows. In other words, the window function does not need GROUP BY.

Suppose we have a “class size” table:

mysql> select * from classes; +--------+-----------+ | name | stu_count | +--------+-----------+ | class1 | 41 | | class2 | 43 | | class3 | 57 | | Class4 57 | | | class5 37 | | + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the code

If I wanted to rank the class size from small to large, I could use the window function 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 the set (0.00) sec)Copy the code

Here we create a window named W, specify that it sort the STU_count field, and then execute the rank() method on W in the SELECT clause, which outputs the result as a rank field.

In fact, the creation of Windows is optional. For example, if I want to add the total number of students in each row, 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 the set (0.00 SEC)Copy the code

What’s the use of that? In this way, we can find out the proportion of students in each class at one time:

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 the set (0.00 SEC)Copy the code

This used to take a long paragraph of obscure sentences to do oh! More on window functions:

dev.mysql.com/doc/ref… How, after reading the above introduction, is there more expectation for MySQL 8.0?