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

What are the characteristics of MySQL 8.0 relational database? In view of this problem, this article introduces the corresponding analysis and answers in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

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) what are the answers to the questions about the characteristics of MySQL 8.0 relational database? I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

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.