What’s New in MySQL 8.0? Key Features, Performance Boosts, and Best Practices

Unified Data Dictionary

MySQL 8.0 replaces the legacy FRM‑file metadata with a single InnoDB‑backed Data Dictionary, eliminating redundant storage and enabling true crash‑safe, transactional DDL. Benefits include atomic DDL execution, centralized caching of dictionary data, and removal of server‑level metadata files.

Redesigned Information Schema

The new Information Schema uses views instead of physical system tables, preventing temporary‑table creation during queries and dramatically improving metadata query performance.

Role‑Based Privilege Management

MySQL now supports CREATE ROLE to group privileges, simplifying permission handling. Example commands:

CREATE ROLE 'app_read', 'app_write';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
CREATE USER 'ro_user'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'rw_user'@'localhost' IDENTIFIED BY 'rw_user1pass';
GRANT 'app_read' TO 'ro_user'@'localhost';
GRANT 'app_read','app_write' TO 'rw_user'@'localhost';

Roles help isolate permissions for mixed‑use instances and streamline DBA workflows.

Persistent Configuration with PERSIST

Adding the PERSIST keyword to a SET command writes the change to mysqld-auto.cnf (JSON format), ensuring the setting survives restarts and takes precedence over my.cnf. Example:

SET GLOBAL sync_binlog=1 PERSIST;

The generated file contains:

{ "mysql_server": { "sync_binlog": "1" } }

Auto‑Increment Improvements

MySQL 8.0 no longer resets AUTO_INCREMENT after a restart, preserving the highest existing value and avoiding reuse of deleted keys (see WL#7816).

InnoDB Optimizations

Histograms : Use ANALYZE TABLE tbl_name UPDATE HISTOGRAM to collect column statistics for better optimizer estimates.

Invisible Indexes : Mark indexes as invisible to test their impact without affecting the optimizer (ensure optimizer_switch=use_invisible_indexes=off if you want them ignored).

Session‑Level SET_VAR : Adjust optimizer variables per statement, e.g.

SELECT /*+ SET_VAR(sort_buffer_size=16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

Mutex Splitting

Large global mutexes have been replaced with finer‑grained locks, such as LRU_list_mutex, zip_free mutex, zip_hash mutex, and free_list_mutex, reducing contention and improving buffer‑pool concurrency.

Row Cache Enhancements

The optimizer now estimates rows to be read and can allocate an appropriately sized row buffer, boosting performance for large sequential scans.

Temporary Table Changes

Support for compressed temporary tables has been removed; metadata for temporary tables is kept in memory, yielding faster creation and cleanup.

Character Set Updates

The default character set switches from latin1 to utf8mb4, with Unicode 9 support and new collations such as utf8mb4_0900_ai_ci (case‑insensitive) and utf8mb4_ru_0900_as_cs (case‑sensitive).

Default Parameter Tweaks

Back‑log default now matches max_connections for better burst handling. event_scheduler defaults to ON, encouraging scheduled jobs. max_allowed_packet increased to 64 MiB (recommended 512 MiB for replicas). log_bin and log_slave_updates default to ON.

Binary log retention extended to 30 days.

Atomic DDL Support

InnoDB now logs DDL operations in mysql.innodb_ddl_log, allowing full rollback if a DDL statement fails, ensuring transactional integrity.

Conclusion

MySQL 8.0 introduces a suite of features that bring the engine closer to enterprise‑grade capabilities, addressing many long‑standing DBA pain points and delivering measurable performance gains. For deeper details, consult the official release notes at https://dev.mysql.com/doc/relnotes/mysql/8.0/en/preface.html.