What is the difference between mysql8.0 and mysql5.7?

Difference: The index of mysql8.0 can be hidden and displayed. When an index is hidden, it will not be used by the query optimizer; 2. Mysql8.0 has a new "SET PERSIST" command; 3 . Starting from mysql8.0, the default encoding of the database will be changed to utf8mb4, which includes all emoji characters.

(Recommended tutorial: mysql video tutorial)

mysql8.0 and mysql5. The difference between 7 is

1. NoSql storage

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

2. Hidden index

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

In other words, you can hide an index and then observe the impact on the database. If the performance drops, it means that the index is effective, so Just "restore the display"; if there is no change in the database performance, it means that the index is too large and can be deleted.

The syntax of hiding an index

ALTER TABLE t ALTER INDEX i INVISIBLE;

Restore the display The syntax 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 specifically used for optimization and debugging. If you hide an index for a long time, you might as well kill it because the existence of the index will affect the data. Insert\update and delete functions

3. Set persistence

MySQL settings can be changed at runtime through the SET GLOBAL command, but this change will only take effect temporarily until the next time The database will be read from the configuration file at startup.
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 start it next time The file will be read and the configuration in it will be used 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 rank() function. When it is necessary to achieve ranking in the 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:

Description : 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, and output the result as the rank field.
This feature is also a new feature of Oracle11g, and it also plays a very important role in optimization.

For more programming-related knowledge, please visit: Introduction to Programming! !

The above is the detailed content of What is the difference between mysql8.0 and mysql5.7?. For more information, please follow other related articles on the PHP Chinese website!