Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the difference between mysql8.0 and mysql5.7, which has certain reference value, and friends who need it can refer to it. I hope you all have a lot to gain after reading this article. Let's take a look at it together.

Difference: mysql8.0 index can be hidden and displayed, when an index hidden, it will not be used by the query optimizer;2, mysql8.0 added the "SET PERIST" command;3, starting from mysql8.0, the database default encoding will be changed to utf8mb 4, including all emoji characters.

1. NoSql Storage

Mysql has provided NoSQL storage since version 5.7, with some modifications in 8.0, but this is rarely used in practice

2. hide index

The hidden index feature 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, you can hide an index and watch the impact on the database. If the performance drops, it means that the index is valid, so it can be "restored to show"; if the database performance does not see changes, it means that the index is more than, you can delete it

Hide syntax for an index

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 summary output of the show index command that the index visible property has a value of No

** Note:** When the index is hidden, its content is still updated in real time like the normal index. This feature itself is specially used for optimizing debugging. If you hide an index for a long time, it is better to kill it, because the existence of the index will affect the insertion\update and deletion of data.

3. Set persistence

MySQL settings can be changed at runtime via the SET GLOBAL command, but such changes only take effect temporarily, and the database is read from the configuration file at the next startup.

MySQL 8 adds SET PERIST commands such as:

SET PERSIST max_connections = 500;

MySQL will save the configuration of this command to the mysqld-auto.cnf file in the data directory, which will be read the next time it starts, overwriting the default configuration file with the configuration in it.

4. UTF-8 encoding

Starting with MySQL 8, the default encoding for the database will be utf8mb4, which contains all emoji characters. For years we have had to code MySQL carefully, lest we forget to change the default latin and garble. From now on there's nothing to worry about.

5. 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, the levels and areas are more distinct, and it is clearer to know which part to change.

This feature is useful in many reporting scenarios and is an important feature of mysql optimization.

1.235 Window Functions (Windows Functions)

One of MySQL's most criticized features is the lack of rank() function, when you need to implement ranking in the query, you must write @ variable. However, since 8.0 MySQL has added a concept called window function, which can be used to implement several new query methods.

Window functions are somewhat like SUM(), COUNT() aggregate functions, but instead of merging the results of a multi-row query into a single row, they put the results back into the multi-row. In other words, window functions do not require GROUP BY.

Suppose we have a "class size" table:

If you want to rank the class size from small to large, you can use the window function like this:

Note: Here you create a window named w, specify that it sorts the stu_count field, and then execute the rank() method on w in the select clause, outputting the result as the rank field.

This feature is also a new feature in Oracle 11g and plays an important role in optimization.

Thank you for reading this article carefully. I hope Xiaobian can share what is the difference between mysql8.0 and mysql5.7. At the same time, I hope that everyone will support you a lot. Pay attention to the industry information channel. When you encounter problems, find detailed solutions waiting for you to learn!

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.