Five new features of MySQL 8.0 are very practical!

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

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.

That is, we can hide an index and observe the impact on the database. If the database performance decreases, it indicates that the 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:

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 Visible attribute value 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 an 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 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. This file will be read at the next startup, and the default configuration file will be overwritten with the configuration.

3. UTF-8 encoding

Starting from MySQL 8, the default code of the database will be changed to utf8mb4, which contains 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 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:

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

For more details on CTE, see: https://dev.mysql.com/doc/refman/8.0/en/with.html

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 implement several new query methods.

The window function is somewhat 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. In other words, GROUP BY is not required for window functions.

Suppose we have a "class number" 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:

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 this? 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 set (0.00 sec)

In the past, it was necessary to write a long paragraph of obscure sentences to do this! More about window functions: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

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

6 December 2021, 22:27 | Views: 1819