In this series, the author will collate and summarize all the new features interpretations of the MySQL 8.0 version so far. They will be classified according to functionality, performance, security, optimizer and other aspects, and will be explained in multiple articles in the future.
This is part 1. We will introduce 28 new features with enhanced functionality.
1. All system tables replaced with InnoDB engine
All system tables are replaced with transactional InnoDB table. The default MySQL instance will not contain any MyISAM tables unless a MyISAM table is manually created.
2. DDL atomization
The DDL of InnoDB tables supports transaction integrity, either succeeding or rolling back. The DDL operation rollback log is written into the data dictionary table mysql.innodb_ddl_log for rollback operations. This table is a hidden table and cannot be seen by using show tables. By setting parameters, the DDL operation log can be printed and output to the mysql error log.
For example:
mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;
3. DDL instant column addition
Only supported in versions above MySQL 8.0.12.
For example:
mysql> show create table sbtest1;
CREATE TABLE `sbtest1` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
`d` int NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> alter table sbtest1 drop column d ;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
Query OK, 1000000 rows affected (19.61 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
Query OK, 2000000 rows affected (38.25 sec)
Records: 2000000 Duplicates: 0 Warnings: 0
mysql> insert into sbtest1(k,c,pad) select k,c,pad from sbtest1;
Query OK, 4000000 rows affected (1 min 14.51 sec)
Records: 4000000 Duplicates: 0 Warnings: 0
mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 8000000 |
+----------+
1 row in set (0.31 sec)
mysql> alter table sbtest1 add column d int not null default 0;
Query OK, 0 rows affected (1.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table sbtest1 add column e int not null default 0;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
4. CTE
CTE (Common Table Expression) can be regarded as a substitute for derived tables. To some extent, CTE simplifies complex join queries and subqueries. Additionally, CTE can easily implement recursive queries and improve the readability and execution performance of SQL. CTE is part of the ANSI SQL 99 standard and was introduced in MySQL 8.0.1 version.
CTE advantages：
- Better readability of query statements
- Can be referenced multiple times in a query
- Can link multiple CTEs
- Can create recursive queries
- Can improve SQL execution performance
- Can effectively substitute views
5. Default character set changed from latin1 to utf8mb4
Before version 8.0, the default character set was latin1, and utf8 pointed to utf8mb3. In version 8.0, the default character set is utf8mb4, and utf8 also defaults to point to utf8mb4.
6. Clone plugin
The MySQL 8.0 clone plugin provides the function of cloning another instance from one instance. The cloning function provides a more effective way to quickly create MySQL instances and build primary-secondary replication and group replication.
7. Resource groups
MySQL 8.0 adds a resource group function for regulating thread priorities and binding CPU cores. MySQL users need to have RESOURCE_GROUP_ADMIN permission to create, modify, and delete resource groups. In the Linux environment, the MySQL process needs to have CAP_SYS_NICE permission to use the full functionality of resource groups.
8. Role management
A role can be regarded as a collection of some permissions. Assigning a unified role to users, and permission modifications are directly carried out through the role without having to authorize each user separately.
For example:
# Create a role
mysql>create role role_test;
QueryOK, 0rows affected (0.03sec)
# Grant permissions to the role
mysql>grant select on db.*to 'role_test';
QueryOK, 0rows affected (0.09sec)
# Create a user
mysql>create user 'read_user'@'%'identified by '123456';
QueryOK, 0rows affected (0.09sec)
# Assign the role to the user
mysql>grant 'role_test'to 'read_user'@'%';
QueryOK, 0rows affected (0.02sec)
# Add insert permission to the role role_test
mysql>grant insert on db.*to 'role_test';
QueryOK, 0rows affected (0.08sec)
# Delete insert permission from the role role_test
mysql>revoke insert on db.*from 'role_test';
QueryOK, 0rows affected (0.10sec)
# View default role information
mysql>select * from mysql.default_roles;
# View the relationship between roles and users
mysql>select * from mysql.role_edges;
# Delete the role
mysql>drop role role_test;
9. Multi-valued indexes
Starting from MySQL 8.0.17, InnoDB supports creating multi-valued indexes, which are secondary indexes defined on JSON columns that store value arrays. A single data record can have multiple index records. Such indexes are defined using key parts, for example, CAST(data->'$.zipcode' AS UNSIGNED ARRAY). The MySQL optimizer automatically uses multi-valued indexes for appropriate queries and can be viewed in the output of **EXPLAIN**.
10. Functional indexes
MySQL 8.0.13 and higher versions support functional indexes (functional key parts), that is, using the value of an expression as the content of an index instead of a column value or a column value prefix. Using a function as an index key can be used to index content that is not directly stored in the table.
In fact, the virtual column function was introduced in MySQL 5.7, and the functional index in MySQL 8.0 is also implemented based on virtual columns.
- Only functions that can be used for calculated columns can be used to create functional indexes.
- Subqueries, parameters, variables, storage functions, and custom functions are not allowed in functional indexes.
- Spatial indexes and FULLTEXT indexes do not support functional indexes.
11. Invisible indexes
In MySQL 5.7 and earlier versions, indexes could only be deleted explicitly. At this time, if an error occurs after deleting an index, the deleted index can only be recreated explicitly. If the amount of data in a data table is very large or the data table itself is very large, this operation will consume too many system resources and the operation cost is very high.
Starting from MySQL 8.x, hidden indexes (invisible indexes) are supported. Only need to set the index to be deleted as a hidden index so that the query optimizer no longer uses this index (even if using force index (forcing the use of an index), the optimizer will not use the index), and after confirming that the system is not affected by setting the index as a hidden index, the index can be completely deleted. This way of first setting the index as a hidden index and then deleting it is soft deletion.
For example:
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `idx_c1` (`c1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
# When invisible, the index will not be used and key=null.
mysql> explain select * from t1 where c1=3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# Set the index to be visible.
mysql> alter table t1 alter index idx_c1 visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int DEFAULT NULL,
`c2` int DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
# The index can be used and key=idx_c1.
mysql> explain select * from t1 where c1=3;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | idx_c1 | idx_c1 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
12. New descending indexes added
Starting from MySQL 8.0, actual descending indexes are created. In previous versions, although the syntax supported descending indexes, in practice, ascending indexes were created.
13. SET_VAR syntax
Added in SQL syntax for dynamic adjustment of some parameters to improve statement performance.
For example:
select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order id ;
insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);
14. Parameter modification persistence
In MySQL 8.0, online modification of global parameters can be persisted to a new configuration file mysqld-auto.cnf. When restarting, the configuration is obtained from this file.
For example:
set PERSIST expire_logs_days=10;
15. InnoDB's SELECT FOR UPDATE skips lock waiting
In MySQL 8.0, in the SELECT... FOR UPDATE and SELECT... FOR SHARE syntax, NOWAIT and SKIP LOCKED syntax are added.
This allows skipping lock waiting or locking. Compared to versions 5.7 and earlier, the handling is different.
16. GROUP BY no longer implicitly sorts
The purpose is to be compatible with the standard syntax of SQL for convenient migration.
MySQL 5.7
mysql> select count(*),age from t5 group by age;
+----------+------+
| count(*) | age |
+----------+------+
| 1 | 25 |
| 1 | 29 |
| 1 | 32 |
| 1 | 33 |
| 1 | 35 |
+----------+------+
5 rows in set (0.00 sec)
MySQL 8.0
mysql> select count(*),age from t5 group by age;
+----------+------+
| count(*) | age |
+----------+------+
| 1 | 25 |
| 1 | 32 |
| 1 | 35 |
| 1 | 29 |
| 1 | 33 |
+----------+------+
5 rows in set (0.00 sec)
As can be seen, MySQL 5.7 performs implicit sorting on grouped fields in GROUP BY. However, MySQL 8.0 cancels implicit sorting. If sorting needs to be added, it needs to be explicitly increased.
For example:
select count(*), age from t5 group by age order by age;.
17. Persistence of auto-increment variables
In versions prior to 8.0, the auto-increment value was stored in memory. If the value of the auto-increment primary key AUTO_INCREMENT was greater than max(primary key)+1, after MySQL was restarted, would be reset to AUTO_INCREMENT=max(primary key)+1. This phenomenon can lead to business primary key conflicts or other hard-to-detect problems in some cases. The problem of the auto-increment primary key being reset on restart was discovered long ago https://bugs.mysql.com/bug.php?id=199 and was not resolved until version 8.0. In version 8.0, the AUTO_INCREMENT value will be persisted and will not change after MySQL is restarted.
Starting from version 8.0, whenever the current maximum auto-increment counter changes, the value is written into the redo log and saved to the private system table at each checkpoint. This change persists the AUTO_INCREMENT value and the value will not change after MySQL is restarted.
- After the MySQL server is restarted, the effect of the AUTO_INCREMENT = N table option is no longer canceled. If the auto-increment counter is initialized to a specific value or the auto-increment counter value is changed to a larger value, the new value is persisted even if the server is restarted.
- Immediately restarting the server after a rollback operation will no longer cause the auto-increment values allocated to the rolled-back transaction to be reused.
- If the AUTO_INCREMENT column value is modified to a value greater than the current maximum auto-increment value (for example, in an update operation), the new value will be persisted and subsequent insert operations will start allocating auto-increment values from the new, larger value.
For example:
-- Confirm your own version.
select VERSION();
/*
VERSION() |
----------+
5.7.26-log|
*/
-- Create table.
create table testincr(
id int auto_increment primary key,
name varchar(50)
);
-- Insert data.
insert into testincr(name) values
('Tom'),
('jack'),
('lucy');
-- View the current auto-increment value.
select t.AUTO_INCREMENT from information_schema.TABLES t where TABLE_NAME ='testincr';
/*
AUTO_INCREMENT|
--------------+
4|
*/
-- Change column value.
update testincr set id=4 where id=3;
-- View the current table values.
/*
id|name|
--+----+
1|Tom|
2|Jack|
4|Lucy|
*/
-- Insert new value. Problem occurs.
insert into testincr(name) values('Kate');
/*
SQL Error [1062] [23000]: Duplicate entry '4' for key 'PRIMARY'.
*/
-- If we insert again, it is normal because the id is up to 5...
mysql> insert into testincr(name) values('Kate');
Query OK, 1 row affected (0.01 sec).
18. Binlog log transaction compression
In MySQL 8.0.20, this function is added. Transaction information is compressed using the zstd algorithm and then written into the binlog. Correspondingly, a new event type is introduced.
19. Partition table improvements
Before MySQL 8.0, partition tables were implemented at the server layer and supported multiple storage engines. Starting from MySQL 8.0, partition tables are moved to the engine layer and only InnoDB supports them. This change is introduced.
20. Automatic parameter setting
When innodb_dedicated_server is enabled, it can automatically adjust the values of the following four parameters:
- innodb_buffer_pool_size (total memory size)
- innodb_log_file_size (size of redo log files)
- innodb_log_files_in_group (number of redo log files)
- innodb_flush_method (data flushing method)
Just set innodb_dedicated_server = ON. The above four parameters will be automatically adjusted, which solves the problem that the default initial values of database parameters are relatively low after non-professionals install the database. This allows MySQL to adaptively adjust the above four parameters. The prerequisite is that the server is dedicated to the MySQL database. If there are other software or resources or multiple instances of MySQL in use, it is not recommended to enable this parameter. This article takes MySQL 8.0.19 as an example.
So, according to what rules are these adjusted? The official MySQL documentation provides the following related parameter adjustment rules:
① innodb_buffer_pool_size Automatic Adjustment Rule
Dedicated server memory size
buffer_pool_size size
Less than 1GB
128MB (MySQL default value)
1GB to 4GB
OS memory * 0.5
Greater than 4GB
OS memory * 0.75
② innodb_log_file_size Automatic Adjustment Rule
buffer_pool_size size
log_file_size size
Less than 8GB
512MB
8GB to 128GB
1024MB
Greater than 128GB
2048MB
③ innodb_log_files_in_group Automatic Adjustment Rule (The value of innodb_log_files_in_group is the number of log files)
buffer_pool_size size
Number of log files
Less than 8GB
ROUND(buffer pool size)
8GB to 128GB
ROUND(buffer pool size * 0.75)
Greater than 128GB
64
Note: If the value of ROUND(buffer pool size) is less than 2GB, then innodb_log_files_in_group will be forcibly set to 2.
④ InnoDB Flush Method Automatic Adjustment Rule
The adjustment rule for this parameter directly quotes the explanation from the official documentation: The flush method is set to O_DIRECT_NO_FSYNC when innodb_dedicated_server is enabled. If the O_DIRECT_NO_FSYNC setting is not available, the default innodb_flush_method setting is used.
If the system allows it, it is set to O_DIRECT_NO_FSYNC; if the system does not allow it, it is set to the default Flush method of InnoDB.
Advantages and Limitations of Adaptive Parameters
Advantages
- Automatic adjustment, simple and convenient, saving DBA's efforts.
- Comes with an optimization aura. Before this parameter, after default installation and initialization,
innodb_buffer_pool_sizeandlog_file_sizeare only 128M and 48M respectively. This is far from enough for a production environment. Usually, DBAs manually adjust and optimize according to the server's hardware configuration. After the appearance of this parameter, it can basically solve the performance problem after beginners install MySQL. - For cloud vendors and virtualization, after dynamic resource expansion or contraction, there is no need to worry about MySQL parameter configuration issues.
Limitations
- Applicable only to servers dedicated solely to MySQL.
- Not applicable in the case of multiple instances on a single machine.
- Not applicable when there are other software or applications running on the server.
21. Window functions
Starting from MySQL 8.0, a new concept called window functions is introduced.
What is a window?
It can be understood as a record set. A window function is a special function executed on a record set that meets certain conditions. For each record, the function needs to be executed within this window. For some functions, as the records are different, the window size is fixed. This belongs to a static window. For some functions, on the contrary, different records correspond to different windows. This kind of dynamically changing window is called a sliding window.
It can be used to implement several new query methods. Window functions are similar to aggregate functions such as SUM() and COUNT(), but they do not combine multiple rows of query results into one row. Instead, they put the results back into multiple rows. That is, window functions do not require GROUP BY.
No detailed introduction is provided here.
22. Index damage marking
When the index tree is damaged, InnoDB writes a damage flag in the redo log and a private system table. During recovery, the flags are read and merged, and the table and index objects are marked as damaged.
23. InnoDB memcached plugin
The InnoDB memcached plugin supports batch get operations (obtaining multiple key-value pairs in one memcached query) and range queries. Reducing the communication traffic between the client and the server, the function of obtaining multiple key-value pairs in a single memcached query can improve read performance.
24. Online DDL
Starting from MySQL 8.0.12 (only for the InnoDB engine), the following ALTER TABLE operations support ALGORITHM=INSTANT:
- Adding a column. This feature is also known as "instant column addition". Limitations apply.
- Adding or removing a virtual column.
- Adding or removing a default value for a column.
- Modifying the definition of an ENUM or SET column.
- Changing the index type.
- Renaming a table.
Benefits of Online DDL
Operations that support ALGORITHM=INSTANT only modify the metadata in the data dictionary. There is no metadata lock on the table, and the table data is not affected. The operation is instant and does not cause business jitter. This is very convenient in some systems with relatively high service level requirements (7\*24). This feature is contributed by the Tencent Games DBA team.
If not explicitly specified, operations that support it use ALGORITHM=INSTANT by default. If ALGORITHM=INSTANT is specified but not supported, the operation will immediately fail with an error. It should be noted that before MySQL 8.0.29, a column could only be added as the last column of a table. Adding a column to any other position among other columns is not supported. Starting from MySQL 8.0.29, an instantaneously added column can be added to any position in the table.
25. Explain Analyze
Explain is a commonly used query analysis tool that can evaluate the execution mode of query statements and give many useful clues. However, it is only an evaluation and not the actual execution situation. For example, the "rows" in the result may be very different from the actual result.
Explain Analyze is a new tool provided in MySQL 8. The valuable thing is that it can give the actual execution situation. Explain Analyze is a query performance analysis tool that can show in detail where and how much time is spent during the execution of a query statement. Explain Analyze will make a query plan and actually execute it to measure the actual indicators of each key point in the query plan, such as time consumption and number of rows, and finally print them out in detail.
This new function is built on the regular EXPLAIN and can be regarded as an extension of EXPLAIN FORMAT = TREE added before MySQL 8.0. In addition to outputting the query plan and estimated cost, EXPLAIN ANALYZE also outputs the actual cost of each iterator in the execution plan.
26. ReplicaSet
InnoDB ReplicaSet is composed of one master node and multiple slave nodes. The ReplicaSet object of MySQL Shell and AdminAPI can be used to operate and manage the replication set, such as checking the state of the InnoDB replication set and manually performing failover to a new master server in case of failure.
All nodes of ReplicaSet must be based on GTID, and data replication uses an asynchronous method. Using the replication set can also take over the existing master-slave replication, but it should be noted that once taken over, it can only be managed through AdminAPI.
27. Backup lock
In MySQL 8.0, a lightweight backup lock is introduced. This lock can ensure backup consistency and has relatively few blocking operations. It is a very important new feature.
In MySQL 8.0, to solve the problem of backup FTWRL, a lightweight backup lock is introduced. The backup lock can be obtained and released through LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE. Executing this statement requires the BACKUP_ADMIN permission.
The backup lock does not block read and write operations. However, the backup lock will block most DDL operations, including creating/deleting tables, adding/deleting fields, adding/deleting indexes, optimize/analyze/repair table, etc.
In general, the backup lock is still very practical. After all, it will not affect the normal read and write of business. As for the conflict between the backup lock and DDL operations, there are still many ways to avoid it, such as staggering the time of backup and change, and avoiding long-term blocking through pt-online-schema-change/gh-ost. With the introduction of the backup lock, the Oracle official backup tool MEB 8.0 and the Percona open source backup tool XtraBackup 8.0 have also updated their support for the backup lock.
28. Binlog enhancement
The MySQL 8.0.20 version adds the binlog log transaction compression function. The transaction information is compressed using the zstd algorithm and then written into the binlog log file. This compressed transaction information corresponds to a new event type in the binlog called Transaction_payload_event.
