New features of MySQL8.0

Inadvertently found that the version of MySQL has been updated to 8.0, learn about the different places

Data dictionary

A transactional data dictionary has been added to store database object information

Previously, dictionary data was stored in metadata files and non-transactional tables

Account rights management

Added support for "roles", roles will correspond to a set of permissions, roles can be added and deleted, and permissions on roles can be added or deleted

You can add and delete roles to user accounts

InnoDB promotion

Improve the management of the self-increment counter (auto-increment counter), for example, set the initial value of the counter, it will not be affected after the server restart, and the execution of ROLLBACK will not cause the value of the counter to be reused

The InnoDB memcached plug-in supports getting multiple key/value pairs in a query and performing range queries.

A new dynamic configuration item, innodb_deadlock_detect, has been added to disable deadlock checking, because in highly concurrent systems, deadlock checking can greatly slow down the database when a large number of threads wait for the same lock

All InnoDB temporary tables are created into a shared temporary tablespace

SELECT... FOR SHARE and SELECT... NOWAIT and SKIP LOCKED options are supported in FOR UPDATE. The function of NOWAIT is to return immediately when the requested row is locked. The function of SKIP LOCKED is to remove the locked row from the result set.

JSON promotion

Adding the inline path operator-> > is equivalent to calling JSON_UNQUOTE () on the result set of JSON_EXTRACT ()

Two new JSON aggregate functions are added: JSON_ARRAYAGG () and JSON_OBJECTAGG () (related articles: experience Mysql operation JSON documents, experience MySQL 8.0 JSON aggregation function)

Add a new tool function JSON_PRETTY (), which can format JSON and improve readability

When order by is used to sort JSON values in a query, the sort key length of each value will be variable instead of a fixed 1K, which has two advantages: 1) more efficient use of sort buffer space, more data can be in memory to avoid unnecessary disk access; 2) improve the comparison performance of key

Optimizer optimizer

Hidden indexes are supported. The optimizer can ignore hidden indexes, but the hidden indexes are normally maintained to test invalid indexes. Before deleting an index, you can set it to hidden indexes to make sure there is no impact on the system before deleting them, so as to prevent them from being rebuilt again after deletion. (related article: MySQL 8.0 New Features: hidden indexes)

Support descending index, you can define DESC on the index, before, the index can be scanned in reverse order, but the performance is affected, and the descending index can be completed efficiently