Detailed explanation of new relational database features in MySQL 8.0

Preface

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

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

1. Hidden Index

The hidden index feature is very 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 then observe the impact on the database. If the database performance has declined, it means that the index is useful, so you can "restore it to display"; if the database performance has not changed, it means 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 to restore 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 property value of the index is NO.

Note: When an index is hidden, its content is still updated in real time like a normal index. This feature is specifically for optimization and debugging. If you hide an index for a long time, it is better to delete it, because after all, the existence of the index will affect the performance of insert, update, and delete.

2. Set persistence

MySQL settings can be changed at runtime using the SET GLOBAL command, but this change is only temporary and the database will read the configuration file again the next time it is started.

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. The next time it is started, it will read the file and use the configuration in it to overwrite the default configuration file.

3. UTF-8 encoding

Starting from MySQL 8, the default encoding of the database will be changed to utf8mb4, which includes all emoji characters. For many years, we have been very careful with encoding when using MySQL, for fear that we would forget to change the default latin and cause garbled characters. You don't have to worry about it from now on.

4. Common Table Expressions

Complex queries use embedded tables, for example:

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;

This way, the layers and areas appear clearer, and you will know more clearly which part to change.

For more detailed information about CTE, please see the official documentation.

5. Window Functions

One of the most complained features of MySQL is the lack of a rank() function. When you need to implement ranking in a query, you have to write the @ variable manually. However, starting from 8.0, MySQL added a new concept called window function, which can be used to implement several new query methods.

Window functions are a bit like aggregate functions such as SUM() and COUNT(), but they do not merge multiple rows of query results into one row, but put the results back into multiple rows. In other words, window functions do not require GROUP BY.

Suppose we have a table of "number of students in a class":

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

mysql> select *, rank() over was `rank` from classes
 -> window was (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, specify it to sort the stu_count field, and then execute the rank() method on w in the select clause and output the result as the 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 point of doing 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, this could only be achieved by writing a long and obscure paragraph! More information about window functions can be found here.

How about it? After reading the above introduction, do you have more expectations for MySQL 8.0?

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.