Shulou(Shulou.com)05/31 Report--

MySQL 8.0 in the relational database what are the new features, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

The editor introduces several major new features of 8.0 in relational databases.

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

1. Hide the index

The nature of hidden indexes is 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 there is a decline in database performance, the index is useful, so it can be "restored to display"; if there is no change in database performance, 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 and display 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 an index is hidden, its contents are still updated in real time as normal indexes, and this feature itself is designed for optimal debugging. If you hide an index for a long time, you might as well delete it, because after all, the existence of the index will affect the performance of inserts, updates, and deletes.

2. Set persistence

The settings for MySQL can be changed at run time through the SET GLOBAL command, but the change will only take effect temporarily, and the database will read from the configuration file the next time it starts.

MySQL 8 adds SET PERSIST commands, such as:

SET PERSIST max_connections = 500,

MySQL saves the configuration of the command to the mysqld-auto.cnf file in the data directory, which is read the next time it starts, and overwrites the default configuration file with the configuration.

3. UTF-8 coding

Starting with MySQL 8, the default encoding of the database will be changed to utf8mb4, which contains all emoji characters. For many years, we have to be careful in coding when we use MySQL, lest we forget to change the default latin and cause garbled problems. From now on, you don't have to worry.

4. General table expression (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 levels and areas are more clear, and it is more clear to know which part to change.

For a more detailed description of CTE, please see the official documentation.

5. Window function (Window Functions)

One of the most complained features of MySQL is the lack of the rank () function. When you need to rank in a query, you have to handwrite the @ variable. But 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 a bit like a collection function like SUM () and COUNT (), but instead of merging the results of multiple rows of queries 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 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 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, requiring it to sort the stu_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 to each line, 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 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 set (0.00 sec) is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.