MySQL 8.0 New Features: Performance, Security, Optimizer, and Other Enhancements

Previous Article Recap

The previous article discussed functional enhancements of MySQL 8.0; readers can refer to the linked "Most Complete MySQL 8.0 New Features Interpretation (Part 1)" for details.

2. Performance Enhancements

2.1 Contention‑Aware Transaction Scheduling (CATS)

MySQL 8.0.3 introduced CATS, replacing the FIFO lock‑wait algorithm with a smarter, contention‑aware scheduler that significantly improves performance under high load and high contention.

2.2 WriteSet‑Based Parallel Replication

Since MySQL 5.7.22, the WriteSet mechanism tracks transaction dependencies, enabling faster binlog replay on replicas. Setting the following two parameters activates this feature:

binlog_transaction_dependency_tracking = WRITESET   # COMMIT_ORDER
transaction_write_set_extraction = XXHASH64

2.3 JSON Feature Enhancements

MySQL 8 adds JSON_EXTRACT() for path‑based extraction, and aggregation functions JSON_ARRAYAGG() and JSON_OBJECTAGG(). A new binlog_row_value_options parameter allows transmitting only modified parts of JSON columns, reducing resource usage.

2.4 Spatial Data Type Enhancements

Support for additional spatial functions and data types has been expanded, delivering better analysis capabilities and performance.

2.5 Doublewrite Improvements

From version 8.0.20 onward, the doublewrite buffer resides in its own tablespace, lowering write latency and increasing throughput while offering flexible placement.

2.6 Hash Join

MySQL 8.0.18 introduced hash join for equality joins without indexes; version 8.0.20 extended it to non‑equality joins, replacing BNL joins.

2.6.1 NestLoop Join Algorithm

The classic nested‑loop join iterates over the outer table rows and, for each, scans the inner table, resulting in O(M×N) complexity.

2.6.2 Hash Join

For equal‑join scenarios, the outer table is loaded into memory and hashed, allowing a single pass over the inner table. Classic Hash Join (CHJ) works when the entire dataset fits in memory; otherwise, a batched approach is used.

2.7 Anti Join

Introduced in 8.0.17, anti join rewrites NOT IN/NOT EXISTS subqueries into a join operation, improving performance by up to ~20% in suitable cases.

2.8 Redo Log Lock‑Free Optimization

MySQL 8 removes mutex contention on redo log writes by delegating log flushing to dedicated threads, allowing user threads to write only to the log buffer.

2.9 Histograms (Statistics)

Histograms based on column_statistics help the optimizer choose better execution plans. They can be created or dropped with ANALYZE TABLE … UPDATE HISTOGRAM … or DROP HISTOGRAM … .

2.10 Query Cache Removal

Starting with MySQL 8.0, the query cache is discontinued due to low hit‑rate benefits and mutex contention; external tools like ProxySQL are recommended for caching.

3. Security Enhancements

3.1 Deadlock Detection

From version 5.0.3, the innodb_deadlock_detect variable can disable deadlock detection, which may improve performance under high concurrency.

3.2 Default Authentication Plugin

MySQL 8.0.4 switches the default authentication plugin from mysql_native_password to caching_sha2_password , with client libraries updated accordingly.

3.3 Password Policy Enhancements

Policies include preventing reuse of the last five passwords ( password_history = 5 ) and disallowing password reuse within 90 days ( password_reuse_interval = 90 ). Changing a password now requires the current password ( password_require_current = on ).

3.4 Dual Passwords

Supporting a secondary password allows applications to switch to the new password without downtime.

3.5 Role Management

Roles act as named privilege sets that can be granted to users, simplifying permission administration.

3.6 Redo & Undo Log Encryption

Encryption can be enabled via the innodb_redo_log_encrypt and innodb_undo_log_encrypt variables.

4. Optimizer Enhancements

4.1 Cost Model Improvements

The optimizer now accounts for whether pages reside in the buffer pool, improving cost estimations.

4.2 Scalable Read/Write Workloads

MySQL 8.0 shows up to double the throughput of 5.7 under mixed read/write loads, thanks to a redesigned InnoDB redo‑log architecture that eliminates the global file_system_mutex lock.

4.3 Better Performance Under High Contention

Version 8.0 delivers superior latency and throughput for hot‑row workloads, efficiently handling bursty transaction spikes.

5. Other Enhancements

5.1 Persistent Global Variables

Appending PERSIST to a SET statement writes the variable to mysqld-auto.cnf , ensuring it survives restarts.

SET PERSIST max_connections = 1000;
SET @@PERSIST.max_connections = 1000;

5.2 Binlog Expiration in Seconds

MySQL 8 replaces expire_logs_days with binlog_expire_logs_seconds , allowing second‑level granularity.

5.3 Automatic Undo Space Reclamation

Parameters innodb_undo_log_truncate , innodb_undo_tablespaces , and innodb_max_undo_log_size manage automatic undo tablespace cleanup.

5.4 GIS Enhancements

Support for SRS‑aware spatial data types, indexes, and functions enables accurate geodesic calculations across thousands of coordinate systems.

5.5 Optimizer Switch Table

The @@optimizer_switch variable lists all optimizer flags; they can be set per session or globally, e.g., set session optimizer_switch="use_invisible_indexes=off" .