Author: fabricated belief
Original text: https://segmentfault.com/a/1190000013803247

This paper introduces several main new features of 8.0 in relational database.

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.

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:

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 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 In the CNF file, the 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:

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, please see the official documents.

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

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)

Recent hot article recommendations:

1.600 + Java interview questions and answers (2021 latest edition)

2.Finally got the IntelliJ IDEA activation code through the open source project. It's really fragrant!

3.Ali Mock tools are officially open source and kill all Mock tools on the market!

4.Spring Cloud 2020.0.0 is officially released, a new and subversive version!

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!