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

This article introduces the knowledge about "what is the difference between Mysql8 and mysql 5.7". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

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;

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 One of MySQL's most criticized features is the lack of rank() function, when you need to implement ranking in the query, you must hand-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.

What is the difference between Mysql8 and mysql 5.7? Thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

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.