New Features and Enhancements in MySQL 8.0

New Features and Enhancements in MySQL 8.0

1.1 All System Tables Use InnoDB Engine

All system tables are switched to the transactional InnoDB engine; the default MySQL instance no longer contains any MyISAM tables unless they are created manually.

1.2 Atomic DDL

DDL on InnoDB tables now supports full transaction integrity – it either succeeds or rolls back. Rollback logs are written to the hidden mysql.innodb_ddl_log data‑dictionary table. By setting system variables, the DDL log can also be printed to the MySQL error log.

mysql> set global log_error_verbosity=3;
mysql> set global innodb_print_ddl_logs=1;

1.3 Instant Column Add (DDL "秒加列")

Instant column addition is supported only in MySQL 8.0.12 and later. The article demonstrates creating a large test table, dropping a column, bulk inserting data, and then adding new columns instantly.

mysql> show create table sbtest1;
... (output omitted for brevity) ...
mysql> alter table sbtest1 add column d int not null default 0;
mysql> alter table sbtest1 add column e int not null default 0;

1.4 Common Table Expressions (CTE)

CTE provides a derived‑table alternative that simplifies complex joins and sub‑queries, supports recursion, improves readability and performance. CTE became part of the ANSI SQL‑99 standard and was introduced in MySQL 8.0.1.

1.4.1 Advantages of CTE

Better query readability

Can be referenced multiple times within a single query

Allows chaining of multiple CTEs

Enables recursive queries

Improves SQL execution performance

Effectively replaces views

1.5 Default Character Set Changed to utf8mb4

Prior to 8.0 the default charset was latin1 and utf8 mapped to utf8mb3 . Starting with 8.0 the default charset is utf8mb4 , and utf8 also points to utf8mb4 .

1.6 Clone Plugin

The Clone plugin allows cloning an entire MySQL instance to another, facilitating rapid instance creation, master‑slave replication, and group replication setups.

1.7 Resource Groups

MySQL 8.0 adds resource groups for controlling thread priority and binding threads to specific CPU cores. Users need the RESOURCE_GROUP_ADMIN privilege, and the MySQL process must have CAP_SYS_NICE on Linux.

1.8 Role Management

Roles act as collections of privileges that can be granted to users, simplifying permission management.

# Create role
mysql> create role role_test;
# Grant privileges to role
mysql> grant select on db.* to 'role_test';
# Create user
mysql> create user 'read_user'@'%' identified by '123456';
# Assign role to user
mysql> grant 'role_test' to 'read_user'@'%';
# Modify role privileges
mysql> grant insert on db.* to 'role_test';
mysql> revoke insert on db.* from 'role_test';
# View role information
mysql> select * from mysql.default_roles;
mysql> select * from mysql.role_edges;
# Drop role
mysql> drop role role_test;

1.9 Multi‑Value Indexes

From MySQL 8.0.17, InnoDB supports multi‑value indexes on JSON array columns, allowing multiple index entries per row. The optimizer automatically uses these indexes, visible in EXPLAIN output.

1.10 Functional Indexes

MySQL 8.0.13+ supports functional key parts, indexing the result of an expression rather than a column value. This builds on the virtual column feature introduced in MySQL 5.7.

1.11 Invisible Indexes

MySQL 5.7 required explicit index removal. Starting with MySQL 8.x, indexes can be marked invisible, causing the optimizer to ignore them without dropping them, enabling a soft‑delete workflow.

mysql> show create table t1\G
... (output showing KEY `idx_c1` (c1) /*!80000 INVISIBLE */ ...)
mysql> alter table t1 alter index idx_c1 visible;

1.12 Descending Indexes

Although MySQL historically stored indexes in ascending order, MySQL 8.0 actually creates descending indexes when specified.

1.13 SET_VAR Syntax

The new SET_VAR hint allows dynamic adjustment of session variables within a statement to improve performance.

select /*+ SET_VAR(sort_buffer_size = 16M) */ id from test order by id;
insert /*+ SET_VAR(foreign_key_checks=OFF) */ into test(name) values(1);

1.14 Persistent Parameter Modification

MySQL 8.0 can persist online global variable changes by appending the PERSIST keyword, storing them in mysqld-auto.cnf which overrides my.cnf on restart.

set persist expire_logs_days=10;

1.15 SELECT ... FOR UPDATE NOWAIT / SKIP LOCKED

MySQL 8.0 adds NOWAIT and SKIP LOCKED options to SELECT ... FOR UPDATE and SELECT ... FOR SHARE , allowing immediate return when rows are locked.

1.16 GROUP BY No Longer Implicitly Sorts

MySQL 8.0 removes the implicit sorting of the GROUP BY column, aligning with the SQL standard. Explicit ORDER BY is required for ordered results.

1.17 AUTO_INCREMENT Persistence

Prior to 8.0, AUTO_INCREMENT values were stored only in memory and could reset after a restart. MySQL 8.0 persists the counter in the redo log and a private system table, preventing resets.

1.18 Binlog Transaction Compression

MySQL 8.0.20 adds compression of binlog transactions using the ZSTD algorithm, creating a new Transaction_payload_event type.

1.19 Partition Table Improvements

Partitioning moved from the server layer to the storage‑engine layer; only InnoDB supports partitions in MySQL 8.0.

1.20 Automatic Parameter Tuning (innodb_dedicated_server)

When enabled, MySQL automatically adjusts innodb_buffer_pool_size , innodb_log_file_size , innodb_log_files_in_group , and innodb_flush_method based on server memory, simplifying configuration for dedicated MySQL servers.

1.21 Window Functions

MySQL 8.0 introduces window functions, which operate on a set of rows (a window) without collapsing them into a single aggregated row, enabling advanced analytical queries without GROUP BY .

1.22 Index Corruption Marking

InnoDB writes a corruption flag to the redo log and a private system table when an index tree is damaged, allowing recovery processes to detect and mark corrupted indexes.

1.23 InnoDB Memcached Plugin

The plugin supports batch GET operations and range queries, reducing client‑server traffic and improving read performance.

1.24 Online DDL (ALGORITHM=INSTANT)

From MySQL 8.0.12, certain ALTER TABLE operations (e.g., adding columns, virtual columns, default values, changing ENUM/SET definitions, index type changes, renaming tables) can be performed instantly without metadata locks, minimizing service disruption.

1.25 EXPLAIN ANALYZE

EXPLAIN ANALYZE executes the query and reports actual execution metrics (time, rows, etc.) in addition to the estimated plan, providing a more accurate performance analysis.

1.26 InnoDB ReplicaSet

ReplicaSet consists of one primary and multiple secondary nodes, managed via MySQL Shell's ReplicaSet object and AdminAPI, supporting GTID‑based asynchronous replication and manual failover.

1.27 Lightweight Backup Lock

MySQL 8.0 introduces a lightweight backup lock ( LOCK INSTANCE FOR BACKUP ) that ensures backup consistency while minimally blocking DDL operations; it requires the BACKUP_ADMIN privilege.

1.28 Binlog Enhancements

MySQL 8.0.20 adds transaction compression to binlog using ZSTD, creating a new event type for compressed payloads.

These enhancements collectively improve MySQL’s reliability, performance, and ease of administration for modern workloads.