Comparing MySQL® 5.7 and 8.0
MySQL® 8.0 is a major update compared to MySQL® 5.7, released almost ten years ago. This version offers significant improvements in the DBMS architecture and functionality.
Transactional data dictionary
MySQL® 8.0 has replaced the separate .frm
file-based metadata storage system with the unified transactional data dictionary.
Advantages of the new approach:
- Eliminates the metadata desynchronization issues in case of faults.
- No locks when executing DDL operations.
- Optimized table structure modification without long-term locks.
- More reliable database structure backup and restore operations.
Security
MySQL® 8.0 implements improved security mechanisms.
Key improvements:
caching_sha2_password
instead ofmysql_native_password
as the default mechanism.- Improved password storage system.
- Advanced protection against modern attack vectors.
- Backward compatibility with previous authentication mechanisms.
SQL features
-
MySQL® 8.0 provides built-in support of window functions to facilitate complex computations:
SELECT sale_date, product_id, sales_amount, ROUND(AVG(sales_amount) OVER ( ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2) as moving_average_3days FROM daily_sales;
-
MySQL® 8.0 implements Common Table Expressions (CTE), which allows you to create better structured queries:
WITH customer_totals AS ( SELECT customer_id, region, SUM(order_amount) as total_spent FROM orders GROUP BY customer_id, region ) SELECT * FROM customer_totals;
Working with JSON
MySQL® 8.0 offers advanced features for working with JSON data:
-
Creating indexes:
ALTER TABLE users ADD INDEX idx_profile_age ((CAST(profile->>'$.age' AS UNSIGNED)));
-
Optimized syntax for JSON:
-- Value extraction SELECT id, profile->>'$.name' as name, profile->>'$.contacts.email' as email FROM users;
Additional features
New features in MySQL® 8.0:
- Invisible indexes for testing index structures in the production environment.
EXPLAIN ANALYZE
for analyzing actual query plans.- Optimized metadata processing system to reduce the number of locks.
Performance
MySQL® 8.0 performance test results:
- Single-host configurations demonstrate higher performance with MySQL® 5.7.
- Multi-host configurations show improved stability with MySQL® 8.0.
The choice of version depends on your specific performance requirements.
Upgrading to MySQL® 8.0
Starting October 2023, official support of MySQL® 5.7 has been discontinued. Advantages of upgrading to MySQL® 8.0:
- Access to new features and improved security mechanisms.
- Yandex Cloud migration tools.
- Long-term support of the new version.
For more details about migration, see MySQL® version upgrade.