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; The syntax for restoring the 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 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.

MySQL Settings can be changed at run time using the SET GLOBAL command, but this change only takes effect temporarily and the database will be read from the configuration file again at next startup.

MySQL 8 added the SET PERSIST command.

SET PERSIST max_connections = 500; 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 database default encoding 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.

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.

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:

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 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:

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?

So we can figure out the proportion of students in each class at 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 the set (0.00 SEC)Copy the code

MySQL 8.0 is a user-friendly update. Which version do you use?