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

What are the new features of MySQL8.0

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

Shulou(Shulou.com)05/31 Report--

This article focuses on "what are the new features of MySQL8.0". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "what are the new features of MySQL8.0"?

1. Default character set 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. Note: tested on Percona Server version 8.0.15, utf8 still points to utf8mb3, which is inconsistent with the official documentation.

Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. | |

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.

# MySQL 5.7

Mysql > select distinct (ENGINE) from information_schema.tables

+-+

| | ENGINE |

+-+

| | MEMORY |

| | InnoDB |

| | MyISAM |

| | CSV |

| | PERFORMANCE_SCHEMA |

| | NULL |

+-+

6 rows in set (0.00 sec)

# MySQL 8.0

Mysql > select distinct (ENGINE) from information_schema.tables

+-+

| | ENGINE |

+-+

| | NULL |

| | InnoDB |

| | CSV |

| | PERFORMANCE_SCHEMA |

+-+

4 rows in set (0.00 sec)

3. Self-increasing variable persistence

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 was discovered a long time ago and was not resolved until 8.0. the AUTO_ index value will be persisted in version 8.0, 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:

2019-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]

2019-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete: by id 41

2019-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]

2019-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete: by id 42

2019-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]

2019-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete: by id 43

2019-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl: begin for thread id: 44

2019-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.7

Mysql > 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

Empty set (0.00 sec)

# MySQL 8.0

Mysql > 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; the system generates a file containing mysqld-auto.cnf in json format 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.7

Mysql > 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\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`c1` int (11) DEFAULT NULL

`c2` int (11) DEFAULT NULL

KEY `idx_c1_ c2` (`c1`, `c2`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

# MySQL 8.0

Mysql > create table T1 (C1 int,c2 int,index idx_c1_c2 (C1 desc c2))

Query OK, 0 rows affected (0.06 sec)

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create 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=DYNAMIC

1 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.

DELIMITER

CREATE PROCEDURE test_insert ()

BEGIN

DECLARE i INT DEFAULT 1

WHILE i explain select * from T1 order by C1, c2 desc limit 5

+- -- +

+- -- +

| | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100113 | 100.00 | Using index; Using filesort |

+- -- +

1 row in set, 1 warning (0.00 sec)

# MySQL 8.0

Mysql > explain select * from T1 order by C1, c2 desc limit 5

+-- +

+-- +

| | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index |

+-- +

1 row in set, 1 warning (0.00 sec)

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 5.7

Mysql > explain select * from T1 order by C1 desc, c2 desc limit 5

+-- +

+-- +

| | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index |

+-- +

1 row in set, 1 warning (0.01sec)

# MySQL 8.0

Mysql > explain select * from T1 order by C1 desc, c2 desc limit 5

+- -- +

+- -- +

| | 1 | SIMPLE | T1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100429 | 100.00 | Using index; Using filesort |

+- -- +

1 row in set, 1 warning (0.01sec)

7. Group by no longer sorts implicitly

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\ G

* * 1. Row *

Table: tb1

Create 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=DYNAMIC

1 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.7

Mysql > select count (id), group_own from tb1 group by group_own

+-+ +

+-+ +

| | 4 | 0 |

| | 1 | 1 |

| | 2 | 5 |

+-+ +

3 rows in set (0.00 sec)

# MySQL 8.0.11

Mysql > select count (id), group_own from tb1 group by 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

+-+ +

+-+ +

| | 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_encryptinnodb_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 | 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 (0.00 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)

twelve。 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 an invisible index

Create table T2 (C1 int,c2 int,index idx_c1_c2 (C1 desc c2 desc) invisible)

# Index visible

Alter table t2 alter index idx_c1_c2 visible

# Index is not visible

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.

# add / update histogram

Mysql > analyze table T1 update histogram on C1, c2 with 32 buckets

+-- +

+-- +

+-- +

2 rows in set (2.57 sec)

# Delete histogram

Mysql > analyze table T1 drop histogram on C1, c2

+-- +

+-- +

+-- +

2 rows in set (0.13 sec)

14. New innodb_dedicated_server parameter

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.7

2019-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry' @ root@localhost' ignored in-skip-name-resolve mode.

2019-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry' user mysql.session@localhost' ignored in-- skip-name-resolve mode.

2019-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.0

2019-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv' entry' @ root@localhost' ignored in-skip-name-resolve mode.

2019-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.

2019-06-21T17:53:13.040542+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry' sys_config mysql.sys@localhost' ignored in-skip-name-resolve mode.

16. Automatic recovery of undo space

The default value of innodb_undo_log_truncate parameter 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; adds the current thread to the resource group: SET RESOURCE GROUP test_resouce_group; adds a thread to the resource group: SET RESOURCE GROUP test_resouce_group FOR thread_id; checks which threads are in the resource group: select * from Performance_Schema.threads where RESOURCE_GROUP='test_resouce_group'; modifies the resource group: alter resource group test_resouce_group vcpu= 2, THREAD_PRIORITY 3, group = 8 Delete resource group: drop resource group test_resouce_group

# create a 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

+-- +

+-- +

| | 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 the thread with thread id 60 to the resource group test_resouce_group, and thread id can be obtained through Performance_Schema.threads

Mysql > SET RESOURCE GROUP test_resouce_group FOR 60

Query OK, 0 rows affected (0.00 sec)

# when there are threads 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 resource group

Mysql > alter resource group test_resouce_group vcpu = 2pm 3 THREAD_PRIORITY = 8

Query OK, 0 rows affected (0.10 sec)

Mysql > select * from RESOURCE_GROUPS

+-- +

+-- +

| | 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_default

Mysql > SET RESOURCE GROUP USR_default FOR 60

Query OK, 0 rows affected (0.00 sec)

# deleting a 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 roles

Mysql > create role role_test

Query OK, 0 rows affected (0.03 sec)

# Grant permissions to roles

Mysql > grant select on db.* to 'role_test'

Query OK, 0 rows affected (0.10 sec)

# create a user

Mysql > create user' read_user'@'%' identified by '123456'

Query OK, 0 rows affected (0.09 sec)

# assign roles to users

Mysql > grant 'role_test' to' read_user'@'%'

Query OK, 0 rows affected (0.02 sec)

# add insert permissions to the role role_test

Mysql > grant insert on db.* to 'role_test'

Query OK, 0 rows affected (0.08 sec)

# remove insert permissions for role role_test

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

+-+

+-+

| |% | read_user |% | role_test |

+-+

1 row in set (0.00 sec)

# View the relationship between roles and users

Mysql > select * from mysql.role_edges

+-+

+-+

| |% | role_test |% | read_user | N |

+-+

1 row in set (0.00 sec)

# deleting a role

Mysql > drop role role_test

Query OK, 0 rows affected (0.06 sec)

At this point, I believe you have a deeper understanding of "what are the new features of MySQL8.0". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.