1. No storage

Mysql provides NoSQL storage functionality from version 5.7, with some changes in 8.0, but this is rarely used in practice

The index hiding feature is very 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 see the impact on the database. If performance degrades, the index is valid and can be “restored to display”. If there is no change in database performance, the index is overloaded and can be dropped

Syntax for hiding an index

ALTER TABLE t ALTER INDEX i INVISIBLE;
Copy the code

The syntax for restoring the display of the index is:

ALTER TABLE t ALTER INDEX i VISIBLE;
Copy the code

When an index is hidden, we can see from the summary output of the show index command that the index visible property value is No

** Note :** When an index is hidden, its contents are updated in real time just like the normal index. This feature is used specifically to optimize 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 insert, update and delete functions of the data

3. SET persistence MySQL Settings can be changed at run time using the SET GLOBAL command, but this change only takes effect temporarily and the database will be read from the configuration file again at next startup. MySQL 8 added the SET PERSIST command.

SET PERSIST max_connections = 500;
Copy the code

The MySQL saves the configuration of the command to the mysqld-auto-. CNF file in the data directory. The next startup will read the file and use the configuration in the file to overwrite the default configuration file.

4. Utf-8 encoding Starting with MySQL 8, the default encoding for the database will be UTF8MB4, which contains all emoji characters. For years with MySQL, we had to be careful about coding so that we didn’t forget to change the default Latin and get garbled. There’s no need to worry after that.

Common Table Expressions Complex queries use embedded tables, such as:

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

With CTE, we can write:

WITH
    t1 AS (SELECT col1 FROM table1),
    t2 AS (SELECT col2 FROM table2)
SELECT t1.*, t2.* 
FROM t1, t2;
Copy the code

In this way, the layers and areas are more clearly defined, and it is easier to know which parts to change. This feature is useful in many reporting scenarios and is an important feature for mysql optimization.

One of the most ridiculed features of MySQL is its lack of a rank() function, which requires the @ variable to be written when ranking a query. But since 8.0, MySQL has added a new concept called window functions, which can be used to implement several new queries. The window function is a bit like a collection function such as SUM() and COUNT(), but instead of combining the multi-row query results 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:

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

Description: Create a window named W, specify it to sort stu_count, and then perform the rank() method on win the SELECT clause to output the result as a rank field. This feature is also a new feature in Oracle11g and plays an important role in optimization.