Top ten new features of MySQL 8.0

Today, let's take a look at the ten improvements that MySQL8.0 improves database administrator productivity.

It's not easy to find 10 points from a lot of features, here are the ten features:

1. Improvements to temporal tables

2. Persistent global variables

3. Cancel the default MyISAM system table

4.UNDO Space Recycling

5.utf8 Performance Improvements

6. Cancel the query Cache

7. Atomic DDL

8. Faster, better-performing schema and information schema

9. Role Management

10. The redo log and the undo log in the encrypted table space are encrypted

Improvements to temporal tables

In MySQL5.7, all temporary tables are created in a tablespace called "IBTMP1". In addition, the metadata for the temporary table is stored in memory (no longer stored in the frm file).

In MySQL8.0, the temporary table storage engine was used as the default engine for storing temporary tables (created for operations that optimize joins, unions, and so on), thereby replacing the original memory storage engine.

The new engine makes the storage of varchar and varbinary columns more efficient (all storage spaces are fully allocated).

Persistent global variables

MySQL8.0 can now set the variable and make its change persist until the server restarts.

Using this syntax in conjunction with the new Restart command, it is very easy to configure MySQL from the shell. This is a very friendly feature for cloud users.

To cancel the default MyISAM system table

Because of the new local data dictionary, we don't need to myisam the system table now!

These tables and data dictionaries appear in a innodb tablespace file named Mysql.idb.

This means that if you do not explicitly use the MyISAM table (which we do not recommend for your data), you can create a MySQL instance that does not have any MyISAM tables.

Undo Space Reclamation

In MySQL5.7, we can already truncate the undo space (set Innodb_undo_log_truncate, which is disabled by default).

In MySQL8, we improved the disk format so that each undo table had a large number of undo segments.

In addition, the undo segment is now created by default for two separate undo tablespace (not InnoDB system tablespace (min. 2, size dynamic)).

We do not recommend using Innodb_undo_tablespaces to set this value, as we will provide SQL commands to allow the database administrator to interact with the undo table space.

Auto truncate undo table space is enabled by default.

UTF8 Performance Improvements

UTF8 Performance

Because the default character set has been changed from Latin1 to UTF8MB4, UTF8 is now much faster and 1800% faster on specific queries! Emojis now everywhere, MySQL is of course the choice to support him!??

Cancel Query Cache

The first thing I suggested in the performance audit was to disable the query Cache because it caused a lot of trouble with the design.

MySQL QC causes more problems than it solves. So we decided to cancel it in MySQL 8.0 because people shouldn't use it.

If your workload requires query cache, you should instead use Proxysql instead of query cache.

Atomic DDL

With the introduction of a new data dictionary, MySQL 8.0 now supports Atomic data definition statements (Atomic DDL).

This means that when the DDL is executed, the data dictionary updates, the storage engine operations, and the write operations in the binary log are merged into a single atomic transaction, either fully executed or not executed at all.

This improves the stability of the DDL to ensure that incomplete DDL does not leave any incomplete data.

8. Faster, better-performing schema and information schema

We have made many improvements to the schema, such as false indexes and histograms.

Queries like select * from Sys.session are up to 30 times times faster thanks to the existence of false indexes.

Queries avoid table scans as much as possible, and the use of indexes can significantly reduce execution time.

In addition to this, the performance schema also provides a histogram of statement latency. The new histogram also makes the optimizer more efficient.

The new data dictionary also makes the information schema improved, and now does not require a large number of. FRM table structure definition files.

Another information schema now supports expansion to more than 1,000,000 sheets!

Role Management

The SQL role feature was added in MySQL 8.0. A role is a specified collection that has specific permissions. Like a user account, a role can have permissions to grant and revoke.

Roles can be modified by default or by session. You can also force the role to be set.

both the redo log and the undo log in the encrypted table space are encrypted

In MySQL 5.7, you can encrypt the InnoDB table space for the tables stored in each table. In MySQL 8.0, we do this by adding encryption for the undo and redo logs.

In addition, there are many improvements I have not finished. There are a lot of other nice features. I would like to list some of the things I think are important (though all the improvements are important??). ）

1. Persistent auto-increment

2.InnoDB Self-tuning

3.JSON Performance Optimization

3. Invisible Index

4. New Backup Lock

5. Resource group functions

6. Additional binary log meta-data

7. And OpenSSL for Community Edition

Top ten new features of MySQL 8.0