The latest version of MySQL 8.0 is 8.0.4rc and is expected to be released soon. This article introduces several of the major new features in 8.0 for relational databases.

You probably already know that MySQL has NoSQL storage since version 5.7, and it got some improvements in 8.0, but since it’s rarely used in practice and I haven’t used it myself, I won’t cover it in this article, focusing instead on the relational database aspect.

1. Hide indexes

The ability to hide indexes 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 database performance degrades, the index is useful and can be “restored to display”. If database performance does not change, the index is redundant and can be dropped.

The syntax for hiding an index is:

ALTER TABLE t ALTER INDEX i INVISIBLE;
Copy the code

The syntax for restoring the display of the index is:

ALTER TABLE t ALTER INDEX i VISIBLE;
Copy the code

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

Note: When an index is hidden, its contents are updated in real time just like a normal index. This feature itself is designed for optimized debugging. If you hide an index for a long time, you might as well delete it altogether, because 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 only takes effect temporarily until the next startup when the database reads from the configuration file.

MySQL 8 added the SET PERSIST command.

SET PERSIST max_connections = 500;
Copy the code

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

3. Utf-8 encoding

Starting with MySQL 8, the default encoding for the database will be UTF8MB4, which contains all emoji characters. For years with MySQL, we had to be careful about coding so that we didn’t forget to change the default Latin and get garbled. There’s no need to worry after that.

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, the layers and areas are more clearly defined, and it is easier to know which parts to change.

See the official documentation for more details on CTE.

Dev.mysql.com/doc/refman/…

5, Window Functions

One of the most ridiculed features of MySQL is the lack of the rank() function, which requires the @ variable to be written when ranking a query. But since 8.0, MySQL has added 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 such as SUM() and COUNT(), but instead of combining the multi-row query results into one row, it puts the results back into multiple rows. In other words, window functions do not need GROUP BY.

Suppose we have a “class size” table:

If I wanted to rank the class size from small to large, I could use the window function like this:

Here we create a window named W, specify that it sorts the STU_count field, and then perform the rank() method on win the SELECT clause to output the result as a rank field.

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

What’s the use of that? So we can figure out the proportion of students in each class at once:

It used to take writing a long paragraph of obscure sentences to do this! More information about window functions can be found here.

MySQL 8.0 MySQL 8.0 8.0 MySQL 8.0 8.0

**PS: ** In case you can’t find this article, you can click “like” to browse and find it