Five new features of MySQL 8.0

1. Hide index

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

In other words, we can hide an index and observe the impact on the database. If the database performance decreases, it indicates that this index is useful, so "restore the display" can be used; If the database performance does not change, it indicates that the index is redundant and can be deleted.

The syntax for hiding an index is:


The syntax for restoring the display of the index is:


When an index is hidden, we can see from the output of the show index command that the value of the Visible attribute of the index is NO.

Note: when the index is hidden, its content is still updated in real time as the normal index. This feature itself is specially used for optimization and debugging. If you hide an index for a long time, you might as well delete it altogether, because after all, the existence of the index will affect the performance of insertion, update and deletion.

2. Set persistence

MySQL settings can be changed through the SET GLOBAL command at runtime, 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 command "SET PERSIST", for example:

SET PERSIST max_connections = 500;

MySQL will save the configuration of this command to mysqld auto CNF} file, this file will be read at the next startup, and the default configuration file will be overwritten with the configuration in it.

3. UTF-8 coding

Starting from MySQL 8, the default code of the database will be changed to {utf8mb4, which contains all emoji characters. Over the years, we have been cautious in coding when using mysql, for fear that we may forget to change the default latin and cause garbled code. Don't 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;

With CTE, we can write:

  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 level and area are more clear, and it is clearer to know which part to change.

For more details on CTE, please see:

5. Window Functions

One of the characteristics of MySQL being the most common way to tuck is the lack of rank() function. When we need to make complaints about queries, we must write @ variables. However, since 8.0, MySQL has added a new concept called window function, which can be used to realize several new query methods.

The window function is a bit like a collection function such as SUM() and COUNT(), but it does not merge multiple rows of query results into one row, but puts the results back into multiple rows. That is to say, 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 set (0.00 sec)

If I want to rank the class size from small to large, I can use the window function:

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 a window named w and specify that it is for stu_ Sort the count field, and then execute the rank() method on w in the select clause to output the result as a rank field.

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


Added by cosminb on Tue, 08 Feb 2022 14:36:21 +0200