What are the differences between Mysql8 and mysql5.7

This article will introduce to you the difference between Mysql8 and mysql5.7. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to everyone.

1. NoSql storage

Mysql has provided NoSQL storage function since version 5.7. This part has been modified in 8.0, but this is not practical in practice. Very rarely used

2. Hidden index

The hidden index feature is very useful for performance debugging. In 8.0, the index can be hidden and displayed. When an index is hidden, it will not be used by the query optimizer

#That is to say, you can hide an index and observe the impact on the database. If performance drops, It means that this index is valid, so just "restore display"; if the database performance does not change, it means that this index is too large and can be deleted.

Syntax for hiding an index

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 summary 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 like a normal index. This feature itself is specially used for optimization and 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 functions of data

3. Set persistence

MySQL settings can be changed at runtime through the SET GLOBAL command, but this change It will only take effect temporarily, and the database will be read from the configuration file the next time it is started.

MySQL 8 has added 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, and will Read this file and use the configuration in it to overwrite the default configuration file.

4.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 careful in coding when using MySQL, for fear of forgetting to change the default latin and causing garbled code problems. No more worries from now on.

5. Common Table Expressions

Complex queries will use embedded tables, for example:

SELECT t1.*, t2.* FROM
	 (SELECT col1 FROM table1) t1,
	 (SELECT col2 FROM table2) t2;

With CTE, we can write like this :

	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 look more distinct, and it is clearer to know which part to change when making changes.

This feature is very useful in many reporting scenarios and is also a very important feature of mysql optimization.

1.235 Window Functions

One of the most criticized features of MySQL is the lack of the rank() function. When ranking needs to be achieved in a query, the @ variable must be written by hand. But starting from 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 an aggregate function such as SUM() and COUNT(), but it does not merge the multi-row query results into one row, but puts 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":

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

Explanation: A window named w is created here, and it is specified to sort the stu_count field, and then execute the rank() method on w in the select clause to put the result The output is the rank field.

This feature is also a new feature of Oracle11g, and it also plays a very important role in optimization.

Related recommendations: "mysql tutorial"

The above is the detailed content of What are the differences between Mysql8 and mysql5.7. For more information, please follow other related articles on the PHP Chinese website!