What are the new features of MySQL8.0 04/02 Update SLTechnology News&Howtos

What are the new features of MySQL8.0

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article is to share with you about the new features of MySQL8.0. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

1. The default character set is changed from latin1 to utf8mb4

Prior to version 8.0, the default character set of latin1,utf8 points to the utf8mb3,8.0 version, and the default character set of utf8mb4,utf8 also points to utf8mb4.

(recommended: MySQL tutorial)

2. Change all MyISAM system tables to InnoDB tables

The system tables are all replaced by transactional innodb tables, and the default MySQL instance will not contain any MyISAM tables unless the MyISAM table is manually created.

In versions prior to 8.0, if the value of the self-increasing primary key AUTO_INCREMENT is greater than max (primary key) + 1, AUTO_INCREMENT=max (primary key) + 1 will be reset after MySQL restart. In some cases, this phenomenon will lead to business primary key conflicts or other difficult problems. The problem of adding primary key reset has been discovered for a long time (https://bugs.mysql.com/bug.ph...), which was not resolved until 8.0. Version 8.0 will persist the AUTO_ index value, which will not change after MySQL restart.

4. DDL atomization

The DDL of the InnoDB table supports transaction integrity, either succeed or rollback. Write the DDL operation rollback log to the data dictionary data dictionary table mysql.innodb_ddl_log for rollback operations, which is hidden and cannot be seen through show tables. You can print the ddl operation log to the mysql error log by setting parameters.

Mysql > set global log_error_verbosity=3;mysql > set global innodb_print_ddl_logs=1;mysql > create table T1 (c int) engine=innodb # MySQL error log: 2018-06-26T11:25:25.817245+08:00 44 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert: [DDL record: DELETE SPACE, id=41, thread_id=44, space_id=6 Old_file_path=./db/t1.ibd] 2018-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete: by id 412018-06-26T11:25:25.819753+08:00 44 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert: [DDL record: REMOVE CACHE, id=42, thread_id=44, table_id=1063 New_file_path=db/t1] 2018-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete: by id 422018-06-26T11:25:25.820556+08:00 44 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert: [DDL record: FREE, id=43, thread_id=44, space_id=6, index_id=140 Page_no=4] 2018-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete: by id 432018-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl: begin for thread id: 442018-06-26T11:25:25.825784+08:00 44 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl: end for thread id: 44

Let's look at another example. There is only one T1 table in the library, drop table T1 instruction T2; an attempt was made to delete two T1 DDL tables, but the T1 table was deleted and the T1 table was not deleted, which proved the atomicity of the 8.0 SQL operation, either successfully or rolled back.

# MySQL 5.7mysql > show tables;+-+ | Tables_in_db | +-+ | T1 | +-+ 1 row in set (0.00 sec) mysql > drop table T1, T2 db.t2'mysql error 1051 (42S02): Unknown table 'db.t2'mysql > show tables;Empty set (0.00 sec) # MySQL 8.0mysql > show tables +-+ | Tables_in_db | +-+ | T1 | +-+ 1 row in set (0.00 sec) mysql > drop table T1, T2 + error 1051 (42S02): Unknown table 'db.t2'mysql > show tables +-+ | Tables_in_db | +-+ | T1 | +-+ 1 row in set (0.00 sec) 5. Parameter modification persistence

MySQL version 8.0 supports online modification of global parameters and persistence. By adding the PERSIST keyword, the modified parameters can be persisted to a new configuration file (mysqld-auto.cnf). When MySQL is restarted, the latest configuration parameters can be obtained from this configuration file.

For example, execute:

Set PERSIST expire_logs_days=10

A file containing mysqld-auto.cnf in json format is generated in the data directory, as shown below after formatting, and the latter has a higher priority when my.cnf and mysqld-auto.cnf exist at the same time.

{"Version": 1, "mysql_server": {"expire_logs_days": {"Value": "10", "Metadata": {"Timestamp": 1529657078851627, "User": "root", "Host": "localhost"}} 6. New descending index

MySQL has supported descending indexes for a long time in syntax, but in fact it is still an ascending index, as shown in MySQL 5.7 below, c2 fields are in descending order, but c2 is still in ascending order from show create table point of view. 8.0 you can see that the c2 field is in descending order.

# MySQL 5.7mysql > create table T1 (C1 int,c2 int,index idx_c1_c2 (C1 desc c2)) Query OK, 0 rows affected (0.03 sec) mysql > show create table T1\ show create table * * Table: t1Create Table: CREATE TABLE `t1` (`c1` int (11) DEFAULT NULL, `c2` int (11) DEFAULT NULL, KEY `idx_c1_ c2` (`c1`) `c2`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.00 sec) # MySQL 8.0mysql > create table T1 (C1 int,c2 int,index idx_c1_c2 (C1 int,c2 int,index idx_c1_c2 c2 desc)) Query OK, 0 rows affected (0.06 sec) mysql > show create table T1\ show create table * * Table: t1Create Table: CREATE TABLE `t1` (`c1` int (11) DEFAULT NULL, `c2` int (11) DEFAULT NULL, KEY `idx_c1_ c2` (`c1`) `c2` DESC)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC1 row in set (0.00 sec)

Let's take a look at the performance of the descending index in the execution plan, insert 100000 random data into the T1 table, and view the execution plan of select * from T1 order by C1, c2 desc;. As can be seen from the execution plan, the scan count of 100113 is much larger than the five lines of 8.0, and filesort is used.

The descending index is only valid for the specific sort order in the query. If it is not used properly, the query efficiency is even lower. For example, the above query sorting condition is changed to order by C1 desc, c2 desc. In this case, the execution plan of 5.7 is significantly better than that of 8.0, as follows:

Mysql 8.0 no longer implicitly sorts group by fields. If sorting is required, the order by clause must be explicitly added.

# Table structure mysql > show create table tb1\ gateway * 1. Row * * Table: tb1Create Table: CREATE TABLE `tb1` (`id` int (11) NOT NULL AUTO_INCREMENT, `name` varchar (50) DEFAULT NULL, `group_ own` int (11) DEFAULT'0' PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC1 row in set (0.00 sec) # Table data mysql > select * from tb1 +-1 | 1 | 0 | 2 | 2 | 0 | 3 | 3 | 0 | 4 | 4 | 0 | 5 | 5 | 5 | 8 | 8 | 1 | | 10 | 10 | 5 | +-+ 7 rows in set (0.00 sec) # MySQL 5.7mysql > select count (id) Group_own from tb1 group by group_own +-+ | count (id) | group_own | +-+-+ | 4 | 0 | 1 | 1 | 2 | 5 | +-+-+ 3 rows in Set (0.00 sec) # MySQL 8.0.11mysql > select count (id) Group_own from tb1 group by group_own +-+-+ | count (id) | group_own | +-+-+ | 4 | 0 | 2 | 5 | 1 | 1 | +-+-+ 3 rows in Set (0.00 sec) # MySQL 8.0.11 explicitly add order by to sort mysql > select count (id) Group_own from tb1 group by group_own order by group_own +-+ | count (id) | group_own | +-+-+ | 4 | 0 | 1 | 1 | 2 | 5 | +-+-+ 3 rows in Set (0.00 sec) 8. JSON feature enhancement

MySQL 8 greatly improves support for JSON, adding the JSON_EXTRACT () function to extract data from the JSON field based on path query parameters, and the JSON_ARRAYAGG () and JSON_OBJECTAGG () aggregate functions for combining data into JSON arrays and objects, respectively.

In master-slave replication, a new parameter binlog_row_value_options is added to control the transmission mode of JSON data. Partial modification of Json type is allowed, and only the modified part is recorded in binlog, so as to reduce the resource occupation of json big data with only a few modifications.

9. Redo & undo log encryption

Add the following two parameters to control the encryption of redo and undo logs.

Innodb_undo_log_encrypt

10. Innodb select for update skips lock waiting

Select... For update,select... For share add NOWAIT, SKIP LOCKED syntax, skip lock waiting, or skip lock.

In versions 5. 7 and earlier, select...for update, if it could not get the lock, would wait until innodb_lock_wait_timeout timed out.

In version 8.0, you can return immediately by adding nowait,skip locked syntax. If the row of the query is locked, nowait returns an error immediately, and skip locked returns immediately, except that the locked row is not included in the returned result.

# session1:mysql > begin;mysql > select * from T1 where C1 = 2 for update;+-+-+ | C1 | c2 | +-+-+ | 2 | 60530 | 2 | 2 | 24678 | +-+-+ 2 rows in set (0.00 sec) # session2:mysql > select * from T1 where C1 = 2 for update nowait ERROR 3572 (HY000): Statement aborted because lock (s) could not be acquired immediately and NOWAIT is set.mysql > select * from T1 where C1 = 2 for update skip locked;Empty set (sec) 11. Add SET_VAR syntax

Adding SET_VAR syntax to sql syntax and dynamically adjusting some parameters are helpful to improve the performance of sentences.

Select / + SET_VAR (sort_buffer_size = 16m) / id from test order id; insert / + SET_VAR (foreign_key_checks=OFF) / into test (name) values (1); 12. Support for invisible indexes

Use the INVISIBLE keyword to set whether the index is visible in creating a table or making table changes. The index is invisible only that the optimizer does not use the index when querying, and even if force index is used, the optimizer will not use the index, and the optimizer will not report that the index does not exist, because the index still exists and can be quickly restored to visibility if necessary.

# create invisible index create table T2 (C1 int,c2 int,index idx_c1_c2 (C1 create table C2 desc) invisible); # index visible alter table T2 alter index idx_c1_c2 visible;# index invisible alter table T2 alter index idx_c1_c2 invisible;13. Support histogram

The optimizer will use the column_statistics data to determine the distribution of the values of the fields and get a more accurate execution plan.

You can use ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS | DROP HISTOGRAM ON clo_name] to collect or delete histogram information.

The histogram counts the data distribution of some fields in the table, which provides a reference for optimizing and selecting an efficient execution plan. There is an essential difference between the histogram and the index, and there is a cost to maintain an index. Every time insert, update, delete will need to update the index, which will have a certain impact on performance. The histogram is created once and never updated unless it is explicitly updated. So it will not affect the performance of insert, update, delete.

Allows InnoDB to automatically configure the three innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_method parameters based on the amount of memory detected on the server.

15. Log classification in more detail

The error message number [MY-010311] and the subsystem to which the error belongs [Server] are added to the error message

# MySQL 5.72018-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry' @ root@localhost' ignored in-- skip-name-resolve mode.2018-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry' user mysql.session@localhost' ignored in-- skip-name-resolve mode.2018-06-08T09:07:20.117868+08:00 0 [Warning] 'tables_priv' entry' sys_config mysql.sys@ Localhost' ignored in-- skip-name-resolve mode. # MySQL 8.02018-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv' entry' @ root@localhost' ignored in-skip-name-resolve mode.2018-06-21T17:53:13.040520+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry' user mysql.session@localhost' ignored in-- skip-name-resolve mode.2018-06-21T17:53:13. 040542 08Server 00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry' sys_config mysql.sys@localhost' ignored in-- skip-name-resolve mode.16. The default value of innodb_undo_log_truncate parameter for automatic recycling of undo space is changed from OFF to ON in version 8.0.2, and automatic collection of undo log tablespaces is enabled by default. The innodb_undo_tablespaces parameter defaults to 2 in version 8.0.2, and when one undo table space is reclaimed, there is another one that provides normal service. The innodb_max_undo_log_size parameter defines the maximum value of undo table space recycling, and when the undo table space exceeds this value, the table space is marked as recyclable. 17. Increase resource group

MySQL 8.0 added a resource group feature to regulate thread priorities and bind CPU cores.

MySQL users need RESOURCE_GROUP_ADMIN permission to create, modify, and delete resource groups.

In the Linux environment, the MySQL process requires CAP_SYS_NICE permission to use the full functionality of the resource group.

[root@localhost~] # sudo setcap cap_sys_nice+ep / usr/local/mysql8.0/bin/mysqld [root@localhost~] # getcap / usr/local/mysql8.0/bin/mysqld/usr/local/mysql8.0/bin/mysqld = cap_sys_nice+ep

Two resource groups are provided by default, namely USR_default,SYS_default

Create a resource group:

Create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5

Add the current thread to the resource group:

SET RESOURCE GROUP test_resouce_group

Add a thread to the resource group:

SET RESOURCE GROUP test_resouce_group FOR thread_id

See which threads are in the resource group:

Select * from Performance_Schema.threads where RESOURCE_GROUP='test_resouce_group'

Modify the resource group:

Alter resource group test_resouce_group vcpu = 2pm 3 THREAD_PRIORITY = 8

Delete a resource group:

Drop resource group test_resouce_group

# create resource group mysql > create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;Query OK, 0 rows affected (0.03 sec) mysql > select * from RESOURCE_GROUPS +-+ | RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | | THREAD_PRIORITY | +-- + | USR_default | USER | 1 | 0-3 | 0 | SYS_default | SYSTEM | 1 | 0-3 | 0 | | test_resouce_group | USER | 1 | 0-1 | 5 | +- -+ 3 rows in set (0.00 sec) # add threads with thread id 60 to the resource group test_resouce_group Thread id can obtain mysql > SET RESOURCE GROUP test_resouce_group FOR 60 through Performance_Schema.threads Query OK, 0 rows affected (0.00 sec) # when there is a thread in the resource group, delete the resource group and report an error mysql > drop resource group test_resouce_group;ERROR 3656 (HY000): Resource group test_resouce_group is busy.# modify the resource group mysql > alter resource group test_resouce_group vcpu = 2jing3 THREAD_PRIORITY = 8 THREAD_PRIORITY query OK, 0 rows affected (0.10 sec) mysql > select * from RESOURCE_GROUPS +-+ | RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | | THREAD_PRIORITY | +-- + | USR_default | USER | 1 | 0-3 | 0 | SYS_default | SYSTEM | 1 | 0-3 | 0 | | test_resouce_group | USER | 1 | 2-3 | 8 | +- -+ 3 rows in set (0.00 sec) # move threads from the resource group to the default resource group USR_defaultmysql > SET RESOURCE GROUP USR_default FOR 60 Query OK, 0 rows affected (0.00 sec) # Delete resource group mysql > drop resource group test_resouce_group;Query OK, 0 rows affected (0.04 sec) 18. Add role management

Roles can be thought of as a collection of permissions, giving users a unified role. Permissions are modified directly through roles, and there is no need to authorize each user separately.

# create role mysql > create role role_test;Query OK, 0 rows affected (0.03 sec) # Grant permissions to role mysql > grant select on db.* to 'role_test';Query OK, 0 rows affected (0.10 sec) # create user mysql > create user' read_user'@'%' identified by' 123456 query OK, 0 rows affected (0.09 sec) # assign role mysql > grant 'role_test' to' read_user'@'%' to user Query OK, 0 rows affected (0.02 sec) # add insert permissions mysql > grant insert on db.* to 'role_test';Query OK to role role_test, 0 rows affected (0.08 sec) # remove insert permissions mysql > revoke insert on db.* from' role_test';Query OK, 0 rows affected (0.10 sec) # View default role information mysql > select * from mysql.default_roles +-+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +-- -+ |% | read_user |% | role_test | +-+ 1 row in set (0.00 sec) # View roles and uses Customer relationship mysql > select * from mysql.role_edges +-+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +- -+ |% | role_test |% | read_user | N | +- -+ 1 row in set (0.00 sec) # Delete role mysql > drop role role_test Query OK, 0 rows affected (0.06 sec) Thank you for your reading! What are the new features of MySQL8.0 to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.